Публикации. Olap кубы в excel Только качественные посты

Проблемы аналитики, OLAP, хранилищ данных вызывают все больший интерес у российских ИТ-специалистов. К настоящему времени в нашей компьютерной печати и Интернете опубликовано немало хороших, академичных по способу изложения материалов по этой тематике, в том числе и вводного характера. Мы же предлагаем вашему вниманию статью, в которой сознательно пытаемся объяснить OLAP “на пальцах”, на конкретном примере. Практика показывает, что такое объяснение необходимо и некоторым ИТ-специалистам и особенно конечным пользователям.

Итак, OLAP *1 в первом приближении, “на пальцах”, можно определить как особый способ анализа данных и получения отчетов. Его суть состоит в предоставлении пользователю многомерной таблицы, автоматически суммирующей данные в различных разрезах и позволяющей интерактивно управлять вычислениями и формой отчета. В этой статье будет рассказано о технологии и основных операциях OLAP на примере анализа счетов-фактур предприятия, занятого оптовой торговлей продуктами питания.

*1. OLAP - On-Line Analytical Processing, оперативный анализ данных.

В качестве инструмента будет рассмотрена OLAP-система самого простого и недорогого класса - OLAP-клиент *1. Для примера выбран наиболее простой продукт из числа OLAP-клиентов - “Контур Стандарт” компании Intersoft Lab. (Для наглядности далее в статье общепринятые термины OLAP будут обозначаться жирным шрифтом и сопровождаться англоязычными аналогами.)

*1. Более подробно о классификации OLAP-систем рассказано в статье "OLAP, сделано в России" в PC Week/RE, №3/2001.

Итак, приступим к работе с системой. Для начала потребуется описать Источник данных (data source) - путь к таблице и ее поля. Это задача пользователя, знающего физическую реализацию базы данных. Для конечных пользователей он переводит название таблицы и ее полей в термины предметной области. За “источником данных” стоит локальная таблица, таблица или представление (view) SQL-сервера или хранимая процедура.

Скорее всего в конкретной базе данных счета-фактуры хранятся не в одной, а в нескольких таблицах. Кроме того, часть полей или записей может не использоваться для анализа. Поэтому далее создается Выборка (result set или query), - в которой настраиваются: алгоритм объединения таблиц по ключевым полям, условия фильтрации и набор возвращаемых полей. Назовем нашу выборку “Счета-фактуры” и поместим в нее все поля источника данных “Счета-фактуры”. Таким образом, ИТ-специалист, создавая семантический слой, скрывает физическую реализацию базы данных от конечного пользователя.

Затем настраивается OLAP-отчет. Этим может заняться специалист в предметной области. Сначала поля плоской выборки данных разбиваются на две группы - факты (facts или measures) и измерения (dimensions). Факты - это цифры, а измерения - “разрезы”, в которых будут суммироваться факты. В нашем примере измерениями станут: “Регион”, “Город”, “Покупатель”, “Товар”, “Дата”, а факт будет один - поле “Сумма” счета-фактуры. Для факта нужно выбрать один или несколько алгоритмов агрегации. OLAP способен не только суммировать итоги, но и выполнять более сложные вычисления, вплоть до статистического анализа. При выборе нескольких алгоритмов агрегации будут созданы виртуальные, вычисляемые факты (calculated facts). В примере выбран один алгоритм агрегации - “Сумма”.

Особое свойство OLAP-систем - генерация измерений и данных по старшим временным периодам из даты и автоматическое вычисление итогов по этим периодам. Выберем периоды “Год”, “Квартал” и “Месяц”, при этом данных за каждый день в отчете не будет, но появятся сгенерированные измерения “Год”, “Квартал” и “Месяц”. Назовем отчет “Анализ продаж” и сохраним его. Работа по созданию интерфейса аналитического приложения закончена.

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

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

Например, перетащив (операция “move”) колонку “Товар” на первое место, мы получим отчет о сравнении - “Сравнение объемов продаж товаров за год”. Чтобы агрегировать данные за год, достаточно перетащить колонки “Квартал” и “Месяц” в верхнюю часть таблицы - “область неактивных измерений”. Измерения “Квартал” и “Месяц”, перенесенные в эту область, будут закрыты (операция “close dimension”), т. е. исключены из отчета; при этом факты просуммируются за год. Несмотря на то что измерения закрыты, по ним можно задавать конкретные годы, кварталы и месяцы для фильтрации данных (операция “filter”).

Для большей наглядности изменим тип графика, иллюстрирующего OLAP-таблицу, и его расположение на экране.

Углубление в данные (операция “drill down”) позволяет получить более детальные сведения о продажах интересующего нас товара. Щелкнув на знаке “+” напротив товара “Кофе”, мы увидим объемы его продаж в разрезе регионов. Раскрыв регион “Урал”, получим объемы продаж в разрезе городов Уральского региона, углубившись в данные по “Екатеринбургу”, сможем просмотреть данные по оптовым покупателям этого города.

Для установки фильтров можно использовать и открытые измерения. Чтобы сравнить динамику продаж конфет в Москве и Екатеринбурге, установим фильтры на измерения “Товар” и “Город”.

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

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

Действительно, OLAP - это естественное продолжение и развитие идеи электронных таблиц. По сути, визуальный интерфейс OLAP - это тоже электронная таблица (spreadsheet), но оснащенная мощной машиной вычислений и особым стандартом представления данных и управления ими. Более того, некоторые OLAP-клиенты реализованы как add-in к MS Excel. Поэтому миллионная армия “белых воротничков”, уверенно владеющая электронными таблицами, очень быстро осваивает и OLAP-инструменты. Для них это “бархатная революция”, предоставляющая новые возможности, но не сопряженная с необходимостью переучиваться.

