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 8 raz(y) 67.23 KB

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

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

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

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



Pomógł: 213 razy
Posty: 549
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ł: 785 razy
Posty: 3275
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ł: 935 razy
Posty: 2638
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