Cseréljen egy kimutatótáblát 3 dinamikus tömbképlettel - Excel tippek

Nyolc nap telt el azóta, hogy dinamikus tömbképleteket hirdettek az orlandói Ignite 2018 konferencián. Itt van, amit megtanultam:

A Modern tömböket 2018. szeptember 24-én jelentették be az Ignite-ben, és hivatalosan Dynamic Arrays-nek hívták őket.
Írtam egy 60 oldalas e-könyvet, amelyben 30 példa található a használatukra, és 2018 végéig ingyen kínálom.
A bevezetés sokkal lassabb lesz, mint bárki szeretné, ami frusztráló. Miért ilyen lassú? Az Excel csapata módosította a Calc Engine kódját, amely 30 éve stabil. Különös aggodalomra ad okot: olyan bővítményekkel, amelyek képleteket injektálnak az Excelbe, amelyek véletlenül implicit kereszteződést használtak. Ezek a bővítmények megszakadnak, ha az Excel most Spill tartományt ad vissza.
Új módon lehet hivatkozni a tömb által visszaadott tartományra: =E3#de még nincs neve. A # -ot kiömlött képlet operátornak hívják . Mit gondol egy olyan névről, mint a Spill Ref (az Excel MVP Jon Acampora javaslata) vagy a The Spiller (az MVP Ingeborg Hawighorst javaslata)?

A Pivot Table Data Crunching társszerzőjeként szeretem a jó pivot táblázatot. De mi van akkor, ha frissíteni kell a pivot táblákat, és nem bízhat abban, hogy a menedzser menedzsere rákattint a Frissítés gombra? A ma ismertetett technika három képletből álló sorozatot kínál a forgatótábla helyettesítésére.

Az egyedi ügyfelek rendezett listájának megszerzéséhez használja =SORT(UNIQUE(E2:E564))az I2-ben.

Egy dinamikus tömbképlet az ügyfelek létrehozásához a jelentés oldalán

A termék tetejére helyezéséhez használja =TRANSPOSE(SORT(UNIQUE(B2:B564)))a J1-ben.

Az oszlopok területéhez használja a TRANSPOSE elemet

Itt van egy probléma: nem tudod, milyen magas lesz az ügyfelek listája. Nem tudja, milyen széles lesz a terméklista. Ha az I2 # -ra hivatkozik, a Spiller automatikusan a visszaadott tömb aktuális méretére hivatkozik.

A képlet, hogy visszatérjen az értékeket területe a pivot tábla egyetlen tömb képletet J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Angolul ez azt mondja, hogy hozzá akarja adni a G2: G564 bevételeket, ahol az E vásárlók az I2 tömbképletről egyezik az aktuális sor vevőjével, a B kategóriában szereplő termékek pedig a J1 tömbképlet aktuális oszlopával.

Ez édes formula

Mi történik, ha az alapul szolgáló adatok megváltoznak? Új forrást és új terméket adtam hozzá a forrás ezen két cellájának megváltoztatásával.

Módosítson néhány cellát az eredeti adatokban

A jelentés új sorokkal és új oszlopokkal frissül. Az I2 # és J1 # tömbtartomány-referenciája kezeli az extra sort és oszlopot.

A keresztfüles jelentés automatikusan kibővül az új adatokkal

Miért működik a SUMIFS? Ez az Excel program Broadcasting nevű fogalma. Ha képlete van, amely két tömbre utal:

Az első tömb (27 sor) x (1 oszlop)
A második tömb (1 sor) x (3 oszlop)
Az Excel egy olyan eredményt tartalmazó tömböt ad vissza, amely olyan magas és széles, mint a hivatkozott tömbök legmagasabb és legszélesebb része:
Az eredmény (27 sor) x (3 oszlop) lesz.
Ezt Broadcasting tömbnek hívják.