Kurkite pasirinktines funkcijas programoje „Excel“

Nors programoje "Excel" yra daugybė įtaisytųjų darbalapio funkcijų, tikėtina, kad ji neturi funkcijos kiekvieno tipo skaičiavimams. "Excel" kūrėjai negalėjo numatyti kiekvieno vartotojo skaičiavimo poreikių. Vietoj to, "Excel" suteikia galimybę kurti pasirinktines funkcijas, kurios paaiškintos šiame straipsnyje.

Paprastos pasirinktinės funkcijos kūrimas

Pasirinktinės funkcijos, pvz., makrokomandos, naudoja " Visual Basic for Applications" (VBA) programavimo kalbą. Jos skiriasi nuo makrokomandų dviem svarbiais bruožais. Pirma, jie naudoja funkcijos procedūras vietoj antrinių procedūrų. Tai yra jie prasideda sakiniu Function vietoj sakinio Sub ir baigiasi End Function vietoj End Sub. Antra, jie atlieka skaičiavimus, užuot ėmęsi veiksmų. 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, turite dirbti su "Visual Basic" rengykle (VBE), kuri atidaroma atskirame lange nuo "Excel".

Tarkime, jūsų įmonė siūlo 10 procentų nuolaidą produkto pardavimui, jei užsakymas yra didesnis negu 100 vienetų. Tolesnėse pastraipose parodysime šios nuolaidos apskaičiavimo funkciją.

Toliau pateiktame pavyzdyje rodoma užsakymo forma, kurioje išvardijami visi elementai, kiekiai, kaina, nuolaida (jei tokių yra) ir gauta išplėstinė kaina.

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

1. Paspauskite "Alt" + F11 , kad atidarytumėte "Visual Basic" rengyklę ("Mac" kompiuteryje paspauskite FN + ALT + F11), tada spustelėkite Įterpti>modulį. Naujas modulio langas atsiranda dešinėje "Visual Basic" rengyklės pusėje.

2. Nukopijuokite ir įklijuokite toliau pateiktą 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
   
   

Pasirinktinių funkcijų naudojimas

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

=DISCOUNT(D7,E7)

"Excel" apskaičiuoja 10 procentų nuolaidą 200 vienetų po 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ų: kiekis ir kaina. Iškviesdami funkciją darbalapio langelyje, turite įtraukti šiuos du argumentus. Formulėje =DISCOUNT(D7,E7) D7 yra kiekio argumentas, o E7 – kainos argumentas. Dabar galite kopijuoti DISCOUNT formulę į G8:G13 ir gauti toliau pateiktus rezultatus.

Panagrinėkime, kaip "Excel" interpretuoja šią funkcijos procedūrą. Paspaudus Enter, "Excel" dabartinėje darbaknygėje ieško pavadinimo NUOLAIDA ir randa, kad tai yra pasirinktinė VBA modulio funkcija. Argumentų pavadinimai skliausteliuose kiekis irkaina yra reikšmių, kuriomis remiantis skaičiuojama nuolaida, vietos rezervavimo ženklai.

Šio kodo bloko sakinys Jei nagrinėja kiekio argumentą ir nustato, ar parduotų elementų skaičius yra didesnis arba lygus 100:

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

Jei parduotų elementų skaičius yra didesnis arba lygus 100, VBA vykdo toliau pateiktą sakinį, kuris kiekio reikšmę padaugina iš kainos reikšmės ir gautą rezultatą padaugina iš 0,1:

Discount = quantity * price * 0.1

Rezultatas saugomas kaip kintamasis Nuolaida. VBA sakinys, kuriame saugoma kintamojo reikšmė, vadinamas priskyrimo sakiniu, nes jis įvertina reiškinį dešinėje lygybės ženklo pusėje ir priskiria rezultatą kairėje pusėje esančiam kintamojo pavadinimui. Kadangi kintamojo Nuolaida pavadinimas yra toks pat kaip funkcijos procedūros, kintamajame saugoma reikšmė grąžinama į darbalapio formulę, kuri iškvietė funkciją DISCOUNT.

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

Discount = 0

Galiausiai, šis sakinys suapvalina kintamajam Nuolaida priskirtą reikšmę iki dviejų skaitmenų po kablelio:

Discount = Application.Round(Discount, 2)

VBA neturi funkcijos ROUND, bet programa "Excel" ją turi. Todėl norėdami naudoti ROUND šiame sakinyje, nurodykite VBA ieškoti apvalinimo metodo (funkcijos) objekte Programa ("Excel"). Tai padarysite prieš žodį "apvalus" pridedant žodį "Taikymas ". Naudokite šią sintaksę kaskart, kai jums reikia pasiekti "Excel" funkciją iš VBA modulio.

Pasirinktinių funkcijų taisyklių supratimas

Pasirinktinė funkcija turi prasidėti sakiniu Function ir baigtis sakiniu End Function. Be funkcijos pavadinimo, funkcijos sakinys paprastai nurodo vieną ar kelis argumentus. Tačiau galite sukurti funkciją be argumentų. "Excel" turi kelias įtaisytąsias funkcijas, pvz., RAND ir NOW, kurios nenaudoja argumentų.

Po funkcijos sakinio funkcijos procedūra apima vieną ar daugiau VBA sakinių, kurie priima sprendimus ir atlieka skaičiavimus naudodami funkcijai perduotus argumentus. Galiausiai, kažkur funkcijos procedūroje turite įtraukti sakinį, priskiriantį reikšmę kintamajam, turinčiam tokį patį pavadinimą kaip funkcija. Ši reikšmė pateikiama formulei, kuri iškviečia funkciją.

VBA raktažodžių naudojimas pasirinktinėse funkcijose

