Сводные таблицы Excel: Семинар

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

Знакомство со сводными таблицами excel

• Сводные таблицы и их практическое применение
• Структура сводных таблиц: Область строк, Область данных,  Область страниц и  столбцов.
• Негласная работа сводных таблиц и ограничения при создании отчётов с помощью сводных  таблиц

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

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

Создание стандартной сводной таблицы

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

Настройка полей сводной таблицы

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

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

• Использование опции Автоформат
• Применение собственных стилей
• Настройка параметров таблицы
• Общая сумма по столбцам
• Общая сумма по строкам
• Автоформат
• Включать скрытые значения
• Объединять ячейки заголовков
• Сохранять форматирование
• Повторять подписи на каждой страницы печати
• Флажок Помечать итоги
• Макет страницы
• Форматирование сводной таблицы

 Изменение способа просмотра данных сводных таблиц

• Функции отображение и сокрытия элементов
• Основы сокрытия элемента
• Отображение всех элементов
• Отображение или сокрытие большинства элементов
• Сокрытие или отображение элементов без данных
• Сокрытие или отображение элементов в поле страниц
• Отображение или сокрытие элементов в поле данных
• Сортировка элементов сводной таблицы
• Сортировка элементов в диалоговом окне Дополнительные параметры поля сводной таблицы
• Влияние изменений в макете на сортировку данных
• Сортировка в ручную
• Сортировка с мопощью кнопок на стандартной панели инструментов
• Создание отчетов Первая десятка»
• Группировка сводных таблиц
• Группировка полей дат
• Включение итогов по годам при группировке данных по месяцам
• Группировка полей дат по неделям
• Группировка двух полей дат в одном отчете
• Отчет по срокам выполнения работ
• Группировка числовых полей
• Группировка текстовых полей
• Группировка и разгруппировка

Сводные таблицы - расширенные возможности

Выполнение расчетов в сводных таблицах

• Вычисляемые поля и вычисляемые элементы
• Метод 1. Добавление вычисляемого поля в источник данных вручную
• Метод 2. Использование формулы вне сводной таблицы для создания вычисляемого поля
• Метод 3. Непостредственная вставка вычисляемого поля в сводную таблицу
• Создание вычисляемого поля
• Прогнозирование итогов по следующему году
• Создание вычисляемых элементов
• Создание отчета
• Преимущества и надостатки вычислений в сводных таблицах
• Приоритет выполнения операторов
• Ссылки на ячейки и именованные диапазоны
• Функции рабочих листов
• Константы
• Ссылки на итоговые суммы
• Специальные правила для вычисляемых полей
• Специальные правила использования вычисляемых элементов
• Управление и поддержка вычислений в сводных таблицах
• Редактирование и удаление вычислений в сводных таблицах
• Изменение порядка выполнения расчетов в вычисляемых полях
• Документирование формул

Создание и использование сводных диаграмм

• Что такое сводная диаграмма
• Создание сводных диаграмм
• Преимущества и недостатки сводных диаграмм
• Оптимизация макета сводной диаграммы
• Точечная, пузырьковая и биржевая диаграммы: вход воспрещен
• Ограничения на размер и расположение элементов
• Непостоянство отдельных настроек
• Создание динамической годовой диаграммы
• Альтернативы сводных диаграмм
• Зачем выносить сор из избы?
• Ограничение на форматирование сводных диаграмм

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

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

Использование в сводных таблицах внешних источников данных

• Создание сводной таблицы с использованием внешних источников данных
• Обхождение ограничения на управление данными в Excel
• Программа MS Query
• Анализ набора данных, содержащего более 83000 записей, с помощью сводной таблицы
• Импорт и использование внешних данных без мастера сводных таблиц
• Создание динамического отчета в сводной таблице
• Создание автономной динамической системы вывода отчетов в сводных таблицах
• Параметры данных сводных таблиц

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

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