Порядок выполнения работы. Методические указания

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

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

 

Термином «макрос» обычно называют файл, хранящий последовательность действий, заданных пользователем системы. Каждый макрос должен иметь собственное имя.

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

Макрос представляет собой программу и может быть создан автоматически в специальном режиме работы программной системы (в том числе и Excel) или как результат программирования, в терминах языка системы. Если пользователь владеет языком задания макроса, то созданный любым способом макрос может быть подвергнут редактированию с целью изменения его возможностей или устранения ошибок. В пакете Microsoft Office таким языком является язык VBA.

 

При работе с Excel, как, впрочем, и с другими программами пакета Microsoft Office, для создания макроса легче всего использовать автоматический режим его создания, вызываемый из главного меню системы командами Сервис/Макрос.

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

 

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

 

Рисунок 1 Окно «Безопасность»

 

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

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

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

Необходимо иметь в виду существование двух возможных типов записи ссылок на ячейки в Excel– A1и R1C1. По умолчанию при программировании формул используется стиль A1, для которого адрес каждой ячейки представляет собой строку символов, содержащую имя столбца и номер строки. Использование этого стиля позволяют организовать относительную и абсолютную адресацию к ячейкам таблицы (за счет введения в строку символа $). Тем не менее при записи макросовExcel использует тип ссылки R1C1. В обозначении типа присутствуют первые буквы английских слов Row(строка) и Column (колонка). В первую очередь, обратите внимание на то, что, в отличие от типа A1, при использовании типа ссылок R1C1сначала записывается строка, а потом столбец. При использовании абсолютной адресации после символов R и C указывается собственно номер строки и столбца. Так, например, ячейка $B$3 имеет адрес R3C2. При использовании относительной адресации в стиле R1C1после обозначения строки или колонки в квадратных скобках указывается смещение по отношению к текущей ячейке. Так, например, если данные находятся в ячейке B3, а ссылка на нее программируется в ячейке А5, то в формуле она запишется как R[–2]C[1]. Эта запись может интерпретироваться как обращение к ячейке, находящейся на две строки выше и одну колонку правее текущей. Соответственно, запись R[2]C[–1] означает обращение к ячейке на две строки ниже и одну колонку левее (по отношению к активной ячейке A5 такая ячейка не существует).

Пример 1. Рассмотрим таблицу, показанную на рис. 1. В ней необходи­мо рассчитать индекс массы тела (ИМТ) каждого пациента, рассчитать среднее значения индекса по всем пациентам и выставить оценку индекса. Записывался макрос с именем Макрос3.

Рисунок 2 Таблица с расчетом ИМТ

Текст макроса имеет вид:

 

Макрос3()

Range("D2").Select

ActiveCell.FormulaR1C1 = "=(RC[-1]*10000)/RC[-2]^2"

Selection.AutoFill Destination:=Range("D2:D5"), Type:=xlFillDefault

Range("D2:D5").Select

Range("D6").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)"

Range("E2").Select

ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]<18,""low"",IF(AND(RC[-1]>18,RC[-1]<24),""middle"",""heigh""))"

Selection.AutoFill Destination:=Range("E2:E5"), Type:=xlFillDefault

Range("E2:E5").Select

End Sub

Первый исполняемый оператор программы Range(“D2”).Selectсоздан системой в виде выражения, которое содержит в терминологии VBA свойс­тво Range в сочетании с методом Select. Обратите внимание на то, что свойство имеет записанный в круглых скобках аргумент в виде строки символов и отделяется от метода точкой. В нашем примере аргумент свойс­тва представляет собой ссылку на ячейку в стиле A1, с которой началось программирование макроса.

 

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

Порядок выполнения работы

I. Настройка Excel

1. Откройте книгу Excel. Сделайте ее настройку:

1) выполните команду Сервис / Параметры и в диалоговом окне выберите вкладку Общие, установив следующие параметры:

2) стиль ссылок R1C1: выключено;

3) листов в новой книге: 3;

4) стандартный шрифт: размер 10;

5) выберите рабочий каталог для сохранения новых книг;

6) введите имя пользователя;

7) выберите вкладку Вид, установив флажки следующих параметров:

· отображать: область задач при запуске, строку формул, строку состояния, окна на панели задач;

· примечания: только индикатор;

 

Рисунок 3 Настройка. Окно «Параметры», вкладка «Общие»

 

8) объекты: отображать;

9) параметры окна: заголовки строк и столбцов, горизонтальная полоса прокрутки, символы структуры, вертикальная полоса прокрутки, сетка, нулевые значения, ярлычки листов;

 

Рисунок 4 Настройка. Окно «Параметры», вкладка «Вид»

10) выберите вкладку Вычисления, установив флажки следующих параметров:

· вычисления: автоматически;

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

 

2. Переименуйте рабочий лист, выполнив следующие действия:

· установите указатель мыши на вкладку с именем листа (Лист 1) и вызовите контекстное меню, щелкнув правой клавишей мыши;

· выберите в текстовом меню параметр «Переименовать», введите имя в диалоговое меню: «Исследование»

3. Сохраните созданную рабочую книгу с новым именем, выполнив команду Файл / Сохранить как.

II. Запись макроса

По исходным данным рассчитайте возраст пациентов

Рисунок 5 Таблица с исходными данными

Для отображения панели инструментов Visual Basic необходимо открыть пункт меню

Вид->Панели инструментов-> Visual Basic

Для записи макроса на панели инструментов Visual Basic необходимо нажать кнопку запись макроса

 

Введите имя макроса: ВозрастФормула

 

С этого момента все действия записываются в макрос.

1. Поставьте курсор в ячейку G4

2. Введите формулу для расчета возраста, формула рассчитывает разность между текущей датой и датой рождения:

=ГОД(СЕГОДНЯ())-ГОД(B4) - ЕСЛИ(ИЛИ(МЕСЯЦ(СЕГОДНЯ())<МЕСЯЦ(B4); И(МЕСЯЦ(СЕГОДНЯ())=МЕСЯЦ(B4);ДЕНЬ(СЕГОДНЯ())<ДЕНЬ(B4)));1;0)

 

a) функции СЕГОДНЯ: Возвращает текущую дату в числовом формате. Числовой формат даты — это код даты и времени, с помощью которого в Microsoft Excel производятся вычисления над датами и промежутками времени.

b) функции ГОД: Возвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999.

 

 

3. Скопируйте созданную формулу до ячейки G13

4. Остановите запись макроса (кнопка стоп)

 

Рисунок 6 Таблица с результатом выполнения записанного макроса «ВозрастФормула»

 

Для просмотра результата записи макроса необходимо зайти в пункт меню

Сервис->Макрос->Редактор Visual Basic

 

Рисунок 7 Путь к Редактору Visual Basic

 

Командой Сервис / Макрос / Редактор VisualBasic запустите редактор VisualBasic. В окне проектов (Project-VBAProject) раскройте содержимое проекта VBAProject(PERSONAL.XLS) и ветвь Modules. В ее составе должен быть один (например, Module1) или несколько модулей. Дважды щелкните левой кнопкой мыши по имени модуля. В ответ в правом верхнем окне должен появиться его текст. Просмотрите содержимое модулей и найдите записанный вами макрос.