Crearea de funcții particularizate în Excel

Deși Excel include o multitudine de funcții de foaie de lucru predefinite, sunt șanse să nu aibă o funcție pentru fiecare tip de calcul pe care îl efectuați. Designerii de Excel nu pot anticipa, eventual, 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.

Căutați informații despre cum să creați o funcție particularizată JavaScript pe care o puteți executa în Excel pentru Windows, Excel pentru Mac sau Excel pentru web ? Dacă sunteți, consultați articolul prezentarea generală a funcțiilor particularizate din Excel.

Funcții particularizate, cum ar fi macrocomenzi, utilizați limba de programare Visual Basic for Applications (VBA) . Acestea diferă de macrocomenzi în două moduri semnificative. Mai întâi, utilizează proceduri funcționale în loc de sub proceduri. Mai exact, aceștia încep cu o instrucțiune de funcție în loc de subinstrucțiune și se termină cu funcția end în loc de subtitlul final. În al doilea rând, efectuează calcule în loc să ia măsuri. Anumite tipuri de declarații, cum ar fi declarațiile care selectează și formatează intervale, sunt excluse din funcțiile particularizate. În acest articol, veți afla cum să creați și să utilizați funcțiile 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 dumneavoastră oferă o reducere cantitativă de 10 procente pentru vânzarea unui produs, cu condiția ca comanda să fie 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 element, cantitate, preț, reducere (dacă este cazul) și prețul extins rezultat.

Exemplu de formular de comandă fără o funcție particularizată

Pentru a crea o funcție de reducere 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. Apare o fereastră modul nou în partea dreaptă a Editorului Visual Basic.

  2. Copiați și lipiți următorul cod în modulul nou.

    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
    

Notă: Pentru a face codul mai lizibil, puteți utiliza tasta Tab pentru a indenta liniile. Indentarea este doar pentru beneficiul dumneavoastră și este opțională, deoarece codul se va executa cu sau fără aceasta. După ce tastați o linie indentată, editorul Visual Basic presupune că următoarea linie va fi indentată în mod similar. Pentru a vă deplasa (adică la stânga) cu un caracter tabulator, apăsați Shift + Tab.

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

= DISCOUNT (D7, E7)

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

În prima linie a codului VBA, reducere funcție (cantitate, preț), ați indicat că funcția DISCOUNT necesită două argumente, Cantitate și preț. Atunci când apelați funcția într-o celulă de foaie de lucru, trebuie să includeți cele două argumente. În formula = DISCOUNT (D7, E7), D7 este argumentul cantitativ , iar E7 este argumentul prețurilor . Acum puteți să copiați formula de reducere la G8: G13 pentru a obține rezultatele afișate mai jos.

Să luăm în considerare modul în care Excel interpretează această procedură de funcție. Atunci când apăsați pe Enter, Excel caută reducerea numelui din registrul de lucru curent și constată că aceasta este o funcție particularizată într-un modul VBA. Numele argumentelor încadrate între paranteze, Cantitate și prețsunt substituenți pentru valorile pe care se bazează calculul discountului.

Exemplu de formular de comandă cu o funcție particularizată

Instrucțiunea IF din următorul bloc de cod examinează argumentul cantitativ și determină dacă numărul de elemente 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 elemente vândute este mai mare sau egal cu 100, VBA execută următoarea instrucțiune, care înmulțește valoarea cantitativă cu valoarea prețului , apoi înmulțește rezultatul cu 0,1:

Discount = quantity * price * 0.1

Rezultatul este stocat ca reducerevariabilă. O instrucțiune VBA care stochează o valoare dintr-o variabilă se numește o instrucțiune de atribuire , deoarece evaluează expresia din partea dreaptă a semnului egal și atribuie rezultatul la numele variabil din stânga. Deoarece reducerea variabilă are același nume cu procedura de funcție, valoarea stocată în variabila este returnată la formula foii de lucru DENUMITĂ funcția discount.

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

Discount = 0

În cele din urmă, următoarea instrucțiune rotunjește valoarea atribuită variabilei de reducere la două zecimale:

Discount = Application.Round(Discount, 2)

VBA nu are funcția ROUND, dar Excel o face. Prin urmare, pentru a utiliza runda în această instrucțiune, spuneți VBA să caute metoda Round (funcția) în obiectul Application (Excel). Faceți acest lucru adăugând aplicația Word înaintea cuvântului Round. Utilizați această sintaxă de câte ori aveți nevoie pentru a accesa o funcție Excel dintr-un modul VBA.

O funcție particularizată trebuie să înceapă cu o instrucțiune de funcție și să se termine cu o instrucțiune pentru funcția end. În plus față de numele funcției, instrucțiunea funcției specifică, de obicei, unul sau mai multe argumente. Cu toate acestea, puteți crea o funcție fără argumente. Excel include mai multe funcții predefinite-RAND și acum, de exemplu, care nu utilizează argumente.

Urmând instrucțiunea funcției, o procedură de funcție include una sau mai multe declarații VBA care iau decizii și efectuează calcule utilizând 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 funcția. Această valoare este returnată la formula care apelează funcția.

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țiile particularizate nu sunt permise pentru a face 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 redimensiona ferestrele, edita o formulă într-o celulă sau modifica opțiunile de font, culoare sau model pentru textul dintr-o celulă. Dacă includeți "acțiune" cod de acest tip într-o procedură de funcție, funcția returnează #VALUE! .

