Excel - kurs online - oferta dla każdego

Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.


Excel - kurs online. Dlaczego warto?

Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.

Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.

Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.

Excel — Ranking sprzedawców proste formuły tablicowe — porada 402

W tym poście wykonamy na podstawie przykładowych danych z rys. nr 1 ranking sprzedawców tablicowo. Tym razem omówimy prostą metodę. W poprzednich poradach (399–401) szliśmy torem myślenia Excela klasycznego, używaliśmy kolumn pomocniczych i różnych skomplikowanych funkcji żeby pokazać możliwości łączenia tych funkcji w jedną formułę.

rys1 - przykładowe dane
rys1 — przykładowe dane

Używaliśmy skomplikowanych formuł, żeby wyciągnąć dane do rankingu sprzedawców. Poprzednie posty są ważne, bo pokazują pewien tok myślenia jak łączyć dane, jak przekształcać poszczególne elementy. W tym poście opiszemy sposób, jak wyciągnąć ranking sprzedawców w jak najprostszy sposób, żeby jednak kolumna odpowiadała niejako za jedną kolumnę danych. Zaczniemy od listy unikatowych sprzedawców. Jesteśmy w Excelu tablicowym, więc skorzystamy z funkcji UNIKATOWE. Argumentem funkcji UNIKATOWE jest Tablica, czyli zakres z którego chcemy wyciągnąć unikatowe wartości (kolumna A – Sprzedawca). Jest to formuła tablicowa, więc mamy tutaj nomenklaturę tablicową, czyli kolumnę zapiszemy jako tSprzedażk[Sprzedawca]. Zapis formuły powinien wyglądać następująco:

=UNIKATOWE(tSprzedażk[Sprzedawca])

Po zatwierdzeniu formuły otrzymamy listę unikatowych sprzedawców przedstawioną na rys. nr 2.

Rys. nr 2 – lista unikatowych sprzedawców
Rys. nr 2 – lista unikatowych sprzedawców

Ilość unikatowych sprzedawców nie zmieni się, kiedy zmienimy ich kolejność. Z tego względu możemy im dołożyć numerację w kolumnie Miejsce. Użyjemy do tego funkcji SEKWENCJA. Pierwszym argumentem funkcji są wiersze, czyli ilość wierszy wypełnionych listą unikatowych sprzedawców. Ilość tych wierszy obliczymy za pomocą funkcji ILE.WIERSZY. Argumentem tej funkcji jest tablica, czyli zakres, w którym chcemy obliczyć ilość wierszy (jesteśmy w Excelu tablicowym więc rozlany zakres zapiszemy H2#). Zapis całej formuły będzie wyglądał następująco:

=SEKWENCJA(ILE.WIERSZY(H2#))

Po zatwierdzeniu powyższej formuły otrzymamy numerację wierszy przedstawioną na rys. nr 3.

Rys. nr 3 – numeracja wierszy unikatowych sprzedawców
Rys. nr 3 – numeracja wierszy unikatowych sprzedawców

Mając listę unikatowych sprzedawców, możemy podliczyć (zsumować) ich wyniki sprzedaży. Zrobimy to przy użyciu funkcji SUMA.JEŻELI. Pierwszym argumentem funkcji jest zakres, czyli nasza kolumna B – Sprzedawca (tSprzedażk[Sprzedawca]). Drugi argument to kryteria, czyli lista naszych unikatowych sprzedawców z rozlanej formuły (H2#). Trzeci argument to suma_zakres, czyli kolumna po jakiej chcemy zsumować wartości (tSprzedażk[Sprzedaż]). Zapis formuły powinien wyglądać następująco:

=SUMA.JEŻELI(tSprzedażk[Sprzedawca];H2#; tSprzedażk[Sprzedaż])

Po zatwierdzeniu powyższej formuły otrzymamy wyniki sprzedaży poszczególnych sprzedawców przedstawione na rys. nr 4.

Rys. nr 4 – wartości sprzedaży poszczególnych sprzedawców
Rys. nr 4 – wartości sprzedaży poszczególnych sprzedawców

W Excelu tablicowym mamy możliwość posortowania jednej kolumny według drugiej, czyli możemy posortować listę unikatowych sprzedawców według kolumny Sprzedaż. Trzeba tylko odpowiednio te dane połączyć. Zaczniemy od skopiowania formuły funkcji SUMA.JEŻELI za pomocą skrótu klawiszowego Ctrl+C. Teraz w kolumnie Sprzedawca wpiszemy funkcję SORTUJ.WEDŁUG. Pierwszym argumentem funkcji jest tablica, czyli kolumna którą chcemy posortować, w naszym przypadku będzie to lista unikatowych sprzedawców uzyskana dzięki funkcji UNIKATOWE. Drugi argument funkcji to według_tablicy1, czyli kolumna według której chcemy posortować pierwszą kolumna. W naszym przykładzie będzie to kolumna z podsumowaniem sprzedaży – kolumna Sprzedaż, uzyskana dzięki funkcji SUMA.JEŻELI. Jednak w tej formule odwołujemy się do kolumny, która się rozlewa (H2#), więc musimy ją poprawić. Kolejny argument funkcji to kolejność_sortowania, gdzie mamy do wyboru sortowanie rosnące (wartość 1) lub malejące (wartość ‑1). Wybieramy wartość ‑1. Zapis formuły powinien wyglądać następująco:

=SORTUJ.WEDŁUG(UNIKATOWE(tSprzedażk[Sprzedawca]);SUMA.JEŻELI(tSprzedażk[Sprzedawca];H2#; tSprzedażk[Sprzedaż]);-1)

Jeśli zatwierdzimy formułę bez zmiany zakresu H2# w funkcji SUMA.JEŻELI, to pojawi się nam komunikat Excela o odwołaniach cyklicznych przedstawiony na rys. nr 5.

Rys. nr 5 – komunikat Excela
Rys. nr 5 – komunikat Excela

Powyższy komunikat potwierdzimy przyciskiem OK i otrzymamy dane przedstawione na rys. nr 6.

Rys. nr 6 – wyniki sortowania według
Rys. nr 6 – wyniki sortowania według

Jak widać na rysunku powyżej zepsuła nam się lista unikatowych sprzedawców. Wynika to z tego, że nie zmieniliśmy odwołania do rozlanej formuły (H2#) w formule funkcji SUMA.JEŻELI dla funkcji SORTUJ.WEDŁUG. Aby formuła zadziałała prawidłowo, musimy podmienić zakres H2# na formułę, ż której ten zakres powstał, czyli na UNIKATOWE(tSprzedażk[Sprzedawca]. Zapis formuły powinien wyglądać następująco:

=SORTUJ.WEDŁUG(UNIKATOWE(tSprzedażk[Sprzedawca]);SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca];tSprzedażk[Sprzedaż]);-1)

Po zatwierdzeniu powyższej formuły otrzymamy prawidłowe wyniki sortowania według przedstawione na rys. nr 7.

Rys. nr 7 – prawidłowe wyniki sortowania
Rys. nr 7 – prawidłowe wyniki sortowania

Wykonaliśmy nasze zadanie za pomocą dwóch prostych formuł. Mamy ranking (numerację), posortowanych sprzedawców oraz wartości sprzedaży, które same się posortowały, dopasowując się do miejsca konkretnego sprzedawcy w rankingu. Jest to dużo prostszy sposób i bardziej zrozumiały niż formuły z poprzednich postów, gdzie używaliśmy wielu różnych funkcji.


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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel — Jak policzyć ilość punktów zdobytych we wszystkich wyścigach — INDEKS — porada #376

W dzisiejszym poście nauczymy się jak zmusić funkcję INDEKS, aby zwracała wiele wartości do jednej komórki.

Funkcja INDEKS ma możliwość zwrócić z przykładowej tablicy danych albo całą kolumnę albo cały wiersz (rys. nr 1).

Rys. nr 1 – argumenty funkcji INDEKS
Rys. nr 1 – argumenty funkcji INDEKS

Pierwszym argumentem funkcji jest tablica, czyli zaznaczony obszar danych (tabela). W naszym małym przykładzie chcemy, aby funkcja zwróciła nam wiersz, więc wystarczy jako drugi argument funkcji (nr_wiersza) podać numer wiersza, z którego dane chcemy uzyskać. Zapis funkcji będzie wyglądał następująco:

=INDEKS(A9:A15;0)

Kiedy podejrzymy sobie wyniki formuły w trybie edycji komórki za pomocą klawisza F9, otrzymamy wszystkie dane z zaznaczonego obszaru (rys. nr 2).

Rys. nr 2 – wyniki funkcji INDEKS
Rys. nr 2 – wyniki funkcji INDEKS

Funkcja ta zwróciła nam wszystkie wartości z kolumny, problem pojawia się wtedy, kiedy chcemy wyciągnąć tylko kilka wartości. Możemy określić je ręcznie zapisując poszczególne numery tych wartości w nawiasach klamrowych. Zapis powinien wyglądać następująco:

=INDEKS(A9:A15;{1;3;5})

Pojawia się wtedy problem, ponieważ przy takim zapisie funkcja INDEKS zwraca nam tylko jedną wartość (rys. nr 3).

Rys. nr 3 – tylko jedna wartość zwrócona przez funkcję INDEKS
Rys. nr 3 – tylko jedna wartość zwrócona przez funkcję INDEKS

Tak działają funkcje klasycznego Excela. W nowych updatach firmy Microsoft zostały wprowadzone ulepszenia, funkcje zostały poprawione aby lepiej spełniać potrzeby użytkowników.

Podczas rozwiązywania wyzwania 13 (https://www.youtube.com/watch?v=VYjplS87Z‑o) szukaliśmy rozwiązania, które zwróciło by dwie wartości w jednej komórce. Udało się to zrobić dla wartości tekstowych (rys. nr 4).

Rys. nr 4 – skomplikowany zapis formuły, która zwraca dwie wartości do jednej komórki
Rys. nr 4 – skomplikowany zapis formuły, która zwraca dwie wartości do jednej komórki

Jeśli jesteś tym szczęśliwcem i masz dostęp do najnowszych subskrypcji Microsoft to masz dostęp do nowych funkcji Excela. W tej subskrypcji funkcja INDEKS zwróci nam więcej wartości poprzez rozlanie. Zapis formuły powinien wyglądać następująco:

=INDEKS(A9:A15;{2;4})

Funkcja zwróci nam dwie wartości ale nie w jednej komórce, wynik zostanie rozlany na komórkę poniżej jak pokazano na rys. nr 5.

Rys. nr 5 – rozlanie wyników funkcji INDEKS
Rys. nr 5 – rozlanie wyników funkcji INDEKS

Excela klasyczny (zanim zostaną wprowadzone udoskonalenia) nie radzi sobie z tą funkcjonalnością. Funkcja INDEKS nie zwraca wartości liczbowych tylko range (zakres komórek) i pewnie z tego powodu ma problem, ponieważ my dajemy jej tablicę danych jako numery wierszy a nie jako zakres. Musimy „oszukać” funkcję INDEKS i tak przedstawić te wartości aby funkcja mogła je odczytać, czyli musimy zmusić funkcję INDEKS aby zwróciła ciąg wartości. W tym celu musimy użyć dwóch dodatkowych funkcji – JEŻELI i N. Dla funkcji JEŻELI jako pierwszy argument funkcji (test_logiczny) wpiszemy wartość 1, czyli sprawimy że test logiczny zawsze będzie zwracał wartość logiczną PRAWDA. Drugi argument funkcji – wartość_jeżeli_prawda to będą wartości, które chcemy zwrócić, czyli numery wierszy {1;3;5}.

Zapis funkcji powinien wyglądać następująco:

=INDEKS(A9:A15;JEŻELI(1;{1;3;5}))

Jeżeli podejrzymy wyniki tej formuły nadal otrzymamy zwróconą tylko jedną wartość, wiec musimy dołożyć funkcję N. Funkcja N konwertuje wartości nieliczbowe na liczby, daty oraz liczby kolejne. Upraszczając funkcja ta zamieni nam liczby na liczby, ale wykonując przy tym coś, co sprawi, że funkcja INDEKS zwróci nam wiele wartości. Zapis formuły powinien wyglądać następująco:

=INDEKS(A9:A15;N(JEŻELI(1;{1;3;5})))

Kiedy w trybie edycji komórki podejrzymy wyniki powyższej formuły za pomocą klawisza F9, otrzymamy trzy wyniki przedstawione na rys. nr 6.

Rys. nr 6 – Wyniki zwrócone przez funkcje INDEKS (z użyciem funkcji JEŻELI i N)
Rys. nr 6 – Wyniki zwrócone przez funkcje INDEKS (z użyciem funkcji JEŻELI i N)

Podsumowując zmusiliśmy funkcję INDEKS, aby zwróciła nam kilka wartości nie po kolei (wybrane wartości, a nie cały wiersz czy kolumnę). Teraz możemy użyć funkcji SUMA, która zsumuje nam te zwrócone wartości. Zapis formuły powinien wyglądać następująco:

=SUMA(INDEKS(A9:A15;N(JEŻELI(1;{1;3;5}))))

Otrzymamy wynik przedstawiony na rys. nr 7.

Rys. nr 7 – Suma trzech wartości otrzymanych z funkcji INDEKS
Rys. nr 7 – Suma trzech wartości otrzymanych z funkcji INDEKS

Wyjaśniliśmy działanie funkcji INDEKS z użyciem dodatkowych funkcji JEŻELI i N na prostym przykładzie. Teraz przejdziemy do wykorzystania takich obliczeń na bardziej praktycznym przykładzie. Chcemy w tym przykładzie żeby funkcja INDEKS zwróciła nam kilka wartości, abyśmy potem mogli je zsumować i wyznaczyć zwycięzcę. Zadanie to omówimy na podstawie przykładowych danych z rys. nr 8.

Rys. nr 8 – przykładowe dane
Rys. nr 8 – przykładowe dane

W danych tych mamy 4 wyścigi i 5 zawodników, którzy w każdym wyścigu zajęli jakieś miejsca. W zależności od zajętego miejsca jest przyznawana różna punktacja. Naszym zadaniem jest zsumowanie punktów wszystkich uczestników wyścigów, aby wiedzieć kto zdobył najwięcej punktów a kto najmniej. Naszym rdzeniem (podstawą obliczeń) będzie funkcja PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli miejsca zajęte przez danego zawodnika. Drugi argument to przeszukiwana_tab, czyli tablica z numerami miejsc (J2:J6). Zakres ten blokujemy bezwzględnie za pomocą klawisza F4. Trzeci opcjonalny argument to typ_dopasowania, czyli w naszym przykładzie wartość 0 dla dopasowania dokładnego. Zapis funkcji powinien wyglądać następująco:

=PODAJ.POZYCJĘ(G10:J10;$J$2:$J$6;0)

Kiedy podejrzymy wyniki tej funkcji dla pierwszego zawodnika w trybie edycji komórki za pomocą klawisza F9 otrzymamy 4 wyniki przedstawione na rys. nr 9.

Rys. nr 9 – Wyniki zwracane przez funkcję PODAJ.POZYCJĘ
Rys. nr 9 – Wyniki zwracane przez funkcję PODAJ.POZYCJĘ

Mamy numery miejsc, ale my chcemy wyciągnąć wartości dopasowane do tych numerów miejsc, czyli liczbę punktów przyznawanych za zdobycie danego miejsca. Użyjemy tutaj funkcji INDEKS. Pierwszy argument funkcji, czyli tablica, to będzie zakres z punktacją za poszczególne miejsca w wyścigu, który musimy zablokować bezwzględnie za pomocą klawisza F4. Drugi argument, czyli nr_wiersza, to wartości zwracane przez funkcję PODAJ.POZYCJĘ. Zapis funkcji będzie wyglądał następująco:

=INDEKS($K$2:$K$6;PODAJ.POZYCJĘ(G10:J10;$J$2:$J$6;0))

Jeśli podejrzymy wyniki tej funkcji za pomocą klawisza F9 otrzymamy błąd #ARG! (rys. nr 10). Funkcja INDEKS sobie nie radzi i zwraca nam błąd.

Rys. nr 10 – błąd zwrócony przez funkcję INDEKS
Rys. nr 10 – błąd zwrócony przez funkcję INDEKS

Musimy dołożyć kombinację funkcji JEŻELI i funkcji N taj ka w poprzednim przykładzie. Dokładamy funkcję JEŻELI z testem logicznym 1, który zwraca zawsze wartość logiczną PRAWDA, czyli wartości zwrócone przez funkcję PODAJ.POZYCJĘ. Musimy pamiętać o dołożeniu funkcji N. Zapis funkcji powinien wyglądać następująco:

=INDEKS($K$2:$K$6;N(JEŻELI(1;PODAJ.POZYCJĘ(G10:J10;$J$2:$J$6;0))))

Teraz jeśli podejrzymy wyniki formuły za pomocą klawisza F9 otrzymamy punktację za każdy wyścig, formuła zwróci nam 4 wartości przedstawione na rys. nr 11.

Rys. nr 11 – punktacja za poszczególne wyścigi
Rys. nr 11 – punktacja za poszczególne wyścigi

Na tym etapie możemy te obliczenia wstawić do funkcji SUMA i uzyskać całkowitą liczbę punktów zdobytą na zawodach przez poszczególnych zawodników. Zapis formuły powinien wyglądać następująco:

=SUMA(INDEKS($K$2:$K$6;N(JEŻELI(1;PODAJ.POZYCJĘ(G10:J10;$J$2:$J$6;0)))))

Musimy pamiętać aby podaną formułę zatwierdzić skrótem klawiszowym Ctrl+Shift+Enter, ponieważ jest to formuła tablicowa. Otrzymany wynik przeciągamy na wiersze poniżej i otrzymamy wyniki punktacji z całych zawodów przedstawione na rys. nr 12.

Rys. nr 12 – zsumowana punktacja poszczególnych zawodników
Rys. nr 12 – zsumowana punktacja poszczególnych zawodników

Z wyników tych widać kto jest zwycięzcą całych zawodów. Podsumowując w klasycznym Excelu musimy się namęczyć i napisać dość skomplikowane formuły aby uzyskać wymagany wynik, czyli w zasadzie wymusić aby funkcja INDEKS zwróciła kilka wyników. Od momentu wprowadzenia udoskonaleń formuły takie będą o wiele mniej skomplikowane, ponieważ wszystkie funkcje będą sobie radzić z formułami tablicowymi.


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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Ostatni i pierwszy wiersz po warunku — 2 formuły — porada #308

Kontynuujemy temat wyciągania ostatnich i pierwszych wierszy po warunku z porady 307. Tym razem napiszemy formuły, które pozwolą nam wyciągnąć interesujące nas dane.

Zaczniemy od formuły, która jest dla mnie klasycznym podejściem w podobnych sytuacjach – w funkcji JEŻELI będziemy sprawdzać dwa interesujące nas warunki – dla dnia i firmy, czyli porównujemy całe kolumny danych do konkretnych wartości. Ponieważ musimy sprawdzić 2 kolumny, dlatego wykonujemy na wynikach operacji porównań mnożenie (pamiętając o kolejności działań dokładamy nawiasy). Jeśli warunki są spełnione to chcemy mieć wartość z kolumny liczba porządkowa, jeśli nie to chcemy domyślną wartość FAŁSZ, więc nic nie wpisujemy:

=JEŻELI((tNotowania[Data]=G2)*(tNotowania[Firma]=H2);tNotowania2[L.p])

rys. 1 – Formuła sprawdzająca warunki

rys. 1 – Formuła sprawdzająca warunki

W formułach korzystamy z tego, że zakres danych jest zamieniony na tabelę Excela (tNotowania) oraz, że mamy kolumnę z liczbą porządkową. Jeśli nie mielibyśmy kolumny z liczbą porządkową musielibyśmy ją stworzyć w formule – odwołanie tNotowania2[L.p] musielibyśmy zamienić na formułę:

WIERSZ(tNotowania[Data])-WIERSZ(B1)

Tak stworzona funkcja JEŻELI zwraca tablicę z numerami porządkowymi, gdzie warunki były spełnione i wartościami FAŁSZ tam, gdzie nie były one spełnione:

{1;FAŁSZ;FAŁSZ;FAŁSZ;5;FAŁSZ;7;FAŁSZ;9;10;11;FAŁSZ;13;FAŁSZ;15;FAŁSZ;17;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}

Z takiej tablicy wystarczy odpowiednio wyciągnąć minimalną i maksymalną wartość, żeby uzyskać pierwszy i ostatni numer wiersza, który spełnia nasze warunki:

=MIN(JEŻELI((tNotowania[Data]=G2)*(tNotowania[Firma]=H2);tNotowania[L.p]))

rys. 2 – Wyciąganie numeru pierwszego wiersza za pomocą funkcji MIN

rys. 2 – Wyciąganie numeru pierwszego wiersza za pomocą funkcji MIN

Musimy pamiętać, że nasza formuła jest formuła tablicową, więc żeby zwracała prawidłowe wyniki musimy zatwierdzać ją za pomocą kombinacji klawiszy Ctrl + Shift + Enter.

Jak wyciągnęliśmy numer wiersza, to wystarczy już tylko funkcja INDEKS, żeby pobrać wartość z kolumny Cena:

=INDEKS(tNotowania[Cena];MIN(JEŻELI((tNotowania[Data]=G2)*(tNotowania[Firma]=H2);tNotowania2[L.p])))

rys. 3 – Obliczone pierwsze i ostatnie ceny po warunku

rys. 3 – Obliczone pierwsze i ostatnie ceny po warunku

Mamy już wynik, ale chce przedstawić jeszcze inny sposób na znalezienie ostatniego wiersza. Pierwszy raz poznałem tą sztuczkę od Billa Szysz(kowskiego). Wykorzystuje ona funkcję WYSZUKAJ i najważniejsze w niej jest to, że nie musimy formuły zatwierdzać jako formuły tablicowej.

Zanim zaczniemy pisać formułę, musimy sobie powiedzieć 2 ważne rzeczy na temat funkcji WYSZUKAJ – zawsze dokonuje dopasowania przybliżonego oraz nie zwraca uwagi na błędy w kolumnie w której szuka.

Dlatego będziemy szukać liczby 2 i kolumnę po której będziemy szukać stworzymy jako wynik podzielenia 1 przez wynik naszych porównań po kolumnach (pamiętając o kolejności operacji matematycznych). Wektorem (tablicą) wynikową będzie kolumna Cena:

=WYSZUKAJ(2;1/((tNotowania[Data]=G2)*(tNotowania[Firma]=H2));tNotowania[Cena])

rys. 4 – Wyszukiwanie ostatniego wiersza po warunku za pomocą funkcji WYSZUKAJ

rys. 4 – Wyszukiwanie ostatniego wiersza po warunku za pomocą funkcji WYSZUKAJ

Czemu nasza funkcja działa? Przypomnij sobie co robi funkcja WYSZUKAJ.PIONOWO, gdy szukamy na zasadzie przybliżonej wartości, która jest większa od największej liczby jaką mamy w posortowanej kolumnie – zwraca ostatnią wartość. Analogicznie działa funkcja WYSZUKAJ – szuka 2 na liście 1 i błędów dzielenia przez zero:

{1;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;1;#DZIEL/0!;1;#DZIEL/0!;1;1;1;#DZIEL/0!;1;#DZIEL/0!;1;#DZIEL/0!;1;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!}

Tak właśnie wygląda nasz przeszukiwany wektor (kolumna). Jeśli oba warunki są spełnione to mamy dwie wartości PRAWDA. Dowolna operacja matematyczna (np.: dzielenie) zamienia je na 1, czyli 1/(1*1)=1. Ale jeśli warunek nie jest spełniony otrzymujemy wartość FAŁSZ, która przy operacjach matematycznych zamienia się na 0, czyli np.: 1/(1*0) = błąd dzielenia przez zero (#DZIEL/0!). Ponieważ funkcja WYSZUKAJ ignoruje błędy pozostają jej same 1, czyli szukając 2, przy przeszukiwaniu przybliżonym, będzie przeszukiwała po ‘posortowanej’ liście 1, czyli odnajdzie pozycję ostatniej 1, a tym samym zwróci korespondującą do niej wartość z kolumny Cena.

Przedstawimy jeszcze inny sposób na znalezienie pierwszego wiersza. Niestety funkcja WYSZUKAJ tu nie zadziała i będziemy musieli wykorzystać funkcję PODAJ.POZYCJĘ. Będziemy sprawdzać kilka warunków – muszą się zgadzać wartości w kilku kolumnach. Jednym ze sposobów na to jest scalanie szukanych wartości i scalanie kolumn. W ten sposób uzyskamy jedną scaloną wartość, której będziemy szukać w scalonej kolumnie – funkcja PODAJ.POZYCJĘ idealnie się do tego nadaje. Dokładamy jeszcze dopasowanie dokładne i udaje nam się odnaleźć pierwszy wiersz z szukaną wartością, bo dopasowanie dokładne zwraca pierwszą wartość/pierwszy wiersz jaki znajdzie od początku danych:

=PODAJ.POZYCJĘ(G2&H2;tNotowania[Data]&tNotowania[Firma];0)

rys. 5 – Odnalezione numery pierwszych wierszy za pomocą funkcji PODAJ.POZYCJĘ

rys. 5 – Odnalezione numery pierwszych wierszy za pomocą funkcji PODAJ.POZYCJĘ

Wystarczy jeszcze tylko dołożyć funkcję INDEKS po kolumnie z ceną i mamy wartości pierwszych cen.

=INDEKS(tNotowania[Cena];PODAJ.POZYCJĘ(G2&H2;tNotowania[Data]&tNotowania[Firma];0))

Pamiętaj są to formuły tablicowe i musisz je zatwierdzić za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
P.S. Po podejrzeniu (klawiszem F9) przykładowa szukana scalona wartość w funkcji PODAJ.POZYCJĘ będzie wyglądać tak:

"42738Acme"

Zamiast daty zobaczyć liczbę, ale nie przejmuj się, gdyż Excel bez problemu sobie z tym radzi, poza tym scalone ze sobą kolumny Data i Firma też mają w sobie liczby, które są odpowiednikiem dat:

{"42738Acme";"42738Małe Kucyki";"42738Puchatek i Przyjaciele";"42738Małe Kucyki";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Acme";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Małe Kucyki";"42738Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42740Acme";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Puchatek i Przyjaciele";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Acme";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Małe Kucyki"}

Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak wyciągnąć wszystkie cyfry z tekstu POŁĄCZ TEKSTY — porada #295

Przed nami ciężkie zadanie wyciągnięcia wszystkich cyfr. Okazuje się ono dużo prostsze jeśli w Twojej wersji Excel 2016 (lub 365) masz zainstalowany już update z funkcją POŁĄCZ.TEKSTY i ZŁĄCZ.TEKST (ważne! bez Y na końcu, bo ZŁĄCZ.TEKSTY działa inaczej).

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 01

Jeśli masz te funkcję to czytaj dalej. Jeśli nie to zajrzyj do następnej porady (296).

Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.

Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:

=ADR.POŚR("1:"&DŁ(A2))

Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.

=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 02

Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.

{1;2;3;4;5;6;7;8;9;10;11;12;13;14}

Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:

=FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 03

Uzyskamy dzięki temu tekst rozłożony na pojedyncze znaki (tablicę znaków):

{"5";" ";"S";"u";"k";"i";"e";"n";"k";"a";" ";"1";"0";"0"}

Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów

{5;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;1;0;0}

Na szczęście jest nam to na rękę ponieważ nam zależy tylko na liczbach, a tam gdzie pojawił się błąd nie chcemy nic wyświetlać, więc wystarczy, że wstawimy naszą formułę do funkcji JEŻELI.BŁĄD, która zamiast błędów będzie zwracała pusty ciąg tekstowy (dwa podwójne cudzysłowy)

=JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);"")

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 04

Teraz formułą zwraca tablicę cyfr i pustych ciągów znaków, 

{5;"";"";"";"";"";"";"";"";"";"";1;0;0}

którą możemy łatwo połączyć za pomocą funkcji ZŁĄCZ.TEKST (przypominam bez Y na końcu) lub POŁĄCZ.TEKSTY jeśli zależy nam, żeby rozdzielać cyfry konkretnym ogranicznikiem, a nie bezpośrednio łączyć je ze sobą, ale w większości sytuacji chcemy je bezpośrednio łączyć, więc formuła będzie wyglądać tak:

=ZŁĄCZ.TEKST(JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);""))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 05

Musimy tylko pamiętać, że jest to formuła tablicowa i musimy ją zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter.

Przykład dla funkcji POŁĄCZ.TEKSTY z przecinkiem jako ogranicznikiem i pomijaniem pustych wartości (wartość logiczna PRAWDA) wygląda tak:

=POŁĄCZ.TEKSTY(",";PRAWDA;JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);""))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 06

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

Jak znaleźć komórki spełniające warunki po dacie wierszu i wartości komórki — widzowie #111

Potrzebujemy odnaleźć komórki, które spełniają warunek daty (wybieramy odpowiedni wiersz) oraz wartości w tych komórkach mają być większe od zera.

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 01

Żeby odnaleźć je wszystkie potrzebujemy niestety skomplikowanej formuły. W pierwszej kolejności warto, żebyśmy obliczyli ilość komórek, które spełniają warunki, żeby ograniczyć później ilość wykonywanych obliczeń tablicowych.

Żeby zlokalizować wiersz dla daty będziemy potrzebowali przede wszystkim funkcji INDEKS, która będzie patrzeć na cały zakres danych. Następnie będziemy potrzebowali znaleźć wiersz, który nas interesuje, czyli datę. W tym pomoże nam funkcja PODAJ.POZYCJĘ, która odnajdzie pozycję wybranej daty na liście wszystkich dat. Będziemy jeszcze potrzebowali powiedzieć Excelowi, że interesuje nas cały wiersz, dlatego w trzecim argumencie funkcji INDEKS będziemy musieli wpisać wartość 0.

=INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 02

Dzięki temu, że funkcja INDEKS zwraca odwołanie do zakresu możemy ją wstawić do funkcji LICZ.JEŻELI i policzyć ilość wartości w wierszu większych od zera.

=LICZ.JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0);">0")

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 03

Gdy mamy obliczoną ilość komórek w interesującym nas wierszu, które spełniają nasze kryterium, to możemy zacząć pisać formułę, która pozwoli nam wyciągnąć szukane wartości. W środku będzie nasza pierwsza funkcja INDEKS, dla której sprawdzimy, które wartości są większe od zera. Dla tych wartości chcemy, żeby Excel nam zwracał numery kolumn, w których te wartości się znajdują. Będziemy do tego potrzebowali funkcji JEŻELI, a jako jej drugiego argumentu funkcji NR.KOLUMNY, która będzie patrzeć na zakres $A$1:$H$1, żeby zwracała nam kolejne liczby (numery kolumn od 1 w górę). Trzeci argument funkcji JEŻELI pomijamy dzięki temu wynikiem naszej formuły będzie tablica numerów kolumn, gdzie była wartość, która spełniała nasz warunek i wartości FAŁSZ, tam gdzie wartość nie spełniała tego warunku.

=JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1)) -> {1\2\3\FAŁSZ\FAŁSZ\6\7\FAŁSZ}

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 04

Dalszym krokiem będzie wyciąganie kolejnych wartości, czyli będziemy potrzebowali zatrudnić funkcję MIN.K, która wyciągnie nam numery kolumn od najmniejszego, dzięki temu, że w argument k wpiszemy funkcję LICZBA.KOLUMN z dynamicznym zakresem ($K$8:K8), który będzie się powiększał, gdy formułę będziemy przeciągać w bok, dzięki czemu funkcja MIN.K będzie nam zwracała kolejne numery kolumn, gdzie wartość spełniła warunek, a jeśli przeciągniemy formułę za daleko to zobaczymy błąd #LICZBA!, ponieważ nie mamy aż tylu wartości w komórkach.

=MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8))

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 05

Ponieważ nam zależy na wartościach z tych kolumn, nie numerach kolumn, to musimy jeszcze raz napisać formułę wyciągającą wiersz po dacie, a wynik funkcji MIN.K wstawić w miejsce wcześniejszego zera (numeru kolumny)

=INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8)))

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 06

Ponieważ formuły tablicowe oprócz tego, że musimy je zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter, mocno obciążają procesor, to dopiszemy jeszcze warunek w funkcji JEŻELI, który będzie sprawdzał, czy przekroczyliśmy już liczbę, które spełniają nasze warunki. Jeśli tak to będziemy chcieli wstawić pusty ciąg znaków (dwa podwójne cudzysłowy).


=JEŻELI(LICZBA.KOLUMN($K$8:K8)>$L3;"";INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8))))

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 07

Uff. Udało się to cała nasza formuła. Do pełni szczęścia 😀 potrzebujemy jeszcze, wyciągać nagłówki kolumn, w których zostały znalezione wartości, które nas interesują – będzie to ciut prostsza formuła, bo od początku znamy zakres komórek odpowiadający nagłówkowi.

=JEŻELI(LICZBA.KOLUMN($K$8:K8)>$L3;"";INDEKS($B$1:$I$1;1;MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8))))

Widzowie 111 - Jak znaleźć komórki spełniające warunki po dacie w wierszu i wartości komórki 08

Od Excela 2010 możesz zastosować funkcję AGREGUJ, żebyś nie musiał formuły zatwierdzać jako formuły tablicowej (nie musisz korzystać z zatwierdzania kombinacją klawiszy Ctrl + Shift + Enter).

Dla nagłówków

=JEŻELI(LICZBA.KOLUMN($K$26:K26)>$L22;"";INDEKS($B$1:$I$1;AGREGUJ(15;6;NR.KOLUMNY($A$1:$H$1)/(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K22;$A$2:$A$15;0);0)>0);LICZBA.KOLUMN($K$26:K26))))

Dla wartości:

=JEŻELI(LICZBA.KOLUMN($K$27:K27)>$L22;"";INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K22;$A$2:$A$15;0);AGREGUJ(15;6;NR.KOLUMNY($A$1:$H$1)/(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K22;$A$2:$A$15;0);0)>0);LICZBA.KOLUMN($K$27:K27))))

Pozdrawiam
Adam Kopeć
Miłośnik Excela