Практические работы по olap технология в excel. Olap кубы в excel. Программные компоненты, необходимые для доступа к источникам данных OLAP

Клиентские 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.

Таблица 1.1. Продукты Oracle для OLAP и бизнес-анализа

Тип средств	Продукт

В стандартной сводной таблице исходные данные хранятся на локальном жестком диске. Таким образом, вы всегда можете управлять ими и переорганизовывать их, даже не имея доступа к сети. Но это ни в коей мере не касается сводных таблиц 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

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

По ходу работы мне часто требовалось делать сложные отчеты, я все время пытался найти в них что-то общее, чтобы составлять их более просто и универсально, даже написал и опубликовал по этому поводу статью «Дерево Осипова». Однако мою статью раскритиковали и сказали, что все те проблемы, которые я поднял, давно уже решены в 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 доработана, чтобы справляться с выгрузкой большого объема данных.

Выберите документ из архива для просмотра:

18.5 КБ автомобили.xls

14 КБ страны.xls

Excel пр.р. 1.docx

Библиотека
материалов

Практическая работа 1

«Назначение и интерфейс MS Excel»

Выполнив задания этой темы, вы:

1. Научитесь запускать электронные таблицы;

2. Закрепите основные понятия: ячейка, строка, столбец, адрес ячейки;

3. Узнаете как вводить данные в ячейку и редактировать строку формул;

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

Задание: Познакомиться практически с основными элементами окна MS Excel.

Запустите программу Microsoft Excel. Внимательно рассмотрите окно программы.

Документы, которые создаются с помощью EXCEL , называются рабочими книгами и имеют расширение . XLS . Новая рабочая книга имеет три рабочих листа, которые называются ЛИСТ1, ЛИСТ2 и ЛИСТ3. Эти названия указаны на ярлычках листов в нижней части экрана. Для перехода на другой лист нужно щелкнуть на названии этого листа.

Действия с рабочими листами:

Переименование рабочего листа. Установить указатель мыши на корешок рабочего листа и два раза щелкнуть левой клавишей или вызвать контекстное меню и выбрать команду Переименовать. Задайте название листа "ТРЕНИРОВКА"

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

Удаление рабочего листа. Выделить ярлычок листа "Лист 2", и с помощью контекстного меню удалите .

Ячейки и диапазоны ячеек.

Рабочее поле состоит из строк и столбцов. Строки нумеруются числами от 1 до 65536. Столбцы обозначаются латинскими буквами: А, В, С, …, АА, АВ, … , IV , всего – 256. На пересечении строки и столбца находится ячейка. Каждая ячейка имеет свой адрес: имя столбца и номер строки, на пересечении которых она находится. Например, А1, СВ234, Р55.

Для работы с несколькими ячейками их удобно объединять их в «диапазоны».

Диапазон – это ячейки, расположенные в виде прямоугольника. Например, А3, А4, А5, В3, В4, В5. Для записи диапазона используется « : »: А3:В5

8:20 – все ячейки в строках с 8 по 20.

А:А – все ячейки в столбце А.

Н:Р – все ячейки в столбцах с Н по Р.

В адрес ячейки можно включать имя рабочего листа: Лист8!А3:В6.

2. Выделение ячеек в Excel

Что выделяем

Действия

Одну ячейку

Щелчок на ней или перемещаем выделения клавишами со стрелками.

Строку

Щелчок на номере строки.

Столбец

Щелчок на имени столбца.

Диапазон ячеек

Протянуть указатель мыши от левого верхнего угла диапазона к правому нижнему.

Несколько диапазонов

Выделить первый, нажать SCHIFT + F 8, выделить следующий.

Всю таблицу

Щелчок на кнопке «Выделить все» (пустая кнопка слева от имен столбцов)

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

Воспользуйтесь полосами прокрутки для того, чтобы определить сколько строк имеет таблица и каково имя последнего столбца.
Внимание!!! Чтобы достичь быстро конца таблицы по горизонтали или вертикали, необходимо нажать комбинации клавиш: Ctrl+→ - конец столбцов или Ctrl+↓ - конец строк. Быстрый возврат в начало таблицы - Ctrl+Home.

В ячейке А3 Укажите адрес последнего столбца таблицы.

