3.3 Итоги

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

Операция: Определяет вид "итоговой" функции. Итоги выводятся под столбцом, который был выбран в разделе Добавить итоги по.

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

Сумма - сумма элементов. Используется по умолчанию для подведения итогов по числовым полям.

Кол-во значений - число строк данных. Используется по умолчанию для нечисловых полей.

Среднее - среднее арифметическое элементов.

Максимум - максимальное числовое значение в группе.

Минимум - минимальное числовое значение в группе.

Произведение - произведение всех значений в группе.

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

Смещенная/несмещенная дисперсия - смещенная/несмещенная оценка дисперсии по выборке.

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

Заменять текущие итоги. Заменяет все полученные ранее итоги новыми. Если убрать этот флажок, то новые итоги будут вставлены под получен­ными ранее, а не заместят их. Это позволяет, при необходимости, выпол­нить процедуру Итоги несколько раз. Так, если нам нужно вычислить не только суммы продаж по каждому из продавцов, но и число самих про­даж, следует в окошке Операция указать функцию Кол-во значений.

Практическая работа.

1 . Найти суммарную выручку каждого из работников организации. Для этого таблицу следует предварительно отсортировать по колонке Имя. Затем с помощью меню Данные+Итоги нужно вызвать окно Промежуточные итоги, где в поле (При каждом изменении в) группировки задать имя этого поля (в данном случае поля Имя). В поле Операция задать обобщающую функцию Сумма, а в поле Добавить итоги по выбрать поле выручка (рис. 16).

2. Найти итоги продаж по видам товаров. Здесь сна­чала следует отсортировать данные по полю Товар, а затем определить ито­ги по полю Товар с Суммой по полю Выручка. Сформировать внутренний итог по полю Товар и Суммой по полю Вы­ручка. Флаг Заменить текущие итоги должен быть сброшен.

3.4 Консолидация

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

Практическая работа.

1 . Сначала следует установить курсор на свободное место, так, чтобы снизу и справа было незанятое пространство для формирования консолиди­рующей таблицы. В поле функция выбрать одну из доступных обобщающих функций. Чаще всего это функция Сумма. Затем в разделе Ссылка следует задать блок, для которого выполняется анализ - А1:D14 (рис.17). После этого следует нажать кнопку Добавить - ко­ординаты выделенного блока переместятся в окошко Список диапазонов. При необходимости объединить несколько таблиц эти действия следует повторить столько раз, сколько имеется таблиц. В случае потребности изъять какие-то блоки используется кнопка Удалить. Флаги в разделе Использовать в качестве имен определяют, указы­вать или нет в консолидирующей таблице названия колонок (флаг Подписи верхней строки) и значений ключевого поля (флаг Значения левого столбца). Флаг Создавать связи с исходными данными используется для установления связи с таблицами-источниками, находящимися на других лис­тах/книгах. В этом случае изменение данных в них повлечет автоматическое изменение в консолидирующей таблице. Итоги консолидации тогда должны формироваться на отдельном листе.

Видим, что каждый сотрудник представлен только одной строкой, где в колонке Выручка отображена сумма всех его продаж. Колонка Товар не содержит никаких данных, поскольку это текстовое поле, но зато колонка Дата содержит некоторые бессмысленные даты. Это сумма всех дат продаж в формате Дата, ведь даты в Excel – это числа и они могут суммироваться. В виду этого эти колонки в последствии нужно удалить или ранее в разделе Ссылка не указывать эти колонки.

2. Нас интересует выручка по номенклатуре товаров. Для этого следует сделать так, чтобы самой левой колонкой была колонка Товар и попутно не включим колонку Дата.

3. В очередном задании выполнить консолидацию нескольких (трех) таблиц на разных листах по видам товаров.