Если читатель, прочитав эту статью, не потерял интереса к OLAP, он может обратиться к упомянутым в начале материалам. Сборники таких материалов размещены на ряде сайтов в Интернете, включая сайт Intersoft lab - www.iso.ru. С него можно также скачать демонстрационную версию системы “Контур Стандарт” с описанным в статье примером.

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

Если вам все же необходимо анализировать OLAP-данные после отключения от сети, создайте автономный куб данных. Автономный куб данных - это отдельный файл, который представляет собой кеш сводной таблицы и хранит OLAP-данные, просматриваемые после отключения от локальной сети. OLAP-данные, скопированные в сводную таблицу, можно распечатать, на сайте http://everest.ua подробно об этом рассказано.

Чтобы создать автономный куб данных, сначала создайте сводную таблицу OLAP. Поместите курсор в пределах сводной таблицы и щелкните на кнопке Средства OLAP (OLAP Tools) контекстной вкладки Параметры (Tools), входящей в группу контекстных вкладок Работа со сводными таблицами (PivotTable Tools). Выберите команду Автономный режим OLAP (Offline OLAP) (рис. 9.8).

Рис. 9.8. Создание автономного куба данных

На экране появится диалоговое окно настроек автономного куба данных OLAP. Щелкните в нем на кнопке Создать автономный файл данных (Create Offline Data File). Вы запустили мастер создания файла куба данных. Щелкните на кнопке Далее (Next), чтобы продолжить процедуру.

Cначала необходимо указать размерности и уровни, которые будут включаться в куб данных. В диалоговом окне необходимо выбрать данные, которые будут импортироваться из базы данных OLAP. Идея состоит в том, чтобы указать только те размерности, которые понадобятся после отключения компьютера от локальной сети. Чем больше размерностей укажете, тем больший размер будет иметь автономный куб данных.

Щелкните на кнопке Далее для перехода к следующему диалоговому окну мастера. В нем вы получаете возможность указать члены или элементы данных, которые не будут включаться в куб. В частности, вам не потребуется мера Internet Sales-Extended Amount, поэтому флажок для нее будет сброшен в списке. Сброшенный флажок указывает на то, что указанный элемент не будет импортироваться и занимать лишнее место на локальном жестком диске.

На последнем этапе укажите расположение и имя куба данных. В нашем случае файл куба будет назван MyOfflineCube.cub и будет располагаться в папке Work.

Файлы кубов данных имеют расширение .cub

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

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

Работа с файлами автономного куба

автономный файл куба (. cub) хранит данные в форме куба OLAP (Online Analytical Processing). Эти данные могут представлять часть базы данных OLAP на сервере OLAP или могут создаваться независимо от базы данных OLAP. Используйте автономный файл куба, чтобы продолжить работу с отчетами сводной таблицы и сводной диаграммы, если сервер недоступен или когда вы отключены от сети.

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

При работе с отчетом сводной таблицы или сводной диаграммы, основанными на исходных данных сервера OLAP, вы можете с помощью мастера автономного куба скопировать исходные данные в отдельный файл автономного куба на компьютере. Для создания этих автономных файлов необходимо, чтобы поставщик данных OLAP поддерживал такую возможность, например MSOLAP из служб Microsoft SQL Server Analysis Services, установленных на компьютере.

Примечание: Создание и использование файлов автономных кубов из служб Microsoft SQL Server Analysis Services регулируется термином и лицензированием установки Microsoft SQL Server. Ознакомьтесь с соответствующими сведениями о лицензировании версии SQL Server.

Работа с мастером автономного куба

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

Перевод данных в автономный режим и их обратное подключение

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

Ниже описаны основные шаги, которые следует выполнить для автономной работы с данными, а затем снова перевести данные в Интернет.

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

Создание автономный файл куба на компьютере. В разделе Создание файла автономного куба из базы данных OLAP-сервера (ниже в этой статье).

Отключение от сети и работа с файлом автономного куба.

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

Обновление файла автономного куба с новыми данными и повторное создание автономного файла куба. Ознакомьтесь с разделом обновление и повторное создание файла автономного куба (ниже в этой статье).

БЛОГ

Только качественные посты

Что такое Сводные таблицы Excel и OLAP кубы

Смотрите видео к статье:

OLAP – это англ. online analytical processing, аналитическая технология обработки данных в реальном времени. Простым языком – хранилище с многомерными данными (Куб), еще проще – просто база данных, из которой можно получить данные в Excel и проанализировать с помощью инструмента Excel – Сводные таблицы.

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

Чтобы было понятно, давайте сравним «Обычную таблицу» со «Сводной таблицей»

Обычная таблица:

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

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

Как пользоваться

Откройте файл Excel, который подключен к OLAP-кубу, например «BIWEB»:

Теперь, что это означает и как этим пользоваться?

Перетащите нужные поля, чтобы получить, например, такую таблицу:

«Плюсики » позволяют детализировать отчет. В этом примере «Бренд» детализируется до «Сокращенных названий», а «Квартал» до «Месяца», т.е. так:

Аналитические функции в Excel (функции кубов)

Microsoft постоянно добавляет в Excel новые возможности в части анализа и визуализации данных. Работу с информацией в Excel можно представить в виде относительно независимых трех слоев:

