Crearea funcțiilor particularizate în Excel

Deși Excel include o multitudine de funcții predefinite pentru foi de lucru, este posibil să nu aibă o funcție pentru fiecare tip de calcul efectuat. Proiectanții Excel nu puteau anticipa nevoile de calcul ale fiecărui utilizator. În schimb, Excel vă oferă posibilitatea de a crea funcții particularizate, care sunt explicate în acest articol.

Crearea unei funcții particularizate simple

Funcțiile particularizate, cum ar fi macrocomenzile, utilizează limbajul de programare Visual Basic for Applications (VBA). Acestea diferă de macrocomenzi în două moduri semnificative. Mai întâi, utilizează proceduri Function în loc de proceduri Sub . Aceasta înseamnă că încep cu o instrucțiune Function în loc de instrucțiunea Sub și se încheie cu End Function în loc de End Sub. În al doilea rând, aceștia efectuează calcule în loc să efectueze acțiuni. Anumite tipuri de instrucțiuni, cum ar fi instrucțiunile care selectează și formatează zone, sunt excluse din funcțiile particularizate. În acest articol, veți afla cum să creați și să utilizați funcții particularizate. Pentru a crea funcții și macrocomenzi, lucrați cu Visual Basic Editor (VBE), care se deschide într-o fereastră nouă, separată de Excel.

Să presupunem că firma dvs. oferă o reducere de cantitate de 10 procente la vânzarea unui produs, dacă comanda este pentru mai mult de 100 de unități. În paragrafele următoare, vom demonstra o funcție pentru a calcula această reducere.

Exemplul de mai jos afișează un formular de comandă care listează fiecare articol, cantitatea, prețul, reducerea (dacă există) și prețul extins rezultat.

Pentru a crea o funcție DISCOUNT particularizată în acest registru de lucru, urmați acești pași:

1. Apăsați Alt+F11 pentru a deschide Visual Basic Editor (pe Mac, apăsați FN+ALT+F11), apoi faceți clic pe Inserare>modul. O fereastră de modul nouă apare în partea dreaptă a Visual Basic Editor.

2. Copiați și lipiți următorul cod în noul 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
   
   

Utilizarea funcțiilor particularizate

Acum sunteți gata să utilizați noua funcție DISCOUNT. Închideți Visual Basic Editor, selectați celula G7 și tastați următoarele:

=DISCOUNT(D7,E7)

Excel calculează reducerea de 10 procente pentru 200 de unități, la 47,50 lei pe unitate și returnează 950,00 lei.

În prima linie a codului VBA, Funcția DISCOUNT(cantitate, preț), ați indicat că funcția DISCOUNT necesită două argumente, cantitatea și prețul. Când apelați funcția într-o celulă din foaia de lucru, trebuie să includeți aceste două argumente. În formula =DISCOUNT(D7,E7), D7 este argumentul cantității și E7 este argumentul preț . Acum puteți copia formula DISCOUNT în G8:G13 pentru a obține rezultatele afișate mai jos.

Să analizăm modul în care Excel interpretează această procedură a funcției. Când apăsați pe Enter, Excel caută numele DISCOUNT în registrul de lucru curent și descoperă că este o funcție particularizată într-un modul VBA. Numele de argumente dintre paranteze, cantitate și preț, sunt substituenții pentru valorile pe care se bazează calculul reducerii.

Instrucțiunea If din următorul bloc de cod examinează argumentul cantității și determină dacă numărul de articole vândute este mai mare sau egal cu 100:

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

Dacă numărul de articole vândute este mai mare sau egal cu 100, VBA execută următoarea instrucțiune, care înmulțește valoarea cantității cu valoarea prețului , apoi înmulțește rezultatul cu 0,1:

Discount = quantity * price * 0.1

Rezultatul este stocat ca variabilă Reducere. O instrucțiune VBA care stochează o valoare într-o variabilă este denumită instrucțiune de atribuire , deoarece evaluează expresia din partea dreaptă a semnului egal și atribuie rezultatul numelui variabilei din stânga. Deoarece variabila Reducere are același nume ca procedura funcției, valoarea stocată în variabilă este returnată la formula din foaia de lucru care a apelat funcția DISCOUNT.

Dacă cantitatea este mai mică de 100, VBA execută următoarea instrucțiune:

Discount = 0

În fine, următoarea instrucțiune rotunjește valoarea atribuită variabilei Discount la două zecimale:

Discount = Application.Round(Discount, 2)

VBA nu are funcția ROUND, dar Excel are. Prin urmare, pentru a utiliza ROUND în această instrucțiune, spuneți serviciului VBA să caute metoda (funcția) Round în obiectul Application (Excel). Faceți acest lucru adăugând cuvântul Aplicație înaintea cuvântului Rundă. Utilizați această sintaxă oricând trebuie să accesați o funcție Excel dintr-un modul VBA.

Înțelegerea regulilor pentru funcții particularizate

O funcție particularizată trebuie să înceapă cu o instrucțiune Function și să se termine cu o instrucțiune End Function. Pe lângă numele funcției, instrucțiunea Function specifică, de obicei, unul sau mai multe argumente. Totuși, puteți crea o funcție fără argumente. Excel include mai multe funcții predefinite - RAND și NOW, de exemplu - care nu utilizează argumente.

După instrucțiunea Function, o procedură de funcție include una sau mai multe instrucțiuni VBA care iau decizii și efectuează calcule folosind argumentele transmise funcției. În cele din urmă, undeva în procedura funcției, trebuie să includeți o instrucțiune care atribuie o valoare unei variabile cu același nume ca cel al funcției. Această valoare este returnată formulei care apelează funcția.

