ЧЕТЫРЕХМОДУЛЬНОЕ ОБУЧЕНИЕ ПО EXCEL

Тренер Наталья Кузьмина / г. Хабаровск 

Модуль 1. Бизнес-аналитика в Excel. Базовые возможности

Семинар-практикум

Дата проведения: 04.04.2025

• Общая продолжительность обучения– с 12.00 до 19.00, перерыв на обед – с 13.30 до 14.30, а также каждые 1,5 часа – кофе-брейк

• Технические требования – компьютер или ноутбук с установленной версией Excel не ранее 2010 года

Что Вы будете уметь:

• Уверенно работать с функциями
• Создавать выпадающие списки и делить текст по столбцам. Объединять данные, находящиеся в разных ячейках
• Консолидировать данные с разных листов
• Создавать сводные таблицы
• Выделять цветом ячейки, имеющие определенный признак
• Защищать свои данные от редактирования и удаления

1. Наиболее востребованные инструменты Excel

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

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

2.Функции суммирования

• Автосумма
• Суммирование данных по критерию при помощи функции СУММЕСЛИ
• Использование абсолютных, относительных и комбинированных ссылок в Excel
• Функция СУММЕСЛИМН: суммирование данных по нескольким критериям одновременно

3.Функция ВПР, ЕСЛИОШИБКА

• Подстановка данных с помощью функций ВПР – как за несколько секунд перенести данные из одной таблицы в другую
• Замена ошибок с помощью логической функции ЕСЛИОШИБКА
• Использование ВПР при неточном совпадении
• Ограничения ВПР

4.Функции округления, счета. Функции работы с датами и текстом

• Применение математических функций округления - ОКРУГЛ, ОКРУГЛВНИЗ, ОКРУГЛВВЕРХ и ЦЕЛОЕ
• Функции счета – СЧЁТ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН
• Статистические формулы: СРЗНАЧ, СРЗНАЧА, СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН
• Функции для работы с датами. Преобразование даты в текст (например, день недели или месяц)
• Функции для работы с текстом (ЛЕВСИМВ, ПРАВСИМВ, НАЙТИ)

5.Логические функции

• Логические функции – ИСТИНА, ЛОЖЬ, ЕСЛИ, ИЛИ, И, НЕ
• Как с помощью логических функций автоматизировать расчет, при котором используется несколько условий (на примере начисления премии сотрудникам)

6.Сводные таблицы и условное форматирование

• Условное форматирование с применением встроенных правил: гистограмма, цветовые шкалы, наборы значков
• Требования к исходным данным при построении сводных таблиц

• Создание одномерных, двумерных и многомерных сводных таблиц. Обновление сводных таблиц
• Настройка полей сводной таблицы
• Группировка дат в сводных таблицах
• Итоги и промежуточные итоги в сводной таблице
• Стили сводной таблицы
• Обновление сводных таблиц и изменение диапазона данных

Модуль 2. Бизнес-аналитика в Excel. Уверенный пользователь

Семинар-практикум

Дата проведения: 10.04.2025

• Общая продолжительность обучения– с 12.00 до 19.00, перерыв на обед – с 13.30 до 14.30, а также каждые 1,5 часа – кофе-брейк
• Технические требования – компьютер или ноутбук с установленной версией Excel не ранее 2010 года

Что Вы будете уметь:

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

1.Функции работы с текстом

• Подсчет количества знаков в ячейке (ДЛТР)
• Изменить буквы на прописные или строчные (ПРОПИСН, СТРОЧН)
• Функции поиска (НАЙТИ, ПОИСК)
• Функции объединения текста (СЦЕПИТЬ)
• Функции для извлечения части теста из ячейки (ЛЕВСИМВ, ПРАВСИМВ, ПСТР

2.Логические функции и условное форматирование

• Расчет премии сотрудникам с помощью логических функций (ЕСЛИ, ИЛИ, НЕ, И)
• Условное форматирование с применением формул Создание собственных правил (выделить цветом просроченные счета/уволенных сотрудников
• Примеры использования логических функций совместно с условным форматированием: рассчитать скидку от объема, уценка товара с коротким сроком годности

3.Продвинутый ВПР

• Ограничения ВПР
• ВПР по нескольким ключевым полям
• Совместное использование функций ИНДЕКС и ПОИСКПОЗ
• Использование связки ВПР +ПОИСКПОЗ

4.Другие функции

• Функции работы с датами и временем (ДЕНЬНЕД, ГОД, МЕСЯЦ, ДЕНЬ, СЕГОДНЯ, КОНМЕСЯЦА, РАЗНДАТ)
• Автоматизация расчета ежедневной плановой суммы продаж
• Средневзвешенное. Функция СУММПРОИЗВ
• Использование функции ИНДЕКС для создания прочной ссылки
• Использование формул ИНДЕКС и ПОИСКПОЗ для получения данных из двухмерных таблиц
• Создание многоуровневых выпадающих списков с помощью функции ДВССЫЛ

5.Анализ данных с помощью сводных таблиц

• Создание одномерных, двумерных, многомерных сводных таблиц
• Добавление вычисляемых полей в сводную таблицу
• Дополнительные вычисления в сводной таблице
• Группировка полей (дата, число, текст)
• Создание сводных диаграмм
• Построение нескольких связанных между собой отчетов

Модуль 3. Сводные таблицы и визуализация в Excel

Семинар-практикум

Дата проведения: 24.04.2025

• Общая продолжительность обучения– с 12.00 до 19.00, перерыв на обед – с 13.30 до 14.30, а также каждые 1,5 часа – кофе-брейк

• Технические требования – компьютер или ноутбук с установленной версией Excel не ранее 2013 года; с возможностью выхода в Интернет

Что Вы будете уметь:

• Создавать сводные таблицы, группировать, сортировать данные в сводных таблицах
• Копировать сводные таблицы, добавлять дополнительные вычисления в сводную таблицу
• Связывать между собой несколько отчетов сводных таблиц с помощью инструментов Срез и временная шкала
• Создавать связи между Диаграммой в Power Point и источником данных в книге Excel, настраивать переходы между документами с помощью гиперссылки
• Познакомиться с возможностью надстройки Power Query для преобразования сырых данных, консолидации, объединении запросов и др.

1.Общие принципы построения сводных таблиц, настройка параметров

• Требования к исходным данным для построения сводных таблиц
• Преобразование исходных данных (убираем объединенные ячейки, заполняем пустые ячейки, преобразовываем таблицу в плоскую и т.д.)
• Создание сводной таблицы, настройка полей.
• Сортировка и группировка полей в сводной таблице. Группировка дат, чисел, текстовых данных
• Сортировка в сводной таблице
• Детализация отдельных данных сводной таблицы
• Параметры сводной таблицы. Как отключить автоматическое изменение ширины столбцов при обновлении
• «Умная» таблица как источник данных
• Классическое отображение сводных таблиц
• Стили в сводной таблице
• Общие и промежуточные итоги в сводной таблице
• Обновление сводных таблиц, изменение источник данных

2.Вычисления в сводной таблице. Визуализация в сводных таблицах

• Дополнительные вычисления (% от общей суммы, процент от суммы по столбцу, процент от суммы по строке)
• Создание вычисляемого поля
• Вычисляемый объект
• Использование формулы вне сводной таблицы для создания вычисляемого поля
• Визуализация данных в сводной таблице с помощью Срезов
• Шкала времени в сводной таблице
• Создание сводных диаграмм
• Как связать несколько сводных таблиц и диаграмм на одном листе
• Условное форматирование в сводной таблице
• Экспорт диаграммы в Power Point
• Настройка гиперссылки для быстрого перехода из Power Point в Excel и обратно
• Настройка условного форматирования в сводных таблицах

3.Основы использование Power Query для создания отчетов

• Загрузка данных из файла Excel
• Объединить запросы из разных файлов
• Добавить информацию по ключевому полю (аналог ВПР)
• Преобразование «сырых» данных в Power Query (развернуть таблицу из перекрестной в плоскую, транспонировать, удалить дубликаты и др)
• Загрузить данные из папки. Автоконсолидация
• Создание сводной таблицы из подключения
• Создание дашборда

Модуль 4. Бизнес-аналитика в Google-таблицах.

Базовые возможности

Семинар-практикум

Дата проведения: 25.04.2025

• Общая продолжительность обучения– с 10.00 до 16.00, перерыв на обед – с 13.30 до 14.30, а также каждые 1,5 часа – кофе-брейк

·Технические требования:

• компьютер или ноутбук

• стабильное подключение к Интернету
• Наличие google-аккаунта

Инструкция по созданию google-аккаунта: https://drive.google.com/file/d/14Riw9r7X4Xq9QGkWxVhhft3HkTp2XAXK/view?u sp=sharing

Что Вы будете уметь:

• Освоите функции Google-таблиц, поймете их основные отличия и схожесть с функциями Excel
• Научитесь создавать сводные таблицы
• Строить наглядные графики и диаграммы
• Выделять цветом ячейки, имеющие определенный признак
• Создавать выпадающие списки
• Защищать свои данные от редактирования и удаления

·1 Особенности работы с функциями в Google таблицах, сравнение с Excel. Инструменты и простые формулы

• Начало работы, интерфейс. Создание нового документа. Сравнение с интерфейсом Excel, совместимость файлов, созданных в Excel и Google таблицах
• Совместная работа в таблицах. Настройка прав доступа. Способы защиты информации
• Простые функции СУММ, СРЗНАЧ, ОКРУГЛ, ЦЕЛОЕ, МИН, МАКС. Особенности работы с функциями в Google таблицах
• Использование именованных ячеек в формулах: Создание имен Редактирование имен Вставка имен в формулу
• Как находящуюся в одной ячейке информацию разбить на несколько столбцов
• Как объединить текст из нескольких ячеек в одну
• Выпадающие списки. Наличие быстрого поиска, автоматическое удаление дубликатов из диапазона списочных значений
• Закрепление строк и столбцов
• Подстановка данных с помощью функций ВПР, ГПР

2 Особенности фильтрации, сортировки. Логические функции и

функции суммирования. Уникальные функции (отсутствующие в Excel)

• Сортировка данных и фильтрация в Google-таблицах
• Выборка данных с помощью функции FILTER
• Вывод и подсчет уникальных значений. Функции UNIC,UNICCOUNT
• Форматы данных. Отличие от форматов в Excel
• Суммирование данных по критерию при помощи функции СУММЕСЛИ
• Функция СУММЕСЛИМН: суммирование данных по нескольким критериям одновременно
• Логические функции. Функции ЕСЛИ, И, ИЛИ