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

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

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

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

=DISCOUNT(D7,E7)

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

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

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

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

Ако отчета в следните блок от код проверява количеството аргумент и определя дали броя на продадените артикули е по-голямо или равно на 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 да търси метода Round (функция) в приложение на обект (Excel). Можете да направите това чрез добавяне на думата приложение преди думата кръг. Използвайте следния синтаксис, всеки път, когато трябва да имате достъп до функция на Excel от VBA модул.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    add-ins dialog box

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

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

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

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

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

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

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

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

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

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

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

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

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

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

×