к занятиям по офисному программированию

Методические указания 1

к занятиям по офисному программированию

1. Автоматизация обработки данных в Excel с помощью элементов управления

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

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

Элементы управления размещаются непосредственно на рабочих листах обычно рядом с клетками, в которых обрабатываются (выбираются, рассчитываются) данные, и интегрируются в процесс обработки настройкой параметров или кодом VBA. В рабочий лист можно вставить такие стандартные элементы управления Windows, как флажки, списки, поля со списками, переключатели, кнопки и др. Вставка выполняется из панели инструментов Формы (меню Вид/Панели инструментов в Excel 97,… 2003) путем вычерчивания на рабочем листе мышью прямоугольной зоны в том месте, где он должен располагаться. Размеры зоны будут ограничивать размеры элемента. Элемент не обязательно должен располагаться в пределах одной клетки, но он не должен «залезать» на клетку, в которой уже имеется другой элемент или его часть. Кроме того, нежелательно, чтобы элемент управления «залезал» на клетку, в которой имеется данное или формула.

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

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

Настройка выполняется на вкладке Элемент управления команды Формат объекта из контекстного меню вставленного элемента. Для списка и поля со списком кроме связанной ячейки следует задать адрес блока клеток, в которых находятся данные, которые должны в них отображаться. Для флажка и кнопок переключателя следует задать исходное состояние (включено, выключено). Кроме указанных параметров для вставленных элементов можно задать вид изображения (плоский или объемный), используя флажок Объемное затенение на вкладке Элемент управления.

Принцип управления обработкой данных с помощью элементов управления состоит в следующем: пользователь воздействует на элемент управления мышью, выбирая нужную позицию списка или включая нужную кнопку переключателя или изменяя состояние флажка, элемент управления при этом вставляет свое текущее значение в связанную ячейку, формула в какой-либо клетке, выполняющая управляемую обработку данных, обращается к связанной ячейке с помощью специальных функций и в зависимости от имеющегося там значения выполняет нужные действия. Имеется несколько таких специальных функций, используемых для считывания значения связанной ячейки. Так для работы с элементами управления Список и Поле со списком обычно используют Функцию Индекс из категории функций Ссылки и массивы, для работы с переключателями обычно используют функцию Выбор из той же категории и для работы с флажками используют функцию Если из категории логических функций.

Функция Индекс выполняет выбор значения из таблицы по заданному номеру строки и столбца. Она имеет три параметра: первый задает диапазон клеток рабочего листа, в которых располагаются значения таблицы, второй - задает номер строки, а третий – номер столбца. При использовании функции для работы с элементами управления в качестве второго и третьего параметров подставляются адреса связанных ячеек элементов. Эта функция может быть компонентом какой-либо формулы в клетке, участвуя в обработке данных, или может самостоятельно вставлять в клетку выбираемое значение.

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

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

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

Sub Кнопка1_щелкнуть ()

End Sub

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

2. Автоматизация анализа в Excel с помощью сценариев

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

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

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

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

3. Повышение уровня автоматизация обработки данных в Excel с помощью пользовательских функций

Наиболее эффективным средством повышения уровня автоматизации табличных расчетов в задачах на основе Excel является использование программных компонентов на алгоритмическом языке VBA (Visual Basic for Application), в частности - пользовательских функций. Язык VBA является объектно-ориентированным языком программирования, адаптированным к процедурам обработки данных и подготовки документов в программах пакета Microsoft Office. Варианты этого языка встроены в каждую программу пакета Microsoft Office и отличаются только используемыми объектами. Язык VBA табличного процессора Excel манипулирует объектами табличного документа, язык VBA текстового процессора Word – объектами текстового документа и т.д. Алфавит, синтаксис, операторы и структура программных модулей VBA одинаковы для всех программ пакета Microsoft Office.

Программной единицей VBA является процедура (Procedure). Используются процедуры двух видов функции (Function) и подпрограммы (Sub), отличающиеся только тем, что Function возвращает через свое имя, какое-либо данное, вычисленное в ней, а Sub это не делает. Соответственно Function может использоваться только в выражениях, например, расчетных формулах, куда она подставляет свое значение. Пользовательская функция Excel это процедура типа Function. Процедуры типа Function называют пользовательскими и они после создания могут вставляться в формулы в клетках рабочих листов Excel с помощью мастера вставки функций или явным вводом с клавиатуры.

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

Public Function ТранспТариф ()

End Function

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

ТранспТариф = 10, или ТранспТариф = ИсхТариф * 0.8

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

Public Function ТранспТариф (ИсхТариф, ОбщМасс)

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

Byte, Integer, Long – соответственно, однобайтовое, двухбайтовое и четырех байтовое целое число,

Single, Double – вещественное (т.е. в общем случае дробное) число обычной и удвоенной точности,

String – строка символов,

Boolean – логическое данное,

Data – дата,

Variant – универсальный тип, который может представлять любые другие типы (он требует больше места в памяти).

Тип задается с помощью слова AS, например:

Public Function ТранспТариф (ИсхТариф As Single, ОбщМасс As Single)

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

Public Function ТранспТариф (ИсхТариф As Single, ОбщМасс As Single) As Single

Процедуры записываются в модулях. В Excel имеются модули рабочих листов и рабочей книги и стандартные модули, они в совокупности составляют проект VBA. Модули рабочих листов и рабочей книги включаются в проект по умолчанию, а стандартные модули должны создаваться явно с помощью команды Insert / Module среды редактора VBA. Если процедура находится в модуле рабочего листа, то она может использоваться только в формулах в этом листе. Пользовательские функции должны располагаться в стандартном модуле. Количество стандартных модулей определяется объемом процедур, из которых состоит программа VBA. Для простых задач обычно бывает достаточно одного модуля.

Для создания пользовательской функции Excel необходимо:

Ø открыть среду редактора VBA командой Сервис / Макрос / Редактор Visual Basic,

Ø создать стандартный модуль (если его еще нет) командой Insert / Module,

Ø вставить в стандартный модуль заготовку новой функции командой Insert / Procedure,

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

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

Ø ввести между заголовком и окончанием нужные строки с операторами VBA.

Созданная пользовательская функция доступна в окне мастера вставки функций, в группе «Определенные пользователем». Она вставляется в формулу в таблице так же, как и любая встроенная функция Excel. При вставке пользовательской функции «вручную» следует учесть, что значения ее параметров разделяются знаками -;.

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

имя переменной = выражение

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

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

Dim strVar As String, rate As Single, vCoeff As Integer

Если переменных много, их описание следует распределить на нескольких операторов Dim.

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

If условие Then

…

End If

где условие - это какое-либо логическое выражение, в простейшем случае логическое отношение >, <, =>, <=, <>. Внутри этого оператора записываются операторы, которые должны выполняться в случае истинности условия, например:

If общМасс <= 1000 Then

транспТариф = ИсхТариф

End If

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

If условие Then

строки с операторами, которые будут выполняться при истинности условия

Else

строки с операторами, которые будут выполняться при ложности условия

End If

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

If условие1 Then

строки с операторами, которые будут выполняться при истинности условия1

ElseIf условие2 Then

строки с операторами, которые будут выполняться при истинности условия2

ElseIf условие3 Then

строки с операторами, которые будут выполняться при истинности условия3

…

…

Else

строки с операторами, которые будут выполняться при ложности всех условий

End If

Альтернативой этому оператору является встроенная функция языка VBA – Switch, описание которой имеется в справочной системе редактора VBA.

Дата добавления: 2015-11-04