Анализ данных MS Excel. Консолидация данных. Сводные таблицы

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

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

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

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

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

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

Создание формулы, выполняющей консолидацию данных

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

2. Вызовите функцию для выполнения итоговых вычислений, воспользовавшись кнопкой Autosum (Автосумма) или строкой формул.

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

Консолидация данных по положению

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

2. С помощью команды Данные > Консолидация откройте диалоговое окно Консолидация.

3. Выберите в списке Функция функцию, предназначенную для выполнения консолидации.

4. Задайте в поле Ссылка ссылку на первый диапазон и нажмите кнопку Добавить.

5. Повторяйте действия, указанные в пункте 4, пока не выберете все исходные данные.

6. Установите флажок Создавать связи с исходными данными, если необходимо связать исходные данные с результатами консолидации.

7. Нажмите кнопку ОК, и итоговые значения появятся в заданном диапазоне.

Консолидация данных по заголовкам строк и столбцов

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

2. Вызовите команду Данные > Консолидация, для того чтобы открыть диалоговое окно Консолидация.

3. Выберите в списке Функция функцию, используемую при выполнении консолидации.

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

5. Повторяйте действия, указанные в пункте 4, пока не выберете все исходные данные.

6. Установите флажок Подписи верхней строки или Значения левого столбца либо оба флажка, в зависимости от того, где расположены названия исходных данных.

7. Установите флажок Создавать связи с исходными данными, если необходимо связать исходные данные с результатами консолидации.

8. Нажмите кнопку ОК, и итоговые данные вместе с названиями строк или столбцов появятся в заданном диапазоне.

Сводные таблицы и диаграммы

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

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

2. Выберите в нем источник данных и укажите, что будет создавать сводная таблица. Щелкните на кнопке Далее.

3. Во втором диалоговом окне мастера при необходимости выберите диапазон ячеек для исходных данных. Щёлкните на кнопке Далее.

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

5. Щелкните на кнопке Макет для открытия одноименного диалогового окна. Перетащите кнопки полей в области макета таблицы. Вы должны разместить хотя бы по одному полю в областях Строка, Столбец и Данные. Заполнять область Страница не обязательно. Нажмите кнопку ОК (Готово), а затем — кнопку Готово. После этого сводная таблица с заданными параметрами появится на рабочем листе.

ИЛИ

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