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

1

Итоги

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

2

Данные-Итоги

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

3

Вид окна с Итоговой функцией

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

4

Выбор функций

Имеется возможность выбора следующих функций:

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

5

Добавить итоги по…

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

6

Подписи в нижних строках

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

7

Карта Структуры

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

8

Консолидация данных

Другим способом получения итоговой информации является консолидация данных которая выполняется в соответствии с выбранной функцией обработки.

Консолидация — агрегирование (объединение) данных, представленных в исходных областях-источниках.

Инструмент Консолидация позволяет объединить таблицы, находящиеся в разных местах и, даже, в разных книгах.

Все таблицы должны иметь абсолютно идентичную структуру. Соединение не является механическим.

Итоговая таблица будет содержать только одну строку с ключевым полем, а числовые данные в ней будут суммами (или другими функциями) всех строк объединенного подмножества.

Ключевым полем считается самое левое поле (колонка) таблицы или ее выделенная часть.

Для выполнения консолидации предварительная сортировка не нужна.

9

Области консолидации

10

​

Представление о консолидации данных

Представление о консолидации данных

Варианты консолидации данных:

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

​

11

1. Консолидация с помощью формул

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

• все области на одном листе — в ссылках указывается адрес блока ячеек,

Например, D1:C8;

• области на разных листах — в ссылках указывается название листа, диапазон,

Например, лист1!D1 :лист2!С8;

• области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон,

например, [книга1]лист1!D1: [книга2] лист2!С8.

12

2. Консолидация по расположению

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

13

Пример

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

​

3. Консолидация по категориям

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

15

Пример

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

16

Окно Функция

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

17

Флажки…

Флажки в разделе «Использовать в качестве имен» определяют, указывать или нет в консолидированной таблице названия колонок.

• Флаг Подписи верхней строки) и значений ключевого поля (флаг значения левого столбца) Excel автоматически переносит эти имена в область назначения.

18

4. Консолидация внешних данных

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

19

Переключатель Создавать связи с исходными данными

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

20

Окно Список диапазонов

В окне «Список диапазонов» для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники.

Ссылки можно модифицировать:

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

21