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

Пользовательские функции создаются в среде Microsoft Visual Basic® для приложений (VBA). VBA — это встроенный язык программирования Excel. VBA является весьма гибким инструментом, обеспечивающим все возможности формул Excel, а также обладающим многими другими достоинствами.

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

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

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

1. В меню Сервис выберите команду Макрос, а затем — команду Редактор Visual Basic.
2. В окне Microsoft Visual Basic в меню Insert выберите команду Module.
3. В окне Moduleномер введите программу, реализующую нужную функцию. Например, функция для расчета комиссионных с продаж на уровне 6% может выглядеть следующим образом:

   

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

4. В меню File выберите команду Close and Return to Microsoft Excel.
5. Созданная функция может использоваться в формулах на листе так же, как и любая другая функция. Например, с помощью функции Commission можно рассчитать комиссионные с продаж:

   

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

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

Дополнительные сведения

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

Чтобы получить сведения о создании пользовательских функций в среде VBA, введите фразу Function procedure в помощнике или на вкладке мастера ответов в окне справки по Visual Basic программы Excel, нажмите кнопку Найти, а затем выберите разделы «Writing a Function Procedure» и «Function Statement».

Начинающим пользователям, которые хотят приступить к программированию на языке VBA, полезно прочитать книгу Руководство программиста по Visual Basic для Microsoft Office 2000 (Microsoft Office 2000/Visual Basic Programmer's Guide). Чтобы узнать, где можно приобрести это руководство, введите фразу Руководство программиста в помощнике или в окне справки Excel на вкладке Мастер ответов и нажмите кнопку Найти.