Сколько строк содержится в таблице? Укажите адрес последней строки в ячейке B3.

3. В EXCEL можно вводить следующие типы данных:

Числа.

Текст (например, заголовки и поясняющий материал).

Функции (например, сумма, синус, корень).

Формулы.

Данные вводятся в ячейки. Для ввода данных нужную ячейку необходимо выделить. Существует два способа ввода данных:

Просто щелкнуть в ячейке и напечатать нужные данные.

Щелкнуть в ячейке и в строке формул и ввести данные в строку формул.

Нажать ENTER .

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

4. Изменение данных.

Выделить ячейку и нажать F 2 и изменить данные.

Выделить ячейку e щелкнуть в строке формул и изменить данные там.

Для изменения формул можно использовать только второй способ.

Измените данные в ячейке N35, добавьте свою фамилию. используя любой из способов.

5. Ввод формул.

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

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

Действие

Примеры

+

Сложение

А1+В1

-

Вычитание

А1 - В2

*

Умножение

В3*С12

/

Деление

А1 / В5

Возведение в степень

А4 ^3

=, <,>,<=,>=,<>

Знаки отношений

А2

В формулах можно использовать скобки для изменения порядка действий.

Автозаполнение.

Очень удобным средством, которое используется только в MS EXCEL , является автозаполнение смежных ячеек. К примеру, необходимо в столбец или строку ввести названия месяцев года. Это можно сделать вручную. Но есть гораздо более удобный способ:

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

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

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

Если необходимо заполнить какой-то числовой ряд, то нужно в соседние две ячейки ввести два первых числа (например, в А4 ввести 1, а в В4 – 2), выделить эти две ячейки и протянуть за маркер область выделения до нужных размеров.

Выбранный для просмотра документ Excel пр.р. 2.docx

Библиотека
материалов

Практическая работа 2

«Ввод данных и формул в ячейки электронной таблицы MS Excel»

· Вводить в ячейки данные разного типа: текстовые, числовые, формулы.

Задание: Выполните в таблице ввод необходимых данных и простейшие расчеты.

Технология выполнения задания:

1. Запустите программу Microsoft Excel.

2. В ячейку А1 Листа 2 введите текст: "Год основания школы". Зафиксируйте данные в ячейке любым известным вам способом.

3. В ячейку В1 введите число –год основания школы (1971).

4. В ячейку C1 введите число –текущий год (2016).

Внимание! Обратите внимание на то, что в MS Excel текстовые данные выравниваются по левому краю, а числа и даты – по правому краю.

5. Выделите ячейку D1 , введите с клавиатуры формулу для вычисления возраста школы: = C1- B1

Внимание! Ввод формул всегда начинается со знака равенства «=». Адреса ячеек нужно вводить латинскими буквами без пробелов. Адреса ячеек можно вводить в формулы без использования клавиатуры, а просто щелкая мышкой по соответствующим ячейкам.

6. Удалите содержимое ячейки D1 и повторите ввод формулы с использованием мышки. В ячейке D1 установите знак «=» , далее щелкните мышкой по ячейке C1, обратите внимание адрес этой ячейки появился в D1, поставьте знак «–» и щелкните по ячейке B1 , нажмите {Enter}.

7. В ячейку А2 введите текст "Мой возраст".

8. В ячейку B2 введите свой год рождения.

9. В ячейку С2 введите текущий год.

10. Введите в ячейку D2 формулу для вычисления Вашего возраста в текущем году (= C2- B2).

11. Выделите ячейку С2. Введите номер следующего года. Обратите внимание, перерасчет в ячейке D2 произошел автоматически.

12. Определите свой возраст в 2025 году. Для этого замените год в ячейке С2 на 2025.

Самостоятельная работа

Упражнение: Посчитайте, используя ЭТ, хватит ли вам 130 рублей, чтоб купить все продукты, которые вам заказала мама, и хватит ли купить чипсы за 25 рублей?

