Тверской государственный университет
Опубликован: 13.09.2006 | Доступ: свободный | Студентов: 5132 / 387 | Оценка: 4.23 / 3.83 | Длительность: 28:12:00
Специальности: Программист, Менеджер
Лекция 8:

Анализ деятельности офиса

В данной главе я рассмотрю некоторые средства Office 2000, прежде всего средства Excel, применяемые при анализе деятельности офиса. Ни одно серьезное решение в офисе не принимается, как правило, без анализа его деятельности за некоторый период, предшествующий моменту принятия решения. Я уже достаточно подробно говорил, например, при рассмотрении объектов ADO, о том, как важно для принятия решения располагать полной информацией, поступающей из различных источников - баз данных, таблиц Excel, интернет. Без знания прошлого, нельзя эффективно спланировать будущую деятельность, - "прошлое позволяет предвидеть будущее".

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

  • Access при работе с базами данных;
  • Outlook при работе с текущей оперативной информацией, - встречами, совещаниями, задачами;
  • Power Point при подведении итогов и проведении презентаций;
  • Электронная приборная панель (Digital Dashboard), которую можно встроить в Outlook, ориентированная на принятие решений, когда вся нужная и уже обработанная информация находится на единой "панели приборов";
  • Специальное приложение Project 2000, предназначенное для управления проектами и принятия решений в процессе работы с проектами.

И, все-таки, "главным" приложением, используемым при проведении анализа деятельности офиса, является Excel. Анализ многомерных данных, построение прогнозов, анализ типа "Что если", графическое отображение результатов анализа - все это и многое другое выполняется в Excel.

Рассмотрим ряд инструментов Excel, которые принято относить к средствам, используемым при проведении анализа:

  • сводные таблицы;
  • таблицы подстановки,
  • сценарии,
  • средства оптимизации;
  • средства прогнозирования.

Кубы OLAP, сводные таблицы и анализ данных

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

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

Структура сводной таблицы

Сводную таблицу можно рассматривать как таблицу с тремя измерениями, в каждой точке которой заданы данные. Четыре оси сводной таблицы носят названия:

  • Оси Строк;
  • Оси Столбцов;
  • Оси Страниц, называемой также осью Фильтров;
  • Оси Данных.

Если бы на каждой оси располагались значения одного типа, то эта модель была бы совершенно простой и понятной. Можно было бы рассматривать сводную таблицу, как функцию трех переменных - F(x,y,z), заданную таблицей. Вся сложность сводной таблицы состоит в том, что на каждой оси может располагаться несколько полей, это же верно и относительно данных, - полей данных может быть также несколько. Поэтому, каждая из координат сводной таблицы, также как и значение функции F, представляет собой агрегат довольно сложной структуры. Поля, располагаемые на той или иной оси, получают тип этой оси - поля строк, поля столбцов, поля фильтра, поля данных.

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

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

Чтобы пояснить ситуацию со структурой сводной таблицы, приведу простой пример, когда на каждой оси располагается ровно одно поле. Рассмотрим организацию, занимающуюся продажами. При построении сводной таблицы на оси страниц (фильтров) расположим поле "Отделы", на оси строк - поле "Сотрудники", на оси столбцов - поле "Месяцы". Единственное поле данных "Продажи" будет задавать объем продаж. Тогда, если выбрать соответствующий отдел, или, другими словами, включить фильтр по отделам, строки таблицы будут задавать имена сотрудников выбранного отдела, столбцы будут задавать месяцы, а значения на пересечении строки и столбца будут определять объем продаж, совершенных данным сотрудником данного отдела в данном месяце. Для этой таблицы достаточно естественно ввести группирование данных, как по строкам, так и по столбцам. Например, сотрудников отделов можно сгруппировать по лабораториям, а месяцы - по кварталам. Тогда легко получать представление данных с разной степенью детал изации, например, сравнить данные о продажах отдельных лабораторий за определенный квартал. Реальные сводные таблицы бывают значительно более сложными, хотя бы по той причине, что на каждой оси располагаются несколько различных полей, а уровней группирования данных может быть более двух.

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

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

