«Bill Jelen, Mr. Excel Tracy Syrstad 800 East 96th Street Indianapolis, Indiana 46240 ?изнес-решения Применение VBA и макросов в Microsoft® Excel Билл Джелен, “Мистер Excel” Трейси Сирстад Москва • Санкт-Петербург • Киев ...»

Встроенные диаграммы и диаграммы, расположенные на отдельном листе Изначально все диаграммы созда вались на отдельном листе. В середи 230 Часть II Автоматизация Excel не 1990 х годов в Excel была добавлена возможность встраивать диаграмму в существующий рабочий лист.

Наличие двух различных типов диаграмм вызвало необходимость создания двух объектных моделей. Диаграмме, расположенной на отдельном листе, со ответствует объект Chart, в то время как для работы со встроенной диаграм мой следует использовать объект ChartObject.

Встроенные диаграммы и контейнер ChartObject Объект ChartObject является своеобразным ‘‘контейнером’’ встроенной диаграммы. Его основное предназначение заключается в обеспечении способа определения размера встроенной диаграммы и ее положения на рабочем лис те. Эти параметры распространяются на все внедренные в диаграмму объекты, такие как автофигуры и изображения.

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

Щелкните на диаграмме, удерживая нажатой клавишу или.

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

Рис. 10.1. Чтобы выделить контейнер ChartObject, щелкните на встроен ной диаграмме, удерживая нажатой клавишу или. Имя кон тейнера диаграммы появится в поле Имя слева от поля ввода формулы В поле Имя (Name Box) слева от поля ввода формулы появится имя контей нера встроенной диаграммы. Это имя используется для обращения к объекту ChartObject, как показано ниже:

ActiveSheet.ChartObjects("Диаграмма 1").Select Чтобы определить смещение контейнера встроенной диаграммы от верх ней границы рабочего листа, выделите объект ChartObject и введите в окне Immediate (Быстрое выполнение) редактора Visual Basic строку Print Selection.Top.

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

По бокам и в углах области диаграммы появятся квадратные маркеры управ ления размером черного цвета (рис. 10.2), что свидетельствует о выделении области диаграммы.

Рис. 10.2. Чтобы выделить область диаграммы, щелкните на незанятом пространстве между границей диаграммы и областью ее построения. В по ле Имя появится стандартное имя области всех диаграмм — Область Области всех диаграмм имеют стандартное имя Область диаграммы (Chart Area), которое выводится в поле Имя слева от поля ввода формулы.

Ниже приведен код VBA, соответствующий выделению области диаграммы:

ActiveSheet.ChartObjects("Диаграмма 1").Activate ActiveChart.ChartArea.Select Чтобы узнать смещение области диаграммы от верхней границы контейне ра, выделите область диаграммы и введите в окне Immediate редактора Visual Basic строку Print Selection.Top.

Ниже приведен пример изменения цвета области диаграммы, встроенной в рабочий лист Excel:

Worksheets("Лист3").ChartObjects("Диаграмма 2").Chart.ChartArea. _ Interior.ColorIndex = 232 Часть II Автоматизация Excel Диаграммы, расположенные на отдельном листе При работе с диаграммами, расположенными на отдельном листе, приме няется объектная модель, отличная от той, что применялась при работе со встроенными диаграммами. В частности, объект диаграммы принадлежит не объекту контейнера, а объекту листа. Ниже приведен пример изменения цвета области диаграммы, расположенной на отдельном листе:

Sheets("Диаграмма 2").ChartArea.Interior.ColorIndex = Создание диаграмм с помощью VBA Рис. 10.3. Чтобы создать показанных на рис. 10.3.

диаграмму, выделите ис ходные данные и нажмите Рис. 10.4. В результате нажатия клавиши Excel создаст новую диаграмму, расположенную на отдельном листе Ниже приведен код, сгенерированный средством записи макросов:

Charts.Add ActiveChart.SetSourceData Source:=Sheets("Лист1").Range("A1:B5") ActiveChart.Location Where:=xlLocationAsNewSheet Проанализируем первую строку кода:

Charts.Add Коллекция Charts представляет собой коллекцию всех листов диаграмм в ра бочей книге. Каждая коллекция имеет метод добавления нового элемента Add.

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

Добавив новый лист диаграммы, Excel автоматически делает его активным.

Для обращения к текущей активной диаграмме можно использовать как объект Chart (например, Charts("Диаграмма 1")), так и объект VBA ActiveChart.

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

ActiveChart.SetSourceData Source:=Sheets("Лист4").Range("A1:B5") Вторая строка автоматически сгенерированного кода определяет диапазон исходных данных для диаграммы с помощью метода SetSourceData. Следу ет отметить, что VBA содержит соответствующие методы для всех действий, которые можно выполнить посредством пользовательского интерфейса. Так, для того чтобы задать диапазон исходных данных диаграммы, необходимо щелкнуть на ней правой кнопкой мыши и выбрать команду контекстного ме ню Исходные данные (Source Data). На экране появится диалоговое окно Исходные данные (Source Data) (рис. 10.5), вкладка Диапазон данных (Data Range) которого позволяет указать диапазон исходных данных.

Ниже приведен полный синтаксис метода SetSourceData:

SetSourceData(Source, PlotBy) Здесь Source это ссылка на диапазон ячеек, а PlotBy константа, принимающая значение xlColumns или xlRows.

Ниже приведен пример вызова метода SetSourceData с указанием всех аргументов:

ActiveChart.SetSourceData Source:=Sheets("Лист4").Range("A1:B5"), _ PlotBy:=xlColumns Обратите внимание, что автоматически сгенерированный код не содержит ар гумент PlotBy. Вероятно, средство записи макросов сочло возможным опустить его, поскольку структура исходных данных (имена заголовков столбцов в ячейках A1 и B1) предполагает, что ряды данных расположены в столбцах. К сожалению, подобная эффективность является скорее исключением, чем правилом.

ActiveChart.Location Where:=xlLocationAsNewSheet 234 Часть II Автоматизация Excel Рис. 10.5. Для определения исходных данных диаграммы можно восполь зоваться диалоговым окном Исходные данные или методом VBA SetSourceData Приведенная выше строка соответствует последнему шагу мастера созда ния диаграмм (рис. 10.6).

Ниже приведен полный синтаксис метода Location:

Location(Where, Name) Константа Where может принимать значения xlLocationAsNewSheet, xlLocationAsNewObject и xlLocationAutomatic.

Name — это строка, которая определяет имя нового листа, на котором будет размещена диаграмма (параметр Where принимает значение xlLocationAsNewSheet);

имя рабочего листа, на котором будет размещена встроенная диаграм ма (параметр Where принимает значение xlLocationAsNewObject).

Как уже отмечалось, средство записи макросов генерирует много избыточ ного кода. Поскольку использование метода Charts.Add подразумевает, что новая диаграмма будет размещаться на отдельном листе, последняя строка сгенерированного кода является лишней и ее можно удалить.

Изменение размещения диаграммы Метод Location позволяет изменить тип диаграммы, преобразовав ее из встроенной на размещенную на отдельном листе и наоборот. (Чтобы изменить размещение диаграммы с помощью пользовательского интерфейса, щелкните на диаграмме правой кнопкой мыши и выберите команды контекстного меню Размещение (Location).) Рассмотрим следующий код:

Worksheets("Лист1").ChartObjects("Диаграмма 1").Activate ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="МояДиаграмма" Его выполнение приведет к преобразованию диаграммы Диаграмма 1, встроенной в рабочий лист Лист1, в диаграмму, размещенную на отдельном листе МояДиаграмма.

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

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

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

ActiveChart.PlotArea.Interior.ColorIndex = xlNone 236 Часть II Автоматизация Excel Чтобы изменить стандартный тип диаграммы с помощью пользовательского ин терфейса, щелкните на диаграмме правой кнопкой мыши и выберите команду контекстного меню Тип диаграммы (Chart Type). Выберите требуемый тип диа граммы и щелкните на кнопке Сделать стандартной (Set As Default Chart).

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

Set Cht = Charts.Add Cht.SourceData = Source:=Sheets("Лист4").Range("A1:B5") Объектные переменные будут использоваться на протяжении оставшейся части этой главы.

Еще одним преимуществом объектных переменных является поддержка редактором Visual Basic автозаполнения. Чтобы включить автозаполнение, выберите команду меню редактора Visual Basic Tools Options (Сервис Параметры) и установите флажок Auto List Members (Автозаполнение) на вкладке Editor (Редактор), как показано на рис. 10.7.

Автозаполнение позволяет редактору Visual Basic автоматически пред лагать подходящий способ продолжения ввода программного кода. Все, что требуется от пользователя это выбрать нужный элемент из списка, как показано на рис. 10.8.

Рис. 10.8. Автозаполнение в действии — после ввода выражения Cht.ChartType = редактор Visual Basic предлагает выбрать требуемую константу из списка Внимание Сбой в работе средства автозаполнения может быть вызван наличием ошибки компилирования в программном коде. Чтобы обнаружить ошибку, выберите ко манду меню редактора Visual Basic Debug Compile VBA Project (Отладка Компилировать проект VBA). Устранение ошибки компилирования должно при вести к восстановлению работы средства автозаполнения.

“Анатомия” диаграммы В этом разделе рассматриваются VBA эквиваленты различных элементов диаграммы, их свойства и методы.

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

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

круговая и кольцевая диаграммы не имеют осей как таковых.

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

Рассмотрим пример форматирования области диаграммы, для чего запишем небольшой макрос. Щелкните правой кнопкой мыши на области диаграммы и 238 Часть II Автоматизация Excel выберите команду контекстного меню Формат области диаграммы (Format Chart Area). На вкладке Вид (Patterns) диалогового окна Формат области диаграммы (Format Chart Area) выберите светло бирюзовый цвет заливки, красный цвет рамки, третью по толщине линию и установите флажок С тенью (Shadow). На вкладке Шрифт (Font) выберите размер шрифта 14 и снимите флажок Автомасштабирование (Auto scale). Ниже приведен код, сгенерирован ный средством записи макроса в результате выполнения указанных действий:

