Kategoria: Excel

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 folderu
        umożliwia pobranie plików z konkretnego katalogu i podkatalogów:
        PQ – lista plikó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ć. 

Adresy komórek w kodzie VBA

Ostatnio kilka razy zdarzyło mi się korygować kod VBA w skoroszytach użytkowników, w których nagle coś przestawało działać.  Przyczyna błędu była prozaiczna, choć niekiedy bywa trudna do zlokalizowania. O co chodzi? O adresy komórek w kodzie VBA. 

Załóżmy, że mamy prostą tabelkę:

kliknij, aby powiększyć

W kolumnie D jest prosta formuła sumująca wartości z komórek
B i C tego samego wiersza. Kolumna E to też suma tych komórek, ale wprowadzona jako wartość kodem VBA:

Public Sub Suma()
With Worksheets(„Arkusz8”)
.Range(„E2”) = .Range(„B2”) + .Range(„C2”)
.Range(„E3”) = .Range(„B3”) + .Range(„C3”)
.Range(„E4”) = .Range(„B4”) + .Range(„C4”)
.Range(„E5”) = .Range(„B5”) + .Range(„C5”)
.Range(„E6”) = .Range(„B6”) + .Range(„C6”)
End With
End Sub

Problem zaczyna się wtedy, gdy użytkownik końcowy zmieni układ arkusza  np. wstawi kolumny lub wiersze. Wszystkie formuły odwołujące się do komórek (także w innych arkuszach) – bez problemu same się zaktualizują, nawet gdy są wpisane jako adresy bezwzględne. 

kliknij, aby powiększyć

W kodzie VBA jednak nic się nie zmieniło, odwołania do poszczególnych komórek zostały takie same. Po wykonaniu makra efekt wygląda tak:

Procedura nadał wpisuje obliczone kodem wartości i wstawia je w kolumnę E, likwidując znajdującą się tam po przesunięciu formułę. W dodatku są tam dziwne wartości – sumowana jest liczba początkowa LP oraz wartość X. 
Problem może się pojawić także przy zmianie nazwy arkusza – w kodzie będzie zapisana stara nazwa i przy uruchomienie procedury pojawi się błąd wykonania 9.
Niestety, w takim przypadku trzeba ręcznie przerobić odwołania w kodzie VBA, dostosowując go do nowego układu danych. 

Ten przykład jest prosty, w bardziej skomplikowanych projektach znalezienie i   poprawienie takiego błędu może być zdecydowanie bardziej skomplikowane. 
Jak zabezpieczyć się przed nieprzewidzianą ingerencją użytkownika? 
Najprostsze sposoby to:

    • włączenie ochrony arkusza
      uniemożliwiające wstawienie wierszy/kolumn;
    • zastosowanie w kodzie VBA odwołań do nazw kodowych arkuszy
      uniezależniające odwołania do konkretnych arkuszy;

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Funkcja USUŃ.ZBĘDNE.ODSTĘPY()

Funkcja USUŃ.ZBĘDNE.ODSTĘPY() to jedna z funkcji tekstowych Excela. Zwraca wartość tekstową z usuniętymi niepotrzebnymi spacjami (na początku, na końcu i podwójne spacje w środku).

Funkcja ma jeden argument:

    • Tekst – zmienna typu tekstowego

Odpowiednikiem funkcji w VBA jest funkcja arkuszowa Trim(). 
W ograniczonym zakresie – w VBA funkcjonuje także funkcja
VBA Trim().

Kurs Excel dla HR

 

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