Л а б ораторная работа 9. А н а л из и обобщение данных

 

в э л е к т ронных таблицах Excel.

 

 

Цель работы:

 

1. Обобщение и анализ данных с использованием Сводных таблиц.

 

2. Выполнение консолидации данных.

 

3. Освоение операции автоматического подведения итогов.

 

Для того чтобы обобщить и проанализировать данные можно вос-

 

пользоваться встроенным в MS Excel 2007 средством «Сводная таблица».

 

Для создания Сводной таблицы на вкладке В ставка в группе Т аблицы

 

нажмите кнопку Сводная т а блица (Рис.9.1.)

 

 

Рис. 9.1.Создание сводной таблицы.

 

Далее в появившемся окне в поле В ы б рать т аблицу и л и д и а п азон

 

автоматически выберется вся ваша таблица. Если нужно выбрать только

 

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

 

мышкой выделите нужную часть таблицы. Обратите особое внимание на

 

то, что верхняя строка выделенного диапазона обязательно должна содер-

 

жать названия столбцов, т.к. она не будет обрабатываться как данные, а

 

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

 

 

115

 

 

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

 

в конце строки ввода еще раз. Далее нажмите Ok (Рис.9.2).

 

Рис.9.2.`Выбор диапазона данных для создания сводной таблицы.

 

Теперь у вас откроется новый лист, в котором и будут формироваться от-

 

четы Сводной таблицы. В левой части листа формируется сам отчет, а в

 

правой список полей Сводной таблицы и параметры формирования отчета.

 

В списке полей (Рис.9.3.) выберите те, по которым вам нужен отчет, а в

 

нижней части мышкой поместите поля в нужные области в нужном поряд-

 

ке (в начале можно проиграться с полями, перетаскивая их в разные облас-

 

ти и следя за изменением отчета в левой части экрана (Рис.9.4)).

 

 

116

 

Рис.9.3 Список полей Сводной таблицы и параметры формирования

 

отчета.

 

В данном примере сформируется отчет с суммой продаж по городам

 

и по товарам в каждом городе (Рис.9.4.).

 

117

 

Рис.9.4. Сводный отчет с суммой продаж по городам и по товарам в каж-

 

дом городе.

 

Консолидация данных представляет собой полезный способ объеди-

 

нения данных из нескольких источников в одном отчете. Такой отчет мо-

 

жет содержать итоговые и средние данные о продажах, текущих уровнях

 

материальных запасов и наиболее популярных продуктах в рамках всей

 

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

 

группе Р абота с данными на вкладке Д анные.

 

Чтобы свести и включить в отчет значения, находящиеся в диапазонах

 

на разных рабочих листах, можно выполнить консолидацию данных из ка-

 

ждого отдельного рабочего листа в сводный отчет на главном рабочем

 

листе. Для этого:

 

1. Организуйте консолидируемые данные на каждом отдельном листе:

 

Необходимо, чтобы все диапазоны данных были представлены в фор-

 

мате списка (первая строка каждого столбца содержит название, ос-

 

тальные строки однотипные данные; пустые строки или столбцы в

 

списке отсутствуют).

 

Разместите каждый диапазон на отдельном листе. Не помещайте диа-

 

пазоны на лист, на котором должна выполняться консолидация. 118

 

 

Убедитесь, что макеты всех диапазонов совпадают.

 

Убедитесь, что подписи столбцов или строк, которые требуется объе-

 

динить, совпадают с учетом регистра букв. Например, заголовки «Ср.

 

за год» и «Средний за год» различаются и не будут объединены в таб-

 

лице консолидации.

 

Присвойте каждому диапазону имя: выделите диапазон ячеек и на

 

вкладке Ф ормулы в группе О пр еделенные и м е н а нажмите кнопку

 

Имя д и апазона и введите имя диапазона в поле Имя.

 

2. Щелкните на основном листе левый верхний угол области, в которой

 

требуется разместить консолидированные данные.

 

3. На вкладке Данные в группе Средства о б работки д анных выберите

 

команду К онсолидация.

 

4. Выберите в раскрывающемся списке Ф ункция итоговую функцию, ко-

 

торую требуется использовать для консолидации данных.

 

5. Введите имя, назначенное диапазону, и нажмите кнопку Добавить. По-

 

вторите этот шаг для всех диапазонов.

 

6. Выберите способ обновления консолидации:

 

чтобы консолидация обновлялась автоматически при изменении ис-

 

ходных данных, установите флажок С оздавать с в я з и с и с ходными

 

д анными;

 

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

 

путем изменения включаемых в нее ячеек и диапазонов, снимите

 

флажок Создавать с в я зи с и с ходными д анными.

 

7. Установите флажки в группе Использовать в к ачестве и м е н, указы-

 

вающие где в исходных диапазонах находятся названия: либо п о дп и с и

 

в ерхней с тр оки, либо з н ачения л е в ого столбца, либо оба флажка одно-

 

временно.