Tag: narzędzia Excela

Power Query: połączenie z innym plikiem

Za pomocą Power Query w prosty i szybki sposób można stworzyć dynamiczne łącze do innego pliki Excela. Każda zmiana w pliku źródłowym będzie od razu zaktualizowana i widoczna w tabeli. 

Karta Dane-> grupa Pobierz dane –> Z pliku –>
Ze Skoroszytu

kliknij, aby powiększyć

W kolejnym oknie wybieramy plik Excela, z którego chcemy pobrać dane. 

kliknij, aby powiększyć

Następnie zaznaczamy arkusz:

kliknij, aby powiększyć

 Po kliknięciu Załaduj w prawym dolnym rogu,  nastąpi ładowanie danych do tabeli. 

kliknij, aby powiększyć

Tak jak każdą tabelę również tabelę PQ  można formatować, sortować czy filtrować.  Choć po odświeżeniu (opcja Odśwież po kliknięciu prawym przyciskiem w dowolnym miejscu tabeli) – wszystko wróci do ustawień jak w arkuszu źródłowym. Oczywiście wszystkie zmiany w nim będą też widoczne. 

To najprostszy sposób pobierania danych. Bardziej rozbudowane przykłady – to temat na osobną notkę. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Power Query: lista plików w katalogu

Listę plików w katalogu (oraz w jego podkatalogach)  można szybko i w prosty sposób pobrać za pomocą Power Query. 
Robimy to tak: 

Karta Dane-> grupa Pobierz dane –> Z pliku –> Z folderu

kliknij, aby powiększyć

Otworzy się okno, w którym wskazujemy folder, z którego chcemy pobrać listę plików:

kliknij, aby powiększyć

W kolejnym kroku widoczny jest podgląd zawartości katalogu:

kliknij, aby powiększyć

 Po kliknięciu Załaduj,  nastąpi ładowanie danych do tabeli. Może to chwilę potrwać – w tym czasie na ekranie widoczna będzie informacja:

kliknij, aby powiększyć

Finalnie otrzymujemy tabelę:

kliknij, aby powiększyć

Poszczególne kolumny to:

    • Name
      nazwa pliku
    • Extension
      rozszerzenie
    • Date accessed
      data ostatniego otworzenia pliku
    • Date modified
      data ostatnio zapisanych zmian w pliku
    • Date utworzenia
      data utworzenia pliku

Tak jak każdą tabelę również tabelę PQ  można formatować, sortować czy filtrować.  

Tabelę PQ wstawiamy do pliku raz, potem wystarczy ją tylko odświeżyć. Najszybciej i najprościej – poprzez kliknięcie prawym przyciskiem myszy na tabeli i wybranie opcji Odśwież. 

kliknij, aby powiększyć

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Power Query

Power Query to jedno z narzędzi Excela o bardzo szerokim zastosowaniu. Umożliwia pobranie i połączenie, a finalnie także przetwarzanie danych z różnych źródeł.
Korzystanie z PQ nie wymaga znajomość VBA. Od wersji Excela 2016 jest to narzędzie wbudowane, w wersjach wcześniejszych Excela – konieczne jest (było?) pobranie i zainstalowanie oddzielnego dodatku do Excela. 

Power Query jest dostępne na karcie Dane –> grupa opcji Pobieranie i przekształcanie danych.

Power Query umożliwia pobieranie danych z wielu różnych źródeł, jest tu naprawdę sporo możliwości.
Podstawowe grupy typów to:

    • Z pliku
      do pobierania danych z plików m.in Excela, tekstowych lub JSON, a nawet całych folderów. 

    • Z bazy danych
      tu źródłem mogą być różne typy baz danych
    • Z platformy Azure
      dane pobrane z połaczenia z usługą Azure
    • Z usług online
      czyli to, co można pozyskać z sieci Microsoftu
    • Z innych źródeł
      to, co jest dostępne na stronach internetowych

Każde z nich wymaga osobnego omówienia, co postaram się sukcesywnie robić i rozbudowywać. 

Poprawność danych

Poprawność danych to kolejne, bardzo przydatne narzędzie Excela. Zabezpiecza przed wstawieniem błędnych wartości w komórce, w wyniku których pojawią się błędy w formułach. Najczęściej jest wykorzystywane tam, gdzie powinna być data, a nie tekst przypominający datę czy wartości z określonego zakresu liczb. Umożliwia także wstawienie list rozwijalnych bezpośrednio w komórce. 

Poprawność danych znajduje się na karcie Dane w grupie opcji Narzędzia danych. 

kliknij, aby powiększyć

Zaznaczamy komórkę lub zakres komórek, klikamy w przycisk
i wybieramy regułę poprawności:

kliknij, aby powiększyć

Jest tu kilka możliwości ustawienia kryterium:

    • dowolna wartość 
      – brak kryterium, można wpisać dowolną wartość czy to liczbową czy tekstową
    • pełna liczba
      możliwe do wstawienia tylko liczby całkowite z zadeklarowanego zakresu:
kliknij, aby powiększyć

Konieczne jest także ustawienie zakresu precyzującego zakres, w którym muszą się mieścić liczby:

kliknij, aby powiększyć

