Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Jak Excel oblicza formuły w tabeli przestawnej

M · Świeżak Wersja: Win Office 365 Posty: 4

Cześć

Chciałbym poruszyć temat sposobu obliczeń przez Excela komórek w tabeli przestawnej.
Chciałbym dobrze zrozumieć tok postępowania i logikę Excela w wypełnianiu tabeli przestawnej, stąd mam kilka pytań.

1. Jakie mogą być powody występowania pustych komórek w tabeli przestawnej?
Oczywistym jest, że otrzymamy pustą komórkę, jeśli część wspólna komórki i wiersza w zbiorze danych będzie zbiorem pustym. Zastanawia mnie natomiast, czemu jeśli wspomniana część wspólna będzie istnieć, ale komórki podlegające obliczeniom będą puste to Excel również zwróci w tabeli przestawnej pustą komórkę, ale jeśli np. w tej części wspólnej pojawią się niepuste komórki ale niezawierające liczb, to zwróci 0?
Dla zobrazowania arkusz 1 w załączonym Excelu.

2. Kolejna rzecz, która wydaje mi się nielogiczna.
Zachowanie mnożenia w elemencie obliczeniowym.
Dwa różne wyniki tych samych operacji - arkusz 2 załączonego pliku.

3. Ostatnia sprawa.
Mam w danych kolumnę wiek z jednym brakiem danych. Wrzucam ją do tabeli przestawnej i tworzę dodatkowo pole obliczeniowe składające się tylko z tej kolumny (tak, wiem że to sztuczny przykład na siłę). Znowu dla braku danych raz otrzymuję 0, a raz pustą komórkę (arkusz 3). Czemu tak jest?

Chciałbym zrozumieć logikę Excela i dowiedzieć się, skąd taka niekonsekwencja?
Wiem, że pokazane 'problemy' nie są kluczowe z punktu widzenia samych informacji dostarczanych przez tabele przestawne, jednak z własnej ciekawości wolałbym wiedzieć jak myśli Excel.
Innymi słowy, czy jest jakaś zależność (jeśli tak, jaka?) mówiąca kiedy Excel w trudnych przypadkach zwróci 0, a kiedy pustą komórkę?

pytania.xlsx

Pobierz Plik ściągnięto 5 raz(y) 23.13 KB

Maciej Gonet · Wysłany: 17-02-2021, 02:31

Nie jestem specjalistą w zakresie tablic przestawnych, ale spróbuję coś wyjaśnić.
Wyniki obliczeń jakie zwraca tablica przestawna są jakie są i trzeba to przyjąć z dobrodziejstwem inwentarza. Niestety różne części Excela nie są do końca konsekwentne w interpretacji danych, najwidoczniej były opracowywane przez różnych autorów i nie uzgodniono do końca wszystkich szczegółów.
Jeśli się przyjrzeć tym wynikom, to nie są one nielogiczne, tylko nieco niekonsekwentne i inne niż w przypadku zwykłych formuł arkuszowych. Oczywiście regularne liczby nie budzą wątpliwości, tylko różne wartości nieliczbowe.

Ad 1. Jak zauważyłeś, pustą komórkę otrzymamy gdy zbiór wyników jest pusty oraz gdy zbiór wyników nie jest pusty, ale jego składniki są puste. W opisie pojawia się tekst "Brak wartości". Wartości tekstowe, również liczby sformatowane jako tekst oraz wartości logiczne (PRAWDA i FAŁSZ) są traktowane jak wartości zerowe. Wartości błędów są propagowane (to znaczy nawet jedna wartość błędu przy sumowaniu powoduje, że wynik jest błędem). Pod tym względem wyniki są zbliżone do wyników funkcji SUMA, różnica dotyczy tylko wyniku pustego, bo funkcja SUMA zawsze w takich przypadkach zwraca zero.

