Эффективная работа в Excel. Оптимизация и автоматизация работы с отчётами

Программа

1 день

Работа с формулами и функциями

• Комбинирование функций в формулах для решения различных задач
• Понятие вспомогательные, основные функции, функции без аргументов
• Примеры различного комбинирования функций
• Более удобный ввод функций с помощью переноса строк

Математические функции и статистические функции

• Понятие «вложенные параметры» функций
• АГРЕГАТ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТ, СЧЁТЗ, СЧИТАТЬПУСТОТЫ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН

Функции для работы с текстом

• ПРОПИСН, ПРОПНАЧ, СТРОЧН, СЖПРОБЕЛЫ, ПЕЧСИМВ, НАЙТИ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР, ДЛСТР, ОБЪЕДИНИТЬ
• Практические примеры использования текстовых функций

Функции для работы с датой и временем

• Особенности учёта времени и даты в Excel
• Расчёт календарных дат без функций
• ТДАТА, СЕГОДНЯ, ГОД, ДАТАМЕС, ДЕНЬНЕД, НОМНЕД, ЧИСТРАБДНИ, РАБДЕНЬ, РАБДЕНЬМЕЖД
• Практические примеры применения функций даты и времени

Логические функции и выражения

• Операторы логики в Excel, логические значения
• ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА, ЕСЛИМН, ПЕРЕКЛЮЧ

Функции ссылки и массивы

• ПОИСКПОЗ, ИНДЕКС, СМЕЩ, ФИЛЬТР, СОРТ, СОРТПО
• Проблемы инструмента фильтр в Excel, особенности и плюсы функции фильтр
• Комбинация функции фильтр с таблицами Power Query

Функции для проверки данных

• Создание выпадающих списков
• Создание логистических калькуляторов с помощью выпадающих списков
• Использование в формулах ссылок на ячейки других листов и других рабочих книг
• Эффективное использование ссылок
• Создание формулы, использующей значения другого листа или книги
• Синтаксис ссылки на ячейку на другом листе в той же книге
• Синтаксис ссылки на ячейку в другой книге
• Работа с внешними связями
• Изменение значений формул с внешними связями. Проверка состояния связей

Инструменты работы с данными и формулами

• Зависимости формул (Formula Auditing)
• Отображение связей между формулами и ячейками
• Трассировка влияющих ячеек
• Трассировка формул, ссылающихся на зависимые ячейки
• Переход к влияющей или зависимой ячейке на другом листе
• Поиск ячеек, ставших причиной ошибок в формуле
• Исправление ошибок в формулах
• Пошаговое вычисление сложных формул
• Окно контрольного значения

Подключение к данным с помощью Power Query

• Создание подключения к различным видам документов CSV, XLS, Google Таблицы
• Задание настроек подключения и обработки данных
• Управление шагами обработки данных

Прогнозирование данных с помощью листа прогноза Excel

• Инструмент лист прогноза
• Требования к данным
• Особенности работы функции ПРЕДСКАЗ.ETS

Инструмент проверка данных

• Определение данных, допустимых для ввода в ячейки
• Разрешить ввод только значений из выпадающего списка
• Разрешить ввод числовых значений, находящихся в заданных пределах
• Разрешить ввод даты и времени в рамках определенного интервала времени
• Разрешить текст определенной длины
• Использование формулы
• Отображение сообщения при вводе неправильных данных
• Отображение неверно введенных данных в таблице

Работа с таблицами

• Работа с объектом таблица (Table)
• Общие положения
• Создание таблицы
• Использование инструмента быстрый анализ для создания таблиц
• Возможности таблицы
• Раскрывающиеся списки фильтра
• Использование среза для фильтрации данных
• Строка вставки
• Строка итогов
• Редактирование таблицы
• Создание вычисляемого столбца таблицы

Визуальный анализ данных

• Условное форматирование (Conditional Formatting)
• Условное форматирование
• Понятие условного форматирования
• Общий подход к созданию условного форматирования
• Типы правил условного форматирования
• Использование формулы в качестве критерия форматирования
• Диспетчер правил условного форматирования
• Приоритет обработки правил условного форматирования
• Условное и «безусловное» форматирования
• Использование инструмента быстрый анализ в условном форматировании

2 день

Спарклайны — отображение тенденций изменения данных

• Создание спарклайнов
• Добавление текста на спарклайн
• Настройка спарклайнов
• Использование инструмента быстрый анализ для создания спарклайнов

Диаграммы

• Создание диаграмм
• Выбор стандартного типа диаграммы «по умолчанию»
• Построение диаграммы на основе данных, расположенных в несмежных ячейках
• Настройка диаграмм
• Изменение типа диаграммы
• Выбор стандартного макета диаграммы
• Выбор стиля и цветового оформления диаграммы
• Изменение макета диаграммы
• Параметры элементов диаграммы
• Изменение места расположения диаграммы
• Фильтрация данных диаграммы
• Линии тренда на диаграмме
• Понятие линии тренда
• Типы диаграмм, поддерживающие линии тренда
• Добавление линии тренда к рядам данных
• Выбор наиболее подходящей линии тренда для данных
• Параметры линии тренда
• Использование линии тренда для создания формулы прогнозирования
• Линии ряда, линии проекций и планки погрешностей на диаграмме
• Линии ряда
• Линии проекций
• Коридор колебаний
• Полосы повышения и понижения
• Предел погрешностей

Продвинутый уровень изменения диаграмм

• Использование формата фигуры для визуальной корректировки диаграммы
• Изменение дизайна диаграммы и её структуры с помощью области формат фигуры
• Создание собственного макета для диаграмм
• Управление форматом чисел в диаграммах

Сводные таблицы и сводные диаграммы (pivot table)

• Общие сведения о сводных таблицах
• Общие сведения об исходных данных для сводных таблиц
• Создание сводной таблицы
• Выбор исходных данных
• Основные объекты для работы со сводной таблицей
• Использование шаблонов для создания сводной таблицы
• Основные области сводной таблицы
• Работа с объектом поля сводной таблицы
• Дополнительные параметры окна поля сводной таблицы
• Сведения о создании сводной таблицы на основе нескольких таблиц исходных данных со отношениями (связями)
• Изменение операции сведения данных для создания итогов
• Применение дополнительных вычислений для итоговых значений
• Изменение названий управляющих полей
• Использование параметров сводной таблицы для изменения макета сводной таблицы
• Фильтрация выбором
• Фильтрация заданием условий
• Сравнительный анализ нескольких вариантов фильтрации
• Создание среза
• Отключение среза
• Удаление среза
• Настройки стиля среза
• Настройки параметров среза
• Создание временной шкалы
• Управление временной шкалой
• Настройка дизайна временной шкалы
• Стили сводной таблицы
• Параметры стилей сводной таблицы
• Макет сводной таблицы
• Параметры видимости элементов
• Параметры действия
• Параметры данных
• Извлечение исходных данных для определенного итогового значения
• Использование итоговых значений сводной таблицы в формулах Excel
• Параметры сортировки
• Создание вычисляемых полей и элементов (вычисления)
• Параметры группировки

Сводная диаграмма

• Создание сводной диаграммы
• Выбор стиля и типа сводной диаграммы
• Визуальные настройки сводной диаграммы
• Основные объекты для работы со сводной диаграммой

Выходное тестирование на знание интерфейса и принципов работы excel

• Практический модуль выходного тестирования
• Теоретический модуль выходного тестирования