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

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

Zwracanie kolejnych wartości dla tego samego kryterium — porada #164

Jak wyszukać kolejne wartości dla tego samego kryterium?

Zwracanie kolejnych wartości dla tego samego kryterium — porada #164 Zwracanie kolejnych wartości dla tego samego kryterium - porada #164

Czasami chcesz wyszukać nie tylko wystąpienie wartości jak to robi funkcja WYSZUKAJ.PIONOWO, ale też kolejne. Niestety w Excelu musisz się nad tym napracować.

Potrzebujesz najpierw sprawdzić czy w danym wierszu jest szukana wartość, a następnie jeśli jest to wstawić numer pozycji na liście, a jeśli nie to zostawić FAŁSZ. Możesz to osiągnąć za pomocą kombinacji funkcji JEŻELI i WIERSZ:

=JEŻELI($A$2:$A$11=$D2;WIERSZ($A$2:$A$11)-WIERSZ($A$2)+1)

dzięki temu uzyskamy ciąg w stylu:

{1;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;7;8;FAŁSZ;FAŁSZ}

teraz będziemy chcieli wyciągać kolejne minimalne wartości, czyli będziemy potrzebować funkcji MIN.K i NR.KOLUMNY (żeby zmieniać wartość parametru k automatycznie).

=MIN.K(JEŻELI($A$2:$A$11=$D2;WIERSZ($A$2:$A$11)-WIERSZ($A$2)+1);NR.KOLUMNY(A1))

ta formuła będzie zwracała kolejne pozycje wystąpienia danego argumentu, jak będziemy ją kopiować w bok.

Teraz wystarczy jeszcze dołożyć funkcję INDEKS i zakres tablicy, z której chcemy pobierać wartości.

=INDEKS($B$2:$B$11;MIN.K(JEŻELI($A$2:$A$11=$D2;WIERSZ($A$2:$A$11)-WIERSZ($A$2)+1);NR.KOLUMNY(A1)))

Na koniec ewentualnie możemy dołożyć obsługę błędów:

=JEŻELI.BŁĄD(INDEKS($B$2:$B$11;MIN.K(JEŻELI($A$2:$A$11=$D2;WIERSZ($A$2:$A$11)-WIERSZ($A$2)+1);NR.KOLUMNY(A1)));"brak ceny")

Te formuły trzeba zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter ponieważ są to formuły tablicowe.

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić to napisz do mnie. Ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Zwracanie kolejnych wartości dla tego samego kryterium — porada #164

Podział długiej listy na mniejsze — widzowie #9

Jak podzielić długą listę na wiele małych list?

Dostałem pytanie, czy istnieje funkcja, którą da się przekształcić długą listę (tak 100 000 elementów) na mniejsze listy każda w osobnej kolumnie.

Wielka lista na małe listy

Wielka lista na małe listy


Podział długiej listy na mniejsze — widzowie #9

Pojedynczej funkcji nie znam, ale udało mi się stworzyć prostą formułę. 

Zaczynamy od funkcji INDEKS. Pozwala ona łatwo wybrać element z listy, trzeba tylko podać odpowiedni numer tego elementu i to jest tutaj najtrudniejszą częścią, żeby dobrze zapisać kolejne kolumny. 

Zacznijmy od numeru wiersza, czyli wykorzystuję funkcję WIERSZ:

=INDEKS($A$1:$A$100000;WIERSZ(A1))

Ta formuła zwróci 1 element z długiej listy, ponieważ odwołuje się do komórki (A1), która jest w pierwszym wierszu. Jak będziesz ją przeciągać w dół, to będzie się odpowiednio zwiększał numer wiersza i będą zwracane kolejne elementy, 2, 3, itd. 

Wybieranie elementów do list tylko z funkcją WIERSZ

Wybieranie elementów do list tylko z funkcją WIERSZ

Problem polega na tym, że jak będę ją kopiował do kolejnych kolumn nie będzie się zmieniać lista elementów, które będą zwracane, dlatego przyda się liczenie kolumn. Tu też można zacząć od komórki A1. Funkcja NR.KOLUMNY zwróci dla tej komórki wartość 1, a jak będziesz tą formułę przesuwał w prawo będzie ta wartość będzie rosła, wraz z numerem kolumny:

      kolumna B — 2
      kolumna C — 3
      itd. 

Ponieważ w pierwszej kolumnie nowych list nie chce mieć jeszcze przesunięcia, to potrzebuje odjąć jedynkę, a wynik odejmowania pomnożyć przez wielkość list (np: 100)

=INDEKS($A$1:$A$100000;WIERSZ(A1)+(NR.KOLUMNY(A1)-1)*100)

Prawidłowa formuła dla przykładowej listy

Prawidłowa formuła dla przykładowej listy

Teraz tą formułę wystarczy przeciągnąć na odpowiedni obszar w dół i w prawo. Dla całej też dużej listy, będzie to obszar C1:ALN100

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Podział długiej listy na mniejsze — widzowie #9