Ad 2. Tu mamy podobny problem jak w arkuszu gdy porównamy wynik bezpośredniego mnożenia A1*B1*C1 z wynikiem funkcji ILOCZYN(A1:C1). Przy mnożeniu bezpośrednim teksty powodują błąd, liczby sformatowane jako tekst są konwertowane na liczby, puste komórki są traktowane jak 0, PRAWDA i FAŁSZ odpowiednio jak 1 i 0, a błędy są propagowane. W przypadku funkcji ILOCZYN jest inaczej: brane są pod uwagę liczby i błędy, wszystko inne: puste komórki, wartości logiczne i teksty (także liczby sformatowane jak tekst) są ignorowane.
W tabeli przestawnej rozwiązano to inaczej: transformacja danych jest dwustopniowa - najpierw dane z tabeli źródłowej są agregowane - na tym etapie puste komórki są zachowywane, błędy są propagowane, liczby w formacie liczbowym są przenoszone, wszystko inne (w tym liczby sformatowane jako tekst) są zamieniane na symboliczne zera. Napisałem "symboliczne", bo są one widoczne jako "0", ale później przy obliczaniu iloczynu ignorowane (jak przy funkcji ILOCZYN).
Inaczej traktowane są elementy obliczeniowe. Ich wartość jest obliczana jak przy zwykłej formule mnożenia zgodnie z zapisanym wzorem. Symboliczne zera są interpretowane jak zera, podobnie jak przy zwykłych formułach.
Zgadzam się, że w tym miejscu jest to trochę nielogiczne.

Ad 3. Tu sytuacja jest podobna do tej z p. 2. Tam gdzie mamy brak danych, ta pusta komórka jest przenoszona do danych zagregowanych. Przy formule obliczeniowej mamy już podobne wyrażenie jak w arkuszu i po znaku "=" formuła zwraca 0. Ale w odróżnieniu od zwykłych formuł obliczenia odbywają się dwustopniowo. Gdyby w danych źródłowych był np. tekst, to najpierw zostanie on zamieniony na zero, a dopiero potem uwzględniony w formule obliczeniowej.
W kontekście pól obliczeniowych też można zauważyć inną interpretację danych. Wynik powinien być liczbą, jeśli nie jest to - wartości logiczne PRAWDA i FAŁSZ są zamieniane odpowiednio na 1 i 0, liczby sformatowane tekstowo są zamieniane na liczby, a inne teksty generują błędy.