Технология выполнения упражнения:
o В ячейку А1 вводим “№”
o В ячейки А2, А3 вводим “1”, “2”, выделяем ячейки А2,А3, наводим на правый нижний угол (должен появиться черный крестик), протягиваем до ячейки А6
o В ячейку В1 вводим “Наименование”
o В ячейку С1 вводим “Цена в рублях”
o В ячейку D1 вводим “Количество”
o В ячейку Е1 вводим “Стоимость” и т.д.
o В столбце “Стоимость” все формулы записываются на английском языке!
o В формулах вместо переменных записываются имена ячеек.
o После нажатия Enter вместо формулы сразу появляется число – результат вычисления

o Итого посчитайте самостоятельно.

Результат покажите учителю!!!

Выбранный для просмотра документ Excel пр.р. 3.docx

Библиотека
материалов

Практическая работа 3

«MS Excel. Создание и редактирование табличного документа»

Выполнив задания этой темы, вы научитесь:

 Создавать и заполнять данными таблицу;

 Форматировать и редактировать данные в ячейке;

 Использовать в таблице простые формулы;

 Копировать формулы.

Задание:

1. Создайте таблицу, содержащую расписание движения поездов от станции Саратов до станции Самара. Общий вид таблицы «Расписание» отображен на рисунке.

2. Выберите ячейку А3 , замените слово «Золотая» на «Великая» и нажмите клавишу Enter .

3. Выберите ячейку А6 , щелкните по ней левой кнопкой мыши дважды и замените «Угрюмово» на «Веселково»

4. Выберите ячейку А5 зайдите в строку формул и замените «Сенная» на «Сенная 1».

5. Дополните таблицу «Расписание» расчетами времени стоянок поезда в каждом населенном пункте. (вставьте столбцы) Вычислите суммарное время стоянок, общее время в пути, время, затрачиваемое поездом на передвижение от одного населенного пункта к другому.

Технология выполнения задания:

1. Переместите столбец «Время отправления» из столбца С в столбец D. Для этого выполните следующие действия:

Выделите блок C1:C7; выберите команду Вырезать .
Установите курсор в ячейку D1;
Выполните команду Вставить ;
Выровняйте ширину столбца в соответствии с размером заголовка.;

2. Введите текст «Стоянка» в ячейку С1. Выровняйте ширину столбца в соответствии с размером заголовка.

3. Создайте формулу, вычисляющую время стоянки в населенном пункте.

4. Необходимо скопировать формулу в блок С4:С7, используя маркер заполнения. Для этого выполните следующие действия:
Вокруг активной ячейки имеется рамка, в углу которой есть маленький прямоугольник, ухватив его, распространите формулу вниз до ячейки С7.

5. Введите в ячейку Е1 текст «Время в пути». Выровняйте ширину столбца в соответствии с размером заголовка.

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

7. Измените формат чисел для блоков С2:С9 и Е2:Е9. Для этого выполните следующие действия:

Выделите блок ячеек С2:С9;
Главная – Формат – Другие числовые форматы - Время и установите параметры (часы:минуты) .

Нажмите клавишу Ок .

8. Вычислите суммарное время стоянок.
Выберите ячейку С9;
Щелкните кнопку Автосумма на панели инструментов;
Подтвердите выбор блока ячеек С3:С8 и нажмите клавишу Enter .

9. Введите текст в ячейку В9. Для этого выполните следующие действия:

Выберите ячейку В9;
Введите текст «Суммарное время стоянок». Выровняйте ширину столбца в соответствии с размером заголовка.

10. Удалите содержимое ячейки С3.

Выберите ячейку С3;
Выполните команду основного меню Правка – Очистить или нажмите Delete на клавиатуре;
Внимание! Компьютер автоматически пересчитывает сумму в ячейке С9!!!

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

11. Введите текст «Общее время в пути» в ячейку D9.

12. Вычислите общее время в пути.

13. Оформите таблицу цветом и выделите границы таблицы.

Самостоятельная работа

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

Выбранный для просмотра документ Excel пр.р. 4.docx

Библиотека
материалов

Практическая работа 4

"Ссылки. Встроенные функции MS Excel".

Выполнив задания этой темы, вы научитесь:

Выполнять операции по копированию, перемещению и автозаполнению отдельных ячеек и диапазонов.

Различать виды ссылок (абсолютная, относительная, смешанная)

Использовать в расчетах встроенные математические и статистические функции Excel.

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

Таблица. Встроенные функции Excel

* Записывается без аргументов.

Таблица . Виды ссылок