Sub Macro2AsRecorded() Sheets("Диаграмма 1").Activate ActiveChart.ChartArea.Select With Selection.Border Selection.Shadow = True With Selection.Interior Selection.AutoScaleFont = False With Selection.Font.Underline = xlUnderlineStyleNone Средство записи макросов зафиксировало все действия, выполненные по средством пользовательского интерфейса (и даже больше). Следующий шаг состоит в оптимизации полученного кода. Строки, набранные полужирным шрифтом, являются избыточными:

Sub Macro2AsRecorded() Sheets("Диаграмма 1").Activate ActiveChart.ChartArea.Select With Selection.Border ' Значение по умолчанию.

Selection.Shadow = True With Selection.Interior ' Светло-бирюзовый цвет заливки.

' Значение по умолчанию.

.PatternColorIndex = ' Значение по умолчанию.

Selection.AutoScaleFont = False With Selection.Font ' Значение не изменилось.

' Значение не изменилось.

.FontStyle = "Regular" ' Значение не изменилось.

.Strikethrough = False ' Значение не изменилось.

.Superscript = False ' Значение не изменилось.

' Значение не изменилось.

.OutlineFont = False ' Значение не изменилось.

' Значение не изменилось.

.Underline = xlUnderlineStyleNone ' Значение не изменилось.

.ColorIndex = xlAutomatic ' Значение не изменилось.

.Background = xlAutomatic End Sub Ниже приведен оптимизированный код макроса:

Sub Macro2Shortened() Sheets("Диаграмма 1").Activate ActiveChart.ChartArea.Select With Selection.Border Selection.Shadow = True With Selection.Interior Selection.AutoScaleFont = False With Selection.Font End Sub С целью дальнейшего упрощения кода создадим объектную переменную, представляющую область диаграммы. Обратите внимание, что при использо вании объектной переменной к области диаграммы можно обращаться без ее предварительного выделения. Более того, следующий код будет выполняться корректно даже в том случае, когда лист диаграммы не будет активным:

240 Часть II Автоматизация Excel Sub ChartArDemo() Dim ChtArea As ChartArea Set ChtArea = Charts("Диаграмма 1").ChartArea Поговорим о цвете При выборе цвета линии, заливки или шрифта Excel предлагает использовать стандартную палитру, состоящую из 56 цветов. Чтобы изменить любой цвет стан дартной палитры, выберите команду меню Excel Сервис Параметры (Tools Options), перейдите во вкладку Цвет (Color) и, указав требуемый цвет, щелкните на кнопке Изменить (Modify). Измененная палитра сохраняется вместе с рабочей книгой. Для доступа к цветам палитры можно использовать свойство рабочей книги Colors.

Следующие строки кода полностью эквивалентны:

.Border.ColorIndex =.Border.Color = ThisWorkbook.Colors(3) Обратите внимание, что порядок цветов в палитре не соответствует их ин дексу, т.е. значению свойства ColorIndex. Например, в стандартной палитре красный цвет (1 й столбец 3 й строки) имеет индекс 3, а бирюзовый (5 й столбец 4 й строки) 8. Наиболее простой способ определения индекса цве та заключается в записи простого макроса, устанавливающего требуемый цвет для произвольного элемента интерфейса.

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

RGB(red, green, blue) Каждый из аргументов принимает значения в диапазоне от 0 до 255. Сле дующие строки кода полностью эквивалентны и используются для установки красного цвета границы:

.Border.ColorIndex =.Border.Color = RGB(255, 0, 0) Область построения диаграммы (PlotArea) Область построения диаграммы содержит визуализированные ряды дан ных, оси и подписи осей. К ней применимы те же операции форматирования, что и к области диаграммы. Вдобавок, вы можете изменять размеры области построения диаграммы и ее размещение в пределах области диаграммы с по мощью свойств Top, Left, Height и Width объекта PlotArea.

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

За единицу измерения в Excel VBA принята точка, составляющая 1/ дюйма.

Размещение объекта полностью определяется расстоянием по вертикали и горизонтали от верхнего левого угла объекта до верхнего левого угла его контей нера. Расстоянию по вертикали соответствует свойство объекта Top, а расстоя нию по горизонтали свойство объекта Left. Высота и ширина объекта сов падают с высотой и шириной его ограничивающего прямоугольника (рис. 10.9).

Рис. 10.9. Размещение объекта полностью опреде ляется расстоянием по вертикали и горизонтали от верхнего левого угла объекта до верхнего левого Свойства Top, Left, Height и Width поддерживают как считывание, так и установку значения. Ниже приведен пример определения высоты объекта:

ObjHt = obj.Height и изменения его размещения:

obj.Top = obj.Left = Рассмотрим следующий код:

Sub PlotArDemo() Dim PltArea As PlotArea 242 Часть II Автоматизация Excel Set PltArea = Charts("Диаграмма 3").PlotArea Результат его выполнения представлен на рис. 10.10.

Рис. 10.10. Свойства объекта Top, Left, Height и Width позволяют изменить его размер Наличие свойств Top, Left, Height и Width позволяет задать размер и размещение объекта с большей точностью, чем это можно было бы сделать с помощью пользовательского интерфейса. Изменяя значения этих свойств, следует помнить об их естественных ограничениях. К примеру, сумма значе ний свойств объекта Left и Width не может превысить значение свойства Width контейнера объекта.

Ряды данных (Series) Ряды данных диаграммы входят в коллекцию SeriesCollection. Рас сматриваемая в качестве примера диаграмма имеет два ряда данных Xdata и Ydata. Ниже приведен синтаксис обращения к ряду данных:

Cht.SeriesCollection(Index) Index — это номер (начиная с 1) или имя ряда данных. Щелкнув на точке данных (столбце) из ряда Xdata, вы увидите в строке формул следующее вы ражение:

=РЯД(Лист1!$A$1;;Лист1!$A$2:$A$5;1) (В англоязычной версии Excel: =SERIES(Лист1!$A$1,,Лист1!$A$2:

$A$5,1).) Лист1!$A$1 это имя ряда данных (Xdata).

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

Лист1!$A$2:$A$5 это диапазон ячеек, в котором находится ряд данных.

Наконец, 1 это индекс ряда данных в коллекции. Чтобы изменить ин декс ряда данных с помощью пользовательского интерфейса, щелкните на точке данных правой кнопкой мыши, выберите команду контекстного меню Формат рядов данных (Format Data Series) и перейдите во вкладку Порядок рядов (Series Order).

Ниже приведены два эквивалентных способа обращения к ряду данных Xdata:

Charts("Диаграмма 1").SeriesCollection("Xdata") Charts("Диаграмма 1").SeriesCollection(1) Следующий макрос позволяет создать комбинированную диаграмму, в ко торой ряд данных Xdata будет представлен в виде графика:

Sub SeriesDemo() Dim Ser As Series Set Ser = Charts("Диаграмма 3").SeriesCollection("Xdata").ChartType = xlLine.Border.Weight = xlThick.MarkerStyle = xlMarkerStyleCircle.MarkerBackgroundColorIndex = xlAutomatic.MarkerForegroundColorIndex = xlAutomatic End Sub Результат выполнения макроса представлен на рис. 10.11.

Оси диаграммы (Axis) Оси диаграммы входят в коллекцию Axes. Рассматриваемая в качестве примера диаграмма имеет две оси ось категорий (X) и ось значений (Y).

Ниже приведен сокращенный синтаксис обращения к оси диаграммы:

Cht.Axes(Type) Type — это константа Excel VBA, определяющая тип оси. Ось категорий (X) имеет тип xlCategory, а ось значений (Y) тип xlValue.

244 Часть II Автоматизация Excel Рис. 10.11. Пример создания комбинированной диаграммы путем изменения спосо ба представления (ChartType) ряда данных Чтобы обратиться ко всей коллекции осей диаграммы, пропустите пара метр Type, как показано ниже:

Cht.Axes Следующий макрос добавляет к диаграмме подписи осей и изменяет фор мат данных оси Y (оси значений):

Sub AxisDemo() Set Axs = Charts("Диаграмма 3").Axes(xlValue).AxisTitle.Caption = "Эффективность производства".TickLabels.NumberFormat = "0.00" Set Axs = Charts("Диаграмма 3").Axes(xlCategory) Результат выполнения макроса представлен на рис. 10.12.

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

Рис. 10.12. Пример добавления к диаграмме подписей осей и изменения формата данных оси значений Добавление вспомогательных осей Если масштаб рядов данных сильно отличается, может возникнуть необхо димость добавления вспомогательной оси (горизонтальной или вертикаль ной). Ниже приведен полный синтаксис обращения к оси диаграммы:

Cht.Axes(Type, AxisGroup) AxisGroup — это константа Excel VBA, определяющая группу оси. Основ ные оси входят в группу xlPrimary, а вспомогательные в группу xlSecondary.

Следующий макрос определяет вспомогательную ось значений (Y) для ряда данных Xdata.

Sub SecondaryAxisDemo() Dim Cht As Chart Set Cht = Charts("Диаграмма 3") Cht.SeriesCollection("Xdata").AxisGroup = End Sub Результат выполнения макроса представлен на рис. 10.13.

Линии сетки (HasMajorGridlines и HasMinorGridlines) Линии сетки являются расширением меток делений оси и предназначены для улучшения восприятия и оценки отображаемых данных. Основным и промежуточным меткам делений оси соответствуют основные и промежу точные линии сетки, которые можно скрыть или отобразить независимо друг 246 Часть II Автоматизация Excel от друга. Линия сетки имеет настраиваемые параметры, такие как тип, цвет Рис. 10.13. Если масштаб рядов данных сильно отличается, добавьте вспомо Следующий макрос удаляет все линии сетки диаграммы:

Sub GridlineDemo() Set Cht = Charts("Диаграмма 3") Подписи данных (DataLabels и DataLabel) Ряд состоит из точек данных. Каждая точка может иметь свою собственную подпись, включающую значение по оси X (категорий), оси Y (значений) или значение, определенное пользователем (строковая константа или ссылка на ячейку). Ниже перечислены различные способы создания подписи данных с помощью VBA.

