MS Excel

Вопрос

Версия программы: Office XP
При работе с ячейками и в Excele можно использовать множество встроенных функций типа МАХ, SUM etc.

А как написать пользовательскую функцию? Я не знаком с VBA, поэтому хотелось бы в живую увидеть хотя бы один готовый пример пользовательской функции и понять, как их создавать (собственно нужен макрос, который по заданным точкам (=значениям ячеек) смоделирует математическую функцию, т.е. попросту сделает из точек ломаную функцию, соединив их, и сможет выдать значение этой функции для конкретного агрумента).

Ответ

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

Открываем редактор Visual Basic
(Service — Macros — Visual Basic Editor, или [Alt]-[F11] с клавиатуры).
Слева, в окошке с деревом проекта, щелкаем правой кнопкой мыши по названию книги, выбираем Insert — Module, получаем Module1. По нему двойной левой, справа появляется его содержимое (пока пустое). Там набираем примерно такой код:

Public Function TestFormula(a As Range)
Dim z as Long z = 0 For i = 1
To a.Rows.Count z = z + a.Cells(i, 1).Value Next
TestFormula = z End Function

Подробнее:

TestFormula — имя формулы, произвольное.
a — переменная-аргумент, в данном случае диапазон, т.е. функции можно будет подсунуть «A1:B5».
Если нужна будет только одна ячейка, то пишем «a As Variant»

Дальше идет короткий пример программы:

"Dim <имя> As <тип>» — объявление переменной (кстати, не обязательно, можно «на лету»)

"For <от> to <до>" — простейший цикл. Здесь a.Rows.Count — количество строк в аргументе-диапазоне. Соответственно a.Columns.Count — количество колонок, и.т.д.

"z = z + " — это, я думаю, и так понятно.
"a.Cells(i,1).Value", буквальный перевод такой:
В объекте «a» (который у нас типа «диапазон», в англ. варианте Range) взять объект «Cells» (который тоже, кстати, Range, т.е. массив ячеек), там взять объект (т.е. ячейку) с индексом i (строка), 1 (столбец), и у этого объекта взять свойство Value (т.е. значение).

"TestFormula = z" — самая важная в функции строка, которая устанавливает,какое значение она возвращает. Слева должно стоять имя функции, как в заголовке. Справа — что нам нужно.
Таким образом, получаем функцию, суммирующую все ячейки в первой столбце указанного диапазона.

На самом деле, многое еще надо доработать, например, что делать, если текст попадется, так как этот пример выдаст ошибку.
Если нам нужны еще функции, то их можно создать здесь же.
Т.е. еще раз пишем

Public Function <имя>(<список аргументов>) As <возвращаемый тип>
<код функции>
End Function
и.т.д.)

2. Применение всего этого.

Допустим, в ячейках A1…A10 находятся числа. Их сумму нужно поместить в ячейку B1.

Тогда в ней пишем:

=TestFormula(A1:A10)

В списке функций (там, где всякие MAX и т.п.) наша будет находиться в разделе «Другие функции», подразделе «Определенные пользователем», который самый последний. У меня русская версия, в английской, вероятно, будет «Other…» — «User defined».

3. По смыслу задачи. Возможно, помогут функции TEND или FORECAST. Ломаная, по-моему, Excel строить не умеет. Есть линия тренда типа «линейная фильтрация», но в функциях я ее не нашел.

Или же, придется строить многочлен Лагранжа, но это уже отдельная тема.

Из конференции Expert_FAQ