Pielāgoto funkciju izveide programmā Excel

Pielāgotas funkcijas, piemēram, makro, izmanto Visual Basic for Applications (VBA) programmēšanas valodu. Tās atšķiras no makro divos ievērojamas veidos. Vispirms tie izmanto funkciju procedūras , nevis apakšprocesus . Tas nozīmē, ka tās sākas ar funkcijas priekšrakstu , nevis apakšatskaiti, un beidzas ar beigu funkciju, nevis ar End Sub. Pēc tam viņi veic aprēķinus, nevis veic darbības. Noteikta veida priekšraksti, piemēram, priekšraksti, kas atlasa un formatē diapazonus, tiek izslēgti no pielāgotām funkcijām. Šajā rakstā uzzināsit, kā izveidot un izmantot pielāgotas funkcijas. Lai izveidotu funkcijas un makro, strādājiet ar Visual Basic redaktoru (VBE), kas tiek atvērts jaunā logā atsevišķi no programmas Excel.

Pieņemsim, ka jūsu uzņēmums piedāvā 10 procentu daudzumu atlaidei produkta pārdošanas gadījumā, ja pasūtījums ir vairāk nekā 100 vienības. Nākamajās rindkopās mēs demonstrēsim funkciju, lai aprēķinātu šo atlaidi.

Tālāk redzamajā piemērā ir parādīta pasūtījuma forma, kurā norādīts katrs vienums, daudzums, cena, atlaide (ja tāda ir) un iegūtā kopējā cena.

Lai šajā darbgrāmatā izveidotu pielāgotu funkciju DISCOUNT, rīkojieties šādi:

1. Nospiediet taustiņu kombināciju Alt+F11 , lai atvērtu Visual Basic redaktoru (Mac datorā nospiediet taustiņu kombināciju FN+ALT+F11) un pēc tam noklikšķiniet uz > moduli. Visual Basic redaktora labajā pusē tiek parādīts jauns moduļa logs.

2. Kopējiet tālāk norādīto kodu un ielīmējiet to jaunajā modulī.

   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
   

Tagad esat gatavs izmantot jauno funkciju DISCOUNT. Aizveriet Visual Basic redaktoru, atlasiet šūnu G7 un ierakstiet:

=DISCOUNT(D7,E7)

Programma Excel aprēķina 10 procentu atlaidi 200 vienībām par 47,50 $ vienai vienībai un atgriež 950,00 $.

VBA koda pirmajā rindiņā Funkcija DISCOUNT(daudzums, cena) jūs norādīju, ka funkcijai DISCOUNT nepieciešami divi argumenti: daudzums un cena. Izsaucot funkciju darblapas šūnā, šie divi argumenti ir jāiekļauj. Formulā =DISCOUNT(D7,E7) D7 ir daudzuma arguments, bet E7 ir cenas arguments. Tagad varat kopēt formulu DISCOUNT uz G8:G13, lai iegūtu tālāk parādītos rezultātus.

Aplūkosim, kā programma Excel interpretē šīs funkcijas procedūru. Nospiežot taustiņu Enter, programma Excel meklē nosaukumu DISCOUNT pašreizējā darbgrāmatā un VBA modulī atrod, ka tā ir pielāgota funkcija. Argumenti, kas ir iekļauti iekavās , daudzums un cena, ir to vērtību vietturi, uz kurām ir balstīts atlaides aprēķins.

Priekšraksts If šajā koda blokā pārbauda argumentu Daudzums un nosaka, vai pārdoto preču skaits ir lielāks par vai vienāds ar 100:

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

Ja pārdoto preču skaits ir lielāks par vai vienāds ar 100, VBA izpilda šādu priekšrakstu, kas reizina daudzuma vērtību ar cenas vērtību un pēc tam rezultātu reizina ar 0,1:

Discount = quantity * price * 0.1

