Afalina XL Report. Анализ данных
Afalina XL Report. Анализ данных
Продолжение. Начало в КГ №№ 27, 28 .
Для областей произвольного формата доступны, в основном, только простые опции области. Это не ограничение XL Report — это ограничение Excel. Дело в том, что все возможности Excel, касающиеся групповых вычислений, анализа данных и их фильтрации, основаны именно на областях-списках. Вы должны четко понимать, что в XL Report сортирует, суммирует, группирует и фильтрует сам Excel. Поэтому необходимые ограничения на дизайн отчетов есть, и это ограничения Excel. Достаточно помнить следующее. Если вам понадобится получить сумму по столбцу, включить автофильтр или получить промежуточные итоги, вам придется создавать и использовать области этого типа. В этой части статьи мы часто будем ссылаться на документацию и электронную справку Excel.
Сортировка области-списка. Какие же дополнительные действия можно производить над областью-списком? Во-первых, область можно сортировать по столбцам. Это указывается опцией столбца Sort в служебной ячейке нужных столбцов. Откройте форму из предыдущего примера с областью-списком. В шаблоне отчета из этого проекта удалите все опции в области OrdersRange. В служебной ячейке столбца PaymentMethod напишите опцию Sort. Сохраните шаблон, закройте его, постройте отчет. Ранее эта область никак не сортировалась. Первичный ключ таблицы Orders обеспечивал упорядочивание данных по умолчанию. В готовом отчете вы должны увидеть, что область отсортирована по столбцу PaymentMethod. Вы можете включить сортировку по убыванию, дописав к опции Sort опцию столбца Desc, разделив их точкой с запятой. В готовом отчете вы обнаружите после этого сортировку по убыванию. Сортировка в XL Report имеет свои ограничения. Во-первых, область может быть отсортирована одновременно максимум по трем столбцам. Это ограничение метода Sort объекта Range. Во-вторых, XL Report сортирует область только слева направо. Если вы укажете в шаблоне больше трех сортируемых столбцов в области-списке, то область будет отсортирована по первым (слева) трем столбцам. Проектируйте области-списки в своих будущих отчетах, не забывая об этих ограничениях.
Итоги по столбцу области-списка. В предыдущей статье было показано, как получить сумму по столбцу области-списка. Вы наверняка запомнили, как выглядела та формула, которая была автоматически вставлена XL Report в ячейку под столбцом области. Вот эта формула: "=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(...". Функция листа "Промежуточные.Итоги" на самом деле позволяет получить по указанному диапазону ячеек не только сумму, но и среднее, минимум, максимум и так далее. XL Report поддерживает все допустимые этой функцией виды итогов. Вот опции, которые вы можете использовать для получения различных итогов по столбцу: Sum — отобразит сумму по столбцу; Count — количество значений в столбце; CountNums — количество не пустых значений в столбце; Avg или Average — среднее значение по столбцу; Max — максимальное значение в столбце; Min — минимальное значение в столбце; Product — произведение по столбцу; StDev — стандартное отклонение; StDevP — стандартное отклонение по генеральной совокупности; Var — дисперсия; VarP — дисперсия для генеральной совокупности.
Подробное описание всех функций вы найдете в электронной справке Excel. По столбцу можно получить только один итог. Попробуйте написать в шаблон предыдущего примера несколько опций итогов, разделив их символом ";". В качестве формулы итога в этом случае будет использована только последняя указанная функция. Обратите внимание на "Итого" в ячейке, расположенной слева от итоговой. В служебной строке в случае, если она не удаляется, вы можете использовать не только константы, но и формулы Excel.
Промежуточные итоги в области-списке. Все перечисленные выше опции промежуточных итогов можно использовать совместно с опцией Group. Опция Group должна находиться в столбцах, по которым вы хотите сгруппировать данные, получив указанные промежуточные итоги. Вот пример. Откройте шаблон из предыдущего примера и в служебных ячейках столбцов Items total и Amount paid укажите опцию Sum. А в служебной ячейке столбца Payment method укажите опцию Group. Сохраните шаблон, закройте его и запустите отчет. На рисунке вы видите, что промежуточные итоги области-списка сгруппированы по столбцу Payment method. Вы можете указывать одновременно до 16 полей, по которым можно группировать промежуточные итоги. В этом же шаблоне добавьте опцию Group в столбец Sale date. В готовом шаблоне вы увидите, что, во-первых, промежуточные итоги сгруппированы по Sale date и Payment method. Во-вторых, область OrdersRange в готовом отчете содержит как оригинальные данные, так и строки с промежуточными итогами. А в-третьих — область группируется слева направо. То есть рассчитываются итоги по крайнему левому столбцу группировки, потом по столбцу, расположенному правее его, и т.д. В отличие от общих итогов по столбцу области, допустимо применять несколько сгруппированных промежуточных итогов. В тот же шаблон вы можете добавить опцию Avg для столбца Amount paid и посмотреть на готовый отчет. По этому столбцу вы увидите сумму и "среднее".
Группировка промежуточных итогов предполагает предварительную сортировку — в нашем случае по столбцам Sale date и Payment method. По умолчанию XL Report сам сортирует предварительно все такие поля. Но на это уходит дополнительное процессорное время. Поэтому, если вы уверены в том, что данные попадут в отчет заранее отсортированными, напишите опцию области GroupNoSort, которая отключит ненужную в этом случае сортировку. Напоминаем вам, что опции области помещаются в левую нижнюю ячейку.
Скрытие детальной информации. Если к опции Group предыдущего примера добавить параметр Collapse и посмотреть на готовый отчет, то можно обнаружить, что дерево промежуточных итогов свернулось до уровня итогов по Payment method. Вот результат работы этой опции. Часто необходимо видеть сразу сгруппированные итоги, которые можно затем раскрыть, воспользовавшись деревом групп, расположенным слева номеров строк листа. На рисунке 7 вы видите результат работы опции Group\Collapse. При использовании Collapse вы должны помнить, что XL Report сворачивает дерево промежуточных итогов по самому правому столбцу с этой опцией.
Сводные таблицы в Excel позволяют манипулировать данными различной сложности и разного объема. Сводная таблица — это очень хорошее средство упорядочивания информации. Использование таких таблиц позволяет простому пользователю без написания проводить анализ данных фактически только с помощью мыши, без написания сложного кода. Сводные таблицы, в отличие от реляционных, являются многомерными и состоят из областей строк, столбцов, листов и областей данных. Поля области страниц добавляют при этом дополнительные измерения к исходной таблице. На следующем рисунке показано, как можно вывести информацию для анализа информации о заказах, выписанных заказчикам.
Если в предыдущих версиях XLReport'а для создания сводных таблиц приходилось использовать VBA, то теперь в шаблоне достаточно создать область-список, где опциями сводной таблицы будут описаны и поля сводной таблицы, и их форматы. При формировании отчета данные из набора будут перенесены в область-список, а XLReport на основе указанных опций создаст сводную таблицу. Проектируя шаблон области, вы можете определить все типы полей сводной таблицы, применить любое форматирование этих полей, определить типы итогов по любым полям и их внешний вид. Приведем пример простой сводной таблицы.
Во-первых, сводные таблицы в Excel строятся на основе двумерной таблицы, будь она результатом запроса к базе данных или списком на одном из листов книги. Поэтому данные из ваших приложений для сводных таблиц необходимо "поставлять" в Excel в виде одного набора. Чаще это результат запроса к базе данных. В DBDEMOS есть две таблицы: "Customer.DB" и "Orders.DB". Объединение этих таблиц по полю CustNo позволит нам получить массив счетов, приходящихся на каждого заказчика. Вот SQL-запрос, который вернет нам эти данные: SELECT * FROM Customer, Orders WHERE Customer. CustNo = Orders.CustNo. Для примера создадим новый проект с одной формой. На форму поместим только один набор данных (TQuery), в свойство SQL которого напишем этот запрос. Назовем этот компонент "qryOrders". Чтобы отобразить все записи этого набора, на форму добавим экземпляр TDataSource, сетку и навигатор. Поместим на форму один компонент TxlReport, добавим в его коллекцию DataSources один элемент и свяжем его с таблицей qryOrders. Добавим кнопки Report и Template c обработчиками, вызывающими, соответственно, методы Report и Edit последнего компонента. В свойство компонента xlReport XLSTemplate напишем имя предполагаемого шаблона — "tPivot1.xls".
Создадим шаблон для этого отчета, который должен иметь вид как на рисунке. В левую нижнюю ячейку созданной нами области напишем опцию Pivot\Name = OrdersPivot\ Dst = Pivot!R4C2\ColumnGrand. Эта опция укажет XL Report, что по области будет строиться сводная таблица с именем OrdersPivot, которая будет размещена на листе Pivot начиная с ячейки R4C2. А параметр ColumnGrand позволит включить итоги по столбцам полученной сводной таблицы. В служебной ячейке столбцов Payment method, OrderNo, Ship date и Tax rate укажем опцию Row. Опция Row определяет поля области строк сводной таблицы (RowFields). Для того чтобы получить итоги, сгруппированные по методу оплаты счетов, к опции Row в поле Payment method добавим опцию Sum. Для полей Amount paid и Items total укажем опцию Data (поля области данных сводной таблицы — DataFields). В опции поля Company добавим опцию Page (поле области страниц).
При проектировании шаблона, кроме распределения опций между столбцами, не забудьте указать различные форматы ячейкам области (в том числе для ячеек с датами и числами). Более того, мы отформатировали служебные ячейки с опциями столбцов, подразумевая, что именно с этим форматом мы получим промежуточные итоги в сводной таблице. А для поля Payment method мы выделили ячейку с опциями цветом. На рис.8 вы видите получившийся у нас шаблон области. После построения отчета мы должны сделать лист Pivot активным. Для этого можно воспользоваться свойством компонента TxlRe-port.ActiveSheet. В случае, если отчет многолистовой, в этом свойстве указывается имя листа, который необходимо сделать активным после построения отчета.
Чтобы придать сводным таблицам наиболее читаемый вид, форматируют область-источник. При создании сводной таблицы XL Report переносит форматы ячеек с формулами полей в ячейки сводной таблицы этих полей. Итоги по этим полям автоматически приобретают форматы, определенные для ячеек с опциями столбцов. В предыдущем примере для выделения итогов по полю Company мы указали желтый цвет фона для ячейки с опциями этого столбца в области-источнике.
Приведем еще один пример со сводной таблицей. Используем для этого тот же шаблон. Вы можете не создавать нового проекта. Достаточно лишь отредактировать шаблон отчета. Задача будущего отчета в том, чтобы показать минимальную и максимальную сумму выписанного счета, а также сумму по методу оплаты. На рисунке выше мы показали распределение опций в шаблоне и то, как мы отформатировали ячейки области-источника.
При проектировании областей-источников для сводных таблиц необходимо учитывать ряд ограничений, которыми они обладают. С ограничениями сводных таблиц вашей версии Excel вы можете ознакомиться в электронной справке Excel. Более точно эти ограничения и порядок их расчета описаны в MSDN статья Q211517 для Excel 2000 и Q157486 для Excel 97.
Кросс-таблицы. Как вы убедились, для построения сводных таблиц теперь достаточно указать в области-списке опции сводной таблицы. После этого названная область становится источником данных для сводной таблицы. Опция Pivot — это первая опция, на которую обращает внимание XL Report при анализе ячеек области-списка. Ранее вы встречались только с простыми опциями, состоящими из одного слова. Эта опция отличается от других тем, что может иметь несколько аргументов.
Вот ее синтаксис: Pivot \ Name=Pivot TableName \ [Dst= Destination] \ [DataTo Rows] \ [RowGrand] \ [ColumnGrand] \ [NoPreserveFormatting] где: Name = PivotTableName — имя сводной таблицы, допустимое в Excel. Dst= Destination — ячейка, в которую необходимо поместить левый верхний угол сводной таблицы. Если Destination не указано, то сводная таблица автоматически размещается на новом листе книги. Обратите внимание, что аргументы этой опции должны быть отделены друг от друга символом "\". DataToRows — опция, которая позволяет поместить поля данных в строках таблицы.
По умолчанию данные размещаются в столбцах. RowGrand — позволяет включить в сводной таблице итоги по строкам. ColumnGrand — включает итоги по столбцам сводной таблицы. NoPreserve Formatting — позволяет строить сводную таблицу без сохранения форматирования исходной области, что уменьшает время построения отчета. Вот несколько примеров правильного написания опции Pivot: "Pivot\Name = Pivot1\Dst = Totals!A1" — будет создана сводная таблица с именем Pivot1; таблица будет размещена на листе Totals начиная с ячейки A1; "Pivot\Name = Pivot25\Data ToRows" — будет создана сводная таблица Pivot25, поля данных которой разместятся в столбцах; "Pivot\Name = Pivot25\Dst = Totals!R1 C1\ RowGrand" — в сводной таблице Pivot25 включены итоги по строкам данных; "Pivot\Name = Pivot25\Co-lumnGrand" — в сводной таблице Pivot25 включены итоги по столбцам. Из предыдущего примера вы наверняка заметили, что поля во все области сводной таблицы добавляются в порядке их следования в шаблоне (слева направо). Поэтому при проектировании области-списка, по которой будет построена сводная таблица, нужно придерживаться одного простого правила: выстраивайте столбцы в том порядке, в каком бы вы хотели видеть их в сводной таблице.
Наименования полей для сводной таблицы берутся из строки над областью-списком — заголовок таблицы-источника. Будьте внимательны при создании этого заголовка, так как существуют некоторые ограничения по наименованию полей в сводных таблицах. Например, Excel 97 не всегда корректно работает с полями, в имени которых есть символ перевода строки. Впрочем, Excel 2000 и 2002 ведет себя не многим лучше. Однако с помощью сводных таблиц не составит никакого труда создавать в отчетах сложнейшие кросс-таблицы.
Для примера мы взяли предыдущий проект и изменили опции в исходной области. Для получения итогов по столбцам и строкам сводной таблицы мы добавили к этой опции два параметра: ColumnGrand и RowGrand. Далее мы поместили в область строк поле Company (с суммой в итогах) и поля OrderNo, Ship date, Tax rate. В область столбцов — поле Payment method. В области данных по-прежнему остались поля Items total и Amount paid. Готовый отчет с описанной таким образом сводной таблицей будет кросс-таблицей, по осям которой расположатся поля Company и PaymentMethod. В этом примере мы использовали опцию Sum для получения итогов по полю Company. В аналогичных ситуациях вы можете использовать любые опции промежуточных итогов, доступные в XL Report. Вы можете получать в качестве итогов максимальные или минимальные значения, отклонения и др. Вы также можете получать несколько итогов, перечисляя через точку с запятой в служебной ячейке столбца необходимые опции промежуточных итогов (рис.8а).
Но XL Report 4 поддерживает более мощное средство работы со сводными таблицами. Теперь вы можете разместить одну или несколько сводных таблиц прямо в шаблоне отчета, воспользовавшись удобством мастера сводных таблиц Excel и практически всеми возможностями в их оформлении и структурировании.
Приведем пример. В качестве отправной точки мы использовали шаблон первого примера со сводной таблицей с исходной областью SourceRange на листе Sheet1. Прямо в шаблоне мы поместили статическую сводную таблицу, построенную по этой области. На следующих рисунках показаны шаги построения этой таблицы. Во-первых, необходимо выделить для сводной таблицы исходную область. Она не идентична области SourceRange, так как включает только строку с данными и заголовок над ней. Обратите внимание, как выделена исходная область на рисунке (рис.9).
Далее мы поместили сводную таблицу на отдельном листе PivotSheet и распределили ее поля в области строк, столбцов и данных. Мы отформатировали поля сводной таблицы, а также их заголовки. Обратите внимание на то, что сводная таблица содержит только поля с ошибкой #NAME, что вполне естественно, так как исходная область содержит только формулы полей XL Report:
В завершение мы назвали сводную таблицу именем PivotTable1, а в качестве опции исходной области указали "PIVOT\Refresh=PivotSheet!Pivot Table1". Параметр Refresh опции области PIVOT требует указания списка сводных таблиц, статически размещенных в шаблоне и построенных по области. После переноса данных все сводные таблицы, перечисленные в этом списке, будут обновлены. В этом списке имена сводных таблиц указываются вместе с именем листа, на котором они размещены, и отделяются друг от друга запятыми. То есть по одной области вы можете построить несколько сводных таблиц. Вот простой пример. Мы добавили еще одну статическую сводную таблицу в шаблон, поместив ее на листе Pivot Sheet2, и назвали ее "Second PivotTable". Соответственно, опция PIVOT была изменена до "PIVOT\ Refresh = PivotSheet!PivotTable1, Pivot Sheet1!Second PivotTable". Обратите внимание на то, что имена листов и таблиц, содержащих пробелы, не требуют заключения в одиночные кавычки, как это принято во внешних ссылках Excel.
Счастливые обладатели Excel 2000 и выше могут попробовать разместить в шаблоне и статические сводные диаграммы (Pivot Chart). После переноса данных в исходную область будут обновлены и сводные таблицы, и сводные диаграммы.
При работе со сводными таблицами следует учитывать следующие замечания. Используя опции сводной таблицы в области-списке, можно автоматически построить сводную таблицу по области. Первое, что необходимо для создания сводной таблицы, — указать опцию области Pivot. Эта опция имеет несколько аргументов, позволяющих определить имя сводной таблицы, место ее будущего расположения в книге, вариант расположения полей данных в области данных. Если для будущей сводной таблицы не указана ячейка расположения, то XL Report размещает сводную таблицу на новом листе книги. Для определения полей в область строк достаточно указать для них опцию Row, для области страниц — Page, столбцов — Column, данных — Data. Совместно с этими опциями можно указать опции промежуточных итогов для этих полей. При этом доступны все функции промежуточных итогов. В сводных таблицах можно использовать несколько функций промежуточных итогов на одно поле области строк или столбцов. Наименования полей для сводной таблицы XL Report берет из ячеек строки над областью — из заголовка таблицы. Поля попадают в области сводной таблицы в порядке слева направо. При проектировании области-списка удобно располагать сначала поля страниц, затем строк, столбцов и данных. Цвет фона, шрифт и числовой формат ячеек области-списка переносится в ячейки полей сводной таблицы. Форматы ячеек опций столбцов переносятся в ячейки промежуточных итогов сводной таблицы. В свойстве ActiveSheet можно указать имя листа, который будет активным после построения отчета. Начиная с build 115 XL Report 4 поддерживает создание сводных таблиц прямо в шаблоне. Для быстрого создания сводных таблиц можно использовать параметр No-PreserveFormatting.
Вызов VBA. В этой части статьи рассмотрим возможность XL Report'а вызывать опубликованные в книге-шаблоне процедуры VBA. Эти процедуры помещаются в отдельный модуль или несколько модулей и объявляются как public. У классов TxlReport и TxlDataSource существуют свойства MacroBefore и MacroAfter, в которых указывают полное наименование процедур VBA.
MacroBefore. Процедура, указанная в этом свойстве, вызывается перед построением отчета (перед переносом данных у TxlDataSource).
МacroAfter. Процедура, указанная в этом свойстве, вызывается после построения отчета (после переноса данных у TxlDataSource). Событие On-Macro у этих двух классов служит для передачи параметров в вызываемые процедуры VBA.
Иногда всех описанных выше возможностей XL Report бывает недостаточно для решения задач, которые ставит реальная жизнь. В таком случае мы прибегаем к помощи VBA. Этот инструмент офисного программирования по праву имеет пальму первенства среди подобных систем. Существует целая область программирования, называемая офисным программированием, и масса книг и сайтов, посвященных этой теме и VBA. Используя VBA, вы получаете в свои руки всю мощь Excel. Классы TxlReport и TxlDataSource (элемент свойства-коллекции DataSources) имеют по два published-свойства: MacroAfter и MacroBefore. Эти свойства предназначены для вызова опубликованных процедур VBA. Их строковые значения должны содержать полные названия опубликованных процедур VBA, которые сохранены вместе с книгой-шаблоном. В своей работе мы применяем эти макросы чаще всего для построения в Excel диаграмм. Но этим не ограничивается круг применения макросов. Вы можете делать в этих макросах все что угодно. Точнее, все, на что способен VBA в Excel. А способен он на очень и очень многое. Для вызова VBA из XL Report при создании шаблона необходимо добавить модуль в книгу и записать в него public-процедуру. Если вы потом укажете имя этого модуля и, через точку, имя этой процедуры в одном из свойств MacroBefore или MacroAfter, то при создании отчета эта процедура будет автоматически вызвана. К примеру, ниже показан результат работы стандартного Hello, World:
Имейте в виду, что, если у вас макросы отключены, ваш Excel может сигнализировать об ошибке во время прогона нижеследующих примеров.
Простой вызов. Возьмем проект из самого первого примера документа. Откроем шаблон отчета в этом проекте. Добавим в книгу модуль и public-процедуру в этот модуль с именем HelloWorld. В ней сделаем вызов MsgBox с параметром-строкой Hello XL Report!. Сохраним шаблон и закроем его. В свойство Macro After экземпляра TxlReport введем полное имя этой процедуры — Mo-dule1.HelloWorld. Воспользовавшись контекстным меню компонента, вызовем отчет прямо из IDE Delphi. Процедура выполнилась. Как вы наверняка догадались, макрос, указанный в свойстве MacroBefore, выполняется перед построением всего отчета (для TxlReport) или перед переносом данных из набора (для TxlDataSource). Макросы в MacroAfter — соответственно, после этих действий. В макросах доступно многое. Как же будут доступны данные из вашего приложения в этих макросах? Все просто! Для NoRange-наборов данные доступны в поименованных ячейках, а для Range-наборов — в поименованных областях.
Диаграммы Excel и VBA. Попробуем построить такую диаграмму. На ней показаны итоги заказов всех покупателей, сумма которых более $70.000. Для этого создадим следующее тестовое приложение. Во-первых, нам необходимо создать набор данных. Возьмем за основу исходные тексты примера с первой сводной таблицей. Для qryAll в свойство SQL напишем следующий код:
SELECT c.Company, Sum(o. "ItemsTotal") as ItemsTo-tal,
Sum(o."AmountPaid") as AmountPaid
FROM Orders o, Customer c
WHERE c.CustNo = o.CustNo
GROUP BY c.Company ORDER BY c.Company
Этот код вернет нам названия всех компаний с просуммированными полями ItemsTotal и AmountPaid из таблицы счетов. Так как мы взяли за основу предыдущий пример, нам остается только изменить шаблон и написать в нем новый макрос. Форма шаблона показана на рис. 10.
Макрос для создания диаграммы мы поместили в один из модулей книги. Этот макрос создает и форматирует на новом листе диаграмму Excel по области AllRange. В этой процедуре интересна строка SrcRange. AutoFilter Field:=3, Criteria1:= "> 70000", Operator:=xlAnd, с помощью которой задается условие для созданного XL Report на эту область автофильтра. Этот макрос записан с помощью команды Excel "Записать макрос" и отредактирован для последующей работы с переменным набором AllRange.
Передача параметров в процедуры VBA. В предыдущем примере мы статически указали критерий отбора клиентов для вывода в диаграмму. Сделаем эту возможность динамической. Добавим на форму предыдущего примера поле редактирования с именем edCriteria. Исправим в шаблоне процедуру, добавив в ее объявление параметра Criteria as string и изменив код так, чтобы автофильтр реагировал на значение этого параметра. Необходимо сделать так, чтобы число, введенное в поле редактирования edCriteria, было передано в процедуру BuildChart. Для этого служит событие OnMacro у экземпляра TxlReport. Вот такой код мы написали в обработчике этого события:
procedure TfrmVBA3.xlRe-portMacro(Report: TxlRe-port;
const AMacroType: Txl MacroType; const AMacroNa-me: String;
var Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30: OleVariant);
begin
if AMacroName = 'Module1. BuildChart' then
Arg1 := edCriteria.Text;
end;
Такое огромное количество параметров диктуется синтаксисом метода Run объекта Application из Excel Type Library. В нашем же случае достаточно только первого параметра.
Денис "Denver" Мигачев, dtm@tut.by
Продолжение. Начало в КГ №№ 27, 28 .
Для областей произвольного формата доступны, в основном, только простые опции области. Это не ограничение XL Report — это ограничение Excel. Дело в том, что все возможности Excel, касающиеся групповых вычислений, анализа данных и их фильтрации, основаны именно на областях-списках. Вы должны четко понимать, что в XL Report сортирует, суммирует, группирует и фильтрует сам Excel. Поэтому необходимые ограничения на дизайн отчетов есть, и это ограничения Excel. Достаточно помнить следующее. Если вам понадобится получить сумму по столбцу, включить автофильтр или получить промежуточные итоги, вам придется создавать и использовать области этого типа. В этой части статьи мы часто будем ссылаться на документацию и электронную справку Excel.
Сортировка области-списка. Какие же дополнительные действия можно производить над областью-списком? Во-первых, область можно сортировать по столбцам. Это указывается опцией столбца Sort в служебной ячейке нужных столбцов. Откройте форму из предыдущего примера с областью-списком. В шаблоне отчета из этого проекта удалите все опции в области OrdersRange. В служебной ячейке столбца PaymentMethod напишите опцию Sort. Сохраните шаблон, закройте его, постройте отчет. Ранее эта область никак не сортировалась. Первичный ключ таблицы Orders обеспечивал упорядочивание данных по умолчанию. В готовом отчете вы должны увидеть, что область отсортирована по столбцу PaymentMethod. Вы можете включить сортировку по убыванию, дописав к опции Sort опцию столбца Desc, разделив их точкой с запятой. В готовом отчете вы обнаружите после этого сортировку по убыванию. Сортировка в XL Report имеет свои ограничения. Во-первых, область может быть отсортирована одновременно максимум по трем столбцам. Это ограничение метода Sort объекта Range. Во-вторых, XL Report сортирует область только слева направо. Если вы укажете в шаблоне больше трех сортируемых столбцов в области-списке, то область будет отсортирована по первым (слева) трем столбцам. Проектируйте области-списки в своих будущих отчетах, не забывая об этих ограничениях.
Итоги по столбцу области-списка. В предыдущей статье было показано, как получить сумму по столбцу области-списка. Вы наверняка запомнили, как выглядела та формула, которая была автоматически вставлена XL Report в ячейку под столбцом области. Вот эта формула: "=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(...". Функция листа "Промежуточные.Итоги" на самом деле позволяет получить по указанному диапазону ячеек не только сумму, но и среднее, минимум, максимум и так далее. XL Report поддерживает все допустимые этой функцией виды итогов. Вот опции, которые вы можете использовать для получения различных итогов по столбцу: Sum — отобразит сумму по столбцу; Count — количество значений в столбце; CountNums — количество не пустых значений в столбце; Avg или Average — среднее значение по столбцу; Max — максимальное значение в столбце; Min — минимальное значение в столбце; Product — произведение по столбцу; StDev — стандартное отклонение; StDevP — стандартное отклонение по генеральной совокупности; Var — дисперсия; VarP — дисперсия для генеральной совокупности.
Подробное описание всех функций вы найдете в электронной справке Excel. По столбцу можно получить только один итог. Попробуйте написать в шаблон предыдущего примера несколько опций итогов, разделив их символом ";". В качестве формулы итога в этом случае будет использована только последняя указанная функция. Обратите внимание на "Итого" в ячейке, расположенной слева от итоговой. В служебной строке в случае, если она не удаляется, вы можете использовать не только константы, но и формулы Excel.
Промежуточные итоги в области-списке. Все перечисленные выше опции промежуточных итогов можно использовать совместно с опцией Group. Опция Group должна находиться в столбцах, по которым вы хотите сгруппировать данные, получив указанные промежуточные итоги. Вот пример. Откройте шаблон из предыдущего примера и в служебных ячейках столбцов Items total и Amount paid укажите опцию Sum. А в служебной ячейке столбца Payment method укажите опцию Group. Сохраните шаблон, закройте его и запустите отчет. На рисунке вы видите, что промежуточные итоги области-списка сгруппированы по столбцу Payment method. Вы можете указывать одновременно до 16 полей, по которым можно группировать промежуточные итоги. В этом же шаблоне добавьте опцию Group в столбец Sale date. В готовом шаблоне вы увидите, что, во-первых, промежуточные итоги сгруппированы по Sale date и Payment method. Во-вторых, область OrdersRange в готовом отчете содержит как оригинальные данные, так и строки с промежуточными итогами. А в-третьих — область группируется слева направо. То есть рассчитываются итоги по крайнему левому столбцу группировки, потом по столбцу, расположенному правее его, и т.д. В отличие от общих итогов по столбцу области, допустимо применять несколько сгруппированных промежуточных итогов. В тот же шаблон вы можете добавить опцию Avg для столбца Amount paid и посмотреть на готовый отчет. По этому столбцу вы увидите сумму и "среднее".
Группировка промежуточных итогов предполагает предварительную сортировку — в нашем случае по столбцам Sale date и Payment method. По умолчанию XL Report сам сортирует предварительно все такие поля. Но на это уходит дополнительное процессорное время. Поэтому, если вы уверены в том, что данные попадут в отчет заранее отсортированными, напишите опцию области GroupNoSort, которая отключит ненужную в этом случае сортировку. Напоминаем вам, что опции области помещаются в левую нижнюю ячейку.
Скрытие детальной информации. Если к опции Group предыдущего примера добавить параметр Collapse и посмотреть на готовый отчет, то можно обнаружить, что дерево промежуточных итогов свернулось до уровня итогов по Payment method. Вот результат работы этой опции. Часто необходимо видеть сразу сгруппированные итоги, которые можно затем раскрыть, воспользовавшись деревом групп, расположенным слева номеров строк листа. На рисунке 7 вы видите результат работы опции Group\Collapse. При использовании Collapse вы должны помнить, что XL Report сворачивает дерево промежуточных итогов по самому правому столбцу с этой опцией.
Сводные таблицы в Excel позволяют манипулировать данными различной сложности и разного объема. Сводная таблица — это очень хорошее средство упорядочивания информации. Использование таких таблиц позволяет простому пользователю без написания проводить анализ данных фактически только с помощью мыши, без написания сложного кода. Сводные таблицы, в отличие от реляционных, являются многомерными и состоят из областей строк, столбцов, листов и областей данных. Поля области страниц добавляют при этом дополнительные измерения к исходной таблице. На следующем рисунке показано, как можно вывести информацию для анализа информации о заказах, выписанных заказчикам.
Если в предыдущих версиях XLReport'а для создания сводных таблиц приходилось использовать VBA, то теперь в шаблоне достаточно создать область-список, где опциями сводной таблицы будут описаны и поля сводной таблицы, и их форматы. При формировании отчета данные из набора будут перенесены в область-список, а XLReport на основе указанных опций создаст сводную таблицу. Проектируя шаблон области, вы можете определить все типы полей сводной таблицы, применить любое форматирование этих полей, определить типы итогов по любым полям и их внешний вид. Приведем пример простой сводной таблицы.
Во-первых, сводные таблицы в Excel строятся на основе двумерной таблицы, будь она результатом запроса к базе данных или списком на одном из листов книги. Поэтому данные из ваших приложений для сводных таблиц необходимо "поставлять" в Excel в виде одного набора. Чаще это результат запроса к базе данных. В DBDEMOS есть две таблицы: "Customer.DB" и "Orders.DB". Объединение этих таблиц по полю CustNo позволит нам получить массив счетов, приходящихся на каждого заказчика. Вот SQL-запрос, который вернет нам эти данные: SELECT * FROM Customer, Orders WHERE Customer. CustNo = Orders.CustNo. Для примера создадим новый проект с одной формой. На форму поместим только один набор данных (TQuery), в свойство SQL которого напишем этот запрос. Назовем этот компонент "qryOrders". Чтобы отобразить все записи этого набора, на форму добавим экземпляр TDataSource, сетку и навигатор. Поместим на форму один компонент TxlReport, добавим в его коллекцию DataSources один элемент и свяжем его с таблицей qryOrders. Добавим кнопки Report и Template c обработчиками, вызывающими, соответственно, методы Report и Edit последнего компонента. В свойство компонента xlReport XLSTemplate напишем имя предполагаемого шаблона — "tPivot1.xls".
Создадим шаблон для этого отчета, который должен иметь вид как на рисунке. В левую нижнюю ячейку созданной нами области напишем опцию Pivot\Name = OrdersPivot\ Dst = Pivot!R4C2\ColumnGrand. Эта опция укажет XL Report, что по области будет строиться сводная таблица с именем OrdersPivot, которая будет размещена на листе Pivot начиная с ячейки R4C2. А параметр ColumnGrand позволит включить итоги по столбцам полученной сводной таблицы. В служебной ячейке столбцов Payment method, OrderNo, Ship date и Tax rate укажем опцию Row. Опция Row определяет поля области строк сводной таблицы (RowFields). Для того чтобы получить итоги, сгруппированные по методу оплаты счетов, к опции Row в поле Payment method добавим опцию Sum. Для полей Amount paid и Items total укажем опцию Data (поля области данных сводной таблицы — DataFields). В опции поля Company добавим опцию Page (поле области страниц).
При проектировании шаблона, кроме распределения опций между столбцами, не забудьте указать различные форматы ячейкам области (в том числе для ячеек с датами и числами). Более того, мы отформатировали служебные ячейки с опциями столбцов, подразумевая, что именно с этим форматом мы получим промежуточные итоги в сводной таблице. А для поля Payment method мы выделили ячейку с опциями цветом. На рис.8 вы видите получившийся у нас шаблон области. После построения отчета мы должны сделать лист Pivot активным. Для этого можно воспользоваться свойством компонента TxlRe-port.ActiveSheet. В случае, если отчет многолистовой, в этом свойстве указывается имя листа, который необходимо сделать активным после построения отчета.
Чтобы придать сводным таблицам наиболее читаемый вид, форматируют область-источник. При создании сводной таблицы XL Report переносит форматы ячеек с формулами полей в ячейки сводной таблицы этих полей. Итоги по этим полям автоматически приобретают форматы, определенные для ячеек с опциями столбцов. В предыдущем примере для выделения итогов по полю Company мы указали желтый цвет фона для ячейки с опциями этого столбца в области-источнике.
Приведем еще один пример со сводной таблицей. Используем для этого тот же шаблон. Вы можете не создавать нового проекта. Достаточно лишь отредактировать шаблон отчета. Задача будущего отчета в том, чтобы показать минимальную и максимальную сумму выписанного счета, а также сумму по методу оплаты. На рисунке выше мы показали распределение опций в шаблоне и то, как мы отформатировали ячейки области-источника.
При проектировании областей-источников для сводных таблиц необходимо учитывать ряд ограничений, которыми они обладают. С ограничениями сводных таблиц вашей версии Excel вы можете ознакомиться в электронной справке Excel. Более точно эти ограничения и порядок их расчета описаны в MSDN статья Q211517 для Excel 2000 и Q157486 для Excel 97.
Кросс-таблицы. Как вы убедились, для построения сводных таблиц теперь достаточно указать в области-списке опции сводной таблицы. После этого названная область становится источником данных для сводной таблицы. Опция Pivot — это первая опция, на которую обращает внимание XL Report при анализе ячеек области-списка. Ранее вы встречались только с простыми опциями, состоящими из одного слова. Эта опция отличается от других тем, что может иметь несколько аргументов.
Вот ее синтаксис: Pivot \ Name=Pivot TableName \ [Dst= Destination] \ [DataTo Rows] \ [RowGrand] \ [ColumnGrand] \ [NoPreserveFormatting] где: Name = PivotTableName — имя сводной таблицы, допустимое в Excel. Dst= Destination — ячейка, в которую необходимо поместить левый верхний угол сводной таблицы. Если Destination не указано, то сводная таблица автоматически размещается на новом листе книги. Обратите внимание, что аргументы этой опции должны быть отделены друг от друга символом "\". DataToRows — опция, которая позволяет поместить поля данных в строках таблицы.
По умолчанию данные размещаются в столбцах. RowGrand — позволяет включить в сводной таблице итоги по строкам. ColumnGrand — включает итоги по столбцам сводной таблицы. NoPreserve Formatting — позволяет строить сводную таблицу без сохранения форматирования исходной области, что уменьшает время построения отчета. Вот несколько примеров правильного написания опции Pivot: "Pivot\Name = Pivot1\Dst = Totals!A1" — будет создана сводная таблица с именем Pivot1; таблица будет размещена на листе Totals начиная с ячейки A1; "Pivot\Name = Pivot25\Data ToRows" — будет создана сводная таблица Pivot25, поля данных которой разместятся в столбцах; "Pivot\Name = Pivot25\Dst = Totals!R1 C1\ RowGrand" — в сводной таблице Pivot25 включены итоги по строкам данных; "Pivot\Name = Pivot25\Co-lumnGrand" — в сводной таблице Pivot25 включены итоги по столбцам. Из предыдущего примера вы наверняка заметили, что поля во все области сводной таблицы добавляются в порядке их следования в шаблоне (слева направо). Поэтому при проектировании области-списка, по которой будет построена сводная таблица, нужно придерживаться одного простого правила: выстраивайте столбцы в том порядке, в каком бы вы хотели видеть их в сводной таблице.
Наименования полей для сводной таблицы берутся из строки над областью-списком — заголовок таблицы-источника. Будьте внимательны при создании этого заголовка, так как существуют некоторые ограничения по наименованию полей в сводных таблицах. Например, Excel 97 не всегда корректно работает с полями, в имени которых есть символ перевода строки. Впрочем, Excel 2000 и 2002 ведет себя не многим лучше. Однако с помощью сводных таблиц не составит никакого труда создавать в отчетах сложнейшие кросс-таблицы.
Для примера мы взяли предыдущий проект и изменили опции в исходной области. Для получения итогов по столбцам и строкам сводной таблицы мы добавили к этой опции два параметра: ColumnGrand и RowGrand. Далее мы поместили в область строк поле Company (с суммой в итогах) и поля OrderNo, Ship date, Tax rate. В область столбцов — поле Payment method. В области данных по-прежнему остались поля Items total и Amount paid. Готовый отчет с описанной таким образом сводной таблицей будет кросс-таблицей, по осям которой расположатся поля Company и PaymentMethod. В этом примере мы использовали опцию Sum для получения итогов по полю Company. В аналогичных ситуациях вы можете использовать любые опции промежуточных итогов, доступные в XL Report. Вы можете получать в качестве итогов максимальные или минимальные значения, отклонения и др. Вы также можете получать несколько итогов, перечисляя через точку с запятой в служебной ячейке столбца необходимые опции промежуточных итогов (рис.8а).
Но XL Report 4 поддерживает более мощное средство работы со сводными таблицами. Теперь вы можете разместить одну или несколько сводных таблиц прямо в шаблоне отчета, воспользовавшись удобством мастера сводных таблиц Excel и практически всеми возможностями в их оформлении и структурировании.
Приведем пример. В качестве отправной точки мы использовали шаблон первого примера со сводной таблицей с исходной областью SourceRange на листе Sheet1. Прямо в шаблоне мы поместили статическую сводную таблицу, построенную по этой области. На следующих рисунках показаны шаги построения этой таблицы. Во-первых, необходимо выделить для сводной таблицы исходную область. Она не идентична области SourceRange, так как включает только строку с данными и заголовок над ней. Обратите внимание, как выделена исходная область на рисунке (рис.9).
Далее мы поместили сводную таблицу на отдельном листе PivotSheet и распределили ее поля в области строк, столбцов и данных. Мы отформатировали поля сводной таблицы, а также их заголовки. Обратите внимание на то, что сводная таблица содержит только поля с ошибкой #NAME, что вполне естественно, так как исходная область содержит только формулы полей XL Report:
В завершение мы назвали сводную таблицу именем PivotTable1, а в качестве опции исходной области указали "PIVOT\Refresh=PivotSheet!Pivot Table1". Параметр Refresh опции области PIVOT требует указания списка сводных таблиц, статически размещенных в шаблоне и построенных по области. После переноса данных все сводные таблицы, перечисленные в этом списке, будут обновлены. В этом списке имена сводных таблиц указываются вместе с именем листа, на котором они размещены, и отделяются друг от друга запятыми. То есть по одной области вы можете построить несколько сводных таблиц. Вот простой пример. Мы добавили еще одну статическую сводную таблицу в шаблон, поместив ее на листе Pivot Sheet2, и назвали ее "Second PivotTable". Соответственно, опция PIVOT была изменена до "PIVOT\ Refresh = PivotSheet!PivotTable1, Pivot Sheet1!Second PivotTable". Обратите внимание на то, что имена листов и таблиц, содержащих пробелы, не требуют заключения в одиночные кавычки, как это принято во внешних ссылках Excel.
Счастливые обладатели Excel 2000 и выше могут попробовать разместить в шаблоне и статические сводные диаграммы (Pivot Chart). После переноса данных в исходную область будут обновлены и сводные таблицы, и сводные диаграммы.
При работе со сводными таблицами следует учитывать следующие замечания. Используя опции сводной таблицы в области-списке, можно автоматически построить сводную таблицу по области. Первое, что необходимо для создания сводной таблицы, — указать опцию области Pivot. Эта опция имеет несколько аргументов, позволяющих определить имя сводной таблицы, место ее будущего расположения в книге, вариант расположения полей данных в области данных. Если для будущей сводной таблицы не указана ячейка расположения, то XL Report размещает сводную таблицу на новом листе книги. Для определения полей в область строк достаточно указать для них опцию Row, для области страниц — Page, столбцов — Column, данных — Data. Совместно с этими опциями можно указать опции промежуточных итогов для этих полей. При этом доступны все функции промежуточных итогов. В сводных таблицах можно использовать несколько функций промежуточных итогов на одно поле области строк или столбцов. Наименования полей для сводной таблицы XL Report берет из ячеек строки над областью — из заголовка таблицы. Поля попадают в области сводной таблицы в порядке слева направо. При проектировании области-списка удобно располагать сначала поля страниц, затем строк, столбцов и данных. Цвет фона, шрифт и числовой формат ячеек области-списка переносится в ячейки полей сводной таблицы. Форматы ячеек опций столбцов переносятся в ячейки промежуточных итогов сводной таблицы. В свойстве ActiveSheet можно указать имя листа, который будет активным после построения отчета. Начиная с build 115 XL Report 4 поддерживает создание сводных таблиц прямо в шаблоне. Для быстрого создания сводных таблиц можно использовать параметр No-PreserveFormatting.
Вызов VBA. В этой части статьи рассмотрим возможность XL Report'а вызывать опубликованные в книге-шаблоне процедуры VBA. Эти процедуры помещаются в отдельный модуль или несколько модулей и объявляются как public. У классов TxlReport и TxlDataSource существуют свойства MacroBefore и MacroAfter, в которых указывают полное наименование процедур VBA.
MacroBefore. Процедура, указанная в этом свойстве, вызывается перед построением отчета (перед переносом данных у TxlDataSource).
МacroAfter. Процедура, указанная в этом свойстве, вызывается после построения отчета (после переноса данных у TxlDataSource). Событие On-Macro у этих двух классов служит для передачи параметров в вызываемые процедуры VBA.
Иногда всех описанных выше возможностей XL Report бывает недостаточно для решения задач, которые ставит реальная жизнь. В таком случае мы прибегаем к помощи VBA. Этот инструмент офисного программирования по праву имеет пальму первенства среди подобных систем. Существует целая область программирования, называемая офисным программированием, и масса книг и сайтов, посвященных этой теме и VBA. Используя VBA, вы получаете в свои руки всю мощь Excel. Классы TxlReport и TxlDataSource (элемент свойства-коллекции DataSources) имеют по два published-свойства: MacroAfter и MacroBefore. Эти свойства предназначены для вызова опубликованных процедур VBA. Их строковые значения должны содержать полные названия опубликованных процедур VBA, которые сохранены вместе с книгой-шаблоном. В своей работе мы применяем эти макросы чаще всего для построения в Excel диаграмм. Но этим не ограничивается круг применения макросов. Вы можете делать в этих макросах все что угодно. Точнее, все, на что способен VBA в Excel. А способен он на очень и очень многое. Для вызова VBA из XL Report при создании шаблона необходимо добавить модуль в книгу и записать в него public-процедуру. Если вы потом укажете имя этого модуля и, через точку, имя этой процедуры в одном из свойств MacroBefore или MacroAfter, то при создании отчета эта процедура будет автоматически вызвана. К примеру, ниже показан результат работы стандартного Hello, World:
Имейте в виду, что, если у вас макросы отключены, ваш Excel может сигнализировать об ошибке во время прогона нижеследующих примеров.
Простой вызов. Возьмем проект из самого первого примера документа. Откроем шаблон отчета в этом проекте. Добавим в книгу модуль и public-процедуру в этот модуль с именем HelloWorld. В ней сделаем вызов MsgBox с параметром-строкой Hello XL Report!. Сохраним шаблон и закроем его. В свойство Macro After экземпляра TxlReport введем полное имя этой процедуры — Mo-dule1.HelloWorld. Воспользовавшись контекстным меню компонента, вызовем отчет прямо из IDE Delphi. Процедура выполнилась. Как вы наверняка догадались, макрос, указанный в свойстве MacroBefore, выполняется перед построением всего отчета (для TxlReport) или перед переносом данных из набора (для TxlDataSource). Макросы в MacroAfter — соответственно, после этих действий. В макросах доступно многое. Как же будут доступны данные из вашего приложения в этих макросах? Все просто! Для NoRange-наборов данные доступны в поименованных ячейках, а для Range-наборов — в поименованных областях.
Диаграммы Excel и VBA. Попробуем построить такую диаграмму. На ней показаны итоги заказов всех покупателей, сумма которых более $70.000. Для этого создадим следующее тестовое приложение. Во-первых, нам необходимо создать набор данных. Возьмем за основу исходные тексты примера с первой сводной таблицей. Для qryAll в свойство SQL напишем следующий код:
SELECT c.Company, Sum(o. "ItemsTotal") as ItemsTo-tal,
Sum(o."AmountPaid") as AmountPaid
FROM Orders o, Customer c
WHERE c.CustNo = o.CustNo
GROUP BY c.Company ORDER BY c.Company
Этот код вернет нам названия всех компаний с просуммированными полями ItemsTotal и AmountPaid из таблицы счетов. Так как мы взяли за основу предыдущий пример, нам остается только изменить шаблон и написать в нем новый макрос. Форма шаблона показана на рис. 10.
Макрос для создания диаграммы мы поместили в один из модулей книги. Этот макрос создает и форматирует на новом листе диаграмму Excel по области AllRange. В этой процедуре интересна строка SrcRange. AutoFilter Field:=3, Criteria1:= "> 70000", Operator:=xlAnd, с помощью которой задается условие для созданного XL Report на эту область автофильтра. Этот макрос записан с помощью команды Excel "Записать макрос" и отредактирован для последующей работы с переменным набором AllRange.
Передача параметров в процедуры VBA. В предыдущем примере мы статически указали критерий отбора клиентов для вывода в диаграмму. Сделаем эту возможность динамической. Добавим на форму предыдущего примера поле редактирования с именем edCriteria. Исправим в шаблоне процедуру, добавив в ее объявление параметра Criteria as string и изменив код так, чтобы автофильтр реагировал на значение этого параметра. Необходимо сделать так, чтобы число, введенное в поле редактирования edCriteria, было передано в процедуру BuildChart. Для этого служит событие OnMacro у экземпляра TxlReport. Вот такой код мы написали в обработчике этого события:
procedure TfrmVBA3.xlRe-portMacro(Report: TxlRe-port;
const AMacroType: Txl MacroType; const AMacroNa-me: String;
var Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30: OleVariant);
begin
if AMacroName = 'Module1. BuildChart' then
Arg1 := edCriteria.Text;
end;
Такое огромное количество параметров диктуется синтаксисом метода Run объекта Application из Excel Type Library. В нашем же случае достаточно только первого параметра.
Денис "Denver" Мигачев, dtm@tut.by
Компьютерная газета. Статья была опубликована в номере 34 за 2003 год в рубрике программирование :: разное
