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:

    • wykorzystanie Power Query
      rozwiązanie preferowane
    • łącza w komórkach
      rozwiązanie stosowane w przypadkach, gdy nie można wykorzystać PQ np. z powodu ograniczeń administracyjnych komputera.

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


Linki w pliku Excela

W niektórych plikach Excela znajdują się łącza do innych plików. Najczęściej są to działania świadome i celowe, pozwalające np. na agregację danych z różnych arkuszy/skoroszytów. Czasem jednak zdarza się, że łączy teoretycznie nie powinno być, a jednak są widoczne.
O ich istnieniu dowiadujemy się z komunikatów wyświetlanych przy otwarciu skoroszytu. W dodatku ich odnalezienie jest trudne, nie można ich także przerwać.

Wprawdzie można wyłączyć aktualizację łączy, ale to tylko maskuje, a nie rozwiązuje problem. Brak komunikatów nie załatwia sprawy.
W tego typu przypadkach najczęściej okazuje się, że łącza do innego pliku wcale nie są wstawione bezpośrednio w komórkach, a nieco bardziej “zagnieżdżone”. Na początek warto więc sprawdzić:

  • ustawienia poprawności danych
    kto wie, czy na którejś z list nie ma odwołania do innego pliku?
  • formatowanie warunkowe
    tu też mogą być łącza

Tego typu sytuacje mogą się zdarzyć, gdy na przykład kopiujemy jakiś zakres komórek z innego skoroszytu, zapominając o tym, że jest tam zdefiniowana lista poprawności danych (przećwiczyłam to na sobie, zajęło mi to sporo czasu).
Jeśli plik jest mało rozbudowany – można to znaleźć ręcznie, choć czasem trzeba się naszukać. Gorzej, gdy plik zawiera wiele arkuszy, w każdym jest sporo danych, tabel i wykresów. Wówczas znalezienie takich odwołań może być naprawdę bardzo pracochłonne. Na takie przypadki jest specjalny dodatek Billa Manville’a:

FindLink

Zainstalowałam go i całkiem często go wykorzystuję. Naprawdę jest przydatny, polecam wszystkim.