Курс MS Excel Расширенные возможности Уровень 2

Тема №1. Импорт данных из других источников для последующего анализа.

1. Импорт данных из Microsoft Access.
2. Импорт из текстового файла.
3. Импорт с веб-страницы. Тема №2. Именованные диапазоны. 1.Способы создания имен ячеек и диапазонов:

• Применение поля Имя для создания имени.
• Создание имен в диалоговом окне Создание имени.
• Автоматическое создание имен на основе текста заголовков.

1. Синтаксические правила для имен.
2. Использование диалогового окна Диспетчер имен:

• Изменение и удаление имен.
• Фильтрация имен.

1. Поиск именованных диапазонов на рабочем листе.
2. Работа с именами ячеек и диапазонов:

• Вставка имен в формулу.
• Применение имен в уже существующих формулах.
• Создание списка доступных имен на листе. Тема №3. Проверка данных. Раскрывающийся список в ячейках Excel.

1. Защита ячеек от ввода неверных данных - Проверка вводимых значений.
2. Установка параметров проверки. Создание сообщений для ввода. Создание сообщений об ошибке.
3. Проверка вводимых данных с формулой. Запрет ввода повторяющихся значений.
4. Создание раскрывающегося списка на основе Проверки данных.
5. Раскрывающийся список как элемент управления - Поле со списком. Тема №4. Использование Excel для анализа данных. Анализ «что-если». Оптимизация данных. 1.Решение задач поиска целевого значения с помощью Подбора параметра. 2.Использование Таблицы данных для анализа чувствительности моделей на изменение входных данных:

• Таблица данных с одной переменной.
• Таблица данных с двумя переменными. 3.Численная оптимизация и поиск оптимального распределения с помощью надстройки Поиск решения.

1. Моделирование развития событий при неопределенности факторов с использованием Сценариев:

• Создание, просмотр, изменение сценария.
• Итоговый отчет для выбора оптимального варианта. Тема №5. «Продвинутый» ВПР. 1.Динамические диапазоны при работе с функцией ВПР. 2.Многоразовое копирование формулы ВПР, определение номера столбца ( - использование дополнительных ячеек, - функции СТОЛБЕЦ, ЧИСЛСТОЛБ, - определение позиции с помощью функции ПОИСКПОЗ. 3.ВПР по двум и более критериям для извлечения данных из таблицы.

1. ВПР и повторяющиеся значения. 5.Поиск совпадений в разных списках, сравнение баз данных. Тема №6. Многокритериальный поиск данных в массиве данных. Функции ИНДЕКС и ПОИСКПОЗ.
2. Однокритериальный поиск по горизонтали и вертикали.
3. Выборка из списка по нескольким условиям по горизонтали.
4. Многокритериальная выборка по горизонтали и вертикали. Тема №7. Функция "косвенной переадресации" ДВССЫЛ.
5. Сбор информации с нескольких листов. Использование названий листов в формуле.
6. Связанные раскрывающиеся списки с постоянным источником данных. Тема №8. Ссылочная функция и функция поиска СМЕЩ.
7. Связанные раскрывающиеся списки с изменяемым диапазоном данных.
8. Отображение фото при выборе значений в раскрывающемся списке. Тема №9. Транспонирование таблиц и сохранение связи с исходными данными.
9. Использование функций ИНДЕКС и ПОИСКПОЗ.
10. Транспонирование с помощью формулы массива.
11. Функции ДВССЫЛ, АДРЕС, СТРОКА, СТОЛБЕЦ. Тема №10. Сводные таблицы и диаграммы. 1.Создание сводной таблицы (диаграммы). 2.Вычисления в сводных таблицах: итоги, дополнительные вычисления, вычисляемые поля. 3.Макеты сводных таблиц. Скрытие и отображение промежуточных и общих итогов. 4.Группировка дат, числовых и текстовых полей. 5.Фильтрация данных сводной таблицы. Срезы. 6.Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Примеры использования. Тема №11. Условное форматирование. Расширенные возможности. 1.Расширенные возможности использования условного форматирования с использованием функций Excel. 2.Включение/Отключение условного форматирования с помощью переключателя. 3.Построение диаграммы Ганта с использованием условного форматирования. Тема №12. Возможности визуализации данных. Диаграммы.
12. Динамическая диаграмма с изменяемым диапазоном данных.
13. Добавление на гистограмму подписей с итоговыми значениями.
14. Попарное сравнение данных с помощью диаграммы Торнадо.
15. Визуализация достижения целевых значений в виде Пулевой диаграммы.
16. Каскадная диаграмма отклонений Водопад.
17. Иллюстрация плана, графика работ по проекту с помощью диаграммы Ганта.
18. Создание диаграммы Спидометр с движущейся стрелкой.
19. "Живые" интерактивные диаграммы:

• Диаграмма с выбором значений в раскрывающемся списке.
• Включение и выключение рядов данных при помощи флажков.
• Диаграмма с прокруткой и масштабированием. ДОПОЛНИТЕЛЬНАЯ ТЕМА (Эта тема читается в случае, если осталось время в конце курса. Если время не остается, можно заказать отдельный урок по этой теме. Длительность 2 часа. ) Тема № 13. Методы прогнозирования данных

1. Сглаживание временных рядов: метод скользящего среднего.
2. Прогнозирование с помощью функций регрессии:

• Прогноз методом линейной регрессии: функция ПРЕДСКАЗ.
• Множественная линейная регрессия: функция ТЕНДЕНЦИЯ.
• Составление нелинейного прогноза: функция РОСТ.

1. Пример прогнозирования с использованием линии тренда.
2. Сезонная модель прогнозирования.