Contenuti
Cosa sono gli array dinamici
A settembre 2018, Microsoft ha rilasciato un aggiornamento che aggiunge uno strumento completamente nuovo a Microsoft Excel: gli array dinamici e 7 nuove funzioni per utilizzarli. Queste cose, senza esagerare, cambiano radicalmente tutta la consueta tecnica di lavoro con formule e funzioni e riguardano, letteralmente, ogni utente.
Considera un semplice esempio per spiegare l'essenza.
Supponiamo di avere una semplice tabella con i dati sui mesi-città. Cosa accadrà se selezioniamo una cella vuota a destra del foglio e inseriamo in essa una formula che si collega non a una cella, ma immediatamente a un intervallo?
In tutte le versioni precedenti di Excel, dopo aver cliccato su entrare otterremmo il contenuto di una sola prima cella B2. In che altro modo?
Bene, o sarebbe possibile racchiudere questo intervallo in una sorta di funzione di aggregazione come =SUM(B2:C4) e ottenere un totale complessivo per esso.
Se avessimo bisogno di operazioni più complesse di una somma primitiva, come l'estrazione di valori univoci o Top 3, allora dovremmo inserire la nostra formula come una formula di matrice usando una scorciatoia da tastiera Ctrl+Shift+entrare.
Ora è tutto diverso.
Ora dopo aver inserito una tale formula, possiamo semplicemente fare clic su entrare – e ottenere di conseguenza immediatamente tutti i valori uXNUMXbuXNUMXba cui abbiamo fatto riferimento:
Questa non è magia, ma i nuovi array dinamici di cui dispone ora Microsoft Excel. Benvenuti nel nuovo mondo 🙂
Caratteristiche del lavoro con array dinamici
Tecnicamente, il nostro intero array dinamico è memorizzato nella prima cella G4, riempiendo il numero richiesto di celle a destra e in basso con i suoi dati. Se selezioni un'altra cella nell'array, il collegamento nella barra della formula sarà inattivo, mostrando che siamo in una delle celle "figlie":
Un tentativo di eliminare una o più celle "figlie" non porterà a nulla: Excel le ricalcolerà immediatamente e le riempirà.
Allo stesso tempo, possiamo tranquillamente fare riferimento a queste celle "figlie" in altre formule:
Se copi la prima cella di una matrice (ad esempio, da G4 a F8), l'intera matrice (i suoi riferimenti) si sposterà nella stessa direzione delle formule regolari:
Se abbiamo bisogno di spostare l'array, sarà sufficiente spostarlo (con il mouse o una combinazione di Ctrl+X, Ctrl+V), ancora una volta, solo la prima cella principale G4, dopo di essa verrà trasferita in una nuova posizione e l'intero array verrà nuovamente ampliato.
Se devi fare riferimento da qualche altra parte sul foglio all'array dinamico creato, puoi usare il carattere speciale # ("cancelletto") dopo l'indirizzo della sua cella iniziale:
Ad esempio, ora puoi facilmente creare un elenco a discesa in una cella che fa riferimento all'array dinamico creato:
Errori di matrice dinamica
Ma cosa succede se non c'è abbastanza spazio per espandere l'array, o se ci sono celle già occupate da altri dati nel suo percorso? Incontra un tipo fondamentalmente nuovo di errori in Excel: #TRASFERIMENTO! (#VERSARE!):
Come sempre, se clicchiamo sull'icona con un diamante giallo e un punto esclamativo, otterremo una spiegazione più dettagliata dell'origine del problema e potremo trovare rapidamente le celle che interferiscono:
Errori simili si verificheranno se l'array esce dal foglio o colpisce una cella unita. Se rimuovi l'ostacolo, tutto verrà immediatamente corretto al volo.
Array dinamici e tabelle intelligenti
Se l'array dinamico punta a una tabella "intelligente" creata da una scorciatoia da tastiera Ctrl+T oppure Home – Formatta come una tabella (Home — Formatta come tabella), quindi erediterà anche la sua qualità principale: il ridimensionamento automatico.
Quando si aggiungono nuovi dati in basso o a destra, anche la tabella intelligente e l'intervallo dinamico si allungheranno automaticamente:
Tuttavia, c'è una limitazione: non possiamo usare un riferimento all'intervallo dinamico nelle forumule all'interno di una tabella intelligente:
Matrici dinamiche e altre funzionalità di Excel
Va bene, dici. Tutto questo è interessante e divertente. Non c'è bisogno, come prima, di allungare manualmente la formula con un riferimento alla prima cella dell'intervallo originale in basso ea destra e tutto il resto. E questo è tutto?
Non proprio.
Gli array dinamici non sono solo un altro strumento in Excel. Ora sono incorporati nel cuore (o cervello) di Microsoft Excel, il suo motore di calcolo. Ciò significa che altre formule e funzioni di Excel a noi familiari ora supportano anche l'utilizzo di array dinamici. Diamo un'occhiata ad alcuni esempi per darvi un'idea della profondità dei cambiamenti avvenuti.
Trasporre
Per trasporre un intervallo (scambia righe e colonne) Microsoft Excel ha sempre avuto una funzione incorporata TRASPORTO (TRASPORRE). Tuttavia, per utilizzarlo, devi prima selezionare correttamente l'intervallo per i risultati (ad esempio, se l'input era un intervallo di 5×3, devi aver selezionato 3×5), quindi entrare nella funzione e premere il combinazione Ctrl+Shift+entrare, perché potrebbe funzionare solo in modalità formula di matrice.
Ora puoi semplicemente selezionare una cella, inserire la stessa formula e fare clic sulla normale entrare – l'array dinamico farà tutto da solo:
Tabellina
Questo è l'esempio che fornivo quando mi è stato chiesto di visualizzare i vantaggi delle formule di matrice in Excel. Ora, per calcolare l'intera tabella pitagorica, è sufficiente posizionarsi nella prima cella B2, inserire lì una formula che moltiplichi due array (insieme verticale e orizzontale di numeri 1..10) e semplicemente fare clic su entrare:
Incollaggio e conversione custodia
Gli array non solo possono essere moltiplicati, ma anche incollati insieme con l'operatore standard & (e commerciale). Supponiamo di dover estrarre il nome e il cognome da due colonne e correggere il caso saltante nei dati originali. Lo facciamo con una breve formula che forma l'intero array, quindi applichiamo la funzione ad esso PROPNACO (CORRETTO)per riordinare il registro:
Conclusione Top 3
Supponiamo di avere un gruppo di numeri da cui vogliamo derivare i primi tre risultati, disponendoli in ordine decrescente. Ora questo viene fatto con una formula e, di nuovo, senza alcuna Ctrl+Shift+entrare come prima:
Se si desidera inserire i risultati non in una colonna, ma in una riga, è sufficiente sostituire i due punti (separatore di riga) in questa formula con un punto e virgola (separatore di elementi all'interno di una riga). Nella versione inglese di Excel, questi separatori sono rispettivamente punto e virgola e virgola.
VLOOKUP estraendo più colonne contemporaneamente
funzioni VPR (CERCA.VERT) ora puoi estrarre valori non da una, ma da più colonne contemporaneamente: basta specificare i loro numeri (nell'ordine desiderato) come matrice nel terzo argomento della funzione:
Funzione OFFSET che restituisce un array dinamico
Una delle funzioni più interessanti e utili (dopo VLOOKUP) per l'analisi dei dati è la funzione SMALTIMENTO (COMPENSARE), a cui ho dedicato contemporaneamente un intero capitolo del mio libro e un articolo qui. La difficoltà nel comprendere e padroneggiare questa funzione è sempre stata che di conseguenza ha restituito un array (intervallo) di dati, ma non è stato possibile vederlo, perché Excel non sapeva ancora come lavorare con gli array pronti all'uso.
Ora questo problema è nel passato. Guarda come ora, utilizzando una singola formula e un array dinamico restituito da OFFSET, puoi estrarre tutte le righe per un determinato prodotto da qualsiasi tabella ordinata:
Diamo un'occhiata alle sue argomentazioni:
- A1 – cella di partenza (punto di riferimento)
- IMPOSTA(F2;A2:A30;0) – calcolo dello spostamento dalla cella di partenza in basso – al primo cavolo trovato.
- 0 – spostamento della “finestra” verso destra rispetto alla cella di partenza
- SCATTO(A2:A30;F2) – calcolo dell'altezza della “finestra” restituita – il numero di righe in cui è presente il cavolo.
- 4 — dimensione della “finestra” in orizzontale, ovvero output 4 colonne
Nuove funzioni per array dinamici
Oltre a supportare il meccanismo dell'array dinamico nelle vecchie funzioni, a Microsoft Excel sono state aggiunte diverse funzioni completamente nuove, ottimizzate in modo specifico per lavorare con gli array dinamici. In particolare si tratta di:
- GRADE (ORDINARE) – ordina l'intervallo di input e produce un array dinamico sull'output
- ORDINAMENTO (ORDINA PER) – può ordinare un intervallo per valori da un altro
- FILTRO (FILTRO) – recupera le righe dall'intervallo di origine che soddisfano le condizioni specificate
- UNICO (UNICO) – estrae valori univoci da un intervallo o rimuove i duplicati
- SLMASSIVO (A CASO) – genera una matrice di numeri casuali di una determinata dimensione
- PLACENTA (SEQUENZA) — forma una matrice da una sequenza di numeri con un determinato passaggio
Maggiori informazioni su di loro - un po 'più tardi. Valgono un articolo a parte (e non uno) per uno studio ponderato 🙂
Conclusioni
Se hai letto tutto ciò che è scritto sopra, penso che ti rendi già conto della portata dei cambiamenti che sono avvenuti. Tante cose in Excel ora possono essere fatte in modo più semplice, più facile e più logico. Devo ammettere che sono un po' scioccato da quanti articoli ora dovranno essere corretti qui, su questo sito e nei miei libri, ma sono pronto a farlo a cuor leggero.
Riassumendo i risultati, плюсы array dinamici, puoi scrivere quanto segue:
- Puoi dimenticare la combinazione Ctrl+Shift+entrare. Excel ora non vede alcuna differenza tra "formule regolari" e "formule di matrice" e le tratta allo stesso modo.
- Sulla funzione MATR.SOMMA.PRODOTTO (SOMMAPRODOTTO), precedentemente utilizzato per immettere formule di matrice senza Ctrl+Shift+entrare puoi anche dimenticare: ora è abbastanza facile SUM и entrare.
- Le tabelle intelligenti e le funzioni familiari (SUM, IF, VLOOKUP, SUMIF, ecc.) ora supportano in tutto o in parte anche gli array dinamici.
- Esiste la compatibilità con le versioni precedenti: se apri una cartella di lavoro con matrici dinamiche in una vecchia versione di Excel, queste si trasformeranno in formule di matrice (tra parentesi graffe) e continueranno a funzionare nel "vecchio stile".
Ho trovato un numero svantaggi:
- Non è possibile eliminare singole righe, colonne o celle da un array dinamico, ovvero vive come una singola entità.
- Non è possibile ordinare un array dinamico nel solito modo Dati – Ordinamento (Dati — Ordina). Ora c'è una funzione speciale per questo. GRADE (ORDINARE).
- Un intervallo dinamico non può essere trasformato in una tabella intelligente (ma puoi creare un intervallo dinamico basato su una tabella intelligente).
Naturalmente, questa non è la fine e sono sicuro che Microsoft continuerà a migliorare questo meccanismo in futuro.
Dove posso scaricare?
E infine, la domanda principale 🙂
Microsoft ha annunciato e mostrato per la prima volta un'anteprima degli array dinamici in Excel a settembre 2018 in una conferenza infiammare. Nei mesi successivi c'è stato un test approfondito e un rodaggio delle nuove funzionalità, prima di tutto i gatti dipendenti della stessa Microsoft, e poi su tester volontari della cerchia di Office Insider. Quest'anno, l'aggiornamento che aggiunge gli array dinamici ha iniziato a essere distribuito gradualmente ai normali abbonati a Office 365. Ad esempio, l'ho ricevuto solo ad agosto con il mio abbonamento a Office 365 Pro Plus (mirato mensile).
Se il tuo Excel non ha ancora array dinamici, ma vuoi davvero lavorarci, allora ci sono le seguenti opzioni:
- Se hai un abbonamento a Office 365, puoi semplicemente attendere fino a quando questo aggiornamento ti raggiunge. La rapidità con cui ciò accade dipende dalla frequenza con cui gli aggiornamenti vengono consegnati al tuo Office (una volta all'anno, una volta ogni sei mesi, una volta al mese). Se disponi di un PC aziendale, puoi chiedere all'amministratore di configurare gli aggiornamenti da scaricare più spesso.
- Puoi unirti ai ranghi di quei volontari del test di Office Insider, quindi sarai il primo a ricevere tutte le nuove funzionalità e funzioni (ma c'è la possibilità di aumentare il bug in Excel, ovviamente).
- Se non disponi di un abbonamento, ma di una versione standalone in scatola di Excel, dovrai attendere almeno fino al rilascio della prossima versione di Office ed Excel nel 2022. Gli utenti di tali versioni ricevono solo aggiornamenti di sicurezza e correzioni di bug e tutti i nuovi "chicche" ora vanno solo agli abbonati a Office 365. Triste ma vero 🙂
In ogni caso, quando gli array dinamici vengono visualizzati in Excel, dopo questo articolo sarai pronto per questo 🙂
- Cosa sono le formule di matrice e come usarle in Excel
- Somma finestra (intervallo) utilizzando la funzione OFFSET
- 3 modi per trasporre una tabella in Excel