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

Функциите по избор, като например макроси, използват езика за програмиране Visual Basic приложения (VBA). Те се различават от макросите по два важни начина. Първо, те използват процедури за функции вместо подпрограми. Т.е. те започват с команда функция вместо команда 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
    

Забележка: За да направите кода по-четим, можете да използвате клавиша Tab, за да отстъпите редове. Отстъпът е само за ваша полза и е незадължителен, тъй като кодът ще се изпълнява със или без него. След като въведете ред с отстъп, редакторът на Visual Basic предполага, че следващият ред ще бъде с подобен отстъп. За да се придвижите (т.е. наляво) един знак за табулатор, натиснете Shift+Tab.

Сега сте готови да използвате новата функция 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

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

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

Discount = 0

И накрая, следната команда закръгляване на стойността, присвоена на променливата Отстъпка, до два знака след десетичния знак:

Discount = Application.Round(Discount, 2)

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

Функцията по избор трябва да започва с команда функция и да завършва с команда End Function. В допълнение към името на функцията командата Функция обикновено задава един или повече аргументи. Можете обаче да създадете функция без аргументи. Excel включва няколко вградени функции – НАПРИМЕР RAND и NOW – които не използват аргументи.

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

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

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

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

Пример за VBA функция с коментари

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

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

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

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

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

Избиране на опцията за израз в екрана ''Групиране, сортиране и общи суми''.

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

  1. След като сте създали функциите, които ви трябват, щракнете върху Файл >Запиши като.

    В Excel 2007 щракнете върху бутона Microsoft Office ищракнете върху Запиши като

  2. В диалоговия прозорец Запиши като отворете падащия списък Запиши като тип и изберете Excel добавката. Запишете работната книга под разпознаваемо име, като например MyFunctions, в папката AddIns. Диалоговият прозорец Запиши като ще предложи тази папка, така че всичко, което трябва да направите, е да приемете местоположението по подразбиране.

  3. След като сте записали работната книга, щракнете върху Файл > Excel Опции.

    В Excel 2007 щракнете върху бутона Microsoft Office ищракнете върху Excel Опции.

  4. В диалоговия Excel опции щракнете върху категорията Добавки.

  5. В падащия списък Управление изберете Excel добавки. След това щракнете върху бутона Отиди.

  6. В диалоговия прозорец Добавки поставете отметка в квадратчето до името, което сте използвали за записване на работната книга, както е показано по-долу.

    add-ins dialog box

  1. След като сте създали функциите, които ви трябват, щракнете върху Файл >Запиши като.

  2. В диалоговия прозорец Запиши като отворете падащия списък Запиши като тип и изберете Excel добавката. Запишете работната книга под разпознаваемо име, като например MyFunctions.

  3. След като сте записали работната книга, щракнете върху Инструменти > Excel добавки.

  4. В диалоговия прозорец Добавки изберете бутона Преглед, за да намерите вашата добавка, щракнете върху Отвории след това поставете отметка в квадратчето до вашия Add-In в полето Налични добавки.

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

Отваряне на файл в монополен режим

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

Това съдържание първоначално е автор на Mark Dodge и Craig Stinson като част от тяхната книга Microsoft Office Excel 2007 Inside Out. Оттогава тя е актуализирана, за да се прилага и за по-Excel версии на Excel.

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

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

Нуждаете се от още помощ?

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединяване към Microsoft Office участници в Insider

Беше ли полезна тази информация?

Доколко сте доволни от качеството на езика?
Какво е повлияло на вашия потребителски опит?

Благодарим ви за обратната връзка!

×