Лаб.практикум по инф-ке_МУ. С. Л. Миньков лабораторный практикум по информатике
 Скачать 4.64 Mb.
|
|
Раздел 4. Создание макросов с помощью макрорекодера Цель раздела:используя сервисное средство MacroRecoder среды программирования Visual Basic for Application (VBA) в Excel, предназначенное для автоматического создания макро- сов, записать простой макрос и обеспечить его вызов с помощью объекта управления Кнопка. Microsoft Excel — мощный инструмент, используемый для управления данными, их анализа и представления. Но иногда, не- смотря на богатый набор возможностей стандартного интерфейса пользователя, может понадобиться найти несложный способ вы- полнять повседневные повторяющиеся задачи или какие-то зада- чи, которые не удается решить с помощью интерфейса пользова- теля. Для этих целей в приложения MS Office интегрирован Visual Basic для приложений (VBA) — язык и среда программи- рования, позволяющие расширять возможности этих приложе- ний. VBA работает, выполняя макросы (пошаговые процедуры), написанные на языке Visual Basic. Из всех приложений Office чаще всего используемой платформой для применения макросов является именно Excel Для работы с VBA необходимо вывести на ленту вкладку Разработчик (по умолчанию она отсутствует), выполнив следу- ющие действия (в Excel 2010): Файл | Параметры | Настройка ленты | Настройка ленты | Основные вкладки | флажок Раз- работчик | ОК. На ленте Excel появится вкладка Разработчик (рис. 4.1). Рис. 4.1 — Вкладка Разработчик Кнопка Безопасность макросов, находящаяся в группе Код, открывает окно, в котором можно определить, какие макросы вы- полняются и при каких условиях. Рекомендуется установить 70 опцию Отключить все макросы с уведомлением. Тогда при от- крытии книги, содержащей макрос, между лентой и листом появ- ляется строка Предупреждение системы безопасности: запуск макросов отключен. Чтобы включить макрос, нужно нажать кнопку Включить содержимое. Также в качестве мер безопасно- сти не сохраняйте макрос в формате файлов Excel, используемом по умолчанию (xlsx-файлы); вместо этого макрос должен быть сохранен в файле со специальным расширением xlsm-файл. Кнопка Запись макроса включает макрорекодер (автомати- ческую запись макроса). Кстати, на вкладке Вид имеется кнопка Макросы, также содержащая Запись макроса (рис. 4.2), т. е. макрорекодер можно запускать и без установки вкладки Разработчик. Рис. 4.2 — Вкладка Вид | Макросы При выборе этой команды открывается окно (рис. 4.3), в ко- тором вводится имя будущего макроса, сочетание клавиш, с по- мощью которых его можно будет запускать (не обязательно), функциональное назначение макроса. Рис. 4.3 — Запуск Макрорекодера 71 После нажатия на ОК все действия пользователя на листах Excel рабочего файла («книги») будут записываться транслято- ром Макрорекодер на языке Visual Basic — от момента запуска до окончания записи макроса. Автоматическая запись макроса позволяет, даже не зная са- мого языка Visual Basic, создавать на нем простейшие макросы для автоматизации рутинных действий по обработке данных и по выполнению группы команд. Остановка записи происходит при вызове команды Остано- вить запись (рис. 4.4). Рис. 4.4 — Остановка Макрорекодера Чтобы посмотреть на код макроса, нужно нажать на кнопку Макросы (рис. 4.4). Появится диалоговое окно Макрос (рис. 4.5). Отметим, что этот же результат достигается нажатием сочетания клавиш Alt+F8. Рис. 4.5 — Диалоговое окно «Макрос» 72 Правое меню обеспечивает запуск выбранного макроса на выполнение (Выполнить), переход к коду макроса (Выполнить или Изменить — при этом открывается диалоговое окно Microsoft Visual Basic for Application (рис. 4.6)), удаление макроса (Удалить), вызов параметров макроса (то же окно, что и на рис. 4.3). Можно упростить запуск макроса, создав на листе кнопку и назначив ей макрос. В Excel существует несколько вариантов кнопок. 1) кнопка-автофигура; 2) кнопка-картинка; 3) кнопка как элемент управления формы; 4) кнопка как элемент ActiveX 1 Рис. 4.6 — Диалоговое окно Microsoft Visual Basic for Application В первых двух вариантах на лист вставляется фигура или рисунок (Вставка | группа Иллюстрации). Затем вызывается контекстно-зависимое меню, выбирается Назначить макрос. 1 ActiveX (ранее OLE — Object Linking and Embedding) — техноло- гия Microsoft, позволяющая встраивать управляющие элементы в прило- жения. 73 В появившемся окне Назначить макрос объекту выбирается нужный макрос. В третьем варианте вызывается объект Элементы управле- ния формы (Разработчик | Вставить | группа Элементы управления формы) (рис. 4.7). На появившейся панели щелчком мыши выбираем форму Кнопка. При этом указатель мыши пре- вращается в тонкий крестик. Щелкаем им по листу. На нем появ- ляется кнопка с именем Кнопка1 и одновременно открывается окно Назначить макрос объекту, в котором выбирается имя макроса. Рис. 4.7 — Элементы управления формы и элементы ActiveX Для четвертого варианта требуется знание некоторых кон- струкций VBA и об этом будет рассказано чуть позже. Можно изменить формат кнопки (шрифт надписи, размер и т. п.). Для этого следует вызвать контекстно-зависимое меню и выполнить необходимые операции. Задание 4.1. Штатное расписание Составить штатное расписание небольшой частной больни- цы, то есть определить, сколько сотрудников, с каким окладом и на какие должности необходимо принять на работу. Общий ме- сячный фонд зарплаты (ФЗП) составляет $20000. Предположим, что для нормальной работы больницы нуж- но: 1 заведующий больницей, 1 главный врач, 3 заведующих от- делениями, 10 12 врачей, 8 10 медсестер, 5 7 санитарок, 1 заве- дующий аптекой, 1 заведующий хозяйством. 74 Предлагается следующая модель решения задачи. За основу берется оклад санитарки. Размер оклада остальных сотрудников определяется по формуле Оклад = А×(Оклад санитарки) + В, где А — должностной коэффициент оклада; В — величина надбавки, $. Значения А и В назначаются исходя из следующих сообра- жений: – медсестра должна получать в 1,5 раза больше санитарки; – врач — в 3 раза больше санитарки; – заведующий отделением — на $300 больше, чем врач; – заведующий аптекой — в 2 раза больше санитарки; – заведующий хозяйством — на $180 больше медсестры; – главный врач — в 4 раза больше санитарки; – заведующий больницей — на $200 больше главного врача. 1. На листе Excel оформить таблицу, используя следующие столбцы: Должность, Количество сотрудников, Должностной коэффициент оклада, Надбавка, Оклад, Итого. При решении задачи используйте сервисную функцию Excel Подбор параметра (см. задание 2.1). В поле Установить в ячейке ввести адрес ячейки, где вычисляется общая месячная зарплата всех сотрудников больницы. В поле Значение ввести сумму ФЗП. В поле Изменяя значение ячейки ввести адрес ячейки, где находится оклад санитарки. После нажатия ОК про- изойдет автоматический подбор значения оклада санитарки та- ким образом, чтобы ФЗП составил $20000. 2. Рассчитать оклады для нескольких вариантов штата, из- меняя количество штатных единиц в соответствии с заданными условиями. 3. Включить запись макроса и выполнить описанные выше действия по расчету штатного расписания «под запись». Посмот- реть код созданного макроса. 4. Изменить макрос таким образом, чтобы можно было в не- которой ячейке задавать произвольное значение ФЗП и в соответ- ствии с этим значением рассчитывать оклады сотрудников. Про- верить его работу. 75 5. Создать на листе кнопку вызова макроса. Теперь описан- ная выше процедура расчета штатного расписания будет выпол- няться простым нажатием кнопки. Примечание. Иногда требуется скрыть (не уничтожить!) не- сколько столбцов или строк в таблице, для того, например, чтобы при печати они не отображались или с целью спрятать конфи- денциальную информацию. Для этого щелкаем мышью по имени столбца (строки) и, удерживая нажатой левую кнопку, проводим по тем столбцам (строкам), которые необходимо скрыть. Затем вызываем кон- текстно-зависимое меню и выбираем Скрыть. Чтобы снова их показать, необходимо выделить столбцы (строки), между кото- рыми находится скрытое, и в контекстно-зависимом меню вы- брать Показать. Чтобы наложить запрет на изменение данных в созданном вами листе Excel, необходимо выполнить последовательность действий Рецензирование | группа Изменения | Защитить лист. В появившемся окне следует отметить, что именно защищается: содержимое, объекты, сценарии. Можно указать пароль. Снима- ется защита по команде Снять защиту листа (там же — в группе Изменения) и паролю. Задание 4.2. Шаблоны таблицы и диаграммы 1. Записать в виде макроса процесс создания шаблона таб- лицы и построения диаграммы по табличным данным. Варианты выбрать из лабораторной работы 1 (задание 1.1). При вызове макроса на активном рабочем листе должен автоматически со- здаваться шаблон таблицы. При занесении исходных данных рас- чет результатных данных (которые помечены знаком вопроса) и построение диаграммы будут происходить автоматически. При наборе формул предусмотреть возможность возникно- вения аварийных ситуаций (например, деление на нуль) и обойти их, используя функцию ЕСЛИ. 76 2. Открыть код записанного макроса и добавить коммента- рии, описав, каким вашим действиям соответствуют те или иные строчки программного кода. Комментарии начинаются с кавычки (рис. 4.6) и не влияют на выполнение кода. 77 Раздел 5. Среда программирования VBA в Excel: процедуры и функции Цель раздела:освоить программирование в среде Visual Basic for Application пользовательских процедур и работу с мас- сивами. Итак, после выполнения предыдущей работы на ленте оста- лась вкладка Разработчик. Нажатие на кнопку Visual Basic (или сочетание клавиш Alt+F11) приводит к появлению окна проектов редактора Visual Basic for Application (рис. 5.1). Рассмотрим на примерах некоторые часто используемые возможности языка программирования Visual Basic (в связке со средой Excel) и визу- альной среды программирования VBA. Рис. 5.1 — Окно редактора VBA 5.1 Структура программ на VBA Программы на VBA хранятся в проектах (VBAProject). Проект содержит модули различных типов, а модули включают различные процедуры. 78 Имеется три типа модулей: 1) стандартные модули (Module) — это модули, в которых можно описать доступные во всем проекте процедуры; 2) модули класса (Class Module) содержат описание объек- та, который является членом класса. Процедуры, написанные в модуле класса, используются только в этом модуле; 3) модули форм и отчетов (UserForm), которые связаны с конкретной формой или отчетом. Модули форм и отчетов часто содержит процедуры обработки событий, которые срабатывают в ответ на событие в форме или отчете. Процедуры обработки со- бытий используются для управления поведением форм и отчетов и их реакцией на действия пользователя типа щелчка мыши на кнопке. Модули содержат описания и процедуры — наборы описа- ний и операторов, сгруппированных для выполнения. Существу- ет три типа процедур: 1) процедура Sub (подпрограмма) — набор команд, с помо- щью которого можно решить определенную задачу. При ее за- пуске выполняются команды процедуры, а затем управление пе- редается в приложение пакета MS Office или процедуру, которая вызвала данную процедуру. 2) процедура Function (функция) также представляет собой набор команд, который решает определенную задачу. Различие заключается в том, что такие процедуры обязательно возвращают значение, тип которого можно описать при создании функции. 3) процедура Property используется для ссылки на свойство объекта. Данный тип процедур применяется для установки или получения значения пользовательских свойств форм и модулей. Начало модуля называется общей областью, в которой рас- полагаются общие описания, например, типа данных, используе- мого по умолчанию (DefТип), инструкция Option Explicit, тре- бующая явного описания всех используемых в модуле перемен- ных, а также описания общих (глобальных) для всех модулей и для данного модуля переменных. Процесс создания проекта программы можно разделить на следующие этапы: – постановка задачи (подготовительный этап) — составле- ние точного и полного словесного описания работы программы, 79 определение исходных и выходных данных, будет ли использо- ваться экранная форма и как она будет выглядеть, т. е. какие объ- екты должны быть на ней и какими должны быть их свойства (объекты могут располагаться непосредственно на рабочем листе); – разработка пользовательского интерфейса — создание экранной формы в среде VBА со всеми находящимися на этой форме объектами и их свойствами; – программирование — определение событий, происходя- щих в процессе работы программы, составление процедур этих событий и написание программных кодов этих процедур; – отладка программы — устранение логических ошибок в процедурах и достижение правильности работы программы; – сохранение проекта. Рассмотрим основные элементы языка программирования VВА. 5.2 Скалярные типы данных Все данные, которыми оперирует VBA, относятся к опреде- ленному типу. Тип данных определяет: – область возможных значений переменной; – структуру организации данных; – операции, определенные над данными этого типа. Типы данных подразделяются на простые (скалярные) и сложные (структурированные). У простых типов данных воз- можные значения данных едины и неделимы. Сложные же типы имеют структуру, в которую входят различные простые типы данных. Скалярные типы данных представлены в таблице 5.1. Таблица 5.1 — Скалярные типы VBA Имя типа Название типа Возможные значения Boolean Логический True, False Byte Байтовый 0…255 Integer Целое –32768…+32767 Long Длинное целое –2147483648…+2147483647 Single Число с плавающей точкой –3,4Е38…–1,4Е–45 для отрицательных значе- ний. 1,4Е–45…3,4Е38 для положительных значений 80 Имя типа Название типа Возможные значения Double Число с плавающей точкой двойной точно- сти –1,7Е308…–4,9Е–324 для отрицательных зна- чений. 4,9Е–324…1,7Е308 для положительных значений Currency Денежный Десятичные числа с фиксированной позицией запятой. Возможны 15 цифр до запятой и 4 после String Строковый Есть два вида строк: строки фиксированной длины (до 216 символов) и строки переменной длины (до 231 символов). Данные записыва- ются в кавычках Date Дата Даты изменяются в диапазоне от 1.01.100 г. до 31.12.9999г. Object Объект Ссылка на объект (указатель) Variant Вариант Универсальный тип, значением которого мо- гут быть данные любого из перечисленных выше типов, объекты, значения NULL и зна- чения ошибок ERROR Переменные в программе можно описывать или не описы- вать. В последнем случае переменной будет присвоен тип Variant. Явно описывать переменную можно как в начале блока, так и в любом месте, где возникла необходимость использовать новую переменную. Как правило, переменные описывают явно и в начале блока. Для запрета использования переменных, кото- рые не были описаны явно, в начало программы необходимо вставить оператор Option Explicit. Описание простых переменных имеет следующий синтак- сис: Dim ИМЯ_ПЕРЕМЕННОЙ As ИМЯ_ТИПА Одним оператором Dim можно описать произвольное число переменных, но конструкция Аs должна быть указана для каждой из них, иначе переменным без Аs будет присвоен тип Variant. Например, Dim X As Byte, Z As Integer, С, Cлово As String Здесь переменная Х — это переменная байтового типа, пе- ременная Z — целого типа, переменная С — типа вариант (по умолчанию), переменная Слово — строкового типа. Данные, не изменяющиеся внутри программы, можно счи- тать константами. Их описание: Окончание табл. 5.1 81 Const ИМЯ_КОНСТАНТЫ As ИМЯ_ТИПА=ПОСТОЯННОЕ ВЫРАЖЕНИЕ Например. Const Pi As Double = 3.141593 5.3 Структурированные типы данных Для хранения векторов, матриц и т. д. используют массивы. Массив — это структурированный тип данных, который представляет собой последовательность ячеек памяти, имеющих общее имя и хранящих данные одного типа. Каждый элемент массива определяется индексом (номером). Количество элемен- тов в массиве называется размерностью массива. Массив описы- вается следующей конструкцией: Dim ИМЯ_МАССИВА (СПИСОК_РАЗМЕРНОСТЕЙ) As ИМЯ_ТИПА В списке размерностей массива задаются нижняя и верхняя границы изменения индексов. Каждый элемент списка отделяется запятой. Например. Dim X(1 TO 5) As Integer, Y(1 To 10, 1 To 20) As Single Здесь Х — одномерный массив, состоящий из 5 элементов це- лого типа, Y — двумерный массив, у которого 10 строк и 20 столб- цов с элементами числового типа одинарной точности. 5.4 Выражения Выражения устанавливают порядок выполнения действий над элементами данных. Выражения состоят из операндов и зна- ков операций. Операндами являются константы, переменные, указатели функций, выражения, взятые в скобки. Операции бывают арифметические, отношения и логические. Арифметические операции: ^ возведение в степень, * умножение, / деление, \ деление нацело, 82 mod остаток от деления, + плюс, – минус. Операции отношения: < меньше, > больше, <= меньше или равно, >= больше или равно, = равно, <> не равно. Логические операции: Not логическое отрицание, And логическое «И», Or логическое «ИЛИ». Xor логическое исключение, Imp логическая импликация, Eqv логическая эквивалентность. Результатом логической операции может быть одно из двух значений: True («истина») или False («ложь»). Выражения бывают арифметические, отношения и логические. Арифметические выражения записываются с помощью опе- рандов числовых типов, арифметических операций, стандартных математических функций, а результатом является числовое зна- чение. Выражения отношения определяют истинность или ложность результата при сравнении двух операндов. Сравнивать можно дан- ные любого одинакового типа. Результат операции отношения только логический: True — «истина» или False — «ложь». Результатом логического выражения является логическое значение True или False. Простейшими видами логических выра- жений являются: логическая константа, логическая переменная, логическая функция, выражение отношения. Логические опера- ции выполняются только над операндами логического типа. Например, выражение 1≤Х ≤5 в VВА будет выглядеть так: X>=1 And X<=5 Его значение при Х=3.1 будет True. 83 5.5 Операторы Оператор — это наименьшая способная выполняться еди- ница кода VBA. Основные операторы программирования — это операторы присваивания, условного перехода и цикла. При записи операторов необходимо следовать следующим правилам: каждый новый оператор записывается с новой строки; для записи нескольких операторов на одной строке их разделяют между собой двоеточием ( : ); если оператор не помещается в одной строке, то необхо- димо поставить в конце строки пробел и знак подчеркивания ( _ ), а затем продолжить его на следующей строке. 5.5.1 Оператор присваивания Оператор присваивания используется, если какой-то пе- ременной нужно присвоить новое значение. Он имеет следующий синтаксис: ИМЯ_ПЕРЕМЕННОЙ = ВЫРАЖЕНИЕ Сначала вычисляется выражение в правой части (см. п.5.4), а затем результат присваивается переменной, стоящей в левой ча- сти. Х=3.1 Q = X>=1 And X<=5 5.5.2 Условные операторы Оператор условного перехода (условный оператор) имеет несколько форм записи. 1. Оператор If УСЛОВИЕ Then БЛОК ОПЕРАТОРОВ1 Else БЛОК ОПЕРАТОРОВ2 End If 84 УСЛОВИЕ — это отношение или выражение логического типа. Их результатами является логическое значение True или False. БЛОК ОПЕРАТОРОВ — один или несколько операторов. Первый блок выполняется, если УСЛОВИЕ истинное, второй — если ложное. Если блоки содержат по одному оператору, то условный оператор записывается на одной строке и имеет вид: If УСЛОВИЕ Then ОПЕРАТОР1 Else ОПЕРАТОР2 В условном операторе могут проверяться и несколько усло- вий: If УСЛОВИЕ1 Then БЛОК ОПЕРАТОРОВ1 ElseIf УСЛОВИЕ2 Then БЛОК ОПЕРАТОРОВ2 Else …. End If Для обхода по условию используется укороченный услов- ный оператор: If УСЛОВИЕ Then БЛОК ОПЕРАТОРОВ End If 2. Оператор выбора Select Case. Его удобно использовать, когда в зависимости от значения некоторого выражения, имеющего конечное множество допусти- мых значений, необходимо выполнить разные действия. Select Case ПРОВЕРЯЕМОЕ ВЫРАЖЕНИЕ Case ЗНАЧЕНИЯ 1 ОПЕРАТОРЫ 1 Case ЗНАЧЕНИЯ 2 ОПЕРАТОРЫ 2 … Case ЗНАЧЕНИЯ N ОПЕРАТОРЫ N … Case Else ОПЕРАТОРЫ ПРИ НЕСОВПАДЕНИИ 85 End Select ПРОВЕРЯЕМОЕ ВЫРАЖЕНИЕ имеет любой скалярный тип, кроме вещественного. ЗНАЧЕНИЯ состоят из произвольного количества значений или диапазонов (n to m), отделенных друг от друга запятыми. Тип ЗНАЧЕНИЙ должен совпадать с типом ПРОВЕРЯЕМОГО ВЫРАЖЕНИЯ. Сначала вычисляется ПРОВЕРЯЕМОЕ ВЫРАЖЕНИЕ. Если его значение совпадает с одним из значений ЗНАЧЕНИЯ I, то выполняются ОПЕРАТОРЫ_I, и управление передается опера- тору, стоящему после End Select. Если его значение не совпадает ни с одним из значений ЗНАЧЕНИЯ_I, то выполнятся ОПЕРАТОРЫ ПРИ НЕСОВПАДЕНИИ и управление передается оператору, стоящему после End Select. 5.5.3 Операторы цикла Операторы цикла тоже имеют несколько форм записи. 1. Оператор For…Next. Используется для выполнения набора инструкций указанное число раз. Циклы For используют в качестве счетчика переменную, значение которой увеличивает- ся или уменьшается при каждом выполнении цикла. For СЧЕТЧИК = НАЧАЛО To КОНЕЦ [Step ШАГ] БЛОК_ОПЕРАТОРОВ [Exit For] БЛОК_ОПЕРАТОРОВ Next [СЧЕТЧИК] Повторяет выполнение блока операторов, пока переменная СЧЕТЧИК изменяет свое значение от начального до конечного с указанным шагом. Если ШАГ не указан, то по умолчанию он по- лагается равным 1. Немедленный выход из цикла осуществляется оператором Exit For. Квадратные скобки обозначают необяза- тельность конструкции в синтаксисе оператора. 2. Оператор While…Wend While УСЛОВИЕ БЛОК_ОПЕРАТОРОВ Wend 86 Выполняет блок операторов, пока заданное условие имеет значение True (истина). Условие проверяется перед выполнением операторов, расположенных внутри цикла. 3. Оператор Do While…Loop Do [While УСЛОВИЕ] БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop Выполняет блок операторов, пока заданное условие имеет значение True (истина). Условие проверяется перед выполнением операторов, расположенных внутри цикла. Если условие отсут- ствует, то получаем бесконечный цикл. Оператор Exit Do позво- ляет немедленно выйти из цикла. 4. Оператор Do…Loop While Do БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop [While УСЛОВИЕ] Выполняет блок операторов, пока заданное условие имеет значение True (истина). Условие проверяется после выполнения операторов, расположенных внутри цикла. 5. Оператор Do Until…Loop Do [Until УСЛОВИЕ] БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop Выполняет блок операторов, пока заданное условие имеет значение False (ложь). Условие проверяется перед выполнением операторов, расположенных внутри цикла. 6. Оператор Do…Loop Until Do БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop [Until УСЛОВИЕ] 87 Выполняет блок операторов, пока заданное условие имеет значение False (ложь). Условие проверяется после выполнения операторов, расположенных внутри цикла. 7. Оператор For Each…Next For Each ЭЛЕМЕНТ In ГРУППА БЛОК_ОПЕРАТОРОВ [Exit For] БЛОК_ОПЕРАТОРОВ Next [ЭЛЕМЕНТ] Повторяет выполнение блока операторов для элементов группы. Группой может быть как массив, так и набор объектов (ячейки, диаграммы, формы и т. д.). VBA автоматически задает значение переменной ЭЛЕМЕНТ во время каждой итерации цикла. Пример 5.1. Составить таблицу начисления премиальных по итогам работы сети 4-х магазинов за три месяца по следую- щему правилу: – если продукции продано не меньше чем на 60000 рублей, то премиальные составляют 2 % от суммарной выручки магази- на; – за первое место дополнительно начисляется 4 % преми- альных, за второе — 2 %, за третье — 1 % от суммарной выруч- ки магазина. Сформируем таблицу в Excel и заполним значениями, например так, как показано на рис. 5.2. Рис. 5.2 — Исходная таблица примера 1 Для удобства будущей работы в VBA можно сменить стиль ссылок на ячейки: буквы столбцов заменить на цифры (см. рис. 5.5). Для этого исполните команду: Файл | Параметры | Формулы | Работа с формулами | Стиль ссылок R1С1 (поста- вить галочку)). Теперь адрес ячейки D5 будет выглядеть как 88 R5C4, а в формулах адрес используемой ячейки, будет задаваться сдвигом относительно той ячейки, где вводится формула. Удоб- ство этого вы оцените, когда будете работать с объектом Cells. Создадим в редакторе VBA процедуру обработки события Click (щелчок мыши по кнопке), которая проводит вычисления по заданному алгоритму и заполняет пустые ячейки таблицы. Сначала создадим кнопку (в группе Элементы управления выполнить Вставить | Кнопка (Элементы ActiveX)). На листе Excel рисуем мышкой кнопку с именем по умолчанию CommandButton1. Изменить имя можно несколькими способами. Оба способа работают при нажатой кнопке Режим конструктора в Элемен- тах управления. Способ 1. Вызвать на кнопке контекстно-зависимое меню и выбрать опцию Свойства (рис. 5.3). В поле Caption заменить имя CommandButton1 на Заполнение таблицы. Рис. 5.3 — Окно Свойства кнопки Способ 2. Вызвать контекстно-зависимое меню и выбрать опцию Объект CommandButton | Edit. В поле кнопки появляет- ся маркер — можно изменить текст. Двойной щелчок по созданной кнопке, или выбор опции Исходный текст в контекстно-зависимом меню, или щелчок по кнопке Visual Basic осуществляет переход в редактор VBA 89 (рис. 5.4), в правом окне которого уже появился шаблон процеду- ры обработки события Click. Рис. 5.4 — Интерфейс редактора VBA Напишем следующий код для этой процедуры с использова- нием циклических структур. Текст, начинающийся с кавычки, — это примечание. Private Sub CommandButton1_Click() 'Цикл суммирования выручки за 3 месяца For i = 2 To 5 Cells(i, 5).Value = Cells(i, 2).Value + Cells(i, 3).Value + Cells(i, 4).Value Next 'Создание вспомогательного массива box 'и заполнение его значениями выручки Dim box(4) As Double box(1) = Cells(2, 5) box(2) = Cells(3, 5) box(3) = Cells(4, 5) box(4) = Cells(5, 5) 'Сортировка выручки за 3 месяца по убыванию методом «пузырька». 'При этом в box(1) окажется максимальное значение выручки For i = 1 To 4 For j = i + 1 To 4 If box(i) < box(j) Then q = box(i) box(i) = box(j) box(j) = q End If 90 Next Next 'Начисление процентов в зависимости от занятого места For i = 2 To 5 Cells(i, 7) = 0 If Cells(i, 5) = box(1) Then Cells(i, 7) = 4 If Cells(i, 5) = box(2) Then Cells(i, 7) = 2 If Cells(i, 5) = box(3) Then Cells(i, 7) = 1 Next 'Начисление процентов, если выручка за 3 месяца больше плановой выручки For i = 2 To 5 If Cells(i, 5).Value >= Cells(1, 11).Value Then Cells(i, 6) = 2 Else: Cells(i, 6) = 0 End If Next 'Суммирование процентов For i = 2 To 5 Cells(i, 8).Value = Cells(i, 6).Value + Cells(i, 7).Value Next 'Расчет итоговой премии For i = 2 To 5 Cells(i, 9).Value = Cells(i, 5).Value * Cells(i, 8).Value / 100 Next End Sub Закроем редактор и нажмем на кнопку «Заполнение табли- цы», отключив предварительно Режим конструктора. Получим заполненную таблицу (рис. 5.5). Рис. 5.5 — Результирующая таблица: значение премии в зависимости от выручки магазина 91 В результате в столбце Е окажется сумма выручек за 3 ме- сяца, в столбце F — процент, назначенный за перевыполнение плана, в столбце G — процент, назначенный в зависимости от занятого места, в столбце Н — итоговый процент, в столбце I — величина премии. Пример 5.2. Составить таблицу начисления премии по итогам работы сети 4-х магазинов за три месяца по следую- щему правилу: – если продукции продано меньше чем на 20 тыс. руб., то премия не начисляется; – если продукции продано на сумму от 20 до 40 тыс. руб., премия составляет 3 % от выручки; – если продукции продано на сумму от 40 до 80 тыс. руб., премия составляет 4,5 % от выручки; – если продукции продано больше чем на 80 тыс. руб., то премия составляет 6,5 %. Аналогично примеру 1 составим исходную таблицу, введя значение выручки каждого магазина за три месяца (рис. 5.6). Рис. 5.6 — Исходная таблица примера 2 Создадим кнопку и назначим ей процедуру обработки собы- тия Click (по щелчку), позволяющую вычислять и заносить в со- ответствующие ячейки выручку и премию. Для расчета премии используем функцию Премия, код которой приведен ниже про- цедуры. 92 Private Sub CommandButton1_Click() 'Цикл суммирования выручки за 3 месяца For i = 11 To 14 Cells(i, 5).Value = Cells(i, 2).Value + Cells(i, 3).Value_ + Cells(i, 4).Value Next 'Цикл начисления премии в зависимости от выручки за 3 месяца For i = 11 To 14 Cells(i, 6).Value = Премия(Cells(i, 5).Value) Next End Sub 'Функция начисления премии в зависимости от объема продаж Function Премия(Продажа As Double) As Double Select Case Продажа Case 0 To 20000 Премия = 0 Case 20001 To 40000 Премия = 0.03 * Продажа Case 40001 To 80000 Премия = 0.045 * Продажа Case Is>80001 Премия = 0.065 * Продажа End Select End Function Закроем редактор и нажмем на кнопку. В столбце Е появит- ся доход от продаж, в столбце F — премия, которая начисляется в зависимости от того, на какую сумму продан товар (рис. 5.7). Рис. 5.7 — Результирующая таблица 93 Пример 5.3. Вычислить значение 1 1 1 2 1 1 2 1 1 m n n i ij i i j m m i i i i a c S a a , где а — вектор из m компонентов, с — матрица размерности n×n, при a= [3;1;2;3], 2 2 4 2 4 6 2 5 3 ñ . Откроем лист Excel и введем исходные данные. В диапазон (A1:D1) введем элементы вектора а, в диапазон (A3:C5) — эле- менты матрицы с. Способ 1. Непосредственный ввод формулы в ячейку с использованием стандартных функций рабочего листа Excel. В ячейку Е3 введем формулу: =((2*СУММ(A1:D1)+СУММ(A3:C5))/((1+СУММ(A1:D1))*(1+ СУММКВ(A1:D1)))) и нажмем клавишу Enter. В результате в ячейке Е3 получим ис- комое значение S (рис. 5.8). Рис. 5.8 — Результирующая таблица примера 3 Способ 2. Программирование в VBA с использованием стандартных функций для работы с массивами. Для этого в редакторе VBA добавляем модуль с помощью команды Insert | Module (рис. 5.9). 94 Рис. 5.9 — Выбор команды Insert | Module Запишем в модуле код: Public Function FirstFunction(a, c As Variant) As Variant Dim s1, s2, s3 As Variant 'Сумма элементов вектора а s1 = WorksheetFunction.Sum(a) 'Сумма элементов матрицы с s2 = WorksheetFunction.Sum(c) 'Сумма квадратов элементов вектора а s3 = WorksheetFunction.SumSq(a) 'Расчет результата FirstFunction = (2 * s1 + s2) / ((1 + s1) * (1 + s3)) End Function Теперь в списке функций Excel в категории Определенные пользователем появилась функция FirstFunction. Ее вызов на лист Excel (Формулы | группа Библиотека функций | Вставить функцию или щелчок по соответствующей кнопке на строке формул) приводит к появлению диалогового окна Аргументы функции (рис. 5.10), где вводятся адреса массивов а и с. Рис. 5.10 — Диалоговое окно Аргументы функции 95 После нажатия кнопки ОК в той ячейке, из которой вызвали функцию (здесь Е4), получаем результат (рис. 5.8). Способ 3. Программирование в VBA с использованием циклических конструкций. Для этого в том же модуле запишем новую функцию, где расчет вычисления искомого значения S оформим без использова- ния стандартных функций, используя циклы (для разнообразия — трех разных типов). Public Function SecondFunction(a, c As Variant) As Double Dim s1, s2, s3 As Double, i, j, n, m As Integer 'Нахождение количества элементов вектора а m = a.Columns.Count 'Нахождение количества строк матрицы с (оно же – количество столбцов, 'если матрица квадратная) n = c.Rows.Count 'Найдем сумму элементов вектора а (цикл типа While… Wend) s1 = 0 i = 1 While i <= m s1 = s1 + a(i) i = i + 1 Wend 'Найдем сумму элементов матрицы с (цикл типа For… Next) s2 = 0 For i = 1 To n For j = 1 To n s2 = s2 + c(i, j) Next j Next i 'Найдем сумму квадратов элементов вектора а (цикл типа Do… Loop) s3 = 0 i = 1 Do Until i > m s3 = s3 + a(i) ^ 2 i = i + 1 Loop 'Расчет результата SecondFunction = (2 * s1 + s2) / ((1 + s1) * (1 + s3)) End Function 96 Теперь в списке функций Excel в категории Определенные пользователем появилась еще одна функция SecondFunction. Ее вызов в ячейке Е5 приводит к появлению третьего ответа (рис. 5.8). Все три решения совпали, что и следовало ожидать. Пример 5.4. Построить квадратную матрицу G размером n×n, элементы которой определяются по формуле: 2 sin ( ( )), ( , ) ( ) cos( ( )), i j C i G i j i j C i j C i , для С=[2;4;1;7], n=4. Сформируем на новом листе массив вектора C в ячейках А2:D2, в ячейку B4 занесем значение n (рис.5.11). Рис. 5.11 — Таблицы примера 4 Способ 1. Создание процедуры построения матрицы, за- пускаемой кнопкой. Создадим кнопку «Старт!», как это было сделано в преды- дущей работе. Затем щелкнем два раза по кнопке и в окне редак- тирования кода модуля запишем процедуру обработки щелчка по кнопке — процедуру построения матрицы. Private Sub CommandButton1_Click() 'Определяем переменную, в которой будет храниться размер матрицы Dim n As Integer 'Определяем массив С для исходных данных и массив g 'для результирующей квадратной матрицы 97 Dim C(), g() 'Задаем значение переменной n, выбирая его из ячейки В4 n = Cells(4, 2) 'Выделяем память для массивов С и g ReDim C(1 To n), g(1 To n, 1 To n) 'Вводим в массив С значений из ячеек A2:D2 For k = 1 To n C(k) = Cells(2, k) Next 'Проводим расчет элементов матрицы 'по заданной формуле, 'организуя двойной цикл (по строкам и столбцам) For i = 1 To n For j = 1 To n If i <= j Then g(i, j) = Sin(C(i)) ^ 2 Else g(i, j) = C(i – j) + Cos(C(i)) End If 'Ввод элементов полученной матрицы g в ячейки A7:D10 листа Excel Cells(i + 5, j) = g(i, j) Next j Next i End Sub Затем, нажав на кнопку «Старт!», в ячейках A7:D10 полу- чим искомую матрицу G (рис. 5.11). Задание 5.1. Исключить лишнее входное данное Измените код так, чтобы в примере, рассмотренном в спо- собе 1 примера 5.4, можно было обойтись без использования ячейки со значением n, т. е. убрать его из списка входных дан- ных. Способ 2. Создание пользовательской функции. По аналогии с примером 3 создадим функцию, аргументом которой является массив и которая после работы возвращает также массив. Function T(C As Variant) As Variant 'Определяем локальные переменные 98 Dim n, i, j As Integer 'Определяем массив R для исходных данных Dim R() As Variant 'Определяем число элементов вектора, расположенного в строке n = C.Columns.Count 'Выделяем память для вспомогательного двумерного массива R 'размером n×n ReDim R(1 To n, 1 To n) 'Вычисляем элементы матрицы For i = 1 To n For j = 1 To n If i <= j Then R(i, j) = Sin(C(i)) ^ 2 If i > j Then R(i, j) = C(i – j) + Cos(C(i)) Next Next 'Возвращаем элементы матрицы T = R End Function Теперь в списке пользовательских функций появилась функция Т. Вызов этой функции отличается от вызова функции, резуль- татом которой является одно число. Сначала надо выделить на листе Excel массив ячеек, куда будет занесен ответ. В нашем случае это массив ячеек A7:D10 размером 4×4. Затем после вызо- ва функции и занесения данных (массив ячеек A2:D2) следует выйти из диалогового окна функции, нажав сочетание клавиш Shift+Ctrl+Enter (а не ОК или Enter, как обычно). Задание 5.2. Процедуры и функции Пусть C — одномерный массив, состоящий из n элементов, а G — квадратная матрица n n . В задании (a) необходимо вы- числить квадратную матрицу G и вывести значения ее элементов на рабочий лист. В задании (б) нужно вычислить значение s тре- мя способами: 1) написать функцию, используя различные операторы цикла; 2) написать функцию, используя стандартные функции VBA; 3) ввести формулу в ячейку с помощью стандартных функ- ций рабочего листа Excel. 99 Вариант 1 а) построить матрицу ( 3 n и 3 4 1 C ) 2 2 ( ), ; , sin( ( ) ( )) ( ), ; C i i j G i j C i C i j C i i j б) вычислить значение 2 1 1 1 1 2 3 n m m i i ij i i j n i i x y b s x , где x, y — векторы из n компонентов, b — матрица размерности m m , причем n = 4, m = 2, 3 1 2 3 x , 1 7 2 3 y , 4 1 2 5 b Вариант 2 а) построить матрицу ( 4 n и 2 4 1 7 C ) 2 sin ( ( )), ; , ( ) cos( ( )), ; C i i j G i j C i j C i i j б) вычислить значение 1 1 1 2 1 1 2 1 1 m n n i ij i i j m m i i i i a c s a a , где a — вектор из m компонентов, c — матрица размерности n n , причем n = 3, m = 4 3 1 2 3 a , 2 2 4 2 4 6 2 5 3 c 100 Вариант 3 а) построить матрицу ( 5 n и 6 4 2 7 6 C ) 2 3 ( ) , 3; , ( ) ( ) , 3; C i i j i j G i j C i j C i i j б) вычислить значение 3 2 1 1 1 1 1 2 5 3 n n m m i i ij i i i j n i i x y b s y , где x, y — векторы из n компонентов, b — матрица размерности m m , причем n = 4, m = 2, 1 2 7 4 x , 1 7 2 3 y , 4 1 2 5 b Вариант 4 а) построить матрицу ( 4 n и 1 2 1 7 C ) 2 ( ) ( ), 2; , ( ) ( ), 2; C i C j i j G i j C i j C i i j б) вычислить значение 2 2 1 1 1 1 1 3 7 1 m n n n n i ij ij i i j i j s a c c , где a — вектор из m компонентов, c — матрица размерности n n , причем n = 3, m = 4, 3 1 2 3 a , 2 2 4 2 4 6 2 5 3 c 101 Вариант 5 а) построить матрицу ( 6 n и 1 3 1 7 2 2 C ) sin( ( ) ( )), 2; , ( ) ( ) , 2; C i C j i j G i j C i j C i i j б) вычислить значение 2 1 1 1 1 1 1 2 2 2 1 n n m m n n i i ij i i i i i i j i i s x y b x x y , где x, y — векторы из n компонентов, b — матрица размерности m m , причем n = 4, m = 2, 1 2 7 4 x , 1 7 2 3 y , 4 1 2 5 b Вариант 6 а) построить матрицу ( 7 n и 1 4 1 3 4 5 7 C ) 3 3 ( ) 2 ( ), 2; , ( ) ( ) , 2; C i C j i j G i j C i j C i i j б) вычислить значение 2 2 2 1 1 1 1 1 1 3 1 m n n n n m i ij ij i i i j i j i s a c c a , где a — вектор из m компонентов, c — матрица размерности n n , причем n = 3, m = 4, 3 3 1 3 a , 2 2 4 2 4 6 2 5 3 c Вариант 7 а) построить матрицу ( 4 n и 1 4 8 7 C ) 2 3 ( ) cos ( ( )), 1; , ( ) ( ) , 1; C i C j i j G i j C i j C i i j 102 б) вычислить значение 2 1 1 1 1 2 2 3 n n n n i i i i i i i i i s x x y x x , где x, y — векторы из n компонентов, причем n = 4, 1 2 7 4 x , 1 7 2 3 y Вариант 8 а) построить матрицу ( 5 n и 2 9 1 7 3 C ) sin( ( ) ( )), 1; , ( ) 3 ( ) 7 ( ), 1; C i C j i j G i j C i j C i C j i j б) вычислить значение 2 2 2 1 1 1 1 1 1 1 1 1 1 m m n n n n i i ij ij i i i j i j s a a c c , где a — вектор из m компонентов, c — матрица размерности n n , причем n = 2, m = 4, 1 4 1 3 a , 2 1 3 2 c Вариант 9 а) построить матрицу ( 6 n и 1 2 1 7 8 2 C ) 3 ( ) 2 ( ), 2; , sin( ( )) 3 ( ) 7 ( ) , 2; C i C j i j G i j C i j C i j C j i j б) вычислить значение 2 2 1 1 1 1 1 5 1 3 n n n n n i i i i i i i i i i i s x x y x y x , где x, y — векторы из n компонентов, причем n = 4, 7 5 7 4 x , 2 4 2 3 y 103 Вариант 10 а) построить матрицу ( n 7 и 5 1 1 7 1 2 1 C ) ( ) 5 ( ) , 1; , ( ) 4 sin( ( )) 7 ( ), 1; C i C j i j G i j C i j C i C j i j б) вычислить значение 2 2 2 1 1 1 1 1 1 1 1 1 4 m n n m n n i ij i ij i i j i i j s a c a c , где a — вектор из m компонентов, c — матрица размерности n n , причем n = 3, m = 4, 2 1 1 3 a , 5 2 4 1 4 2 7 3 5 c |