Rezultāts tiek saglabāts kā mainīgais Discount. VBA priekšraksts, kurā tiek glabāta vērtība mainīgajā, tiek dēvēts par uzdevuma priekšrakstu, jo tas novērtē izteiksmi vienādības zīmes labajā pusē un rezultātu piešķir mainīgā nosaukumam pa kreisi. Tā kā mainīgajam Discount ir tāds pats nosaukums kā funkcijas procedūrai, mainīgajā saglabātā vērtība tiek atgriezta darblapas formulā, kas tiek dēvēta par funkciju DISCOUNT.

Ja daudzums ir mazāks par 100, VBA izpilda šādu priekšrakstu:

Discount = 0

Visbeidzot šis priekšraksts noapaļo mainīgo Discount piešķirto vērtību līdz divām decimāldaļas vietām:

Discount = Application.Round(Discount, 2)

Vba nav round funkcijas, bet excel tā ir. Tāpēc, lai šajā priekšrakstā izmantotu ROUND, vba meklējiet metodi (funkciju) objektā Application (Excel). To var izdarīt, pievienojot vārdu Lietojumprogramma pirms vārda Round. Izmantojiet šo sintaksi ikreiz, kad vēlaties piekļūt Excel funkcijai no VBA moduļa.

Pielāgotai funkcijai ir jāsākas ar funkcijas priekšrakstu un jābeidzas ar priekšrakstu End Function. Papildus funkcijas nosaukumam funkcijas priekšraksts parasti norāda vienu vai vairākus argumentus. Tomēr varat izveidot funkciju bez argumentiem. Programmā Excel ir vairākas iebūvētas funkcijas , piemēram, RAND un NOW, kas neizmanto argumentus.

Pēc funkcijas priekšraksta funkcijas procedūrā ir viens vai vairāki VBA priekšraksti, kas pieņem lēmumus un veic aprēķinus, izmantojot funkcijai nodotos argumentus. Visbeidzot, kaut kur funkcijas procedūrā ir jāiekļauj priekšraksts, kas piešķir vērtību mainīgajam ar tādu pašu nosaukumu kā funkcijai. Šī vērtība tiek atgriezta formulā, kas izsauc funkciju.

VBA atslēgvārdu skaits, ko var izmantot pielāgotās funkcijās, ir mazāks nekā makro izmantojamais skaits. Pielāgotām funkcijām nav atļauts veikt neko citu kā vien atgriezt vērtību formulai darblapā vai izteiksmē, kas izmantota citā VBA makro vai funkcijā. Piemēram, pielāgotas funkcijas nevar mainīt logu izmērus, rediģēt formulu šūnā vai mainīt teksta fonta, krāsas vai raksta opcijas šūnā. Ja funkcijas procedūrā iekļaujat šāda veida "darbības" kodu, funkcija atgriež #VALUE! Ja norādītā pozīcija atrodas pirms lauka pirmā vienuma vai aiz lauka pēdējā vienuma, formula radīs kļūdu #REF!.

Viena darbība, ko funkcijas procedūra var veikt (izņemot aprēķinu veikšanu), ir parādīt dialoglodziņu. Varat izmantot priekšrakstu InputBox pielāgotā funkcijā kā veidu, kā iegūt ievadi no lietotāja, kas izpilda funkciju. MsgBox priekšrakstu var izmantot, lai lietotājam sniegtu informāciju. Varat arī izmantot pielāgotus dialoglodziņus vai objektus UserForms, bet tas nav šī ievada mērķis.

Pat vienkāršus makro un pielāgotas funkcijas var būt grūti lasāmas. Jūs varat padarīt tos vieglāk saprotamus, ierakstot paskaidrojuma tekstu komentāru veidā. Pievienojiet komentārus, pirms paskaidrojuma teksta pievienojot apostrofu. Piemēram, piemērā tālāk ir parādīta funkcija DISCOUNT ar komentāriem. Pievienojot līdzīgus komentārus, jums un citiem ir vieglāk saglabāt VBA kodu laika gaitā. Ja nākotnē būs jāveic koda izmaiņas, jums būs vieglāk saprast, ko sākotnēji paveicāt.

