Вычисление итогов, создание сводной таблицы и диаграммы в Excel

Вычисление итогов.

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

В Excel для решения подобных задач предназначена функция автоматического вычисления итогов, которая вызывается командой Итоги меню Данные. Для применения этой функции данные должны быть представлены в форме списка. Поскольку указанная функция вычисляет итоги только для заданной группы данных, предварительно следует выполнить сортировку данных в таблице. В нашем примере необходимо определить суммы оборота для каждого продавца. Эту задачу можно решить и с помощью применения обычных формул, однако использование функции автоматического вычисления итогов позволяет значительно упростить её выполнение.

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

Переместим указатель ячейки в любую ячейку внутри таблицы и активизируем команду Сортировка меню Данные. Сортируемый диапазон будет автоматически выделен, и пользователь получит возможность указать в диалоговом окне критерии сортировки.

После нажатия кнопки ОК в диалоговом окне Сортировка диапазона данные таблицы будут отсортированы, (в столбце Продавец таблицы фамилии продавцов разместятся в алфавитном порядке). Тем самым мы выполнили подготовительные действия для выполнения функции автоматического вычисления итогов.

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

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

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

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

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

Возможно так же комбинировать несколько итогов (применение вложенных итогов).

Дополните таблицу ещё одним показателем: количество автомобилей проданных конкретным продавцом. Для этого следует повторно открыть с помощью команды Итоги меню Данные диалоговое окно Промежуточные итоги. В поле При каждом Изменении в уже представлен элемент Продавец. Чтобы определить количество проданных автомобилей для каждого продавца, выберите в списке Операция элемент Кол-во значений. В качестве основы для подведения итогов в последнем поле может быть указан любой столбец, поскольку данные уже отсортированы по группам и необходимо всего лишь определить количество строк в группе. Выделите, например, в списке Добавить итоги по элемент Марка. Для того чтобы итоги по обоим критериям были представлены в таблице, перед нажатием кнопки ОК следует отменить опцию Заменить текущие итоги.