• «правильно» организованные исходные данные
• математика (логика) обработки данных
• представление данных

Рис. 1. Анализ данных в Excel: а) исходные данные, б) мера в Power Pivot, в) дашборд; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Функции кубов и сводные таблицы

Наиболее простым и в тоже время очень мощным средством представления данных являются сводные таблицы. Они могут быть построены на основе данных, содержащихся: а) на листе Excel, б) кубе OLAP или в) модели данных Power Pivot. В последних двух случаях, помимо сводной таблицы, можно использовать аналитические функции (функции кубов) для формирования отчета на листе Excel. Сводные таблицы проще. Функции кубов сложнее, но предоставляют больше гибкости, особенно в оформлении отчетов, поэтому они широко применяются в дашбордах.

Дальнейшее изложение относится к формулам кубов и сводным таблицам на основе модели Power Pivot и в нескольких случаях на основе кубов OLAP.

Простой способ получить функции кубов

Когда (если) вы начинали изучать код VBA, то узнали, что проще всего получить код, используя запись макроса. Далее код можно редактировать, добавить циклы, проверки и др. Аналогично проще всего получить набор функций кубов, преобразовав сводную таблицу (рис. 2). Встаньте на любую ячейку сводной таблицы, перейдите на вкладку Анализ , кликните на кнопке Средства OLAP , и нажмите Преобразовать в формулы .

Рис. 2. Преобразование сводной таблицы в набор функций куба

Числа сохранятся, причем это будут не значения, а формулы, которые извлекают данные из модели данных Power Pivot (рис. 3). Получившуюся таблицу вы может отформатировать. В том числе, можно удалять и вставлять строки и столбцы внутрь таблицы. Срез остался, и он влияет на данные в таблице. При обновлении исходных данных числа в таблице также обновятся.

Рис. 3. Таблица на основе формул кубов

Функция КУБЗНАЧЕНИЕ()

Это, пожалуй, основная функция кубов. Она эквивалента области Значения сводной таблицы. КУБЗНАЧЕНИЕ извлекает данные из куба или модели Power Pivot, и отражает их вне сводной таблицы. Это означает, что вы не ограничены пределами сводной таблицы и можете создавать отчеты с бесчисленными возможностями.

Написание формулы «с нуля»

Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать любую формулу куба «с нуля». Например, в ячейку С10 введена следующая формула (рис. 4):

Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице

Маленькая хитрость. Чтобы удобнее было читать формулы кубов, желательно, чтобы в каждой строке помещался только один аргумент. Можно уменьшить окно Excel. Для этого кликните на значке Свернуть в окно , находящемся в правом верхнем углу экрана. А затем отрегулируйте размер окна по горизонтали. Альтернативный вариант – принудительно переносить текст формулы на новую строку. Для этого в строке формул поставьте курсор в том месте, где хотите сделать перенос и нажмите Alt+Enter.

Рис. 5. Свернуть окно

Синтаксис функции КУБЗНАЧЕНИЕ()

Справка Excel абсолютно точна и абсолютно бесполезна для начинающих:

КУБЗНАЧЕНИЕ(подключение; [выражение_элемента1]; [выражение_элемента2]; …)

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

Выражение_элемента – необязательный аргумент; текстовая строка, представляющая многомерное выражение, которое возвращает элемент или кортеж в кубе. Кроме того, «выражение_элемента» может быть множеством, определенным с помощью функции КУБМНОЖ. Используйте «выражение_элемента» в качестве среза, чтобы определить часть куба, для которой необходимо возвратить агрегированное значение. Если в аргументе «выражение_элемента» не указана мера, будет использоваться мера, заданная по умолчанию для этого куба.

Прежде, чем перейти к объяснению синтаксиса функции КУБЗНАЧЕНИЕ, пару слов о кубах, моделях данных, и загадочном кортеже .

Некоторые сведения о кубах OLAP и моделях данных Power Pivot

Кубы данных OLAP (O nl ine A nalytical P rocessing - оперативный анализ данных) были разработаны специально для аналитической обработки и быстрого извлечения из них данных. Представьте трехмерное пространство, где по осям отложены периоды времени, города и товары (рис. 5а). В узлах такой координатной сетки расположены значения различных мер: объем продаж, прибыль, затраты, количество проданных единиц и др. Теперь вообразите, что измерений десятки, или даже сотни… и мер тоже очень много. Это и будет многомерный куб OLAP. Создание, настройка и поддержание в актуальном состоянии кубов OLAP – дело ИТ-специалистов.

Рис. 5а. Трехмерный куб OLAP

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

Power Pivot – относительно новая фишка Microsoft. Это встроенная в Excel и отчасти независимая среда с привычным интерфейсом. Power Pivot значительно превосходит по своим возможностям стандартные сводные таблицы. Вместе с тем, разработка кубов в Power Pivot относительно проста, а самое главное – не требует участия ИТ-специалиста. Microsoft реализует свой лозунг: «Бизнес-аналитику – в массы!». Хотя модели Power Pivot не являются кубами на 100%, о них также можно говорить, как о кубах (подробнее см. вводный курс Марк Мур. Power Pivot и более объемное издание Роб Колли. Формулы DAX для Power Pivot).

Основные компоненты куба – это измерения, иерархии, уровни, элементы (или члены; по-английски members) и меры (measures). Измерение – основная характеристика анализируемых данных. Например, категория товаров, период времени, география продаж. Измерение – это то, что мы можем поместить на одну из осей сводной таблицы. Каждое измерение помимо уникальных значений включает элемент , выполняющий агрегацию всех элементов этого измерения.