Чаще всего в роли источника данных для построения сводной таблицы выступают базы данных. Это могут быть табличные (реляционные) базы данных, например, Access или Microsoft SQL Server, В последнее время в качестве источников данных стали широко применяться кубы OLAP. Между кубами OLAP и сводными таблицами много общего. Эти объекты, в какой-то мере, близнецы - братья. Кубы OLAP служат для хранения многомерных данных, а сводные таблицы для проведения анализа этих данных.

Многомерные (OLAP) источники данных

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

Сам термин OLAP означает O n- L ine A nalytical P rocessing и отражает тот факт, что сервер, хранящий базы данных OLAP, выполняет определенную аналитическую обработку. Часто в термин OLAP вкладывают и другой смысл, отражающий многомерность структуры хранимых данных. Так что, когда говорят о многомерных хранилищах данных, неявно предполагают, что речь идет о базах данных OLAP. Первичной структурой в этих базах является многомерный куб - гиперкуб. Оси этого куба, как и положено, называются измерениями. С каждой точкой в пространстве этого куба связаны данные. Важной особенностью OLAP-куба является то, что на каждом измерении можно задать иерархию, определяющую способ группирования или классификации элементов, принадлежащих данному измерению. Например, одним из измерений куба, хранящего данные о продажах, может быть измерение "Заказчики", на котором естественным образом можно задать иерархию, определяющую географическое распределение заказчиков по континентам, страна м, регионам, городам. На одном измерении можно задать несколько иерархий. Например, тех же заказчиков можно сгруппировать по профессиональным признакам. Кубы OLAP идеально приспособлены для проведения анализа "в глубину". Так пользователь вначале может проанализировать объем заказов по странам. Заметьте, суммарное число заказчиков может быть очень большим и измеряться десятками тысяч, в то же время число стран будет измеряться единицами, так что объем передаваемой информации будет малым. При необходимости пользователь может для той или иной страны проанализировать распределение по регионам, для некоторых выбранных регионов - по городам, так можно дойти и до конкретного заказчика и проанализировать сделанные им заказы. Внутри куба OLAP можно производить и итоговые вычисления, применяя, например, функции суммирования данных, вычисления среднего, нахождения максимума и другие.

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

Построение сводной таблицы вручную

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

Программная реализация предполагает знакомство с соответствующими классами объектов. Сама таблица и все ее части являются объектами, классы которых содержат большое количество свойств и методов. Об объектной модели сводной таблицы поговорим чуть позже, а сейчас рассмотрим на конкретном примере построение таблицы с использованием Мастера сводных таблиц. В примере я использую в качестве источника данных уже созданную базу данных офиса "Родная Речь", с которой шла работа в предыдущей главе. Замечу, что эта база данных в офисе используется уже давно, и потому там хранятся сведения о продажах за несколько лет.

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

  • Как шли продажи в стоимостном и количественном исчислении за те или иные периоды времени?
  • Какие книги продавались наиболее успешно?
  • Кто из сотрудников офиса оформлял наибольшее число заказов?
  • С кем из заказчиков шла наиболее успешная работа?
  • С какими городами шло наиболее успешное сотрудничество?

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

Итак, цель ясна - приступим?

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

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

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

Заметьте, из четырех возможностей задания разных типов источников данных - списков Excel, внешних источников, нескольких диапазонов, другой сводной таблицы - я выбрал внешний источник данных, поскольку, как я уже говорил, буду строить сводную таблицу, используя базу данных Access. Вторая группа переключателей позволяет задать желаемый вид отчета - сводную таблицу или сводную диаграмму, построенную на основе сводной таблицы. Пример с диаграммой приведем чуть позже, а сейчас займемся чисто сводными таблицами. Сделав выбор, остается нажать кнопку "Далее", что заставляет Мастера сделать очередной шаг. Вот окно, открываемое на втором шаге:

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

Рис. 8.2. Окно второго шага Мастера сводных таблиц
Ольга Гафарова
Ольга Гафарова

Добрый день. Подскажите формулы при решении задачи на рис. 2.2 в лекции №2. Закон Ома, какие должны использоваться формулы для I и R

Курс: Основы офисного программирования и документы Excel

Серегй Лушников
Серегй Лушников