Шаг 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 Мастера сводных таблиц и диаграмм, что можно сделать, выбрав кнопку Далее. Нажимать кнопку Готово нецелесооб­разно, так как построение сводной таблицы еще не завершено.