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.

Kompresja obrazów w Excelu

Każdy, kto kiedyś wstawiał obrazy do Excela wie dobrze, że przy większej ich ilości  skoroszyt “puchnie” znacznie zwiększając swój rozmiar. Można sobie z tym poradzić kompresując osadzone obrazy i znacznie zmniejszając plik.

Jak to zrobić? Pokazuję to tu:

Zapraszam na mój kanał Excela na YT.

Kurs Excel - zaawansowane techniki

Wymuszenie włączenia makr w skoroszycie Excela

Wielu metod ochrony pliku Excela przed wprowadzaniem nieuprawnionych zmian opiera się na makrach i/lub kodzie VBA. Wszystko działa dobrze i sprawnie pod warunkiem, że otwierający plik użytkownik ma włączone makra. Nie da się tego zrobić automatycznie (i dobrze, szczególnie w przypadku plików nieznanego pochodzenia), ale jak spowodować, żeby użytkownik sam to zrobił? Trzeba spowodować, aby plik był bezużyteczny i tylko włączenie makr pozwoli na pracę.

Szczegóły mogą być tu różne, ale generalnie podstawowe zasady powinny wyglądać tak:

    • wszystkie arkusze ukryte kodem VBA
    • jeden dodatkowy arkusz startowy, z przyciskiem makra włączającego dostęp do skoroszytu czyli odkrywające arkusze robocze i zakrywające arkusz startowy. Jeżeli makra będą wyłączone – makro nie zadziała.
    • makro przy zdarzeniu przed zamknięciem skoroszytu (Workbook_BeforeClose) ukrywające wszystkie arkusze robocze i odkrywające arkusz startowy.

To powinno wystarczyć. Wszystkie inne sposoby ochrony pliku można dostosować do własnych potrzeb.

 

 

Kurs Excel dla księgowych

 

Ukrywanie obiektów Excela

Jedną z metod ochrony pliku Excela przed nieuprawnionym działaniem użytkowników jest ukrywanie niektórych obiektów.
Jest tu kilka możliwości:

    • ukrycie kart arkuszy (zakładek)
      karta Plik –> Zaawansowanie –> Pokaż karty arkuszy
      Po odznaczeniu tej opcji nazwy kart arkuszy

będą niewidoczne

 

    • ukrycie arkuszy
      konkretne arkusze można ukryć. Wystarczy zaznaczyć jeden lub kilka arkuszy, a następnie po kliknięciu prawym przyciskiem myszy wybrać Ukryj z menu podręcznego

      Nie da się ukryć  wszystkich arkuszy – co najmniej jeden musi zostać odkryty.
      Odkrywanie arkuszy odbywa się bardzo podobnie – kliknięciem prawy przyciskiem myszy i wybraniem z menu podręcznego Odkryj…
       Pokaże się formularz, w którym widoczne będą wszystkie ukryte arkusze. Zaznaczając arkusz z listy (tu niestety trzeba to zrobić oddzielnie dla każdego arkusza) i naciskając OK – odkrywamy go.

    • ukrycie arkuszy kodem VBA
      arkusze mogą zostać także ukryte “głęboko”, tak, aby nie można było ich w prosty sposób odkryć. Jest to możliwe w kodzie VBA z wykorzystaniem właściwości Visible arkusza. Do wyboru jest tu:

        • xlSheetHidden
          arkusz zostanie ukryty, tak jak po ukryciu w menu podręcznym
        • xlSheetVeryHidden
          arkusz zostanie głęboko ukryty. Nie będzie widoczny na liście arkuszy do odkrycia w formularzu menu podręcznego
        • xlSheetVisible
          odkrycie arkusza

Przykładowa procedura ukrywająca arkusz może wyglądać tak:

Sub UkryjArkusz()
Worksheets(“Arkusz1”).Visible = xlSheetVeryHidden
End Sub

Odkrycie arkusza wymaga również procedury VBA przypisującej Visible=xlSheetVisible

    • ukrycie paska formuły/nagłówków/linii siatki
      domyślnie widoczne, można ukryć w karcie Widok–>Pokazywanie, odhaczając poszczególne opcje:

przy braku zaznaczenia tych opcji widok będzie wyglądać tak:

    • ukrycie formuł komórki
      jeżeli w komórce znajduje się formuła wynik jej działania jest widoczny w komórce, a sama formuła – na  pasku formuł nad nagłówkami kolumn.

Chcąc ukryć zapis tej formuły, przechodzimy do formatowania komórki i w zakładce Ochrona zaznaczamy opcję Ukryj.

Po włączeniu ochrony arkusza zapisana w komórce formuła jest niewidoczna (choć sam wynik jej działania nadal jest widoczny w komórce):

 

 

Poprawność danych w Excelu

Wymuszanie poprawności danych w Excelu jest jedną z metod zabezpieczania pliku Excel przed nieuprawnioną ingerencją i wpisywaniem błędnych danych, generujących dalsze błędy np. w formułach.
Ograniczenia wprowadzanych danych definiujemy na karcie Dane –> Narzędzia danych –> Poprawność danych