Все точки данных диаграммы имеют подписи одного и того же типа:

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone Точки данных определенного ряда имеют подписи одного и того же типа:

With ActiveChart.SeriesCollection("Xdata").ApplyDataLabels Type:=xlDataLabelsShowValue Определенная точка в ряде данных имеет подпись в виде строковой константы:

With ActiveChart.SeriesCollection("Xdata").Points(1).DataLabel.Text="Подпись точки данных" Определенная точка в ряде данных имеет подпись в виде R1C формулы (ссылки на ячейку):

With ActiveChart.SeriesCollection("Xdata").Points(1).DataLabel.Text="=Лист1!R1C1" Подпись данных имеет настраиваемые параметры, такие как положение, ориентация и т.п.

Следующий макрос создает подписи для точек данных ряда Xdata.

Sub DataLabelDemo() With Charts("Диаграмма 3").SeriesCollection("Xdata").HasDataLabels = True.ApplyDataLabels Type:=xlDataLabelsShowValue.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.Position = xlLabelPositionAbove End Sub Название диаграммы, легенда и таблица данных (ChartTitle, HasLegend и HasDataTable) Название диаграммы и легенда имеют такие настраиваемые параметры, как шрифт и размещение. Кроме того, рядом с каждой диаграммой можно отобразить таблицу ее исходных данных. Следующий макрос добавляет к диаграмме ее название (отформатированное полужирным шрифтом 16 го размера и размещенное в верхнем левом углу), легенду (расположенную внизу и посередине диаграммы) и таблицу данных.

Обратите внимание, что установка параметров названия диаграммы, ле генды и таблицы данных становится возможной только после присвоения значения True свойствам HasTitle, HasLegend и HasDataTable, соответ ственно.

Sub DemoMisc() With Charts("Диаграмма 3") 248 Часть II Автоматизация Excel Линии тренда и полосы погрешности (Trendlines и ErrorBar) Линия тренда дает наглядное представление о направлении изменения ря да данных. Excel содержит несколько типов линий тренда: линейная, лога рифмическая, полиномиальная, степенная, экспоненциальная и линейная фильтрация. Полосы погрешности позволяют оценить отклонение фактиче ских данных от тренда.

На рис. 10.14 показана точечная диаграмма годовых продаж.

Следующий макрос добавляет к диаграмме линию тренда с прогнозом на 5 периодов (лет) вперед, выводит уравнение линии тренда и величину досто верности аппроксимации (R ):

Sub AddTrendLine() Set Cht = Worksheets("Тренд и погрешности").ChartObjects(" _ Диаграмма 1").Chart Set Ser = Cht.SeriesCollection(1) Set Trnd = Ser.Trendlines.Add(Type:=xlLinear, Forward:=5, _ Backward:=0, DisplayEquation:=True, DisplayRSquared:=True) Trnd.Border.LineStyle = xlDot.ChartTitle.Characters.Text = "Прогнозируемые продажи" Результат выполнения макроса показан на рис. 10.15. Величина достовер ности аппроксимации (R ), близкая к 1, означает, что линия тренда соответст вует фактическим данным.

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

Sub AddErrorBars() Set Cht = Worksheets("Тренд и погрешности").ChartObjects(" _ Диаграмма 1").Chart Set Ser = Cht.SeriesCollection(1) Ser.ErrorBar Direction:=xlY, Include:=xlBoth, _ Type:=xlFixedValue, Amount:= End Sub Рис. 10.14. Чтобы добавить линию тренда, щелкните на любой точке данных диаграммы годовых продаж правой кнопкой мыши и выберите команду контекстного меню Добавить линию тренда (Add Trendline) Полосы погрешности могут размещаться по обе стороны точки данных, выше или ниже ее. Погрешность может определяться как фиксированное зна чение, относительное значение, заданное число стандартных отклонений, стандартная погрешность, а также с помощью пользовательской формулы.

Как показано на рис. 10.16, прогнозируемый уровень продаж выходит за рам ки допустимой погрешности только в 1987 году.

250 Часть II Автоматизация Excel Рис. 10.15. Добавление линии тренда с прогнозом на 5 периодов (лет) вперед Рис. 10.16. Добавление полос погрешности Типы диаграмм В состав Excel входит множество встроенных диаграмм различных типов.

Типы и виды стандартных диаграмм Excel перечислены в табл. 10.1.

