Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Wielowymiarowa tabela

translation · Świeżak Wersja: Win Office 365 Posty: 2

Cześć,

próbowałem szukać po słowach związanych z wielowymiarowością itd., ale nie znalazłem podobnego tematu.
Czy w pliku mającym układ taki, jak zaprezentowałem, da się zrobić jakąś tabelę przestawną albo w miarę dynamiczną formułę, która będzie zbierać dane w taki sposób, że łatwo będzie mi (i w sposób dynamiczny, ew. po filtrowaniu tabeli przestawnej) przeglądać sprzedaż grupy produktowej 1 przez dywizję 3 w kanale sales internet?

Zaznaczam, że chodzi mi o rozwiązanie niezwiązane z Power Pivot czy makrami, jako że w tych tematach jestem jeszcze zielony. ;)

Pozdrawiam!

problem excel.xlsx

Pobierz Plik ściągnięto 29 raz(y) 12.88 KB

bodek · Wersja: Win Office 2019 **Pomógł:** 1060 razy Posty: 3125

Jeżeli chcesz filtrować=raportować dane za pomocą TP, to dane te muszą mieć odpowiednią konstrukcję.
Podstawowa i nienegocjowalna :-D

zasada jest taka, że informacje o typie, który chcesz potem filtrować muszą być w JEDNEJ kolumnie. Czyli tylko takie kolumny:
grupa,
produkt,
dywizja,
spółka,
sales shops,
sales intenet,
sales B@B,

lub zamiast 3 ostatnich
type of sales
wartość

zrobisz sobie wtedy co i jak chcesz (no niemalże).
Czyli tabela z małą, w/w ilością kolumn, ale za to dużo wierszy

translation · Świeżak Wersja: Win Office 365 Posty: 2

Ech, no właśnie. :(
A z ciekawości, narzędzia takie jak Power Pivot są w stanie takie coś obrobić szybko?

To, co przesłałem w załączniku, to tylko przykład - dane rzeczywiste są o wiele, wiele większe i przerabianie ich byłoby bardzo czasochłonne.

dziękuję za odpowiedź!

Maciej Gonet · Wysłany: 20-03-2023, 21:51

W nowym Excelu jest dość uniwersalna funkcja FILTRUJ, która pozwala na filtrowanie zarówno po wierszach, jak i po kolumnach, ale nie równocześnie. Jeśli mamy filtrowanie 2-wymiarowe, to trzeba użyć tej funkcji dwukrotnie.
Zobacz w załączniku.

problem excel.xlsx

Pobierz Plik ściągnięto 23 raz(y) 14.44 KB

bodek · Wersja: Win Office 2019 **Pomógł:** 1060 razy Posty: 3125

Do przerabiania takich danych służy Power Query. Ręcznie przy kilku... spółkach to dramat :-/

Power Pivot - nie ta bajka :-P

, tego potrzebujemy jak masz wiele tabel i/lub wskaźniki obliczane za pomocą standardowych TP nie dają rady.

Osobiście funkcje do analizy bym sobie odpuścił, ale może się mylę.

Reasumując -> niech się wypowiedzą, coś zaproponują w kwestii przekształceń tabeli fachowcy od PQ.

xfish · Wysłany: 21-03-2023, 07:58

Trochę zabawy ale formułami też się da.

Kopia problem excel.xlsx

Pobierz Plik ściągnięto 18 raz(y) 18.97 KB

Bill Szysz · Wysłany: 21-03-2023, 09:50

Troche ponad rok temu napisałem sobie LAMBDĘ do "unpivotowania" danych. Zrobiłem to trochę z ciekawości a trochę z potrzeby doprowadzenia danych (z przeklejonej jako wartości Tabeli przestawnej) do formy zdatnej do przetwarzania. Nie chciałem uzywac PQ ponieważ zależało mi pełnej dynamice. W przypadku zaprezentowanym w tym temacie ta funkcja będzie jak znalazł.
Zapewni odpowiedni układ danych do budowy TP a więc dostaniemy dokładnie to co chcesz.
Przykład wraz z TP w załączniku.

problem excel_BS.xlsx

Pobierz Plik ściągnięto 22 raz(y) 29.97 KB

DwaNiedźwiedzie · Wysłany: 21-03-2023, 10:09

To ja się zgłaszam z rozwiązaniem w PQ :)

1) Zaznacz dowolne pole w Twojej tabeli i z menu wybierz Dane -> Z tabeli/zakresu.
2) Wyskoczy okienko "Utwórz tabelę" - odznacz opcję "Moja tabela ma nagłówki" (ma być pusta). Ważne jest żeby zaproponowany zakres obejmował tylko samą tabelę, bez zbędnych wierszy nad, pod czy obok (w przykładowym pliku będzie to $A$3:$AF$11). Naciśnij OK.
3) Otworzy się edytor PQ, z menu wybierz "Narzędzia -> Edytor zaawansowany".
4) Otworzy się kolejne okienko z kodem w stylu let Źródło = ... in Źródło. Zwróć uwagę na fragment [Name = "Tabela1"], nazwa w cudzysłowach jest istotna.
5) Zastąp cały domyślny kod poniższym, ewentualnie podmień nazwę Tabela1 w pierwszym kroku na tę z punktu 4.
6) Kliknij "Gotowe", a następnie "Zamknij i załaduj do..." i wybierz opcję "Raport w formie tabeli przestawnej".

Kod:

let
Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
#"Transponowano tabelę" = Table.Transpose(Źródło),
#"Anulowano przestawienie innych kolumn" = Table.UnpivotOtherColumns(#"Transponowano tabelę", {"Column1", "Column2", "Column3"}, "Atrybut", "Wartość"),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Anulowano przestawienie innych kolumn", each [Column1] = null and [Column2] = null and [Column3] = null),
#"Usunięto inne kolumny" = Table.SelectColumns(#"Przefiltrowano wiersze",{"Atrybut", "Wartość"}),
#"Pogrupowano wiersze" = Table.Group(#"Usunięto inne kolumny", {"Atrybut"}, {{"lst", each Text.Combine(_[Wartość], "|"), type text}}),
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Anulowano przestawienie innych kolumn", each [Column1] <> null or [Column2] <> null or [Column3] <> null),
#"Scalone zapytania" = Table.NestedJoin(#"Przefiltrowano wiersze1",{"Atrybut"},#"Pogrupowano wiersze",{"Atrybut"},"Nowa kolumna",JoinKind.LeftOuter),
#"Rozwinięty element Nowa kolumna" = Table.ExpandTableColumn(#"Scalone zapytania", "Nowa kolumna", {"lst"}, {"lst"}),
#"Podzielono kolumnę według ogranicznika" = Table.SplitColumn(#"Rozwinięty element Nowa kolumna","lst",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"grupa", "produkt"}),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Podzielono kolumnę według ogranicznika",{{"Column1", "dywizja"}, {"Column2", "spółka"}, {"Column3", "rodzaj"}, {"Wartość", "wartość"}}),
#"Usunięto kolumny" = Table.RemoveColumns(#"Zmieniono nazwy kolumn",{"Atrybut"}),
#"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Usunięto kolumny",{"dywizja", "spółka", "rodzaj", "grupa", "produkt", "wartość"})
in
#"Zmieniono kolejność kolumn"