Pielāgotu funkciju izveide programmā Excel

Piezīme.: Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā.

Kaut arī programmā Excel ir iekļautas daudzas iebūvētas darblapas funkcijas, iespējams, tā nav funkcija katram veicamajam aprēķina veidam. Excel izstrādātāji nevarēja paredzēt katra lietotāja aprēķinu vajadzības. Tā vietā programma Excel piedāvā iespēju izveidot pielāgotas funkcijas, kas ir izskaidrotas šajā rakstā.

Vai meklējat informāciju par to, kā izveidot JavaScript pielāgoto funkciju, ko varat izmantot programmā Excel darbam ar Windows, Excel darbam ar Mac vai Excel Online? Ja tā ir, skatiet rakstu Excel pielāgoto funkciju pārskata.

Pielāgotas funkcijas, piemēram, makro, izmantojiet Visual Basic for Applications (VBA) programmēšanas valodu. Tie atšķiras no makro divos nozīmīgos veidos. Vispirms apakšprocedūru vietā tiek lietotas funkciju procedūras . Tas nozīmē, ka tiek sākts ar funkciju priekšrakstu, nevis priekšrakstu sub un end ar funkciju end sub. Otrkārt, tās 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ājat ar Visual Basic redaktoru (VBE), kas tiek atvērts jaunā logā atsevišķi no programmas Excel.

Pieņemsim, ka jūsu uzņēmums produkta tirdzniecībā piedāvās 10 procentu atlaidi, ja šis pasūtījums ir paredzēts vairāk nekā 100 vienībām. Nākamajās rindkopās tiks demonstrēta funkcija šīs atlaides aprēķināšanai.

Tālāk sniegtajā piemērā ir redzama pasūtījuma veidlapa, kurā ir uzskaitīti visi vienumi, daudzums, cena, atlaide (ja tāda ir) un iegūtā cena.

Pasūtījuma veidlapas piemērs, neizmantojot pielāgotu funkciju

Lai izveidotu pielāgotu ATLAIŽU funkciju šajā darbgrāmatā, veiciet tālāk norādītās darbības.

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

  2. Kopējiet un ielīmējiet tālāk norādīto kodu 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 kodu padarītu vieglāk lasāmu, varat izmantot tabulēšanas taustiņu, lai izveidotu rindiņu atkāpes. Atkāpe attiecas tikai uz jūsu priekšrocību, un tas nav obligāts, jo kods tiks izpildīts ar vai bez tā. Pēc tam, kad esat ierakstījis līniju ar atkāpi, Visual Basic redaktors pieņem, ka nākamā rindiņa būs vienādi ar atkāpi. Lai pārvietotos (tas ir, pa kreisi) vienu tabulēšanas rakstzīmi, nospiediet taustiņu kombināciju SHIFT + tabulēšanastaustiņš.

Tagad esat gatavs izmantot jauno ATLAIŽU funkciju. Aizvērtu Visual Basic redaktoru, atlasiet šūnu G7 un ierakstiet tālāk norādīto informāciju.

= DISCOUNT (D7, E7)

Programma Excel aprēķina 10 procentu atlaidi 200 vienībās $47,50 uz vienību un atgriež $950,00.

VBA koda pirmajā rindiņā funkcija disCOUNT (daudzums, cena) jūs norādījusi, ka funkcija disCOUNT prasa divus argumentus, daudzumu un cenu. Kad zvanāt uz funkciju darblapas šūnā, ir jāiekļauj šie divi argumenti. Formulā = atlaide (D7, E7), D7 ir arguments Quantity un šūnā E7 ir cenas arguments. Tagad varat kopēt ATLAIŽU formulu G8: G13, lai iegūtu tālāk redzamos rezultātus.

Aplūkosim, kā Excel interpretē šo funkciju procedūru. Kad nospiežat taustiņu Enter, programma Excel meklē nosaukumu atlaidi pašreizējā darbgrāmatā un konstatē, ka tā ir pielāgota funkcija VBA modulī. Iekavās iekļautie argumentu nosaukumi, daudzums un Cenair vietturi vērtībām, uz kurām ir pamatots atlaides aprēķins.

PieMēra pasūtījuma forma ar pielāgotu funkciju

Ja priekšraksts tālāk esošajā koda blokā pārbauda daudzuma argumentu un nosaka, vai pārdoto vienumu skaits ir lielāks vai vienāds ar 100:

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

