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: 64377 Skopiuj do schowka Jak otrzymać wyniki w oczekiwanym układzie danych?
Autor Wiadomość
nunus67 
ExcelSpec


Pomógł: 172 razy
Posty: 505
Wysłany: 10-03-2019, 11:19   Jak otrzymać wyniki w oczekiwanym układzie danych?

Stawiam moje pierwsze kroki w temacie Power Query.
Natrafiłem na idealną moim zdaniem okazję do nauczenia się czegoś w tym temacie.
Kierujący oddziałem przygotowania produkcji codziennie wypełniają raporty i zapisują je na dysku według klucza rrrrmmdd.xlsm. Jest potrzeba uzyskania odpowiednich KPI z tych raportów, przedstawienia ich w formie wykresów itd...
Czyli dane z każdego raportu dziennego muszą trafić do raportu zbiorczego. Mógłbym tutaj użyć makr, ale to już umiem...

Na podstawie tej prezentacji "wyklikałem" sobie dane z plików źródłowych. Proszę tu o wyrozumiałość :oops: robiłem coś takiego po raz pierwszy...

Jednak potrzebowałbym uzyskać te dane w trochę innym układzie (arkusz "Oczekiwany wynik").
Obejrzałem kilka filmików na youtube i wiem że możliwości PQ są naprawdę ogromne.

1. Czy jest możliwość uzyskania danych "na gotowo" w takim układzie jak to przedstawiłem. Jeśli tak to w jaki sposób?
2. Czy można to moje klikanie jakoś uprościć?
3. Byłbym wdzięczny za linki, tytuły książek, ogólnie wszystko co pomogłoby by mi lepiej poznać PQ.

20180406.xlsm
Pobierz Plik ściągnięto 21 raz(y) 67.23 KB

20180407.xlsm
Pobierz Plik ściągnięto 16 raz(y) 66.76 KB

20180408.xlsm
Pobierz Plik ściągnięto 14 raz(y) 66.69 KB

Preparation DATA.xlsx
Pobierz Plik ściągnięto 22 raz(y) 24.28 KB

_________________
Nunus67
ID posta: 363906 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 215 razy
Posty: 552
Wysłany: 10-03-2019, 18:33   

W PQ wszystko jest możliwe :)
Kod:
let
    Źródło = Folder.Files("C:\Users\DN\Desktop\pliki"),
    #"Przefiltrowano wiersze" = Table.SelectRows(Źródło, each ([Extension] = ".xlsm")),
    #"Dodano kolumnę niestandardową" = Table.AddColumn(#"Przefiltrowano wiersze", "arkusze", each Excel.Workbook([Content])),
    #"Dodano kolumnę niestandardową1" = Table.AddColumn(#"Dodano kolumnę niestandardową", "arkusz", each Table.SelectRows(_[arkusze], each (_[Name] = "Shift report preparation"))[Data]{0}),
    #"Dodano kolumnę niestandardową2" = Table.AddColumn(#"Dodano kolumnę niestandardową1", "transp", each Table.FromRows({{[arkusz][Column2]{3},[arkusz][Column2]{8},[arkusz][Column9]{8},[arkusz][Column9]{10},[arkusz][Column12]{6},[arkusz][Column16]{6},[arkusz][Column12]{8},[arkusz][Column14]{8}},
{[arkusz][Column2]{3},[arkusz][Column2]{16},[arkusz][Column9]{16},[arkusz][Column9]{18},[arkusz][Column12]{14},[arkusz][Column16]{14},[arkusz][Column12]{16},[arkusz][Column14]{16}},
{[arkusz][Column2]{3},[arkusz][Column2]{24},[arkusz][Column9]{24},[arkusz][Column9]{26},[arkusz][Column12]{22},[arkusz][Column16]{22},[arkusz][Column12]{24},[arkusz][Column14]{24}}},{"DATE","SHIFT","SONY","TEMP","ORDERS","QTY","HPKG hours","HPKG orders"})),
    #"Usunięto inne kolumny" = Table.SelectColumns(#"Dodano kolumnę niestandardową2",{"transp"}),
    #"Rozwinięty element transp" = Table.ExpandTableColumn(#"Usunięto inne kolumny", "transp", {"DATE", "SHIFT", "SONY", "TEMP", "ORDERS", "QTY", "HPKG hours", "HPKG orders"}, {"DATE", "SHIFT", "SONY", "TEMP", "ORDERS", "QTY", "HPKG hours", "HPKG orders"}),
    #"Dodano kolumnę niestandardową3" = Table.AddColumn(#"Rozwinięty element transp", "BODY", each [SONY]+[TEMP]),
    #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Dodano kolumnę niestandardową3",{"DATE", "SHIFT", "SONY", "TEMP", "BODY", "ORDERS", "QTY", "HPKG hours", "HPKG orders"})
in
    #"Zmieniono kolejność kolumn"

