Създаване на функции по избор в Excel

Макар че Excel включва множество вградени функции за работен лист, има вероятност да няма функция за всеки тип изчисление, което извършвате. Конструкторите на Excel не биха могли да предвидят нуждите от изчисления на всеки потребител. Вместо това Excel ви дава възможност да създавате функции по избор, които са обяснени в тази статия.

Създаване на проста потребителска функция

Функциите по избор, подобно на макросите, използват програмния език Visual Basic for Applications (VBA ). Те се различават от макросите по два съществени начина. Първо, те използват функционални процедури вместо подпроцедури . Тоест, те започват с команда за функция вместо команда Sub и завършват с команда End Function вместо End Sub. Второ, те извършват изчисления, вместо да предприемат действия. Някои видове команди, като например команди, които избират и форматират диапазони, са изключени от функциите по избор. В тази статия ще научите как да създавате и използвате функции по избор. За да създавате функции и макроси, работите с редактора на Visual Basic (VBE), който се отваря в нов прозорец, отделен от Excel.

Да предположим, че фирмата ви предлага отстъпка от 10 процента за продажба на продукт, при условие че поръчката е за повече от 100 бройки. В следващите параграфи ще покажем функция за изчисляване на тази отстъпка.

Примерът по-долу показва формуляр за поръчка, който изброява всеки елемент, количество, цена, отстъпка (ако има такава) и получената разширена цена.

За да създадете потребителска функция DISCOUNT в тази работна книга, изпълнете следните стъпки:

1. Натиснете Alt+F11 , за да отворите редактора на Visual Basic (на Mac натиснете FN+ALT+F11), след което щракнете върху "Вмъкване>на модул". В дясната страна на редактора на Visual Basic се появява нов прозорец на модул.

2. Копирайте и поставете следния код в новия модул.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

Използване на потребителски функции

Вече сте готови да използвате новата функция DISCOUNT. Затворете редактора на Visual Basic, изберете клетка G7 и въведете следното:

=DISCOUNT(D7;E7)

Excel изчислява 10-процентната отстъпка за 200 бройки като 47,50 лв. за един брой и връща 950,00 лв.

В първия ред на кода на VBA, функция DISCOUNT(количество; цена), посочихте, че функцията DISCOUNT изисква два аргумента – количество и цена. Когато извиквате функцията в клетка на работен лист, трябва да включите тези два аргумента. Във формулата =DISCOUNT(D7;E7) D7 е аргументът за количество , а E7 е аргументът за цена . Сега можете да копирате формулата DISCOUNT в G8:G13, за да получите резултатите, показани по-долу.

Нека разгледаме как Excel тълкува тази процедура на функция. Когато натиснете Enter, Excel търси името DISCOUNT в текущата работна книга и открива, че това е функция по избор във VBA модул. Имената на аргументите, заградени в скоби " Количество " и "Цена", са контейнери за стойностите, на които се основава изчисляването на отстъпката.

Инструкцията If в следващия блок с кодове разглежда аргумента "количество" и определя дали броят на продадените артикули е по-голям или равен на 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Ако броят на продадените артикули е по-голям или равен на 100, VBA изпълнява следната команда, която умножава стойността на количеството по стойността на цената и след това умножава резултата по 0,1:

Discount = quantity * price * 0.1

Резултатът се съхранява като променливата Discount. VBA команда, която съхранява стойност в променлива, се нарича команда за присвояване , защото изчислява израза отдясно на знака за равенство и присвоява резултата на името на променливата вляво. Тъй като променливата Discount има същото име като процедурата на функцията, стойността, съхранена в променливата, се връща във формулата в работния лист, която извиква функцията DISCOUNT.

Ако "количество" е по-малко от 100, VBA изпълнява следната команда:

Discount = 0

Накрая, следната команда закръгля стойността, присвоена на променливата Discount , до две цифри след десетичния знак:

Discount = Application.Round(Discount, 2)

VBA няма функция ROUND, но Excel има. Следователно, за да използвате ROUND в тази команда, казвате на VBA да търси метода (функцията) Round в обекта на приложението (Excel). Това става чрез добавяне на думата Application преди думата Round. Използвайте този синтаксис, когато имате нужда от достъп до функция на Excel от VBA модул.

Разбиране на правилата на функцията по избор

Функцията по избор трябва да започва с инструкция за функция и да завършва с команда за край на функцията. Освен името на функцията, командата Function обикновено задава един или повече аргументи. Можете обаче да създадете функция без аргументи. Excel включва няколко вградени функции, например RAND и NOW, които не използват аргументи.

След командата за функция, една процедура на функцията включва една или повече VBA команди, които вземат решения и извършват изчисления с помощта на аргументите, подадени на функцията. И накрая, някъде в процедурата за функцията трябва да включите команда, която присвоява стойност на променлива със същото име като функцията. Тази стойност се връща на формулата, която извиква функцията.