Измерения построены на основе иерархии . Например, категория товаров может разбиваться на подкатегории, далее – на модели, и наконец – на названия товаров (рис. 5б) Иерархия позволяет создавать сводные данные и анализировать их на различных уровнях структуры. В нашем примере иерархия Категория включает 4 Уровня .

Элементы (отдельные члены) присутствуют на всех уровнях. Например, на уровне Category есть четыре элемента: Accessories, Bikes, Clothing, Components. Другие уровни имеют свои элементы.

­Меры – это вычисляемые значения, например, объем продаж. Меры в кубах хранятся в собственном измерении, называемом (см. ниже рис. 9). Меры не имеют иерархий. Каждая мера рассчитывает и хранит значение для всех измерений и всех элементов, и нарезается в зависимости от того, какие элементы измерений мы поместим на оси. Еще говорят, какие зададим координаты, или какой зададим контекст фильтра. Например, на рис. 5а в каждом маленьком кубике рассчитывается одна и та же мера – Прибыль. А возвращаемое мерой значение зависит от координат. Справа на рисунке 5а показано, что Прибыль (в трех координатах) по Москве в октябре на яблоках = 63 000 р. Меру можно трактовать, и как одно из измерений. Например, на рис. 5а вместо оси Товары , разместить ось Меры с элементами Объем продаж , Прибыль , Проданные единицы . Тогда каждая ячейка и будет каким-то значением, например, Москва, сентябрь, объем продаж.

Кортеж – несколько элементов разных измерений, задающие координаты по осям куба, в которых мы рассчитываем меру. Например, на рис. 5а Кортеж = Москва, октябрь, яблоки. Также допустимый кортеж – Пермь, яблоки. Еще один – яблоки, август. Не вошедшие в кортеж измерения присутствуют в нем неявно, и представлены членом по умолчанию . Таким образом, ячейка многомерного пространства всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Нельзя включить два элемента одного измерения в кортеж, не позволит синтаксис. Например, недопустимый кортеж Москва и Пермь, яблоки. Чтобы реализовать такое многомерное выражение потребуется набор двух кортежей: Москва и яблоки + Пермь и яблоки.

Набор элементов – несколько элементов одного измерения. Например, яблоки и груши. Набор кортежей – несколько кортежей, каждый из которых состоит из одинаковых измерений в одной и той же последовательности. Например, набор из двух кортежей: Москва, яблоки и Пермь, бананы.

Автозавершение в помощь

Вернемся к синтаксису функции КУБЗНАЧЕНИЕ. Воспользуемся автозавершением. Начните ввод формулы в ячейке:

Excel предложит все доступные в книге Excel подключения:

Рис. 6. Подключение к модели данных Power Pivot всегда называется ThisWorkbookDataModel

Рис. 7. Подключения к кубам

Продолжим ввод формулы (в нашем случае для модели данных):

Автозавершение предложит все доступные таблицы и меры модели данных:

Рис. 8. Доступные элементы первого уровня – имена таблиц и набор мер (выделен)

Выберите значок Measures . Поставьте точку:

КУБЗНАЧЕНИЕ(» ThisWorkbookDataModel » ; » .

Автозавершение предложит все доступные меры:

Рис. 9. Доступные элементы второго уровня в наборе мер

Выберите меру . Добавьте кавычки, закрывающую скобку, нажмите Enter.

КУБЗНАЧЕНИЕ(» ThisWorkbookDataModel » ; » . »)

Рис. 10. Формула КУБЗНАЧЕНИЕ в ячейке Excel

Аналогичным образом можете добавить третий аргумент в формулу:

VBA в Excel Объект Excel.PivotTable и работа со сводными таблицами и кубами OLAP в Excel

10.8 Работа со сводными таблицами (объект PivotTable)

Объект Excel.PivotTable, программная работа со сводными таблицами и кубами OLAP в Excel средствами VBA, объект PivotCache, создание макета сводной таблицы

В процессе работы большинства предприятий накапливаются так называемые необработанные данные (raw data) о деятельности. Например, для торгового предприятия могут накапливаться данные о продажах товаров - по каждой покупке отдельно, для предприятий сотовой связи - статистика нагрузки на базовые станции и т.п. Очень часто менеджменту предприятия необходима аналитическая информация, которая генерируется на основе необработанной - например, посчитать вклад каждого вида товара в доходы предприятия или качество обслуживания в зоне данной станции. Из необработанной информации такие сведения извлечь очень тяжело: нужно выполнять очень сложные SQL-запросы, которые выполняются долго и часто мешают текущей работе. Поэтому все чаще в настоящее время необработанные данные сводятся вначале в хранилище архивных данных - Data Warehouse, а затем - в кубы OLAP, которые очень удобны для интерактивного анализа. Проще всего представить себе кубы OLAP как многомерные таблицы, в которых вместо стандартных двух измерений (столбцы и строки, как в обычных таблицах), измерений может быть очень много. Обычно для описания измерений в кубе используется термин «в разрезе». Например, отделу маркетинга может быть нужна информация во временном разрезе, в региональном разрезе, в разрезе типов продукта, в разрезе каналов продаж и т.п. При помощи кубов (в отличие от стандартных SQL-запросов) очень просто получать ответы на вопросы типа «сколько товаров такого-то типа было продано в четвертом квартале прошлого года в Северо-Западном регионе через региональных дистрибьюторов.

Конечно же, в обычных базах данных такие кубы не создать. Для работы с кубами OLAP требуются специализированные программные продукты. Вместе с SQL Server поставляется база данных OLAP от Microsoft, которая называется Analysis Services. Есть OLAP-решения от Oracle, IBM, Sybase и т.п.

Для работы с такими кубами в Excel встроен специальный клиент. По-русски он называется Сводная таблица (на графическом экране он доступен через меню Данные -> Сводная таблица ), а по-английски - Pivot Table . Соответственно, объект, который представляет этот клиент, называется PivotTable. Необходимо отметить, что он умеет работать не только с кубами OLAP, но и с обычными данными в таблицах Excel или баз данных, но многие возможности при этом теряются.

Сводная таблица и объект PivotTable - это программные продукты фирмы Panorama Software, которые были приобретены Microsoft и интегрированы в Excel. Поэтому работа с объектом PivotTable несколько отличается от работы с другими объектами Excel. Догадаться, что нужно сделать, часто бывает непросто. Поэтому рекомендуется для получения подсказок активно использовать макрорекордер. В то же время при работе со сводными таблицами пользователям часто приходится выполнять одни и те же повторяющиеся операции, поэтому автоматизация во многих ситуациях необходима.

Как выглядит программная работа со сводной таблицей?

Первое, что нам потребуется сделать - создать объект PivotCache, который будет представлять набор записей, полученных с источника OLAP. Очень условно этот объект PivotCache можно сравнить с QueryTable. Для каждого объекта PivotTable можно использовать только один объект PivotCache. Создание объекта PivotCache производится при помощи метода Add() коллекции PivotCaches:

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

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

• SourceType - обязательный, определяет тип источника данных для сводной таблицы. Можно указать создание PivotTable на основе диапазона в Excel, данных из базы данных, во внешнем источнике данных, другой PivotTable и т.п. На практике обычно OLAP есть смысл использовать только тогда, когда данных много - соответственно нужно специализированное внешнее хранилище (например, Microsoft Analysis Services). В этой ситуации выбирается значение xlExternal.
• SourceData - обязательный во всех случаях, кроме тех, когда значение первого параметра - xlExternal. Собственно говоря, определяет тот диапазон данных, на основе которого и будет создаваться PivotTable. Обычно принимает объект Range.

Следующая задача - настроить параметры объекта PivotCache. Как уже говорилось, этот объект очень напоминает QueryTable, и набор свойств и методов у него очень похожий. Некоторые наиболее важные свойства и методы:

• ADOConnection - возможность возвратить объект ADO Connection, который автоматически создается для подключения к внешнему источнику данных. Используется для дополнительной настройки свойств подключения.
• Connection - работает точно так же, как и одноименное свойство объекта QueryTable. Может принимать строку подключения, готовый объект Recordset, текстовый файл, Web-запрос. файл Microsoft Query. Чаще всего при работе с OLAP прописывается строка подключения напрямую (поскольку получать объект Recordset, например для изменения данных, большого смысла нет - источники данных OLAP практически всегда доступны только на чтение). Например, настройка этого свойства для подключения к базе данных Foodmart (учебная база данных Analysis Services) на сервере LONDON может выглядеть так:

PC1.Connection = «OLEDB;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000»

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

• свойство LocalConnection позволяет подключиться к локальному кубу (файлу *.cub), созданному средствами Excel. Конечно, такие файлы для работы с «производственными» объемами данных использовать очень не рекомендуется - только для целей создания макетов и т.п.
• свойство MemoryUsed возвращает количество оперативной памяти, используемой PivotCache. Если PivotTable на основе этого PivotCache еще не создана и не открыта, возвращает 0. Можно использовать для проверок, если ваше приложение будет работать на слабых клиентах.
• свойство OLAP возвращает True, если PivotCache подключен к серверу OLAP.
• OptimizeCache - возможность оптимизировать структуру кэша. Изначальная загрузка данных будет производиться дольше, но потом скорость работы может возрасти. Для источников OLE DB не работает.

Остальные свойства объекта PivotCache совпадают с аналогичными свойствами объекта QueryTable, и поэтому здесь рассматриваться не будут.

Главный метод объекта PivotCache - это метод CreatePivotTable(). При помощи этого метода и производится следующий этап - создание сводной таблицы (объекта PivotTable). Этот метод принимает четыре параметра:

• TableDestination - единственный обязательный параметр. Принимает объект Range, в верхний левый угол которого будет помещена сводная таблица.
• TableName - имя сводной таблицы. Если не указано, то автоматически сгенерируется имя вида «СводнаяТаблица1».
• ReadData - если установить в True, то все содержимое куба будет автоматически помещено в кэш. С этим параметром нужно быть очень осторожным, поскольку неправильное его применение может резко увеличить нагрузку на клиента.
• DefaultVersion - это свойство обычно не указывается. Позволяет определить версию создаваемой сводной таблицы. По умолчанию используется наиболее свежая версия.

Создание сводной таблицы в первой ячейке первого листа книги может выглядеть так:

PC1.CreatePivotTable Range («A1»)

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

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

Полагаться на пользователя в том, что он правильно разместит элементы во всех четырех областях, трудно. Кроме того, это может занять определенное время. Поэтому часто требуется расположить данные в сводной таблице программным образом. Эта операция производится при помощи объекта CubeField. Главное свойство этого объекта - Orientation, оно определяет, где будет находиться то или иное поле. Например, помещаем измерение Customers в область столбцов:

PT1.CubeFields («»).Orientation = xlColumnField

Затем - измерение Time в область строк:

PT1.CubeFields («»).Orientation = xlRowField

Затем - измерение Product в область страницы:

PT1.CubeFields («»).Orientation = xlPageField

И наконец, показатель (числовые данные для анализа) Unit Sales:

PT1.CubeFields(«.»).Orientation = xlDataField

Первый интерфейс сводных таблиц, называемых также сводными отчеты, был включен в состав Excel еще в 1993м году (версии Excel 5.0). Несмотря на множество полезных функциональных возможностей, он практически не применяется в работе большинством пользователей Excel. Даже опытные пользователи зачастую подразумевают под термином «сводный отчет» нечто построенное с помощью сложных формул. Попробуем популяризировать использование сводных таблиц в повседневной работе экономистов. В статье обсуждаются теоретические основы создания сводных отчетов, даются практические рекомендации по их использованию, а также приводится пример доступа к данным на основе нескольких таблиц.

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

Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.

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

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

• виды или группы товаров;
• бренды или категории товаров;
• периоды (месяц, квартал, год);
• покупатели или группы покупателей;
• регионы продаж
• и т.п.

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

• объем продаж;
• цена продажи;
• индивидуальная скидка
• и т.п.

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

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

OLAP

Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта. По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server. Некоторые даже ошибочно считают его единственным представителем программной реализации данной концепции.

Виртуальный куб данных

«Виртуальный куб» (многомерный куб, OLAP-куб) - это специальный термин, предложенный некоторыми поставщиками специализированного программного обеспечения. OLAP-системы обычно готовят и хранят данные в собственных структурах, а специальные интерфейсы анализа (например, сводные отчеты Excel) обращаются к данным этих виртуальных кубов. При этом использование подобного выделенного хранилища совсем не обязательно для обработки многомерной информации. В общем случае, виртуальный куб – это и есть массив специально оптимизированных многомерных данных, который используется для создания сводных отчетов. Он может быть получен как через специализированные программные средства, так и через простой доступ к таблицам базы данных или любой другой источник, например к таблице Excel.

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

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

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

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

Редактирование сводных таблиц

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

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

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

Подойдем к практическому применению сводных таблиц. Попробуем проанализировать данные о продажах в различных направлениях. Файл pivottableexample.xls состоит из нескольких листов. Лист Пример содержит основную информацию о продажах за определенный период. Для простоты примера будем анализировать единственный числовой показатель – объем продажи в кг. Имеются следующие ключевые измерения данных: продукция, покупатель и перевозчик (транспортная компания). Кроме того, имеются несколько дополнительных измерений данных, являющихся признаками продукта: тип, бренд, категория, поставщик, а также покупателя: тип. Эти данные собраны на листе Справочники. На практике подобных измерений может быть гораздо больше.

Лист Пример содержит стандартное средство анализа данных – автофильтр. Глядя на пример заполнения таблицы, очевидно, что нормальному анализу поддаются данные о продажах по датам (они расположены по столбцам). Кроме того, используя автофильтр можно попробовать просуммировать данные по сочетаниям одного или нескольких ключевых критериев. Совершенно отсутствует информация о брендах, категориях и типах. Нет возможности сгруппировать данные с автоматическим суммированием по определенному ключу (например, по покупателям). Кроме того, набор дат зафиксирован, и просмотреть итоговую информацию за определенный период, например, 3 дня, автоматическими средствами не удастся.

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

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

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

От автофильтра к сводному отчету

Теоретически на данных листа Продажи уже можно проводить анализ в трех измерениях: товары, покупатели и перевозчики. Данные о свойствах продукции и покупателей на данном листе отсутствуют, что, соответственно, не позволит показать их и в сводной таблице. В нормальном режиме создания сводной таблицы для исходных данных Excel не позволяет связывать данные нескольких таблиц по определенным полям. Обойти это ограничение можно программными средствами – см. пример-дополнение к данной статье на нашем сайте. Чтобы не прибегать к программным методам обработки информации (тем более, что они и не универсальны), следует добавить дополнительные характеристики непосредственно в форму ввода журнала – см. лист ПродажиАнализ.

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

Теперь посмотрим какие возможности дает интерфейс сводных таблиц. На листе СводАнализ построено несколько отчетов на основе диапазона ячеек с данными листа ПродажиАнализ .

Первая таблица анализа построена через интерфейс Excel 2007 Лента \ Вставка \ Сводная таблица (в Excel 2000-2003 меню Данные \ Сводная таблица ).

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

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

Свойства и форматирование

Кроме непосредственного отображения данных, имеется большой набор возможностей по отображению внешнего вида сводных таблиц. Лишние данные можно скрывать, используя фильтры. Для единичного элемента или поля проще пользоваться пунктом контекстного меню Удалить (в версии 2000-2003 Скрыть ).

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

Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:

Обратите внимание, что в диаграмме активны управляющие фильтры и области перетаскивания.

Доступ к внешним данным

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

Excel поддерживает множество типов источников внешних данных:

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

Удивительное - рядом...

По ходу работы мне часто требовалось делать сложные отчеты, я все время пытался найти в них что-то общее, чтобы составлять их более просто и универсально, даже написал и опубликовал по этому поводу статью «Дерево Осипова». Однако мою статью раскритиковали и сказали, что все те проблемы, которые я поднял, давно уже решены в MOLAP.RU v.2.4 (www.molap.rgtu.ru) и порекомендовали посмотреть сводные таблицы в EXCEL.
Это оказалось настолько простым, что приложив к этому свои гениальные ручонки, у меня получилась очень простая схема для выгрузки данных из 1С7 или любой другой базы данных (в дальнейшем под 1С подразумевается любая база данных) и анализа в OLAP.
Я думаю, многие схемы выгрузки в OLAP слишком усложнены, я выбираю простоту.

Характеристики :

1. Для работы требуется только EXCEL 2000.
2. Пользователь сам может конструировать отчеты без программирования.
3. Выгрузка из 1С7 в простом формате текстового файла.
4. Для бухгалтерских проводок уже имеется универсальная обработка для выгрузки, работающая в любой конфигурации. Для выгрузки других данных имеются обработки-образцы.
5. Можно заранее сконструировать формы отчетов, а затем применять их к разным данным без их повторного конструирования.
6. Довольно хорошая производительность. На первом длительном этапе данные сначала импортируются в EXCEL из текстового файла и строится куб OLAP, а затем довольно быстро на основе этого куба может быть построен любой отчет. Например, данные о продажах товара по магазину за 3 месяца с ассортиментом 6000 товаров, загружаются в EXCEL 8 минут на Cel600-128M, рейтинг по товарам и группам (OLAP-отчет) пересчитывается за 1 минуту.
7. Данные выгружаются из 1С7 полностью за указанный период (все движения, по всем складам, фирмам, счетам). При импорте в EXCEL возможно использование фильтров, загружающих для анализа только нужные данные (например, из всех движений, только продажи).
8. В настоящее время разработаны способы анализа движений или остатков, но не движений и остатков вместе, хотя это в принципе возможно.

Что такое OLAP : (www.molap.rgtu.ru)

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

Дата - дата операции
Месяц - месяц операции
Неделя - неделя операции
Вид - закуп, продажа, возврат, списание
Контрагент - внешняя организация, участвующая в операции
Автор - человек, выписавший накладную

В 1С, например, одна строка этой таблицы будет соответствовать одной строке накладной, некоторые поля (Контрагент, Дата) при этом берутся из шапки накладной.

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

Эта таблица является исходной для OLAP-анализа.

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

Как использовать у себя :

Данные из дистрибутива распаковать именно в каталог c:\fixin (для торговой системы возможно в c:\reports) . Прочитайте readme.txt и выполните все инструкции в нем.

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

Дата|ДеньНедели|Неделя|Год|Квартал|Месяц|Документ|Фирма|Дебет|ДтНоменклатура
|ДтГруппаНоменклатура|ДтРазделНоменклатура|Кредит|Сумма|ВалСумма|Количество
|Валюта|ДтКонтрагенты|ДтГруппаКонтрагенты|КтКонтрагенты|КтГруппаКонтрагенты|
КтРазныеОбъекты

Где под префиксами Дт(Кт) идут субконто Дебета (Кредита), Группа - это группа данного субконто (если имеется), Раздел - группа группы, Класс - группа раздела.

Для торговой системы поля могут быть такие:

Направление|ВидДвижения|ЗаНал|Товар|Количество|Цена|Сумма|Дата|Фирма
|Склад|Валюта|Документ|ДеньНедели|Неделя|Год|Квартал|Месяц|Автор
|КатегорияТовара|КатегорияДвижения|КатегорияКонтрагента|ГруппаТовара
|ВалСумма|Себестоимость|Контрагент

Для анализа данных используются таблицы "Анализ движений.xls" ("Анализ бухгалтерии.xls"). Открывая их, не отключайте макросы, иначе вы не сможете обновлять отчеты (они запускаются макросами на языке VBA). Исходные данные эти файлы берут из файлов C:\fixin\motions.txt (C:\fixin\buh.txt), в остальном они одинаковые. Поэтому возможно, вам придется скопировать ваши данные в один из этих файлов.
Чтобы в EXCEL загрузились ваши данные, выберите или напишите свой фильтр и нажмите кнопку "Сформировать" на листе "Условия".
Листы отчетов начинаются префиксом "Отч". Перейдите на лист отчета, нажмите "Обновить" и данные отчета изменятся в соответствии с последними загруженными данными.
Если вас не устраивают стандартные отчеты, есть лист ОтчШаблон. Скопируйте его в новый лист и настройте вид отчета, работая со сводной таблицей на этом листе (о работе со сводными таблицами - в любой книге по EXCEL 2000). Рекомендую настраивать отчеты на небольшом наборе данных, а затем уже запускать их на большом массиве, т.к. нет никакой возможности отключить перерисовку таблиц при каждом изменении макета отчета.

Технические комментарии :

При выгрузке данных из 1С пользователь выбирает папку, куда ему выгружать файл. Я сделал это потому, что вполне вероятно в ближайшем будущем будут выгружаться несколько файлов (остатки и движения). Затем по нажатию в Проводнике кнопки "Отправить" --> "На OLAP-анализ в EXCEL 2000" данные копируются из выбранной папки в папку C:\fixin. (чтобы эта команда появилась в списке команды "Отправить" и нужно скопировать файл "На OLAP-анализ в EXCEL 2000.bat" в каталог C:\Windows\SendTo) Поэтому выгружайте данные сразу давая имена файлам motions.txt или buh.txt.

Формат текстового файла:
Первая строка текстового файла - заголовки колонок разделенные "|", остальные строки содержат значения этих колонок, разделенные "|".

Для импорта текстовых файлов в Excel используется Microsoft Query (составная часть EXCEL) для его работы необходимо наличие в каталоге импорта (C:\fixin) файла shema.ini, содержащего следующую информацию:

ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Пояснение: motions.txt и buh.txt - это название раздела, соответствует имени импортируемого файла, описывает, как импортировать текстовый файл в Эксель. Остальные параметры означают, что первая строка содержит названия колонок, разделителем колонок является "|", набор символов - Windows ANSI (для ДОС - OEM).
Тип полей определяется автоматически исходя из содержащихся в колонке данных (дата, число, строка).
Перечень полей не нужно нигде описывать - EXCEL и OLAP сами определят, какие поля содержатся в файле по заголовкам в первой строке.

Внимание, проверьте ваши региональные настройки "Панель управления" --> "Региональные настройки" . В моих обработках числа выгружаются с разделителем запятая, а даты в формате "ДД.ММ.ГГГГ".

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

Я понимаю, что любители MS SQL Server и мощных баз данных начнут ворчать, что у меня слишком все упрощено, что моя обработка загнется на годичной выборке, но в первую очередь я хочу дать преимущества OLAP-анализа для средних организаций. Я бы позиционировал этот продукт как инструмент годичного анализа для оптовых компаний, квартального анализа для розничной торговли и оперативного анализа для любой организации.

Мне пришлось повозиться с VBA, чтобы данные брались из файла с любым списком полей и можно было заранее готовить бланки отчетов.

Описание работы в EXCEL (для пользователей):

Инструкция по использованию отчетов:
1. Отправьте на анализ выгруженные данные (уточните у администратора). Для этого нажмите правой кнопкой на папке, в которую у вас выгрузились данные из 1С и выберите команду "Отправить", затем "На OLAP-анализ в EXCEL 2000".
2. Откройте файл "Анализ движений.xls"
3. Выберите Значение фильтра, нужные вам фильтры можно дописать на закладке "Значения".
4. Нажмите кнопку "Сформировать", при этом выгруженные данные будут загружены в EXCEL.
5. После загрузки данных в EXCEL, можно смотреть различные отчеты. Для этого достаточно нажать кнопку "Обновить" в выбранном отчете. Листы с отчетами начинаются на Отч.
Внимание! После того как вы поменяете значение фильтра, нужно еще раз нажать кнопку "Сформировать", чтобы данные в EXCEL перезагрузились из файла выгрузки в соответствие с фильтрами.

Обработки из демо-примера:

Обработка motionsbuh2011.ert – последняя версия выгрузки проводок из Бухгалтерии 7.7 для анализа в Excel . В ней есть галочка «Присоединить в файл», которая позволяет выгружать данные частями по периодам, присоединяя их в один и тот же файл, а не выгружая в один и тот же файл заново:

Обработка motionswork.ert выгружает данные о продажах для анализа в Excel.

Примеры отчетов :

Шахматка по проводкам:

Загруженность операторов по видам накладных:

P.S. :

Понятно, что по аналогичной схеме можно организовать выгрузку данных из 1С8.
В 2011 году ко мне обращался пользователь, которому нужно было доработать эту обработку в 1С7, чтобы она выгружала большие объемы данных, я нашел аутсорсера и выполнил эту работу. Так что разработка вполне актуальна.

Обработка motionsbuh2011.ert доработана, чтобы справляться с выгрузкой большого объема данных.

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

Если исходные данные содержатся в настольной СУБД, вычисление агрегатных данных производится самим OLAP-средством. Если же источник исходных данных - серверная СУБД, многие из клиентских OLAP-средств посылают на сервер SQL-запросы, содержащие оператор GROUP BY, и в результате получают агрегатные данные, вычисленные на сервере.

Как правило, OLAP-функциональность реализована в средствах статистической обработки данных (из продуктов этого класса на российском рынке широко распространены продукты компаний StatSoft и SPSS) и в некоторых электронных таблицах. В частности, средствами многомерного анализа обладает Microsoft Excel. С помощью этого продукта можно создать и сохранить в виде файла небольшой локальный многомерный OLAP-куб и отобразить его двух- или трехмерные сечения.

Надстройки к пакету приложений Microsoft Office для извлечения и обработки данных представляют собой ряд функций, обеспечивающих доступ к возможностям извлечения и обработки данных из приложений Microsoft Office, и тем самым позволяющих осуществлять прогностический анализ на локальном компьютере. Благодаря тому, что встроенные в службы платформы Microsoft SQL Server алгоритмы извлечения и обработки данных доступны из среды приложений Microsoft Office, бизнес-пользователи могут легко извлекать ценную информацию из сложных наборов данных всего несколькими щелчками мыши. Надстройки к пакету приложений Office для извлечения и обработки данных дают конечным пользователям возможность выполнять анализ непосредственно в приложениях Microsoft Excel и Microsoft Visio.

В состав Microsoft Office 2007 входят три отдельных OLAP-компонента:

1. клиент извлечения и обработки данных для Excel позволяет создавать проекты извлечения и обработки данных на базе служб SSAS и управлять ими из Excel 2007;
2. средства анализа таблиц для приложения Excel позволяют использовать встроенные в службы SSAS функции извлечения и обработки информации для анализа данных, хранящихся в таблицах Excel;
3. шаблоны извлечения и обработки данных для приложения Visio позволяют визуализировать деревья решений, деревья регрессии, кластерные диаграммы и сети зависимостей на диаграммах Visio.