VBA #23 – Jak wykorzystać funkcje makr 4.0 w VBA

W poradzie 305 wspominałem o funkcjach makr 4.0. Pomogły mi wyciągnąć listę plików z katalogu, ale był z nimi jeden problem dla Polaków. Ciężko było znaleźć ich nazwę po Polsku.

Mi przyszedł do głowy jeden pomysł na poprawne tłumaczenie ich nazw za pomocą króciutkiego kodu VBA. Zadaniem tego kodu jest po prostu dodanie nazwy, która będzie odwoływać się do przykładowej funkcji makr 4.0.

Ponieważ VBA jest angielsko języczny, więc funkcję makr 4.0 wpisujemy w nim po angielsku (listę funkcji makr 4.0 z dokładnym opisem po angielsku możesz pobrać na stronie:
https://www.myonlinetraininghub.com/excel-4-macro-functions).

Po uruchomieniu tego makra do nazw Excela (patrz Menadżer nazw na karcie Formuły) dodadzą się dwie nazwy korzystające już z polskich nazw funkcji makr 4.0, gdyż mój Excel ‘mówi’ po polsku 😉 Po prostu między VBA, a Excelem funkcje zostały przetłumaczone. Dzięki temu będziemy już mogli korzystać z potrzebnych nam funkcji makr 4.0 już bez pomocy VBA.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak znaleźć polską nazwę funkcji makr 4.0 za pomocą VBA – porada #306

W poradzie 305 wspominałem o funkcjach makr 4.0. Był to dla mnie nowy temat i zaciekawił na tyle, że chciałbym móc używać tych funkcji w VBA, ale oczywiście w prosty sposób się nie da. Podobnie jak funkcji makr 4.0 nie da się wpisać bezpośrednio do komórki arkusza, tak nie można ich użyć bezpośrednio w kodzie VBA. Potrzeba użyć formuły (ExecuteExcel4Macro) wywołującej funkcje makr 4.0, czyli np. takiego kodu:

Powyższy kod w aktywną komórkę (ActiveCell) wpisuje jej odległość od lewej krawędzi Excela (GET.CELL(42))

Funkcji makr 4.0 są dziesiątki i warto przejrzeć chociaż ich listę żeby wiedzieć, kiedy mogą się przydać. Taką listę z opisem ich funkcjonalności i przykładami użycia (niestety po angielsku) możesz pobrać na stronie:
https://www.myonlinetraininghub.com/excel-4-macro-functions

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Przefiltrowana lista plików z katalogu i podkatalogów – Power Query #16

W poprzednim wpisie wyciągnęliśmy listę plików z katalogu za pomocą funkcji makr 4.0. Tym razem chcemy to zrobić za pomocą Power Query. Funkcje w porównaniu do Power Query mają jedną zaletę automatycznie się odświeżają. Natomiast zapytania Power Query przeważnie musimy odświeżać ręcznie. Ale w pozostałych kwestiach uważam, że Power Query się lepiej sprawdza do rozwiązania tego problemu m.in. dlatego, że automatycznie sięga do podkatalogów, a z makrami trzeba już pisać bardzo skomplikowany kod VBA.

Starczy wstępu. Znamy już ścieżkę katalogu (C:\Users\PC\Documents\Pliki), więc możemy zacząć budować zapytanie Power Query. W Excelu 2016 przechodzimy do karty Dane -> polecenie Nowe zapytanie -> Z pliku -> Z folderu…

Power Query #16 - Lista plików z katalogu i podkatalogów - 01

Pobieranie danych z folderu za pomocą Power Query

Wklejamy ścieżkę w okno, które się pojawi i naciskamy przycisk OK.

Power Query #16 - Lista plików z katalogu i podkatalogów - 02

Okno ścieżki do folderu

W następnym oknie przechodzimy do Edycji.

Power Query #16 - Lista plików z katalogu i podkatalogów - 03

Podgląd wyników zapytania z folderu

Pierwsza zaleta na korzyść Power Query w porównaniu do funkcji makr 4.0 – od razu widzimy wyniki zapytania i mamy więcej informacji o pliku niż tylko jego nazwa.

Dodatkowo jeśli odnajdziemy kolumnę Folder Path, to zobaczymy, że Power Query wyciągnął również pliki z podkatalogów wskazanego katalogu.

Power Query #16 - Lista plików z katalogu i podkatalogów - 04

Informacje o plikach wyciągnięte przez Power Query

W większości chcemy zaglądać również do podkatalogów wskazanego folderu, ale jeśli ich nie potrzebujesz wystarczy przefiltrować dane po kolumnie Folder Path.

Jeśli chcemy zobaczyć więcej informacji o plikach wystarczy, że rozwiniemy kolumnę Attributes (klikając w dwie strzałki wskazujące w przeciwnych kierunkach w nagłówku tej kolumny).

Power Query #16 - Lista plików z katalogu i podkatalogów - 05

Lista atrybutów pliku możliwa do rozwinięcia

My więcej atrybutów nie potrzebujemy, chcemy ich mieć wręcz mniej, ale to zrobimy na koniec zapytania. Teraz chcemy przefiltrować dane tak, żebyśmy widzieli tylko pliki Excela. Możemy do tego wykorzystać kolumnę Extension, ale musimy pamiętać, że Power Query jest Case Sensitive, czyli, w przeciwieństwie do Excela, zwraca uwagę na wielkość liter. Czasami się zdarza, że niektóre programy zapisują rozszerzenia wielkimi literami, co może się dla nas okazać problemem. Dlatego przed filtrowaniem zamienimy wszystkie litery w kolumnie Extension na małe (karta Przekształć -> polecenie Format -> małe litery)

Power Query #16 - Lista plików z katalogu i podkatalogów - 06

Polecenie zamiany tekstu na małe litery

Teraz możemy już przefiltrować dane po kolumnie Extension, tylko nie chcemy po prostu odhaczać niepasujących rozszerzeń, tylko chcemy przejść do filtrów tekstu – zaczyna się od.

Power Query #16 - Lista plików z katalogu i podkatalogów - 07

Filtry tekstu – zaczyna się od, dla kolumny Extension

W oknie, które się pojawi wpisujemy tekst ‘.xls’, ponieważ interesują nas takie rozszerzenia jak ‘.xls’, ‘.xlsx’, ‘.xlsm’ i podobne związane z Excelem.

Power Query #16 - Lista plików z katalogu i podkatalogów - 08

Okno filtrowania wierszy

Odfiltrowaliśmy nieinteresujące nas pliki, więc teraz wystarczy usunąć wszystkie kolumny poza Name. To bardzo proste, bo wystarczy na nią kliknąć prawym przyciskiem myszy i w podręcznym menu wybrać opcję Usuń inne kolumny.

Power Query #16 - Lista plików z katalogu i podkatalogów - 09

Podręczne menu dla kolumny

Teraz pozostaje nam tylko załadować dane do Excela.

Power Query w porównaniu do funkcji makr 4.0 sięga do podkatalogów, pozwala łatwo odfiltrować niechciane katalogi i daje więcej możliwości jeśli chodzi o filtrowanie plików i ich rozszerzeń. Niestety nie odświeża się automatycznie i trudniej mu zmienić ścieżkę do folderu, gdzie funkcja PLIKI patrzy na wartość w komórce, którą łatwo umiemy zmienić (żeby nakłonić Power Query, żeby pobierał ścieżkę do folderu zobacz mój film/wpisu – Porada #305). Czyli jak to często powtarzam w Excelu, jak w życiu, jest wiele sposobów na uzyskanie tego samego, z jakiego sposobu skorzystasz, zależy głównie od Twoich potrzeb i możliwości 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Lista plików z katalogu za pomocą funkcji makr 4.0 – porada #305

Przed nami niezwykle trudne zadanie – potrzebujemy zrobić w Excelu listę wszystkich plików ze wskazanego katalogu. Spokojnie nie będzie, aż tak źle. Nawet nie użyjemy VBA, a przynajmniej nie zajrzymy do edytora VBA i nie będziemy tworzyć, żadnych nowych makr.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 01

Rys. 1 – Lista plików z katalogu

Technika, na wypisanie wszystkich plików ze wskazanego katalogu, jaką chcę Ci przedstawić opiera się na funkcjach makr 4.0.
Informacja o nich trafiła do mnie w przeciągu miesiąca z dwóch źródeł.
1) Ze strony http://www.myonlinetraininghub.com/, z której dostałem olbrzymi plik o funkcjach makr 4.0
2) Oraz ze strony https://trumpexcel.com/ gdzie był opisany przedstawiony problem wypisania wszystkich plików ze wskazanego folderu:

