Методы анализа данных в Ms Excel

Рис.10. Структура программы VBA

Рис.9. Первый уровень иерархического дерева объектов Excel

На данном рисунке имеются следующие обозначения элементов иерархии:

Object and collection – объекты и коллекции;

Object only – только объекты.

Click arrow to expand chart – переход на один уровень ниже (от объекта к подобъекту). Во встроенной справке на иерархическом дереве объектов простые объекты окрашены бирюзовым цветом, а объекты, являющиеся также и семействами — желтым.

Полная ссылка на объект состоит из ряда имен вложенных последовательно друг друга объектов. Разделителями имен объектов в этом ряду являются точки. Ряд начинается с объекта Application (Приложение) и заканчивается именем самого объекта. Например, полная ссылка на ячейку A1 рабочего листа Урожай рабочей книги с именем Сельхоз_культуры имеет вид:

Application.Workbooks(«Сельхоз_культуры»).Worksheets(«Урожай»).Range(«А1»)

Приводить каждый раз полную ссылку на объект не обязательно. Обычно достаточно ограничиться неявной ссылкой на объект. В неявной ссылке, в отличие от полной, объекты, которые активны в данный момент, можно опускать. Например, в рассмотренном выше случае, если ссылка на ячейку А1 дана в программе, выполняемой в среде Excel, то ссылка на объект Application, может быть опущена, т.е. достаточно привести относительную ссылку:

Workbooks(«Сельхоз_культуры»).Worksheets(«Урожай»).Range(«А1»)

Если и рабочая книга Сельхоз_культуры и рабочий лист Урожай активны, то в относительной ссылке достаточно ограничиться упоминанием только диапозона А1:

Range(«А1»)

В VBA каждый объект обладает определенными свойствами (Properties), описывающими этот объект или его состояние.

Например, в приложении Excel объект Range (диапазон ячеек рабочего листа) имеет такие свойства, как:

Font — используемый шрифт;

Formula — определяет содержимое ячейки или диапазона ячеек;

Value — определяет значение ячейки и многие, многие другие свойства;

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

Объект.Свойство

Например, для ссылки на значение активной ячейки используется следующая запись:

ActiveCell.Value

К некоторой путанице может привести то, что некоторые свойства объектов сами в свою очередь могут выступать в качестве объектов. Так, например, объект Application имеет свойство ActiveWindow, которое показывает, какое окно является активным в настоящий момент. В то же время ActiveWindow является объектом семейства Windows, который может иметь свойство ActiveCell, в свою очередь являющееся объектом класса Range. Кроме этого, ActiveCell имеет свойство Font, также являющееся объектом, имеющим такие свойства, как Name, Size или Bold.

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

Таблица 3. Свойства объектов Excel

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

Объект.Свойство = НовоеЗначение

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

Числовое значение. Например, при установлении размера шрифта может использоваться следующий оператор:

ActiveCell.Font.Size = 14

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

ActiveCell.Font.Name = "Courier New Cyr"

Логическое значение. В VBA используется обозначение True и False, в отличие от функций рабочего листа, где используется обозначение ИСТИНА и ЛОЖЬ. Вот пример изменения начертания шрифта (применение курсива):

ActiveCell.Font.Italic = True

Методы

Объект сам по себе не представляет большого значения. намного важнее то какие действия можно совершать над объектом. Метод (Methods) как раз и представляет собой действие, выполняемое над объектом. Синтаксис применения метода:

Объект.Метод

Например, при помощи метода Save (Сохранить) сохраняется рабочая книга (объект Workbook):

Workbook(«Сельхоз_культуры».Save

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

К наиболее распространенным методам Excel можно отнести следующие.

Таблица 4. Методы объектов Excel

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

Использованию переменной обычно предшествует ее объявление (обозначение свойств и возможностей) посредством оператора Dim, в котором указываются имя переменной и тип данных, для хранения значений которого она предназначена. Хотя такой оператор не является обязательным, тем не менее, хороший стиль программирования требует предварительного описания всех используемых переменных. Имена переменных могут иметь длину до 255 символов (используются и символы кириллицы) и не должны содержать пробелов, знаков препинания, специальных символов. Они не могут совпадать с ключевыми словами VBA и именами стандартных объектов.

В процедурах и функциях VBA могут использоваться переменные следующих типов данных:

- Boolean — логические переменные, принимающие одно из двух значений: True или False;

- Byte — целое число из диапазона от 0 до 255;

- Currency — переменные для денежных вычислений с фиксированным количеством разрядов после десятичной запятой; позволяют избежать накопления погрешностей при округлении;

- Date — переменные для хранения даты и времени;

- Double — переменные, использующиеся для хранения значений с плавающей точкой двойной точности;

- Integer — целые числа из диапазона от -32 768 до 32 767;

- Long - целые числа из диапазона от -2 147 483 648 до 2 147 483 647;

- Object — переменные для хранения ссылок на объекты;

- Single — переменные для хранения значений одинарной точности с плавающей точкой;

- String — переменные для хранения строк символов длиной от 0 до 64 Кбайт;

- Variant — переменные, тип которых определяется типом последнего присвоенного им значения,

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

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

Существуют три уровня видимости переменной.

- Процедура (областью видимости является только та процедура, в которой переменная объявлена).

- Модуль (область видимости — все процедуры модуля, в котором переменная объявлена).

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

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

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

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

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

[Private | Public | Friend] [Static] Sub имя_процедуры (аргумент _1, аргумент_2. ...аргумент_n)

<оператор VBA>

<оператор VBA>

<оператор VBA >

End Sub

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

[Private | Public | Friend] [Static] Function имя_функции (аргумент_1, аргумент __2, ...аргумент_n) As тип_возвращаемого_значения

<оператор VBA>

<оператор VBA>

имя_функции= возвращаемое_значение

End Function

Ключевое слово Private задает следующую область видимости для процедуры - модуль, в котором она описана. Значит, ее могут вызывать только процедуры этого же модуля. Ключевое слово Publ ic, наоборот, объявляет процедуру доступной для всех модулей проекта. С помощью ключевого слова Friend процедура или функция становится видимой только в том проекте, где был описан класс, членом которого она является. По умолчанию любая процедура общедоступна, то есть имеет статус Public.

За операторами объявления следует необязательное ключевое слово Static, объявляющее все локальные переменные статическими.

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

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

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

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

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

Как правило, программа на языке VBA состоит из нескольких процедур и функций. Описание этих программных единиц хранится в модулях. Модули, в свою очередь, как составные части входят в проекты. Проекты сохраняются в файлах рабочих книг Excel 2003, но работать с ними можно лишь при наличии отдельного приложения — редактора Visual Basic. Если нужно вызвать процедуру или функцию, описание которой находится в другом модуле, ее имя при вызове указывается следующим образом: имя_модуля.иня_процедуры. Но если описание процедуры или функции содержится в другом проекте, при ее вызове следует использовать другой синтаксис:

имя_проекта.имя_модуля.имя_процедуры.

2.2.1 Средства анализа «что-если»

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

Анализ «что-если» предполагает изменение значений ячеек для выяснения того, как эти изменения повлияют на результаты выполнения формул на листе.

В Ms Excel предусмотрены средства анализа «что-если» трех типов:

- сценарии;

- таблицы данных;

- подбор параметров.

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

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

Запустить средство анализа данных «что-если» можно с вкладки Данные Ms Excel.

Рис.11. Вкладки Данные, содержащей средство анализа «что-если»

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