zanyatieExcel_3

Своднаятаблица

Сводная таблица, Pivot Table (до 2000-го года – сводный отчет) – это пользовательский интерфейс для отображения многомерных данных. С помощью него можно группировать, сортировать, фильтровать и менять расположение данных для получения различных аналитических выборок. Обновление таблицы производится простыми средствами пользовательского интерфейса, данные автоматически агрегируются по заданным правилам, при этом не требуется дополнительный или повторный ввод какой-либо информации. Первый интерфейс сводных таблиц был включен в состав Excel еще в 1993 году (версия Excel 5.0). Интерфейс сводных таблиц Excel является, пожалуй, наиболее мощным средством Excel по работе с базами данных и самым популярным программным продуктом для работы с многомерными данными. Он полезен как для анализа, так и для обобщения информации, хранящейся в базах данных, и поддерживает в качестве источника данных как внешние источники данных (OLAP-кубы и реляционные базы данных), так и внутренние диапазоны электронных таблиц. Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводную диаграмму (Pivot Chart).

Реализованный в Excel интерфейс сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Для простоты можно представлять себе сводную таблицу, как отчет, лежащий сверху диапазона ячеек (на самом деле есть определенная привязка форматов ячеек к полям сводной таблицы). Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы. Эти поля имеют собственные свойства и формат отображения.

Внимание

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

Чаще всего сводная таблица используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, вывод более детальных данных, т.е. управление данными.

По сути, сводные таблицы Excel есть OLAP-кубы. OLAP – On-Line Analytical Processing

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

Термины многомерного анализа данных

Многомерныеданные, измерения

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

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

•виды или группы заболеваний;

•категории пациентов;

•периоды (день, месяц, квартал, год);

•пациенты или группы пациентов;

•проведенные манипуляции;

•и т.п.

Каждый из приведенных критериев в терминах многомерного анализа данных называется «измерением». Можно сказать, что измерение характеризует информацию по определенному набору значений. Специальным типом измерения многомерной информации являются «данные». В нашем примере данными могут являться:

•количество пациентов;

•количество рецептов;

•индивидуальная льгота;

•количество проведенных манипуляций и процедур;

•расход материалов;

•и т.п.

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

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

Упражнение «Создание сводной таблицы»

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

ЖурналПриема2

1. Откройте лист1 книги ЖурналПриема2.

Таблица имеет форму журнала ввода информации. Здесь все измерения данных (столбцы) является равноправными в отличие от БД листов 0509201109092011 книга ЖурналПриема, где дата приема содержится в названии листа, а не поля базы

Подготовка многомерных данных

Правило 1

В исходных данных не должно быть предопределенное (неравноправное) расположение одного из измерений исходных данных

Правило 2

Все недостающие дополнительные характеристики должны быть предварительно добавлены непосредственно в форму журнала ввода

информации/

Применение функций ВПР и НПР позволяет легко дополнить исходные данные недостающими характеристиками.

Также следует заметить, что для последующего анализа в сводных таблицах совершенно безразлично относительное положение строк друг относительно друга (иначе говоря, сортировка). Этими свойствами обладают записи в реляционных базах данных. Именно на анализ баз данных больших объемов ориентирован интерфейс сводных таблиц. Поэтому необходимо придерживаться этих правил и при работе с источником данных в виде диапазонов ячеек. При этом никто не запрещает использовать в работе интерфейсные средства Excel, т.е. форматирование, фильтры, группировки и сортировки исходных ячеек.

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

2.Для выделения базы щелкните в любом месте базы данных, т.е. диапазона ячеек A1:H120.

Важно, чтобы в диапазон ячеек попадал заголовок столбца.

3.В пункте меню Данные щелкните на команде Сводная таблица.

Excel открывает первое диалоговое окно трехшагового Мастера сводных

таблиц и диаграмм

Рисунок 32. Первое диалоговое окно Мастер сводных таблиц и диаграмм.

В первом диалоговом окне Мастер сводных таблиц и диаграмм необходимо установить переключатель в одно из 4-х возможных положений, описывающих местоположение данные для сводной таблицы:

•в списке или базе данных Microsoft (Office) Excel – если данные берутся с одного рабочего листа;

•во внешнем источнике данных – если данные берутся из внешней базы данных. Данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel "понимает" практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет.;

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

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

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

или сводная диаграмма.

4.Установите первый переключатель Создать таблицу на основе данных,

находящихся: в положение в списке или базе данных Microsoft (Office)

Excel.

5.Установите второй переключатель Вид создаваемого отчета в положение

сводная таблица.

6.Нажмите кнопку Далее >.

Открывается второе диалоговое окно Мастера сводных таблиц и диаграмм:

Рисунок 33. Второе диалоговое окно Мастер сводных таблиц и диаграмм

7.В окошке Диапазон: укажите диапазон, на основании которого строится сводная таблица:$A$1:$H$120 (как правило, Excel это уже сделал сам.

8.Нажмите кнопку Далее >.

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

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

Если диапазон ячеек находится на другом листе той же книги или в другой книге, введите имя книги и листа, используя следующий синтаксис:

([имякниги]имялиста!диапазон).

5.Установите переключатель Укажите, куда следует поместить отчет сводной таблицы в положение на новый лист.

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

6. Нажмите кнопку ОК.

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