Найти тему
EvvA -Excel Word Application

Создаём свои функции в EXCEL

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

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

Макрос - программный алгоритм действий, записанный пользователем.

Для этого необходимо:

1. Открыть вкладку "Файл";

2. Выбрать "Параметры";

3. Выбрать "Центр управления безопасностью" и "Параметры центра управления безопасностью";

4. Выбрать "Параметры макросов" и "Включить все макросы".

На рисунке 1 показано расположение библиотеки функций. Как видно из рисунка, стандартные функции находятся на вкладке "Формулы" (Здесь и далее будут рассмотрены примеры относительно Microsoft Office 2016)

                                                           Рисунок 1 - Библиотека функций
Рисунок 1 - Библиотека функций

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

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

Для созданию модуля, необходимо открыть редактор Visual Basic.

Visual Basic for Application (VBA) - упрощённая реализация языка программирования Visual Basic, встроенная в линейку продуктов Microsoft Office.

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

1. Перейти на вкладку "Файл" (рисунок 2)

                                                           Рисунок 2 - Вкладка "Файл"
Рисунок 2 - Вкладка "Файл"

2. В самом низу выбрать пункт "Параметры" (рисунок 3)

                                                           Рисунок 3 - Вкладка "Параметры"
Рисунок 3 - Вкладка "Параметры"

3. В меню слева выбрать пункт "Настроить ленту"

4. Справа в меню "Основные вкладки", поставить галочку напротив вкладки "Разработчик"

5. Нажать "ОК"

На рисунке 4 показан путь включения вкладки "Разработчик".

                                                           Рисунок 4 - Включение вкладки "Разработчик"
Рисунок 4 - Включение вкладки "Разработчик"

После этого, появится новая вкладка "Разработчик". Данная вкладка показана на рисунке 3.

                                                           Рисунок 5 - Вкладка "Разработчик"
Рисунок 5 - Вкладка "Разработчик"

Теперь необходимо нажать "Visual Basic". Откроется окно редактора Microsoft Visual Basic for Application. Во вкладке "Insert" выбрать "Module". На рисунке 6 представлен данный алгоритм.

                                                           Рисунок 6 - Добавление модуля в книгу
Рисунок 6 - Добавление модуля в книгу

После того, как модуль вставился, в дереве книги появится папка "Modules" и в ней новый модуль "Module1", как показано на рисунке 7.

                                                           Рисунок 7 - Положение модуля в книге
Рисунок 7 - Положение модуля в книге

Далее, можно приступать к непосредственному созданию собственной функции.

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

Public Function ПЛТРЕУГВЫСОТА(ОСНОВАНИЕ As Double, ВЫСОТА As Double) As Double
ПЛТРЕУГВЫСОТА = 1 / 2 * ОСНОВАНИЕ * ВЫСОТА ' Формула расчёта площади треугольника по основанию и высоте
End Function

Скопируйте данный код и вставьте его в "Module1". На рисунке 8 показано то, что у вас должно получиться.

Рисунок 8 - Код функции расчёта площади треугольника по известным значениям основания и высоты
Рисунок 8 - Код функции расчёта площади треугольника по известным значениям основания и высоты

В написании своих функций есть определённые правила:

1. Функции начинаются с обязательного оператора Function и заканчиваются обязательным параметром End Function;

2. Функция выполняет вычисления, а не действия.

Первая строка кода содержит обязательный параметр "Function", название функции (ПЛТРЕУГВЫСОТА) и её аргументов (ОСНОВАНИЕ, ВЫСОТА). Перед "Function" прописан необязательный аргумент "Public". Данный аргумент указывает на то, что процедура "Function" доступна для всех других процедур во всех модулях.

Название функции необходимо выбирать так, чтобы оно более ясно отражало суть функции. В данном примере: ПЛТРЕУГВЫСОТА - площадь треугольника по высоте.

Далее идут входные параметры функции - те значения, по которым будут произведены расчёты. В данным примере: ОСНОВАНИЕ As Double, ВЫСОТА As Double - "As Double" это тип данных. Указывание типа данных после скобок, показывает на тип возвращаемого значения. В данном примере, функция "ПЛТРЕУГВЫСОТА" возвращает число типа double.

Double используется для хранения:
1. Отрицательных чисел от -1,79769313486232*10^308 до -4,94065645841247*10^(-324)
2. Положительных чисел от 4,94065645841247*10^(-324) до 1,79769313486232*10^308.
Числа, хранимые с использованием типа Double, называются числами двойной точности.

После ввода начальных параметров, пишутся требуемые формулы. В данном примере: ПЛТРЕУГВЫСОТА = 1 / 2 * ОСНОВАНИЕ * ВЫСОТА. В данной формуле для наглядности исчислений каждая переменная именована полностью в соответствии со своим назначением.

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

В конце ставится обязательный параметр "End Function", заканчивающий функцию.

Теперь, на листе можно написать собственную функцию. На рисунке 9 показано её использование. В ячейке D2 введена пользовательская функция и получен её результат.

                                                           Рисунок 9 - Применение собственной функции
Рисунок 9 - Применение собственной функции

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

(с)
(с)