Jedyną upierdliwością było tu wybranie odpowiednich komórek z pliku źródłowego, ja go sobie zwyczajnie otworzyłem i skrobnąłem formułę, którą skopiowałem dla wszystkich trzech zmian w arkuszu i tak spreparowany tekst przeniosłem do PQ:
Kod:
="{{[arkusz][Column"&NR.KOLUMNY($C$4)-1&"]{"&WIERSZ($C$4)-1&"},[arkusz][Column"&NR.KOLUMNY(C9)-1&"]{"&WIERSZ(C9)-1&"},[arkusz][Column"&NR.KOLUMNY(J9)-1&"]{"&WIERSZ(J9)-1&"},[arkusz][Column"&NR.KOLUMNY(J11)-1&"]{"&WIERSZ(J11)-1&"},[arkusz][Column"&NR.KOLUMNY(M7)-1&"]{"&WIERSZ(M7)-1&"},[arkusz][Column"&NR.KOLUMNY(Q7)-1&"]{"&WIERSZ(Q7)-1&"},[arkusz][Column"&NR.KOLUMNY(M9)-1&"]{"&WIERSZ(M9)-1&"},[arkusz][Column"&NR.KOLUMNY(O9)-1&"]{"&WIERSZ(O9)-1&"}},"
ID posta: 363923 Skopiuj do schowka
 
 
nunus67 
ExcelSpec


Pomógł: 172 razy
Posty: 505
Wysłany: 10-03-2019, 18:55   

DwaNiedźwiedzie napisał/a:
W PQ wszystko jest możliwe :)

Też tak myślę :mrgreen:
Dzięki, będę miał teraz nad czym siedzieć aby samemu uzyskać taki sam wynik.
_________________
Nunus67
ID posta: 363926 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 794 razy
Posty: 3302
Wysłany: 11-03-2019, 10:55   

nunus67, ja bym się zastanowił czy nie dodać arkusza (lub chocby wydzielonego miejsca gdzies z boku) z prostymi formułami które zbierałyby informacje jakie chcesz importować. Byłoby banalnym później importowanie ich do PQ i prawdopowobnie odbywałoby się to znacznie szybciej niż takie indeksowanie jakie w tej chwili chcesz stosować (dla dużej ilości plików, zauważysz różnicę).
Oczywiście to co zaproponowały dwa niedźwiadki też będzie chodzić, więc nie jestem pewien czy jest o co "kruszyć kopię" :-) ))
_________________
Szkolenia z Power Query!!!

Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 363949 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2016
Pomógł: 945 razy
Posty: 2690
Wysłany: 11-03-2019, 22:13   

Ja dodam swoje 3 grosze.

Po pierwsze, problem, którego rozwiązanie zaproponowałeś nie do końca leży w gestii PQ. Dlaczego? Bo PQ ma obrabiać dane tabelaryczne, czyli duże ilości rekordów ..., czasami w wielu tabelach (ten aspekt jest spełniony). Za pomocą VBA generujesz raport - PQ generalnie nie służy do obróbki raportów, ale danych tabelarycznych. Twoje dane "do zebrania" to klasyczny raport, czyli pojedyncze wartości rozrzucone w komórkach po arkuszu - nie tędy droga, raczej nie PQ pomimo iż się da, ale to już "na siłę",

Po drugie, PQ wg mnie służy do przygotowania danych źródłowych w postaci tabelarycznej, do dalszej obróbki (w postaci raportów) poprzez tabele przestawne (w tym np: Power Pivot), czyli raczej nie generujemy raportów ostatecznych, jak określiłeś to tutaj, raczej PQ ma zwrócić tabelę, z której zrobi się raporcik :-) albo nawet kilka innych raporcików.

Po trzecie, DwaNiedźwiedzie, Bill Szysz, to mistrzowie i czasami ciężko się ich "czyta" :-P ale warto podglądać, tyle, że skąpią rozwiązań.
_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu
ID posta: 363978 Skopiuj do schowka
 
 
nunus67 
ExcelSpec


Pomógł: 172 razy
Posty: 505
Wysłany: 13-03-2019, 08:17   

Bill Szysz bardzo spodobała mi się Twoja sugestia :mrgreen:
Na pewno będę o niej pamiętał kdy ponownie usiądę nad innym takim problemem.
Niestety w pracy ja wciąż pracuję z excelem 2010 - powyższy problem jest problemem kolegi szczęśliwca któremu już wymieniono kompa i mógł się przesiąść na office 2016. Rozwiązanie podane przez DwaNiedźwiedzie jak na razie działa bez problemu (zobaczymy pod koniec roku ;-) ). Ja tylko skorzystałem z okazji żeby zacząć wykorzystywać PQ w praktyce a nie jak dotąd "bawić" się nim w domu.

Bodek:
Cytat:
PQ ma zwrócić tabelę, z której zrobi się raporcik :-) albo nawet kilka innych raporcików.

dokładnie tak jest w tym przypadku.

Cytat:
czasami ciężko się ich "czyta"

ehh... z czasem i tego się nauczę...
_________________
Nunus67
ID posta: 364071 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