cuntenutu
Chì sò i matrici dinamichi
In settembre di u 2018, Microsoft hà publicatu una aghjurnazione chì aghjunghje un strumentu completamente novu à Microsoft Excel: Array Dinamiche è 7 funzioni novi per travaglià cun elli. Queste cose, senza esagerazione, cambianu radicalmente tutte e tecniche abituale di travaglià cù formule è funzioni è cuncernate, literalmente, ogni utilizatore.
Cunsiderate un esempiu simplice per spiegà l'essenza.
Eppo supponi chì avemu un tavulu simplice cù dati nantu à a cità-mesi. Chì succede se selezziunà ogni cellula viota à a diritta di u fogliu è inserisce una formula chì ùn liga micca à una cellula, ma immediatamente à un intervallu?
In tutte e versioni precedenti di Excel, dopu à cliccà Rinsignate avemu avutu u cuntenutu di una sola prima cellula B2. Cumu altru ?
Ebbè, o saria pussibile imballà sta gamma in una sorta di funzione di aggregazione cum'è = SUM (B2: C4) è uttene un gran tutale per questu.
Se avemu bisognu di operazioni più cumplesse cà una somma primitiva, cum'è l'estrazione di valori unichi o Top 3, allora duvemu entre in a nostra formula cum'è una formula di array utilizendu una scurciatoia di tastiera. Ctrl+Shift+Rinsignate.
Avà tuttu hè diversu.
Avà dopu à entre in una tale formula, pudemu simpricimenti cliccà Rinsignate – è uttene subitu subitu tutti i valori uXNUMXbuXNUMXb à quale avemu riferitu:
Questu ùn hè micca magia, ma i novi array dinamichi chì Microsoft Excel hà avà. Benvenuti à u novu mondu 🙂
Funzioni di travaglià cù arrays dinamichi
Tecnicamente, u nostru array dinamicu tutale hè almacenatu in a prima cellula G4, riempiendu u numeru necessariu di cellule à a diritta è in basso cù i so dati. Se selezziunate qualsiasi altra cellula in a matrice, u ligame in a barra di formula serà inattivu, chì mostra chì simu in una di e cellule "figlioli":
Un tentativu di sguassà una o più cellule "figlioli" ùn porta micca à nunda - Excel ricalcula immediatamente è li riempia.
À u listessu tempu, pudemu riferite in modu sicuru à queste cellule "figlioli" in altre formule:
Se copiate a prima cellula di un array (per esempiu, da G4 à F8), allora tutta l'array (i so referenze) si move in a listessa direzzione cum'è in e formule regular:
Se avemu bisognu di spustà u array, allora serà abbastanza per spustà (cù u mouse o una cumminazione di Ctrl+X, Ctrl+V), di novu, solu a prima cellula principale G4 - dopu, serà trasfiruta à un novu locu è u nostru array tutale serà allargatu novu.
Sè avete bisognu di riferite in un altru locu nantu à u fogliu à a matrice dinamica creata, pudete aduprà u caratteru speciale # ("libbra") dopu l'indirizzu di a so cellula principale:
Per esempiu, avà pudete fà facilmente una lista dropdown in una cellula chì si riferisce à a matrice dinamica creata:
Errori di matrice dinamica
Ma chì succede s'ellu ùn ci hè micca abbastanza spaziu per espansione l'array, o s'ellu ci sò cellule digià occupate da altre dati in u so percorsu? Incuntrà un tipu fundamentale novu d'errori in Excel - #TRASFERIMENTO ! (#SPILL!):
Cum'è sempre, se clicchemu nantu à l'icona cù un diamante giallu è un segnu d'esclamazione, averemu una spiegazione più dettagliata di a fonte di u prublema è pudemu truvà rapidamente cellule interferenti:
Errori simili accaderanu se l'array s'alluntana da u fogliu o colpisce una cellula fusionata. Sè vo sguassate l'ostaculu, allura tuttu sarà subitu currettu nantu à a mosca.
Array dinamichi è tabelle intelligenti
Se l'array dinamica punta à una tavola "intelligente" creata da una scurciata di tastiera Ctrl+T o di Home - Formate cum'è una tavola (Casa - Formate cum'è Table), Tandu eredite ancu a so qualità principale - auto-sizing.
Quandu aghjunghjenu novi dati à u fondu o à a diritta, a tavola intelligente è a gamma dinamica si stende ancu automaticamente:
Tuttavia, ci hè una limitazione: ùn pudemu micca aduprà una riferenza di intervallu dinamicu in forumulas in una tavola intelligente:
Array dinamichi è altre funzioni di Excel
Va bè, dite. Tuttu chistu hè interessante è divertente. Ùn ci hè bisognu, cum'è prima, di stende manualmente a formula cù una riferimentu à a prima cellula di a gamma originale è à a diritta è tuttu ciò. È questu hè tuttu?
Not calmu.
I matrici dinamichi ùn sò micca solu un altru strumentu in Excel. Avà sò incrustati in u core (o cervellu) di Microsoft Excel - u so mutore di calculu. Questu significa chì altre formule Excel è funzioni familiari per noi avà supportanu ancu u travagliu cù arrays dinamichi. Fighjemu uni pochi di esempii per dà una idea di a prufundità di i cambiamenti chì sò accaduti.
Traspurtà
Per traspone un intervallu (scambià fila è colonne) Microsoft Excel hà sempre avutu una funzione integrata TRANSP (TRASPOSI). In ogni casu, per usà, deve prima selezziunà currettamente u intervallu per i risultati (per esempiu, se l'input era un intervallu di 5 × 3, allora duvete avè sceltu 3 × 5), dopu entre in a funzione è appughjà u buttone. cumminazzioni Ctrl+Shift+Rinsignate, perchè puderia travaglià solu in u modu di formula array.
Avà pudete solu selezziunate una cellula, inserite a stessa formula è cliccate nantu à u normale Rinsignate - una matrice dinamica farà tuttu da sè stessu:
Tavola di multiplicazione
Questu hè l'esempiu chì aghju datu quandu mi era dumandatu di visualizà i benefici di e formule di array in Excel. Avà, per calculà tutta a tavola pitagorica, hè abbastanza per stà in a prima cellula B2, entre quì una formula chì multiplica dui array (set verticale è horizontale di numeri 1..10) è simpricimenti cliccate nant'à Rinsignate:
Incollatura è cunversione di casu
Arrays pò micca solu esse multiplicate, ma ancu incollatu cù l'operatore standard & (ampersand). Eppo supponi chì avemu bisognu d'estrattà u primu è l'apellido da duie colonne è corregge u casu di salta in i dati originali. Facemu questu cù una formula corta chì forma tutta l'array, è poi applichemu a funzione PROPNACH (PROPRI)per mette in ordine u registru :
Conclusioni Top 3
Eppo supponi chì avemu una mansa di numeri da quale vulemu derivà i primi trè risultati, organizendu in ordine discendente. Avà questu hè fattu da una formula è, di novu, senza alcunu Ctrl+Shift+Rinsignate cum'è prima:
Se vulete chì i risultati ùn sò micca posti in una colonna, ma in una fila, allora hè abbastanza per rimpiazzà i dui punti (separatore di linea) in questa formula cù un puntu e virgola (separatore di elementi in una linea). In a versione inglese di Excel, sti separatori sò punti e virgule è virgule, rispettivamente.
VLOOKUP estrae parechje colonne à una volta
funzioni VPR (CERCA V) Ora pudete tirà i valori micca da una, ma da parechje culonne à una volta - solu specificate i so numeri (in ogni ordine desideratu) cum'è un array in u terzu argumentu di a funzione:
Funzione OFFSET chì torna un array dinamicu
Una di e funzioni più interessanti è utili (dopu à VLOOKUP) per l'analisi di dati hè a funzione DISPOSIZIONE (OFFSET), à quale aghju dedicatu à un tempu un capitulu sanu in u mo libru è un articulu quì. A difficultà per capiscenu è ammaistrà sta funzione hè sempre stata chì hà tornatu un array (range) di dati com'è u risultatu, ma ùn pudemu micca vede, perchè Excel ùn sapia ancu cumu travaglià cù array fora di a scatula.
Avà stu prublema hè in u passatu. Vede cumu avà, aduprendu una sola formula è una matrice dinamica restituita da OFFSET, pudete estrattà tutte e fila per un pruduttu determinatu da ogni tavola ordinata:
Fighjemu i so argumenti:
- A1 - cellula di partenza (puntu di riferimentu)
- ПОИСКПОЗ(F2;A2:A30;0) - calculu di u shift da a cellula di partenza in giù - à a prima col trovata.
- 0 - sposta di a "finestra" à a diritta relative à a cellula di partenza
- СЧЁТЕСЛИ(A2:A30;F2) - calculu di l'altitudine di a "finestra" tornata - u numeru di linii induve ci hè col.
- 4 - dimensione di a "finestra" orizontale, vale à dì output 4 culonni
Nove Funzioni per Arrays Dinamici
In più di sustene u mecanismu di array dinamica in e funzioni antichi, parechje funzioni completamente novi sò state aghjunte à Microsoft Excel, sharpened specificamente per travaglià cù arrays dinamichi. In particulare, questi sò:
- Pendentif (SORT) - ordina a gamma di input è produce un array dinamicu nantu à a output
- SORTPO (SORTBY) - pò sorte un intervallu per valori da un altru
- FILTER (FILTRE) - recupera fila da a gamma di fonte chì risponde à e cundizioni specificate
- UNIK (UNIQUE) - estrae i valori unichi da un intervallu o elimina i duplicati
- SLMASSIVE (RANDARRAY) - genera una matrice di numeri aleatorii di una certa dimensione
- DOPO NATA (SEQUENZA) - forma un array da una sequenza di numeri cù un passu datu
Più nantu à elli - un pocu dopu. Valenu a pena un articulu separatu (è micca unu) per un studiu pensativu 🙂
cuegghiè
Se avete lettu tuttu ciò chì hè scrittu sopra, allora pensu chì avete digià capitu a scala di i cambiamenti chì sò accaduti. Tanti cose in Excel ponu avà esse fattu più faciule, più faciule è più logicu. Devu ricunnosce chì sò un pocu scunfittatu da quanti articuli duverà avà esse curretti quì, in stu situ è in i mo libri, ma sò prontu à fà questu cun un cori ligeru.
Riassumendu i risultati, più arrays dinamichi, pudete scrive u seguente:
- Pudete scurdà di a cumminazzioni Ctrl+Shift+Rinsignate. Excel avà ùn vede nisuna differenza trà "formule regulare" è "formule array" è li tratta di listessa manera.
- Circa a funzione SUMPRODUCT (SUMPRODUCT), chì era prima utilizatu per inserisce formule di array senza Ctrl+Shift+Rinsignate pudete ancu scurdà - avà hè abbastanza faciule SUM и Rinsignate.
- I tavulini intelligenti è e funzioni familiari (SUM, IF, VLOOKUP, SUMIFS, etc.) supportanu ancu cumplettamente o parzialmente array dinamichi.
- Ci hè una cumpatibilità inversa: se apre un libru di travagliu cù arrays dinamichi in una versione antica di Excel, si trasformanu in formule di array (in curly braces) è cuntinueghjanu à travaglià in u "vechju stile".
Truvatu qualchì numeru minus:
- Ùn pudete micca sguassà singuli fila, culonne o cellule da un array dinamicu, vale à dì vive cum'è una sola entità.
- Ùn pudete micca sorte un array dinamicu in u modu di solitu Dati - Sorting (Dati - Sort). Ci hè avà una funzione speciale per questu. Pendentif (SORT).
- Un intervallu dinamicu ùn pò micca esse trasfurmatu in una table intelligente (ma pudete fà un intervallu dinamicu basatu annantu à una table intelligente).
Di sicuru, questu ùn hè micca a fine, è sò sicuru chì Microsoft continuarà à migliurà stu mecanismu in u futuru.
Induve possu scaricà?
È infine, a quistione principale 🙂
Microsoft hà annunziatu per a prima volta è hà dimustratu una vista previa di arrays dinamichi in Excel in settembre 2018 in una cunferenza. Ignite. In i prossimi mesi, ci hè statu una prova approfondita è un run-in di novi funzioni, prima misgiu impiegati di Microsoft stessu, è dopu nantu à i testatori vuluntarii da u circulu di Office Insiders. Quist'annu, l'aghjurnamentu chì aghjunghje arrays dinamichi hà cuminciatu à esse gradualmente implementatu à l'abbonati regulari di Office 365. Per esempiu, l'aghju ricivutu solu in Aostu cù u mo abbonamentu Office 365 Pro Plus (Mistu Mensu).
Se u vostru Excel ùn hà micca ancu array dinamichi, ma vulete veramente travaglià cun elli, allora ci sò e seguenti opzioni:
- Sè avete un abbonamentu à Office 365, pudete simpricimenti aspittà finu à chì sta aghjurnazione vi ghjunghje. A rapidità chì succede dipende da quantu spessu l'aghjurnamenti sò mandati à u vostru Uffiziu (una volta à l'annu, una volta ogni sei mesi, una volta à u mese). Se tenete un PC corporativu, pudete dumandà à u vostru amministratore di stabilisce l'aghjurnamenti per esse scaricati più spessu.
- Pudete unisce à i ranchi di quelli volontari di teste Office Insiders - allora sarete u primu à riceve tutte e funzioni è e funzioni novi (ma ci hè una chance d'aumentu di buggy in Excel, sicuru).
- Se ùn avete micca un abbonamentu, ma una versione boxed standalone di Excel, allora duverete aspittà finu à a liberazione di a prossima versione di Office è Excel in 2022, almenu. L'utilizatori di tali versioni ricevenu solu aghjurnamenti di sicurezza è correzioni di bug, è tutti i novi "boni" avà vanu solu à l'abbonati di Office 365. Triste ma vera 🙂
In ogni casu, quandu i matrici dinamichi appariscenu in u vostru Excel - dopu à questu articulu, sarete prontu per questu 🙂
- Chì sò e formule di array è cumu si usanu in Excel
- Finestra (range) summation usendu a funzione OFFSET
- 3 Modi per traspone una Tavola in Excel