Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Dostępność po 2 kanałach

darko2000 · Fan Excela Wersja: Win Office 2019 Posty: 76

Potrzebuję obliczyć dostępność (może jakiś jeden przycisk makra) produktów w poszczególnych kanałach i sklepach.
Przykładowo żeby tworzyło się coś takiego jak w pliku dostepnosc.xlsx arkusze „HYP”, „HYC”, „SUP” (teraz ręcznie to wybrałem). Oczywiście te arkusze do końcowego wyniku mogą być niepotrzebne.

Ean 524023890000006 z arkusza „Ean” wiem, że powinien być tylko w wynikach arkusza „HYP”. W arkuszu "Dane" jak go wyfiltruje i wezmę tylko sklepy HYP to widzę, że jest tylko w HYP2, HYP3, HYP6.
Ean 524018520000006 z arkusza „Ean” wiem, że powinien być w wynikach w trzech arkuszach „HYP”, „HYC”, „SUP”.
Potrzebuję żeby makro przyporządkowało tak wszystkie dane z arkusza ”Dane” (może być różna ilość rekordów).

Końcowy wynik powinien być coś w tym stylu:
HYP1: 25 produkty na 100, HYP2: 12 produktów na 100 itd.
HYC1: 13 produktów na 94, HYC2: 52 produkty na 94 itd.
SUP1: 15 produktów na 81, SUP2: 43 produkty na 81 itd.

dostepnosc.xlsx

Pobierz Plik ściągnięto 20 raz(y) 347.51 KB

Kaper · Wysłany: 22-10-2024, 19:03

Myślę, że najwygodniej zrobić to za pomocą Pobierz i przekształć Dane (czyli Power Query).

Gdybyś bardzo upierał się, żeby nie przerabiać obu tabel z danymi na tabele Excela, trzeba by to zrobić nie "pobierz dane z tabeli" ale "pobierz dane z pliku excela". Ja zrobiłem pobierając dane z tabel (pozwalając Excelowi by przekształcił je w tabele excela).

Najpierw tylko zapytanie (bez tabeli wynikowej) zliczające kody EAN w grupach:

Kod:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PLU", type text}, {"Nazwa produktu", type text}, {"HYP", Int64.Type}, {"HYC", Int64.Type}, {"SUP", Int64.Type}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"HYP", "HYC", "SUP"}, "Typ", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"Typ"}, {{"Licznik", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"

To dosłownie 3 kliknięcia (każde generujące jedną linię kodu m), a jedyna ingerencja - w tym wypadku zbędna, bo i tak nie korzystamy, to zmiana typu pola EAN na tekstowe, na wypadek gdyby pojawił się kod zaczynający od 0.

I drugie zapytanie, które najpierw policzy unikatowe eany dla każdego ze sklepów , a potem połączy z danymi dla tego typu sklepów z powyższego zapytania. nazwę sklepu podzieliłem, a nie tylko wyodrębniłem 3 pierwsze znaki jako typ, bo tak jak jest sortowałyby się SUP1, SUP10, SUP11, ... SUP19, SUP2, SUP20, SUP21 ...
Też w sumie niewiele, bo 6 poleceń (można powiedzieć 7 bo pierwsza zmiana typu to znowu ingerencja w typ EANa, ale już druga zmiana typu została automatycznie dodana przez PQ, więc jej nie liczę :)) :

Kod:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ean", type text}, {"Nazwa", type text}, {"Sklep", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Sklep"}, {{"Licznik_Sklep", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Sklep", "Sklep - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Sklep - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Sklep - Copy.1", "Sklep - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Sklep - Copy.1", type text}, {"Sklep - Copy.2", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{ {"Sklep - Copy.1", Order.Ascending}, {"Sklep - Copy.2", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Sklep - Copy.1"}, EAN, {"Typ"}, "EAN", JoinKind.LeftOuter),
#"Expanded EAN" = Table.ExpandTableColumn(#"Merged Queries", "EAN", {"Licznik"}, {"Licznik"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded EAN",{"Sklep - Copy.1", "Sklep - Copy.2"})
in
#"Removed Columns"

Wynik można oczywiście przerabiać - ja zostawiłem w układzie trzykolumnowym sklep, liczba, liczba dla typu w arkuszu Wynik.

Aby odświeżyć dane po zmianie prawoklik na zielonej tabeli i Odśwież.

PS zostawiłem te dodatkowe arkusze, ale ani z nich, ani z dodatkowych kolumn w arkuszu z danymi ze sklepów nie korzystałem.

dostepnosc.xlsx

Pobierz Plik ściągnięto 19 raz(y) 364.37 KB

darko2000 · Fan Excela Wersja: Win Office 2019 Posty: 76

dzięki za pomoc, szkoda tylko, że strona znowu tydzień nie działała, musiałem sobie jakoś poradzić :beer

Kaper · Wysłany: 28-10-2024, 10:21

Nas też martwią problemy ze stroną. Ostatnio były dwa razy - ten drugi dość szybko usunięty, ale ten pierwszy faktycznie trwał kilka dni.