Ja pārdoto vienumu skaits ir lielāks par vai vienāds ar 100, VBA izpilda tālāk norādīto 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īgā atlaide. VBA priekšraksts, kurā tiek glabāta vērtība mainīgajā, tiek dēvēts par uzdevuma priekšrakstu, jo tas novērtē izteiksmes labo pusi no vienādības zīme un piešķir rezultātu mainīgajam nosaukumam pa kreisi. Tā kā mainīgajām atLaidēm ir tāds pats nosaukums kā funkcijas procedūrai, mainīgajā iekļautā vērtība tiek atgriezta darblapas formulā, kas tiek dēvēta par ATLAIŽU funkciju.

Ja daudzums ir mazāks par 100, VBA izpilda šādu paziņojumu:

Atlaide = 0

Visbeidzot tālāk esošajā priekšrakstā tiek noapaļota atlaides mainīgajai vērtībai piešķirtā vērtība divas decimāldaļas vietas:

Atlaide = Application. Round (atlaide, 2)

VBA nav funkcija ROUND, bet Excel to dara. Tāpēc, lai izmantotu šo priekšrakstu, pavēstiet VBA, lai programmas objektā (Excel) atrastu apaļo metodi (funkciju). To var izdarīt, pievienojot vārdu lietojumprogrammu pirms vārda Round. Izmantojiet šo sintaksi ikreiz, kad ir jāpiekļūst Excel funkcijai no VBA moduļa.

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

Sekojot priekšrakstam Function, funkcija Procedure ietver vienu vai vairākus VBA priekšrakstus, kas pieņem lēmumus un veic aprēķinus, izmantojot funkcijai nodotos argumentus. Visbeidzot, kaut arī funkcijā Function, 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.

To VBA atslēgvārdu skaits, ko var izmantot pielāgotās funkcijās, ir mazāks par skaitli, ko var izmantot makro. Pielāgotām funkcijām nav atļauts veikt citas darbības, kas nav formulas atgriešana darblapā vai citā VBA makro vai funkcijā lietotā izteiksme. Piemēram, pielāgotas funkcijas nevar mainīt logu izmērus, rediģēt formulu šūnā vai mainīt fonta, krāsu vai raksta opcijas šūnā esošo tekstu. Ja funkcijas procedūrā iekļaujat šī veida kodu "darbība", funkcija atgriež #VALUE! kļūdu.

Vienu darbību procedūras funkcija var veikt (neatkarīgi no aprēķinu veikšanas) tiek parādīts dialoglodziņš. InputBox priekšrakstu var izmantot pielāgotā funkcijā, lai iegūtu ievadi no lietotāja, kas izpilda funkciju. Priekšrakstu MsgBox varat izmantot kā informāciju, ar kuru lietotājam tiek nodota informācija. Varat arī izmantot pielāgotus dialoglodziņus vai UserForms, bet tas ir tēma ārpus šī ievada jomas.

Var būt grūti lasīt pat vienkāršus makro un pielāgotas funkcijas. Jūs varat padarīt tās saprotamākas, ierakstot skaidrojošu tekstu komentāru formā. Komentārus pievieno pirms skaidrojošā teksta ar apostrofu. Piemēram, tālāk sniegtajā piemērā ir redzama funkcija disCOUNT ar komentāriem. Komentāru pievienošana, piemēram, ar tiem, atvieglo jūsu un citu lietotāju iespējas saglabāt savu VBA kodu kā laiku. Ja vēlaties nākotnē veikt izmaiņas kodā, jums būs vieglāk saprast to, ko darījāt sākotnēji.

VBA funkcijas piemērs ar komentāriem

Apostrofs liek programmai Excel ignorēt visu, kas atrodas tieši tajā pašā rindiņā, lai jūs varētu izveidot komentārus pa rindiņām vai pa labi no līnijām, kas satur VBA kodu. Varat sākt samērā garu koda bloku ar komentāru, kurā izskaidrots tā vispārīgais nolūks un pēc tam izmantot iekļautos komentārus dokumentu atsevišķos priekšrakstos.

Vēl viens veids, kā dokumentēt savus makro un pielāgotas funkcijas, ir piešķirt tiem aprakstošus nosaukumus. Piemēram, tā vietā, lai nosauktu makro etiķetes, varat nosaukt to MonthLabels , lai precīzāk aprakstītu makro kalpošanas nolūku. Makro un pielāgoto funkciju aprakstošo nosaukumu izmantošana ir īpaši noderīga, ja esat izveidojis daudzas procedūras, it īpaši, ja izveidojat procedūras, kurām ir vienādi, bet ne identiski nolūki.