Използване на ключови думи на VBA във функции по избор

Броят на ключовите думи на VBA, които можете да използвате във функции по избор, е по-малък от броя, който можете да използвате в макроси. На функциите по избор не е позволено да правят нищо друго, освен да връщат стойност на формула в работен лист или на израз, използван в друг VBA макрос или функция. Например потребителските функции не могат да преоразмеряват прозорци, да редактират формула в клетка или да променят опциите за шрифт, цвят или шарка за текста в клетка. Ако включите код "действие" от този вид в процедура на функцията, функцията връща #VALUE! грешка.

Единственото действие, което една процедура на функция може да извършва (освен извършването на изчисления), е да покаже диалогов прозорец. Можете да използвате команда InputBox във функция по избор като средство за получаване на информация от потребителя, който изпълнява функцията. Можете да използвате команда MsgBox като средство за предаване на информация до потребителя. Можете също да използвате диалогови прозорци по избор или потребителски формуляри, но това е тема извън обхвата на това въведение.

Документиране на макроси и функции по избор

Дори прости макроси и потребителски функции могат да бъдат трудни за четене. Можете да ги направите по-лесни за разбиране, като въведете пояснителен текст под формата на коментари. Коментарите се добавят чрез апостроф пред пояснителния текст. Например следващият пример показва функцията DISCOUNT с коментари. Добавянето на коментари като този улеснява вас или другите потребители в поддържането на кода на VBA, докато времето минава. Ако в бъдеще се налага да направите промяна в кода, ще ви бъде по-лесно да разберете какво сте направили първоначално.

Апострофът указва на Excel да игнорира всичко отдясно на един и същ ред, така че да можете да създавате коментари или в редове сами, или от дясната страна на редовете, съдържащи VBA код. Може да започнете сравнително дълъг блок от код с коментар, който обяснява основната му цел, и след това да използвате вградени коментари, за да документирате отделните декларации.

Друг начин за документиране на макросите и функциите по избор е да им дадете описателни имена. Например вместо да наименувате макрос "Етикети", можете да го наречете "Етикети на месеца ", за да опишете по-конкретно целта, която служи макросът. Използването на описателни имена за макроси и функции по избор е особено полезно, когато сте създали много процедури, особено ако създавате процедури, които имат подобни, но не идентични цели.

Как документирате макросите и функциите по избор е въпрос на лични предпочитания. Важното е да възприемете някакъв метод на документация и да го използвате последователно.

Направете функциите си по избор достъпни навсякъде

За да използвате функция по избор, работната книга, която съдържа модула, в който сте създали функцията, трябва да бъде отворена. Ако тази работна книга не е отворена, получавате #NAME? при опит за използване на функцията. Ако препращате към функцията в друга работна книга, трябва да поставите пред името на функцията името на функцията името на функцията името на името си, в която се намира функцията. Ако например създадете функция, наречена DISCOUNT, в работна книга, наречена Personal.xlsb, и извикате тази функция от друга работна книга, трябва да въведете =personal.xlsb!discount(), а не просто =discount().

Можете да си спестите някои натискания на клавиши (и възможни грешки при въвеждане), като изберете функциите по избор от диалоговия прозорец "Вмъкване на функция". Вашите функции по избор се показват в категорията "Дефинирани от потребителя":

По-лесен начин да направите своите потребителски функции достъпни по всяко време е да ги съхраните в отделна работна книга и след това да запишете тази работна книга като добавка. След това можете да направите добавката достъпна всеки път, когато изпълнявате Excel. Ето как да направите това:

След като изпълните тези стъпки, вашите функции по избор ще са достъпни всеки път, когато стартирате Excel. Ако искате да добавите към библиотеката с функции, се върнете към редактора на Visual Basic. Ако погледнете в прозореца на проекта на редактора на Visual Basic под заглавие на VBA проект, ще видите модул с име на вашия файл на добавката. Вашата добавка ще има разширение .xlam.

Двукратното щракване върху този модул в прозореца на проекта кара редактора на Visual Basic да покаже функционалния код. За да добавите нова функция, поставете точката на вмъкване след командата End Function, която прекратява последната функция в прозореца "Код", и започнете да пишете. По този начин можете да създадете толкова функции, колкото ви трябват, и те винаги ще бъдат на разположение в категорията "Дефинирани от потребителя" в диалоговия прозорец "Вмъкване на функция ".

За авторите

Това съдържание първоначално е написано от Марк Додж и Крейг Стинсън като част от книгата им Microsoft Office Excel 2007 Inside Out. Оттогава тя е актуализирана, за да се прилага и за по-новите версии на Excel.

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност за Excel или да получите поддръжка в общностите.