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

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 znaleźć przedostatni wiersz po warunku (formuła tablicowa) — widzowie #115

Czasami potrzebujemy znaleźć przedostatnią wartość na liście, a nawet przedostatnią wartość pod warunkiem. Rozpatrzmy to na prostym zestawie biegaczy i ich czasów, tylko nie chodzi nam o ich przedostatni czas w kontekście wartości, ale miejsca na naszej liście.

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 01

W pierwszej kolejności będziemy musieli sprawdzać warunek – wykonać test logiczny, a jak wykonujemy test logiczny, to niemal nieodzowna jest funkcja JEŻELI. Właśnie w niej napiszemy nasz prosty warunek, czy pozycja na liście jest równa wybranemu przez nas biegaczowi.

=JEŻELI(A2:A21=E1

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 02

Jeśli warunek jest spełniony to chcemy uzyskać numer wiersza danych (funkcja WIERSZ), gdzie ten warunek został spełniony. W przeciwnej sytuacji chcemy mieć zwracaną wartość FAŁSZ – wystarczy, że nie wypełnimy trzeciego argumentu funkcji JEŻELI:

=JEŻELI(A2:A21=E1;WIERSZ(A2:A21)-WIERSZ(A2)+1)

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 03

Jeśli podejrzymy wynik formuły (klawiszem F9) to zobaczymy tablicę wartości FAŁSZ i numerów wiersza danych tam, gdzie warunek został spełniony.

{FAŁSZ;FAŁSZ;FAŁSZ;4;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}

Nas interesuje druga maksymalna wartość, czyli potrzebujemy skorzystać z funkcji MAX.K, gdzie jej drugim argumentem będzie wartość 2.

=MAX.K(JEŻELI(A2:A21=E1;WIERSZ(A2:A21)-WIERSZ(A2)+1);2)

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 04

Musimy tylko pamiętać zatwierdzać formułę kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Uzyskujemy wtedy numer wiersza, gdzie jest interesująca nas wartość, a my potrzebujemy samej wartości. Wystarczy, że skorzystamy z funkcji INDEKS, której podamy kolumnę, z której chcemy poznać wartość.

=INDEKS($B$2:$B$21;MAX.K(JEŻELI($A$2:$A$21=E1;WIERSZ($A$2:$A$21)-1);2))

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 05

Ciągle musimy pamiętać zatwierdzać formułę Ctrl + Shift + Enter. Analogicznie możesz budować formuły pozwalające Ci odszukać kolejne wystąpienia warunku zmieniając funkcję MAX.K na MIN.K

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

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

Jak zaznaczyć 5 komórek w ciągu, które spełniają warunek — widzowie #108

Dostałem zapytania jak zaznaczyć ciąg 5 (lub więcej) komórek, które spełniają warunek (np: są puste). Jeśli możesz sobie pozwolić, że nad danymi, które sprawdzasz jest odpowiednia ilość wierszy, gdzie nie będzie spełniony sprawdzany warunek, to zadanie sprowadza się do liczenia najdłuższego ciągu w zakresie, który się przesuwa — porada 263:
https://www.youtube.com/watch?v=as9mztMnTsw

Końcowa formuła tablicowa (musisz zatwierdzić Ctrl + Shift + Enter):

=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);"");JEŻELI(CZY.PUSTA(A1:A9);"";WIERSZ(A1:A9))))>4

Jednak czasami zdarza się sytuacja, gdzie musisz martwić się o sufit, czyli górę arkusza, żeby Twój zakres nie wyszedł poza niego. W takiej sytuacji musisz najpierw zbudować zakres, który zatrzyma się na pierwszym wierszu arkusza i nie będzie szedł wyżej. Przyda się Ci do tego funkcja INDEKS.

Tablicą na którą będziemy patrzeć jest cała kolumna A (odwołanie A:A). Musimy tylko wybrać odpowiedni wiersz. W górę musimy przesunąć się 4 wiersze w górę (dla ciągu 5 komórek spełniających nasz warunek), ale nie dalej niż wiersz 1. Wystarczy, że do funkcji MAX wstawimy formułę WIERSZ()-4 oraz 1, funkcja WIERSZ bez podania argumentów zwraca wiersz komórki, w której znajduje się formuła. Czyli pierwsza komórka zakresu to:

=INDEKS(A:A;MAX(WIERSZ()-4;1))

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-01

Tu trzeba wspomnieć, że funkcja INDEKS zwraca faktycznie odwołanie do komórki, a nie jej wartość, dzięki czemu jeśli dołożymy dwukropek możemy za pomocą funkcji INDEKS budować zakresy. Druga komórka jest łatwiejsza do zbudowania, bo to tylko WIERSZ()+4, czyli:

=INDEKS(A:A;WIERSZ()+4)

łącząc dwie powyższe formuły mamy zbudowany dynamiczny zakres:

=INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4)

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-02

Teraz musimy sprawdzić, czy komórki z zakresu spełniają warunek, tu jest on prosty — wystarczy wstawić naszą formułę do funkcji CZY.PUSTA. Ważniejszy jest kolejny krok, gdzie jeśli dana komórka spełnia nasz warunek, to chcemy, żeby został zwrócony numer wiersza tej komórki. Będziemy potrzebować funkcji JEŻELI i funkcji WIERSZ po naszym zbudowanym zakresie. Jeśli komórka nie spełnia warunku chcemy uzyskać pusty ciąg znaków (""), czyli całość sprowadza się do formuły:

=JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"")

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-03

Zbudowana przez nas formuła posłuży jako tablica danych do funkcji CZĘSTOŚĆ, natomiast jako tablicę przedziałów będziemy chcieli mieć odwrotność zbudowanej formuły, czyli pusty ciąg znaków ma być wtedy, gdy warunek jest spełniony, a numer wiersza wtedy, gdy warunek nie jest spełniony.

=CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"");JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"";WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4))))

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-04

Pozostaje sprawdzić, jaki jest maksymalny ciąg (wstawić naszą formułę do funkcji MAX) i pamiętać żeby zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter ponieważ jest to formuła tablicowa.

Niestety tej formuły nie przyjmie nam formatowanie warunkowe, bo dynamicznie budujemy zakres za pomocą funkcji INDEKS i musimy sobie zostawić formułę w kolumnie pomocniczej, by sprawdzać, czy dla danego wiersza maksymalny ciąg spełnia nasze kryteria. 

Ponieważ te komplikacje występują przypomnę jeszcze raz, że łatwiejszym rozwiązaniem może być dostawienie wierszy na początku arkusza, żebyśmy od pierwszej sprawdzanej komórki mogli zaznaczyć cały sprawdzany zakres, który będzie się przesuwał z formułą w dół, czyli przykładowa formuła:

=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);"");JEŻELI(CZY.PUSTA(A1:A9);"";WIERSZ(A1:A9))))>4

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak wyszukiwać uwzględniając wielkość liter różne funkcje — widzowie #95

 

Tym razem do wyszukiwania z uwzględnieniem wielkości liter wykorzystamy inne funkcje niż WYSZUKAJ.PIONOWO. Rozpatrzymy dwa rozwiązania. Oba będą wykorzystały wynik funkcji PORÓWNAJ:

=PORÓWNAJ(E2;$A$2:$A$11)

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 01

Tak jak w poprzednich wpisach wynikiem będzie tablica wartości logicznych PRAWDA i FAŁSZ. PRAWDA, będzie w tych komórkach, gdzie ciągi tekstowe były identyczne w pozostałych będzie FAŁSZ.

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 02

Pierwsza formuła będzie jeszcze korzystać z funkcji PODAJ.POZYCJĘ oraz INDEKS. Za pomocą funkcji PODAJ.POZYCJĘ odnajdziesz wartość PRAWDA w wynikach funkcji PORÓWNAJ:

=PODAJ.POZYCJĘ(PRAWDA;PORÓWNAJ(F2;$A$2:$A$11);0)

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 03

A jak masz już pozycję prawdy, to wystarczy skorzystać z funkcji INDEKS, gdzie jako pierwszy argument wstawisz zakres kolumny, z której chcesz wyciągnąć wartość:

=INDEKS($C$2:$C$11;PODAJ.POZYCJĘ(PRAWDA;PORÓWNAJ(F2;$A$2:$A$11);0))

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 04

Pamiętaj tylko zatwierdzić formułę jako formułę tablicową – kombinacją klawiszy Ctrl + Shift + Enter.

Drugie rozwiązanie będzie wykorzystywało funkcję WYSZUKAJ. Tylko standardowo musisz odpowiednio przygotować dane, żeby z niej skorzystać. Tym razem wynik funkcji PORÓWNAJ posłuży jako dzielnik. Wystarczy, że wartość 1 podzielisz przez wynik funkcji PORÓWNAJ:

=1/PORÓWNAJ(F2;$A$2:$A$11)

Ponieważ w Excelu jeśli wykonasz dowolną operację matematyczną na wartościach logicznych PRAWDA lub FAŁSZ, to Excel zamieni je odpowiednio na liczby 1 i 0. To wynikiem takiego dzielenia, będzie tablica błędów dzielenia przez zero i liczby 1 (dla PRAWDA):

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 05

Teraz już możesz obudować naszą tablicę z wynikami funkcją WYSZUKAJ. Będziesz szukał liczby 2, ponieważ funkcja WYSZUKAJ zawsze działa na zasadzie dopasowania przybliżonego, dlatego jeśli karzesz jej szukać 2 w tablicy 1 i błędów dzielenia przez zero (#DZIEL/0!), to Excel znajdzie ostatnią liczbę 1. (Odwrotnie jak w pierwszej formule, która znajduje pierwsze wystąpienie szukanej wartości).

Potrzeba jeszcze tylko podać argument wektor_wynikowy, czyli analogicznie jak w funkcji INDEKS kolumnę, z której chcesz wyciągnąć wartość:

=WYSZUKAJ(2;1/PORÓWNAJ(F2;$A$2:$A$11);$C$2:$C$11)

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 06

To rozwiązanie fajne jest dlatego, gdyż nie wymaga zatwierdzania specjalna kombinacją klawiszy.

Odpowiednio wyniki dla dwóch zaprezentowanych formuł będą się różnić ze względu na ilość występujących tekstów i ich pozycje:

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 07
Pozdrawiam
Adam Kopeć
Miłośnik Excela