Apostrofs liek programmai Excel ignorēt visus pa labi no tās pašas rindiņas, lai varētu izveidot komentārus rindiņās atsevišķi vai pa labi no rindiņām, kurās ir VBA kods. Iespējams, sāksit relatīvi garu koda bloku ar komentāru, kas izskaidro kopējo mērķi un pēc tam izmanto iekļautos komentārus atsevišķu priekšrakstu dokumentam.

Vēl viens veids, kā dokumentēt makro un pielāgotās funkcijas, ir piešķirt tām aprakstošus nosaukumus. Piemēram, tā vietā, lai makro etiķetes nosauktu par MonthLabels , lai precīzāk raksturotu makro mērķi. Aprakstošu makro un pielāgotu funkciju nosaukumu izmantošana ir īpaši noderīga, ja esat izveidojis daudzas procedūras, īpaši, ja veidojat procedūras, kurām ir līdzīgi, bet nav identiski mērķi.

Veids, kā dokumentēt makro un pielāgotās funkcijas, ir personiskas izvēles nozīme. Svarīgi apiet kādu dokumentu veidu un to izmantot konsekventi.

Lai izmantotu pielāgotu funkciju, darbgrāmatai, kurā atrodas modulis, kurā izveidojāt funkciju, ir jābūt atvērtai. Ja darbgrāmata nav atvērta, saņemsiet #NAME? kļūdas ziņojums, mēģinot izmantot funkciju. Ja atsaucat uz funkciju citā darbgrāmatā, pirms funkcijas nosaukuma ir jānorāda tās darbgrāmatas nosaukums, kurā atrodas funkcija. Piemēram, ja izveidojat funkciju DISCOUNT darbgrāmatā ar nosaukumu Personal.xlsb un šo funkciju zvanāt no citas darbgrāmatas, ierakstiet =personal.xlsb!discount(), nevis vienkārši =discount().

Varat ietaupīt dažus taustiņsitienus (un iespējamās rakstīšanas kļūdas), atlasot pielāgotās funkcijas dialoglodziņā Funkcijas ievietošana. Jūsu pielāgotās funkcijas ir redzamas kategorijā Lietotāja definēts:

Vienkāršāks veids, kā pielāgotās funkcijas visu laiku padarīt pieejamas, ir to glabāšanu atsevišķā darbgrāmatā un pēc tam saglabāt šo darbgrāmatu kā pievienojumprogrammu. Pēc tam varat padarīt pievienojumprogrammu pieejamu ikreiz, kad palaižat programmu Excel. Lūk, kā to paveikt:

Kad šīs darbības ir izpildītas, jūsu pielāgotās funkcijas būs pieejamas katru reizi, kad palaižat Excel. Ja vēlaties pievienot funkciju bibliotēkai, atgriezieties Visual Basic redaktorā. Ja skatāt Visual Basic redaktora projektu pārlūku zem virsraksta VBA projekts, redzēsit moduli ar nosaukumu pēc pievienojumprogrammas faila. Pievienojumprogrammai būs paplašinājums .xlam.

Veicot dubultklikšķi uz šī moduļa projektu pētnieks, Visual Basic redaktors parāda jūsu funkcijas kodu. Lai pievienotu jaunu funkciju, novietojiet ievietošanas punktu aiz priekšraksta Beigu funkcija, kas pārtrauc pēdējo funkciju logā Kods, un sāciet rakstīt. Šādā veidā varat izveidot tik daudz funkciju, cik nepieciešams, un tās vienmēr būs pieejamas dialoglodziņa Funkcijas ievietošana kategorijā Lietotāja definēts.

Šo saturu sākotnēji autorēta Mark Dodžs un Andreja Stinsons kā daļa no viņu grāmatas Microsoft Office Excel 2007 Inside Out. Kopš tā atjaunināšanas tas ir lietots arī jaunākām Excel versijām.