Izveidot pielāgotas funkcijas 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ā.

Lai gan programmā Excel ir ietverti daudzu iebūvēto darblapas funkcijām, iespējas ir tas nav funkcija katra veida aprēķinu, ko jūs veicat. Excel noformētāju nebija iespējams paredzēt katram lietotājam aprēķinu vajadzībām. Tā vietā, programma Excel piedāvā iespēju izveidot pielāgotas funkcijas, kas izskaidrotas šajā rakstā.

Pielāgotas funkcijas, piemēram, makro, izmantojiet programmēšanas valodu Visual Basic for Applications (VBA) . Tās atšķiras no makro ievērojami divējādi. Vispirms funkcija procedūras izmantojiet nevis Sub procedūrām. Tas ir, viņi sāk ar funkciju paziņojumu, nevis paziņojums par Sub un end ar End Function , nevis End Sub. Otrkārt, viņi veiktu aprēķinus, nevis darbību veikšanas. Noteikta veida pārskatos, piemēram, paziņojumus, ka atlasiet un formatējiet diapazonus, tiek izslēgti no pielāgotas funkcijas. Šajā rakstā uzzināsit, kā izveidot un lietot pielāgotas funkcijas. Lai izveidotu funkcijas un makro, jūs strādājat ar Visual Basic redaktoru (VBE), kas tiek atvērts jaunā logā atsevišķi no Excel.

Pieņemsim, ka jūsu uzņēmums piedāvā daudzums atlaide 10 procenti par produktiem, ja pasūtījumu pārdošana ir vairāk nekā 100 vienības. Šādus punktus, turpmāk ir sniegts funkciju, lai aprēķinātu šo atlaidi.

Tālāk piemērā parādīta pasūtījuma veidlapa, kurā uzskaitītas katra vienuma, daudzumu, cenu, atlaides (ja tādas ir), un rezultātā kopējā cena.

Piemērā pasūtījuma veidlapa bez pielāgoto funkciju

Lai izveidotu pielāgotu diskonta funkcija šajā darbgrāmatā, rīkojieties šādi:

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

  2. Nokopējiet un ielīmējiet šādu kodu, lai jauno moduli.

    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 būtu vieglāk salasāms, varat izmantot taustiņu Tab , lai rindas atkāpi. Atkāpes ir tikai jūsu labā un nav obligāts, jo kods darbosies ar vai bez tā. Pēc tam, kad rakstāt rindā atkāpi, Visual Basic redaktora tiek pieņemts, nākamajā rindā ir līdzīgi atkāpi. Lai pārvietotos (t.i., pa kreisi) viens tabulēšanas rakstzīmi, nospiediet taustiņu Kombināciju Shift + Tab.

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

=DISCOUNT(D7,E7)

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

VBA kods, funkcija DISCOUNT(quantity, price) pirmajā rindā norādīto funkcijā atlaide ir jāizmanto divi argumenti, daudzuma un cenas. Kad zvanīsit funkcijas darblapas šūnā, ir jābūt iekļautiem šos divus argumentus. Formulu = DISCOUNT(D7,E7), D7 daudzums arguments ir, bet E7 argumenta cenu . Tagad varat kopēt formulu diskonta G8:G13, lai iegūtu rezultātu, kas redzams tālāk.

Apskatīsim, kā Excel uzskata, šī procedūra Function. Nospiežot taustiņu Enter, programma Excel meklē pašreizējā darbgrāmatā atlaide nosaukumu un konstatē, ka tas ir pielāgota funkcija VBA modulī. Argumentu nosaukumus, ietverta iekavās daudzuma un cenas, ir vērtības, kas atlaide aprēķinu pamatā vietturi.

Piemērā pasūtījuma veidlapa ar pielāgotu funkciju

Ja priekšrakstā šādu kodu bloku noskaidro daudzumu argumentu 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 paziņojumu, kas daudzums vērtību reizina ar cenu 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 atlaide. VBA paziņojumu, kas saglabā vērtību kā mainīgo tiek dēvēta par uzdevuma paziņojums, jo tas novērtē izteiksmi labajā pusē ar vienādības zīmi un piešķir rezultāts mainīgā nosaukuma kreisajā pusē. Jo atlaide mainīgais ir tāds pats nosaukums kā funkcijas procedūru, saglabāto mainīgo vērtību tiek atgriezts darblapas formula, kas sauc par funkciju atlaide.

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

