Pielāgoto funkciju izveide programmā Excel

Lai gan programmā Excel ir ietverts plašs iebūvēto darblapas funkciju klāsts, iespējams, tajā nav funkcijas katram jūsu veiktajam aprēķinam. Excel izstrādātāji nevarēja paredzēt katra lietotāja aprēķinu vajadzības. Tā vietā programma Excel nodrošina iespēju izveidot pielāgotas funkcijas, kas ir detalizēti izskaidrotas šajā rakstā.

Vienkāršas pielāgotas funkcijas izveide

Pielāgotas funkcijas, piemēram, makro, izmanto programmēšanas valodu Visual Basic for Applications (VBA). Tie atšķiras no makro divos būtiskos veidos. Pirmkārt, viņi izmanto funkciju procedūras, nevis apakšprocedūras . Proti, tās sākas ar priekšrakstu Function , nevis Sub , un beidzas ar Function End , nevis End Sub. Otrkārt, viņi veic aprēķinus, nevis veic darbības. Noteikta veida priekšraksti, piemēram, priekšraksti, kas atlasa un formatē diapazonus, ir izslēgti no pielāgotām funkcijām. Šajā rakstā uzzināsit, kā izveidot un lietot pielāgotas funkcijas. Lai izveidotu funkcijas un makro, ir jāizmanto Visual Basic redaktors (VBE), kas tiek atvērts jaunā logā atsevišķi no Excel.

Pieņemsim, ka jūsu uzņēmums piedāvā daudzuma atlaidi 10 procentu apmērā preces pārdošanai, ja pasūtījumā ir vairāk par 100 vienībām. Nākamajās rindkopās demonstrēsim funkciju šīs atlaides aprēķināšanai.

Tālāk sniegtajā piemērā ir parādīta pasūtījuma forma, kurā ir norādīts katrs vienums, daudzums, cena, atlaide (ja tāda ir) un no tās izrietošā paplašinātā 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 Ievietot>moduli. Visual Basic redaktora labajā pusē tiek parādīts jauna moduļa logs.

2. Nokopē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
   
   

Pielāgoto funkciju izmantošana

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

=DISCOUNT(D7,E7)

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

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

Apskatīsim, kā Excel interpretē šo funkcijas procedūru. Nospiežot taustiņu Enter, programma Excel pašreizējā darbgrāmatā meklē nosaukumu DISCOUNT un atrod, ka tā ir pielāgota funkcija VBA modulī. Iekavās iekļautie argumentu nosaukumi, Daudzums un Cena ir to vērtību vietturi, kas tiek izmantoti atlaides aprēķināšanas pamatā.

Tālāk norādītajā koda blokā esošais priekšraksts If pārbauda argumentu Daudzums un nosaka, vai pārdoto vienību 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 vai vienāds ar 100, VBA izpilda šādu priekšrakstu, kurā daudzuma vērtība tiek reizināta ar cenas vērtību un pēc tam iegūtais rezultāts tiek reizināts ar 0,1:

Discount = quantity * price * 0.1

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

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

Discount = 0

Visbeidzot šis priekšraksts noapaļo mainīgajam diskonts piešķirto vērtību līdz divām cipariem aiz komata:

Discount = Application.Round(Discount, 2)

VBA nav funkcijas ROUND, bet programmā Excel ir. Tāpēc, lai šajā priekšrakstā izmantotu ROUND, jānorāda VBA meklēt ROUND metodi (funkciju) lietojumprogrammas objektā (Excel). To var izdarīt, pievienojot vārdu Lietojumprogramma pirms vārda Round. Izmantojiet šo sintaksi, ja nepieciešams piekļūt Excel funkcijai no VBA moduļa.

Izpratne par pielāgotu funkciju kārtulām

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

Pēc priekšraksta Function 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 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 izmantošana pielāgotās funkcijās

VBA atslēgvārdu skaits, ko var izmantot pielāgotās funkcijās, ir mazāks nekā makro. Pielāgotām funkcijām ir atļauts veikt tikai vērtības atgriešanu darblapā esošai formulai vai citā VBA makro vai funkcijā izmantotai izteiksmei. Piemēram, pielāgotās funkcijas nevar mainīt logu izmērus, rediģēt formulu šūnā vai mainīt šūnas teksta fonta, krāsas vai raksta opcijas. Ja funkcijas procedūrā iekļaujat šādu kodu "darbība", 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!.

Vienīgā darbība, ko var veikt funkcijas procedūra (neskaitot aprēķinu veikšanu), ir parādīt dialoglodziņu. Priekšrakstu InputBox var izmantot pielāgotā funkcijā, lai saņemtu ievadi no lietotāja, kas izpilda funkciju. Varat izmantot MsgBox priekšrakstu kā informācijas nodošanas līdzekli lietotājam. Varat arī izmantot pielāgotus dialoglodziņus vai lietotāja formas, bet šī tēma šajā ievadā nav aplūkota.

