Лабораторная работа 8. Анализ и обобщение данных в электронных таблицах Excel.

Цель работы:

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

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

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

Для того чтобы обобщить и проанализировать данные можно воспользоваться встроенным в MS Excel 2010 средством «Сводная таблица». Для создания Сводной таблицы на вкладке Вставка в группе Таблицы нажмите кнопку Сводная таблица (Рис.8.1.)

Рисунок 8.1. - Создание сводной таблицы

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

Рисунок 8.2. - Выбор диапазона данных для создания сводной таблицы

Теперь у вас откроется новый лист, в котором и будут формироваться отчеты Сводной таблицы. В левой части листа формируется сам отчет, а в правой список полей Сводной таблицы и параметры формирования отчета. В списке полей (Рис.8.3.) выберите те, по которым вам нужен отчет, а в нижней части мышкой поместите поля в нужные области в нужном порядке (в начале можно проиграться с полями, перетаскивая их в разные области и следя за изменением отчета в левой части экрана (Рис.8.4)).

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

В данном примере сформируется отчет с суммой продаж по городам и по товарам в каждом городе (Рис.8.4.).

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

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

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

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

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

• Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.

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

• Убедитесь, что подписи столбцов или строк, которые требуется объе-динить, совпадают с учетом регистра букв. Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таб-лице консолидации.

• Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена нажмите кнопку Имя диапазона и введите имя диапазона в поле Имя.

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

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

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

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

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

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

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

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