Na podstawie tego problemu chcę Ci przybliżyć sposób działania funkcji makr 4.0 i opowiedzieć o trudnościach, z nimi związanymi, jakie czekają polskiego użytkownika Excela (i innych nie angielskich narodowości).

Pierwsza trudność jest taka, że nie możesz ich wpisać bezpośrednio w komórki Excela. Może znasz funkcję DATA.RÓŻNICA, której Excel nie podpowiada, że istnieje, ale jak wpiszesz ją w komórkę Excela to ona działa. Funkcje makr 4.0 nie działają w komórkach arkusza Excela.

Żeby z nich skorzystać, chyba najprostszym sposobem, jest stworzenie na ich podstawie nazwy (Menadżer nazw karta dane).

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 02

Rys. 2 – Menadżer nazw

Przy tworzeniu tej nazwy pojawia się kolejny problem, bo ciężko znaleźć nazwę tych funkcji po polsku (ogólnie w innym języku niż angielski). Źródła, które podałem na początku podają potrzebną nam funkcję w języku angielskim – nazywa się FILES. Ale jeśli spróbujemy z niej skorzystać polski Excel jej nie rozpozna. Potrzebujemy jej nazwę po polsku. W wielu przypadkach zadziała bezpośrednie tłumaczenie – tak mi się udało znaleźć polski odpowiednik dla tej funkcji – PLIKI. O innych sposobach pomówimy na koniec wpisu. Teraz zajmiemy się rozwiązaniem postawionego problemu.

