Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Przekształcenie danych

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

Mam taki problem mam dane jak w załączonym pliku w arkuszu "Dane" i chciałbym żeby np. za przyciśnięciem jednego przycisku wyszły tak przekształcone dane jak w arkuszu "Wynik".
Teraz utworzyłem to ręcznie kolumny B,C i D a dobrze żeby to działo się automatycznie bo co tydzień może być inna liczba eanów i sklepów (i dużo więcej niż w przykładzie).
Dodatkowo cenę zrobiłem formułami indexem i podaj pozycję a może to jakoś w kodzie zapisać?
I ostatni temat w arkuszu "Dane" w "G7" jest puste to w arkuszu "Wynik" w "D66" też niech będzie puste a nie "0"

sklepy.xlsm

Pobierz Plik ściągnięto 13 raz(y) 15.09 KB

Kaper · Wysłany: 18-09-2024, 10:03

Jak to zrobić - sposobów może być wiele, poczynając od Twojego, czyli formuł, przez makro, po Power Query (Dane-> Pobierz i przekształć). Na tym ostatnim się skupię, bo wydaje mi się, że to zdecydowanie najlepsze narzędzie do takiej pracy.
Robi się to w kilku (nawet nie kilkunastu!) kliknięciach (no i kilka literek wpisanych z klawiatury).
Obecność pustych komórek, które mają być w wyniku także przedstawione jako puste, a nie pominięte (taki byłby "naturalny" skutek zastosowania "odpiwotowania") dodaje do całej zabawy dwa kroki (ReplacedValue i ReplacedValue1).

Zobacz załacznik i kod (był wyklikany z interfejsu).

Kod:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"EAN", type text}}),
ReplacedValue = Table.ReplaceValue(ChangedType,null,"puste",Replacer.ReplaceValue,{"Sklep 1", "Sklep2", "Sklep3", "Sklep4"}),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(ReplacedValue, {"EAN"}, "Attribute", "Value"),
AddedCustom = Table.AddColumn(UnpivotedOtherColumns, "Custom", each [EAN]&[Attribute]),
SortedRows = Table.Sort(AddedCustom,{{"Attribute", Order.Ascending},{"EAN", Order.Ascending}}),
ReorderedColumns = Table.ReorderColumns(SortedRows,{"Custom", "EAN", "Attribute", "Value"}),
ReplacedValue1 = Table.ReplaceValue(ReorderedColumns,"puste","",Replacer.ReplaceValue,{"Value"})
in
ReplacedValue1

Nietypowe wyklikanie - to zmiana typu tylko dla kolumny EAN w pierwszym kroku. PQ domyślnie zmieniłby typ wszystkim kolumnom.

A jedyną rzecz, którą należałoby zmienić "niewyklikując" w docelowym kodzie, to lista kolumn, w których trwa poszukiwanie pustych wartości (czyli lista sklepów) A tych, jak napisałeś będzie więcej, i nie wiadomo ile.

zatem trzecią linię kodu można zamienić na taką (w pliku już tak jest)

Kod:

ReplacedValue = Table.ReplaceValue(ChangedType,null,"puste",Replacer.ReplaceValue,List.RemoveMatchingItems(Table.ColumnNames(ChangedType),{"EAN"})),

PS no i jeszcze warto by pozmieniać nagłówki kolumn na łądniejsze, ale tego nie robiłem, żeby nie zaciemniać kodu i nie płoszyć :-)

sklepy.xlsm

Pobierz Plik ściągnięto 17 raz(y) 33.18 KB

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

Dzięki