Таблица 10.1. Стандартные диаграммы Excel диаграммы Гистограмма Обычная гистограмма xlColumnClustered Объемный вариант гистограммы xl3DColumnStacked Нормированная гистограмма с xlColumnStacked Линейчатая Обычная линейчатая диаграмма xlBarClustered диаграмма Объемный вариант обычной ли xl3DBarClustered Линейчатая диаграмма с накоп xlBarStacked Объемный вариант линейчатой xl3DBarStacked 252 Часть II Автоматизация Excel диаграммы Круговая Обычная круговая диаграмма xlPie диаграмма Точечная Обычная точечная диаграмма xlXYScatter диаграмма диаграммы Точечная диаграмма со значе xlXYScatterLinesNoMarkers Пузырьковая Обычная пузырьковая диаграмма xlBubble диаграмма Диаграмма с Обычная диаграмма с областями xlArea областями Объемный вариант диаграммы с xl3DAreaStacked Нормированная диаграмма с об xlAreaStacked Кольцевая Обычная кольцевая диаграмма xlDoughnut диаграмма Лепестковая Обычная лепестковая диаграмма диаграмма Лепестковая диаграмма с марке xlRadarMarkers Поверхностная Обычная поверхностная диа xlSurface диаграмма грамма Контурная диаграмма (вид свер xlSurfaceTopView 254 Часть II Автоматизация Excel диаграммы Проволочная (прозрачная) кон xlSurfaceTopViewWireframe Биржевая Биржевая диаграмма для набо xlStockHLC диаграмма ров из трех значений (самый вы Цилиндри Обычная гистограмма со столб xlCylinderColClustered ческая диа цами в виде цилиндров диаграммы Нормированная линейчатая диа xlCylinderBarStacked Коническая Обычная гистограмма со столб xlConeColClustered диаграмма цами в виде конусов Обычная линейчатая диаграмма xlConeBarClustered Линейчатая диаграмма с накопле xlConeBarStacked Нормированная гистограмма с xlConeColStacked Нормированная линейчатая диа xlConeBarStacked Пирамидаль Обычная гистограмма со столб xlPyramidColClustered ная диаграмма цами в виде пирамид Обычная линейчатая диаграмма xlPyramidBarClustered Линейчатая диаграмма с накопле xlPyramidBarStacked Нормированная гистограмма с xlPyramidColStacked 256 Часть II Автоматизация Excel Не отчаивайтесь, взглянув на размеры этой таблицы. С практической точ ки зрения цилиндрические, конические и пирамидальные диаграммы анало гичны гистограммам, а линейчатые диаграммы это гистограммы, поверну тые на 90° по часовой стрелке.

В большинстве случаев разные виды диаграмм в пределах одного типа отличаются значениями нескольких параметров. К примеру, единственное отличие обычной точечной диаграммы (xlXYScatter) от точечной диаграм мы со значениями, соединенными отрезками (xlXYScatterLines), заклю чается в том, что параметр SeriesCollection(1).Border.LineStyle последней имеет значение xlAutomatic.

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

Параметры трехмерных диаграмм Все трехмерные диаграммы имеют параметры объемного вида, являющие ся свойствами объекта Chart.

Elevation. Возвышение, с которого наблюдатель смотрит на диа грамму. Если значение параметра Elevation равно 0, наблюдатель не видит верхнюю поверхность фигур. Если значение параметра Elevation равно 90, наблюдатель смотрит на диаграмму сверху вниз.

Rotation. Этот параметр принимает значения в диапазоне от 0 до 359.

При небольшом значении угла поворота наблюдатель смотрит на диа грамму так, как если бы он находился справа от нее, а при значении уг ла поворота, равном 330 350, так, как если бы он находился слева от нее. Чтобы развернуть диаграмму и посмотреть на нее сзади, исполь зуйте значения параметра Rotation в диапазоне от 150 до 210.

Perspective. Этот параметр принимает значения в диапазоне от до 100. Установка больших значений параметра Perspective приво дит к искривлению основания диаграммы.

DepthPercent. Глубина диаграммы в процентах от стандартной HeightPercent. Высота диаграммы в процентах от стандартной RightAngleAxes. Установка значения этого параметра равным True исключает возможность изменения перспективы диаграммы, что ха Объекты Walls (стенки) и Floor (основание) доступны только для трех мерных диаграмм. Эти объекты имеют одинаковые наборы настраиваемых параметров, таких как цвет заливки, тип рамки и т.п.

Ниже перечислены параметры ряда данных трехмерной диаграммы.

GapWidth. Если ширина зазора равна 0, фигуры, представляющие на диаграмме один ряд данных, соприкасаются друг с другом. Чем выше ширина зазора, тем больше расстояние между фигурами. GapWidth — это свойство объекта ChartGroup, представляющего все ряды данных одинакового типа. Если один ряд данных на диаграмме представлен трехмерными столбцами, а другой трехмерным графиком, то шири на зазора между столбцами будет определяться значением свойства GapWidth объекта Columns3DGroup.

GapDepth. Если глубина зазора равна 0, фигуры, представляющие на диаграмме соседние ряды данных, соприкасаются друг с другом. Чем выше глубина зазора, тем больше расстояние между фигурами. Не смотря на то что глубину зазора можно определить посредством диа логового окна Формат ряда данных (Format Data Series), GapDepth является свойством объекта Chart.

ChartDepth. Если значение параметра ChartDepth равно 20, диа грамма выглядит очень плоской. Чем больше значение этого парамет ра, тем больше глубина диаграммы. ChartDepth является свойством объекта Chart.

Следующий макрос оперирует всеми рассмотренными выше параметрами.

Sub Format3D() Dim Cht As Chart Set Cht = Worksheets("Трехмерная _ диаграмма").ChartObjects(1).Chart.RightAngleAxes = False.HeightPercent =.AutoScaling = True.DepthPercent = Cht.Column3DGroup.GapWidth = With Cht.Walls.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=.ForeColor.SchemeColor =.BackColor.SchemeColor = With Cht.Floor.Fill.PresetGradient Style:=msoGradientHorizontal, _ Variant:=1, PresetGradientType:=msoGradientCalmWater 258 Часть II Автоматизация Excel Результат выполнения макроса показан на рис. 10.17.

Рис. 10.17. Пример изменения параметров трехмерной диаграммы Параметры круговых диаграмм Один из недостатков круговых диаграмм состоит в возможности перекры вания подписей данных, как показано на рис. 10.18.

К счастью, уникальные свойства круговой диаграммы могут помочь в ре шении этой проблемы.

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

Sub RotateFirstSlice() Set Cht = Worksheets("Круговая диаграмма").ChartObjects(1).Chart Cht.PieGroups(1).FirstSliceAngle = Рис. 10.18. Подписи данных круговой диаграммы могут перекрывать друг друга Если же друг друга перекрывает много подписей данных, вынесите все до ли, не превышающие 5%, во вторичную гистограмму с накоплением:

Sub CreateBarOfPie() Dim Cht As Chart Dim CG As ChartGroup Set Cht = Worksheets("Круговая диаграмма").ChartObjects(1).Chart Cht.ChartType = xlBarOfPie Set CG = Cht.PieGroups(1).SplitType = xlSplitByPercentValue ' Доли менее 5%.

' Зазор между основной и вторичной диаграммой.

' Размер вторичной диаграммы в % от основной.

.SecondPlotSize = End Sub Результат выполнения приведенного выше кода показан на рис. 10.19.

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

260 Часть II Автоматизация Excel Рис. 10.19. Вторичная гистограмма с накоплением используется для выне сения из круговой диаграммы долей, не превышающих 5%. В большинстве случаев это позволяет избежать перекрывания подписей данных Интерактивные диаграммы Рассмотрим использование VBA для создания интерактивных диаграмм.

События диаграмм Одним из недостатков диаграмм является автоматическое изменение внешнего вида диаграммы при обновлении исходных данных. Управлять из менением внешнего вида диаграммы помогут события. (Более подробно со бытия рассматривались в главе 8, ‘‘События’’.) Код обработки событий диа граммы, расположенной на отдельном рабочем листе, помещается в модуль этого листа. Для обработки событий встроенной диаграммы необходимо соз дать модуль класса. Ниже перечислены некоторые из наиболее часто исполь зуемых событий диаграммы:

SeriesChange — срабатывает при обновлении ряда данных на диа Calculate — срабатывает при изменении исходных данных диаграммы;

Activate — срабатывает при активизации диаграммы;

Deactivate — срабатывает при деактивизации диаграммы.

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

Private Sub Chart_Calculate() Dim Ser As Series Set Ser = Me.SeriesCollection(1) If Ser.Points.Count > 5 Then Ser.Border.ColorIndex = Ser.Border.ColorIndex = End Sub Экспорт диаграммы в файл изображения Экспортировать диаграмму в файл изображения формата GIF не составля ет никакого труда:

Sub SaveChart() Dim Cht As Chart Set Cht = Worksheets("Круговая диаграмма").ChartObjects(1).Chart Cht.Export Filename:=ThisWorkbook.Path & _ Application.PathSeparator & "pie.gif", FilterName:="GIF" End Sub Подобный шаг может иметь следующие мотивы.

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

Экономия системных ресурсов. Диаграммы занимают много места в оперативной памяти компьютера. Если вам нужно отобразить 100 или больше диаграмм, воспользуйтесь средствами VBA для создания каж дой отдельной диаграммы, ее сохранения в файле формата GIF и за грузки полученного файла в рабочую книгу. Ниже приведен код загруз ки файла изображения в рабочую книгу:

ActiveSheet.Pictures.Insert (ThisWorkbook.Path & _ Application.PathSeparator & "pie.gif") Необходимость помещения диаграммы на пользовательскую форму.

Единственный способ помещения диаграммы на пользовательскую форму заключается в ее загрузке из файла изображения. Ниже приве ден код загрузки файла изображения в элемент управления Image:

Me.Image1.Picture = LoadPicture(ThisWorkbook.Path & _ Application.PathSeparator & "pie.gif") 262 Часть II Автоматизация Excel Удивительные возможности точечных диаграмм Несмотря на то, что по части построения чертежей Excel существенно ус тупает таким ‘‘монстрам’’, как AutoCAD, вы будете приятно поражены, от крыв для себя удивительные возможности точечных диаграмм. На рис. 10. показан пример использования точечной диаграммы для построения логотипа компании MrExcel Consulting.

Рис. 10.20. Для построения логотипа компании MrExcel Consulting с помощью точечной диаграммы понадобилось 15 точек данных Идея использования точечных диаграмм для построения чертежей была доведена до совершенства Малой Сингхом (Mala Singh) из компании XLSoft Consulting (Индия). На рис. 10.21 показан один из его шедевров.

Создание нестандартных диаграмм К сожалению, создание нестандартных диаграмм Excel с помощью VBA выходит за рамки этой книги. Все диаграммы, приведенные в этом разделе, были созданы с помощью VBA и используются с разрешения компании XLSoft Consulting.

Круговая пузырьковая диаграмма Внешний вид круговой пузырьковой диаграммы представлен на рис. 10.22.

Рис. 10.21. Этот чертеж на самом деле является точечной диаграммой Excel. На его построение уходит около 25 с Рис. 10.22. Круговая пузырьковая диаграмма Пузырьковая диаграмма похожа на точечную диаграмму с добавлением третьего ряда данных в виде диаметра пузырька. В свою очередь диаграмма, показанная на рис. 10.22, является расширением пузырьковой диаграммы с добавлением четвертого ряда данных, представленного в виде обычной круго вой диаграммы. Круговая пузырьковая диаграмма строится с помощью цикла 264 Часть II Автоматизация Excel по всем точкам четвертого ряда данных. Для каждой точки создается скрытая круговая диаграмма, которая затем используется в качестве изображения со ответствующего пузырька.

Диаграмма с точками данных в виде спидометров На рис. 10.23 показана диаграмма с точками данных в виде спидометров.

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

Каждый из спидометров, показанных на рис. 10.23, на самом деле является изображением отдельной диаграммы. Макрос, создающий приборную доску, генерирует диаграмму на основе данных таблицы Excel (одной строке данных соответствует одна приборная доска), а затем использует ее для создания ста тического изображения. Наконец, полученные изображения спидометров упорядочиваются на рабочем листе.

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

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

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

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

Рис. 10.25. Иерархическая кольцевая диаграмма 266 Часть II Автоматизация Excel Следующий шаг Диаграммы являются неотъемлемой частью всех программ для работы с электронными таблицами, поскольку они позволяют получить наглядное представление об исходных данных. Следующая глава посвящена анализу данных с помощью расширенного фильтра.

Глава Преимущества VBA интерфейсом Excel

интерфейсом Excel заданного диапазона................. Диалоговое окно Excel Расши- фильтра с указанием условия ренный фильтр (Advanced Filter) на отбора данных

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

С другой стороны, работа с рас Автофильтр

ширенным фильтром посредством Следующий шаг

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

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

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

268 Часть II Автоматизация Excel Способ обработки исходных данных. Чтобы показать результат фильтра ции, скрыв ненужные строки, установите переключатель Фильтровать список на месте (Filter the list, in place). Чтобы скопировать отфильт рованные строки в другую область листа, установите переключатель Скопировать результат в другое место (Copy to another location).

Условие отбора. Фильтрация с условием позволяет отобрать подмноже ходного диапазона. Фильтрация без условия применяется также при Отбор только уникальных записей. Установите флажок Только уникальные записи (Unique records only), для того чтобы отобрать только уникальные значения из заданного диапазона.

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

Общее число записей неизвестно, однако известно, что данные начинаются с ячейки A2 (1 я строка используется в качестве строки заголовка). Справа от исходных данных на рабочем листе находится пустое пространство.

Отбор уникальных значений из заданного столбца с помощью пользовательского интерфейса Установите указатель ячейки в любом месте исходного диапазона и выберите команду меню Данные Фильтр Расширенный фильтр (Data Filter Advanced Filter). При первом вызове этой команды Excel автоматически подставляет в поле Исходный диапазон (List range) адрес исходного диапазона данных. При после дующих вызовах команды Данные Фильтр Расширенный фильтр Excel под ставляет в поле Исходный диапазон его предыдущее значение.

Установите флажок Только уникальные записи (Unique records only), рас положенный внизу диалогового окна Расширенный фильтр (Advanced Filter).

Установите переключатель Скопировать результат в другое место (Copy to another location) и введите $J$1 в поле Поместить результат в диапазон (Copy to).

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

Сужение диапазона исходных данных до одного столбца Введите в поле Исходный диапазон (List range) адрес диапазона исходных данных, ограниченного столбцом D. В рассматриваемом случае это означает замену адреса $A$1:$H$1127 адресом $D$1:$D$1127, как показано на рис. 11.2.

Недостаток этого метода заключается в том, что Excel запоминает значение по ля Исходный диапазон и автоматически подставляет его при следующем вызове команды Данные Фильтр Расширенный фильтр (Data Filter Advanced Filter). Если позже вам понадобится отобрать уникальные значения из столбца C, вам придется изменить адрес исходного диапазона.

Копирование заголовка столбца исходных данных в первую строку области вставки результатов Не спешите менять адрес исходного диапазона с $A$1:$H$1127 на $D$1:$D$1127. Вместо этого введите в ячейке J1 заголовок столбца D, в дан ном случае 270 Часть II Автоматизация Excel Рис. 11.3. Чтобы не менять адрес исходного диапазона, скопируйте заголовок столбца D в ячейку J Обнаружив в первой строке области вставки результатов заголовок столб ца D, Excel скопирует исходные данные только из этого столбца. Этот способ ограничения результата фильтрации рекомендуется применять при много кратном использовании фильтра. Поскольку Excel запоминает значение поля Исходный диапазон (List range) и автоматически подставляет его при сле дующем вызове команды Данные Фильтр Расширенный фильтр (Data Filter Advanced Filter), вам не придется каждый раз изменять значение ис ходного диапазона.

Результат отбора уникальных значений из столбца D показан на рис. 11.4.

Рис. 11.4. Отбор уникальных значений из заданного столбца — классический пример использо вания расширенного фильтра Отбор уникальных значений из заданного столбца с помощью VBA Команде Данные Фильтр Расширенный фильтр (Data Filter Advanced Filter) соответствует метод VBA.AdvancedFilter. Этот метод имеет 3 параметра.

Способ обработки исходных данных. Чтобы показать результат фильтра ции, скрыв ненужные строки, установите значение параметра Action равным xlFilterInPlace. Чтобы скопировать отфильтрованные стро ки в другую область листа, установите значение параметра Action рав ным xlFilterCopy. В последнем случае установите также значение па раметра CopyToRange, например, CopyToRange:=Range("J1").

Условие отбора. Чтобы задать условие фильтрации, установите значение параметра CriteriaRange, например, CriteriaRange:=Range ("L1:L2"). Для фильтрации без условия не указывайте значение этого параметра.

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

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

Sub GetUniqueCustomers() ' Выделить рабочий лист.

Worksheets("Данные").Select ' Очистить результат предыдущего выполнения макроса.

Range("J1:AZ1").EntireColumn.Delete Dim IRange As Range Dim ORange As Range ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Копирование заголовка столбца D в 1-ю строку 1-го столбца ' области вставки результатов.

' Определение целевого диапазона данных.

Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

IRange.AdvancedFilter Action:=xlFilterCopy, _ 272 Часть II Автоматизация Excel CopyToRange:=ORange, Unique:=True По умолчанию расширенный фильтр копирует все столбцы исходного диапазона. Чтобы ограничиться только столбцом D, скопируйте его заголовок в первую строку области вставки результата.

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

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

Sub UniqueCustomerRedux() ' Копирование заголовка столбца D в ячейку J1.

Range("J1").Value = Range("D1").Value ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, _ CopyToRange:=Range("J1"), Unique:=True Результат выполнения обоих макросов одинаков справа от исходных данных размещается список уникальных значений из столбца D (см. рис. 11.4).

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

Sub RevenueByCustomers() ' Выделить рабочий лист.

Worksheets("Данные").Select ' Очистить результат предыдущего выполнения макроса.

Range("J1:AZ1").EntireColumn.Delete ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Копирование заголовка столбца D в 1-ю строку 1-го столбца ' области вставки результатов.

' Определение целевого диапазона данных.

Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ORange, Unique:=True ' Определение размера списка заказчиков.

LastRow = Cells(65536, NextCol).End(xlUp).Row ' Сортировка списка заказчиков.

Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, _ NextCol), Order1:=xlAscending, Header:=xlYes ' Подсчет выручки, приходящейся на каждого заказчика, ' с помощью формулы массива.

Cells(1, NextCol + 1).Value = "Выручка" Cells(2, NextCol + 1).FormulaArray = "=SUM((R2C4:R" & _ FinalRow & "C4=RC[-1])*R2C6:R" & FinalRow & "C6)" If LastRow > 2 Then Cells(2, NextCol + 1).Copy Cells(3, _ NextCol + 1).Resize(LastRow - 2, 1) End Sub Результат выполнения макроса представлен на рис. 11.5.

Список заказчиков может служить источником дан ных для списка или комбинированного списка, располо женного на пользовательской форме. Создадим макрос, позволяющий генерировать отчет о сделках для выбран ных заказчиков. Добавьте к проекту форму (назовем ее frmReport), разместите на ней список (установите зна чение свойства списка MultiSelect равным frmMulOK, Отмена, Выбрать tiSelectMulti) и 4 кнопки все и Очистить. Процедура UserForm_Initialize ис пользуется для заполнения списка на форме данными, полученными в результате отбора уникальных значений из столбца D и их последующей сортировки.

Private Sub CancelButton_Click() Unload Me End Sub Private Sub cbSubAll_Click() For i = 0 To lbCust.ListCount - Me.lbCust.Selected(i) = True End Sub Private Sub cbSubClear_Click() For i = 0 To lbCust.ListCount - Me.lbCust.Selected(i) = False End Sub Private Sub OKButton_Click() For i = 0 To lbCust.ListCount - If Me.lbCust.Selected(i) = True Then ' Создание отчета.

RunCustReport WhichCust:=Me.lbCust.List(i) 274 Часть II Автоматизация Excel Private Sub UserForm_Initialize() ' Выделить рабочий лист.

Worksheets("Данные").Select ' Очистить результат предыдущего выполнения макроса.

Range("J1:AZ1").EntireColumn.Delete ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Копирование заголовка столбца D в 1-ю строку 1-го столбца ' области вставки результатов.

' Определение целевого диапазона данных.

Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:="", CopyToRange:=ORange, Unique:=True ' Определение размера списка заказчиков.

LastRow = Cells(65536, NextCol).End(xlUp).Row ' Сортировка списка заказчиков.

Cells(1, NextCol).Resize(LastRow, 1).Sort _ Key1:=Cells(1, NextCol), Order1:=xlAscending, Header:=xlYes FinalRow = Range("J65536").End(xlUp).Row For Each cell In Cells(2, NextCol).Resize(LastRow - 1, 1) ' Удаление списка заказчиков.

Cells(1, NextCol).Resize(LastRow, 1).Clear Ниже приведен код вывода формы frmReport на экран:

Sub ShowCustForm() Как показано на рис. 11.6, список заказчиков поддерживает множествен Рис. 11.6. Использование расширенного фильтра — Отбор уникальных значений из комбинации нескольких столбцов с помощью VBA Чтобы отобрать уникальные значения из комбинации нескольких столб цов, скопируйте заголовки этих столбцов в первую строку области вставки ре зультата. Ниже приведен пример отбора уникальных значений из комбинации столбцов B и D.

Sub UniqueCustomerProduct() Dim IRange As Range Dim ORange As Range ' Выделить рабочий лист.

Worksheets("Данные").Select ' Очистить результат предыдущего выполнения макроса.

Range("J1:AZ1").EntireColumn.Delete ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Копирование заголовков столбцов B и D во 2-й и 1-й столбец ' 1-й строки области вставки результатов, соответственно.

' Определение целевого диапазона данных.

Range("D1").Copy Destination:=Cells(1, NextCol) Range("B1").Copy Destination:=Cells(1, NextCol + 1) 276 Часть II Автоматизация Excel Set ORange = Cells(1, NextCol).Resize(1, 2) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора уникальных ' значений из комбинации столбцов B и D.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ORange, Unique:=True ' Определение количества уникальных значений.

LastRow = Cells(65536, NextCol).End(xlUp).Row ' Сортировка полученного результата.

Cells(1, NextCol).Resize(LastRow, 2).Sort Key1:=Cells(1, _ NextCol), Order1:=xlAscending, Key2:=Cells(1, NextCol + 1), _ Order2:=xlAscending, Header:=xlYes Рис. 11.7. Результат от бора всех уникальных комбинаций значений рис. 11.8 показан диапазон условий J1:J2 и диапазон Рис. 11.8. Пример задания параметров расширенного фильтра, сортирующего товары, приобретенные заказчиком CDE INC.

Чтобы отобрать товары, приобретенные заказчиком CDE INC., с помо щью пользовательского интерфейса Excel, выберите команду меню Данные Фильтр Расширенный фильтр (Data Filter Advanced Filter) и заполните поля открывшегося диалогового окна так, как показано на рис. 11.8. Результат отбора представлен на рис. 11.9.

Рис. 11.9. Результат применения расширенного фильтра, сортирующего товары, приобретен ные заказчиком CDE INC.

Аналогичных результатов можно достичь с помощью следующего макроса.

Sub UniqueProductsOneCustomer() Dim IRange As Range Dim ORange As Range Dim CRange As Range ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Определение столбца, по которому будет проводиться фильтрация.

Cells(1, NextCol).Value = Range("D1").Value ' В действительности, значение CDE INC. должно ' вводиться посредством пользовательской формы.

278 Часть II Автоматизация Excel Cells(2, NextCol).Value = "CDE INC."

Set CRange = Cells(1, NextCol).Resize(2, 1) ' Определение целевого диапазона данных.

' Копирование заголовка столбца B1 в столбец L1.

Range("B1").Copy Destination:=Cells(1, NextCol + 2) Set ORange = Cells(1, NextCol + 2) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора уникальных ' комбинаций товаров и заданного заказчика.

IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ CRange, CopyToRange:=ORange, Unique:=True ' Приведенная выше строка может быть записана так:

'IRange.AdvancedFilter xlFilterCopy, CRange, ORange, True ' Определение количества уникальных значений.

LastRow = Cells(65536, NextCol + 2).End(xlUp).Row ' Сортировка полученного результата.

Cells(1, NextCol + 2).Resize(LastRow, 1).Sort Key1:=Cells(1, _ NextCol + 2), Order1:=xlAscending, Header:=xlYes Объединение нескольких условий с помощью логической операции “ИЛИ” Расширенный фильтр позволяет отбирать значения, удовлетворяющие од ному из двух условий, с помощью логической операции ИЛИ. Примером по добного объединения условий является отбор заказчиков, которые приобрели товар ABC или товар XYZ.

Чтобы объединить условия с помощью операции ‘‘ИЛИ’’, разместите их в последовательных строках диапазона условий, как показано на рис. 11.10.

Объединение нескольких условий с помощью логической операции “И” Расширенный фильтр позволяет отбирать значения, удовлетворяющие од новременно двум условиям, с помощью логической операции И. Примером подобного объединения условий является отбор заказчиков, которые приоб рели товар XYZ в западном регионе.

Чтобы объединить условия с помощью операции ‘‘И’’, разместите их в од ной строке диапазона условий, как показано на рис. 11.11.

Рис. 11.10. Диапазон условий J1:J3 ис Рис. 11.11. Диапазон условий J1:K2 использу пользуется для отбора заказчиков, ко ется для отбора заказчиков, которые приоб торые приобрели товар ABC или XYZ рели товар XYZ в западном регионе Дополнительные аспекты объединения условий с помощью логической операции “ИЛИ” Диапазон условий, показанный на рис. 11.12, основан на значении двух различных полей, объединенных с помощью логической операции ‘‘ИЛИ’’.

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

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

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

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

А теперь представьте, что на некотором диапазоне исходных данных пользова тель выбрал 10 товаров, 9 регионов и 499 заказчиков. Поскольку диапазон ус ловий должен содержать все возможные комбинации значений полей, по кото рым проводится отбор, его размер превысит 44 000 строк. Попробуйте создать 280 Часть II Автоматизация Excel подобный фильтр, и вы вскоре поймете, что на его применение может уйти це лая вечность.

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

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

Рассмотрим задачу отбора всех записей, для которых процент валовой при были не превышает 53%. Оставим ячейку J1 пустой, а в ячейку J2 поместим булеву формулу =(H2/F2) 2 Then ' Использование относительных ссылок на строку R2 обязательно.

' В англоязычной версии Excel:

' MyFormula = "=NOT(ISNA(MATCH(RC" & MyColumn & ",R2C" & _ ' NextTCol & ":R" & NextRow - 1 & "C" & NextTCol & ",False)))" ' Cells(2, NextCCol).FormulaR1C1 = MyFormula MyFormula = "=НЕ(ЕНД(ПОИСКПОЗ(RC" & _ MyColumn & ";R2C" & NextTCol & ":R" & NextRow - 1 & "C" & _ NextTCol & ";Ложь)))" Cells(2, NextCCol).FormulaR1C1Local = MyFormula Unload Me ' На рис. 11.15 показано текущее содержимое рабочего листа.

' Закрыть форму и создать расширенный фильтр с диапазоном ' условий на основе построенных выше формул.

If NextCCol > 10 Then Set CRange = Range(Cells(1, 10), Cells(2, NextCCol - 1)) Set IRange = Range("A1").CurrentRegion Set ORange = Cells(1, 20) IRange.AdvancedFilter xlFilterCopy, CRange, ORange ' Очистить диапазон условий.

Cells(1, 10).Resize(1, 10).EntireColumn.Clear ' Вывести сообщение.

MsgBox "Область вставки результата применения фильтра _ начинается с ячейки T1" End Sub 284 Часть II Автоматизация Excel Private Sub UserForm_Initialize() ' Определение размера диапазона исходных данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Определение целевого диапазона данных.

' Копирование заголовка столбца D1 в столбец J1.

Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:="", CopyToRange:=ORange, Unique:=True ' Определение размера списка заказчиков.

LastRow = Cells(65536, NextCol).End(xlUp).Row ' Сортировка списка заказчиков.

Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, _ NextCol), Order1:=xlAscending, Header:=xlYes FinalRow = Range("J65536").End(xlUp).Row For Each cell In Cells(2, NextCol).Resize(LastRow - 1, 1) ' Удаление списка заказчиков.

Cells(1, NextCol).Resize(LastRow, 1).Clear ' Определение целевого диапазона данных.

' Копирование заголовка столбца B1 в столбец J1.

Range("B1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца B.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ORange, Unique:=True ' Определение размера списка товаров.

LastRow = Cells(65536, NextCol).End(xlUp).Row ' Сортировка списка товаров.

Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, _ NextCol), Order1:=xlAscending, Header:=xlYes FinalRow = Range("J65536").End(xlUp).Row For Each cell In Cells(2, NextCol).Resize(LastRow - 1, 1) ' Удаление списка товаров.

Cells(1, NextCol).Resize(LastRow, 1).Clear ' Определение целевого диапазона данных.

' Копирование заголовка столбца A1 в столбец J1.

Range("A1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца A.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ORange, Unique:=True ' Определение размера списка регионов.

LastRow = Cells(65536, NextCol).End(xlUp).Row ' Сортировка списка регионов.

Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, _ NextCol), Order1:=xlAscending, Header:=xlYes With Me.lbRegion FinalRow = Range("J65536").End(xlUp).Row For Each cell In Cells(2, NextCol).Resize(LastRow - 1, 1) ' Удаление списка регионов.

Cells(1, NextCol).Resize(LastRow, 1).Clear End Sub На рис. 11.15 показано содержимое рабочего листа перед выполнением ме тода AdvancedFilter.

