7

Тема: технология обработки эи с помощью консолидированных таблиц

1. Понятие Консолидации данных. Способы консолидации.

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

В консолидации используются следующие понятия:

Область-источник – это область, в которой размещены исходные данные. Например: диапазон ячеек, содержащий данные на листе Январь, на листе Февраль и т.д. Области-источники могут находиться на разных листах РК или в разных рабочих книгах. В консолидации могут участвовать до 255 областей-источников, а сами области могут быть закрыты.

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

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

На каждом рабочем листе в каждый момент времени может иметься только один набор консолидируемых данных.

Существует несколько видов консолидации:

• Консолидация с помощью трехмерных ссылок. Это подсчет итоговых значений с помощью формул так, как мы это делали при создании итоговой ведомости за квартал. Мы складывали данные трех исходных листов.

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

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

2. Задания для выполнения

2.1. Создание многостраничного документа «ведомость учета норм выработки рабочими-сдельщиками за 1-й квартал

Ведомость учета норм выработки рабочими-сдельщиками
за январь
Цех	Табельный номер	Фамилия	Нормо-часы	Фактически отработанные часы	Выполнение норм выработки (%)
1	103		50,0	52,0
1	102
1	103
2	201
2	202
2	203
3	301
3	302
3	301
3	304

Выполнение норм выработки = Фактически отработанные часы / Нормо-часы (установить процентный формат)

1. Создать новую трехстраничную рабочую книгу. Сохранить на сетевом диске в папке Excel Назвать файл «Фамилия-консолидация».

2. Назвать листы согласно временному периоду – Январь, Февраль, Март.

3. Заполнить ведомости исходными данными. - Количество записей должно быть разным в каждой ведомости: 10 записей на первом листе, 15 на втором, 20 – на третьем. - Числовые данные в трех ведомостях не должны совпадать. - В полях, содержащих категории, должны быть повторения. В рассматриваемом примере это поля «Цех» и «Табельный номер». Использовать 3 номера цеха. В каждом цехе по 4 работника. Один табельный номер может повторяться в течение одного месяца или не встречаться в каком-либо месяце.

4. Выполнить форматирование ведомостей:

• Выполнить выравнивание названия ведомости по центру интервала, который занимает ведомость; отцентрировать названия граф ведомости;

• Для названия ведомости и названий граф ведомости установить полужирный шрифт;

• Выполнить выравнивание данных в соответствии с их форматом;

• Подобрать нужную ширину граф ведомости;

• Обвести таблицы.