Эффективное использование MS Excel для печати

1.

План тренинга
1. Знакомство с основными «горячими клавишами» для ускорения
работы в Excel (30 мин.)
2. Работа с функциями (математические, статистические, текстовые
функции, ВПР для анализа данных). (90 мин)
3. Работа с сводными таблицами: создание сводных таблиц для
анализа больших объемов данных, применение сводных таблиц к
реальным бухгалтерским отчетам. (30 мин)
4. Excel в помощь финансисту: фильтрация данных, условное
форматирование, поиск ошибок и опечаток в таблицах, контроль
вводимых данных, преобразование таблиц из 1С в плоскую таблицу для
анализа и пр. (60 мин)
5. Вопросы – ответы. (30 мин)
Окончание тренинга - 17.00

2. 1. Горячие клавиши

Сочетания клавиш на клавиатуре, при использовании которых
можно существенно сократить время работы. Горячие
клавиши предназначены для:
удобства навигации между окнами, по файлу и по таблице;
быстрого изменения форматов ячеек;
скрытия и отображения столбцов, группировки данных;
разбивки текста по абзацам;
и многого другого.
Вам не нужно запоминать
полный список – начните
использовать те, которые удобны Вам.
Совет: распечатать часто используемые варианты горячих
клавиш,
повесить
шпаргалку
около
монитора
напоминание и приучать себя ими пользоваться.
как

3. Работа с функциями

Функция ВПР ― это функция для поиска в Excel.
Анализирует выбранный диапазон таблицы, двигаясь сверху вниз в
поисках заданного значения. Как только идентификатор найден, ВПР
копирует в другую таблицу данные из нужной колонки напротив него.
* Обычно ставится 0 – достаточно это запомнить. Справочно в рабочем файле приведен пример, когда
интервальный просмотр не указан или не равен 0, используется редко.
Некоторые ограничения для ВПР:
- Столбец с искомыми значениями должен быть строго слева
от столбцов с данными
- Значения в таблице должны четко соответствовать искомому
значению (грамматика, отсутствие лишних пробелов и пр.)
- При наличии дубликатов в исходной таблице, ВПР вернет
значение, относящееся к первому из них, то есть к тому,
который стоит выше в таблице.

4. Работа с текстовыми функциями

Рассмотрим часто используемые:
Функция СЦЕПИТЬ(текст1;[текст2];…) объединяет несколько
строк в одну (можно заменить символом &)
ДЛСТР(текст) – возвращает количество знаков в текстовой
строке
ПРАВСИМВ(текст;[число знаков]) – возвращает указанное
число знаков с конца строки текста;
ЛЕВСИМВ(текст;[число знаков]) – возвращает указанное число
знаков с начала строки текста;
НАЙТИ(искомый текст;просматриваемый текст;[нач_позиция])
– возвращает позицию начала искомой строки текста в
содержащей ее строке текста;
ПСТР(текст;начальная_позиция;количество_знаков) –
возвращает заданное число знаков из строки текста, начиная
с указанной позиции.
Прочие текстовые функции позволяют делать строчные буквы
прописными, заменять выбранные символы другими, удалять
непечатаемые символы и лишние пробелы из текста и др.

5. Работа с датами

Функция ГОД(дата) – возвращает год для указанной даты;
МЕСЯЦ(дата) – возвращает порядковый номер месяца для
указанной даты;
ДЕНЬ(дата) - возвращает число месяца для указанной
даты;
ДАТАМЕС(нач_дата,число_месяцев) – возвращает дату
через указанное число месяцев от заданной начальной
даты;
ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]) /
ЧИСТРАБДНИ.МЕЖД(нач_дата;кон_дата;[выходные];[празд
ники]) - подсчет количества рабочих дней в диапазоне дат
с учетом стандартных выходных (сб и вс для ЧИСТРАБДНИ)
или других выходных (ЧИСТРАБДНИ.МЕЖД), а также
указанных вручную праздников.

6. Математические

К математическим относятся функции суммирования, поиска
квадратного корня, возведения в степень, модуля числа,
округления, вывода случайных чисел и многие другие. При
необходимости любые функции можно найти через поиск в
Excel. В рамках тренинга разберем 2 крайне полезные
функции суммирования диапазона чисел согласно заданным
условиям:
СУММЕСЛИ(диапазон;критерий;[диапазон суммирования]) –
суммирует ячейки, заданные указанным условием
СУММЕСЛИМН
(диапазон_суммирования;диапазон_условия1;условие1;…) –
суммирует ячейки, заданные указанным набором условий
Часто данными функциями можно заменить функцию ВПР в
случае, когда в исходной таблице есть дубликаты.

7. Статистические

К статистическим относятся функции для расчета
вероятностей, распределений, дисперсий, квадратных
отклонений и пр.
Мы рассмотрим 2 простые функции, которые могут быть
полезны в повседневной работе:
СЧЁТЕСЛИ(диапазон;критерий) - подсчитывает количество
ячеек в диапазоне, удовлетворяющих заданному условию
СЧЁТЕСЛИМН(диапазон_условия1;условие1;…) –
подсчитывает количество ячеек внутри диапазона,
удовлетворяющих нескольким условиям.

8. Сводные таблицы

Незаменимы для анализа больших объемов данных. Позволяют
быстро менять способы анализа, подводить общие и
промежуточные итоги.
Обязательные требования:
Все столбцы должны быть названы
Поля должны быть корректно отформатированы (числа в
числовом формате, даты датой и пр.
Не стоит забывать:
Сводная таблица занимает большой объем оперативной
памяти, поэтому можно отвязать ее от источника данных,
тогда размер файла Excel уменьшится (подходит, если не
ожидается изменение исходных данных)
При изменении исходных данных сводную таблицу
необходимо обновить

9. В помощь финансисту

Использование автофильтров
Проверка данных (контроль вводимых данных,
использование списков для ввода значений в ячейку)
Текст по столбцам (на примере преобразования карточки
счета из 1С в плоскую таблицу)
Условное форматирование для зрительного выделения
необходимой информации
Преобразование ОСВ из 1С в плоскую таблицу