Макрос помещает выбранные пользователем данные (заказчиков, товары и регионы) в столбцы O, P и Q, а затем определяет диапазон условий как J1:L2.

Формула в ячейке J2 проверяет, входит ли значение в ячейке $D2 в список за казчиков в столбце O. Формулы в ячейках K2 и L2 осуществляют аналогичную проверку для ячеек $B2, $A2 и столбцов P, Q, соответственно.

Внимание В справочной системе Excel VBA сказано, что для отбора данных без применения условия достаточно не задать диапазон условий. В Excel 2003 это не так — если вы не определите диапазон условий, метод AdvancedFilter будет использовать значение CriteriaRange, заданное при предыдущем вызове этого метода. Что бы избежать недоразумений, очистите значение CriteriaRange, например, укажите CriteriaRange="" при вызове метода AdvancedFilter.

286 Часть II Автоматизация Excel Рис. 11.15. Содержимое рабочего листа перед применением расширенного фильтра Использование условия на основе формулы при решении экономических задач Следует признать, что задание диапазона условий расширенного фильтра с помощью формулы эффективное, но редко используемое решение.

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

=$A2>СРЗНАЧ($A$2:$A$60000) (В англоязычной версии Excel следует использовать формулу =$A2>AVERAGE( $A$2:$A$60000).) Отбор пустого множества записей Условие расширенного фильтра может быть задано таким образом, что в результате применения последнего будет отобрано пустое множество записей.

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

Фильтрация диапазона исходных данных “на месте” При фильтрации диапазона исходных данных ‘‘на месте’’ нет нужды ука зывать область вставки результата применения фильтра. А вот задание диапа зона условий является обязательным в противном случае фильтр отберет 100% исходных строк.

Обычно фильтрация ‘‘на месте’’ выполняется с помощью пользователь ского интерфейса Excel. Чтобы выделить строки, отобранные в результате фильтрации ‘‘на месте’’, необходимо использовать метод VBA SpecialCells с параметром xlCellTypeVisible. Аналогичное действие в пользователь ском интерфейсе Excel заключается в выборе команды Правка Перейти (Edit Go To), щелчке на кнопке Выделить (Special) в диалоговом окне Переход (Go To) и установке переключателя Только видимые ячейки (Visible cells only) в диалоговом окне Выделение группы ячеек (Go To Special) (рис. 11.16).

Рис. 11.16. Фильтрация “на месте” позволяет скрыть строки, не удовлетворяю щие заданному условию. Чтобы выделить строки, отобранные в результате фильтрации “на месте”, необходимо использовать метод VBA SpecialCells с параметром xlCellTypeVisible Чтобы применить фильтр ‘‘на месте’’, вызовите метод AdvancedFilter, установив значение параметра Action равным xlFilterInPlace и опустив параметр CopyToRange, как показано ниже:

IRange.AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=CRange, Unique:=False 288 Часть II Автоматизация Excel Следующий код подсчитывает количество видимых строк в исходном диа пазоне данных после применения фильтрации ‘‘на месте’’:

For Each cell In Range("A2:A" & FinalRow).SpecialCells( _ xlCellTypeVisible) MsgBox Ctr & " строк удовлетворяют заданному критерию" Отбор пустого множества записей Условие расширенного фильтра может быть задано таким образом, что в ре зультате применения последнего будет отобрано пустое множество записей.

Чтобы определить данную ситуацию при фильтрации ‘‘на месте’’, следует про верить, возвращает ли метод SpecialCells ошибку времени выполнения (не найдено ни одной ячейки, удовлетворяющей заданным условиям).

Для этого воспользуемся универсальной ловушкой ошибок, как показано ниже. (Более подробно обработка ошибок рассматривается в главе 23, ‘‘Обработка ошибок’’.) For Each cell In Range("A2:A" & FinalRow).SpecialCells( _ xlCellTypeVisible) MsgBox Ctr & " строк удовлетворяют заданному критерию" Range("A1").Select MsgBox "Нет строк, удовлетворяющих заданному критерию" Чтобы подобная ловушка сработала, следует исключить строку заголовка из диапазона ячеек, передаваемого методу SpecialCells. В противном слу чае метод SpecialCells не сгенерирует ошибку 1004, поскольку строка за головка остается видимой и после применения расширенного фильтра.

Отображение записей, скрытых в результате фильтрации “на месте” Чтобы отобразить все строки исходного диапазона, скрытые в результате применения расширенного фильтра ‘‘на месте’’, воспользуйтесь методом ShowAllData, как показано ниже:

ActiveSheet.ShowAllData Отбор только уникальных записей при фильтрации “на месте” В результате отбора только уникальных записей при фильтрации ‘‘на мес те’’ расширенный фильтр скроет строки, в которых одинаковыми являются значения всех столбцов исходного диапазона. Другими словами, фильтрация ‘‘на месте’’ не позволяет отобрать строки с уникальной комбинацией только некоторого подмножества столбцов исходного диапазона, например, столбца с названием фирмы заказчика и столбца с наименованием товара.

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

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

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

Чтобы отобрать все записи, удовлетворяющие заданному критерию, сбросьте флажок Только уникальные записи (Unique records only) в диалого вом окне Расширенный фильтр (Advanced Filter) (если расширенный фильтр создается с помощью пользовательского интерфейса Excel) или установите значение параметра Unique метода AdvancedFilter равным False (если расширенный фильтр создается с помощью VBA).

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

В следующих разделах рассматриваются различные примеры использова ния расширенного фильтра.

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

Sub AllColumnsOneCustomer() Dim IRange As Range Dim ORange As Range Dim CRange As Range ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Определение столбца, по которому будет проводиться фильтрация.

Cells(1, NextCol).Value = Range("D1").Value ' В действительности, значение CDE INC. должно ' вводиться посредством пользовательской формы.

290 Часть II Автоматизация Excel Cells(2, NextCol).Value = "CDE INC."