Задание.

1. Заданы стоимость 1 кВт./ч. электроэнергии и показания счетчика за предыдущий и текущий месяцы. Необходимо вычислить расход электроэнергии за прошедший период и стоимость израсходованной электроэнергии.

Технология работы:

1. Выровняйте текст в ячейках. Выделите ячейки А3:Е3. Главная - Формат –Формат ячейки – Выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам.

2. В ячейку А4 введите: Кв. 1, в ячейку А5 введите: Кв. 2. Выделите ячейки А4:А5 и с помощью маркера автозаполнения заполните нумерацию квартир по 7 включительно.

5. Заполните ячейки B4:C10 по рисунку.

6. В ячейку D4 введите формулу для нахождения расхода эл/энергии. И заполните строки ниже с помощью маркера автозаполнения.

7. В ячейку E4 введите формулу для нахождения стоимости эл/энергии =D4*$B$1 . И заполните строки ниже с помощью маркера автозаполнения.

Обратите внимание!
При автозаполнении адрес ячейки B1 не меняется,
т.к. установлена абсолютная ссылка.

8. В ячейке А11 введите текст «Статистические данные» выделите ячейки A11:B11 и щелкните на панели инструментов кнопку «Объединить и поместить в центре».

9. В ячейках A12:A15 введите текст, указанный на рисунке.

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

11. Аналогично функции задаются и в ячейках B13:B15.

12. Расчеты вы выполняли на Листе 1, переименуйте его в Электроэнергию.

Самостоятельная работа

Упражнение1:

Рассчитайте свой возраст, начиная с текущего года и по 2030 год, используя маркер автозаполнения. Год вашего рождения является абсолютной ссылкой. Расчеты выполняйте на Листе 2. Лист 2 переименуйте в Возраст.

Упражнение 2: Создайте таблицу по образцу. В ячейках I 5: L 12 и D 13: L 14 должны быть формулы: СРЗНАЧ, СЧЁТЕСЛИ, МАХ, МИН. Ячейки B 3: H 12 заполняются информацией вами.

Выбранный для просмотра документ Excel пр.р. 5.docx

Библиотека
материалов

Практическая работа 5

Выполнив задания этой темы, вы научитесь:

 Технологии создания табличного документа;

 Присваивать тип к используемым данным;

 Созданию формулы и правилам изменения ссылок в них;

 Использовать встроенные статистических функции Excel для расчетов.

Задание 1. Рассчитать количество прожитых дней.

Технология работы:

1. Запустить приложение Excel.

2. В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.97). Зафиксируйте ввод данных.

3. Просмотреть различные форматы представления даты (Главная – Формат ячейки – Другие числовые форматы - Дата) . Перевести дату в тип ЧЧ.ММ.ГГГГ. Пример, 14.03.2001

4. Рассмотрите несколько типов форматов даты в ячейке А1.

5. В ячейку A2 ввести сегодняшнюю дату.

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

Задание 2. Возраст учащихся. По заданному списку учащихся и даты их рождения. Определить, кто родился раньше (позже), определить кто самый старший (младший).

Технология работы:

1. Получите файл Возраст. По локальной сети: Откройте папку Сетевое окружение– Boss –Общие документы– 9 класс, найдите файл Возраст. Скопируйте его любым известным вам способом или скачайте с этой страницы внизу приложения.

2. Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900 . Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип.

3. Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21) ;

4. Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21) ;

5. Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21) ;

6. Определим самого старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21) .

Самостоятельная работа:
Задача. Произведите необходимые расчеты роста учеников в разных единицах измерения.

Выбранный для просмотра документ Excel пр.р. 6.docx

Библиотека
материалов

Практическая работа 6

«MS Excel. Статистические функции» Часть II.

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

Решение:
Заполним таблицу исходными данными и проведем необходимые расчеты. Обратите внимание на формат значений в ячейках "Средний балл" (числовой) и "Дата рождения" (дата)

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

=ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25)

Прокомментируем ее. Из сегодняшней даты вычитается дата рождения ученика. Таким образом, получаем полное число дней, прошедших с рождения ученика. Разделив это количество на 365,25 (реальное количество дней в году, 0,25 дня для обычного года компенсируется високосным годом), получаем полное количество лет ученика; наконец, выделив целую часть, - возраст ученика.

