Microsoft Excel 2016. Уровень 2. Расширенные возможности

Этот 3-дневный курс с преподавателем позволит студентам получить знания и навыки, которые позволят эффективно использовать встроенные функции Microsoft Excel 2016, обрабатывать большие объемы данных, создавать отчеты с использованием сводных таблиц и сводных диаграмм, производить сложный анализ данных, изучить вопросы импорта данных с применением запросов PowerQuery, защиты и наглядного представления информации.

Целевая аудитория

Этот курс интересен для менеджеров и офисных работников всех уровней, руководителей секретарей, помощников руководителей, бухгалтеров, экономистов, аналитиков, логистов и многих других специалистов

Приобретаемые знания и навыки

  • Управлять разными видами ссылок в расчетах при использовании данных как из одной книги, так и из разных
  • Использовать библиотеку функций для создания сложных формул
  • Использовать функции для сравнения и подстановки данных
  • Сравнивать данные в таблицах, чтобы выявлять совпадения и различия значений
  • Подставлять данные из одной таблицы в другую
  • Выделять нужные данные c использованием условного форматирования
  • Эффективно работать с большими массивами данных: использовать возможности таблиц, сортировать по значениям и форматированию, фильтровать, подводить итоги, консолидировать данные
  • Использовать редактор запросов Power Query для импорта данных из других приложений
  • Анализировать данные с использованием сводных таблиц и сводных диаграмм
  • защищать ячейки, рабочие листы и книги

Программа курса

Модуль 1: Применение встроенных функций Excel

В этом модуле Вы изучите, как создавать формулы с использованием исходных данных как из одной, так и разных книг, решать сложные задачи с применением библиотеки встроенных функций Excel, предоставлять результаты вычислений в наглядном и удобном для анализа виде.

Уроки

  • Ввод формул
  • Различные типы ссылок
  • Связывание листов и рабочих книг
  • Применение различных типов встроенных функций:
  • Математические функции
  • Статистические функции
  • Функции ссылок и подстановки
  • Логические функции
  • Текстовые функции
  • Функции даты
  • Условное форматирование

Lab 1: Использование различных типов ссылок в расчетах

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

Lab 2: Использование библиотеки функций Excel в расчетах

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

Lab 3: Условное форматирование

  • Использование консолидации для расчета итогов по данным одной таблицы
  • Построение итоговых отчетов по данных из нескольких таблиц – расчет годовых итогов на основе квартальных данных
  • Получение консолидированных данных из разных книг с созданием связи с исходными таблицами
After completing this module, students will be able to:
  • Использовать различные типы ссылок в расчетах
  • Использовать функции с условиями или критериями
  • Объединять и разбивать текстовые данные
  • Сравнивать таблицы
  • Использовать условное форматирование для выделение ячеек или диапазонов ячеек

Модуль 2: Работа с большими табличными массивами

В этом модуле Вы изучите, как упорядочивать данные, удалять повторяющиеся записи, выбирать из списка данные по простым и сложным условиям, консолидировать данные из нескольких таблиц, рассчитывать промежуточные итоги, импортировать данные из других источников с использованием Power Query.

Уроки

  • Создание и ведение таблиц
  • Удаление дубликатов
  • Сортировка данных:
  • Сортировка по одному критерию
  • Многоуровневая сортировка
  • Сортировка по форматированию
  • Фильтрация данных:
  • Автофильтр
  • Срезы
  • Расширенный фильтр
  • Подведение промежуточных итогов
  • Консолидация данных из нескольких диапазонов (листов) в один
  • Импортировать данные с использованием Power Query

Lab 1: I Создание и обработка таблиц

  • Преобразование диапазона ячеек в таблицу
  • Расчеты в таблицах: формулы и промежуточные итоги
  • Удаление повторяющихся записей в таблице

Lab 2: Сортировка и фильтрация данных

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

Lab 3: Промежуточные итоги

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

Lab 4: Консолидация

  • Использование консолидации для расчета итогов по данным одной таблицы
  • Построение итоговых отчетов по данных из нескольких таблиц – расчет годовых итогов на основе квартальных данных
  • Получение консолидированных данных из разных книг с созданием связи с исходными таблицами

