4. Сводные таблицы

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

Результатом работы команды Данные/Сводная таблица станет структура, представленная на рис. 1.11. Кнопка в поле «ВМП» позволяет менять данные в зависимости от типа выключателя. Кнопки в полях «ток кз» или «ток откл» позволяют выбирать как все, так и конкретные данные по каждому из упомянутых полей.

Рис. 1.11. Сводная таблица

Рис. 1.12. Макет сводной таблицы

П ри создании сводных таблиц необходимо помнить, что они являются, по существу, лишь табличным отражением трехмерных функцийf(x,y,z). Мастер сводных таблиц посредством макета (рис. 1.12) помогает структурно распределить параметры (в приведенном примере строка – категория «ток кз», столбец – «ном. ток»). Значения функции помещаются в поле «Данные». Один из параметров («выключатель») помещается в поле «Страница».

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

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

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

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

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

Консолидировать данные в Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

Предусмотрены два способа консолидации данных:

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

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

Примечания

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

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

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