Kurkite pasirinktines funkcijas programoje „Excel“

Pasirinktinės funkcijos, pvz., makrokomandos, naudoja "Visual Basic for Applications" (VBA) programavimo kalbą. Jos skiriasi nuo makrokomandų dviem svarbiais būdais. Pirmiausia, vietoje Antrinių procedūrų jos naudoja funkcijų procedūras. T. y. jie prasideda sakiniu Funkcija , o ne Antriniu sakiniu, o baigiasi pabaigos funkcija , o ne "End Sub". 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" Redaktorius (VBE), kuris atidaromas naujame, ne "Excel" lange.

Tarkime, kad jūsų įmonė siūlo 10 procentų dydžio nuolaidą produkto pardavimui, jei užsakymas yra skirtas daugiau nei 100 vienetų. Tolesnėse pastraipose parodysime funkciją šiai nuolaidai apskaičiuoti.

Toliau pateiktame pavyzdyje rodoma užsakymo forma, kurioje išvardijame kiekvieną prekę, kiekį, kainą, nuolaidą (jei yra) ir gautą išplėstinę kainą.

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

1. Paspauskite Alt + F11, kad atidarytumėte "Visual Basic" Redaktorius ("Mac" kompiuteryje paspauskite FN + ALT + F11), tada spustelėkite Įterpti > modulį. Dešinėje "Visual Basic" Redaktorius pusėje rodomas naujas modulio 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
   

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

=DISCOUNT(D7,E7)

"Excel" apskaičiuoja 10 procentų nuolaidą 200 vienetų nuo 47,50 EUR už vienetą ir pateikia 950,00 EUR.

Pirmoje VBA kodo eilutėje Funkcija DISCOUNT(kiekis, kaina) nurodėte, kad funkcija DISCOUNT reikalauja dviejų argumentų, kiekio ir kainos. Kai iškviečiate funkciją darbalapio langelyje, 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.

Apgalvokime, kaip "Excel" interpretuoja šią funkcijos procedūrą. Paspaudus "Enter", "Excel" ieško pavadinimo DISCOUNT dabartinėje darbaknygėje ir randa, kad tai yra pasirinktinė funkcija VBA modulyje. Argumentų pavadinimai apskliausti skliaustais, kiekiu ir kaina yra reikšmių, kuriomis pagrįstas nuolaidos skaičiavimas, vietos rezervavimo ženklai.

Sakinys If šiame kodo bloke 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ų prekių skaičius yra didesnis arba lygus 100, VBA vykdo šį sakinį, kuris padaugina kiekio reikšmę iš kainos reikšmės ir tada rezultatą padaugina iš 0,1:

Discount = quantity * price * 0.1

Rezultatas saugomas kaip kintamojo 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ą kintamojo pavadinimui kairėje. Kadangi kintamojo nuolaida turi tą patį pavadinimą kaip funkcijos procedūra, kintamajame saugoma reikšmė grąžinama į darbalapio formulę, kuri vadinama funkcija DISCOUNT.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA neturi funkcijos ROUND, tačiau ji veikia "Excel". Todėl norėdami naudoti ROUND šiame sakinyje, galite nurodyti VBA ieškoti apvalaus metodo (funkcija) objekte Application (Excel). Tai galite padaryti įtraukdami žodį Application prieš žodį Round. Naudokite šią sintaksę, kai norite pasiekti "Excel" funkciją iš VBA modulio.

Pasirinktinė funkcija turi prasidėti sakiniu Funkcija, o baigiasi sakiniu Pabaigos funkcija. Be funkcijos pavadinimo, sakinys Funkcija paprastai nurodo vieną ar daugiau argumentų. Tačiau galite sukurti funkciją be argumentų. Programoje "Excel" yra kelios įtaisytosios funkcijos, pvz., RAND ir NOW, nenaudojančios argumentų.

Po funkcijos sakinio, funkcijos procedūroje yra vienas ar daugiau VBA sakinių, kurie priima sprendimus ir atlieka skaičiavimus naudodami funkcijai perduotus argumentus. Galiausiai, kur nors funkcijos procedūroje turite įtraukti sakinį, kuris kintamajam priskiria reikšmę tuo pačiu pavadinimu kaip funkcija. Ši reikšmė grąžinama į formulę, kuri iškviečia funkciją.

VBA raktažodžių, kuriuos galite naudoti pasirinktinėms funkcijoms, skaičius yra mažesnis už skaičių, kurį galite naudoti makrokomandose. Pasirinktinėms funkcijoms neleidžiama atlikti nieko kito, tik grąžinti reikšmės į darbalapio formulę arba reiškinį, naudojamą kitoje VBA makrokomandoje ar funkcijoje. Pavyzdžiui, 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ą įtrauksite tokio tipo veiksmo kodą, funkcija grąžins #VALUE! klaidą.