Lab 5: Импорт данных с Power Query

  • Импорт данных о курсах валют с веб-страницы
  • Импорт из текстового файла
  • Импорт данных из базы данных Microsoft Access
  • Работа с импортированными данными: редактирование, создание вычисляемых полей, обновление
  • Объединение запросов
After completing this module, students will be able to:
  • Делать выборку из табличных данных с использованием фильтров и срезов
  • Сортировать и фильтровать по значениям и цветам
  • Удалять повторяющиеся данные
  • Вычислять промежуточные итоги
  • Консолидировать несколько источников в один
  • Объединять данные из нескольких источников как одной, так и различных книг в один источник с выполнением вычислений
  • Импортировать данные из Web, Access или Text с использованием Power Query

Модуль 3: Анализ данных с помощью Сводных таблиц

В этом модуле Вы изучите, как анализировать данные таблиц с построением отчетов сводных таблиц, а так же сводных диаграмм, которые помогают визуализировать отчет сводной таблицы для сравнения и поределения тенденций

Уроки

  • Создание сводных таблиц: рекомендуемые и вручную
  • Преобразование сводных таблиц
  • Фильтрация данных в отчете сводной таблицы
  • Использование срезов и временной шкалы для фильтрации отчетов
  • Настройка полей сводной таблицы
  • Создание и редактирование вычисляемых полей
  • Группировка полей сводной таблицы
  • Создание сводных диаграмм
  • Обновление сводных таблиц и сводных диаграмм
  • Создание сводных таблиц с использованием модели данных:
  • Источник модели данных
  • Экспресс-просмотр с детализацией данных
  • Подсчет количества различных элементов
  • Создание связей между источниками

Lab 1: Построение отчетов

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

Lab 2: Анализ данных

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

Lab 3: Использование модели данных в качестве источика сводной таблицы

  • Создание источника модели данных
  • Анализ сводной таблицы с использованием модели данных, использование детализации данных
  • Расчет количества уникальных значений
  • Построение отчета с созданием связи меду таблицами
After completing this module, students will be able to:
  • Быстро и эффективно создавать необходимые отчеты с применением дополнительных вычислений
  • Перемещать поля между строками и столбцами, чтобы увидеть различные сводки исходных данных
  • Группировать данные для анализа по годам, кварталам, месяцам, неделям, чтобы легко посмотреть данные, которые хотели проанализировать
  • Работать с уровнями данных: сворачивать и разворачивать. Чтобы сосредотачиваться на результатах, представляющих больший интерес для исследователя
  • Использовать форматирование отчетов для лучшего восприятия информации совместно с условным форматированием в виде гистограмм и значков
  • Создавать сводные диаграммы разных типов для иллюстрации различных процессов
  • Использовать новые возможности, появляющиеся при построении сводной таблицы по модели данных
  • Создавать отчет сводной таблицы или сводной диаграммы по связанным таблицам

Модуль 4: Особенности совместной работы

В этом модуле Вы изучите, как защитить информацию от нежелательных изменений, просмотров информации как во всей книге, так и на отдельных листах, предупредить ввод не корректных данных

Уроки

  • Защита ячеек, листов и рабочих книг Excel
  • Проверка вводимых значений:
  • Установка ограничений на ввод данных
  • Поиск и исправление неверных данных

Lab 1: Защита информации

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

Lab 2: Проверка данных

  • Установка ограничений на вводимые значения для различных типов данных
  • Проверка введенных значений на корректность и исправление ошибок
After completing this module, students will be able to:
  • Защищать информацию в книге от несанкционированного доступа со стороны других пользователей
  • Отображать или скрывать формулы
  • Защищать определенные области на листе
  • Использовать проверку данных, чтобы контролировать тип данных или значения, вводимые в ячейку
  • Выявлять неверные данные

Информация о курсе

Вендор: Пользовательские курсы
Код курса: 55160
Продолжительность: 3 дня / 24 часа
Направление: Авторизованные курсы Microsoft Office
Экзамен :
Записаться на курс

Название курса и комментарии*

Ваше имя и фамилия*

Компания*

Телефон*

Email*