Wiemy już jak funkcja nazywa się po polsku (PLIKI), ale nie znamy jej składni. Excel nam jej nie podpowie. Na szczęście składnia tej funkcji jest prosta – wystarczy, że podasz pełną ścieżkę do katalogu, z którego chcesz uzyskać listę plików (pamiętaj na jej końcu dodać gwiazdkę, która oznacza, że bierzemy wszystko):

C:\Users\PC\Documents\Pliki\*

Ścieżkę do katalogu najlepiej, żebyś wstawił do komórki Excela, a dopiero w funkcji makr 4.0 PLIKI się do niej odwoływał. Ułatwi Ci to jej modyfikację.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 03

rys. 3 – Wstawiona ścieżka do komórki A2

Czasami może być ciężko odnaleźć dokładną ścieżkę katalogu, bo nie widać jej na pierwszy rzut oka, ale wystarczy, że w ekspoatora Windowsa i klikniesz na jego górę, gdzie widać ścieżkę w formie bardziej graficznej.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 04

rys. 4 – eksplorator windows i kopiowanie ścieżki

Wyświetli Ci się wtedy pełna ścieżka (nawet jeśli jesteś w swoich dokumentach) i będziesz mógł ją łatwo skopiować.
Mamy ścieżkę, mamy nazwę funkcji, więc możemy ją w końcu stworzyć w Menadżerze nazw (Ctrl + F3). Naciskamy przycisk nowy i przechodzimy do okna tworzenia nazwy:

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 05

rys. 5 – nowa nazwa

Jak stworzyliśmy nazwę możemy się do niej odwoływać w komórkach Excela. Ponieważ funkcja PLIKI zwraca listę, więc nie możemy jej bezpośrednio wstawić do komórki Excela, bo zobaczymy tylko pierwszą wartość (pierwszy plik) z listy. Na szczęście wystarczy skorzystać z funkcji INDEKS, której będziemy mówić, który element z listy ma wyświetlać. Najczęściej w podobnych sytuacjach korzystam z funkcji ILE.WIERSZY i dynamicznego zakresu danych, który się rozrasta jak przeciągamy formułę:

=INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4))

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 06

rys. 6 – Lista plików za pomocą funkcji

Jeśli przeciągniemy formułę wystarczająco daleko w dół zaczną się pojawiąć błędy adresu – #ADR!, ponieważ nie ma już elementów na liście. Najprościej sobie z tym poradzić wstawiając naszą formułę do funkcji JEŻELI.BŁĄD, które karzemy zwracać pusty ciąg znaków (dwa podwójne cudzysłowy), jeśli wyjdziemy poza listę.

=JEŻELI.BŁĄD(INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4));””)

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 07

rys. 7 – jeżeli.błąd

Możemy nawet policzyć ilość pliku w katalogu (w stworzonej przez nas nazwie) np. za pomocą funkcji ILE.NIEPUSTYCH:

=ILE.NIEPUSTYCH(WszystkiePliki)

Udało nam się rozwiązać postawione na początku zadanie i dochodzimy do zapisywania pliku. Pojawia się kolejny problem – funkcje makr 4.0, to jednak makra i skoroszyt je zawierający musi być zapisany w formacie, który obsługuje makra.

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 08

rys. 8 – błąd przy zapisywaniu pliku z funkcjami makr 4.0

Na koniec wpisu warto wspomnieć, że przy funkcji PLIKI działają proste filtry, a dokładniej symbole wieloznaczne. Musieliśmy napisać na końcu ścieżki gwiazdkę (rys. 3), żeby pobrać wszystkie pliki. W podobny sposób możemy ograniczyć wybór plików z katalogu tylko do plików Excela ustawiając końcówkę:

C:\Users\PC\Documents\Pliki\*.xls*

