Excel_lab_4

Microsoft Excel. Работа № 4 – Построение графиков функций.

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

1. Цель занятия:

Научиться строить графики функций, создавать пользовательские функции с

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

2. Теоретические сведения

Excel обладает возможностью автоматизации обработки табличных данных с

использованием как встроенных функций (таких как SIN(), COS() и т.п.), так и функций,

созданных самим пользователем.

Функции пользователя

Функции пользователя – это функции, не входящие в стандартную библиотеку

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

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

встроенный в Excel язык программирования Visual Basic for Application (VBA).

Модули

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

Макросы

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

Элементы управления

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

Создание функций пользователя

Для того, чтобы создать новую функцию и включить ее в библиотеку функций

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

Visual Basic for Application (VBA).

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

открыть редактор Visual Basic, выполнив команду меню: Разработчик → Visual Basic

или нажать комбинацию клавиш Alt+F11;

в открывающемся окне программы Microsoft Visual Basic for Application выполнить команду меню Вставка (Insert) → Модуль (Module);

в открывшемся окне выполнить команду меню Вставка (Insert) → Процедура

(Procedure);

в открывающемся диалоговом окне Добавить процедуру (Add Procedure) в поле

Имя (Name) ввести имя создаваемой функции (например, yvba) и активировать флажки Функция (Function) и Открытая (Public);

Замечание !!! Имя функции должно отличаться от возможных адресов ячеек таблицы

(например, A1 или XY24 – это некорректные имена функций);

в открывшемся окне листа модуля программы появится заготовка для написания функции:

Public Function yvba( )

End Function

Используя эту заготовку, необходимо создать функцию пользователя y(x) из п.1

задания:

Аналогично создаются все остальные функции из задания.

Записав функции на листе модуля, необходимо вернуться в окно программы Excel на лист Графики 1 и вставить вызов функций yvba( ), gvba() и zvba( ) в ячейки F2, G2 и H2

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

После этого необходимо скопировать формулы из ячеек F2, G2 и H2 в ячейки F3 ÷ F22, G3 ÷ G22 и H3 ÷ H22 соответственно.

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

Сохранить.

Создание элементов управления

Для создания элементов управления необходимо выбрать пункт меню Разработчик → Вставить → Элементы ActiveX, после чего в открывшемся окне выбрать нужный элемент (например, кнопку).

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

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

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

Например, для изменения названия элемента с CommandButton6 на Автоматическое заполнение необходимо соответственно изменить свойство Caption.

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

Создание макросов

Для создания макросов необходимо выбрать пункт меню Разработчик → Запись макроса.

В открывшемся окне необходимо задать имя макроса (например, Обновление графиков) и нажать на кнопку ОК.

После этого необходимо выполнить те действия и в той последовательности, в которой они будут в дальнейшем выполняться при вызове макроса (например, выделить диапазон ячеек (J2:J22) и скопировать его в диапазон ячеек (A2:A22)). По окончании записи макроса необходимо выбрать пункт меню Остановить запись.

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

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

Для выполнения, редактирования или удаления макроса необходимо выбрать пункт меню Разработчик → Макросы или нажать комбинацию клавиш Alt+F8. В результате откроется диалоговое окно со списком всех имеющихся макросов.

Для запуска макроса на выполнение кроме команды меню Разработчик → Макросы

→ Имя макроса→ Выполнить можно использовать элементы управления. Для создания элемента управления, который будет запускать на выполнение тот или иной макрос необходимо выбрать пункт меню Разработчик → Вставить → Элементы управления формы → Кнопка.

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

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

3.Задание

3.1.Создать во вновь созданной рабочей книге Excel 5 рабочих листов со следующими названиями: Содержание, Графики 1, Графики 2, Графики 3, Поверхности.

На лист Содержание скопировать свой вариант задания.

3.2. Построить на листе Графики 1 на отдельных диаграммах графики функций в

соответствии с полученным вариантом задания.

В качестве примера рассмотрим построение следующих функций для значений x - 2;2 :

Ячейки B2, C2, D2 протянем вниз до значения x равного 2. В результате получим таблицу,

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