Formule matrice dinamice și comportament al matricelor vărsate

Formulele Excel care returnează un set de valori, denumit și matrice, returnează aceste valori la celulele învecinate. Acest comportament se numește vărsare.

Formulele care pot returna matrice de dimensiune variabilă se numesc formule matrice dinamică. Formulele care returnează în prezent matrice care se varsă cu succes pot fi denumite formule matrice vărsată.

Iată câteva note pentru a vă ajuta să înțelegeți și să utilizați acest tip de formule.

Ce înseamnă vărsare?

Notă: Formulele matrice mai vechi, cunoscute ca formulematrice moștenită returnează întotdeauna un rezultat cu dimensiune fixă - se varsă întotdeauna în același număr de celule. Comportamentul de vărsare descris în acest subiect nu se aplică la formulele matrice moștenite.

Vărsare înseamnă că o formulă a avut ca rezultat mai multe valori, iar acele valori au fost plasate în celulele învecinate. De exemplu, =SORT(D2:D11,1,-1), care sortează o matrice în ordine descrescătoare, ar returna o matrice corespunzătoare cu o înălțime de 10 rânduri. Dar trebuie doar să introduceți formula în celula din stânga sus, sau în F2 în acest caz, iar aceasta se va vărsa automat în celula F11.

Sortați valorile din celulele D2:D11 cu =SORT(D2:D11,1,-1)

Punctele-cheie

Atunci când apăsați pe Enter pentru a confirma formula, Excel va dimensiona dinamic zona de ieșire pentru dvs. și va plasa rezultatele în fiecare celulă din zona respectivă.
Dacă scrieți o formulă matrice dinamică pentru a acționa asupra unei liste de date, poate fi util să o plasați într-un tabel Excel, apoi să utilizați referințe structurate pentru a face referire la date. Acest lucru se întâmplă deoarece referințele structurate se ajustează automat pe măsură ce rândurile sunt adăugate sau eliminate din tabel.
Formulele matrice vărsate nu sunt acceptate în tabelele Excel propriu-zise, așa că ar trebui să le plasați în grilă în afara Tabelului. Tabelele sunt cele mai potrivite pentru păstrarea rândurilor și coloanelor de date independente.
După ce introduceți o formulă matrice vărsată, atunci când selectați orice celulă din zona de vărsare, Excel va plasa o bordură evidențiată în jurul zonei. Bordura va dispărea atunci când selectați o celulă din afara zonei.
Numai prima celulă din zona de vărsare este editabilă. Dacă selectați o altă celulă din zona de vărsare, formula va fi vizibilă în bara de formule, dar textul este „fantomă” și nu poate fi modificat. Dacă trebuie să actualizați formula, ar trebui să selectați celula din stânga sus din zona matrice, să o modificați după cum este necesar, apoi Excel va actualiza automat restul zonei de vărsare atunci când apăsați pe Enter.
Suprapunere de formule - Formulele matrice nu pot fi introduse dacă există ceva care blochează zona de ieșire. iar dacă se întâmplă acest lucru, Excel va returna o eroare #SPILL! care indică faptul că există un blocaj. Dacă eliminați blocajul, formula se va vărsa așa cum vă așteptați. În exemplul de mai jos, zona de ieșire a formulei se suprapune peste altă zonă cu date și este afișată cu o bordură punctată care suprapune celule cu valori care indică faptul că nu se poate vărsa. Eliminați datele de blocare sau copiați-le în altă parte, iar formula se va vărsa conform așteptărilor.
Formulele matrice moștenite introduse prin CTRL+SHIFT+ENTER (CSE) sunt acceptate în continuare din motive de compatibilitate inversă, dar nu ar mai trebui utilizate. Dacă doriți, puteți efectua conversia formulelor matrice moștenite în formule matrice dinamice, localizând prima celulă din zona matrice, copiați textul formulei, ștergeți întreaga zonă a matricei moștenite, apoi introduceți din nou formula în celula din stânga sus. Înainte de a face upgrade formulelor matrice moștenite la formule matrice dinamice, trebuie să țineți cont de câteva diferențe de calculîntre cele două.
Excel oferă suport limitat pentru matricele dinamice dintre registrele de lucru, iar acest scenariu este acceptat doar când sunt deschise ambele registre de lucru. Dacă închideți registrul de lucru sursă, toate formulele matrice dinamice legate vor returna o eroare #REF! când sunt reîmprospătate.