Kā dokumentēt savus makro un pielāgotās funkcijas, ir personiskas preferences jautājums. Svarīgi ir pieņemt dažas dokumentācijas metodes un to izmantot konsekventi.

Lai izmantotu pielāgotu funkciju, darbgrāmatai, kurā ir modulis, kurā esat izveidojis funkciju, ir jābūt atvērtai. Ja šī darbgrāmata nav atvērta, tiek parādīts #NAME? mēģinot izmantot funkciju, tiek parādīts kļūdas ziņojums. Ja atsauce uz funkciju ir citā darbgrāmatā, jāatrodas pirms funkcijas nosaukuma ar tās darbgrāmatas nosaukumu, kurā šī funkcija atrodas. Piemēram, ja izveidojat funkciju ar nosaukumu atlaide darbgrāmatā ar nosaukumu Personal. xlsb un zvanāt uz šo funkciju no citas darbgrāmatas, ir jāieraksta = Personal. xlsb! diskonts (), nevis vienkārši = atlaide ().

Varat saglabāt pats savus taustiņsitienus (un iespējamās rakstīšanas kļūdas), atlasot savas pielāgotās funkcijas no dialoglodziņa funkcijas ievietošana. Jūsu pielāgotās funkcijas tiek rādītas lietotāja definētajā kategorijā:

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

Vienkāršāks veids, kā pielāgot savas pielāgotās funkcijas vienmēr, ir tās glabāt atsevišķā darbgrāmatā un pēc tam saglabāt šo darbgrāmatu kā pievienojumprogrammu. Pēc tam pievienojumprogrammu var padarīt pieejamu, palaižot programmu Excel. Tālāk ir aprakstīts, kā to paveikt.

  1. Pēc nepieciešamo funkciju izveides noklikšķiniet uz Fails _GT_ Saglabāt kā.

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

  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īstamiem nosaukumiem, piemēram, MyFunctions. Dialoglodziņš Saglabāt kā piedāvās šo mapi, lai viss, kas jums ir jādara, ir akceptēt noklusējuma atrašanās vietu.

  3. Pēc darbgrāmatas saglabāšanas noklikšķiniet uz failu _GT_ Excel opcijas.

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

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

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

  6. Dialoglodziņā pievienojumprogrammas atzīmējiet izvēles rūtiņu blakus vārdam, ko izmantojāt, lai saglabātu darbgrāmatu, kā parādīts tālāk.

    add-ins dialog box

  1. Pēc nepieciešamo funkciju izveides noklikšķiniet uz Fails _GT_ 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īstamiem nosaukumiem, piemēram, MyFunctions.

  3. Kad darbgrāmata ir saglabāta, noklikšķiniet uz Rīki , _GT_ Excel pievienojumprogrammas.

  4. Dialoglodziņā pievienojumprogrammas atlasiet pogu Pārlūkot, lai atrastu savu pievienojumprogrammu, noklikšķiniet uz Atvērtun pēc tam atzīmējiet izvēles rūtiņu blakus pievienojumprogrammai pievienojumprogrammā pieejamās pievienojumprogrammas.

Pēc šo darbību veikšanas jūsu pielāgotās funkcijas būs pieejamas ikreiz, kad palaižat programmu Excel. Ja vēlaties pievienot savai funkciju bibliotēkai, atgriezieties Visual Basic redaktorā. Ja izskatīsies Visual Basic redaktora projekta pētnieks zem VBAProject virsraksta, redzēsit moduli, kas nosaukts 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 projektu pārlūkā, Visual Basic redaktors rāda funkcijas kodu. Lai pievienotu jaunu funkciju, novietojiet ievietošanas punktu aiz priekšraksta end funkcijas, kas pārtrauc pēdējo funkciju koda logā, un sāciet rakstīt. Šādā veidā varat izveidot tik daudz funkciju, un tās vienmēr būs pieejamas dialoglodziņa Funkcijas ievietošana kategorijā lietotāja definēts.

Šo saturu sākotnēji izveidoja ar Mark Dodge un Craig Stinson kā daļu no savas grāmatas Microsoft Office Excel 2007 iekšpuses. Pēc tam tā ir atjaunināta, lai to lietotu arī jaunākām Excel 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.

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 varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×