Descărcați exemplul de registru de lucru
Acest tutorial vă va oferi Introducere în formulele din matrice dinamice în Excel și Foi de calcul Google.
Introducere
În septembrie 2022 Microsoft a introdus Dynamic Array Formules în Excel. Scopul lor este de a facilita scrierea formulelor complexe și cu mai puține șanse de eroare.
Formulele de matrice dinamice sunt menite să înlocuiască în cele din urmă formulele de matrice, adică formule avansate care necesită utilizarea Ctrl + Shift + Enter (CSE).
Iată o comparație rapidă între Formula Array și Formula Dynamic Array folosită pentru a extrage o listă de departamente unice din lista noastră din gamă A2: A7.
Legacy Array Formula (CSE):
Următoarea formulă este introdusă în celulă D2 și este introdus apăsând Ctrl + Shift + Enter și copiindu-l din D2 la D5.
| 1 | {= IFERROR (INDEX ($ A $ 2: $ A $ 7 $, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")} |
Formula din matrice dinamică:
Următoarea formulă este introdusă numai în celulă D2 și a intrat apăsând Enter. Dintr-o privire rapidă, puteți spune cât de ușor și direct este să scrieți o Dynamic Array Formula.
| 1 | = UNIC (A2: A7) |
Disponibilitate
Începând din august 2022, formulele din matrice dinamice sunt disponibile numai pentru utilizatorii Office 365.
Vărsarea și intervalul de vărsare
Dynamic Array Formules funcționează prin returnarea mai multor rezultate într-o gamă de celule pe baza unei singure formule introduse într-o singură celulă.
Acest comportament este denumit „Vărsare” iar gama de celule în care sunt plasate rezultatele se numește „Gama de vărsare”. Când selectați orice celulă din intervalul de deversare, Excel o evidențiază cu o margine albastră subțire.
În exemplul de mai jos, formula matricei dinamice FEL este în celulă D2 iar rezultatele au fost vărsate în interval D2: D7
| 1 | = SORT (A2: A7) |
Rezultatele formulei sunt dinamice, ceea ce înseamnă că, dacă are loc o modificare în intervalul sursă, rezultatele se schimbă și intervalul de deversare se redimensionează.
#VERSARE!
Trebuie să rețineți că, dacă intervalul dvs. de deversare nu este complet gol, se returnează o eroare #SPILL.
Când selectați eroarea #SPILL, gama de deversări dorită a formulei este evidențiată cu o margine albastră punctată. Mutarea sau ștergerea datelor din celula care nu este necompletată elimină această eroare permițând formulei să se revarsă.
Notare de referință a deversării
Pentru a face referire la gama de deversări a unei formule, plasăm # simbol după referința celulei primei celule din deversare.
De asemenea, puteți face referire la deversare selectând toate celulele din intervalul de deversare și va fi creată automat o referință la deversare.
În exemplul de mai jos, am dori să numărăm numărul de angajați din firma noastră folosind formula COUNTA după ce au fost ordonate alfabetic folosind formula matricei dinamice FEL.
Intrăm în FEL formula în D2 pentru a comanda angajații din lista noastră:
| 1 | = SORT (A2: A7) |
Apoi intrăm în COUNTA formula în G2 pentru a număra numărul de angajați:
| 1 | = COUNTA (D2 #) |
Rețineți utilizarea # în D2 # pentru a se referi la rezultatele vărsate de SORT în intervalul D2: D7.
Formule noi
Mai jos este lista completă a noilor formule din matrice dinamică:
- UNIC - Returnează o listă de valori unice dintr-un interval
- FEL - Sortează valorile într-un interval
- FILTREAZĂ DUPĂ - Sortează valorile pe baza unui interval corespunzător
- FILTRU - Filtrează o gamă pe baza criteriilor furnizate
- RANDARRAY - Returnează o serie de numere aleatoare cuprinse între 0 și 1
- SECVENŢĂ - Generează o listă de numere secvențiale, cum ar fi 1, 2, 3, 4, 5
Formula Dynamic Arrays în Foi de calcul Google
Toate exemplele de mai sus funcționează exact la fel în Foi de calcul Google ca în Excel.
