Pasirinktinių funkcijų kūrimas Excel

Nors Excel apima daugybę įtaisytųjų darbalapio funkcijų, tikėtina, kad ji neturi funkcijos kiekvienam atliekamo skaičiavimo tipui. "Excel" dizaineriai negalėjo numatyti kiekvieno vartotojo skaičiavimo poreikių. Vietoj to Excel suteikia galimybę kurti pasirinktines funkcijas, kurios paaiškintos šiame straipsnyje.

Pasirinktinės funkcijos, pvz., makrokomandos, Visual Basic programų (VBA) programavimo kalbą. Jos skiriasi nuo makrokomandų dviem svarbiais būdais. Pirma, jie naudoja funkcijas, o ne sub procedūras. Tai reiškia, kad jie prasideda funkcijos sakinį, o ne antriniu sakinį ir baigiasi pabaigos funkcija, o ne pabaigos antriniu. Antra, jie atlieka skaičiavimus, o ne atlieka veiksmus. Tam tikrų tipų sakiniai, pvz., sakiniai, kurie pasirenka ir formatuoja diapazonus, neįtraukiami į pasirinktines funkcijas. Šiame straipsnyje sužinosite, kaip kurti ir naudoti pasirinktines funkcijas. Norėdami kurti funkcijas ir makrokomandas, dirbate su Visual Basic rengykle (VBE),kuri atidaroma naujame lange atskirai nuo Excel.

Tarkime, kad jūsų įmonė siūlo 10 procentų kiekio nuolaidą produkto pardavimui, jei užsakymas skirtas daugiau nei 100 vienetų. Toliau nurodytose pastraipose parodysime šios nuolaidos skaičiavimo funkciją.

Toliau pateiktame pavyzdyje rodoma užsakymo forma, kurioje pateikiamas kiekvienas elementas, kiekis, kaina, nuolaida (jei yra) ir gauta išplėstinė kaina.

Example order form without a custom function

Norėdami šioje darbaknygėje sukurti pasirinktinę funkciją DISCOUNT, atlikite šiuos veiksmus:

  1. Paspauskite Alt + F11, kad atidarytumėte Visual Basic rengyklę ("Mac", paspauskite FN + ALT + F11), tada spustelėkite Įterpti > Modulis. Dešinėje programos rengyklės pusėje rodomas naujas modulio Visual Basic langas.

  2. Nukopijuokite ir įklijuokite šį kodą į naują 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
    

Pastaba: Norėdami, kad kodas būtų labiau skaitomas, eilučių įtraukai galite naudoti klavišą Tab. Įtrauka skirta tik jūsų naudai ir yra pasirinktinis, nes kodas bus vykdomas su juo arba be jo. Kai įjungsite įtrauką, Visual Basic rengyklė manys, kad jūsų kita eilutė bus panašiai įtrauka. Norėdami pereiti iš vieno skirtuko simbolio (t. y. į kairę), paspauskite Shift + Tab.

Dabar esate pasirengę naudoti naują funkciją DISCOUNT. Uždarykite Visual Basic rengyklę, pažymėkite langelį G7 ir įveskite:

=DISCOUNT(D7,E7)

Excel 10 procentų nuolaidą 200 vienetų, esant 47,50 EUR už vienetą, ir grąžina 950,00 EUR.

Pirmoje VBA kodo eilutėje Funkcija DISCOUNT(kiekis, kaina) nurodėte, kad funkcija DISCOUNT reikalauja dviejų argumentų, kiekio ir kainos. Kai darbalapio langelyje skambinate funkcija, turite įtraukti šiuos du argumentus. Formulėje =DISCOUNT(D7,E7) D7 yra kiekio argumentas, o E7 yra kainos argumentas. Dabar galite nukopijuoti formulę DISCOUNT į G8:G13, kad gautumėte toliau nurodytus rezultatus.

Apsvarstykime, kaip Excel šią funkcijos procedūrą. Paspaudus "Enter",Excel ieško pavadinimo DISCOUNT dabartinėje darbaknygėje ir randa, kad tai yra pasirinktinė VBA modulio funkcija. Argumentų pavadinimai skliausteliuose, kiekis irkaina yra reikšmių, kuriomis pagrįstas nuolaidos skaičiavimas, vietos rezervavimo ženklai.

Example order form with a custom function

Šiame kodo bloke esantis sakinys If patikrina kiekio argumentą ir nustato, ar parduotų prekių skaičius yra didesnis už arba lygus 100:

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

Jei parduotų elementų skaičius yra didesnis už 100 arba lygus 100, VBA vykdo šį sakinį, kuris daugina kiekio reikšmę iš kainos reikšmės ir tada sudaugina rezultatą iš 0,1:

Discount = quantity * price * 0.1

