Cuprins
Ce sunt matricele dinamice
În septembrie 2018, Microsoft a lansat o actualizare care adaugă un instrument complet nou la Microsoft Excel: Dynamic Arrays și 7 funcții noi pentru lucrul cu acestea. Aceste lucruri, fără exagerare, schimbă radical toată tehnica obișnuită de lucru cu formule și funcții și privesc, la propriu, fiecare utilizator.
Luați în considerare un exemplu simplu pentru a explica esența.
Să presupunem că avem un tabel simplu cu date despre oraș-luni. Ce se va întâmpla dacă selectăm orice celulă goală din dreapta foii și introducem în ea o formulă care se leagă nu de o celulă, ci imediat de un interval?
În toate versiunile anterioare de Excel, după ce faceți clic pe Intrați am obține conținutul unei singure prime celule B2. Cum altfel?
Ei bine, sau ar fi posibil să includeți acest interval într-un fel de funcție de agregare precum =SUM(B2:C4) și să obțineți un total general pentru el.
Dacă avem nevoie de operații mai complexe decât o sumă primitivă, cum ar fi extragerea de valori unice sau Top 3, atunci ar trebui să introducem formula noastră ca formulă matrice folosind o comandă rapidă de la tastatură Ctrl+Schimba+Intrați.
Acum totul este diferit.
Acum, după ce introducem o astfel de formulă, putem pur și simplu să facem clic pe Intrați – și obțineți ca rezultat imediat toate valorile uXNUMXbuXNUMXbla care ne-am referit:
Aceasta nu este magie, ci noile matrice dinamice pe care le are acum Microsoft Excel. Bun venit în lumea nouă 🙂
Caracteristici de lucru cu matrice dinamice
Din punct de vedere tehnic, întreaga noastră matrice dinamică este stocată în prima celulă G4, umplând numărul necesar de celule în dreapta și în jos cu datele sale. Dacă selectați orice altă celulă din matrice, atunci linkul din bara de formule va fi inactiv, arătând că ne aflăm într-una dintre celulele „copil”:
O încercare de a șterge una sau mai multe celule „copil” nu va duce la nimic – Excel le va recalcula imediat și le va completa.
În același timp, ne putem referi în siguranță la aceste celule „copil” în alte formule:
Dacă copiați prima celulă a unei matrice (de exemplu, de la G4 la F8), atunci întreaga matrice (referențele sale) se va deplasa în aceeași direcție ca în formulele obișnuite:
Dacă trebuie să mutăm matricea, atunci va fi suficient să mutăm (cu mouse-ul sau o combinație de Ctrl+X, Ctrl+V), din nou, doar prima celulă principală G4 – după aceasta, va fi transferată într-un loc nou și întreaga noastră matrice va fi extinsă din nou.
Dacă trebuie să vă referiți în altă parte a foii la matricea dinamică creată, atunci puteți utiliza caracterul special # („liră”) după adresa celulei sale principale:
De exemplu, acum puteți face cu ușurință o listă derulantă într-o celulă care se referă la matricea dinamică creată:
Erori de matrice dinamică
Dar ce se întâmplă dacă nu există suficient spațiu pentru a extinde matricea sau dacă există celule deja ocupate de alte date în calea sa? Faceți cunoștință cu un tip fundamental nou de erori în Excel - #TRANSFER! (#SPILL!):
Ca întotdeauna, dacă facem clic pe pictograma cu un diamant galben și un semn de exclamare, vom obține o explicație mai detaliată a sursei problemei și putem găsi rapid celule care interferează:
Erori similare vor apărea dacă matricea iese din foaie sau lovește o celulă îmbinată. Dacă eliminați obstacolul, atunci totul va fi corectat imediat din mers.
Matrice dinamice și tabele inteligente
Dacă matricea dinamică indică un tabel „inteligent” creat de o comandă rapidă de la tastatură Ctrl+T sau prin Acasă – Formatați ca tabel (Acasă — Format ca tabel), atunci va moșteni și calitatea sa principală – dimensionarea automată.
Când adăugați date noi în partea de jos sau în dreapta, tabelul inteligent și intervalul dinamic se vor întinde automat:
Cu toate acestea, există o limitare: nu putem folosi o referință în intervalul dinamic în forumule în interiorul unui tabel inteligent:
Matrice dinamice și alte caracteristici Excel
Bine, zici tu. Toate acestea sunt interesante și amuzante. Nu este nevoie, ca înainte, să întindeți manual formula cu o referință la prima celulă din intervalul original în jos și la dreapta și toate astea. Și asta e tot?
Nu chiar.
Matricele dinamice nu sunt doar un alt instrument în Excel. Acum sunt încorporate în chiar inima (sau creierul) Microsoft Excel - motorul său de calcul. Aceasta înseamnă că și alte formule și funcții Excel familiare nouă acceptă acum și lucrul cu matrice dinamice. Să aruncăm o privire la câteva exemple pentru a vă face o idee despre profunzimea schimbărilor care au avut loc.
Transpune
Pentru a transpune un interval (schimbați rânduri și coloane), Microsoft Excel a avut întotdeauna o funcție încorporată TRANSP (TRANSPUNE). Cu toate acestea, pentru a-l utiliza, trebuie mai întâi să selectați corect intervalul pentru rezultate (de exemplu, dacă intrarea a fost un interval de 5×3, atunci trebuie să fi selectat 3×5), apoi să introduceți funcția și să apăsați tasta combinaţie Ctrl+Schimba+Intrați, deoarece ar putea funcționa numai în modul de formulă matrice.
Acum puteți doar să selectați o celulă, să introduceți aceeași formulă în ea și să faceți clic pe normal Intrați - matricea dinamică va face totul de la sine:
Tabelul înmulțirii
Acesta este exemplul pe care l-am dat când mi s-a cerut să vizualizez beneficiile formulelor matrice în Excel. Acum, pentru a calcula întregul tabel pitagoreic, este suficient să stai în prima celulă B2, să introduci acolo o formulă care înmulțește două matrice (set vertical și orizontal de numere 1..10) și pur și simplu faceți clic pe Intrați:
Lipirea și conversia carcasei
Matricele pot fi nu numai multiplicate, ci și lipite împreună cu operatorul standard & (ampersand). Să presupunem că trebuie să extragem numele și prenumele din două coloane și să corectăm cazul sărituri din datele originale. Facem acest lucru cu o formulă scurtă care formează întreaga matrice și apoi îi aplicăm funcția PROPNACH (PROPRIAT)pentru a face ordine în registru:
Concluzie Top 3
Să presupunem că avem o grămadă de numere din care dorim să obținem primele trei rezultate, aranjandu-le în ordine descrescătoare. Acum acest lucru se face printr-o singură formulă și, din nou, fără niciuna Ctrl+Schimba+Intrați Ca inainte:
Dacă doriți ca rezultatele să fie plasate nu într-o coloană, ci într-un rând, atunci este suficient să înlocuiți două puncte (separator de linii) în această formulă cu un punct și virgulă (separator de elemente într-o linie). În versiunea engleză a Excel, acești separatori sunt punct și virgulă, respectiv virgulă.
VLOOKUP extrage mai multe coloane simultan
funcţii VPR (CĂUTARE V) acum puteți extrage valori nu dintr-o singură, ci din mai multe coloane simultan - trebuie doar să specificați numerele lor (în orice ordine dorită) ca o matrice în al treilea argument al funcției:
Funcția OFFSET care returnează o matrice dinamică
Una dintre cele mai interesante și utile (după CĂUTARE V) funcții pentru analiza datelor este funcția ELIMINAREA (DECALAJ), căruia i-am dedicat la un moment dat un întreg capitol din cartea mea și un articol aici. Dificultatea de a înțelege și de a stăpâni această funcție a fost întotdeauna că a returnat o matrice (gamă) de date ca rezultat, dar nu am putut-o vedea, deoarece Excel încă nu știa cum să lucreze cu matrice din cutie.
Acum această problemă este în trecut. Vedeți cum acum, folosind o singură formulă și o matrice dinamică returnată de OFFSET, puteți extrage toate rândurile pentru un anumit produs din orice tabel sortat:
Să aruncăm o privire la argumentele ei:
- A1 – celula de pornire (punctul de referință)
- ПОИСКПОЗ(F2;A2:A30;0) – calculul deplasării de la celula de pornire în jos – la prima varză găsită.
- 0 – deplasarea „ferestrei” la dreapta față de celula de pornire
- СЧЁТЕСЛИ(A2:A30;F2) – calculul înălțimii „ferestrei” returnate – numărul de linii în care există varză.
- 4 — dimensiunea „ferestrei” pe orizontală, adică scoateți 4 coloane
Funcții noi pentru matrice dinamice
Pe lângă suportul mecanismului de matrice dinamică în funcțiile vechi, la Microsoft Excel au fost adăugate câteva funcții complet noi, îmbunătățite special pentru lucrul cu matrice dinamice. În special, acestea sunt:
- GRADE (FEL) – sortează domeniul de intrare și produce o matrice dinamică la ieșire
- SORTPO (FILTREAZĂ DUPĂ) – poate sorta un interval după valori de la altul
- FILTRU (FILTRU) – preia rânduri din domeniul sursă care îndeplinesc condițiile specificate
- UNIC (UNIC) – extrage valori unice dintr-un interval sau elimină duplicatele
- SLMASIV (RANDARRAY) – generează o matrice de numere aleatorii de o dimensiune dată
- DUPĂ NAȘTERE (SECVENŢĂ) — formează o matrice dintr-o succesiune de numere cu un pas dat
Mai multe despre ei – puțin mai târziu. Merită un articol separat (și nu unul) pentru un studiu atent 🙂
Concluzii
Dacă ați citit tot ce este scris mai sus, atunci cred că vă dați seama deja de amploarea schimbărilor care au avut loc. Atât de multe lucruri în Excel pot fi făcute acum mai ușor, mai ușor și mai logic. Trebuie să recunosc că sunt puțin șocat de câte articole vor trebui acum corectate aici, pe acest site și în cărțile mele, dar sunt gata să fac asta cu inima ușoară.
Rezumând rezultatele, profesioniști matrice dinamice, puteți scrie următoarele:
- Puteți uita de combinație Ctrl+Schimba+Intrați. Excel nu vede acum nicio diferență între „formule obișnuite” și „formule matrice” și le tratează în același mod.
- Despre funcție SUMPRODUCT (SUMAPRODUS), care a fost folosit anterior pentru a introduce formule matrice fără Ctrl+Schimba+Intrați poți și uita – acum este destul de ușor USM и Intrați.
- Tabelele inteligente și funcțiile familiare (SUM, IF, VLOOKUP, SUMIFS etc.) acceptă acum și în totalitate sau parțial matrice dinamice.
- Există compatibilitate inversă: dacă deschideți un registru de lucru cu matrice dinamice într-o versiune veche de Excel, acestea se vor transforma în formule matrice (în acolade) și vor continua să funcționeze în „stilul vechi”.
Am găsit un număr minusuri:
- Nu puteți șterge rânduri, coloane sau celule individuale dintr-o matrice dinamică, adică trăiește ca o singură entitate.
- Nu puteți sorta o matrice dinamică în modul obișnuit Date – Sortare (Date — Sortare). Există acum o funcție specială pentru aceasta. GRADE (FEL).
- Un interval dinamic nu poate fi transformat într-un tabel inteligent (dar puteți crea un interval dinamic bazat pe un tabel inteligent).
Desigur, acesta nu este sfârșitul și sunt sigur că Microsoft va continua să îmbunătățească acest mecanism în viitor.
Unde pot descărca?
Și în sfârșit, întrebarea principală 🙂
Microsoft a anunțat și a arătat pentru prima dată o previzualizare a matricelor dinamice în Excel în septembrie 2018, la o conferință Aprinde. În următoarele câteva luni, a avut loc o testare amănunțită și o introducere a noilor funcții, mai întâi pisici angajații Microsoft însuși și apoi pe testeri voluntari din cercul Office Insiders. În acest an, actualizarea care adaugă matrice dinamice a început să fie lansată treptat pentru abonații obișnuiți Office 365. De exemplu, l-am primit abia în august cu abonamentul meu Office 365 Pro Plus (țintit lunar).
Dacă Excel-ul dvs. nu are încă matrice dinamice, dar doriți cu adevărat să lucrați cu ele, atunci există următoarele opțiuni:
- Dacă aveți un abonament Office 365, puteți pur și simplu să așteptați până când vă ajunge această actualizare. Cât de repede se întâmplă acest lucru depinde de cât de des sunt livrate actualizările la birou (o dată pe an, o dată la șase luni, o dată pe lună). Dacă aveți un computer corporativ, puteți cere administratorului să configureze actualizări pentru a fi descărcate mai des.
- Vă puteți alătura rândurilor acelor voluntari de testare Office Insiders – atunci veți fi primul care va primi toate noile caracteristici și funcții (dar există șanse de creștere a erorilor în Excel, desigur).
- Dacă nu aveți un abonament, ci o versiune independentă de Excel în cutie, atunci va trebui să așteptați până la lansarea următoarei versiuni de Office și Excel în 2022, cel puțin. Utilizatorii unor astfel de versiuni primesc doar actualizări de securitate și remedieri de erori, iar toate noile „bunătăți” ajung acum doar la abonații Office 365. Trist dar adevărat 🙂
În orice caz, atunci când matricele dinamice apar în Excel - după acest articol, vei fi gata pentru asta 🙂
- Ce sunt formulele matrice și cum să le folosești în Excel
- Însumarea ferestrei (gamă) utilizând funcția OFFSET
- 3 moduri de a transpune un tabel în Excel