Mogą być tu nie tylko liczby, ale także odwołania do adresów komórek z liczbami

      • dziesiętne
        zasady jak w przypadku liczb całkowitych, ale możliwe wstawianie również ułamków
      • Lista
        kliknij, aby powiększyć

        Jako źródło danych można wpisać konkretne wartości oddzielone średnikami np.
        kliknij, aby powiększyć

        zakres komórek lub zdefiniowaną w menedżerze nazw listę:
        kliknij, aby powiększyć

        Efekt końcowy wygląda tak:
        kliknij, aby powiększyć

        Bardzo przydatne rozwiązanie, często stosuję.
      • Data
        kliknij, aby powiększyć

        Podobnie jak w przypadku liczb – konieczne jest wstawienie zakresu dat.
        Biorąc pod uwagę, że różni użytkownicy mają różne pomysły do zapisywania dat (co później skutkuje błędami w formułach) – warto wprowadzić to zabezpieczenie, jest naprawdę przydatne
      • Godzina
        – zasada działania jak w przypadku daty
      • Długość tekstu
        kliknij, aby powiększyć

        W tym przypadku sprawdzana jest ilość znaków wprowadzona do komórki
      • Niestandardowe

        kliknij, aby powiększyć

        tu możemy wpisać własną regułę – opartą na formule

Niezależnie od rodzaju wprowadzonej reguły, Poprawność danych ma też 2 dodatkowe zakładki:

      • Komunikat wejściowy
        kliknij, aby powiększyć

        tu możemy zdefiniować komunikat wyświetlany po wejściu do komórki. Nie ma tu znaczenia, jaka reguła poprawności jest ustawiona, działa także przy dowolnej wartości

      • Alert o błędzie

        kliknij, aby powiększyć

        Ta akcja jest widoczna jeżeli wartość w komórce nie spełnia zasady wprowadzonej do niej reguły poprawności. Warto tu wpisać treść komunikatu informującego jakie wartości są tu dopuszczalne. 

        Możliwe są tu 3 warianty: 

        • Stop (domyślny)
          kliknij, aby powiększyć

          Wprowadzanie danych zostaje zatrzymane, użytkownik musi wpisać prawidłową wartość. 

        • Ostrzeżenie
          kliknij, aby powiększyć

          Wyświetla się ostrzeżenie, choć jeżeli naciśniemy przycisk Tak – Excel pozwoli zostawić w komórce błędną wartość.

        • Informacje
          kliknij, aby powiększyć

          Działanie podobne jak w przypadku Ostrzeżeń. 

Zarówno Ostrzeżenia jak i Informacje pozwalają na wprowadzenie wartości niezgodnych  w regułą poprawności. Chcąc sprawdzić, gdzie taka sytuacja występuje – wystarczy zaznaczyć opcję:


Błędne dane są zaznaczone. W tym miejscu można też usunąć zakreślenia błędnych danych. 
Choć oczywiście te zaznaczenia znikają również po wprowadzeniu prawidłowych danych.

Często korzystam z tego narzędzia Excela – naprawdę warto jet stosować. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Menedżer nazw

Menedżer nazw to jedno z narzędzi Excela, bardzo przydatne w codziennej pracy. Służy głównie do nadawania własnych nazw wyznaczonym zakresom komórek, obiektom, wartościom czy formułom. 

Menedżer nazw znajduje się na karcie Formuły w grupie opcji Nazwy zdefiniowane. 

kliknij, aby powiększyć

Najszybszym i najprostszym sposobem na zdefiniowanie zakresu jest zaznaczenie obszaru, naciśnięcie przycisku Utwórz z zaznaczenia. 

Nadana w ten sposób nazwa jest widoczna na liście nazw Menedżera nazw.

kliknij, aby powiększyć

Na screenie widoczna jest także nazwa Tabela1 – wszystkie obiekty tabel od razu też są tu widoczne, automatycznie wstawiane przez Excela.  
Zadeklarowana nazwa Kurs_Euro jest dostępna w każdym arkuszu tego skoroszytu i może być wykorzystana w każdej formule np.: =A2*Kurs_Euro

Można też przypisać nazwę do dynamicznego zakresu poprzez funkcję np. 
=PRZESUNIĘCIE($E$2;;;ILE.NIEPUSTYCH($E$2:$E$100);1)

kliknij, aby powiększyć

W tym przypadku wysokość zakresu jest uzależniona od ilości niepustych komórek w zakresie. Idealnie nadaje się do tworzenia list w Poprawności danych. 

kliknij, aby powiększyć

Przy tworzeniu nazw warto zwrócić uwagę na to, że dana nazwa może obowiązywać zarówno w całym skoroszycie jak i pojedynczym arkuszu. Domyślnym ustawieniem jest Skoroszyt:

kliknij, aby powiększyć

Wszystkie nazwy muszą zaczynać się od znaku _ lub litery. Nie mogą też zawierać spacji ani niedozwolonych znaków. No i oczywiście – muszą być unikalne, nie mogą być takie same jak inne nazwy (np.oznaczenia komórek typu A1, b10 itp.) 

Zdefiniowane w Excelu nazwy są też dostępne w edytorze VBA:
Range(„Kurs_Euro”) 
lub
[Kurs_Euro]

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela