0
0 Produkty w koszyku

No products in the cart.

Maksymalna i minimalna wartość po warunku za pomocą tabeli przestawnej — widzowie #120

Dostałem zapytanie jak wyciągnąć pierwsze i ostatnie wiersze (wartości z wybranej kolumny) po unikalnych wartościach w innych kolumnach.

rys. 1 – Dane i przykładowe wiersze, które chcemy wyciągnąć

rys. 1 – Dane i przykładowe wiersze, które chcemy wyciągnąć

Zadanie okazuje się prostsze niż z początku myślałem, ponieważ wartość do wyciągnięcia (w tym przykładzie czas) w ostatnim wierszu jest maksymalna dla unikalnych wartości w kolumnach z warunkami i minimalna w pierwszym. Dlatego rozwiązanie naszego problemu sprawdza się do znalezienia max i min po warunkach, a to jest dużo prostsze. Ponieważ chcemy znaleźć max i min po wszystkich unikalnych wartościach w kolumnach warunkowych najprostszym rozwiązaniem okazuje się skorzystanie z Tabeli Przestawnej.

W tym przykładzie potrzebujemy unikalnych wartości po dwóch kolumnach Data i Pracownik, dlatego oba te pola przeciągamy do obszaru etykiet wierszy

rys. 2 – Początek budowania unikalnych wierszy w tabeli przestawnej

rys. 2 – Początek budowania unikalnych wierszy w tabeli przestawnej

Tabela przestawna jeszcze nie prezentuje się tak jakbyśmy sobie tego życzyli – musimy pozmieniać domyślne ustawienia Excela. W pierwszej kolejności potrzebujemy zamienić kompaktowy układ tabeli przestawnej na układ tabelaryczny i przy okazji zaznaczyć opcję powtarzania elementów w tabeli przestawnej.

[rys. 3 – Style układów tabel przestawnych]

[rys. 3 – Style układów tabel przestawnych]

W dalszej kolejności nie potrzebujemy sum częściowych

[rys. 4 – Wyłączanie sum częściowych]

[rys. 4 – Wyłączanie sum częściowych]

I sum końcowych.

 [rys. 5 – Wyłączanie sum końcowych]

[rys. 5 – Wyłączanie sum końcowych]

Teraz możemy przeciągnąć 2 razy pole Czas do obszaru podsumowań wartości. Excel podsumowując czas zlicza ile razy pojawiły się wiersza dla konkretnych warunków.

[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]

[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]

My chcemy mieć Max i Min czas dlatego musimy zmienić sposób podsumowania w tabeli przestawnej. Najprościej kliknąć prawym przyciskiem myszy na kolumnę z podsumowaniem, a następnie z podręcznego menu rozwinąć opcję Podsumuj wartości według i wybrać odpowiednio Maksimum i Minimum.

Niestety w tabeli przestawnej domyślnie ustawiło mi się formatowanie ogólne, które źle pokazuje czas (jako liczbę).

[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]

[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]

Dlatego musimy zmienić formatowanie w kolumnach z podsumowaniem tabeli przestawnej – klikamy prawym przyciskiem myszy i wybieramy z podręcznego menu pozycję Format liczy i w oknie, które się otworzy wybieramy odpowiedni sposób formatowania czasu.

[rys. 8 – Format liczby w tabeli przestawnej]

[rys. 8 – Format liczby w tabeli przestawnej]

Znaleźliśmy maksymalny i minimalny czas (liczbę) po warunkach za pomocą tabeli przestawnej. Dla lepszej estetyki możemy jeszcze wyłączyć przyciski +/- z karty Analiza.

 [rys. 9 – Przyciski +/- na karcie Analiza]


[rys. 9 – Przyciski +/- na karcie Analiza]

Dodatkowym problem w dzisiejszym zadaniu jest wyznaczenie różnicy pomiędzy tymi wartościami. Niestety w zwykłej tabeli przestawnej (nie z dodatku Power Pivot) nie jesteśmy w stanie dodać pola obliczającego tą różnicę prawidłowo, dlatego obliczenia zrobimy poza tabelą przestawną. Formuła to proste odejmowanie, tylko Excel najprawdopodobniej będzie chciał domyślnie wstawiać funkcję WEŹDANETABELI, której nie chcemy, więc najprościej wpisać odwołania do komórek ręcznie:

=L3-K3

[rys. 10 – różnica pomiędzy wartością Max, a Min]

[rys. 10 – różnica pomiędzy wartością Max, a Min]

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

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).


Sub Makro1()
ActiveWorkbook.Names.Add Name:="Test1", RefersToR1C1:="=FILES(Sheet1!R2C1)"
ActiveWorkbook.Names.Add Name:="Test2", RefersToR1C1:="=GET.CELL(42)"
End Sub

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:

ActiveCell = ExecuteExcel4Macro("GET.CELL(42)")

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 https://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