Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 70165 Skopiuj do schowka Jak Excel oblicza formuły w tabeli przestawnej
Autor Wiadomość
M 
Świeżak


Wersja: Win Office 365
Posty: 4
Wysłany: 16-02-2021, 22:18   Jak Excel oblicza formuły w tabeli przestawnej

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

ID posta: 400541 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 2030 razy
Posty: 6415
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. :-(
ID posta: 400543 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2019
Pomógł: 989 razy
Posty: 2879
Wysłany: 17-02-2021, 09:01   

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ą.
_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu, szkolenia online
  
ID posta: 400548 Skopiuj do schowka
 
 
M 
Świeżak


Wersja: Win Office 365
Posty: 4
Wysłany: 18-02-2021, 12:10   

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

ID posta: 400612 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2019
Pomógł: 989 razy
Posty: 2879
Wysłany: 18-02-2021, 14:50   

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.
_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu, szkolenia online
ID posta: 400627 Skopiuj do schowka
 
 
M 
Świeżak


Wersja: Win Office 365
Posty: 4
Wysłany: 18-02-2021, 15:58   

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!.
ID posta: 400632 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2019
Pomógł: 989 razy
Posty: 2879
Wysłany: 18-02-2021, 17:01   

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

_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu, szkolenia online
  
ID posta: 400637 Skopiuj do schowka
 
 
M 
Świeżak


Wersja: Win Office 365
Posty: 4
Wysłany: 18-02-2021, 20:38   

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

ID posta: 400645 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 2030 razy
Posty: 6415
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ą.
ID posta: 400646 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2019
Pomógł: 989 razy
Posty: 2879
Wysłany: 18-02-2021, 20:49   

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.
_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu, szkolenia online
  
ID posta: 400650 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2019
Pomógł: 989 razy
Posty: 2879
Wysłany: 18-02-2021, 20:56   

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
_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu, szkolenia online
  
ID posta: 400651 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe