W tym poście pokażemy, jak stworzyć listę hiperłączy do plików znajdujących się w konkretnym folderze przy użyciu makr.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1. Ważne jest, że jeżeli chcemy korzystać z tej funkcji, musimy stworzyć plik z rozszerzeniem .xlsm, czyli taki, który obsługuje makra.
Rys. nr 1 – przykładowe dane
Przede wszystkim musimy znać polską wersję funkcji, której chcemy użyć (funkcja PLIKI). Jeśli tej funkcji podamy ścieżkę dostępu i zakończymy to znakiem gwiazdki (*) lub odpowiednim rozszerzeniem, to będzie ona pobierać pliki z tego katalogu. Odpowiednie połączenie formuł zapewni nam zbudowanie hiperłączy.
Mamy kilka obostrzeń, jeśli chodzi o korzystanie z tych funkcji. Funkcja nazywa się PLIKI, ale jeśli w komórce zaczniemy pisać słowo "pliki" to takiej funkcji nie znajdziemy, co widać na rys. nr 2.
Rys. nr 2 – problem ze znalezieniem funkcji PLIKI
Tą funkcję możemy zbudować tylko w Menedżerze nazw. W tym celu wybieramy polecenie Menedżer nazw z karty Formuły (rys. nr 3). Możemy również użyć skrótu klawiszowego Ctrl+F3.
Rys. nr 3 – polecenie Menedżer nazw
Otworzy nam się okno Menedżera nazw, gdzie możemy sobie zbudować funkcję PLIKI, która odwołuje się do komórki A2, czyli do ścieżki dostępu. Na tej podstawie funkcja zwraca listę plików z tego katalogu (rys. nr 4).
Rys. nr 4 – okno Menedżera nazw
Jak widać na rysunku powyżej nazwaliśmy tą funkcję WszystkiePliki. Korzystając z tego, że używamy Excela tablicowego, po wpisaniu funkcji WszystkiePliki i jej zatwierdzeniu, funkcja zwróci nam listę wszystkich plików (rys. nr 5).
Rys. nr 5 – zwrócona lista wszystkich plików
Jak widać na rysunku powyżej domyślnie lista ta jest zwracana w poziomie, w jednym wierszu, a nie w kolumnie. W Excelu tablicowym wystarczyłoby dołożyć funkcję TRANSPONUJ, żeby wyniki otrzymać w pionie, czyli w jednej kolumnie.
Zakładamy jednak, że jesteśmy we wcześniejszej wersji Excela i chcemy wyciągnąć takie informacje jak widać na rys. nr 1. W komórce A5 wpisujemy nazwę funkcji WszystkiePliki. Wiemy, że ona rozsuwa wyniki w poziomie, a my chcemy otrzymać je w pionie. W Klasycznym Excelu nie mamy możliwości transponowania wyników ani rozlewania formuły. Dlatego musimy użyć funkcji INDEKS. Pierwszym argumentem funkcji jest tablica, czyli wyniki z funkcji WszystkiePliki. Kolejny argument to nr_wiersza, czyli wpisujemy wartość 1. Zapis formuły powinien wyglądać następująco:
=INDEKS(WszystkiePliki;1)
Po zatwierdzeniu formuły otrzymamy nazwę jednego pliku przedstawioną na rys. nr 6.
Rys. nr 6 – zwrócona nazwa jednego pliku
Podając w funkcji kolejne numery wiersza możemy otrzymać zwrócone kolejne elementy/pliki. Możemy takie podawanie poszczególnych numerów wierszy zastąpić funkcją ILE.WIERSZY. Pierwszym argumentem funkcji jest tablica, czyli zakres od komórki $A$5 zablokowanej bezwzględnie do komórki A5 niezablokowanej. Jak będziemy formułę kopiować w dół, to komórka A5 nie zablokowana bezwzględnie będzie się przesuwać i tym samym zwracać kolejne numery wierszy. Zapis formuły powinien wyglądać następująco:
=INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5))
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane przedstawione na rys. nr 7.
Rys. nr 7 – lista elementów z pliku
Jeśli w ilości skopiowanych komórek w dół przekroczymy ilość elementów w folderze, to Excel zwróci nam błąd adresu #ADR!. Mamy wyciągnięte wszystkie pliki, które znajdują się w tym katalogu. Aby zbudować hiperłącze, potrzebujemy ścieżki dostępu do tych plików (C:\tmp\Obrazki dla Hani\*). Wtedy możemy połączyć nasze dane wklejając skopiowany adres po znaku =. Jako, że jest to tekst, musimy go wstawić w podwójnych cudzysłowach. Następnie połączyć wstawiony adres z resztą formuły za pomocą znaku ampersand (&). Zapis formuły powinien wyglądać następująco:
="C:\tmp\Obrazki dla Hani\"&INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5))
Po zatwierdzeniu powyższej formuły i skopiowaniu jej na komórki poniżej otrzymamy kompletne ścieżki dostępu do każdego z plików (rys. nr 8).
Rys. nr 8 – pełne ścieżki dostępu do każdego z plików w katalogu
Po otrzymaniu kompletnych ścieżek dostępu pozostaje nam dołożenie funkcji HIPERŁĄCZE, której argumentem jest łącze_lokalizacja, czyli wynik naszej poprzedniej formuły. Jeśli chcemy, możemy dodać opcjonalny argument, czyli przyjazna_nazwa. Zapis formuły powinien wyglądać następująco:
=HIPERŁĄCZE("C:\tmp\Obrazki dla Hani\"&INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5)))
Po zatwierdzeniu formuły i skopiowaniu jej w dół otrzymamy hiperłącza do poszczególnych elementów katalogu przedstawione na rys. nr 9.
Rys. nr 9 – hiperłącza do poszczególnych elementów katalogu
Hiperłącza zostały również dodane dla ostatnich dwóch wierszy zawierających błędy. Aby tego uniknąć, możemy dołożyć funkcję JEŻELI.BŁĄD. Pierwszym argumentem funkcji jest wartość, czyli wynik funkcji HIPERŁĄCZE. Drugi argument funkcji to wartość_jeśli_błąd, czyli co chcemy, aby zwróciła funkcja jeśli napotka błąd – u nas pusty ciąg tekstowy (""). Zapis formuły powinien wyglądać następująco:
=JEŻELI.BŁĄD(HIPERŁĄCZE("C:\tmp\Obrazki dla Hani\"&INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5)));"")
Po zatwierdzeniu formuły otrzymamy prawidłowe hiperłącza przedstawione na rys. nr 10.
Rys. nr 10 – prawidłowe hiperłącza
Jeśli klikniemy w dowolne hiperłącze, Excel otworzy nam odpowiedni katalog i wyświetli wybrany obrazek (rys. nr 11).
Rys. nr 11 – wyświetlony obrazek po kliknięciu hiperłącza
Funkcja PLIKI umożliwia filtrowanie danych. Jeśli do adresu katalogu w komórce A2 dopiszemy rozszerzenie .png, to Excel przefiltruje dane i wyświetli tylko hiperłącza zawierające pliki z tym rozszerzeniem (rys. nr 12).
Rys. nr 12 – przefiltrowane dane – pliki z rozszerzeniem .png
W ten sposób możemy łatwo filtrować po jednym rodzaju rozszerzenia. Niestety, żeby filtrować po większej ilości rozszerzeń, musimy zaznaczyć wybrany zakres i kliknąć prawym przyciskiem myszki na nagłówki kolumn i z podręcznego menu wybrać opcję Odkryj (rys. nr 13).
Rys. nr 13 – opcja Odkryj w podręcznym menu
Wyświetlą nam się "dodatkowe" dane przedstawione na rys. nr 14.
Rys. nr 14 – odkryte dane
Po tymi danymi są dużo bardziej skomplikowane formuły, które są potrzebne do zbudowania listy plików spełniających wybrane warunki (rys. nr 15). Tutaj niezbędne okazują się funkcje z Excela tablicowego, bo Excel klasyczny potrzebowałby dużo bardziej skomplikowanych formuł.
Rys. nr 15 – skomplikowane formuły z użyciem Excela tablicowego
Jeśli zależy nam na liście wszystkich plików z katalogu lub na jednym konkretnym rodzaju plików, to funkcja PLIKI jest tutaj najlepszym rozwiązaniem.
W tym poście omówimy stworzenie rankingu sprzedawców tablicowo za pomocą jednej formuły z użyciem funkcji ZEZWALAJ. Jest to kontynuacja tematu z poprzednich dwóch postów, w których najpierw rozwiązywaliśmy to zagadnienie za pomocą kolumn pomocniczych, a następnie za pomocą jednej formuły (stworzonej z pojedynczych formuł, jakich używaliśmy w kolumnach pomocniczych). Przypomnimy najpierw przykładowe dane do tego zadania (rys. nr 1).
Skrócimy
powyższą formułę stosując funkcję ZEZWALAJ. Z mojego punktu widzenia, wygodniej
jest zastosować nazewnictwo funkcji ZEZWALAJ w już istniejącej formule
(zastępując poszczególne elementy). Zastępujemy poszczególne elementy, ponieważ
zazwyczaj kiedy odwołujemy się do wcześniejszej formuły stosujemy odwołanie do jej
wyników a nie wpisujemy (kopiujemy) jej formuły. Zapis formuły z poprzedniej
porady wygląda następująco:
Aby wstawić Enter
w formule (przejść do niższego wiersza) musimy w trybie edycji komórki użyć skrótu
klawiszowego Alt+Enter. Zaczynamy od znaku równa się, a następnie wpisujemy
nazwę funkcji ZEZWALAJ.
Musimy mieć
tutaj "bystre oczko", żeby zauważyć, jakie elementy powtarzają się w naszej
skomplikowanej formule. Możemy łatwo zauważyć, że często powtarza się zakres
tSprzedażk[Sprzedawca], dlatego ten zakres zastąpimy jako pierwszy mniej
skomplikowaną nazwą. Pierwszym argumentem funkcji ZEZWALAJ jest nazwa1,
czyli wybrany przez nas element (nazwiemy go np.SprzedW, czyli wszyscy
sprzedawcy. Drugi argument funkcji to wartość_nazwy1, czyli wartość
naszego nazwanego argumentu – tablica tSprzedażk[Sprzedawca].
Aby za
każdym razem nie szukać tej dłuższej nazwy tSprzedażk[Sprzedawca], żeby ją
podmienić, musimy ją sobie skopiować. Następnie zaznaczamy dwie komórki (jedną
z naszą formułą, a drugą spoza tego zakresu) i używając skrótu klawiszowego Ctrl+H,
otwieramy okno Znajdowania i zamieniania. W tym oknie przechodzimy na
zakładkę Zamień i w polu Znajdź wklejamy skopiowaną nazwę zakresy sprzed
zmiany, a następnie w polu Zamień na wpisujemy nową nazwę nadaną w
funkcji ZEZWALAJ dla tego zakresu – SprzedW. Tak ustawione parametry
zatwierdzamy przyciskiem Zamień wszystkie (rys. nr 3).
Rys. nr 3 – okno Znajdowania i zamieniania
Excel wyświetli nam komunikat o ilości takich zamian (rys. nr 4).
Rys. nr 4 – komunikat Excela o ilości zamienionych nazw
Otrzymaliśmy
błąd w formule #NAZWA?, który wynika z tego, że został zamieniony również drugi
argument funkcji ZEZWALAJ. Musimy go poprawić i wpisać pierwotną nazwę.
Trzeci
argument funkcji to obliczenie_lub_nazwa2. U nas będzie to kolejny
arguemnt nazwa2. Szukamy teraz kolejnego elementu, który się powtarza w naszej
długiej formule – będzie to kolejny zakres tSprzedażk[Sprzedaż]. W formule
ponownie naciskamy skrót Alt+Enter, aby przejść do kolejnego wiersza i tam
podajemy kolejny argument funkcji – (nazwa2) Sprzedaż, trzeci argument, czyli
wartość_nazwy2 to będzie tSprzedażk[Sprzedaż]. Ponownie zaznaczamy jedną
komórkę z zakresu z naszą formułą oraz jedną z sąsiadujących i za pomocą skrótu
klawiszowego Ctrl+H, otwieramy okno Znajdowania i zamieniania. W tym oknie
analogicznie jak dla pierwszej nazwy w polu Znajdź wpisujemy wartość_nazwy2 (tSprzedażk[Sprzedaż]),
a w polu Zamień na wpisujemy nazwa2 (Sprzedaż). Zatwierdzamy zamienianie
przyciskiem Zamień wszystkie. Ponownie wyświetli nam się komunikat Excela o
ilości zmian i otrzymamy błąd w formule, wynikający z tego ze został zamieniony
również argument wartość_nazwy2. Wystarczy to zmienić i formuła zadziała
prawidłowo. Na tym etapie formuła funkcji ZEZWALAJ powinna wyglądać następująco:
Fragmenty
danych, które nazywamy możemy również ręcznie zamieniać w formule bez
korzystania z polecenia Znajdywania i zamieniania. Jednak wykorzystanie go daje
nam gwarancję, że żadne ich wystąpienie nam nie umknie.
Kolejnym
powtarzającym się elementem jest funkcja UNIKATOWE(SprzedW) – argument wartość_nazwa3,
którą zastąpimy słowem Sprzedawcy (nazwa3). Znowu analogicznie jak dla
poprzednich dwóch nazw zastępujemy odpowiednie nazwy polecenie Znajdywania i
zamieniania.
Następną
powtarzającą się nazwą jest funkcja ILE.WIERSZY(Sprzedawcy), czyli nasz kolejny
argument funkcji ZEZWALAJ – wartość_nazwy4, który zamienimy na Wiersze (nazwa4).
Ponownie zamieniamy wartości w całej formule.
Kolejna
nazwa, którą zamienimy to SUMA.JEŻELI(SprzedW; Sprzedawcy; Sprzedaż). Powtarza
się ona dwa razy, więc podmienimy ją ręcznie. Przypiszemy jej nazwę Wyniki.
Zapis całej formuły będzie wyglądał następująco:
Po
zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 5.
Rys. nr 5 – wyniki działania funkcji ZEZWALAJ
Podsumowując,
najłatwiej było zastosować funkcje ZEZWALAJ na istniejącej długiej formule.
Dzięki jej wykorzystaniu mogliśmy podmienić elementy powtarzające się w
formule.
Nasza
formuła nadal działa dynamicznie, czyli kiedy wprowadzimy zmiany do danych
wejściowych, wyniki automatycznie się zaktualizują.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście stworzymy ranking sprzedawców tablicowo za pomocą jednej formuły.
W poprzednim poście omawialiśmy takie samo rozwiązanie tablicowe, ale z wykorzystaniem kolumn pomocniczych (porada 399 https://exceliadam.pl/excel/excel-ranking-sprzedawcow-tablicowo-porada-399 ). Dane do tego zadania oraz kolumny pomocnicze z poprzedniego postu zostały przedstawione na rys. nr 1.
Rys. nr 1 – dane do rozwiązania zadania
W poprzedniej poradzie rozpisaliśmy rozwiązanie na poszczególne formuły pomocnicze w osobnych kolumnach. Dzięki temu rozwiązanie było proste. W tym poście połączymy te formuły w jedną. Zasada ogólnie jest prosta, czyli będziemy kopiować poszczególne formuły. Formułę z poprzedniego kroku będziemy kopiować i wstawiać do kolejnej, podstawiając ją w miejscu, gdzie odwołujemy się do poprzedniej formuły. To wszystko w takim zamyśle było by proste, ale niestety funkcja POZYCJA.NAJW nie radzi sobie z obliczeniami w swoim argumencie odwołanie. Jeśli do tego argumentu dodamy wartość 0 (zero), czyli teoretycznie nie zmienimy wyniku, to Excel wyświetli nam komunikat o nieprawidłowej formule przedstawiony na rys. nr 2.
Rys. nr 2 – komunikat Excela
Podobnie
zachowuje się funkcja SUMA.JEŻELI. Obie te funkcje nie pozwalają na
obliczenia w swoich argumentach. Podsumowując, nie możemy wstawiać formuł z
poprzedniego kroku do kolejnego. Z tego względu musimy użyć innego rozwiązania,
które pozwoli nam wyciągnąć miejsca w rankingu poszczególnych sprzedawców.
Niestety rozwiązanie to będzie skomplikowane. Tak naprawdę, żeby sprawdzić,
które miejsce ma dany sprzedawca, to musimy sprawdzić, ilu sprzedawców ma
lepsze bądź równe miejsce. Kopiujemy sobie zapis funkcji SUMA.JEŻELI.
Zapis wygląda następująco:
I tworzymy
zapis, w którym sprawdzimy czy nasze wyniki funkcji SUMA.JEŻELI są większe bądź
równe wynikom funkcji SUMA.JEŻELI. Zapis formuły powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 3.
Rys. nr 3 – wyniki porównania dwóch funkcji SUMA.JEŻELI
Otrzymaliśmy
wartości logiczne PRAWDA ponieważ funkcja porównała ze sobą poszczególne
wiersze. Nam chodzi o to, aby funkcja porównała każdy wiersz ze wszystkimi
innymi. Taki efekt możemy uzyskać za pomocą funkcji TRANSPONUJ. Dzięki
tej funkcji transponujemy jedną z naszych tablic. Sprawi to, że otrzymamy
wyniki przedstawione na rys. nr 4. Zapis formuły powinien wyglądać następująco:
Rys. nr 4 – wyniki porównania po transponowaniu jednej z tablic
Przy takiej
orientacji w pierwszym wierszu mamy informację, ile wartości z porównania jest
mniejszych od sprawdzanej aktualnie wartości. Dla pierwszego wiersza
(sprzedawca Maria) mamy 5 wartości logicznych PRAWDA i jedną wartość FAŁSZ,
oznacza to, że 5 osób z listy sprzedawców było od niej lepszych. Można
zauważyć, że wynik się zgadza, ponieważ w tabeli powyżej w kolumnie I mamy
informację, że Maria jest na 5‑tym miejscu w rankingu.
Teraz
wartości logiczne PRAWDA i FAŁSZ chcemy zamienić na wartości 0 i 1. Możemy to
zrobić za pomocą podwójnej negacji, czyli wstawiamy przed formułą dwa znaki
minus. Zapis powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 5.
Rys. nr 5 – zamiana wartości logicznych na 0 i 1
Teraz musimy zsumować wartości w poszczególnych wierszach. Gdybyśmy użyli funkcji SUMA.ILOCZYNÓW to otrzymalibyśmy zliczoną całą tablicę. Możemy tutaj wykorzystać funkcję MACIERZ.ILOCZYN, żeby każdy wiersz przemnożył się przez kolumnę danych, którą pokażemy. Funkcja ta będzie mnożyć poszczególne elementy z wierszy pierwszej tabeli przez kolumny z drugiej tabeli, a następnie wyniki tego mnożenia doda do siebie, by wstawić je w pojedynczą komórkę. Potrzebujemy kolumny o takiej samej ilości komórek co wiersz. Cała formuła z poprzedniego kroku to będzie nasz pierwszy argument funkcji, czyli tablica1. Teraz musimy wstawić drugą tablicę (argument tablica2). Aby stworzyć drugą tablicę, musimy użyć funkcji SEKWENCJA, w której musimy mieć taką samą liczbę kolumn i wierszy. Pierwszy argument funkcji to wiersze, który możemy wyznaczyć za pomocą funkcji ILE.WIERSZYdla zakresu G2# (listy unikatowych sprzedawców). Drugi argument funkcji to kolumny, czyli wpisujemy wartość 1, bo potrzebujemy jednaj kolumny. Trzeci argument to początek, czyli od jakiej wartości chcemy zacząć – u nas wartość 1. Czwarty argument to krok, czyli wartość 0. Kiedy podejrzymy argument tablica2 funkcji MACIERZ.ILOCZYN to możemy zauważyć, że mamy same wartości 1 co widać na rys. nr 6.
Rys. nr 6 – podgląd wartości argumentu tablica2
Z podglądu
edycji formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Cały
zapis formuły powinien wyglądać następująco:
Powyższą
formułę zatwierdzamy i otrzymamy tablicę z rankingiem sprzedawców przedstawioną
na rys. nr 7.
Rys. nr 7 – tablica z rankingiem sprzedawców
Stworzona
przez nas formuła jest dużo bardziej skomplikowana niż funkcja POZYCJA.NAJW
użyta w rozwiązaniu z kolumnami pomocniczym. Ta skomplikowana formuła poradziła
sobie doskonale z wyznaczeniem miejsc w rankingu poszczególnych sprzedawców. W
tej formule użyliśmy odwołania do zakres G2#, więc odwołanie to musimy jeszcze
zastąpić formułą z komórki G2. Zapis formuły będzie wtedy wyglądał następująco:
Mamy
odpowiednie wyniki i teraz możemy przejść na kolejny poziom obliczeń (ten,
który uzyskaliśmy w kolumnie K w poprzednim poście). Użyjemy funkcji SEKWENCJA
z komórki K2 i wstawimy ją w funkcję X.WYSZUKAJ. Funkcja X.WYSZUKAJ
miała formułę przedstawioną poniżej:
=X.WYSZUKAJ(K2#;I2#;G2)
Teraz w
miejsce poszczególnych zakresów rozlanych formuł musimy wstawić odpowiednie
formuły. Zakres K2# zastąpimy formułą z komórki K2, czyli
SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca])))
Następnie
zamiast zakresu I2# nie możemy wstawić formuły z komórki I2, ponieważ funkcja
ta nie radzi sobie z obliczeniami w swoich argumentach. Musimy tutaj wstawić
skomplikowaną formułę, dzięki której wyznaczyliśmy ranking sprzedawców. Formuła
ta wyglądała następująco:
Powyższą
formułę zatwierdzamy i otrzymamy wyniki przedstawione na rys. nr 8.
Rys. nr 8 – wyniki skomplikowanej formuły funkcji X.WYSZUKAJ
Tak jak
zaznaczyłem na wstępie, chcemy mieć jedną formułę do przydzielania miejsc. A na
tą chwilę mamy dwie – w komórkach K2 i L2. Mamy w wynikach dwie kolumny, które
możemy połączyć za pomocą funkcji WYBIERZ. Najpierw kopiujemy całą
formułę funkcji X.WYSZUKAJ. Następnie w komórce K2 wpisujemy funkcję WYBIERZ,
której pierwszym argumentem jest nr_arg, czyli argumenty jakie chcemy
wybrać (w naszym przykładzie pierwszy i drugi argument {1\2}). Używamy tutaj backslasha,
żeby poinformować Excela, że chodzi nam o kolumny danych, a nie wiersze. Drugi
argument funkcji to wartość1, czyli zapis formuły, dzięki której
uzyskaliśmy pierwszą kolumnę danych. Formuła wygląda następująco: SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca])))
Kolejny
argument to wartość2, czyli druga kolumna danych uzyskana za pomocą
skomplikowanej formuły funkcji X.WYSZUKAJ.
Zapis całej
formuły funkcji WYBIERZ powinien wyglądać następująco:
Po zatwierdzeniu
powyższej formuły otrzymamy dwie kolumny danych jednocześnie w kolumnach K i L
(takie same jak na rys. nr 8).
Podsumowując,
za pomocą funkcji WYBIERZpołączyliśmy dwie kolumny danych. Jak widać
otrzymaliśmy bardzo skomplikowaną formułę. Dlatego często lepiej jest zrobić
sobie kolumny pomocnicze, by ewentualnie później tylko przekleić formuły w
odpowiednie miejsca do funkcji wyższego poziomu. Gdyby nie funkcja POZYCJA.NAJW,
która nie radzi sobie z obliczeniami w jej argumentach, sprawa była by bardzo
prosta. Jednak tutaj musieliśmy ją zastąpić innym rozwiązaniem.
Mamy wyciągnięte
dane, więc teraz tak jak w poprzednim wideo zmodyfikujemy dane wejściowe przez
doklejenie dodatkowych wierszy. Sprawdzimy w ten sposób, czy nasza formuła
działa dynamicznie. Po doklejeniu dodatkowych danych nasze wyniki się
rozszerzyły o nowe pozycje, co widać na rys. nr 9.
Rys. nr 9 – zmienione dane po zmianach w danych wejściowych
Do wyznaczenia rankingu unikatowych sprzedawców użyliśmy jednej formuły, która zwróciła nam dwie kolumny danych (rys. nr 10).
Rys. nr 10 – skomplikowana formuła zwracająca dwie kolumny danych
Podsumowując,
nie zawsze możemy sobie pozwolić na tworzenie kolumn pomocniczych w analizie
danych. Dlatego możemy sobie je stworzyć tylko po to, by użyć ich do stworzenia
skomplikowanej formuły a potem je skasować.
W kolejnej
poradzie (nr 401) pomówimy o tym jak można ponazywać zakresy danych, żeby
skomplikowana formuła stała się krótsza i bardziej zrozumiała na pierwszy rzut
oka. Użyjemy do tego funkcji ZEZWALAJ.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście nauczymy się jak w rozlewającej się formule dołożyć dodatkowe obliczenia podsumowania danych.
Odcinek bazuje na formule od Billa Szysza (rys. nr 1).
Rys. nr 1 – formuła Billa Szysza
Powyższą formułę przetłumaczyłem na język polski, żeby ułatwić jej zrozumienie (rys. nr 2).
Rys. nr 2 – formuła przetłumaczona na język polski
Mamy przykład, w którym wyciągamy konkretną liczbę ostatnich elementów, u nas 5. Ponadto jest dopisana formuła pozwalająca na dodatkowe obliczenia na tych danych, takie jak suma, średnia, min i max. Przede wszystkim możemy wybrać sobie jakie podsumowania nas interesują (fragmentator). Jak widać na rys. nr 2 formuła jest długa, mimo wykorzystania nowej funkcji ZEZWALAJ. Wykorzystujemy tutaj takie funkcje jak: TRANSPONUJ, LICZBA.KOLUMN, INDEKS, JEŻELI, SEKWENCJA, ILE.WIERSZY, SUMA, WYBIERZ, ŚREDNIA, MIN, MAX, MOD, NIE, CZY.BŁĄD, FILTRUJ, CZY.LICZBA, PODAJ.POZYCJĘ.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Kontynuujemy temat znajdowania elementów listy A, które zawierają elementy listy B. Dzięki wysiłkowi użytkownika YT Bill Szysz dostałem formułę, która wyciąga elementy z listy A bez kolumny pomocniczej.
Budowanie rozwiązania zaczniemy od zbudowania formuły, która oblicza ilość elementów, które spełniają postawiony warunek.
Zaczynamy od tego, że korzystamy z funkcji ZNAJDŹ (zwraca uwagę na wielkość liter) ewentualnie SZUKAJ.TEKST (nie zwraca uwagi na wielkość liter), żeby znaleźć elementy listy B w elementach listy A. W formule skorzystam z funkcji ZNAJDŹ, bo ma krótszy zapis, a wielkość liter jest identyczna w przykładowych listach.
Pojawia się jednak problem przy korzystaniu z funkcji ZNAJŹ ponieważ jeśli podamy pierwszy i drugi argument jako listy (kolumny), to wynik zwracany przez funkcję ZNAJDŹ nam nie pomoże, ponieważ Excel będzie porównywał nam poszczególne elementy na listach, a nie każdy element listy B z każdym elementem listy A. Żeby porównać listy w ten sposób jedna musi mieć orientację poziomą (być wierszem), a druga mieć orientację pionową (być kolumną). Zapisywanie list w odmiennej orientacji (pion-poziom) jest uciążliwe, dlatego pozostawiamy je jako listy w kolumnach, ale zmieniamy orientację listy B w formule za pomocą funkcji TRANSPONUJ.
=ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7)
Dzięki temu wynikiem funkcji ZNAJDŹ jest macierz (tablica) mająca tyle kolumn ile jest elementów na liście B i tyle wierszy ile jest elementów na liście A. Na skrzyżowaniu uzyskujemy informację, czy dany element listy B został odnaleziony w danym elemencie listy A. Wynik zwracany przez aktualną formułę jest mało czytelny ({1\#ARG!;1\#ARG!;#ARG!\#ARG!;#ARG!\1;#ARG!\7;#ARG!\#ARG!}) dlatego dodatkowo przedstawiam go w postaci zakresu w Excelu, któray ułatwi zrozumienie tego wyniku (w powyższym zapisie backslash (\) oznacza nową kolumnę, a średnik (;) nowy wiersz).
Formuła, którą zbudujemy do obliczenia ilości elementów nie będzie najkrótszą formułą, ale będzie częścią formuły, którą musimy stworzyć, żeby wyciągać kolejne elementy listy A spełniające założenia.
Dalszym krokiem będzie zsumowanie wartości w poszczególnych wierszach pomijając błędy (przyjmując, że mają wartość zero). Czyli wynik będzie przedstawiał się jako jedna kolumna, którą widać obok wcześniej pokazywanych danych:
Żeby osiągnąć taki wynik w pierwszej kolejności musimy zamienić wartości błędów na zera – najprościej można to zrobić za pomocą funkcji JEŻELI.BŁĄD.
Dopiero wtedy możemy zsumować wartości w poszczególnych wierszach. Chyba najprościej można to zrobić przez pomnożenie przez macierz (tablicę) zawierającą w kolumnie tyle jedynek ile jest elementów na liście B. Taką macierz można stworzyć np: podnosząc numery wierszy elementów listy B do zerowej potęgi:
WIERSZ($C$2:$C$3)^0
Teraz musimy przemnożyć przez siebie stworzone macierz (więcej o mnożeniu macierzy w Porada #102 — Mnożenie macierzy przez siebie MACIERZ.ILOCZYN, ich zapis i formuły tablicowe)
Teraz musimy zsumować wartości większe od zera. To już proste zadanie bo, wystarczy prosta operacja porównania. Tylko musimy pamiętać, że wynikowy wartości PRAWDA i FAŁSZ nie są sumowane przez funkcję SUMA, dlatego musimy je zamienić na zera i jedynki np.: dodając zero:
Musimy pamiętać, żeby tą formułę zatwierdzać kombinacją klawisz Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.
W ten sposób dowiedzieliśmy się ile jest elementów, które spełniają nasze założenia. Teraz potrzebujemy te elementy wyciągnąć. Potrzebujemy do tego części formuły zwracającej wynik mnożenia macierzy.
Teraz nie chcemy sumować wartości większych od zera tylko zwrócić dla nich numer wiersza odpowiadającego im elementom listy A. Wystarczy, że wynik funkcji MACIERZ.ILOCZYN włożymy do funkcji JEŻELI i odpowiednio policzymy numery wierszy za pomocą funkcji WIERSZ. Dla wartości chcemy, żeby funkcja JEŻELI zwracała wartości logiczne FAŁSZ, więc nie wypełniamy trzeciego argumentu tej funkcji.
Teraz, ponieważ mamy ponumerowane po kolei interesujące nas elementy listy A, wystarczy je wyciągać za pomocą funkcji INDEKS i funkcji MIN.K, której będziemy zwiększać argument k licząc kolejne wiersze funkcją ILE.WIERSZY:
Naszą formułę dopracujemy, żeby nie pokazywała błędów, jeśli dojdziemy do wiersza, który przekroczy ilość elementów, które spełniają za łożenia (to już obliczyliśmy). Wystarczy do tego funkcja JEŻELI i sprawdzanie ilości wiersza:
