Pracując na co dzień lub od święta z tabelami przestawnymi, pojawiają się problemy, takie jak widoczne w filtrze wartości, których w danych źródłowych już nie ma. Utrudniająca korzystanie z wartości w raporcie tabeli funkcja WEŹDANETABELI, a także kilka innych. Spróbuję na kilka z tych problemów znaleźć rozwiązanie.
1. Stare wartości w filtrach tabeli przestawnej
Często pomimo, że w danych źródłowych tabeli przestawnej pewnych wartości nie ma, to pojawiają się one w filtrach tabeli przestawnej. Aby zaradzić temu i lista znajdująca się pod filtrem była aktualna, należy zmienić ustawienia tabeli przestawnej. Przejdę przez kolejne czynności na przykładzie.
Dane dot. indeks giełdowego. Obecnie skonstruowana tabela przestawna, zawiera wartości ze stycznia, lutego, marca i grudnia.
Co też jest widoczne na filtrze.
Usunę w danych źródłowych dane dla marca. Odświeżę tabelę przestawną. W wierszach raportu tabeli przestawnej nie widzę już marca.
Natomiast w filtrach wciąż jest dostępny.
Aby filtry również zawierały zaktualizowane wartości, wybieram prawy przycisk myszy > Opcje tabeli przestawnej.
Dane > Liczba elementów do zachowania w każdym polu > zmieniam z Automatycznie na Brak > OK.
Odświeżam tabelę przestawną, nie widzę już marca wśród wartości filtrów tabeli przestawnej.
Ważne, że obecne ustawienia tabeli przestawnej będą odwzorowane na każdej kolejnej tabeli korzystającej z tej samej pamięci tabeli przestawnej.
2. Pojawiająca się formuła WEŹDANEATABELI
Pracując z tabelami przestawnymi chcemy wartości w nich wygenerowane, wykorzystać w innych formułach na zewnątrz tabeli przestawnej. Domyślnie wybierając wartości z tabeli, generowana jest funkcja WEŹDANETABELI, która jest funkcją bezwzględną, a więc zawsze (jeżeli jej nie zmodyfikujemy) będzie dawać tę samą wartość, ponieważ będzie odnosić się wciąż do tego samego miejsca. Jednym słowem – nie mogę jej przeciągnąć.
Aby wyłączyć tę opcjonalność, muszę zmienić ustawienia tabeli przestawnej. Ustawiam myszkę w obszarze tabeli przestawnej > wybieram na wstążce > Narzędzia tabel przestawnych > Analiza > Tabela przestawna > Opcje > Odznaczam opcję Generuj funkcję WEŹDANETABELI.
Poniżej efekt odwołania się do tabeli przestawnej po zmianie ustawień.
3. Zmiana kalkulacji wartości ze Zlicz na Suma
Dodając pole do obszaru wartości nieraz zamiast sumy, excel zlicza wystąpienia. Dzieję się to dlatego, że w danych źródłowych wybieranego pola są dane tekstowe, puste pola. Aby zmienić zliczenie na sumę należy wybrać żądaną kolumnę w polach wartości > rozwinąć trójkąt > Ustawienia pola wartości > Wybieram Suma > OK.
4. Nie pojawiają się nowe dane
Wstawiając tabelę przestawną, zaznaczam zakres, w którym znajdują się dane, a nie cały obszar Arkusza, dzieje się to w sumie niemal automatycznie przy pomocy kreatorów. Często jednak w trakcie pracy, szczególnie rozciągniętej w czasie, wyniknie potrzeba uzupełnienia danych o nowe wiersze/pozycje. Wówczas we wszystkich tabelach przestawnych korzystających z danych należy zmienić zakres.
Jednym z rozwiązań jest zmiana danych, z których budujemy tabelę przestawną na format tabeli.
Aby utworzyć z zwykłych danych tabelę, ustawiam się w obszarze danych > Wstawianie > Tabela.
Zakres danych zostaje automatycznie wykryty przez Excel , pojawia się też opcja wyboru pierwszego wiersza z zakresu jako nagłówków tabeli.
Po wybraniu OK, zakres zostanie konwertowany na format tabeli jak poniżej, który zawsze można zmienić według własnych preferencji. Tabela otrzymuje nazwę domyślną czyli Tabela1, aby było mi łatwiej poruszać się pomiędzy tabelami, gdy w skoroszycie będzie ich więcej, zmienię automatycznie nadaną nazwę.
Ustawiam myszkę w obszarze tabeli , by wyświetlić menu Narzędzia tabel > Projektowanie > Właściwości > Nazwa tabeli.
W dedykowanym polu wpisuję moją nową nazwę. Teraz tworząc tabelę przestawną będę mógł użyć tej nazwy w kreatorze tabeli przestawnej.
Nowe wiersze wstawiane pod wcześniej sformatowaną tabelą staną się jej elementem i automatycznie wejdą do zakresu tworzącego tabelę przestawną.
5. Nie wyświetlane są wszystkie występujące elementy
Czasem gdy tworzymy tabelę przestawną potrzebny jest nam układ nie tylko dla elementów, które występują w źródle danych (np. tylko dla miesięcy na których są dane) ale pełny zakres, by mieć wszystkie miesiące – pełny układ.
W widocznej tabeli dane wyświetlone są tylko dla miesięcy gdzie są zawarte wartości w dane. Ale powiedzmy że mamy potrzebę zaprezentowania danych dla wszystkich miesięcy pomiędzy skrajnymi wartościami (pomimo tego, że nie ma dla nich wartości).
Ustawiam się myszką na nazwie miesiąca > prawy przycisk myszy > Ustawienia pól > Układ i drukowanie > Pokaż elementy bez danych.
Po tej operacji tabela wygląda jak poniżej.
Pobierz plik Excel
Pobierz plik 5-problemow-z-tabelami-przestawnymi i umieść własne dane.
-1024x269.png)
Uwielbiam skuteczneraporty.pl!! Mimo że pracuję z excel-em ponad 10 lat to tu zawsze znajdę coś czego jeszcze nie znałam, dzięki.
Ja uwielbiam takich wiernych Czytelników 😉
Witam, a co zrobic jeseli przy tworzeniu pivot table nie sa podane wszystkie mozliwe kategorie danych jakie mam w tabeliw ktora wpisuje dane do analizy?
Hej Renata,
odśwież tabelę przestawną (Dane > Odśwież wszystko).
Jeśli to nie pomoże sprawdź Źródło danych tabeli przestawnej (stań na pivocie > Analiza tabeli przestawnej > Zmień źródło danych).
Dzięki, nie znałam.
Przy problemie nr 5 – jeśli dane ciągnę z modelu danych, to nie mogę wykorzystać opcji „Pokaż elementy bez danych”. Jest jakiś sposób na obejście tego?
Da się! Jest to wtedy w opcjach tabeli przestawnej: Opcje tabeli przestawnej > Wyświetlanie > Pokaż elementy bez danych w wierszach. Ważne jest, żeby pracując na modelu posługiwać się tabelą słownikową Kalendarz, która jest pełniejsza niż dane transakcyjne.
Witam, co to są modele danych, gdzie mogę je znaleźć i do czego wykorzystać?
Model danych tworzy się w dodatku Power Pivot do programu Excel. Daje on 3 główne możliwości:
1. Budowa tabel przestawnych na źródle > 1 mln rekordów
2. Łączenie kilku tabel transakcyjnych (np. sprzedaż i budżet)
3. Tworzenie dowolnych kalkulacji w języku DAX.
Więcej informacji na stronie:
ExcelBI.pl
Często, kiedy w jednym pliku mam kilka tabel przestawnych, to po zmianie filtrów w jdnej z tabel, zmieniają się też filtry w innych. Jak tego uniknąć?
Mi też tak parę razy się zdarzyło, gdy tabelę przestawną tworzyłem poprzez skopiowanie innej. Najbezpieczniej unikać kopiowania. W Excelu 365 wydaje mi się, że już się tak nie dzieje.
Czyli jednak coś wiem czego inni nie wiedzą :p Może w O365 jest domyślnie tworzenie tabeli przestawnej na osobnym łączu. Po szczegóły odsyłam tutaj:
https://www.altkomakademia.pl/baza-wiedzy/qna/discussion/8511/tabele-przestawne-ms-excel-2010#Comment_12010
Dzięki za komentarz. Pisałem kiedyś o tym w artykule, ale zastanawiam się, jaki w praktyce może to mieć sens. Teraz każdą tabelę przestawną wstawiam na zapytaniu Power Query i dzięki temu plik waży jeszcze mniej. Link do mojego artykułu: https://skuteczneraporty.pl/blog/co-to-jest-pamiec-podreczna-tabeli-przestawnej-pivot-cache-w-excelu/
Naprawdę mi pomogłeś, Brawo!
Dzięki!
Genialny wpis!
Od tygodnia głowiłam się, co zrobić, żeby w tabeli przestawnej wyświetlały mi się np. nazwy miesięcy, jeśli nie występował w nich dany koszt. Było to ważne; szczególnie przy porównywaniu dwóch dłuższych okresów (lat) zależało mi, żeby tabele miały ten sam format – łatwiej i szybciej ogarnąć wszystko wzrokowo.
Dziękuję! Dziękuję! Dziękuję! Punkt 5. jest fantastyczną odpowiedzią na mój problem.
Cieszę się, że mogłem pomóc. Jeśli pojawią się inne problemy w tabelach przestawnych, proszę pisać na kontakt.
Dzień dobry,
A ja mam pytanie i prośbę dotyczącą pokazania dat jak w pkt 5, ale zejście z poziomem do tygodnia i pojedyńczego dnia. Mam dwa zestawy danych, jeden dla zamówień, jeden dla ofert. Teoretycznie kolumna z datami wygląda tak samo. Jednak w przypadku zamówień tabela rozwija mi się do poziomu dnia, dla ofert już tylko do poziomu miesiąca. Nie mogę znaleźć przyczyny..
Kliknij prawym na pierwszą datę dla miesiąca > Grupuj > zaznacz dni. Powinno zadziałać.
Czy jest sposób na zamianę tabeli przestawnej (zachowując formatowanie, dane itd.) na zwykłą tabelę?
Wydaje mi się, że nie. Taki sposób dotyczy jednak tabel przestawnych o model danych utworzony z Power Pivotem. Szczegóły:
http://excelbi.pl/funkcje-modulowe-i-konwertowanie-tabeli-przestawnej-na-formuly/
pobieram dane z tabeli przestawnej do innej tabeli. Przy osobach których nie ma w tabeli przestawnych pojawia mi się błąd (###). Co zrobić aby w danej komórce podstawiła się wartość zero zamiast błędu.
Z góry dziękuję za odp.
Dziękuję za komentarz. Co masz na myśli, pisząc „Pobieram dane z tabeli przestawnej do innej tabeli”?
### oznaczają zwykle za wąską komórkę, aby wyświetlić cały tekst w komórce.
Jeśli to jednak inny temat, tabela przestawna ma dodatkowe ustawienie w opcjach: Prawy przycisk na tabeli > Opcje tabeli przestawnej > karta Układ i formatowanie > grupa Formatowanie. Są tam 2 ustawienia:
1. Dla błędnych wartości pokaż…
2. Dla pustych wartości pokaż…
Witam Panie Bartku!
Prosilbym o radę bo nie mogę sobie dać rady z tym problemem w Exel od roku…
Co tydzień do mojej tabeli przestawnej w pierwszym arkuszu wklejam nowe dane dotyczące tej samej grupy klientów.
Przy generowaniu nowego zestawienia danego klienta okazuje się że część pozycji normalnie zlicza się razem – oczywiście mają tą samą czcionkę i treść, natomiast część danych występuje jako osobna pozycja wygląda to mniej więcej tak
ABC – pudełko 12 sztuk dalej ilości
ABC – pudełko 12 sztuk dalej ilości
czyli ta sama treść ale w dwóch osobnych pozycjach.
Żeby to naprawić muszę każdy produkt z osobna wyfiltrować i skopiować pierwszą z góry nazwę na pozostałe i nagle się naprawia, ale ponieważ tych pozycji mam z 70 tygodniowo więc MASAKRA.
proszę o pomoc
Proszę o podesłanie pliku – zerknę do środka.
Mam ten sam problem co opisuje Anna. W momencie jak pobieram dane z tabeli przestawnej do innej tabeli (nie jest to tabela przestawna). Wykorzystuję funkcję weźdaneztabeli. W przypadkach dla których nie ma danych w tabeli przestawnej w tabeli „nieprzestawnej” pojawia się błąd. Chciałbym aby w miejsce błędu pojawiała się wartość 0.
Nie wiem, czy dobrze interpretuję, ale wydaje mi się, że wystarczy wokół WEŹDANETABELI dodać w formule JEŻELI.BŁĄD. Jeśli to nie ten scenariusz, proszę o podesłanie pliku na kontakt@skuteczneraporty.pl.
Mam problem z tabelą przestawną, który nie jest tutaj opisany. Otóż jak tworzę tabelę przestawną to w danych mam tabelę z 0, 1 i 2 natomiast w stworzonej tabeli są same 0. Nie wiem jak to ugryźć…
Proszę podesłać plik na kontakt@skuteczneraporty.pl – zerkniemy do środka.
Witam,
Odnośnie punktu 3. A czy da się zmienić dla kilku kolumn na raz? Często mi się zdarza że mam np 12 miesięcy i dla każdego zmienianie ręczne jest bardzo uciążliwe.
Nie da się, ale zwykle w takich przypadkach proponuję dane odpivotować w Power Query – wtedy otrzymasz 2 kolumny: Miesiąc i Wartość, które lądują odpowiednio na półce Wiersze i Wartości. Wtedy masz 1 wartościach tylko 1 miarę zamiast 12. Szczegóły operacji unpivot są w lekcji pokazowej kursu Power Query: https://skuteczneraporty.pl/kurs-power-query/
A co w przypadku, gdy mam w tabeli źródłowej daty (sformatowane prawidłowo), a pivot nie rozpoznaje ich w ten sposób? Traktuje je jak wartości liczbowe….
Hej Michał, dzięki za pytanie. Nie zdarzyło mi się tak, jak opisujesz. Próbowałbym załadować dane do tabeli przestawnej przez Power Query zamiast wstawiać od razu. Niemniej podeślij proszę plik na kontakt@skuteczneraporty.pl – zerknę, czy wszystko OK.
A ja mam problem następujący:
chcę skorzystać z funkcji WEŹDANEATABELI.
Zaznaczam to w opcjach (analiza-opcje)
i nic… nadal odwołanie jest do adresu wskazanej komórki?
no po prostu nie wiem…i oczywiście proszę o pomoc w rozwiązaniu.
No i oczywiście jestem fanką strony od nie wiem ilu lat…pozdrawiam
Już wszystko wiem. Także sprawa nieaktualna.
Cieszę się, że udało się znaleźć rozwiązanie.
Dzień dobry
Mama problem z tabelą przestawną. Gdy tworzę wykres słupkowy i mam w nim również ujemne dane,korzystam przy formatowaniu z serii danych i wybieram funkcję „Odwróć, jeżeli ujemne” co pozwala na dodanie drugiego koloru jako wartości ujemne. Dla wartości ujemnych wybieram np. kolor czerwony i jest pięknie ;-). Po zapisaniu pliku, zamknięciu i ponownym uruchomieniu kolor zamiast czerwonego robi się biały. Przy takim samym wykresie, ale nie przestawnym problemu nie ma. Jakiś pomysł? Będę wdzięczny, Pozdrawiam.
Znam ten błąd tabel przestawnych i znam na niego 3 sposoby:
1. Budowa tabelki przejściowej i wstawienie zwykłego wykresu
2. Budowa 2 serii danych (dodatniej i ujemnej), np. za pomocą pola obliczeniowego, które się na siebie nakłada.
Sprzedaż+ = JEŻELI(Sprzedaż>0;Sprzedaż;0)
Sprzedaż- = JEŻELI(Sprzedaż<=0;Sprzedaż;0) 3. Inny sposób to wypełnienie deseniem w kolorach zielonym i czerwonym – pomysł podesłany przez Krzysztofa, który uczestniczył w Kursie Tabele przestawne i zapytał o to samo. https://skuteczneraporty.pl/kurs-tabele-przestawne-excel/
Dzień dobry
pierwszy raz tu jestem i może Pan mi pomoże.
wydawało mi się, że już dużo wiem o tabelach i jednego nie mogę rozgryźć: jak sprawić żeby przy kopiowaniu arkusza z tabelą przestawną zmieniał się również zakres danych do tej tabeli na dane w nowym arkuszu.
będę bardzo wdzięczny za pomoc, dziękuję
Dzięki za pytanie. Automatycznie się nie da. Trzeba ręcznie znaleźć opcję Zmień dane.
OK, dziękuję. trzeba będzie sobie makro napisać 🙂
Witam,
Mam problem. Tworząc tabelę przestawną z zakresu danych kolumn od A do Af, wiersze przybywają. Po odswieżaniu gubiło dane z kilku kllumn. Zgodnie ze wskazówkami, z danych utworzyłam tAbelę na której podstawie utworzyłam t.przestawną. Ale niestety przy filtrowaniu danych w tabeli zwykłej nie moge dodawać nowych wierszy. Jak mogę to obejść??
Hej Edyta, prześlij plik na kontakt@skuteczneraporty.pl – rzucę okiem, na czym polega problem.
Cześć Bartek,
1. Czy jest jakiś sposób, aby Excel nie ingerował w wysokość wierszy przy tworzeniu nowej tabeli przestawnej? Ja lubię mieć ją trochę większą, ale przy każdym utworzeniu pivota następuje autodopasowanie tych wierszy, w których jest tabela przestawna… Czy można temu zaradzić?
2. Czy jest jakiś sposób aby utworzyć relacyjny model danych w jednym pliku, a tabelę przestawną opartą na nim w innym pliku? Czy można tutaj zdefiniować jakieś połączenie aby to zadziałało? Chciałbym oddzielić mocno dane od analizy, a nie za bardzo znam się na PowerPivot 😬
Z góry dziękuję Ci za info 🙂
Cześć Mariusz, dzięki za pytania.
Ad. 1 Próbowałem to odwzorować w moim Excelu 365 i o ile autodopasowanie kolumn jest domyślnie włączone, to z wierszami u mnie się nic nie dzieje przy wstawianiu tabel przestawnych. Autodopasowanie kolumn można już wyłączyć w opcjach Excela 365: Opcje > Dane > Edytuj układ domyślny > Opcje tabeli przestawnej.
Ad. 2. Wg mnie nie. Kiedyś planowali to zrobić w powiązaniu z SharePointem (jest po tym ślad w interfejsie Power Pivot na karcie Narzędzia główne > Pobierz dane z Analysis Services lub Power Pivot), ale new pewno nigdy to nie działało między lokalnymi plikami. Można za to użyć środowiska Power BI Online i podłączyć się tabelą przestawną do modelu opublikowanego tam. Dzielnie się raportami w takim scenariuszu będzie jednak wymagało kont Pro.
Cześć Bartek,
Bardzo Ci dziękuję za odpowiedź.
Odnośnie punktu nr 1, chodzi mi o taką sytuację.
https://www.makroaplikacje.pl/wp-content/uploads/2022/06/pivot_autodopasowanie_wierszy.png
Ustawiam wysokość wierszy w arkuszu na 40. Tworzę tabelę przestawną (obojętnie na jakim źródle), no i wszystkie wiersze, w których mieści się tabela, dopasowują swoją wysokość do rozmiaru czcionki.
Ale faktycznie, gdy zrobimy to na nowym arkuszu, wszystko będzie ok – wysokość wierszy pozostanie bez zmian. Widocznie jest to jakiś bug. Dopytam o to na excelforum :-).
Ad. 1 – próbuję, ale nie udaje mi się tego powtórzyć. Wygląda jak bug w konkretnym pliku.
Dzień dobry,
mam problem z wykresem przestawnym. Stworzyłam raport sprzedaży dla wybranych klientów w podziale na miesiące na bazie tabel przestawnych. Dla każdego wykresu jest odrębna tabela przestawna. Jeśli wybiorę np. dwóch klientów z filtra (fragmentatora) to słupki na wykresie są o takich samych kolorach. Zmieniam styl wykresu, żeby słupek dla każdego kontrahenta miał inny kolor, a nie taki sam. Zapisuję i zamykam plik. Po ponownym otwarciu pliku kolory słupków są takie same, tak jakby nie zapisał się wybór stylu, choć jest wybrany styl z różnymi kolorami słupków. Proszę o pomoc z czego to wynika brak zapisu odpowiedniego formatowania i jak można to naprawić.
Cześć Agnieszka, dziękuję za pytanie. Też mi się kiedyś zdarzyło gubić formatowania, ale w nowym Excelu (365) ten problem mi zniknął. Jak miałem problem znikania formatowania, bawiłem się makrami (On Pivot Table Update – można ustawić krótką linijkę przywracającą formatowanie wykresu po odświeżeniu tabeli przestawnej) lub uciekałem z tabeli przestawnej (do wykresów, które na nich bazują, ale nie są to wykresy przestawne). Żeby precyzyjnie pomóc, potrzebuję otrzymać plik na maila i opis problemu dla konkretnego pliku. Jeśli nie możesz przesłać oryginału, spróbuj stworzyć analogiczny wykres na przykładowych danych, które znajdziesz w kursach w Akademii SkuteczneRaporty.pl: https://skuteczneraporty.pl/13-godzinny-kurs-excel-za-darmo/
Mhm, mam trywialny problem.
Mam tabele przestawną, gdzie 1 kolumna ilość zamówien, 2 kolumna-ilosc zamowien z brakami (wiele komorek ma nulla). Jak w podsumowaniu dodaje pole obliczeniowe ilosc zamowien z brakami/ilosc zamowien pokazuje mi blad #dziel0!, jak to można ominąć
Hej, najłatwiej prawy na tabeli przestawnej > Opcje tabeli przestawnej > sekcja Układ i formatowanie > zaptaszkuj Dla błędnych wartości pokaż i zostaw puste.
Witam!
Mam problem, którego nie moge rozgryzc.
Pierwsza kolumna w tabeli to data. Co tydzien dodaje ta sama ilosc wierszy z nowa data (i nowymi danymi). Z tygodnia na tdzien tabela sie wydluza. Na podtsawie tej tabeli jest wygenerowana tabela przestawna
Dzis zauwazylem, ze tabela przestawna 'zgubila’ czesc danych dla jednego z tygodni. Zamiast pokazywac wartosci dla wszystkich wierszy ~115, pokazuje tylko dla jednego
Jakies podpoiwedzi jak temu zaradzic?
Moje pomysły:
1. Zerknij, czy prawidłowo jest wczytane źródło danych do tabeli przestawnej (karta Analiza tabeli przestawnej > Zmień źródło danych).
2. Upewnij się, że tabela przestawna się odświeżyła.
3. Zerknij, czy wiersze się czymś różnią między sobą.
4. Wstaw tabelę przestawną od nowa.
Jak to nie działa, możesz podesłać plik do mnie na maila.
Witam,
a ja mam inny problemz tabela przestawną, czy da się to zrobić, tak aby z pola w którym obliczamy średnią, podsumowanie na najwyższym poziomie było sumą średnich z niższego poziomu, dane wygładaja tak:
Etykiety wierszy Zatrudnienie
1 Dział 6,71 – tu chcę widzieć sumę średnich z niższego poziomu danych, czyli 17, a nie średnią
Agencje umowy o pracę 2,00 – tu jest wyliczona średnia dla zatrudnienia
Agencje zlecenia
umowy o pracę 5,00 – tu jest wyliczona średnia dla zatrudnienia
zlecenia 10,00 – tu jest wyliczona średnia dla zatrudnienia
2 Dział ———————————————————–
Cześć Michał, da się to zrobić w Power tabeli przestawnej, czyli z dodatkiem Power Pivot (lub w aplikacji Power BI). Podeślij przykładowy plik na mój e-mail kontakt(at)skuteczneraporty.pl – pomogę Ci napisać tzw. miarę DAX.