Является ли девочка отличницей, определяется формулой (на примере ячейки H4):

=ЕСЛИ(И(D4=5;F4="ж");1;0)

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

=СУММЕСЛИ(F4:F15;"ж";D4:D15)/СЧЁТЕСЛИ(F4:F15;"ж")

Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию. Таким образом и получаем требуемое.
Для подсчета доли отличниц среди всех девочек отнесем количество девочек-отличниц к общему количеству девочек (здесь и воспользуемся набором значений из одной из вспомогательных колонок):

=СУММ(H4:H15)/СЧЁТЕСЛИ(F4:F15;"ж")

Наконец, определим отличие средних баллов разновозрастных детей (воспользуемся в расчетах вспомогательной колонкой Возраст ):

=ABS(СУММЕСЛИ(G4:G15;15;D4:D15)/СЧЁТЕСЛИ(G4:G15;15)-
СУММЕСЛИ(G4:G15;16;D4:D15)/СЧЁТЕСЛИ(G4:G15;16))

Обратите внимание на то, что формат данных в ячейках G18:G20 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.

Выбранный для просмотра документ Excel пр.р. 7.docx

Библиотека
материалов

Практическая работа 7

«Создание диаграмм средствами MS Excel»

Выполнив задания этой темы, вы научитесь:

 Выполнять операции по созданию диаграмм на основе введенных в таблицу данных;

 Редактировать данные диаграммы, ее тип и оформление.

Что собой представляет диаграмма. Диаграмма предназначена для графического представления данных. Для отображения числовых данных, введенных в ячейки таблицы, используются линии, полосы, столбцы, сектора и другие визуальные элементы. Вид диаграммы зависит от её типа. Все диаграммы, за исключением круговой, имеют две оси: горизонтальную – ось категорий и вертикальную – ось значений. При создании объёмных диаграмм добавляется третья ось – ось рядов. Часто диаграмма содержит такие элементы, как сетка, заголовки и легенда. Линии сетки являются продолжением делений, находящихся на осях, заголовки используются для пояснений отдельных элементов диаграммы и характера представленных на ней данных, легенда помогает идентифицировать ряды данных, представленные на диаграмме. Добавлять диаграммы можно двумя способами: внедрять их в текущий рабочий лист и добавлять отдельный лист диаграммы. В том случае, если интерес представляет сама диаграмма, то она размещается на отдельном листе. Если же нужно одновременно просматривать диаграмму и данные, на основе которых она была построена, то тогда создаётся внедрённая диаграмма.

Диаграмма сохраняется и печатается вместе с рабочей книгой.

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

Задача: С помощью электронной таблицы построить график функции Y=3,5x–5. Где X принимает значения от –6 до 6 с шагом 1.

Технология работы:

1. Запустите табличный процессор Excel.

2. В ячейку A1 введите «Х», в ячейку В1 введите «Y».

3. Выделите диапазон ячеек A1:B1 выровняйте текст в ячейках по центру.

4. В ячейку A2 введите число –6, а в ячейку A3 введите –5. Заполните с помощью маркера автозаполнения ячейки ниже до параметра 6.

5. В ячейке B2 введите формулу: =3,5*A2–5. Маркером автозаполнения распространите эту формулу до конца параметров данных.

6. Выделите всю созданную вами таблицу целиком и задайте ей внешние и внутренние границы.

7. Выделите заголовок таблицы и примените заливку внутренней области .

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

9. Выделите таблицу целиком. Выберите на панели меню Вставка - Диаграмма , Тип: точечная, Вид: Точечная с гладкими кривыми.

10. Переместите диаграмму под таблицу.

Самостоятельная работа:

Постройте график функции у= sin (x )/ x на отрезке [-10;10] с шагом 0,5.

Вывести на экран график функции: а) у=х; б) у=х 3 ; в) у=-х на отрезке [-15;15] с шагом 1.

Откройте файл "Города" (зайдите в папку сетевая - 9 класс-Города).

Посчитайте стоимость разговора без скидки (столбец D) и стоимость разговора с учетом скидки (столбец F).

