Рис 6.4

В списке Функция следует выбрать операцию, которая будет выполняться над консолидированными данными. Поскольку предлагаемая программой операция сложения (элемент Сумма) подходит для нашей цели, перейдем к полю ввода Ссылка. Именно в нем следует указать диапазоны ячеек, данные их которых должны быть подвергнуты процессу консолидации. Диапазон ячеек проще всего можно указать, используя мышь. Поместим курсор ввода в поле Ссылка и выполним щелчок мышью на ярлычке листа Киев, затем выделим диапазон ячеек D3:F14 и нажмем в диалоговом окне Консолидация кнопку Добавить. Указанный диапазон ячеек тотчас же будет представлен в поле Список диапазонов. Нажатием кнопки Добавить можно включить в область консолидации, нажатием кнопки Удалить ненужный диапазон может быть из этой области удален (перед нажатием кнопки ненужный диапазон следует выделить в поле Список диапазонов).

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

Рис. 6.5

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

В ячейку A1 введем название таблицы Итоговые данные. Для более наглядного представления данных в столбце F приведем значения долей отдельных товаров в общем объеме продаж. Для этого зададим в ячейке F9 формулу:

= E9/$E$48

и скопируем ее в остальные строки столбца F (вплоть до ячейки F48) предварительно сформатированного процентным стилем.

Рис. 6.7

Необычные адреса ячеек консолидированной таблицы объясняются тем, что при консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа. Структурирование (создание структуры) документа позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы представлены символы структуры. Цифрами обозначены уровни структуры (в нашем примере – 1 и 2). Нажатие кнопки со знаком плюс позволит расшифровать данные высшего уровня структуры. Этого же можно добиться путем исполнения команды Структура/Показать детали меню Данные. На рис. 6.6 показана расшифровка структуры для строк 7, 11 и 15. Скрыть детали можно посредством щелчка на кнопке с изображением минуса или путем выбора команды Структура/Скрыть детали меню Данные.

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

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

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

На рабочем листе сводная таблица конструируется с помощью команды Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report).

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

Рис. 6.8. Отчет о продаже компьютеров сети из трех магазинов

Процесс создания сводной таблицы состоит из нескольких шагов.

Рис. 6.9

Шаг 1.

Выберете команду Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report). Появится первое диалоговое окно мастера сводных таблиц (рис. 6.9). Выбор переключателей, расположенных под заголовком Создать таблицу на основе данных, находящихся: позволяет установить источник данных для сводной таблицы.

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

Шаг 2.

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

Рис. 6.10

поле Диапазон введите ссылку, например А1:Е16, на диапазон, по которому будет строится сводная таблица (рис 6.10). Нажмите кнопку Далее >.

Рис. 6.11

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

Шаг 3.

На новом рабочем листе появляется форма для создания структуры сводной таблицы (рис. 6.12).

Рис. 6.12

Рис. 6.13

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

Выберете поля, которые будут образовывать столбцы СТ и перетащите кнопки, соответствующие этим полям в область Поля столбцов. В данном случае столбцы СТ будут соответствовать месяцам. Поэтому перетащим кнопкуМесяцв областьПоля столбцов.

Выберете поле, по которому будут подводится итоги СТ и перетащите кнопку, соответствующую этому полю в область. В данном случае перетащим кнопку Стоимость в область Данные. В результате получим таблицу, приведенную на рис. 6.13.