VBA raktažodžių skaičius, kurį galite naudoti pasirinktinėse funkcijose, yra mažesnis nei skaičius, kurį galite naudoti makrokomandose. Pasirinktinėms funkcijoms neleidžiama daryti nieko kito, išskyrus grąžinti reikšmę į darbalapio formulę arba reiškinį, naudojamą kitoje VBA makrokomandoje ar funkcijoje. Pavyzdžiui, pasirinktinės funkcijos negali keisti langų dydžio, redaguoti langelyje esančios formulės arba keisti langelyje esančio teksto šrifto, spalvos ar rašto parinkčių. Jei įtrauksite tokio tipo "veiksmo" kodą į funkcijos procedūrą, funkcija pateiks #VALUE! klaidą.

Vienintelis veiksmas, kurį gali atlikti funkcijos procedūra (be skaičiavimų atlikimo), yra parodyti dialogo langą. Galite naudoti sakinį InputBox pasirinktinėje funkcijoje, kad gautumėte įvestį iš funkciją vykdančio vartotojo. Galite naudoti sakinį "MsgBox ", norėdami perteikti informaciją vartotojui. Taip pat galite naudoti pasirinktinius dialogo langus arba vartotojo formas, bet tai yra šio įvado tema.

Makrokomandų ir pasirinktinių funkcijų dokumentavimas

Net paprastas makrokomandas ir pasirinktines funkcijas gali būti sunku perskaityti. Galite padaryti juos lengviau suprantamus įvesdami aiškinamąjį tekstą komentarų forma. Komentarus įtraukiate prieš aiškinamąjį tekstą įrašydami apostrofą. Pavyzdžiui, šiame pavyzdyje rodoma funkcija DISCOUNT su komentarais. Įtraukus tokius komentarus jums arba kitiems bus lengviau tvarkyti VBA kodą laikui bėgant. Jei ateityje reikės pakeisti kodą, bus lengviau suprasti, ką darėte iš pradžių.

Apostrofas nurodo "Excel" nepaisyti visko, kas yra į dešinę nuo tos pačios eilutės, todėl galite kurti komentarus atskirose eilutėse arba eilučių, kuriose yra VBA kodas, dešinėje pusėje. Galite pradėti gana ilgą kodo bloką komentaru, paaiškinančiu jo paskirtį, ir naudoti įdėtuosius komentarus atskiriems sakiniams dokumentuoti.

Kitas būdas dokumentuoti makrokomandas ir pasirinktines funkcijas – suteikti joms aprašomuosius pavadinimus. Pvz., užuot pavadinę makrokomandą Etiketės, galite ją pavadinti Mėnesio etiketės , kad tiksliau apibūdintumėte makrokomandos paskirtį. Aprašomuosius makrokomandų ir pasirinktinių funkcijų pavadinimus naudoti ypač naudinga, kai sukūrėte daug procedūrų, ypač, jei kuriate procedūras, turinčias panašius, bet ne identiškus tikslus.

Kaip dokumentuosite makrokomandas ir pasirinktines funkcijas, priklauso nuo asmeninių pageidavimų. Svarbu yra priimti tam tikrą dokumentacijos metodą ir jį nuosekliai naudoti.

Pasirinktinių funkcijų pasiekiamumas bet kur

Norint naudoti pasirinktinę funkciją, turi būti atidaryta darbaknygė, kurioje yra modulis, kuriame sukūrėte funkciją. Jei darbaknygė nėra atidaryta, gaunate #NAME? klaida, kai bandote naudoti funkciją. Jei nurodote funkciją kitoje darbaknygėje, prieš funkcijos pavadinimą turite nurodyti darbaknygės, kurioje yra funkcija, pavadinimą. Pavyzdžiui, jei kuriate funkciją DISCOUNT darbaknygėje Personal.xlsb ir iškviečiate šią funkciją iš kitos darbaknygės, turite įvesti =personal.xlsb!discount(), o ne tiesiog =DISCOUNT().

Galite sutaupyti klavišų paspaudimų (ir galimų spausdinimo klaidų), dialogo lange Įterpti funkciją pasirinkdami pasirinktines funkcijas. Jūsų pasirinktinės funkcijos rodomos vartotojo apibrėžtoje kategorijoje:

Paprasčiau padaryti, kad pasirinktinės funkcijos būtų visada pasiekiamos, yra išsaugoti jas atskiroje darbaknygėje ir įrašyti tą darbaknygę kaip priedą. Tada galima padaryti, kad papildinys būtų pasiekiamas paleidus "Excel". Toliau nurodyta, kaip tai padaryti“

Atlikus šiuos veiksmus, jūsų pasirinktinės funkcijos bus pasiekiamos kiekvieną kartą paleidus "Excel". Jei norite įtraukti į savo funkcijų biblioteką, grįžkite į "Visual Basic" rengyklę. Jei pažvelgsite į "Visual Basic" rengyklę "Project Explorer" po antrašte "VBAProject", pamatysite modulį, pavadintą papildinio failo vardu. Jūsų papildinys turės plėtinį .xlam.

Dukart spustelėjus modulį "Project Explorer", "Visual Basic" rengyklė parodys funkcijos kodą. Norėdami įtraukti naują funkciją, perkelkite įterpimo vietą po sakinio End Function, kuris užbaigia paskutinę funkciją lange Kodas, ir pradėkite įvesti. Tokiu būdu galite sukurti tiek funkcijų, kiek jums reikia, ir jos visada bus pasiekiamos dialogo lango Įterpti funkciją kategorijoje Vartotojo apibrėžta funkcija.

Apie autorius

Šį turinį iš pradžių parašė 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ėse "Excel" versijose.

Reikia daugiau pagalbos?

Visada galite kreiptis eksperto į "Excel" technologijų bendruomenę arba gauti pagalbos bendruomenėse.