Vienas veiksmas, kurį gali atlikti funkcijos procedūra (neskaitant skaičiavimų atlikimo), yra dialogo langas. Pasirinktinėje funkcijoje galite naudoti sakinį InputBox kaip priemonę gauti įvestį iš funkciją vykdančio vartotojo. Galite naudoti msgbox sakinį kaip informacijos perteijimo vartotojui priemonę. Taip pat galite naudoti pasirinktinius dialogo langus arba vartotojo formas, bet tai tema, nepatenkanti į šį įvadą.

Gali būti sunku perskaityti net paprastas makrokomandas ir pasirinktines funkcijas. Jas lengviau suprasti įvesdami aiškinamąjį tekstą komentarų forma. Komentarus įtraukiate prieš aiškinamąjį tekstą naudodami apostrofą. Pavyzdžiui, šiame pavyzdyje rodoma funkcija DISCOUNT su komentarais. Įtraukus tokius komentarus, jums ar kitiems bus lengviau išlaikyti VBA kodą, kai pereinate prie laiko. Jei reikės pakeisti kodą ateityje, galėsite lengviau suprasti, ką atlikote iš pradžių.

Apostrofas nurodo "Excel" nepaisyti visko dešinėje toje pačioje eilutėje, kad galėtumėte kurti komentarus arba eilutėse, arba dešinėje eilučių, kuriose yra VBA kodas, pusėje. Gana ilgą kodo bloką galite pradėti nuo komentaro, paaiškinančio jo bendrą paskirtį, ir naudoti įdėtuosius komentarus atskiriems sakiniai dokumentuoti.

Kitas būdas dokumentuoti makrokomandas ir pasirinktines funkcijas – suteikti joms aprašomuosius pavadinimus. Pavyzdžiui, užuot pavadinę makrokomandų žymas, galite ją pavadinti MonthLabels , kad tiksliau apibūdintumėte makrokomandos paskirtį. Aprašomuosius pavadinimus makrokomandoms ir pasirinktinėms funkcijoms naudoti ypač patogu, kai sukūrėte daug procedūrų, ypač jei kuriate procedūras, kurių tikslai panašūs, bet nėra vienodi.

Kaip dokumentuoti makrokomandas ir pasirinktines funkcijas, priklauso nuo asmeninių nuostatų. Svarbu priimti tam tikrą dokumentacijos būdą ir nuosekliai jį naudoti.

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

Galite įrašyti keletą klavišų paspaudimų (ir galimų įvedimo klaidų) dialogo lange Įterpti funkciją pasirinkdami pasirinktines funkcijas. Pasirinktinės funkcijos rodomos kategorijoje Vartotojo apibrėžta:

Lengviau bet kuriuo metu padaryti pasirinktines funkcijas pasiekiamas – saugoti jas atskiroje darbaknygėje, tada įrašyti darbaknygę kaip papildinį. Tada galėsite padaryti, kad papildinys būtų pasiekiamas kiekvieną kartą, kai paleidžiate "Excel". Štai kaip tai padaryti:

Atlikus šiuos veiksmus, jūsų pasirinktinės funkcijos bus pasiekiamos kiekvieną kartą paleidus "Excel". Jei norite įtraukti į funkcijų biblioteką, grįžkite į "Visual Basic" Redaktorius. Jei matote "Visual Basic" Redaktorius "Project Explorer" po antrašte VBA projektas, po papildinio failu matysite modulį, pavadintą. Jūsų papildinys turės plėtinį .xlam.

Dukart spustelėjus modulį "Project Explorer", "Visual Basic" Redaktorius rodo jūsų funkcijos kodą. Norėdami įtraukti naują funkciją, nustatykite įterpimo vietą po sakinio Pabaigos funkcija, kuris nutraukia paskutinę funkciją kodo lange ir pradeda vesti tekstą. Tokiu būdu galite sukurti tiek funkcijų, kiek reikia, ir jos visada bus pasiekiamos dialogo lango Įterpti funkciją kategorijoje Vartotojo apibrėžta.

Šį turinį iš pradžių sukūrė Markas Dodge ir Craigas Stinsonas, kaip knygos "Microsoft Office Excel 2007 Inside Out" dalis. Nuo tada ji buvo atnaujinta, kad būtų taikoma ir naujesnėms "Excel" versijoms.