Программа курса Эксперт MS Excel

Создание формул excel с различными видами ссылок: 
Относительные, абсолютные, смешанные, трёхмерные (ссылки с разных листов книги) и четырёхмерные (ссылки из разных файлов).
Именованные диапазоны: 

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

Функции excel, классификация по категориям, синтаксис и практическое применение:

• функции математические: ОКРУГЛ, ОКРУГЛВНИЗ, ОКРУГЛВВЕРХ, ОКРВНИЗ, ОКРВВЕРХ, ЦЕЛОЕ, ЧЁТН, НЕЧЁТН, СУММ, СУММЕСЛИ, СУММЕСЛИМН;
• функции статистики: СРЗНАЧ, СРЗНАЧА, СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН, СЧЁТ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, МИН, МАХ
• функции логические: ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА, вложенность логических функций
• функции категории ссылки и массивы: ВПР, ГПР, ПОИСКПОЗ, ИНДЕКС
• функции текстовые: ЛЕВСИМВ, ПРАВСИМВ, ПРОПНАЧ, СЦЕПИТЬ, СЖПРОБЕЛЫ, ПРОПИСН,СТРОЧН, ПСТР,НАЙТИ,ПОИСК, ДЛСТР, ЗНАЧЕН ТЕКСТ ПО СТОЛБЦАМ
• функции даты и времени: ДЕНЬ, ГОД, МЕСЯЦ, СЕГОДНЯ, ТДАТА, РАБДЕНЬ, ЧИСТРАБДНИ, ДОЛЯГОДА

Фильтрация данных таблицы:

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

Консолидация данных: 

• объединение данных с разных листов и разных книг
• консолидация данных со связями и без связи с исходными данными
• способы изменения и обновления связей при консолидации в excel

Импорт данных:

• Импорт из Интернета
• настройка обновлений импортируемых данных
• Импорт текстовых файлов
• Импорт из баз данных Access

Создание сводных таблиц:

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

Сводные диаграммы: 

• создание сводной диаграммы на основе сводной таблицы
• фильтрация сводной диаграммы
• построение диаграмм с корреляцией

Способы защиты данных эксель: 

• как поставить защиту на лист
• как защитить книгу паролем
• как настроить работу разных пользователей одной таблицы, чтобы они могли изменять определённые диапазоны под своим паролем
• как скрыть формулы в ячейках
• как защитить структуру книги, т.е. удаление, вставку и перемещение листов
• как снять пароль с книги и с листов Временное скрытие данных:  Скрыть / Показать столбец  Скрыть / Показать строку  Скрыть / Показать диапазон

Проверка вводимых значений: 

• Как избежать ошибок при вводе данных
• Проверка на ошибки ввода
• Создание каскадных списков

Способы запуска макросов:

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

Практическое применение инструмента "Таблица данных" для анализа развития данных при 2-х переменных. Создание своих пользовательских форматов для таблиц. Решение многокритериальных задач оптимизации с помощью надстроек Поиск решения, Подбор параметра, Сценарии. Построение нестандартных диаграмм для анализа данных: Диаграмма Ганта, Торнадо, Водопад, Термометр, диаграммы с пользовательскими инструментами управления. Инструменты прогнозирования данных: линии тренда, функции для прогнозирования.

Создание формул с помощью функций категории «Ссылки и массивы»

• Преимущества использования формул массивов
• Применение в простых вычислениях
• Изменение формулы массива
• Как применять функции в формулах массивов

Извлечение данных из массива данных, практическое применение:

• Практическое использование функций подстановок при двустороннем поиске, аналог ВПР и ГПР
• Поиск по нескольким критериям

Поиск с использованием функций Столбец и Строка

Применение при решении сложных задач функции:

• СТРОКА
• СТОЛБЕЦ
• ДВССЫЛ

Научитесь создавать зависимые каскадные списки с постоянным источником.

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

• ПОИСКПОЗ
• ИНДЕКС

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

Научитесь использовать функции: 

• ТРАНСП
• ДВССЫЛ 
• АДРЕС 
• СМЕЩ
• СТРОКА 
• СТОЛБЕЦ

Научитесь создавать зависимые списки с изменяемым источником.

Построение и редактирование различных спарклайнов. Данный инструмент доступен с 2007 версии excel. Построите следующие виды нестандартных диаграмм:

• Саморасширяющиеся - в ситуации, когда построение диаграммы происходит по данным всей таблицы, которые затем могут изменяться – добавляться или удаляться строки/столбцы таблицы, можно построить диаграмму, которая будет сама определять размер текущей таблицы.
• Смешанные диаграммы - Использование на одной диаграмме несколько типов диаграмм, делает ее более наглядной и удобной для анализа данных.
• Гистограмма с отображением итогов

Проектная диаграмма Ганта - используется для построения различных графиков работ и планирования.

Вы научитесь строить следующие виды диаграмм:

• Диаграмма сравнений Торнадо - является визуальным способом представления результатов попарного сравнения двух наборов данных.
• Каскадная диаграмма Водопад - это метод продемонстрировать количественные потоки, как из числа А получается число В.

Создание калибровочной диаграммы Термометр - графическое изображение процесса достижения цели.

Научитесь строить диаграммы с пользовательскими элементами управления:

• Диаграмма с включением/выключением рядов данных
• Диаграмма с выбором значений

Диаграмма с зумом и прокруткой

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

Исходные данные для анализа можно разделить на составляющие: Тренд – общее направление развития ситуации, общая тенденция. Сезонная составляющая – периодически повторяющиеся колебания, оцениваемые коэффициентами сезонности. Случайные возмущения – помехи, маскирующие основной тренд и сезонные колебания.