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

Забележка: Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

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

Търсите информация как да създадете потребителска функция, която може да се изпълнява в Excel за Windows, Excel for Mac или Excel online? Ако е така, вижте статията общ преглед на функциите на Excel.

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

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

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

Примерен формуляр за поръчка без функция по избор

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

  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.

Сега сте готови да използвате новата функция за отстъпки. Затворете редактора на Visual Basic, изберете клетка Г7 и въведете следното:

= ОТБИВ (D7; E7)

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

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

Нека разгледаме как Excel интерпретира тази процедура за функция. Когато натиснете Enter, Excel търси отстъпката за име в текущата работна книга и установи, че е функция по избор във 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, която съхранява стойност в променлива, се нарича инструкция за Присвояване , тъй като изчислява израза в дясната страна на знака за равенство и присвоява резултата на името на променливата отляво. Тъй като отстъпката на променливата има същото име като процедурата за функция, стойността, съхранена в променливата, се връща към формулата на работния лист, която се нарича функция отстъпката.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA няма функция ROUND, но Excel прави. Следователно, за да използвате РУНД в тази декларация, трябва да укажете на VBA да търси метода за кръгли (функция) в обекта на приложението (Excel). Можете да направите това, като добавите приложението Word преди думата кръгли. Използвайте този синтаксис всеки път, когато трябва да получите достъп до функция на Excel от VBA модул.

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. След като сте създали функциите, от които имате нужда, щракнете върху файл _GT_ Запиши като.

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

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

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

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

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

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

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

    add-ins dialog box

  1. След като сте създали функциите, от които имате нужда, щракнете върху файл _GT_ Запиши като.

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

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

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

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

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

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

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

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

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

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

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

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

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×