Funcția de matrice UDF cu argumente în Excel
După cum sugerează titlul, vom învăța cum să creăm o funcție definită de utilizator în Excel care returnează o matrice. Am învățat deja cum să creăm o funcție definită de utilizator în VBA. Deci, fără a pierde timp, să începem cu tutorialul.
Ce este o funcție Array?
Funcțiile matrice sunt funcțiile care returnează o matrice atunci când sunt utilizate. Aceste funcții sunt utilizate cu combinațiile de taste CTRL + SHIFT + ENTER și de aceea preferăm apelarea funcției matrice sau formule ca funcție și formule CSE.
Funcția matricei Excel este adesea formule matrice multicelulare. Un exemplu este funcția TRANSPOSE.
Crearea unei funcții matrice UDF în VBA
Deci, scenariul este că vreau doar să returnez primele 3 numere pare utilizând funcția ThreeEven ().
Codul va arăta astfel.
Funcție ThreeEven () Ca întreg () 'definește matricea Numere dim (2) Ca întreg' Atribuie valori numerelor matrice (0) = 0 numere (1) = 2 numere (2) = 4 'returnează valori ThreeEven = numere Sfârșit Funcție
Să folosim această funcție pe foaia de lucru.
Puteți vedea asta, mai întâi selectăm trei celule (pe orizontală, pentru verticală trebuie să folosim matricea bidimensională. O avem acoperită mai jos.). Apoi începem să ne scriem formula. Apoi apăsăm CTRL + SHIFT + ENTER. Aceasta umple celulele selectate cu valorile matricei.
Notă:
- În practică, nu vei ști de câte celule vei avea nevoie. În acest caz, selectați întotdeauna mai multe celule decât lungimea matricei așteptate. Funcția va umple celulele cu matrice și celulele suplimentare vor afișa o eroare # N / A.
- În mod implicit, această funcție de matrice returnează valori într-o matrice orizontală. Dacă încercați să selectați celule verticale, toate celulele vor afișa numai prima valoare a matricei.
Cum functioneaza?
Pentru a crea o funcție matrice trebuie să urmați această sintaxă.
Funcție funcțieNume (variabile) Ca returnType () dim resultArray (lungime) ca dateType 'Atribuiți valori matricei aici functionName = resultArray Final Function
Declarația funcției trebuie să fie definită mai sus. Aceasta a declarat că este o funcție matrice.
În timp ce îl utilizați pe foaia de lucru, trebuie să utilizați combinația de taste CTRL + SHIFT + ENTER. În caz contrar, va returna prima valoare a matricei.
Funcția VBA Array pentru a returna matricea verticală
Pentru a face funcția matricei UDF să funcționeze pe verticală, nu trebuie să faceți prea multe. Declarați doar tablourile ca o matrice bidimensională. Apoi, în prima dimensiune adăugați valorile și lăsați cealaltă dimensiune necompletată. Așa o faceți:
Funcția ThreeEven () As Integer () 'define array array Dim numbers (2,0) As Integer' Atribuie valori numerelor array (0,0) = 0 numere (1,0) = 2 numere (2,0) = 4 ' returnează valori ThreeEven = numere Funcție de sfârșit
Acesta este modul în care îl utilizați pe foaia de lucru.
Funcția de matrice UDF cu argumente în Excel
În exemplele de mai sus, am imprimat pur și simplu valori statice sumare pe foaie. Să presupunem că vrem ca funcția noastră să accepte un argument de gamă, să efectueze unele operații asupra lor și să returneze matricea rezultată.
Exemplu Adăugați „-done” la fiecare valoare din interval
Acum, știu că acest lucru se poate face cu ușurință, dar doar pentru a vă arăta cum puteți utiliza funcțiile matrice VBA definite de utilizator pentru a rezolva problemele.
Deci, aici vreau o funcție matrice care să ia un interval ca argument și să adauge „-done” la fiecare valoare din interval. Acest lucru se poate face cu ușurință folosind funcția de concatenare, dar vom folosi o funcție matrice aici.
Funcția CONCATDone (rng As Range) As Variant () Dim resulArr () As Variant 'Create a collection Dim col As New Collection' Adăugarea valorilor la colecție On Error Reîncepeți Next pentru fiecare v In rng col.Add v Next On Error GoTo 0 ' finalizarea operației pe fiecare valoare și adăugarea lor la Array ReDim resulArr (col.Count - 1, 0) Pentru i = 0 La col.Count - 1 resulArr (i, 0) = col (i + 1) & "-făcut" Următor CONCATDone = funcția de finalizare resulArr
Explicaţie:
Funcția de mai sus va accepta un interval ca argument și va adăuga „-done” la fiecare valoare din interval.
Puteți vedea că am folosit o colecție VBA aici pentru a păstra valorile matricei și apoi am făcut operațiunea noastră pe fiecare valoare și le-am adăugat înapoi pe o matrice bidimensională.
Deci da, băieți, așa puteți crea o funcție de matrice VBA personalizată care poate returna o matrice. Sper că a fost suficient de explicativ. Dacă aveți întrebări cu privire la acest articol, puneți-l în secțiunea de comentarii de mai jos.
Faceți clic pe linkul de mai jos pentru a descărca fișierul de lucru:
Creați funcția VBA pentru a returna matriceaMatrice în Excel Formul | Aflați ce matrice sunt în Excel.
Cum se creează o funcție definită de utilizator prin VBA | Aflați cum să creați funcții definite de utilizator în Excel
Utilizarea unei funcții definite de utilizator (UDF) dintr-un alt registru de lucru utilizând VBA în Microsoft Excel | Utilizați funcția definită de utilizator într-un alt registru de lucru Excel
Returnează valorile de eroare de la funcțiile definite de utilizator utilizând VBA în Microsoft Excel | Aflați cum puteți returna valorile de eroare dintr-o funcție definită de utilizator
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea
Funcția VLOOKUP în Excel
COUNTIF în Excel 2016
Cum se utilizează funcția SUMIF în Excel