Mamy tu zatem do czynienia z brakiem konsekwencji, ale pocieszajmy się, że tabele przestawne zostały zaprojektowane do pracy z danymi liczbowymi, a inne dane występują stosunkowo rzadko i zostały potraktowane trochę jak "zło konieczne" - program musi zwrócić jakiś wynik. :-(

bodek · Wersja: Win Office 2019 **Pomógł:** 989 razy Posty: 2879

Ja odpowiem trochę inaczej
Jeżeli kombinacja wiersz vs kolumna nie istnieje -> TP zwróci puste

Jeżeli kombinacja wiersz vs kolumna istnieje -> TP zwróci wynik jako SUMA.JEŻELI()/SUMA.WARUNKÓW(), czyli dla danych tekstowych ignoruje je i/ale (jak opisał to Maciej Gonet) zwróci zero.

Pola i elementy obliczeniowe wykonują operacje na sumach z pól, czyli zapis

Kod:

Pole obliczeniowe wiek=wiek

oznacza tak naprawdę zapis

Kod:

Pole obliczeniowe wiek=SUMA(wiek)

Analogicznie, dla elementu obliczeniowego z przykładu otrzymasz

Kod:

=ILOCZYN(SUMA(H10);SUMA(H11))

i konsekwentnie, jak opisał Maciek, jeżeli funkcja, to zwraca zero lub wartość liczbową.

M · Świeżak Wersja: Win Office 365 Posty: 4

Dzięki za odpowiedzi, sporo mi się rozjaśniło.

Mam jeszcze pytanie, bodek, jak rozpisałbyś sytuację którą zamieszczam w tym poście?
Jak tutaj liczone jest pole obliczeniowe, że iloczyn 0*#ARG!=0?

pytanie.xlsx

Pobierz Plik ściągnięto 8 raz(y) 13.7 KB

bodek · Wersja: Win Office 2019 **Pomógł:** 989 razy Posty: 2879

Z formuły pola obliczeniowego wynika, że #ARG! ma być tylko dla 2019

Kod:

rrr=JEŻELI(rok=2019;#ARG!;rok)

natomiast dla pozostałych niech wstawi wartość z pola rok, ale w polu rok, w danych nie ma unikatu 2020*2019, czyli nie istnieje taka kombinacja, czyli zwraca zero, czyli nawet nie wykona operacji przewidzianej elementem obliczeniowym.

p.s.
Generalnie, moim skromnym zdaniem unikaj elementów obliczeniowych.

M · Świeżak Wersja: Win Office 365 Posty: 4

Nie do końca wiem, co rozumiesz pisząc, że nie ma unikatu 2020*2019.

Byłem przekonany, że

Kod:

J7 = suma(H7) *suma(I7)

, tak jak opisałeś to w swoim poprzednim poście, co powinno dać #ARG! * 0 czyli #ARG!.

bodek · Wersja: Win Office 2019 **Pomógł:** 989 razy Posty: 2879

Jak napisałem, formuła, którą podałeś się nie wykona ponieważ pole obliczeniowe (formuła JEŻELI(...)) każe wstawić do komórki wartość z pola rok dla kolumny w TP o unikalnej nazwie 2020*2019. Takiego unikatu w danych (kolumnie B arkusza) nie ma -> nie ma kombinacji pole x wiersz -> zwraca zero i formuła elementu obliczeniowego się nie przelicza!

To jest mocno zakręcone i rzekłbym niespotykane :mrgreen:

ponieważ jednocześnie używasz wartości z pola rok aby zbudować element obliczeniowy oraz jednocześnie wstawiasz unikaty z tego samego pola rok do prezentacji w TP.
edit: mało tego, przedmiotem agregacji jest formuła pola obliczeniowego, które odwołuje się w swoich wynikach do tego samego pola rok. Jak widać, w takim przypadku nie oblicza się formuła elementu obliczeniowego.

Bez nazwy.jpg

Plik ściągnięto 7 raz(y) 84.94 KB

M · Świeżak Wersja: Win Office 365 Posty: 4

Dzięki, jest już trochę jaśniej. Wiem, że przykłady są mocno skrajne i 'nie życiowe', ale jednak dobre zrozumienie narzędzia uważam za rzecz istotną.

Jeszcze jedno pytanko o sumę końcową w przypadku użycia elementu obliczeniowego.
Nie do końca wiem jak ona jest obliczana (przykład w załączniku). Mógłbyś podpowiedzieć, skąd taki wynik (jakie kroki Excel wykonał)?
Jeśli nie ma elementu obliczeniowego, wszystko jest dla mnie jasne (Excel policzy sumę końcową jako SUMA(D2:D3)/SUMA(C2:C3)), ale to co Excel robi w przypadku użycia elementu obliczeniowego jest jakąś magią.

pytanie.xlsx

Pobierz Plik ściągnięto 4 raz(y) 13.9 KB

Maciej Gonet · Wysłany: 18-02-2021, 20:39

Trochę na marginesie tego tematu chciałbym zapytać specjalistów od tabel przestawnych, czy istnieje jakieś narzędzie w stylu "Szacuj formułę" w arkuszu czy wykonania krokowego w VBA, które pozwoliłoby sprawdzić skąd się biorą wyniki zwracane w określonej komórce przez tabelę przestawną.

bodek · Wersja: Win Office 2019 **Pomógł:** 989 razy Posty: 2879

M, największą wadą elementów obliczeniowych jest fakt, że wszystkie sumy końcowe i niektóre pośrednie ZAWSZE źle się liczą. Szkoda czasu na analizę co one tam sobie liczą :-P

Dlatego osobiście nie używam elementów obliczeniowych, a jak muszę (choć nie pamiętam kiedy ostatnio musiałem) to wyłączam pokazywanie sum.

bodek · Wersja: Win Office 2019 **Pomógł:** 989 razy Posty: 2879

Maciej Gonet, na 99,99% nie ma (nie znam) takiego narzędzia, ale przy agregacji danych Sumą lub Licznikiem możesz wylistować sobie wszystkie rekordy, które składają się na wynik w danej komórce TP, poprzez dwuklik na wartości liczbowej w obszarze wartości (na tej komórce). Utworzy się wtedy nowy arkusz z obiektem Tabela, właśnie z tymi rekordami z tabeli źródłowej z których powstała wartość. Potem można na piechotę policzyć sobie SUMA.JEŻELI() /SUMA.WARUNKÓW() /LICZ.JEŻELI() ... Chyba taka sobie odpowiedź na pytanie. :-P