Nahraďte kontingenční tabulku 3 vzorci dynamického pole - tipy pro Excel

Je tomu již osm dní, co byly na konferenci Ignite 2018 v Orlandu oznámeny vzorce s dynamickým polem. Zde jsem se naučil:

Modern Arrays byly oznámeny na Ignite 24. září 2018 a oficiálně se nazývaly Dynamic Arrays.
Napsal jsem 60stránkovou elektronickou knihu s 30 příklady, jak je používat, a do konce roku 2018 ji nabízím zdarma.
Zavádění bude mnohem pomalejší, než kdokoli chce, což je frustrující. Proč tak pomalu? Tým aplikace Excel provedl změny v kódu Calc Engine, který je stabilní 30 let. Zvláštní pozornost: s doplňky, které vkládají vzorce do aplikace Excel, které neúmyslně používaly implicitní průnik. Tyto doplňky se rozbijí, pokud Excel nyní vrátí rozsah rozlití.
Existuje nový způsob, jak odkazovat na rozsah vrácený polem: =E3#ale dosud nemá název. # Se nazývá Rozlité Formula Operator . Co si myslíte o jménu jako Spill Ref (navrhl Excel MVP Jon Acampora) nebo The Spiller (navrhl MVP Ingeborg Hawighorst)?

Jako spoluautor dat kontingenčních tabulek kontingenčních tabulek miluji dobrou kontingenční tabulku. Ale co když potřebujete aktualizovat kontingenční tabulky a nemůžete důvěřovat manažerovi svého manažera, že klikne na Obnovit? Technika popsaná dnes nabízí řadu tří vzorců, které nahradí kontingenční tabulku.

Chcete-li získat seřazený seznam jedinečných zákazníků, použijte =SORT(UNIQUE(E2:E564))v I2.

Jeden vzorec dynamického pole k vytvoření zákazníků po straně sestavy

Chcete-li umístit produkt přes horní část, použijte =TRANSPOSE(SORT(UNIQUE(B2:B564)))v J1.

Pro oblast sloupců použijte TRANSPOSE

Je tu problém: nevíte, jak vysoký bude seznam zákazníků. Nevíte, jak široký bude seznam produktů. Pokud odkazujete na I2 #, Spiller automaticky odkáže na aktuální velikost vráceného pole.

Vzorec vrátit hodnoty oblast kontingenční tabulce je jeden maticový vzorec J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

V angličtině to říká, že chcete přidat výnosy z G2: G564, kde zákazníci v E odpovídají zákazníkovi aktuálního řádku z maticového vzorce I2 a produkty v B odpovídají aktuálnímu sloupci maticového vzorce v J1.

To je sladký vzorec

Co když se změní podkladová data? Přidal jsem nového zákazníka a nový produkt změnou těchto dvou buněk ve zdroji.

Změňte některé buňky v původních datech

Zpráva se aktualizuje o nové řádky a nové sloupce. Odkaz na rozsah polí I2 # a J1 # zpracovává další řádek a sloupec.

Zpráva o křížových kartách se automaticky rozšíří o nová data

Proč SUMIFS funguje? Toto je koncept v aplikaci Excel s názvem Broadcasting. Pokud máte vzorec, který odkazuje na dvě pole:

Pole jedna je (27 řádků) x (1 sloupec)
Pole dva je (1 řádek) x (3 sloupce)
Excel vrátí výsledné pole, které je stejně vysoké a široké jako nejvyšší a nejširší část odkazovaných polí:
Výsledek bude (27 řádků) x (3 sloupce).
Toto se nazývá pole vysílání.