Rezultatas saugomas kaip kintamasis Nuolaida. VBA sakinys, kuriame saugoma kintamojo reikšmė, vadinamas užduoties sakinį, nes jis įvertina reiškinį dešinėje lygybės ženklo pusėje ir priskiria rezultatą kintamojo pavadinimui kairėje. Kadangi kintamojo nuolaida turi tokį patį pavadinimą kaip funkcijos procedūra, kintamajame saugoma reikšmė grąžinama į darbalapio formulę, kuri vadinama funkcija DISCOUNT.

Jei kiekis yra mažesnis nei 100, VBA vykdo šį sakinį:

Discount = 0

Galiausiai, šis sakinys suapvalina reikšmę, priskirtą kintamajam Nuolaida, iki dviejų dešimtainių vietų:

Discount = Application.Round(Discount, 2)

VBA neturi funkcijos ROUND, bet Excel funkcija. Todėl norėdami naudoti ROUND šiame sakinyje, nurodykite VBA ieškoti metodo Round (funkcija) programos objekte (Excel). Tai galite padaryti įtraukdami žodį Programa prieš žodį Apvalinti. Naudokite šią sintaksę kiekvieną kartą, kai Excel funkciją naudodami VBA modulį.

Pasirinktinė funkcija turi prasidėti nuo sakinių Funkcija ir baigti pabaigos funkcijos sakinį. Be funkcijos pavadinimo, sakinys Funkcija paprastai nurodo vieną ar daugiau argumentų. Tačiau galite sukurti funkciją be argumentų. Excel yra kelios įtaisytosios funkcijos , pvz., RAND ir NOW, kurios nenaudos argumentų.

Po funkcijos sakinių funkcijos procedūra apima vieną ar daugiau VBA sakinių, kurie priima sprendimus ir atlieka skaičiavimus naudodami funkcijai perd pateiktus argumentus. Galiausiai, kažkur funkcijos procedūroje turite įtraukti sakinį, kuris priskiria reikšmę kintamajam tokiu pačiu pavadinimu kaip funkcija. Ši reikšmė grąžinama į formulę, kuri iškeitų funkciją.

VBA raktažodžių, kuriuos galite naudoti pasirinktinėse funkcijose, skaičius yra mažesnis už skaičių, kurį galite naudoti makrokomandose. Pasirinktinės funkcijos negali atlikti nieko, išskyrus reikšmę į darbalapio formulę arba reiškinį, naudojamą kitoje VBA makrokomandoje ar funkcijoje. Pvz., pasirinktinės funkcijos negali keisti langų dydžio, redaguoti formulės langelyje arba keisti langelio teksto šrifto, spalvos ar rašto parinkčių. Jei į funkcijos procedūrą įtraukėte tokio tipo veiksmo kodą, funkcija grąžins #VALUE! klaidą.

Vienas veiksmas, kuris gali atlikti funkcijos procedūrą (išskyrus skaičiavimų atlikimą), rodo dialogo langą. Galite naudoti "InputBox" sakinį pasirinktinės funkcijos kaip įvesties iš vartotojo, vykdančio šią funkciją, priemonės. Galite naudoti "MsgBox" sakinį kaip informacijos perdavimo vartotojui priemones. Taip pat galite naudoti pasirinktinius dialogo langus arba "UserForms",bet tai ne šio įvado sritis.

Net paprastas makrokomandas ir pasirinktines funkcijas gali būti sunku skaityti. Jas lengviau suprasti galite įvesdami aiškinamąjį tekstą komentarų forma. Komentarus galite įtraukti prieš aiškinamąjį tekstą su apostrofais. Pavyzdžiui, šiame pavyzdyje rodoma funkcija DISCOUNT su komentarais. Įtraukus tokius komentarus, jums ar kitiems asmenims lengviau išlaikyti VBA kodą, kai eina laikas. Jei ateityje turėsite pakeisti kodą, galėsite lengviau suprasti, ką iš pradžių padarėte.

VBA funkcijos su komentarais pavyzdys

Apostrofas nurodo Excel nepaisyti visko dešinėje toje pačioje eilutėje, kad komentarus galėsite kurti pačiose eilutėse arba dešinėje eilučių, kuriose yra VBA kodas, pusėje. Galite pradėti gana ilgą kodo bloką naudodami komentarą, kuriame paaiškinamas jo bendras tikslas, o tada naudoti atskiriems sakinius dokumentuoti atskiriems komentarams.

Kitas būdas dokumentuoti makrokomandas ir pasirinktines funkcijas yra suteikti joms aprašomuosius pavadinimus. Pavyzdžiui, užuot pavadinę makrokomandų žymas, galite pavadinti jį MonthLabels, kad tiksliau apibūdintumėte makrokomandos paskirtį. Aprašomųjų pavadinimų naudojimas makrokomandoms ir pasirinktinėms funkcijoms ypač naudingas, kai sukuriate daug procedūrų, ypač jei kuriate procedūras, kurios turi panašius, bet ne identiškus tikslus.