Для нагладного представления постройте две круговые диаграммы. (1- диаграмма стоимости разговора без скидки; 2- диагамма стоимости разговора со скидкой).

Выбранный для просмотра документ Excel пр.р. 8.docx

Библиотека
материалов

Практическая работа 8

ПОСТРОЕНИЕ ГРАФИКОВ И РИСУНКОВ СРЕДСТВАМИ MS EXCEL

1. Построение рисунка «ЗОНТИК»

Приведены функции, графики которых участвуют в этом изображении:

у1= -1/18х 2 + 12, хÎ[-12;12]

y 2= -1/8х 2 +6, хÎ[-4;4]

y 3= -1/8(x +8) 2 + 6, хÎ[-12; -4]

y 4= -1/8(x -8) 2 + 6, хÎ

y 5= 2(x +3) 2 – 9, хÎ[-4;0]

y 6=1.5(x +3) 2 – 10, хÎ[-4;0]

- Запустить MS EXCEL

· - В ячейке А1 внести обозначение переменной х

· - Заполнить диапазон ячеек А2:А26 числами с -12 до 12.

Последовательно для каждого графика функции будем вводить формулы. Для у1= -1/8х 2 + 12, хÎ[-12;12], для
y 2= -1/8х 2 +6, хÎ[-4;4] и т.д.

Порядок выполнения действий:

Устанавливаем курсор в ячейку В1 и вводим у1

В ячейку В2 вводим формулу =(-1/18)*А2^2 +12

Нажимаем Enter на клавиатуре

Автоматически происходит подсчет значения функции.

Растягиваем формулу до ячейки А26

Аналогично в ячейку С10 (т.к значение функции находим только на отрезке х от [-4;4]) вводим формулу для графика функции y 2= -1/8х 2 +6. И.Т.Д.

В результате должна получиться следующая ЭТ

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

Выделяем диапазон ячеек А1: G26

На панели инструментов выбираем меню Вставка → Диаграмма

В окне Мастера диаграмм выберите Точечная → Выбрать нужный вид→ Нажать Ok .

В результате должен получиться следующий рисунок:

Задание для индивидуальной работы:

Постройте графики функций в одной системе координат. х от -9 до 9 с шагом 1 . Получите рисунок.

1. «Очки»