Discount = 0

Visbeidzot, šis priekšraksts noapaļo vērtība, kas piešķirta atlaide mainīgo ar divām decimāldaļas vietām:

Discount = Application.Round(Discount, 2)

VBA ir bez funkcija ROUND, taču programma Excel. Tāpēc, lai izmantotu šo paziņojumu ROUND, varat norādīt VBA meklējiet Round metodi (funkcija) programmas objektu (Excel). To var paveikt, pievienojot vārdu lietojumprogrammas pirms vārda Round. Ikreiz, kad tas ir nepieciešams, lai piekļūtu Excel funkciju no VBA modulī, izmantojiet šo sintaksi.

Pielāgotā funkcijā jābūt ar priekšrakstu funkcija sākuma un beigu ar priekšrakstu End Function. Papildus funkcijas nosaukums, funkcija priekšrakstā parasti norāda viens vai vairāki argumenti. Funkcijas var tomēr izveidot bez argumentiem. Excel ietver vairākas iebūvētas funkcijas — RAND un tagad, piemēram, kas neizmanto argumenti.

Pēc priekšrakstu Function, funkcija procedūrā ietilpst viens vai vairāki VBA priekšrakstu, kas lēmumu un veikt aprēķinus, izmantojot pagājis funkcijas argumenti. Visbeidzot, kaut kur funkcija procedūrā ir jāiekļauj paziņojums par to, ka piešķir vērtību kā mainīgo ar tādu pašu nosaukumu kā funkcijas. Šī vērtība tiek atgriezts formula, kas aicina funkcija.

VBA atslēgvārdu, varat izmantot pielāgotas funkcijas skaits ir mazāks par skaitu var izmantot makro. Pielāgotas funkcijas nav atļauts darīt kaut ko, nevis atgrieztā vērtība formulas darblapā, vai izteiksme, kas izmantota citā VBA makro vai funkcija. Piemēram, pielāgotas funkcijas nevar lieluma maiņa windows, rediģējiet formulu šūnā vai mainītu fontu, krāsu vai rakstu opcijas tekstu šūnā. Ja iekļaujat "darbību" kodu šāda veida procedūra Function, funkcija atgriež #VALUE! kļūda.

Viena darbība funkcija procedūru var veikt (izņemot veikt aprēķinus) ir redzams darbinieka detalizētās informācijas skats. Pielāgota funkcija InputBox paziņojumu varat izmantot kā līdzeklis darba ievades no lietotāja, izpildot funkciju. Kā līdzeklis pievades lietotājam informāciju var izmantot MsgBox priekšrakstu. Varat arī izmantot pielāgotus dialoglodziņus vai UserForms, bet tas ir iekļauta šajā iepazīšanās tēmu.

Pat vienkāršu makro un pielāgotas funkcijas var būt grūti izlasīt. Varat padarīt tos vieglāk saprotams, ierakstot skaidrojošu tekstu formā komentārus. Var pievienot komentārus, pirms paskaidrojuma tekstu ar apostrofs. Piemēram, tālāk sniegtajā piemērā ir redzama funkcijas atlaide ar komentāriem. Pievienot komentārus, kā šīs atvieglo jūs vai citi, lai uzturētu savu VBA kodu kā laika gaitā. Ja vēlaties veikt izmaiņas kodu nākotnē, jums būs vieglāk laika informācija par to, ko jūs sākotnēji.

VBA funkcijas ar komentāriem piemērs

Apostrofs programmai Excel norāda, ignorēt visu pa labi tajā pašā rindā, lai varētu izveidot komentārus vai nu rindās atsevišķi vai labajā pusē rindas, kas satur VBA kodu. Varētu sākt salīdzinoši gari bloka koda ar komentāru, kas izskaidro tās vispārējo mērķi un pēc tam izmantojiet iekļautos komentārus dokumentu atsevišķu pārskatu.

Vēl viens veids, kā dokumentu, makro un pielāgotas funkcijas ir piešķirt tiem aprakstošu nosaukumu. Piemēram, nevis makro etiķetesnosaukums, jūs varētu nosaukums tam MonthLabels aprakstiet precīzāk makro kalpo nolūku. Izmantojot aprakstošu nosaukumu makro un pielāgotas funkcijas ir īpaši noderīgi, ja esat izveidojis daudzas procedūras, īpaši, ja veidojat procedūras, kas ir līdzīga, bet ne identiskām mērķiem.

Kā dokumentu, makro un pielāgotas funkcijas, ir atkarīgs no vēlmēm. Svarīgi ir pieņemt kādu metodi dokumentāciju un izmantot konsekventi.

Lai izmantotu pielāgotu funkciju, ir jābūt atvērtai darbgrāmatu, kurā atrodas modulis, kurā izveidojāt funkcijas. Ja šī darbgrāmata nav atvērta, tiek parādīta #NAME? kļūda, mēģinot izmantot funkciju. Ja veidojat atsauci funkcijā citā darbgrāmatā, jābūt pirms funkcijas nosaukuma ar darbgrāmatu, kurā atrodas funkcijas nosaukumu. Piemēram, ja veidojat funkciju sauc diskonta sauc par Personal. xlsb darbgrāmatā, un šī funkcija call no citas darbgrāmatas, ir jāieraksta =personal.xlsb!discount(), nevis vienkārši =discount().

Jūs varat saglabāt sevi daži taustiņsitienu (un iespējamās pareizrakstības kļūdas), atlasot pielāgotas funkcijas no dialoglodziņa funkcijas ievietošana. Pielāgotas funkcijas tiek rādītas kategorijā lietotāja definētas:

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

Vieglāk veids, kā padarīt pielāgotas funkcijas pieejams vietnē visu laiku, ir tos saglabāt citā darbgrāmatā un pēc tam saglabājiet darbgrāmatu kā pievienojumprogrammu. Jūs varat tam pieejamus pievienojumprogrammu ikreiz, kad palaižat Excel. Lūk, kā to paveikt:

  1. Kad esat izveidojis funkcijas, kas jums ir nepieciešams, noklikšķiniet uz Fails > Saglabāt kā.

    Excel 2007, noklikšķiniet uz Microsoft Office pogasun noklikšķiniet uz Saglabāt kā

  2. Dialoglodziņā Saglabāt kā atveriet nolaižamajā sarakstā Saglabāt kā tipu un atlasiet Excel pievienojumprogrammas. Saglabājiet darbgrāmatu sadaļā atpazīstamu nosaukumu, piemēram, MyFunctions, mapē pievienojumprogrammas . Dialoglodziņš Saglabāt kā piedāvās šajā mapē, tāpēc jums ir tikai pieņemiet noklusējuma atrašanās vietu.

  3. Kad esat saglabājis darbgrāmatu, noklikšķiniet uz Fails > Excel opcijas.

    Excel 2007, noklikšķiniet uz Microsoft Office pogasun noklikšķiniet 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. Kad esat izveidojis funkcijas, kas jums ir nepieciešams, noklikšķiniet uz Fails > Saglabāt kā.

  2. Dialoglodziņā Saglabāt kā atveriet nolaižamajā sarakstā Saglabāt kā tipu un atlasiet Excel pievienojumprogrammas. Saglabājiet darbgrāmatu sadaļā atpazīstamu nosaukumu, piemēram, MyFunctions.

  3. Kad esat saglabājis darbgrāmatu, noklikšķiniet uz Rīki > Excel pievienojumprogrammas.

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

Kad šīs darbības būs paveiktas, pielāgotas funkcijas būs pieejams ikreiz, kad palaižat Excel. Ja vēlaties pievienot funkciju bibliotēka, atpakaļ uz Visual Basic Editor. Ja jūs meklējiet Visual Basic redaktora projekta pētnieks VBAProject pozīcijā, redzēsit modulis, kas nosaukta pievienojumprogrammas failu. Pievienojumprogrammu būs paplašinājumu. xlam.

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

Veicot dubultklikšķi uz Project Explorer modulī liek Visual Basic redaktoru, lai parādītu funkcija kodu. Lai pievienotu jaunu funkciju, novietojiet ievietošanas punktu pēc priekšrakstu End Function beidzas pēdējā funkcija koda logā un sāciet rakstīt. Varat izveidot daudzas funkcijas kā jums šādā veidā, un tās vienmēr būtu pieejamas dialoglodziņa Funkcijas ievietošana kategorijā lietotāja definēts.

Šī satura sākotnēji izveidojusi zīme Dodge un Craig Stinson kā daļa no Microsoft Office Excel 2007 Inside Outto grāmatu. Jo tā ir atjaunināts piemērojamo jaunākas programmas Excel versijas arī.

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.

×