Zaznaczamy komórkę lub zakres komórek, w których chcemy wprowadzić ograniczenie, a następnie klikamy przycisk Poprawność danych. Pojawi się nowy formularz:

Zakładka Ustawienia

Kolejny krok to wybranie z listy kryterium poprawności dla zaznaczonych komórek.
Jest tu kilka możliwości:

    • Dowolna wartość
      – ustawienie domyślne, bez ograniczeń. To opcja, którą należy wybrać przy zdejmowaniu ograniczeń.
    • Pełna liczba
      wartości liczbowe będące liczbą całkowitą.
      Wprowadzane wartości danych można dodatkowo zdefiniować do określonego przedziału liczbowego. Możemy być on wyznaczony na sztywno (czyli z konkretnymi wartościami liczbowymi) lub jako odwołanie do wartości innych komórek.
    • Dziesiętne
      zasady identyczne jak w przypadku Pełna liczba, ale bez ograniczenia do liczb całkowitych.
    • Lista
      tu odwołam się do mojej wcześniejszej notki
      Lista rozwijalna
      gdzie szczegółowo opisałam temat, również w aspekcie tworzenia list dynamicznych
    • Data
      w komórkę musimy wpisać datę (również może być ograniczenie wartości do określonego przedziału dat).
      I na wszelki wypadek przypominam, że w Excelu
      Data to liczba
    • Godzina
      chcąc zadeklarować wpisywanie czasu, również musimy pamiętać, że to liczba, choć w odróżnieniu do daty – ułamkowa.
      Warto też zerknąć tu:
      Czas w Excelu
    • Długość tekstu
      definiujemy ilość znaków jaka musi być w komórce. Jeśli wpiszemy np. większa niż 3 – nie da się wpisać “tak”.
      Warto pamiętać, że spacja też jest znakiem.
    • Niestandardowe
      w tej opcji poprawność danych w komórce definiujemy sami za pomocą formuły, mamy więc możliwość wprowadzenia bardziej złożonych kryteriów.
      Np. dla komórki A1:
      =Czy.Tekst(A1) – ogranicza wprowadzanie danych tylko dla tekstu
      =MOD(A1;10) – wprowadzane tylko wartości podzielnych przez 10
      W podobny sposób można tu zdefiniować inne zasady.

Zakładka Komunikat wejściowy

Domyślnie – jest pusty. Po wypełnieniu pól komunikatu pojawi się on po aktywacji komórki.

Zakładka Alert o błędzie

Wypełnienie pól komunikatu powoduje, że przy wpisaniu wartości niezgodnej z ustawieniami wyświetli się komunikat, a sama wartość nie zostanie wprowadzona do komórki.

Kurs Excel 2010 esencja

Plik Excel tylko do odczytu

Zapisywanie pliku Excel tylko do odczytu jest najprostszym sposobem na udostępnienie pliku bez możliwości wprowadzania (a w zasadzie zapisywania) zmian. Oczywiście można taki plik edytować, ale wymaga to podania hasła.

Plik w trybie do odczytu zapisujemy korzystając z opcji Zapisz jako… i korzystając z Narzędzi.

Po kliknięciu w Narzędzia  i wybraniu Opcje ogólne… pojawi się nowy formularz:

Wpisując Hasło ochrony przed zmianami, potem potwierdzając je, spowodujemy, że przy następnym otwarciu pliku pojawi się monit o wpisanie hasła lub plik zostanie otwarty tylko do odczytu.

Jeżeli przy zapisywaniu hasła w opcjach ogólnych zaznaczymy także checkbox Zalecane tylko do odczytu, wyświetlony komunikat będzie wyglądać tak:

Po wpisaniu i potwierdzeniu hasła wystarczy zapisać plik i od tej pory chcąc go otworzyć – konieczne będzie podanie hasła.

Operacja odwrotna czyli odblokowanie tak zabezpieczonego pliku wymaga ponownego zapisania pliku z wyczyszczonym polem na hasło.

 

 

Otwieranie pliku hasłem

Najprostszym zabezpieczeniem pliku Excel przed dostępem osób nieuprawnionych jest po prostu zapisanie go z hasłem.
Karta Plik –> Informacje –> przycisk Uprawnienia

Po wybraniu opcji Szyfruj przy użyciu hasła pojawi się nowy formularz do wpisania hasła ochrony pliku.

Po wpisaniu i potwierdzeniu hasła wystarczy zapisać plik i od tej pory chcąc go otworzyć – konieczne będzie podanie hasła.

Operacja odwrotna czyli odblokowanie tak zabezpieczonego pliku wymaga ponownego wejścia do
Plik –> Informacje –> Uprawnienia –> Szyfruj przy użyciu hasła
i wyczyszczenie pola na hasło.

 

 

Kurs Excel dla księgowych