2. «Кошка» Фильтрация (выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.

Фильтрация данных может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.

Для использования автофильтра нужно:

o установить курсор внутри таблицы;

o выбрать команду Данные - Фильтр - Автофильтр;

o раскрыть список столбца, по которому будет производиться выборка;

o выбрать значение или условие и задать критерий выборки в диалоговом окне Пользовательский автофильтр.

Для восстановления всех строк исходной таблицы нужно выбрать строку все в раскрывающемся списке фильтра или выбрать команду Данные - Фильтр - Отобразить все.

Для отмены режима фильтрации нужно установить курсор внутри таблицы и повторно выбрать команду меню Данные - Фильтр - Автофильтр (снять флажок).

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

Задание.

Создайте таблицу в соответствие с образцом, приведенным на рисунке. Сохраните ее под именем Sort.xls.

Технология выполнения задания:

1. Откройте документ Sort.xls

2.

3. Выполните команду меню Данные - Сортировка.

4. Выберите первый ключ сортировки "По возрастанию" (Все отделы в таблице расположатся по алфавиту).

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

5. Установите курсор-рамку внутри таблицы данных.

6. Выполните команду меню Данные - Фильтр

7. Снимите выделение в таблицы.

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

9. Щелкните по кнопке со стрелкой, появившейся в столбце Количество остатка . Раскроется список, по которому будет производиться выборка. Выберите строку Условие. Задайте условие: > 0. Нажмите ОК . Данные в таблице будут отфильтрованы.

10. Вместо полного списка товаров, мы получим список проданных на сегодняшний день товаров.

11. Фильтр можно усилить. Если дополнительно выбрать какой-нибудь отдел, то можно получить список неподанных товаров по отделу.

12. Для того, чтобы снова увидеть перечень всех непроданных товаров по всем отделам, нужно в списке "Отдел" выбрать критерий "Все".

13. Чтобы не запутаться в своих отчетах, вставьте дату, которая будет автоматически меняться в соответствии с системным временем компьютера Формулы – Вставить функцию - Дата и время - Сегодня .

Самостоятельная работа

«MS Excel. Статистические функции»

1 задание (общее)(2 балла).

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

2.1 задание(2 балла).

Четверо друзей путешествуют на трех видах транспорта: поезде, самолете и пароходе. Николай проплыл 150 км на пароходе, проехал 140 км на поезде и пролетел 1100 км на самолете. Василий проплыл на пароходе 200 км, проехал на поезде 220 км и пролетел на самолете 1160 км. Анатолий пролетел на самолете 1200 км, проехал поездом 110 км и проплыл на пароходе 125 км. Мария проехала на поезде 130 км, пролетела на самолете 1500 км и проплыла на пароходе 160 км.
Построить на основе вышеперечисленных данных электронную таблицу.

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

Вычислить общее количество километров, которое ребята проехали на поезде, пролетели на самолете и проплыли на пароходе (на каждом виде транспорта по отдельности).

Вычислить суммарное количество километров всех друзей.

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

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

2.2 задание(2 балла).

Создайте таблицу “Озера Европы”, используя следующие данные по площади (кв. км) и наибольшей глубине (м): Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море 371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон 591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252; Меларен 1140 и 64. Определите самое большое и самое маленькое по площади озеро, самое глубокое и самое мелкое озеро.

2.3 задание(2 балла).

Создайте таблицу “Реки Европы”, используя следующие данные длины (км) и площади бассейна (тыс. кв. км): Волга 3688 и 1350; Дунай 2850 и 817; Рейн 1330 и 224; Эльба 1150 и 148; Висла 1090 и 198; Луара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79; Темза 340 и 15. Определите самую длинную и самую короткую реку, подсчитайте суммарную площадь бассейнов рек, среднюю протяженность рек европейской части России.

3 задание(2 балла).

В банке производится учет своевременности выплат кредитов, выданных нескольким организациям. Известна сумма кредита и сумма, уже выплаченная организацией. Для должников установлены штрафные санкции: если фирма выплатила кредит более чем на 70 процентов, то штраф составит 10 процентов от суммы задолженности, в противном случае штраф составит 15 процентов. Посчитать штраф для каждой организации, средний штраф, общее количество денег, которые банк собирается получить дополнительно. Определить средний штраф бюджетных организаций.

Найдите материал к любому уроку,

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

В этой статье

Что такое бизнес-аналитика?

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

Как общая сумма продаж всех продуктов в 2007 сравнивается с объемом продаж с 2006?

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

Сколько денег клиенты потратили на 35 в прошлый год и каким образом изменилось это поведение с течением времени?

Сколько продуктов было продано в двух определенных странах/регионах в этом месяце, в отличие от того же месяца прошлого года?

Для каждой группы возрастов клиентов Каково разделение рентабельности (как процент маржи, так и итог) по категориям товаров?

Поиск лучших и нижних продавцов, дистрибьюторов, поставщиков, клиентов, партнеров и клиентов.

Что такое аналитическая обработка в Интернете (OLAP)?

Базы данных OLAP (Online Analytical Processing) упрощают запросы бизнес-аналитики. OLAP - это технология баз данных, оптимизированная для запросов и отчетов, а не для обработки транзакций. Источник данных для OLAP - это оперативные базы данных обработки транзакций (OLTP), которые обычно хранятся в хранилищах данных. Данные OLAP извлекаются из этих исторических данных и объединяются в структуры, которые допускают сложный анализ. Данные OLAP также упорядочиваются иерархически и хранятся в кубах, а не в таблицах. Это сложная технология, использующая многомерные структуры для обеспечения быстрого доступа к данным для анализа. В этой Организации для отчета сводной таблицы или отчета сводной диаграммы можно легко отобразить сводные данные высокого уровня, такие как итоги продаж по всей стране или региону, а также отобразить сведения о сайтах, где продажи особенно велики или слабы.

Базы данных OLAP предназначены для ускорения загрузки данных. Поскольку OLAP-сервер, а не Microsoft Office Excel, вычисляет обобщенные значения, при создании или изменении отчета необходимо отправлять в Excel меньшие данные. Этот подход позволяет работать с большим объемом исходных данных, чем в случае, если данные были организованы в традиционной базе данных, где Excel извлекает все отдельные записи и вычисляет обобщенные значения.

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

В следующих разделах каждый компонент подробно описан ниже.

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

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

Member Элемент в иерархии, представляющий один или несколько вхождений данных. Элемент может быть как уникальным, так и неуникальным. Например, 2007 и 2008 представляют уникальные элементы на уровне года измерения времени, в то время как Январь представляет неуникальные элементы на уровне месяца, так как в измерении времени есть более одного января, так как в нем содержатся данные для более чем одного года.

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

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

Иерархия Логическая структура дерева, которая упорядочивает элементы измерения, так что у каждого элемента есть один родительский элемент и ноль или более дочерних элементов. Дочерний элемент - это член более ранней группы в иерархии, непосредственно связанный с текущим членом. Например, в иерархии времени, содержащей уровни квартал, месяц и день, Январь является дочерним элементом Qtr1. Родительский элемент - это член более низкого уровня в иерархии, непосредственно связанный с текущим членом. Родительским значением обычно является консолидация значений всех дочерних элементов. Например, в иерархии времени, содержащей уровни "квартал", "месяц" и "день", Qtr1 является родительским для января.

Уровень В иерархии данные могут быть организованы в более низкие и более высокие уровни детализации, такие как годы, кварталы, месяцы и дневные уровни в иерархии времени.

Функции OLAP в Excel

Получение данных OLAP Вы можете подключаться к источникам данных OLAP точно так же, как и к другим внешним источникам данных. Вы можете работать с базами данных, созданными с помощью служб Microsoft SQL Server OLAP версии 7,0, служб Microsoft SQL Server Analysis Services версии 2000 и Microsoft SQL Server Analysis Services версии 2005, серверных продуктов Microsoft OLAP. Excel также может работать с продуктами OLAP третьих лиц, которые совместимы с OLE-DB для OLAP.

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

Создание файлов куба для автономного использования Вы можете создать файл автономного куба (. cub) с подмножеством данных из базы данных сервера OLAP. Автономные файлы куба используются для работы с данными OLAP, если вы не подключены к сети. С помощью куба вы можете работать с большим объемом данных в отчете сводной таблицы или отчете сводной диаграммы, чем в противном случае, и ускорить получение данных. Вы можете создавать файлы Куба только в том случае, если вы используете поставщик OLAP, например Microsoft SQL Analysis Services версии 2005, которая поддерживает эту функцию.

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

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

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

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

Программные компоненты, необходимые для доступа к источникам данных OLAP

Поставщик OLAP Для настройки источников данных OLAP для Excel необходим один из указанных ниже провайдеров OLAP.

Поставщик Microsoft OLAP В Excel входит драйвер источника данных и клиентское программное обеспечение для доступа к базам данных, созданным с помощью служб Microsoft SQL Server olap версии 7,0, Microsoft SQL Server olap версии 2000 (8,0) и Microsoft SQL Server Analysis services версии 2005 (9,0).

Сторонние поставщики OLAP Для других продуктов OLAP необходимо установить дополнительные драйверы и клиентское программное обеспечение. Чтобы использовать возможности Excel для работы с данными OLAP, продукт стороннего поставщика должен соответствовать стандарту OLE-DB для OLAP и быть совместимым с Microsoft Office. Для получения сведений об установке и использовании стороннего поставщика OLAP обратитесь к системному администратору или поставщику продукта OLAP.

Базы данных сервера и файлы Куба Клиентское программное обеспечение Excel OLAP поддерживает соединения с двумя типами баз данных OLAP. Если база данных на сервере OLAP доступна в сети, вы можете получать из нее исходные данные прямо из нее. Если у вас есть автономный файл куба, который содержит данные OLAP или файл определение кубов, вы можете подключаться к этому файлу и получать из него исходные данные.

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

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

Различия в функциях OLAP и не-OLAP исходных данных

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

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

В отчетах, основанных на исходных данных OLAP, параметры поля фильтра отчета недоступны, фоновый запрос недоступен, а параметр оптимизации памяти недоступен.

Примечание: Параметр оптимизации памяти также недоступен для источников данных OLEDB и отчетов сводных таблиц, основанных на диапазоне ячеек.

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

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

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

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

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

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

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