Set CRange = Cells(1, NextCol).Resize(2, 1) ' Определение целевого диапазона данных (пустая ячейка).

Set ORange = Cells(1, NextCol + 2) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора строк, ' удовлетворяющих заданному условию.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=CRange, CopyToRange:=ORange Range("L1").Select Результат выполнения приведенного выше макроса показан на рис. 11.17.

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

Форма frmReport предназначается для создания отчета о сделках для вы бранных пользователем заказчиков. Создание отчета осуществляется с помо щью процедуры RunCustReport, которая принимает в качестве параметра имя заказчика.

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

Следующий код копирует соответствующие заголовки столбцов в первую строку области вставки результата применения расширенного фильтра. Метод AdvancedFilter отбирает строки, удовлетворяющие заданному условию, как показано на рис. 11.18.

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

показан на рис. 11.19.

Рис. 11.19. Отчет о сделках для заказчика CDE INC.

Sub RunCustReport(WhichCust As Variant) Dim IRange As Range Dim ORange As Range Dim CRange As Range Dim WBN As Workbook Dim WSN As Worksheet Dim WSO As Worksheet Set WSO = ActiveSheet ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + ' Определение условия отбора.

Cells(1, NextCol).Value = Range("D1").Value Cells(2, NextCol).Value = WhichCust Set CRange = Cells(1, NextCol).Resize(2, 1) 292 Часть II Автоматизация Excel ' Определение целевого диапазона данных.

' В целевой диапазон войдут столбцы C (Дата), ' E (Количество), B (Товар) и F (Выручка).

Cells(1, NextCol + 2).Resize(1, 4).Value = Array(Cells(1, _ 3), Cells(1, 5), Cells(1, 2), Cells(1, 6)) Set ORange = Cells(1, NextCol + 2).Resize(1, 4) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора строк, ' удовлетворяющих заданному условию.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=CRange, CopyToRange:=ORange ' Содержимое рабочего листа на текущий ' момент показано на рис. 11.18.

' Создание новой рабочей книги для размещения ' результата применения расширенного фильтра.

Set WBN = Workbooks.Add(xlWBATWorksheet) Set WSN = WBN.Worksheets(1) ' Определение заголовка отчета.

WSN.Cells(1, 1).Value = "Отчет о сделках для заказчика " _ & WhichCust ' Копирование данных с текущего активного ' рабочего листа в новую рабочую книгу.

WSO.Cells(1, NextCol + 2).CurrentRegion.Copy _ Destination:=WSN.Cells(3, 1) TotalRow = WSN.Cells(65536, 1).End(xlUp).Row + WSN.Cells(TotalRow, 1).Value = "Всего" ' В англоязычной версии Excel:

' WSN.Cells(TotalRow, 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)" ' WSN.Cells(TotalRow, 4).FormulaR1C1 = "=SUM(R2C:R[-1]C)" WSN.Cells(TotalRow, 2).FormulaR1C1Local = "=СУММ(R2C:R[-1]C)" WSN.Cells(TotalRow, 4).FormulaR1C1Local = "=СУММ(R2C:R[-1]C)" ' Стилевое форматирование отчета.

WSN.Cells(3, 1).Resize(1, 4).Font.Bold = True WSN.Cells(TotalRow, 1).Resize(1, 4).Font.Bold = True WSN.Cells(1, 1).Font.Size = WBN.SaveAs "C:\" & WhichCust & ".xls" WBN.Close SaveChanges:=False ' Очистить область вставки результата ' применения расширенного фильтра.

Range("J1:Z1").EntireColumn.Clear Процедура RunCustReport — это простой, однако весьма эффективный способ создания отчетов, который может применить на практике любой поль зователь Excel.

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

1. Первый расширенный фильтр используется для создания списка заказчиков в столбце J. Параметр Unique метода AdvancedFilter имеет значение True, а параметр CopyToRange содержит ссылку на ячейку J1, содержащую заголо вок столбца D.

' Первый расширенный фильтр - создание ' списка заказчиков в столбце J.

' Определение целевого диапазона.

' Копирование заголовка столбца D в ячейку J1.

Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", _ CopyToRange:=ORange, Unique:=True 2. Для каждого заказчика из списка выполняются действия, описанные в пп. 3–7.

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

' Цикл по списку заказчиков.

FinalCust = Cells(65536, NextCol).End(xlUp).Row For Each cell In Cells(2, NextCol).Resize(FinalCust - 1, 1) ThisCust = cell.Value ' Выполнение действий, описанных в пп. 3-7.

Next Cell 3. Условие отбора второго расширенного фильтра содержится в ячейках L1:L (заголовок столбца D в ячейке L1, имя заказчика — в ячейке L2).

' Определение условия отбора.

Cells(1, NextCol + 2).Value = Range("D1").Value Cells(2, NextCol + 2).Value = ThisCust Set CRange = Cells(1, NextCol + 2).Resize(2, 1) 4. Второй расширенный фильтр используется для копирования строк, удовлетво ряющих заданному условию, в область, начинающуюся со столбца N. Параметр Unique метода AdvancedFilter имеет значение False, а параметр CopyToRange представляет собой ссылку на диапазон ячеек N1:Q1, содержащий заго ловки необходимых столбцов исходного диапазона данных.

294 Часть II Автоматизация Excel ' Определение целевого диапазона данных.

' В целевой диапазон войдут столбцы C (Дата), ' E (Количество), B (Товар) и F (Выручка).

Cells(1, NextCol + 4).Resize(1, 4).Value = Array(Cells(1, 3), _ Cells(1, 5), Cells(1, 2), Cells(1, 6)) Set ORange = Cells(1, NextCol + 4).Resize(1, 4) ' Второй расширенный фильтр - отбор строк, ' удовлетворяющих заданному условию.

IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CRange, CopyToRange:=ORange 5. Результат применения второго расширенного фильтра копируется в новую ра бочую книгу. Для создания рабочей книги используется метод Workbooks.Add.

' Создание новой рабочей книги для размещения ' результата применения расширенного фильтра.

Set WBN = Workbooks.Add(xlWBATWorksheet) Set WSN = WBN.Worksheets(1) ' Определение заголовка отчета.

WSN.Cells(1, 1).Value = "Отчет о сделках заказчика " & ThisCust ' Копирование данных с текущего активного ' рабочего листа в новую рабочую книгу.

WSO.Cells(1, NextCol + 4).CurrentRegion.Copy _ Destination:=WSN.Cells(3, 1) 6. Последний штрих — добавление заголовка отчета и итоговой строки. Вдобавок, строка заголовков столбцов и итоговая строка выделяются полужирным ' Определение заголовка отчета.

WSN.Cells(1, 1).Value = "Отчет о сделках заказчика " & ThisCust TotalRow = WSN.Cells(65536, 1).End(xlUp).Row + WSN.Cells(TotalRow, 1).Value = "Всего" ' В англоязычной версии Excel:

' WSN.Cells(TotalRow, 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)" ' WSN.Cells(TotalRow, 4).FormulaR1C1 = "=SUM(R2C:R[-1]C)" WSN.Cells(TotalRow, 2).FormulaR1C1Local = "=СУММ(R2C:R[-1]C)" WSN.Cells(TotalRow, 4).FormulaR1C1Local = "=СУММ(R2C:R[-1]C)" ' Стилевое форматирование отчета.

WSN.Cells(3, 1).Resize(1, 4).Font.Bold = True WSN.Cells(TotalRow, 1).Resize(1, 4).Font.Bold = True WSN.Cells(1, 1).Font.Size = 7. Новая рабочая книга сохраняется в файле с именем, совпадающим с названием соответствующей фирмы заказчика, после чего эта книга закрывается. Перед переходом к следующей итерации цикла макрос очищает область вставки ре зультата выполнения обоих расширенных фильтров.

WBN.SaveAs "C:\" & ThisCust & ".xls" WBN.Close SaveChanges:=False ' Очистить область вставки результата ' применения расширенных фильтров.

Cells(1, NextCol + 2).Resize(1, 10).EntireColumn.Clear Ниже приведен полный код макроса RunReportForEachCustomer.

Sub RunReportForEachCustomer() Dim IRange As Range Dim ORange As Range Dim CRange As Range Dim WBN As Workbook Dim WSN As Worksheet Dim WSO As Worksheet Set WSO = ActiveSheet ' Определение размера исходного диапазона данных.

FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + Первый расширенный фильтр - создание списка заказчиков в столбце J.

Определение целевого диапазона.

Копирование заголовка столбца D в ячейку J1.

Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol) ' Определение исходного диапазона данных.

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2) ' Применение расширенного фильтра для отбора ' уникальных значений из столбца D.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:="", CopyToRange:=ORange, Unique:=True FinalCust = Cells(65536, NextCol).End(xlUp).Row ' Цикл по списку заказчиков.

For Each cell In Cells(2, NextCol).Resize(FinalCust - 1, 1) ThisCust = cell.Value ' Определение условия отбора.

Cells(1, NextCol + 2).Value = Range("D1").Value Cells(2, NextCol + 2).Value = ThisCust Set CRange = Cells(1, NextCol + 2).Resize(2, 1) ' Определение целевого диапазона данных.

' В целевой диапазон войдут столбцы C (Дата), ' E (Количество), B (Товар) и F (Выручка).

Cells(1, NextCol + 4).Resize(1, 4).Value = _ Array(Cells(1, 3), Cells(1, 5), Cells(1, 2), Cells(1, 6)) Set ORange = Cells(1, NextCol + 4).Resize(1, 4) ' Второй расширенный фильтр - отбор строк, ' удовлетворяющих заданному условию.

IRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=CRange, CopyToRange:=ORange ' Создание новой рабочей книги для размещения 296 Часть II Автоматизация Excel ' результата применения расширенного фильтра.

Set WBN = Workbooks.Add(xlWBATWorksheet) ' Определение заголовка отчета.

WSN.Cells(1, 1).Value = "Отчет о сделках заказчика " _ ' Копирование данных с текущего активного ' рабочего листа в новую рабочую книгу.