Singura acțiune pe care o poate face o procedură (pe lângă calculele efectuate) afișează o casetă de dialog. Puteți utiliza o instrucțiune inputboxfuncția într-o funcție particularizată ca mijloc de a primi input de la utilizatorul care execută funcția. Puteți utiliza o instrucțiune MsgBox ca mijloc de transmitere a informațiilor către utilizator. De asemenea, puteți utiliza casete de dialog particularizate sau utilizator, dar acesta este un subiect care depășește domeniul de aplicare a acestei introduceri.

Chiar și macrocomenzile simple și funcțiile particularizate pot fi greu de citit. Puteți să le faceți mai ușor de înțeles tastând text explicativ sub formă de comentarii. Adăugați comentarii prin precedând textul explicativ cu un apostrof. De exemplu, următorul exemplu afișează funcția DISCOUNT cu comentarii. Adăugarea comentariilor, cum ar fi acestea, vă ajută să vă mențineți mai ușor codul VBA pe măsură ce trece timpul. Dacă trebuie să efectuați o modificare a codului în viitor, veți avea un timp mai ușor de înțeles ce ați făcut inițial.

Exemplu de funcție VBA cu comentarii

Un apostrof spune Excel să ignore totul la dreapta de pe aceeași linie, astfel încât să puteți crea comentarii fie pe linii, fie în partea dreaptă a liniilor care conțin cod VBA. Este posibil să începeți un bloc relativ lung de cod cu un comentariu care explică scopul său general, apoi să utilizați comentarii în linie pentru a documenta declarații individuale.

O altă modalitate de a documenta macrocomenzile și funcțiile particularizate este să le dați nume descriptive. De exemplu, mai degrabă decât să denumiți o etichetăde macrocomenzi, o puteți denumi MonthLabels pentru a descrie mai specific scopul pe care îl servește macrocomanda. Utilizarea numelor descriptive pentru macrocomenzi și funcțiile particularizate este utilă mai ales atunci când ați creat mai multe proceduri, mai ales dacă creați proceduri care au scopuri similare, dar nu identice.

Modul în care documentați macrocomenzile și funcțiile particularizate este o chestiune de preferință personală. Ce este important este să adoptați o anumită metodă de documentație și să o utilizați în mod consecvent.

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ă registrul de lucru nu este deschis, veți primi un #NAME? eroare atunci când încercați să utilizați funcția. Dacă faceți referire la funcția din alt registru de lucru, trebuie să precede numele funcției cu numele registrului de lucru în care se află funcția. De exemplu, dacă creați o funcție denumită reducere într-un registru de lucru denumit personal. xlsb și apelați această funcție din alt registru de lucru, trebuie să tastați = personal. xlsb! discount (), nu pur și simplu = discount ().

Puteți să vă Salvați câteva apăsări de taste (și erori posibile de tastare), selectând funcțiile particularizate din caseta de dialog Inserare funcție. Funcțiile particularizate apar în categoria definit de utilizator:

insert function dialog box

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

  1. După ce ați creat funcțiile necesare, faceți clic pe fișier > Salvare ca.

    În Excel 2007, faceți clic pe butonul Microsoft Officeși faceți clic pe Salvare ca

  2. În caseta de dialog Salvare ca , deschideți lista verticală Salvare cu tipul și selectați programul de completare Excel. Salvați registrul de lucru sub un nume recunoscut, cum ar fi MyFunctions, în folderul programe de completare . Caseta de dialog Salvare ca va propune acel folder, Deci tot ce trebuie să faceți este să acceptați locația implicită.

  3. După ce ați salvat registrul de lucru, faceți clic pe fișier > Opțiuni Excel.

    În Excel 2007, faceți clic pe butonul Microsoft Officeși faceți clic pe Opțiuni Excel.

  4. În caseta de dialog Opțiuni Excel , faceți clic pe categoria programe de completare .

  5. În lista verticală gestionare , selectați programe de completare Excel. Apoi faceți clic pe butonul Salt .

  6. În caseta de dialog programe de completare , bifați caseta de selectare de lângă numele pe care l-ați utilizat pentru a salva registrul de lucru, așa cum se arată mai jos.

    add-ins dialog box

  1. După ce ați creat funcțiile necesare, faceți clic pe fișier > Salvare ca.

  2. În caseta de dialog Salvare ca , deschideți lista verticală Salvare cu tipul și selectați programul de completare Excel. Salvați registrul de lucru sub un nume recunoscut, cum ar fi MyFunctions.

  3. După ce ați salvat registrul de lucru, faceți clic pe instrumente > programe de completare Excel.

  4. În caseta de dialog programe de completare , selectați butonul Răsfoire pentru a găsi programul de completare, faceți clic pe Deschidere, apoi bifați caseta de lângă programul de completare, în caseta programe de completare disponibile .

După ce urmați acești pași, funcțiile particularizate vor fi disponibile de fiecare dată când derulează Excel. Dacă doriți să 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 denumit după fișierul de program de completare. Programul de completare va avea extensia. XLAM.

named module in vbe

Dacă faceți dublu clic pe modulul respectiv în Explorer, se determină ca Visual Basic Editor să afișeze codul funcției. Pentru a adăuga o funcție nouă, poziționați punctul de inserare după instrucțiunea funcției end care termină Ultima funcție în fereastra codului și începeți să tastați. Puteți să creați câte funcții aveți nevoie în acest mod și acestea vor fi întotdeauna disponibile în categoria definit de utilizator din caseta de dialog Inserare funcție .

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

Aveți nevoie de ajutor suplimentar?

Puteți întreba întotdeauna un expert de la Excel Tech Community, puteți obține asistență de la comunitatea Answers sau puteți sugera o caracteristică nouă sau o îmbunătățire pe Excel UserVoice.

Notă:  Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Ne puteți spune dacă informațiile au fost utile? Aici se află articolul în limba engleză, ca referință.

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×