Подведение итогов 1 Итоги l Использование функции

Подведение итогов 1

Итоги l Использование функции Промежуточные итоги из меню Данные позволяет выполнить детальный анализ сводных показателей для групп данных, объединенных каким-либо общим признаком. l Чтобы формирование итогов имело смысл, необходимо предварительно отсортировать исходные данные по желаемому признаку. ФИО Товар Выручка Дата ИВАН стол 20 25. 1 ИВАН стул 80 12. 4 ИВАН шкаф 60 25. 3 ОЛЕГ стол 50 25. 2 ОЛЕГ стул 40 21. 3 ОЛЕГ шкаф 30 10. 2 ОЛЕГ шкаф 20 16. 4 ПЕТР стол 100 2. 3 ПЕТР стул 10 1. 1 ПЕТР стул 20 12. 2 2

Данные-Итоги l В окне Промежуточные итоги в поле (При каждом изменении) задать имя этого поля (в данном случае Фамилия). l В поле Операция задать функцию выполнения (в данном случае Сумма). l В поле Добавить итоги по… (выбрать Выручка), затем нажать ОК. результаты показаны в таблице. 3

Вид окна с Итоговой функцией l Поля и переключатели в окне Промежуточные итоги имеют следующий смысл. l Операция: определяет вид «итоговой» функции. l Итоги выводятся под столбцом, который был выбран в разделе Добавить итоги по. . 4

Выбор функций Имеется возможность выбора следующих функций: l Сумма – сумма элементов, используется по умолчанию для подведения итогов по числовым полям. l Количество значений – число строк данных, используется по умолчанию для нечисловых полей. l Среднее – среднее арифметическое элементов. l Максимум – максимальное числовое значение в группе. l Минимум - минимальное числовое значение в группе. l Произведение – произведение всех значений в группе. l Кол-во чисел – кол-во строк, содержащих числовые данные. l И другие 5

Добавить итоги по… l Добавить итоги по… - определяет столбец, в котором должны появиться итоги. Можно указать более, чем один столбец. 6

Подписи в нижних строках l Заменять текущие итоги – заменяет все полученные ранее итоги новыми. Е сли убрать флажок, то новые будут вставлены под полученными ранее, а не заместят их. Это позволяет выполнить процедуру Итоги несколько раз. l Конец страницы между группами – автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Это обеспечивает печать каждой группы на новой странице. l Итоги под данными – помещает строки итогов и общих итогов под соответствующими данными. В противном случае они будут размещены вверху над данными. l Убрать все – удаляет все итоги из таблицы 7

Карта Структуры l Дальнейшую возможность обобщения предоставляет появившаяся слева «карта» структуры данных с кнопками свертки + и —. 8

Консолидация данных Другим способом получения итоговой информации является консолидация данных которая выполняется в соответствии с выбранной функцией обработки. Консолидация — агрегирование (объединение) данных, представленных в исходных областях-источниках. Инструмент Консолидация позволяет объединить таблицы, находящиеся в разных местах и, даже, в разных книгах. Все таблицы должны иметь абсолютно идентичную структуру. Соединение не является механическим. Итоговая таблица будет содержать только одну строку с ключевым полем, а числовые данные в ней будут суммами (или другими функциями) всех строк объединенного подмножества. Ключевым полем считается самое левое поле (колонка) таблицы или ее выделенная часть. Для выполнения консолидации предварительная сортировка не нужна. 9

Области консолидации Консолидированная данных Представление о консолидации таблица Представление о консолидации данных 10

Варианты консолидации данных: 1. с помощью формул, где используются ссылки; 2. по расположению данных для одинаково организованных областейисточников (фиксированное расположение); 3. по категориям для различающихся по своей структуре области данных; 4. консолидация внешних данных. 5. с помощью сводной таблицы; 11

1. Консолидация с помощью формул При консолидации данных с помощью формул используемые в них ссылки могут иметь разное представление в зависимости от взаимного расположения областей-источника и областиназначения: • все области на одном листе — в ссылках указывается адрес блока ячеек, Например, D 1: C 8; • области на разных листах — в ссылках указывается название листа, диапазон, Например, лист1!D 1 : лист2!С 8; • области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон, 12 например, [книга 1]лист1!D 1: [книга 2] лист2!С 8.

2. Консолидация по расположению l При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). l Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т. п. ) по их расположению. l Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные, Консолидация, выбирается вариант и задаются условия консолидации. 13

Пример l На отдельных листах рабочей книги по каждой учебной группе хранятся сведения о среднем балле по фиксированному перечню предметов в разрезе видов занятий. l Голубым цветом показана консолидируемая область источников.

3. Консолидация по категориям l При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областяхисточниках неодинаково. l Для консолидации данных по категориям используются имена строк и/или столбцов (имена включаются в выделенные области-источники). l Выполняется команда Данные-Консолидация, выбирается вариант и задаются условия консолидации. l Условия консолидации задаются в диалоговом окне Консолидация команды меню Данные 15

Пример l На рабочих листах представлена информация областей-источников в виде структуры. l Число строк — переменное, состав предметов и виды занятий повторяются и могут рассматриваться как имена столбцов для консолидации по категориям. l Синим цветом показана консолидируемая область источников. 16

Окно Функция l В окне «Функция» выбирается функция консолидации данных. Здесь можно выбрать одну из доступных обобщающих функций. l Для каждой области-источника строится ссылка, для этого можно задать ее с клавиатуры «руками» или выделить мышью в области-источника блока ячеек. Затем нажимается кнопка <Добавить>. l Координаты выделенного списка переместятся в окошко «Список диапазонов» . Эти действия следует повторять столько раз, сколько имеется таблиц 17

Флажки… Флажки в разделе «Использовать в качестве имен» определяют, указывать или нет в консолидированной таблице названия колонок. l Флаг Подписи верхней строки) и значений ключевого поля (флаг значения левого столбца) Excel автоматически переносит эти имена в область назначения. 18

4. Консолидация внешних данных l При консолидации внешних данных в диалоговом окне «Консолидация» следует нажать кнопку <Обзор>, в диалоговом окне «Обзор» выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек. 19

Переключатель Создавать связи с исходными данными l Переключатель Создавать связи с исходными данными создает при консолидации связи области назначения к областям-источникам. При изменениях в области назначения автоматически обновляются результаты консолидации. l Внимание! При включенном флажке результат консолидации необходимо строить на отдельном листе или в отдельной книге. 20

Окно Список диапазонов В окне «Список диапазонов» для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники. Ссылки можно модифицировать: l добавить новые области-источники, l удалить существующие области-источники либо изменить их конфигурацию, l Для одного листа итогов консолидации набор ссылок на области-источники постоянен, на нем можно построить несколько видов консолидации с помощью различных функций. Курсор переставляется в новое место, выполняется команда Данные, Консолидация, выбирается другая функция для получения сводной информации. 21