Am văzut în unul din articolele anterioare că Dynamic Arrays reprezintă cea mai mare schimbare a formulelor Excel din ultimii ani.
Și o dată cu Dynamic Arrays au fost introduse câteva formule noi pentru a valorifica această nouă funcționalitate.
Deși cu toții suntem entuziasmați de noile funcții, introducerea Dynamic Arrays este despre mai mult decât acestea.
De fapt, este o schimbare fundamentală în modul în care Excel (și utilizatorii Excel) gândesc toate formulele. În continuare, vreau să vă arăt elementele de bază necesare pentru a începe cu acest nou mod de a gând
Spilling - o singură formulă, multe valori
Funcționalitatea Dynamic Arrays a venit la pachet cu mai multe caracteristici suplimentare.
Una dintre acestea se referă la comportamentul în care formulele care au ca rezultat mai multe date se revarsă (spill) în mai multe celule în mod automat.
De asemenea, acest comportament este unul dinamic. Atunci când datele sursă se modifică, rezultatele afișate de o formula Dynamic Array se vor actualiza imediat.
Zona de celule care conține rezultate afișate de o formula Dynamic Array se numește spill range și veți observa ca această zona este evidențiată diferit de program.
E posibil ca acum să te întrebi ce formule vor avea comportamentul de spill. Formulele care în mod normal afișează un singur rezultat nu vor afișa un spill range de la sine.
În general, orice formulă care conține un argument în care în mod normal ai introduce o singură valoare este posibil să afișeze mai multe rezultate într-un spill range.
Să luăm VLOOKUP ca exemplu. Primul argument din VLOOKUP este valoarea de căutat și este, în general, o singură valoare sau celulă. Dar dacă în loc de o celulă, selectăm două sau mai multe celule la lookup_value, formula se va revărsa (spill) și va calcula rezultatul pentru fiecare element inclus în valoarea de căutare.
În exemplul de mai jos, formula Vlookup din celula I8 caută pentru fiecare țară din zona de celule H8:H13 și afișează rezultatele găsite in zona de celule I8:I13.
Astfel, pentru formulele standard regula este că acestea vor afișa rezultatele într-un spill range dacă pentru argumentele care, în mod normal, permit introducerea unei singure valori folosim mai multe valori.
Dacă selectezi una din celulele din spill range bara de formule afișează formula care conduce la rezultatul respectiv.
Dacă este celula din stânga sus, poți edita formula. Cu toate acestea, dacă selectăm o celulă, alta decât cea din stânga sus, formula este afișată cu gri și nu poate fi editată.
Eroarea #SPILL!
În general, o eroare #SPILL! apare atunci când o formulă produce mai multe rezultate, dar celulele din apropiere conțin alte date.
Dacă selectezi celula cu eroare #SPILL!, Excel va afișa spill range-ul unde încearcă să returneze rezultatul formulei. Astfel, poți vedea ce cauzează problema.
Și este alegerea ta dacă muți formula în altă celulă sau ștergi valoarea care generează eroare #SPILL!.
Erorile #SPIL! pot apărea în următoarele situații:
- Spill Range-ul este în afara celulelor disponibile în sheet;
- Spill Range-ul are o dimensiune necunoscută;
- Formula de tip Dynamic Arrays este inclusă într-un table;
- Spill Range-ul conține o merged cell;
- Spill Range-ul este atât de mare încât Excel a rămas fără memorie.
Cum folosim într-o altă formula datele din Spill range?
Până acum am văzut comportamentul de spill al Dynamic Arrays. Însă avem nevoie și de o modalitate de a face referire la toate celulele din spill range.
Din fericire, Microsoft s-a gândit deja la acest lucru și a creat o nouă metodologie de referință folosind simbolul hashtag #.
În captura de mai jos folosim exemplul anterior cu Vlookup pentru a calcula populația Medie din 2021 pentru Romania si vecinii ei adăugata in celula I15.
Formula din celula I15 este:
=Average(I8#)
Folosind I8#, facem referință la toate celulele din spill range (celule I8, I9, I10, I11, I12 și I13). Un avantaj semnificativ este că dacă spill range își schimbă dimensiunea, formula average va realiza calculul pe noul interval de celule.
Array-urile devin populare
Odată cu introducerea Dynamic Arrays, vei întâlnii cuvântul Array mult mai des ca înainte.
Acest lucru se datorează faptului că formulele Dynamic Arrays pot funcționa cu ușurință cu mai multe valori. Astfel calculele realizate pe array (matrice) devin mult mai importante.
Acum nu mai este nevoie ca la finalul unei formule de tip Array să apeși Ctrl+ Shift + Enter. Când se creează o formulă, Excel verifică dacă formula poate returna mai multe valori. Dacă da, aceasta va fi salvată automat ca formulă Dynamic Arrays. Și va face acest lucru fără adăugarea acoladelor de la formule de tip Array anterioare.
Dacă din obișnuință vei folosi o formule de tip Array și la final apeși Ctrl+ Shift + Enter, formula va fi afișată în bara de formule cu acoladele introduse. Cu toate acestea, dacă scrii din nou formula fără să apeși Ctrl+ Shift + Enter, acoladele nu vor fi introduse, dar formula funcționează în continuare si returnează același rezultat.
Concluzia este că formulele de tip array introduse cu Ctrl+ Shift + Enter (CSE) încă funcționează pentru a menține compatibilitatea, dar nu ești nevoit să apeși CSE la finalul lor pentru a funcțion în Dynamic Excel.
Concluzie
Sper că acum ai găsit răspunsuri la o parte din întrebările pe care le ai legate de Dynamic Arrays.
Într-adevăr au fost introduse o mulțime de terminologii și moduri de lucru noi. Deși modificările pot părea inițial confuze, testând vei vedea că acest lucru aduce noi puteri utilizatorilor Excel, ceea ce face Excel-ul mai ușor de utilizat.