WSO.Cells(1, NextCol + 4).CurrentRegion.Copy _ Destination:=WSN.Cells(3, 1) TotalRow = WSN.Cells(65536, 1).End(xlUp).Row + WSN.Cells(TotalRow, 1).Value = "Всего" ' В англоязычной версии Excel:

' WSN.Cells(TotalRow, 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)" ' WSN.Cells(TotalRow, 4).FormulaR1C1 = "=SUM(R2C:R[-1]C)" WSN.Cells(TotalRow, 2).FormulaR1C1Local = _ "=СУММ(R2C:R[-1]C)" WSN.Cells(TotalRow, 4).FormulaR1C1Local = _ "=СУММ(R2C:R[-1]C)" ' Стилевое форматирование отчета.

WSN.Cells(3, 1).Resize(1, 4).Font.Bold = True WSN.Cells(TotalRow, 1).Resize(1, 4).Font.Bold = True ' Очистить область вставки результата ' применения расширенных фильтров.

Cells(1, NextCol + 2).Resize(1, 10).EntireColumn.Clear Cells(1, NextCol).EntireColumn.Clear MsgBox FinalCust - 1 & " отчетов были успешно созданы!" Подведем итог. Комбинация двух расширенных фильтров позволила создать 27 отчетов менее чем за 1 минуту (рис. 11.20).

С учетом того, что опытные пользователи Excel создают один отчет в среднем за 2– 3 минуты, макрос RunReportForEachCustomer позволяет сэкономить около 1 часа рабочего времени.

Рис. 11.20. Создание 27 отчетов менее чем за одну минуту — весьма непло хой результат для комбинации двух расширенных фильтров!

Автофильтр Автофильтр является упрощенным вариантом расширенного фильтра и обычно применяется посредством пользовательского интерфейса.

Тем не менее, существует один аспект автофильтра, доступный исключи тельно посредством VBA. При выборе команды Данные Фильтр Автофильтр (Data Filter AutoFilter) справа от названий столбцов в фильтруемом диапазоне появляются кнопки с изображением указывающей вниз стрелки.

Чтобы скрыть кнопки для столбцов, по которым не нужно проводить фильт рацию, воспользуйтесь VBA. Ниже приведен пример скрытия кнопок раскры вающегося списка для столбцов C (дата), E (количество), F (выручка), G (себестоимость) и H (прибыль):

Sub AutoFilterCustom() Range("A1").AutoFilter Field:=3, VisibleDropDown:=False Range("A1").AutoFilter Field:=5, VisibleDropDown:=False Range("A1").AutoFilter Field:=6, VisibleDropDown:=False Range("A1").AutoFilter Field:=7, VisibleDropDown:=False Range("A1").AutoFilter Field:=8, VisibleDropDown:=False End Sub Параметр VisibleDropDown уникален тем, что он доступен только через программный код. Выполнить аналогичное действие посредством пользова 298 Часть II Автоматизация Excel тельского интерфейса Excel не представляется возможным. ‘‘Модифицируйте’’ подобным образом автофильтр, и вы прославитесь как знаток своего дела в гла зах коллег, ничего не подозревающих об удивительных возможностях VBA. Ре зультат выполнения макроса AutoFilterCustom показан на рис. 11.21.

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

Комбинация расширенного фильтра и сводной таблицы это настоящая гремучая смесь, позволяющая делать с исходными данными все, что вам за благорассудится!

Глава Впервые концепция сводных таб лиц была представлена компанией Lotus в продукте Improv.

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

Впервые сводные таблицы были Создание отчета представлены в Excel 95. В Excel 97 реа лизация сводных таблиц была значи тельно улучшена, а в Excel 2000 — кардинальным образом изменена.

В Excel 2002 к сводным таблицам были добавлены несколько новых Создавая программный код в Ex минимум, максимум и др.......... cel 2003, следует уделить особое вни Дополнительные вычисления мание его совместимости с Excel 2000 в полях области данных и Excel 97. И если совместимость с Excel 2000 достигается за счет внесе ния в код нескольких небольших из менений, то совместимость с Excel требует его полного пересмотра. Учи тывая, что Microsoft прекратила под держку Excel 97, а возраст самого продукта превышает 7 лет, в этой главе будет использоваться кэш сводных таблиц, впервые представ 300 Часть II Автоматизация Excel ленный в Excel 2000. Кроме того, в конце главы будет рассмотрен метод PivotTableWizard единственный способ создания кода, совместимого с Excel 97.

Создание сводных таблиц с помощью пользовательского интерфейса Excel По имеющейся у Microsoft информации, сводные таблицы применяют около 7% пользователей Excel. В то же время, исследование компании MrEx cel Consulting показало, что сводные таблицы применяют около 42% опытных пользователей Excel. Как бы то ни было, о том, что такое сводная таблица, знают далеко не все. Рассмотрим создание простой сводной таблицы с помо щью пользовательского интерфейса Excel.

Предположим, что исходные данные занимают на рабочем листе свыше 12 000 строк (рис. 12.1).

Рис. 12.1. Сводная таблица позволяет подсчитать суммарные значения для большого объема исходных данных Задача заключается в подсчете суммарного дохода по регионам (строки це левой таблицы) и товарам (столбцы целевой таблицы). Для ее решения созда дим сводную таблицу, выполнив следующие действия.

1. Выделите ячейку, принадлежащую диапазону исходных данных, и вы берите команду меню Excel Данные Сводная таблица (Data Pivot Table and PivotChart Report).

2. В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм — шаг 1 из 3 (PivotTable and PivotChart Wizard — Step 1 of 3) установите переключатели В списке или базе данных Microsoft Office Excel (Microsoft Office Excel list or database) и Сводная таблица (PivotTable).

3. Убедитесь, что в поле Диапазон (Range) диалогового окна Мастер сводных таблиц и диаграмм — шаг 2 из 3 (PivotTable and PivotChart Wizard Step 2 of 3) указан верный адрес диапазона исходных данных.

4. В диалоговом окне Мастер сводных таблиц и диаграмм — шаг 3 из (PivotTable and PivotChart Wizard — Step 3 of 3) установите переключа тель Существующий лист (Existing worksheet) и укажите адрес первой ячейки диапазона, в который необходимо поместить сводную таблицу.

Щелкните на кнопке Макет (Layout) (рис. 12.2).

Рис. 12.2. Определите макет сводной таблицы 5. В диалоговом окне Мастер сводных таблиц и диаграмм — макет (PivotTable and PivotChart Wizard и отпустите ее над областью Строка (ROW). Аналогичным образом пе ретащите кнопку Товар и отпустите ее над областью Столбец (COLUMN). Наконец, перетащите кнопку Выручка и отпустите ее над областью Данные (DATA). Если столбец Выручка содержит только чи словые сведения, при отпускании над областью Данные надпись на кнопке Выручка изменится на Сумма по полю Выручка (Sum of Выруч ка), как показано на рис. 12.3. Щелкните на кнопке OK, для того чтобы вернуться к диалоговому окну Мастер сводных таблиц и диаграмм — 6. Щелкните на кнопке Готово (Finish). Практически мгновенно Excel сгенерирует сводную таблицу на основе исходных данных, как показано на рис. 12.4.

302 Часть II Автоматизация Excel Рис. 12.3. Чтобы определить макет сводной таблицы, перетащите кнопки, соответствующие требуемым столбцам исходных данных, и отпустите их над областями Строка, Столбец и Данные диалогового Рис. 12.4. Сводная таблица предельно лаконична Внимание В Excel 97 мастер сводных таблиц предполагает выполнение четырех шагов вместо трех. Шаг 3 мастера сводных таблиц Excel 97 заключается в определении макета сводной таблицы. Чтобы определить макет сводной таблицы в Excel 2003, щелк ните на кнопке Макет (Layout) диалогового окна Мастер сводных таблиц и диаграмм — шаг 3 из 3 (PivotTable and PivotChart Wizard — Step 3 of 3).

Поместив сводную таблицу на рабочий лист, вы можете изменять ее макет путем перетаскивания полей из окна Список полей сводной таблицы (PivotTable Field List) в сводную таблицу, и наоборот. Например, на рис. 12. показана сводная таблица, полученная в результате перетаскивания поля Регион в область столбцов, поля Товар в область строк, и добавления в об ласть строк поля Заказчик.

Рис. 12.5. Сводная таблица, полученная в результате перетаскивания поля Регион в область столбцов, поля Товар — в область строк, и добавления в область строк по ля Заказчик Создание сводных таблиц с помощью VBA Чтобы создать сводную таблицу с помощью VBA в Excel 2000 и более позд них версиях Excel, сперва необходимо создать объект кэша сводных таблиц, как показано далее:

304 Часть II Автоматизация Excel Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim FinalRow As Long Set WSD = Worksheets("Данные") ' Удалить существующие сводные таблицы.

For Each PT In WSD.PivotTables PT.TableRange2.Clear ' Задать диапазон исходных данных и создать ' объект кэша сводных таблиц.

FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8) Set PTCache = ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlDatabase, SourceData:=PRange.Address) После создания кэша сводных таблиц в него можно добавить новую свод ную таблицу с помощью метода CreatePivotTable:

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range( _ "J2"), TableName:="PivotTable1") Параметр TableDestination метода CreatePivotTable определяет размещение сводной таблицы, а параметр TableName — ее имя. Результат выполнения приведенной выше строки кода показан на рис. 12.6.

Рис. 12.6. В результате выполнения метода CreatePivotTable Excel создает пустую свод ную таблицу, состоящую из четырех ячеек Excel не пересчитывает сводную таблицу при добавлении полей к ее макету с помощью пользовательского интерфейса, однако по умолчанию пересчиты вает сводную таблицу на каждом шаге ее построения с помощью VBA. Чтобы повысить эффективность программного кода, временно запретите пересчет сводной таблицы с помощью свойства ManualUpdate:

PT.ManualUpdate = True Теперь все готово для создания макета сводной таблицы с помощью VBA.

Воспользовавшись методом AddFields, добавьте поля к области строк, об ласти столбцов или области страницы сводной таблицы:

' Добавить поля к области строк и области столбцов сводной таблицы.