Makro un pielāgotu funkciju dokumentēšana

Pat vienkāršus makro un pielāgotas funkcijas var būt grūti nolasīt. Jūs varat tos padarīt vieglāk saprotamus, ierakstot paskaidrojošu tekstu komentāru veidā. Komentārus varat pievienot, pirms paskaidrojošā teksta ievietojot apostrofu. Piemēram, tālāk sniegtajā piemērā redzama funkcija DISCOUNT ar komentāriem. Pievienojot šādus komentārus, jums vai citām personām ir vieglāk uzturēt VBA kodu laika gaitā. Ja nākotnē būs jāveic izmaiņas kodā, būs vieglāk saprast, ko darījāt sākotnēji.

Apostrofs norāda programmai Excel ignorēt visu tās pašas rindiņas pa labi, tāpēc varat izveidot komentārus vai nu atsevišķās rindiņās, vai to rindiņu labajā pusē, kurās ir VBA kods. Varat sākt salīdzinoši garu koda bloku ar komentāru, kas izskaidro tā vispārējo mērķi, un pēc tam izmantot iekļautos komentārus, lai dokumentētu atsevišķus priekšrakstus.

Cits veids, kā dokumentēt makro un pielāgotās funkcijas, ir piešķirt tiem aprakstošus nosaukumus. Piemēram, tā vietā, lai makro piešķirtu nosaukumu Etiķetes, varat to nosaukt MonthLabels , lai precīzāk aprakstītu makro uzdevumu. Aprakstošos nosaukumus makro un pielāgotām funkcijām ir īpaši noderīgi, ja esat izveidojis daudzas procedūras, īpaši, ja izveidojat procedūras ar līdzīgiem, bet ne identiskiem mērķiem.

Tas, kā dokumentējat savus makro un pielāgotās funkcijas, ir personīgās izvēles jautājums. Svarīgi ir pieņemt kādu dokumentācijas metodi un izmantot to konsekventi.

Pielāgoto funkciju padarīšana par pieejamām jebkurā vietā

Lai izmantotu pielāgotu funkciju, jābūt atvērtai darbgrāmatai, kurā ir modulis, kurā funkcija tika izveidota. Ja šī darbgrāmata nav atvērta, vai tiek parādīts #NAME? kļūdu, mēģinot lietot funkciju. Ja veidojat atsauci uz funkciju citā darbgrāmatā, funkcijas nosaukuma sākumā jāievada tās darbgrāmatas nosaukums, kurā atrodas funkcija. Piemēram, ja darbgrāmatā Personal.xlsb izveidojat funkciju DISCOUNT un izsaucat šo funkciju no citas darbgrāmatas, ir jāieraksta =personal.xlsb!discount(), nevis vienkārši =Discount().

Atlasot pielāgotās funkcijas dialoglodziņā Funkcijas ievietošana, varat ietaupīt dažus taustiņsitienus (un iespējamās drukas kļūdas). Jūsu pielāgotās funkcijas parādās lietotāja definētajā kategorijā:

Vienkāršāks veids, kā pielāgotās funkcijas padarīt pieejamas vienmēr, ir saglabāt tās atsevišķā darbgrāmatā un pēc tam saglabāt šo darbgrāmatu kā pievienojumprogrammu. Pēc tam pievienojumprogrammu var padarīt pieejamu ikreiz, kad palaižat programmu Excel. Lai to paveiktu, rīkojieties šādi.

Kad šīs darbības ir paveiktas, pielāgotās funkcijas būs pieejamas ikreiz, kad palaidīsit programmu Excel. Ja vēlaties pievienot savai funkciju bibliotēkai, atgriezieties Visual Basic redaktorā. Ja Visual Basic redaktora projekta pētniekā skatīsit zem virsraksta VBAProject, redzēsit moduli, kas nosaukts jūsu pievienojumprogrammas faila vārdā. Jūsu pievienojumprogrammas paplašinājums būs .xlam.

režīmā Veicot dubultklikšķi uz šī moduļa projekta pētniekā, Visual Basic redaktors parāda funkcijas kodu. Lai pievienotu jaunu funkciju, novietojiet ievietošanas punktu aiz priekšraksta Funkcija End, kas logā Kods izbeidz pēdējo funkciju, 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.

Par autoriem

Šī satura autori sākotnēji ir Marks Dodžs un Kreigs Stinsons kā daļu no savas grāmatas Microsoft Office Excel 2007: Inside Out. Kopš tā laika tā ir atjaunināta, lai lietotu arī jaunākas Excel versijas.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistam Excel tehnoloģiju kopienā vai saņemt atbalstu kopienās.