Zastąp tabelę przestawną trzema dynamicznymi formułami tablicowymi - porady dotyczące programu Excel

Spisie treści

Obejrzyj wideo

Minęło osiem dni od ogłoszenia dynamicznych formuł tablicowych na konferencji Ignite 2018 w Orlando. Oto czego się nauczyłem:

Nowoczesne tablice zostały ogłoszone podczas Ignite 24 września 2018 r. I oficjalnie nazwano je dynamicznymi.
Napisałem 60-stronicowy e-book z 30 przykładami ich użycia i oferuję go bezpłatnie do końca 2018 roku.
Wdrażanie będzie o wiele wolniejsze, niż ktokolwiek by chciał, co jest frustrujące. Dlaczego tak wolno? Zespół programu Excel wprowadził zmiany w kodzie Calc Engine, który był stabilny od 30 lat. Szczególnie niepokojące: z dodatkami, które wstrzykują formuły do programu Excel, które nieumyślnie używały niejawnego przecięcia. Te dodatki ulegną awarii, jeśli program Excel zwróci teraz zakres rozlania.
Istnieje nowy sposób odwoływania się do zakresu zwracanego przez tablicę: =E3#ale nie ma on jeszcze nazwy. Znak # jest nazywany operatorem rozlanej formuły . Co sądzisz o nazwach takich jak Spill Ref (zasugerowane przez Excela MVP Jona Acamporę) lub The Spiller (zasugerowane przez MVP Ingeborg Hawighorst)?

Jako współautorka Crunch Data Table Pivot, uwielbiam dobrą tabelę przestawną. Ale co, jeśli potrzebujesz zaktualizować tabele przestawne i nie możesz ufać menedżerowi swojego menedżera, że kliknie Odśwież? Opisana dzisiaj technika oferuje serię trzech formuł, które zastępują tabelę przestawną.

Aby uzyskać posortowaną listę unikalnych klientów, użyj =SORT(UNIQUE(E2:E564))w I2.

Jedna dynamiczna formuła tablicowa do tworzenia klientów z boku raportu

Aby umieścić produkt na górze, użyj =TRANSPOSE(SORT(UNIQUE(B2:B564)))w J1.

W obszarze kolumn użyj opcji TRANSPOSE

Oto problem: nie wiesz, jak wysoka będzie lista klientów. Nie wiesz, jak szeroka będzie lista produktów. Jeśli odwołujesz się do I2 #, Spiller automatycznie odniesie się do bieżącego rozmiaru zwróconej tablicy.

Wzór do powrotu powierzchni wartości tabeli przegubu jest jeden wzór w tablicy J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

W języku angielskim oznacza to, że chcesz dodać przychody z G2: G564, gdzie Klienci w E odpowiadają klientowi bieżącego wiersza z formuły tablicowej I2, a produkty w B są zgodne z bieżącą kolumną formuły tablicowej w J1.

To słodka formuła

Co się stanie, jeśli zmienią się podstawowe dane? Dodałem nowego klienta i nowy produkt, zmieniając te dwie komórki w źródle.

Zmień niektóre komórki w oryginalnych danych

Raport zostanie zaktualizowany o nowe wiersze i nowe kolumny. Array-Range Reference dla I2 # i J1 # obsługuje dodatkowy wiersz i kolumnę.

Twój raport krzyżowy automatycznie rozszerza się o nowe dane

Dlaczego SUMIFS działa? Jest to koncepcja programu Excel nazywana nadawaniem. Jeśli masz formułę, która odwołuje się do dwóch tablic:

Tablica pierwsza to (27 wierszy) x (1 kolumna)
Tablica druga to (1 wiersz) x (3 kolumny)
Program Excel zwróci wynikową tablicę, która jest tak wysoka i szeroka, jak najwyższa i najszersza część tablic, do których się odwołuje:
Wynikiem będzie (27 wierszy) x (3 kolumny).
Nazywa się to tablicami rozgłoszeniowymi.