Nahraďte kontingenčnú tabuľku 3 vzorcami dynamického poľa - Tipy pre Excel

Je to už osem dní, čo boli na konferencii Ignite 2018 v Orlande oznámené vzorce s dynamickým poľom. Tu som sa dozvedel:

Moderné polia boli oznámené na Ignite 24. septembra 2018 a oficiálne sa volali Dynamic Arrays.
Napísal som 60-stranovú e-knihu s 30 príkladmi, ako ich používať, a do konca roka 2018 ju ponúkam zadarmo.
Zavádzanie bude oveľa pomalšie, ako by ktokoľvek chcel, čo je frustrujúce. Prečo tak pomaly? Tím programu Excel vykonal zmeny v kóde Calc Engine, ktorý je stabilný už 30 rokov. Obzvlášť znepokojujúce: s doplnkami, ktoré vkladajú vzorce do programu Excel, ktoré neúmyselne použili implicitný priesečník. Tieto doplnky sa porušia, ak program Excel teraz vráti rozsah rozliatia.
Existuje nový spôsob, ako odkazovať na rozsah vrátený poľom: =E3#ale zatiaľ nemá názov. # So nazýva Rozliate Formula Operator . Čo si myslíte o názve ako Spill Ref (navrhuje Excel MVP Jon Acampora) alebo The Spiller (navrhuje MVP Ingeborg Hawighorst)?

Ako spoluautor dátovej tabuľky kontingenčných tabuliek milujem dobrú kontingenčnú tabuľku. Čo však v prípade, keď potrebujete aktualizovať svoje kontingenčné tabuľky a nemôžete dôverovať manažérovi manažéra, že klikne na Obnoviť? Dnes popísaná technika ponúka sériu troch vzorcov, ktoré nahradia kontingenčnú tabuľku.

Ak chcete získať triedený zoznam jedinečných zákazníkov, použite =SORT(UNIQUE(E2:E564))v I2.

Jeden vzorec dynamického poľa na vytvorenie zákazníkov na konci prehľadu

Na umiestnenie produktu cez hornú časť použite =TRANSPOSE(SORT(UNIQUE(B2:B564)))v J1.

Pre oblasť stĺpcov použite TRANSPOZÍCIA

Je tu problém: neviete, aký vysoký bude zoznam zákazníkov. Neviete, aký široký bude zoznam produktov. Ak sa odvolávate na I2 #, Spiller bude automaticky odkazovať na aktuálnu veľkosť vráteného poľa.

Vzorec vrátiť hodnoty oblasť kontingenčnej tabuľke je jeden vzorec poľa J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

V angličtine to znamená, že chcete pridať výnosy z G2: G564, kde sa zákazníci v E zhodujú so zákazníkom aktuálneho riadku z maticového vzorca I2 a produkty v B sa zhodujú s aktuálnym stĺpcom maticového vzorca v J1.

Toto je sladká formulka

Čo ak sa zmenia základné údaje? Pridaním nového zákazníka a nového produktu som zmenil tieto dve bunky v zdroji.

Zmeňte niektoré bunky v pôvodných dátach

Správa sa aktualizuje o nové riadky a nové stĺpce. Referenčný rozsah polí I2 # a J1 # spracováva ďalší riadok a stĺpec.

Váš prehľad krížových kariet sa automaticky rozšíri o nové údaje

Prečo SUMIFS funguje? Toto je koncept v programe Excel s názvom Broadcasting. Ak máte vzorec odkazujúci na dve polia:

Pole číslo jedna je (27 riadkov) x (1 stĺpec)
Pole dva je (1 riadok) x (3 stĺpce)
Excel vráti výsledné pole, ktoré je rovnako vysoké a široké ako najvyššia a najširšia časť odkazovaných polí:
Výsledok bude (27 riadkov) x (3 stĺpce).
Toto sa nazýva polia vysielania.