Kaip dokumentuoti makrokomandas ir pasirinktines funkcijas yra asmeninio pasirinkimo klausimas. Svarbu priimti tam tikrą dokumentacijos metodą ir nuosekliai jį naudoti.

Norint naudoti pasirinktinę funkciją, darbaknygė, kurioje yra modulis, kuriame sukūrėte funkciją, turi būti atidaryta. Jei darbaknygė neatidaryti, gausite #NAME? klaida, kai bandote naudoti funkciją. Jei nurodote funkciją kitoje darbaknygėje, prieš funkcijos pavadinimą turite nurodyti darbaknygės, kurioje yra funkcija, pavadinimą. Pvz., jei darbaknygėje, pavadintoje "Personal.xlsb", sukuriate funkciją DISCOUNT ir skambinate šią funkciją iš kitos darbaknygės, turite įvesti =personal.xlsb!discount(), o ne tiesiog =discount().

Galite įrašyti keletą klavišų paspaudimų (ir galimų spausdinimo klaidų) pasirinkdami pasirinktines funkcijas dialogo lange Funkcijos įterpimas. Pasirinktinės funkcijos rodomos vartotojo apibrėžtoje kategorijoje:

insert function dialog box

Paprasčiau padaryti, kad pasirinktinės funkcijos visada būtų prieinamos, yra jas saugoti atskiroje darbaknygėje ir įrašyti ją kaip papildinį. Tada galite padaryti papildą prieinamą, kai paleidžiate Excel. Štai kaip tai padaryti:

  1. Sukūrę reikalingas funkcijas, spustelėkite Failas > Įrašyti kaip.

    Lauke Excel 2007 spustelėkite mygtuką Microsoft Office ,tada spustelėkite Įrašyti kaip

  2. Dialogo lange Įrašyti kaip atidarykite išplečiamąjį sąrašą Įrašyti kaip tipą ir pasirinkite Excel papildinių . Įrašykite darbaknygę atpažįstamu vardu, pvz., "MyFunctions",aplanke Papildiniai. Dialogo langas Įrašyti kaip pasiūlys tą aplanką, todėl viskas, ką jums reikia padaryti, yra priimti numatytąją vietą.

  3. Įrašę darbaknygę, spustelėkite Failas >Excel Parinktys.

    Lauke Excel 2007 spustelėkite mygtuką Microsoft Office ,tada spustelėkite Excel Parinktys.

  4. Dialogo Excel parinktys spustelėkite kategoriją Papildiniai.

  5. Išplečiamajame sąraše Tvarkyti pasirinkite Excel papildiniai. Tada spustelėkite mygtuką Eiti.

  6. Dialogo lange Papildiniai pažymėkite žymės langelį šalia pavadinimo, kurį naudojote įrašydami darbaknygę, kaip parodyta toliau.

    add-ins dialog box

  1. Sukūrę reikalingas funkcijas, spustelėkite Failas > Įrašyti kaip.

  2. Dialogo lange Įrašyti kaip atidarykite išplečiamąjį sąrašą Įrašyti kaip tipą ir pasirinkite Excel papildinių . Įrašykite darbaknygę atpažįstamu pavadinimu, pvz., MyFunctions.

  3. Įrašę darbaknygę spustelėkite Įrankiai, > Excel papildiniai.

  4. Dialogo lange Papildiniai pasirinkite mygtuką Naršyti, kad rastumėte papildinį, spustelėkite Atidaryti ,tada pažymėkite žymės langelį šalia Add-In lauke Galimi papildiniai.

Atlikus šiuos veiksmus, jūsų pasirinktinės funkcijos bus prieinamos kiekvieną kartą paleidus Excel. Jei norite įtraukti į savo funkcijų biblioteką, grįžkite į Visual Basic rengyklę. Jei "Visual Basic rengyklės Project "Explorer" dalyje VBAProject antraštė, matysite modulį, pavadintą po papildinio failu. Papildinyje bus plėtinys .xlam.

named module in vbe

Dukart spustelėjus tą modulį "Project Explorer", Visual Basic rengyklė rodo jūsų funkcijos kodą. Norėdami įtraukti naują funkciją, perkelkite įterpimo vietą po pabaigos funkcijos sakinį, kuris nutraukia paskutinę funkciją lange Kodas, ir pradėkite įvesti tekstą. Tokiu būdu galite sukurti tiek funkcijų, kiek jums reikia, ir jos visada bus galimos vartotojo apibrėžtoje kategorijoje, dialogo lange Funkcijos įterpimas.

Šis turinys iš pradžių buvo sukurtas Mark Dodge ir Craig Stinson kaip dalį savo knygos Microsoft Office Excel 2007 Inside Out. Nuo to laiko ji buvo atnaujinta, kad būtų taikoma ir naujesnėms Excel versijoms.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Reikia daugiau pagalbos?

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×