Pielāgotu funkciju izveide programmā Excel

Lai Excel ir iekļauta daudz iebūvētu darblapas funkciju, pastāv iespēja, ka tai nav funkcijas katra veida veiktajiem aprēķiniem. Grupas izstrādātāji Excel iespējams paredzēt katra lietotāja aprēķinu vajadzības. Tā Excel sniegta iespēja izveidot pielāgotas funkcijas, kas izskaidrotas šajā rakstā.

Pielāgotas funkcijas, piemēram, makro, 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. T.i., tie sākas ar funkcijas priekšrakstu, nevis apakšatskaiti, un beidzas ar funkciju End, 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 redaktoru Visual Basic (VBE),kas tiek atvērts jaunā logā atsevišķi no 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 par vairāk nekā 100 vienībām. 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ā ir uzskaitīti katrs vienums, daudzums, cena, atlaide (ja tāda ir) un iegūtā kopējā cena.

Example order form without a custom function

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

  1. Nospiediet taustiņu kombināciju Alt+F11, Visual Basic redaktoru (Mac datorā nospiediet taustiņu kombināciju FN+ALT+F11) un pēc tam noklikšķiniet uz > moduli. Loga Redaktors labajā pusē tiek parādīts jauns Visual Basic 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
    

Piezīme.: Lai jūsu kods būtu lasāms, varat izmantot tabulēšanas taustiņu, lai izveidotu rindiņu atkāpes. Atkāpe ir attiecas tikai uz jūsu priekšrocībām un ir neobligāta, jo kods tiks izpildīts ar vai bez tās. Kad esat ierakstot rindiņu ar atkāpi, Visual Basic redaktors pieņem, ka nākamā rindiņa būs līdzīga atkāpei. Lai pārvietotos par vienu tabulēšanas rakstzīmi (tas ir, pa kreisi), nospiediet taustiņu kombināciju Shift+tabulēšanas taustiņš.

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

=DISCOUNT(D7,E7)

Excel aprēķina 10 procentu atlaidi 200 vienībām ar 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. Zvanot funkcijai 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.

Apdomāsim, kā Excel funkcijas darbību. Nospiežot taustiņu EnterExcel nosaukums DISCOUNT pašreizējā darbgrāmatā un VBA modulī atrod to kā pielāgotu funkciju. 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.

Example order form with a custom function

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 funkcija. Tāpēc, lai šajā priekšrakstā izmantotu ROUND, vba meklējiet metodi (funkciju) objektā Application (Programma) (programma Excel). To var izdarīt, pievienojot vārdu Lietojumprogramma pirms vārda Round. Izmantojiet šo sintaksi ikreiz, kad vēlaties Excel funkciju 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. 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, komentāros ierakstot paskaidrojuma tekstu. 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.

VBA funkcijas ar komentāriem piemērs

Apostrofs norāda Excel ignorēt visu, kas atrodas pa labi no tās pašas rindiņas, tāpēc varat 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ķetesnosauktu 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 AR nosaukumu 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:

Izteiksmes opcijas izvēle rūtī Grupēšana, kārtošana un kopsumma.

Vienkāršāks veids, kā pielāgotās funkcijas vienmēr 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 Excel. Lūk, kā to paveikt:

  1. Kad esat izveidojis funkcijas, noklikšķiniet uz Fails un > Saglabāt kā.

    Programmā Excel 2007 noklikšķiniet uz Microsoft Office pogasun pēc tam uz Saglabāt

  2. Dialoglodziņā Saglabāt kā atveriet nolaižamo sarakstu Saglabāt kā tipu un atlasiet Excel Pievienojumprogramma. Mapē Pievienojumprogrammas saglabājiet darbgrāmatu ar atpazīstamu nosaukumu, piemēram,Manas_ierīces. Dialoglodziņā Saglabāt kā tiks piedāvāts šo mapi, tāpēc jums ir tikai jāpieņem noklusējuma atrašanās vieta.

  3. Pēc darbgrāmatas saglabāšanas noklikšķiniet uz Fails un >Excel opcijas.

    Izvēlnē Excel 2007 noklikšķiniet uz pogas Microsoft Office unpēc tam uz Excel opcijas.

  4. Dialoglodziņā Excel opcijas noklikšķiniet uz kategorijas Pievienojumprogrammas.

  5. Nolaižamajā sarakstā Pārvaldīt atlasiet Visas Excel pievienojumprogrammas. Pēc tam noklikšķiniet uz pogas Aiziet!.

  6. Dialoglodziņā Pievienojumprogrammas atzīmējiet izvēles rūtiņu blakus nosaukumam, ko izmantojāt darbgrāmatas saglabāšanai, kā parādīts tālāk.

    add-ins dialog box

  1. Kad esat izveidojis funkcijas, noklikšķiniet uz Fails un > Saglabāt kā.

  2. Dialoglodziņā Saglabāt kā atveriet nolaižamo sarakstu Saglabāt kā tipu un atlasiet Excel Pievienojumprogramma. Saglabājiet darbgrāmatu ar atpazīstamu nosaukumu, piemēram, Manasvārdas.

  3. Pēc darbgrāmatas saglabāšanas noklikšķiniet uz Rīki > Excel pievienojumprogrammas.

  4. Dialoglodziņā Pievienojumprogrammas atlasiet pogu Pārlūkot, lai atrastu pievienojumprogrammu, noklikšķiniet uz Atvērt unpēc tam atzīmējiet izvēles rūtiņu Add-In lodziņā Pieejamās pievienojumprogrammas.

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

Faila atvēršana ekskluzīvā režīmā

Veicot dubultklikšķi uz šī moduļa pārlūka Project, Visual Basic redaktors parādīs 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 Kregu Stinsons kā daļa no viņu grāmatas Microsoft Office Excel 2007 Inside Out. Kopš atjaunināšanas tā ir atjaunināta, lai to lietotu arī Excel jaunākām versijām.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×