Sostituisci una tabella pivot con 3 formule di array dinamici - Suggerimenti per Excel

Sommario

Guarda un video

Sono passati otto giorni da quando le formule di array dinamici sono state annunciate alla conferenza Ignite 2018 a Orlando. Ecco cosa ho imparato:

Gli array moderni sono stati annunciati a Ignite il 24 settembre 2018 e ufficialmente chiamati Dynamic Arrays.
Ho scritto un eBook di 60 pagine con 30 esempi su come usarli e lo offro gratuitamente fino alla fine del 2018.
Il lancio sarà molto più lento di quanto chiunque desideri, il che è frustrante. Perché così lento? Il team di Excel ha apportato modifiche al codice di Calc Engine che è rimasto stabile per 30 anni. Di particolare interesse: con componenti aggiuntivi che iniettano formule in Excel che hanno utilizzato inavvertitamente l'intersezione implicita. Questi componenti aggiuntivi si interromperanno se Excel ora restituisce un intervallo di versamento.
C'è un nuovo modo per fare riferimento all'intervallo restituito da un array: =E3#ma non ha ancora un nome. Il # è chiamato Operatore formula versata . Cosa ne pensi di un nome come Spill Ref (suggerito dall'MVP di Excel Jon Acampora) o The Spiller (suggerito dall'MVP Ingeborg Hawighorst)?

Come coautore di Pivot Table Data Crunching, adoro una buona tabella pivot. Ma cosa succede se hai bisogno che le tue tabelle pivot si aggiornino e non puoi fidarti del manager del tuo manager per fare clic su Aggiorna? La tecnica descritta oggi offre una serie di tre formule per sostituire una tabella pivot.

Per ottenere un elenco ordinato di clienti unici, utilizzare =SORT(UNIQUE(E2:E564))in I2.

Una formula di matrice dinamica per creare clienti a lato del report

Per mettere il prodotto nella parte superiore, utilizzare =TRANSPOSE(SORT(UNIQUE(B2:B564)))in J1.

Per l'area delle colonne, usa TRANSPOSE

Ecco un problema: non sai quanto sarà alto l'elenco dei clienti. Non sai quanto sarà ampio l'elenco dei prodotti. Se si fa riferimento a I2 #, lo Spiller farà automaticamente riferimento alla dimensione corrente dell'array restituito.

La formula per restituire l'area valori della tabella pivot è un singolo formula matrice in J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

In inglese, si dice che si desidera aggiungere i ricavi da G2: G564 dove i clienti in E corrispondono al cliente della riga corrente dalla formula di matrice I2 ei prodotti in B corrispondono alla colonna corrente della formula di matrice in J1.

Questa è una formula dolce

Cosa succede se i dati sottostanti cambiano? Ho aggiunto un nuovo cliente e un nuovo prodotto modificando queste due celle nella sorgente.

Modifica alcune celle nei dati originali

Il rapporto si aggiorna con nuove righe e nuove colonne. L'array-range Reference di I2 # e J1 # gestisce la riga e la colonna extra.

Il rapporto a campi incrociati si espande automaticamente con i nuovi dati

Perché il SUMIFS funziona? Questo è un concetto in Excel chiamato Broadcasting. Se hai una formula che fa riferimento a due array:

La matrice uno è (27 righe) x (1 colonna)
La matrice due è (1 riga) x (3 colonne)
Excel restituirà un array risultante alto e largo quanto la parte più alta e più ampia degli array a cui si fa riferimento:
Il risultato sarà (27 righe) x (3 colonne).
Questo è chiamato Broadcasting array.