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

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

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

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

На рабочем листе сводная таблица конструируется с помощью команды Данные, Сводная таблица (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.