Utilizarea cuvintelor cheie VBA în funcții particularizate

Numărul de cuvinte-cheie VBA pe care le puteți utiliza în funcțiile particularizate este mai mic decât numărul pe care îl puteți utiliza în macrocomenzi. Funcțiilor particularizate nu li se permite să facă altceva decât să returneze o valoare la o formulă dintr-o foaie de lucru sau la o expresie utilizată în altă macrocomandă sau funcție VBA. De exemplu, funcțiile particularizate nu pot să redimensioneze ferestre, să editeze o formulă într-o celulă sau să modifice opțiunile de font, culoare sau model pentru textul dintr-o celulă. Dacă includeți un cod de "acțiune" de acest fel într-o procedură de funcție, funcția returnează #VALUE! .

Singura acțiune pe care o poate efectua o procedură de funcție (în afară de efectuarea de calcule) este afișarea unei casete de dialog. Puteți utiliza o instrucțiune InputBox într-o funcție particularizată ca mijloc de a obține date de la utilizatorul care execută funcția. Puteți utiliza o instrucțiune MsgBox ca mijloc de a transmite informații utilizatorului. Puteți utiliza, de asemenea, casete de dialog particularizate sau formulare utilizator, însă acesta este un subiect care nu este subiectul acestei introduceri.

Documentarea macrocomenzilor și a funcțiilor particularizate

Chiar și macrocomenzile simple și funcțiile particularizate pot fi dificil de citit. Le puteți face mai ușor de înțeles tastând text explicativ sub formă de comentarii. Adăugați comentarii dacă plasați un apostrof înaintea textului explicativ. De exemplu, următorul exemplu arată funcția DISCOUNT cu comentarii. Adăugarea de comentarii ca acestea vă ajută pe dvs. sau alte persoane să păstrați codul VBA pe măsură ce trece timpul. Dacă trebuie să modificați codul în viitor, veți înțelege mai ușor ce ați făcut inițial.

Un apostrof spune programului Excel să ignore toate elementele din dreapta pe aceeași linie, astfel încât să puteți crea comentarii fie pe linii separate, fie în partea dreaptă a liniilor care conțin cod VBA. Puteți să începeți un bloc de cod relativ lung cu un comentariu care explică scopul general al acestuia, apoi să utilizați comentarii în linie pentru a documenta instrucțiuni individuale.

Altă modalitate de a vă documenta macrocomenzile și funcțiile particularizate este să le oferiți nume descriptive. De exemplu, în loc să denumiți o macrocomandă Etichete, o puteți denumi EticheteLună pentru a descrie mai precis scopul pe care îl servește macrocomanda. Utilizarea de nume descriptive pentru macrocomenzi și funcții particularizate este utilă mai ales atunci când ați creat multe proceduri, mai ales când creați proceduri care au scopuri asemănătoare, dar nu identice.

Modul în care vă documentați macrocomenzile și funcțiile particularizate este o chestiune de preferință personală. Ceea ce este important este să adoptați o metodă de documentare și să o utilizați în mod unitar.

Faceți funcțiile particularizate disponibile oriunde

Pentru a utiliza o funcție particularizată, registrul de lucru care conține modulul în care ați creat funcția trebuie să fie deschis. Dacă acel registru de lucru nu este deschis, primiți un #NAME? atunci când încercați să utilizați funcția. Dacă faceți referire la funcție în alt registru de lucru, trebuie să puneți înaintea numelui funcției numele registrului de lucru în care se află funcția. De exemplu, dacă creați o funcție numită DISCOUNT într-un registru de lucru numit Personal.xlsb și apelați acea funcție din alt registru de lucru, trebuie să tastați =personal.xlsb!discount(), nu doar =discount().

Vă puteți scuti de unele secvențe de taste (și de posibilele erori de tastare) selectând funcțiile particularizate din caseta de dialog Inserare funcție. Funcțiile particularizate apar în categoria Definit de utilizator:

O modalitate mai simplă de a face funcțiile particularizate disponibile permanent este să le stocați într-un registru de lucru separat, apoi să salvați acel registru de lucru ca program de completare. Apoi, puteți face programul de completare disponibil de fiecare dată când rulați Excel. Iată cum să faceți acest lucru:

După ce urmați acești pași, funcțiile particularizate vor fi disponibile de fiecare dată când rulați Excel. Dacă doriți să le adăugați la biblioteca de funcții, reveniți la Visual Basic Editor. Dacă vă uitați în Visual Basic Editor Project Explorer sub un titlu VBAProject, veți vedea un modul numit după fișierul de program de completare. Programul de completare va avea extensia .xlam.

named Dacă faceți dublu clic pe acel modul în Project Explorer, Visual Basic Editor afișează codul funcției. Pentru a adăuga o funcție nouă, poziționați punctul de inserare după instrucțiunea End Function care termină ultima funcție din fereastra Cod și începeți să tastați. Puteți crea oricâte funcții aveți nevoie în acest mod, iar acestea vor fi întotdeauna disponibile în categoria Definit de utilizator din caseta de dialog Inserare funcție .

Despre autori

Acest conținut a fost scris inițial de Mark Dodge și Craig Stinson, ca parte a cărții Microsoft Office Excel 2007: Inside Out. De atunci, a fost actualizat pentru a se aplica și la versiunile mai noi de Excel.

Aveți nevoie de ajutor suplimentar?

Puteți oricând să întrebați un expert de la Excel Tech Community sau să obțineți asistență de la Comunități.