Табличных дел мастер

Original on http://www.brothercake.com/site/resources/scripts/transitions/

Несмотря на богатство и разнообразие возможностей Microsoft Excel, это приложение используется в первую очередь как средство упорядочивания данных. Но при этом не обойтись без определения итоговых значений конкретных полей (столбцов).

Рассмотрим для примера следующую ситуацию. Есть фирма, занимающаяся поставкой принтеров в различные магазины нескольких областей страны. Все необходимые для ее работы данные сведены в одну таблицу.

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

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

Для более простого и быстрого решения поставленной перед нами задачи в табличном редакторе Excel имеется возможность вычисления итогов. Этот инструмент, еще называемый средством резюмирования, позволяет вычислить итоговые значения для записей с одинаковым содержимым одного из полей. Причем для получения конечного результата Excel предлагает целый ряд разнообразных функций, среди которых вычисление суммы, количества, среднего значения, максимума или минимума и многие другие. Так, например, для определения общего количества проданных принтеров для каждой из фирм-покупателей необходимо получить сумму значений поля «Количество» для одинаковых значений поля «Фирма-покупатель».

Этап первый: подготовка

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

Первый шаг в процедуре определения итогов — подготовка исходной таблицы. Она состоит в сортировке данных по тому полю, для значений которого будет вычисляться итоговая функция. В нашем примере таким полем является либо «Фирмапокупатель», либо «Область».

Для сортировки списка выбираем пункт меню Данные Сортировка. В разделе Сортировать по окна Сортировка диапазона указывается поле, по которому следует упорядочить список, а рядом — способ упорядочивания (по возрастанию либо по убыванию). Теперь список готов, и можно применять средства вычисления итогов.

Этап второй: получение итоговых значений

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

После этого в исходную таблицу после каждой группы записей с одинаковыми значениями поля «Фирма-покупатель» будут добавлены строки с полученными результатами, а в конце таблицы появится строка с общим итогом по всем записям.  Различные уровни итогов

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

Кроме управления отображением полученных значений, кнопки + и – дают возможность отобразить либо скрыть результаты вычислений для определенной группы. При необходимости можно определить несколько итогов по различным полям (либо с использованием другой итоговой функции). Для этого нужно повторно выбрать пункт меню Данные Итоги и указать все необходимые параметры. Чтобы полученные значения отображались на рабочем листе одновременно с уже существующими, необходимо отключить пункт Заменить текущие итоги. Обратите внимание на то, что теперь в левой части рабочего листа появилась еще одна кнопка, управляющая режимом отображения данных. Удалить же итоговые значения можно, выбрав Данные Итоги Убрать все.

Итоги — удобное средство формирования отчетов. С их помощью можно упорядочить и проанализировать практически любые массивы данных.