Программирование собственных формул рабочего листа
Часто в Excel возникает необходимость запрограммировать собственные расчетные формулы, т.к. пакет доступных формул не содержит необходимых. Задачу конечно можно решить штатными средствами расчетов- комбинацией элементарных операций, но если такие формулы приходится применять часто, запомнить и воспроизводить их постоянно будет очень трудоемко и долго. К тому же это предъявляет высокие требование к пользователю, производящему расчеты - он должен обладать равными знаниями со всеми ключевыми пользователями и сложные расчеты нельзя будет перепоручить оператору.
Поясним на примере о чем может идти речь. В логистике существует ряд формул расчета оптимальных размеров заказа, для моделей с дефицитом, для случаев выполнения заявок, не выполнения заявок. Например, формула расчета максимального размера дефицита определяется по формуле:
где,
С0 - стоимость подачи заказа
D - годовой спрос
Ch - издержки хранения одной единицы товара
Sb - стоимость отсутствия запасов
В нормальном виде это формула в Excel, если эти данные предварительно введены в ячейки листа будет выглядить следующим образом:
=КОРЕНЬ(((2*N4*N3)/N6))*КОРЕНЬ((N5/(N5+N6)))
Согласитесь, вводить такую формулу каждый раз не очень удобно, тем более что нужно предварительно внести данные на лист, а также вывести пояснения к ним - где какой показатель, чтобы не запутаться в этой формуле.
Данное выражение можно запрограммировать в собственную формулу рабочего листа, которая в последствие будет отображаться в разделе пользовательских
Приведем ее код:
Function ДЕФИЦИТ(Годовой_спрос, Стоимость_подачи_заказа, Стоимость_хранения, Стоимость_отсут_запасов)
ДЕФИЦИТ = ((((2 * Стоимость_подачи_заказа * Годовой_спрос) / Стоимость_отсут_запасов)) ^ 0.5) * (((Стоимость_хранения / (Стоимость_отсут_запасов + Стоимость_хранения)) ^ 0.5))
End Function
Функцию мы называем так, как мы хотим ее вызывать с рабочего листа, аргументы функции (в скобках) мы тоже именуем понятными словосочетаниями, чтобы при вызове ее окна было удобно работать.
Эту функцию можно записать в любом модуле VBA. можно в нашей книге Учет производства, затем сохранить ее в настройку и поместить в нужную папку, тогда такие формулы будут доступны из любой книги Excel.
В итоге функция преобразуется из сложной. которая приведена выше в понятную вида:
=ДЕФИЦИТ(N3;N4;N5;N6)
Нужно все же сказать - если задачу можно решить штатными функциями Excel, то ее следует решать ими. Внутренние функции Excel написаны на языке C и работают намного быстрее, тех что создают пользователи на VBA. Но если ваши формулы действительно очень сложные и вводить их приходится часто, стоит воспользоваться созданием собственных функций, особенно если объем обрабатываемых данных не так велик.
Поясним на примере о чем может идти речь. В логистике существует ряд формул расчета оптимальных размеров заказа, для моделей с дефицитом, для случаев выполнения заявок, не выполнения заявок. Например, формула расчета максимального размера дефицита определяется по формуле:
С0 - стоимость подачи заказа
D - годовой спрос
Ch - издержки хранения одной единицы товара
Sb - стоимость отсутствия запасов
В нормальном виде это формула в Excel, если эти данные предварительно введены в ячейки листа будет выглядить следующим образом:
=КОРЕНЬ(((2*N4*N3)/N6))*КОРЕНЬ((N5/(N5+N6)))
Согласитесь, вводить такую формулу каждый раз не очень удобно, тем более что нужно предварительно внести данные на лист, а также вывести пояснения к ним - где какой показатель, чтобы не запутаться в этой формуле.
Данное выражение можно запрограммировать в собственную формулу рабочего листа, которая в последствие будет отображаться в разделе пользовательских
Приведем ее код:
Function ДЕФИЦИТ(Годовой_спрос, Стоимость_подачи_заказа, Стоимость_хранения, Стоимость_отсут_запасов)
ДЕФИЦИТ = ((((2 * Стоимость_подачи_заказа * Годовой_спрос) / Стоимость_отсут_запасов)) ^ 0.5) * (((Стоимость_хранения / (Стоимость_отсут_запасов + Стоимость_хранения)) ^ 0.5))
End Function
Функцию мы называем так, как мы хотим ее вызывать с рабочего листа, аргументы функции (в скобках) мы тоже именуем понятными словосочетаниями, чтобы при вызове ее окна было удобно работать.
Эту функцию можно записать в любом модуле VBA. можно в нашей книге Учет производства, затем сохранить ее в настройку и поместить в нужную папку, тогда такие формулы будут доступны из любой книги Excel.
В итоге функция преобразуется из сложной. которая приведена выше в понятную вида:
=ДЕФИЦИТ(N3;N4;N5;N6)
Нужно все же сказать - если задачу можно решить штатными функциями Excel, то ее следует решать ими. Внутренние функции Excel написаны на языке C и работают намного быстрее, тех что создают пользователи на VBA. Но если ваши формулы действительно очень сложные и вводить их приходится часто, стоит воспользоваться созданием собственных функций, особенно если объем обрабатываемых данных не так велик.
Комментарии
Отправить комментарий