§1. Технология создания пользовательских функций
Значительным преимуществом программы Excel является возможность создания формул. Формулы представляют собой выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=) и может состоять из пяти типов элементов:
Операторы. Символы, например "+" (сложение) и "*" (умножение).
Ссылки на ячейки. В эту категорию входят именованные ячейки и диапазоны, относящиеся к текущему рабочему листу, ячейкам другого листа текущей книги и даже ячейкам листа другой рабочей книги.
Значения или строки. Например, 7,5 или "Результаты на конец года".
Скобки. Задают порядок выполнения действий в формуле.
Функции и их аргументы. Например, СУММ или СРЗНАЧ и их аргументы.
Формула не может содержать более, чем 1024 символа. Если необходима формула, количество символов в которой превышает предельно допустимое, следует разделить ее на несколько формул или создать пользовательскую функцию (с помощью VBА).
Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, функция ОКРУГЛ округляет число в ячейке A10 (см. рис. 1).
Рис. 1.Структура функции
Структура функции.
Структура функции начинается со знака равенства (=), за ним следует имя функции, открывающая скобка, список аргументов, разделенных запятыми, закрывающая скобка (см. рис. 1).
Имя функции. Для появления списка доступных функций нужно щелкнуть ячейку и нажать клавишу SHIFT+F3.
Аргументы. Существуют различные типы аргументов: число, текст, логическое значение (ИСТИНА и ЛОЖЬ), массивы, значение ошибки (например #Н/Д), или ссылки на ячейку. В качестве аргументов используются константы, формулы, или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.
Всплывающая подсказка аргумента. Всплывающая подсказка с синтаксисом и аргументами появляется после ввода функции. Например, всплывающая подсказка появится после ввода =ОКРУГЛ(. Всплывающие подсказки появляются только для встроенных функций.
Диалоговое окно Мастер функций облегчает ввод функций при создании формул, содержащих функции. При вводе функции в формулу диалоговое окно Мастер функций отображает имя функции, все ее аргументы, описание функции и каждого аргумента, текущий результат функции и всей формулы.
В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.
Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».
В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции СРЗНАЧ и СУММ считаются функциями второго уровня, потому что обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня и так далее.
Функции удобны, поскольку они:
Упрощают формулы.
Позволяют производить вычисления, которые невозможно осуществить без них.
Ускоряют выполнение некоторых задач редактирования.
Позволяют налагать условия на выполнение формул, что дает последним элементарную способность к принятию решений.
В Excel внедрено более 300 предопределенных функций, более того, после установки надстройки "Пакет анализа" становятся доступными дополнительные функции. Excel предоставляет возможность создания дополнительных (пользовательских) функций средствами редактора Visual Basic.
Язык программирования VBА — мощный инструмент, позволяющий Excel выполнять такие задачи, осуществить которые иными средствами чрезвычайно трудно или невозможно. С помощью VBА создаются следующие основные типы макросов:
макросы, автоматизирующие операции в Excel;
макросы, функционирующие в качестве пользовательских функций, которые используются при записи формул таблиц.
При существовании многих функций, доступных в Excel и VBA, новые функции рабочего листа могут понадобиться:
для упрощения работы и для предоставления формулам большей гибкости. Например, можно создать функцию, значительно сокращающую стандартные формулы.
сокращенные формулы читабельнее и с ними намного проще работать. Однако пользовательские функции в формулах работают гораздо медленнее, чем встроенные. Но в производительных системах скоростное различие часто протекает незамеченным.
Пользовательские функции не могут никоим образом изменять среду Excel. Это означает, что определенная пользователем функция не может выбирать, вставлять, удалять или форматировать никакие данные в рабочем листе, а также не может добавлять, удалять или переименовывать листы или рабочие книги.
Когда Excel вычисляет значения рабочего листа, он рассчитает и пользовательскую функцию. Каждый оператор в функции анализируется и выполняется, а результат возвращается на рабочий лист. Функцию можно использовать неограниченное количество раз и применять ее любого значения ячеек.
Пользовательская функция работает так же, как и любая другая внедренная функция. Ее можно вставлять в формулу с помощью команды меню ВставкаФункция или используя кнопку вставки функции в строке формул. В диалоговом окне вставки функции пользовательские функции представляются как функции, созданные пользователем. Как и другие функции, их можно использовать в более сложных формулах.
|
