Шаг 2. Определение интервала исходных данных
Лабораторная работа № 6
ФОРМИРОВАНИЕ ИТОГОВ. СВОДНЫЕ ТАБЛИЦЫ
Подведение промежуточных итогов
Создайте базу данных объема продаж по регионам. Подумайте, как ускорить данный процесс.
Рис. 6.1Фрагмент таблицы объема продаж по регионам
Для получения различных итоговых сведений по определенным группам данных и в целом по списку используется специальная операция, запускаемая командами:
Данные → Итоги
Аналогом операции является специальная встроенная функция ПРОМЕЖУТОЧНЫЕ ИТОГИ.
Перед выполнением промежуточных итогов по одному или нескольким полям списка обязательным является сортировка значений по этим полям, в противном случае не будет достигнут эффект подведения промежуточных итогов.
Технология получения промежуточных итогов заключается в выполнении следующих действий:
Установить курсор в пределах списка→ Команда Данные → Итоги
Далее на экране откроется диалоговое окно Промежуточные итоги (рис. 6.2). В нем, в раскрывающемся списке При каждом изменении в, выбирается имя поля, по которому необходимо подвести итог. В раскрывающемся списке Операция выбирается одна из одиннадцати доступных и предлагаемых для использования операций, которая будет применяться к значениям полей, отмеченных в списке Добавить итоги по. Перечень операций приведен в табл. 6.1
Рис. 6.2Диалоговое окно Промежуточные итоги
При необходимости можно установить флажки в нижней части диалогового окна.
- Если установлен флажок Заменить текущие итоги, то автоматически будут удалены старые существующие итоговые значения и заменены новыми, рассчитанными по другим формулам.
- Если установлен флажок Конец страницы между группами, то после каждого промежуточного итога вставляется разрыв страницы (т.е. каждая группа значений данных выводится на отдельном рабочем листе).
- Если установлен флажок Итоги под данными, то промежуточные итоги размещаются под данными.
- Кнопка Убрать все очищает заданные параметры промежуточных итогов.
Таблица 6.1 Доступные функции для подведения промежуточных итогов списка
Словесное наименование операции | Номер операции | Встроенная функция – аналог операции |
Среднее | СРЗНАЧ | |
Количество чисел | СЧЕТ | |
Количество | СЧЁТЗ | |
Максимум | МАКС | |
Минимум | МИН | |
Произведение | ПРОИЗВЕД | |
Смещенное отклонение | СТАНДОТКЛОН | |
Несмещенное отклонение | СТАНДОТКЛОНП | |
Сумма | СУММ | |
Смещенная дисперсия | ДИСП | |
Несмещенная дисперсия | ДИСПР |
После создания промежуточных итогов Excel автоматически создает структуру документа.
Пример 1
Имеется таблица условных объемов продаж товаров по месяцам для нескольких регионов (6.1).
Требуется подвести итоги по каждому региону за год.
Технология подведения промежуточных итогов по регионам заключается в следующем.
1) Отсортируйте список по полю, по которому требуется подвести промежуточный итог, в нашем примере — это поле Регион.
2) Выделите любую ячейку списка.
3) Выполните команды: Данные → Итоги.
4) В диалоговом окне Промежуточные итоги в поле При каждом изменении в выберите имя поля, по которому образованы группы данных и для которого необходимо подвести итог, в нашем примере таким полем будет Регион. В поле окна Операция выберите итоговую функцию, в нашем случае такой операцией будет Сумма. В поле Добавить итоги по пометьте флажком те поля, по которым требуется подвести итоги, в данном случае таким полем является Объем продаж. Установите флажки в нижней части диалогового окна, если это необходимо.
5) Для закрытия диалогового окна щелкните по кнопке ОК. Результаты подведения итогов представлены на рис. 6.3.
Для облегчения анализа выполнения операции Промежуточные итоги числовые поля с итоговыми сведениями на рисунке снабжены примечаниями, содержащими сгенерированные формулы. По Краснодарскому региону текущие сведения раскрыты. По другим регионам — закрыты.
- Для их открытия выполните щелчок мышью по значку +, находящемуся левее итоговой строки; для закрытия - щелчок по значку «-» скроет соответствующие текущие сведения.
- Маленькие кнопочки «1», «2» и «3», представленные в левом верхнем углу после подведения промежуточных итогов (рис. 6.3), позволяют выводить на экран различные уровни детализации данных.
Рис. 6.3.Фрагмент окна Excel с итогами объема продаж по регионам
Сводные таблицы
Одним из мощных инструментов анализа и обработки данных являются сводные таблицы, с помощью которых можно быстро и легко обобщить большие объемы данных, представляя их в разных разрезах.
Сводная таблица - отчет, генерируемый на основе других существующих таблиц, списков или внешней базы данных. При этом исходная информация может находиться как на рабочем листе, так и во внешнем файле.
Отчеты сводных таблиц допускают различные варианты форматирования, сортировки, фильтрации, группировки данных, получение различных итогов с любой желаемой степенью детализации. Пользователь может легко управлять столбцами и строками, перемещая их из одной области в другую, фильтровать значения в области данных, что позволяет оперативно анализировать данные в различных разрезах.
Рассмотрим создание сводной таблицы на примере анализа учета кредитных договоров.
- Создайте таблицу с данными о заключенных кредитных договорах юридических и физических лиц по нескольким филиалам банка аналогично рис. 6.4.
Рис. 6.4.Фрагмент таблицы кредитных договоров банка с юридическими и физическими лицами
Для нашего примера применение сводных таблиц позволит получить ответы на такие вопросы, как:
- сколько заключено договоров в каждом филиале банка, и какова доля каждого филиала в общем объеме;
- какова общая сумма кредитования юридических и физических лиц в каждом филиале;
- в каком филиале было заключено наибольшее количество договоров по каждому типу и в какой валюте;
- каково распределение кредитных договоров по срокам;
- какой отрасли выдается большее количество кредитов в каждом из филиалов?
Создание сводной таблицы осуществляется выбором команд меню:
Данные → Сводная таблица...
Шаг 1. Задание источника данных
- На данном этапе (рис. 6.5) необходимо выбрать, что является источником данных для сводной таблицы.
Рис. 6.5.Диалоговое окно Шага 1
Мастера сводных таблиц и диаграмм
- Установите вид создаваемого отчета: сводная таблица.
Выполним щелчок левой кнопкой мыши по кнопке окна Далее.
Шаг 2. Определение интервала исходных данных
В этом диалоге (6.6) необходимо указать диапазон исходных данных, предназначенный для построения сводной таблицы.
- Укажите интервал, выделив диапазон данных, воспользовавшись трехцветной кнопкой.
Параметры... |
Рис. 6.6Диалоговое окно шага 2
Мастера сводных таблиц и диаграмм
Вид окна Мастера сводных таблиц и диаграмм на шаге 2 напрямую зависит от источника данных, который был выбран на первом шаге. Если предполагается формировать сводную таблицу из внешних источников данных (в том числе созданных не на основе таблиц Excel, возможно, размещенных на удаленных компьютерах сети), то окно шага 2 имеет вид, представленный на рис. 6.7.
Рис. 6.7.Диалоговое окно шага 2 Мастера сводных таблиц и диаграмм
при указании на шаге 1 внешнего источника данных
Используя кнопку указанного окна Получить данные, в новом окне (рис. 6.8) можно указать в качестве источника Базы данных, Запросы, Кубы ОLAP.Используя кнопку Обзор, можно найти требуемые источники, а с помощью кнопки ОК можно выбрать искомые файлы, следуя появляющимся инструкциям.
Если для создания сводной таблицы использовались данные, находящиеся на нескольких листах рабочей книги, то второй диалог Мастера сводных таблиц и диаграмм разбивается на два подэтапа.
Сначала устанавливается переключатель, определяющий, как следует создавать поля страницы (рис. 6.9). Если переключатель находится в верхнем положении, это означает создание только одного поля страницы для отчета сводной таблицы. Выбор нижнего переключателя назначает диапазонам разные имена.
Рис. 6.9Диалоговое окно шага 2а
Мастера сводных таблиц и диаграмм при указании
на шаге 1 нескольких диапазонов консолидации
Кнопка Далее вызывает окно, представленное на рис. 6.10. Используя возможности этого окна, можно найти и добавить к обработке требуемые диапазоны ячеек, размещенные на одном и том же или разных листах рабочей книги. Для этого в поле Диапазон первоначально заносится требуемый интервал ячеек из любого листа рабочей книги. Затем с помощью кнопки Добавить отмеченный интервал перемещается в список диапазонов нижней части диалога мастера. Таким образом, повторяя операции, в сводную таблицу добавляются все необходимые диапазоны.
Рис. 6.10 Диалоговое окно шага 26
Мастера сводных таблиц и диаграмм при указании
на шаге 1 нескольких диапазонов консолидации
Если сводная таблица строится на базе другой сводной таблицы или сводной диаграммы, размещенных в текущей книге, окно шага 2 имеет вид, представленный на рис. 6.11. В данном окне необходимо выделить требуемый лист.
Рис. 6.11 Диалоговое окно шага 2
Мастера сводных таблиц и диаграмм при выборе данных
на шаге 1 из других сводных таблиц
Дальнейшая работа по созданию сводной таблицы, после выбора исходных данных, вне зависимости от вариантов их размещения, требует перехода к шагу 3 Мастера сводных таблиц и диаграмм, что можно сделать, выбрав кнопку Далее. Нажимать кнопку Готово нецелесообразно, так как построение сводной таблицы еще не завершено.