Содержание

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

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

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

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

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

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

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). Этот метод принимает четыре параметра:

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

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

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

PT1.CubeFields(«[Measures].[Unit Sales]»).Orientation = xlDataField

[expert_bq id=»1570″]Недавно создавая файл со связанными списками столкнулся с проблемкой при обновлении записи в первой ячейке, от которой зависит список во второй, вторая ячейка остается неизменной, не обнуляет значения. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Функция довольно удобная, не есть у нее один большой недостаток ячейки для сцепки нужно указывать по одной, выделение диапазона не работает или работает некорректно. Читать далее «Как соединить текст из нескольких ячеек Excel не потеряв информацию.»

Рубрика Макросы и VBA. VBA (Visual Basic for Applications)

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

Возможно ли в Ecxel создать такую формулу? Сумма данных в зависимости от значения в ячейке

Похожее:

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

Похожее:

[expert_bq id=»1570″]При заполнении формы удобно использовать клавишу Tab для перехода к следующему полю и сочетание Shift Tab для перехода к предыдущему. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] На основе Excel сформирована база заказов. В ней фигурирует номер заказала, артикул и наименование товара, его цена, количество в заказе и размер скидки. Остальные поля рассчитываются с помощью простых формул — это сумма заказа, сумма скидки и итоговая сумма к оплате.

Простая форма ввода данных в Excel без надстроек и VBA | Андрей Сухов | Яндекс Дзен

Если необходимо перебирать ячейки в порядке A1, A2, A3, B1, . а не A1, B1, C1, A2, . то вы можете это организовать при помощи 2-х циклов For . Обратите внимание, как мы узнали количество столбцов ( parRange.Columns.Count ) и строк ( parRange.Rows.Count ) в диапазоне, а также на использование свойства Cells . Тут Cells относится к листу и никак не связано с диапазоном parRange .

Работа со столбцами

Синтаксическая форма	Комментарии по использованию
Range (» B:B «) или [ B:B ]	Колонка B текущего листа.
Range (» B1:B1048576 «) или [ B1:B1048576 ]	То же самое, но с указанием номеров строк, чтобы вы понимали, что это тождественные формы. 2^20=1048576 — максимальный номер строки на листе.
Columns (» B:B «)	То же самое через свойство Columns . Параметр — текстовая строка.
Columns(2)	То же самое. Параметр — числовой индекс столбца. «A» -> 1, «B» -> 2, и т.д.

Синтаксическая форма	Комментарии по использованию
Range (» A1:XFD1048576 «) или [ A1:XFD1048576 ]	Диапазон размером во всё адресное пространство листа Excel. Воспринимайте эту таблицу лишь как теорию — так работать с листами вам не придётся — слишком большое количество ячеек. Даже современные компьютеры не смогут помочь Excel быстро работать с такими массивами информации. Тут проблема больше даже в самом приложении.
Range (» 1:1048576 «) или [ 1:1048576 ]	То же самое, но через строки.
Range (» A:XFD «) или [ A:XFD ]	Аналогично — через адреса столбцов.
Cells	Свойство Cells включает в себя ВСЕ ячейки.
Rows	Все строки листа.
Columns	Все столбцы листа.

[expert_bq id=»1570″]Обратите внимание на то, что типы данных, перечисленные выше, можно сгруппировать, согласно разновидности данных, которые можно помещать в них. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Конструкция With встречалась вам раньше, но обратите внимание, как производился выбор объекта для помещения в эту конструкцию. Сложно сказать, какой объект «тяжелее» для VBA – форма или лист Excel. Но лист Excel мы легко смогли кэшировать в переменной – для формы это было бы сложнее, поэтому кэшируем лист.

Занятие 7. Вывод данных

Приведенный в статье макрос является альтернативой опции «колонки» используемой в настройках «разметки страницы» программы Word . Читать далее «Как поделить таблицу Excel на две колонки для печати на одном листе.»

Синтаксическая форма	Комментарии по использованию
Range (» B:B «) или [ B:B ]	Колонка B текущего листа.
Range (» B1:B1048576 «) или [ B1:B1048576 ]	То же самое, но с указанием номеров строк, чтобы вы понимали, что это тождественные формы. 2^20=1048576 — максимальный номер строки на листе.
Columns (» B:B «)	То же самое через свойство Columns . Параметр — текстовая строка.
Columns(2)	То же самое. Параметр — числовой индекс столбца. «A» -> 1, «B» -> 2, и т.д.