Лабораторная работа № 6. Базы данных – 3

Цель работы: осуществление анализа данных с помощью консолидации данных и сводных таблиц.(4 часа.)

Консолидация данных. С помощью консолидации данных можно объединить данные из одной или более областей-источников и выводить их в таблице в области назначения.

Область-источник – это интервалы, содержащие данные, которые нужно консолидировать. Эти области-источники могут находится на одном рабочем листе, на нескольких рабочих листах или даже в разных рабочих книгах.

Область назначения – это интервал, который будет содержать консолидированные данные. Она может находится на том же рабочем листе, что и данные источников или на другом рабочем листе, или даже в другой рабочей книге.

В качестве области-источника будем использовать рабочую книгу Фирма, составленную ранее.

Рис. 6.1

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

Рис. 6.2

A1. Таблицы представлены на рис. 6.2 и 6.3 соответственно.

Рис. 6.3

Перейдем в четвертый рабочий лист и поместим указатель ячейки на крайнюю левую ячейку таблицы, которая будет содержать консолидированные данные – ячейку A3. Поскольку номер и название товара остаются неизменными и не должны быть подвергнуты операции сложения, то можно скопировать эту часть таблицы из рабочего листа, например, Киев. Для этого выделим в рабочем листе Киев диапазон ячеек A3 – B14, выберем команду Копировать менюПравка и перейдем в четвертый рабочий лист (присвоим ему имя Итог), поместим указатель ячейки на ячейкуA3 и выберем команду Вставкаменю Правка. Скопированный фрагмент будет вставлен в новом месте. Для того, чтобы приступить к операции консолидации данных поместим указатель ячейки на ячейкуC3 и выберем в меню Данныекоманду Консолидация. На экране появится диалоговое окно, представленное на рис. 6.4.

Рис 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. Скрыть детали можно посредством щелчка на кнопке с изображением минуса или путем выбора команды Структура/Скрыть деталименю Данные.

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

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