Power Query do pliku Excela

Poprzednia notka PQ do Arkuszy Google opisuje jak ściągnąć dane z pliku Excela w internecie. W codziennej pracy często mamy jednak do czynienia z plikami udostępnionymi gdzieś na serwerze firmowym. Dane tam zapisane są nam potrzebne do analizy i raportowania. Oczywiście w prosty sposób można to zrobić również za pomocą Power Query.

Na karcie Power Query przechodzimy do grupy Pobierz dane zewnętrzne, naciskamy przycisk Z pliku i wybieramy Z programu Excel.

Otworzy się okno dialogowe, w którym wskazujemy plik, z którego chcemy pobrać dane.
Po wybraniu pliku w formularzu Nawigatora zaznaczamy arkusz z danymi.

Następnie naciskamy załaduj i wszystkie dane z pliku źródłowego znajdą się w pliku docelowym. Możemy w dowolny sposób je filtrować, obliczać czy analizować w tabeli przestawnej, jednocześnie nie wpływając na pracę innych użytkowników źródłowego pliku udostępnionego. W dodatku wszystkie zmiany wprowadzone przez innych użytkowników – będą się aktualizować po każdym odświeżeniu połączenia.
Można to zrobić w dowolnym momencie po wejściu w tabelę i naciśnięciu przycisku Odśwież na karcie Dane lub naciśnięciu przycisków Alt+F5.

Warto też zajrzeć do Właściwości połączenia… – w tym miejscu można ustawić zasady automatycznej aktualizacji danych:

W identyczny sposób można też pobierać dane nie tylko z plików Excela, ale również plików tekstowych csv.

 


 

 

Power Query do Arkuszy Google

Power Query to  świetne narządzie pozwalające na wstawianie do pliku Excela łączy z danymi z różnych źródeł m.in. stron internetowych, innych plików Excela, baz danych itp.
Opis jak go pobrać i zainstalować jest tu:
Power Query

Świetnie nadaje się także do pobierania danych z plików udostępnionych czy Arkuszy Google.  Ma to szczególne znaczenie dla analizy danych. Takie współużytkowane pliki mają znaczne ograniczenia w funkcjonowaniu makr (niektóre w ogóle nie działają), wstawiania tabel itp. Arkusze Google to w ogóle pliki Excela bez makr, więc jedyna funkcjonalność to formuły w komórkach. Poprzez PQ można jednak pobrać dane z takich plików do własnego, lokalnego pliku i tu już zastosować bez problemu wszystkie możliwości Excela. Każda zmiana pliku źródłowego jest automatycznie widoczna po aktualizacji łączy.

Jak to zrobić? Dla potrzeb tej notki utworzyłam i udostępniam taki Arkusz Google, więc pokażę to na konkretnym przykładzie. Plik o nazwie TabelaKsiazki.xlsx jest tu:
TabelaKsiazki na dysku Google
– można do niego wejść, a następnie otworzyć i edytować jako Arkusz Google

Po wejściu kopiujemy link do arkusza:

Jeśli ktoś chce przetestować – to link do mojego pliku to:

https://docs.google.com/spreadsheets/d/1Hsq7m2y2O-xgPyV2JeUP4pA7nrzVltUr/edit#gid=737587641

Chcąc wstawić link jako źródło danych do PQ trzeba go jednak zmodyfikować.  Usuwamy to, co za ostatnim ukośnikiem i wstawiamy tam frazę: export?format=xlsx

https://docs.google.com/spreadsheets/d/1Hsq7m2y2O-xgPyV2JeUP4pA7nrzVltUr/export?format=xlsx

I taki link możemy już wstawiać do pliku Excela, gdzie za pomocą PQ chcemy pobrać dane.

Kolejny krok to przejście na kartę Power Query

Po naciśnięciu OK pokaże się okienko z obiektami widocznymi w tym pliku:

Widać tu:

    1. Arkusz o nazwie TabeleKsiazki.
    2. Tabelę w tym arkuszu, również o nazwie TabelaKsiazki

Po prawej stronie podgląd tych obiektów. Jeżeli wybierzemy tabelę – to wprawdzie wszystkie zmiany w tabeli będą się przenosiły do pliku lokalnego, ale już dodanie nowych wierszy nie będzie widoczne. Bezpieczniej jest więc wybrać jako źródło danych cały arkusz.

A tu krótki filmik z mojego kanału YT ilustrujący opisane pobieranie Arkuszy Google do Power Query.

 


 

Praca z plikiem udostępnionym

Plik udostępniony, znajdujący się gdzieś na serwerze firmowym, jest bardzo dobrym rozwiązaniem w bieżącej pracy. Jednocześnie może nam nim pracować i edytować dane wiele osób. Niestety, trochę gorzej z analizą i przetwarzaniem danych. Przede wszystkim w pliku udostępnionym ograniczone jest działanie kodu VBA, niektóre makra i procedury po prostu nie mogą działać, nie można wstawiać tabel itp. Ma to swoje uzasadnienie – jednocześnie pracujący użytkownicy nie mogą sobie przeszkadzać i niweczyć nawzajem swojej pracy. Poza tym – im więcej osób jednocześnie edytuje dane, tym bardziej plik jest obciążony. Trudno też zastosować zaawansowane metody.
Oczywiście, w takich przypadkach zawsze można zrobić kopię lokalną i na niej pracować, ale przy kolejnej analizie – trzeba operację powtórzyć. Zdecydowanie prościej jest stworzyć własny plik na komputerze i połączyć go z danymi w źródłowym pliku danych. W ten sposób można analizować dane w dowolny sposób, tworzyć np. tabele przestawne oparte na prawdziwych danych, przygotowywać zestawienia itp. A każde odświeżenie łączy – powoduje, że zawsze pracujemy na aktualnych danych źródłowych.

Są 2 główne sposoby na takie połączenie pliku:

W kolejnych notkach opiszę szczegółowo obydwa sposoby.


Power Query

Power Query (w skrócie PQ) to bezpłatny dodatek Microsoftu do Excela, który umożliwia pobranie i analizę danych z innych plików(nie tylko Excela, również wielu innych),  stron internetowych itp. Dużym plusem jest to, że dane są wstawiane do tabeli w formie łącza, a więc mogą być automatycznie aktualizowane.

PQ dla wersji Excela 2010 i 2013 trzeba pobrać jako plik ze strony Microsoft Office:
Dodatek Power Query dla programu Excel

Opis instalacji dodatków jest tu:
Dodatki programu Excel

A praktyczne wykorzystanie PQ? To temat rzeka, któremu zamierzam poświęcić wiele notek na tym blogu – zakładam tag Power Query.