Końcówka (*.xls*) oznacza, że nazwa pliku może zaczynać się od dowolnego ciągu znaków, po którym pojawia się ciąg tekstu ‘.xls’, a za nim może się jest coś pojawić, ale nie musi (symbol wieloznaczny gwiazdka oznacza dowolny ciąg tekstowy – nawet pusty). Efekty tak nałożonego filtru widać od razu w wynikach:

Porada 305 - Lista plików z katalogu za pomocą funkcji makr 4.0 - 09

rys. 9 – przefiltrowane pliki

Drugim, oprócz gwiazdki, symbolem wieloznacznym w Excelu jest znak zapytania ‘?’ – zastępuje on dowolny pojedynczy znak.
Mieliśmy problem z odnalezieniem nazwy funkcji makr 4.0 po polsku, ale mamy źródła, które podają te nazwy po angielsku. Wystarczy, że zmienimy język w Excelu na angielski (Menu Plik -> Opcje -> zakładka Język), zapiszemy plik z angielską nazwą pliku, a następnie zmienimy język Excela na polski i ponownie otworzymy plik – teraz już z polską nazwą funkcji.
Dodanie nowego języka do Excela różni się w zależności od wersji Excela, którą masz, dlatego podaję link do strony Microsoftu dokładnie opisujący proces w zależności od Twojej wersji Excela.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Ile grup jest powyżej progu – porada #304

Dzisiaj chce porozmawiać o problemie zliczania grup powyżej progu. Żeby lepiej zobrazować problem pokaże dane sformatowane warunkowo (komórki wypełnione na żółto) i ich prezentację na wykresie z linią progu.

Porada 304 - Ile grup powyżej progu - 01

Najlepiej można zobaczyć grupy (góry), które wyrastają ponad próg na wykresie. Łatwo jest je też policzyć – jest ich 4. Ale nie chcemy tego liczyć ręcznie za każdym razem, gdy dane się zmienią, albo gdy mamy dużo więcej danych niż w przykładzie. Co chcemy zrobić oczywiście wykorzystać formułę Excela, ale najpierw wyjaśnijmy jak sobie możemy wykryć grupę (górę).

Są 2 możliwości, albo patrzymy na zbocze wznoszące, albo opadające, czyli albo sprawdzamy, że wartość jest mniejsza od progu, a kolejna większa lub równa progowi (znak równości może być przy pierwszym lub drugim punkcie, który sprawdzamy w zależności, czy próg uznajemy już za naszą ‘górkę’ czy jeszcze nie), albo pierwsza wartość większa lub równa progowi, a kolejna mniejsza od progu. W tym przykładzie będziemy szukać zboczy wznoszących.

Wiemy co chcemy znaleźć, ale jak to znaleźć w Excelu za pomocą formuły? Mamy operacje porównania jako warunki i chcemy je zliczać, więc świetnie do tego nadaje się funkcja LICZ.WARUNKI. Wystarczy, że sprawdzimy, czy nasze dane, od pierwszej do przedostatniej wartości, są mniejsze od progu, a następnie sprawdzimy, czy dane od drugiej do ostatniej wartości są większe bądź równe progowi:

=LICZ.WARUNKI(A2:A14;”<"&E1;A3:A15;">=”&E1)

Porada 304 - Ile grup powyżej progu - 02

Czyli sprawdzamy obszary, które w stosunku do siebie są przesunięte o 1 komórkę.

Niestety wynik jaki otrzymujemy jest błędny – brakuje nam jednej ‘górki’. Taka sytuacja występuje wtedy kiedy nasze dane zaczynają się już od wartości większej (lub równej) progowi. Na szczęście bardzo łatwo skorygować naszą formułę. Wystarczy, że dodamy warunek sprawdzający, czy pierwsza dane jest większa równa od wyznaczonego progu. Musimy pamiętać o kolejności działań i dodatkowe porównanie zapisać w nawiasach (dodatkowe obliczenie poza funkcją LICZ.WARUNKI):

=LICZ.WARUNKI($A$2:$A$14;”<"&$E$1;$A$3:$A$15;">=”&E1)+(A2>=$E$1)

Porada 304 - Ile grup powyżej progu - 03

Operacja porównania (A2>=$E$1) daje w wyniku wartość PRAWDA i FAŁSZ, które w wyniku dodawania są konwertowane odpowiednio na 1 i 0, czyli jeśli pierwsza wartość w danych przekracza wartość progu, to mamy jedną górkę ekstra i dodajemy ją do wyniku funkcji LICZ.WARUNKI, ale jeśli nie przekracza progu to nic nie dodajemy – nie ma górki na początku danych.

Odpowiednio wizualizując sobie problem udało nam się znaleźć proste rozwiązanie w Excelu 🙂

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP