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 — Usuwanie pustych wierszy — porada 320

W tym poście omówimy zagadnienie usuwania pustych wierszy.

Zdarza się, że po zaimportowaniu danych w arkuszu zostaną puste wiersze, ale nie chcemy przy okazji usunąć pustych komórek. Będziemy działać na naszych przykładowych danych. (Rys. nr 1)

Rys. nr 1 - Przykładowe dane

Rys. nr 1 — Przykładowe dane

Jeśli nie mamy sytuacji jak w wierszu oznaczonym kolorem żółtym, czyli pustej tylko jednej komórki z wiersza, możemy usunąć puste wiersze przy użyciu skrótu klawiszowego Ctrl + G lub klawisza F5. Otworzy się wtedy okienko Przechodzenie do. (Rys. nr 2)

Rys. nr 2 - Usuwanie pustych wierszy

Rys. nr 2 — Usuwanie pustych wierszy

W tym oknie klikamy przycisk Specjalnie, a następnie w oknie, które się otworzy (oknie Przechodzenia do – specjalnie) zaznaczamy Opcję Puste i zatwierdzamy przyciskiem Ok. (Rys. nr 3)

Rys. nr 3 - Okno Przechodzenie do - specjalnie

Rys. nr 3 — Okno Przechodzenie do — specjalnie

W efekcie otrzymujemy zaznaczone puste komórki. Możemy kliknąć na taką zaznaczoną komórkę prawym przyciskiem myszy i w podręcznym menu wybrać opcję Usuń lub użyć skrótu klawiszowego CTRL + - (Rys. nr 4)

Rys. nr 4 - Usuwanie pustych komórek za pomocą skrótu klawiszowego Ctrl + -

Rys. nr 4 — Usuwanie pustych komórek za pomocą skrótu klawiszowego Ctrl + -

W okienku Usuwania, które się otworzy wybieramy opcję Przesuń komórki do góry i zatwierdzamy przyciskiem Ok. Ponieważ przy Wojciechu (wiersz oznaczony na żółto) była pusta komórka, a nas interesowało usunięcie całych pustych wierszy, usunięcie pustych komórek zadziałało nie tak jakbyśmy chcieli, ponieważ w kolumnie Nazwisko nastąpiło przesunięcie o jedną komórkę w górę (popsuło to połączenia pomiędzy danymi). (Rys. nr 5)

Dlatego musimy cofnąć naszą komendę (Ctrl + Z).

Rys. nr 5 - Efekt zastosowania formuły usunięcia pustych wierszy, (błędny w przypadku pustej jednej komórki w wierszu)

Rys. nr 5 — Efekt zastosowania formuły usunięcia pustych wierszy, (błędny w przypadku pustej jednej komórki w wierszu)

W celu usunięcia pustych wierszy musimy sobie policzyć ile jest pustych komórek w poszczególnych wierszach. Użyjemy do tego funkcję LICZ.PUSTE. (Rys. nr 6) W sytuacji kiedy mamy mało danych, możemy sobie przeciągnąć funkcję na komórki poniżej.

Rys. nr 6 - Funkcja LICZ.PUSTE

Rys. nr 6 — Funkcja LICZ.PUSTE

Gdybyśmy mieli więcej danych, musielibyśmy skopiować funkcję Ctrl + C i skrótem klawiszowym Ctrl + End przejść na sam koniec arkusza.

Wtedy możemy łatwo przejść do odpowiedniej kolumny, a następnie naciskamy skrót Ctrl + Shift + , aby zaznaczyć wszystkie komórki powyżej, aż do komórki z formułą (ewentualnie strzałkami góra – dół dopasować zaznaczony zakres) i wklejamy naszą wcześniej przygotowaną formułę skrótem klawiszowym Ctrl + V. (Rys. nr 7)

Rys. nr 7 - Działanie skrótu klawiszowego Ctrl + Shift + ↑

Rys. nr 7 — Działanie skrótu klawiszowego Ctrl + Shift + ↑

Kolejnym etapem naszej pracy jest włączenie filtrów. Wybieramy z karty Dane – zakładkę Filtruj lub wciskamy skrót klawiszowy Ctrl + Shift + L. (Rys. nr 8)

Rys. nr 8 - Filtrowanie i użycie skrótu klawiszowego Ctrl + Shift + L

Rys. nr 8 — Filtrowanie i użycie skrótu klawiszowego Ctrl + Shift + L

Interesuje nas, aby usunąć wiersze, gdzie występuje cyfra 2. Po rozwinięciu menu filtrów, odznaczamy wyniki, które nas nie interesują. (Rys. nr 9)

Rys. nr 9 - menu filtrów

Rys. nr 9 — menu filtrów

Otrzymujemy wynik, czyli tylko puste wiersze (Rys. nr 10), które teraz możemy usunąć.

Rys. nr 10 - Puste wiersze, otrzymane po uruchomieniu filtrów

Rys. nr 10 — Puste wiersze, otrzymane po uruchomieniu filtrów

Zaznaczamy wiersze, a następnie używamy skrótu klawiszowego Ctrl + - aby je usunąć.

Należy pamiętać o wyłączeniu filtrów, których użyliśmy do wyselekcjonowania wierszy z numerem 2 (Rys. nr 11) oraz usunięciu pomocniczych obliczeń z kolumny C.

Rys. nr 11 - Czyszczenie filtrów z kolumny C

Rys. nr 11 — Czyszczenie filtrów z kolumny C

Otrzymujemy dane z usuniętymi pustymi wierszami (Rys. nr 12)

Rys. nr 12 - Dane po usunięciu pustych wierszy

Rys. nr 12 — Dane po usunięciu pustych wierszy


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 — Aktualna data i czas — funkcje DZIŚ i TERAZ — porada #316

W poradzie nr 316 zajmiemy się tematem aktualnej daty i czasu. Będziemy w tym zagadnieniu używać funkcji DZIŚ i TERAZ.

Jeśli chcemy, aby Excel wstawił nam w komórkę aktualną datę musimy użyć funkcji DZIŚ. (Rys. nr 1)

Rys. nr 1 - Funkcja DZIŚ

Rys. nr 1 — Funkcja DZIŚ

Cechą charakterystyczną dla funkcji DZIŚ i TERAZ jest to, że są to funkcje ulotne, czyli  przeliczają się za każdym razem jak zmienimy coś w arkuszu, np. jak wpiszemy wartość w komórkę.

Pokażemy to dokładnie na funkcji TERAZ. (Rys. nr 2)

Rys. nr 2 - Funkcja TERAZ

Rys. nr 2 — Funkcja TERAZ

Używając funkcji TERAZ otrzymamy dokładną datę, w zależności od formatowania nawet z dokładnością do jednej sekundy. (Rys. nr 3)

Rys. nr 3 - Efekt wykorzystania funkcji TERAZ

Rys. nr 3 — Efekt wykorzystania funkcji TERAZ

Aby wymusić ponowne przeliczenie arkusza możemy wejść w tryb edycji komórki (nacisnąć klawisz F2), a następnie zatwierdzić wartość klawiszem Enter. Wtedy funkcja TERAZ ponownie się przeliczy, zwracając aktualną datę i czas. (Rys. nr 4)

Rys. nr 4 - Przeliczenie aktualnego czasu

Rys. nr 4 — Przeliczenie aktualnego czasu

Analogicznie zadziała funkcja DZIŚ. Jeśli otworzymy arkusz jutro data zmieni nam się na aktualną.

Jak to często bywa w Excelu możemy ułatwić sobie pracę używając skrótów klawiszowych. Skrót Ctrl + ; zadziała wstawiając nam aktualną datę, ale ta data się nie zaktualizuje, kiedy otworzymy plik innego dnia. Dzieję się tak dlatego, że ten skrót nie wstawia nam funkcji a konkretną liczbę. (Rys. nr 5 i 6)

Rys. nr 5 - Skrót klawiszowy Ctrl + ;

Rys. nr 5 — Skrót klawiszowy Ctrl + ;

Rys. nr 6 - Efekt działania skrótu Ctrl + ;

Rys. nr 6 — Efekt działania skrótu Ctrl + ;

Podobnie w sytuacji, kiedy chcemy wstawić aktualną godzinę. Użyjemy do tego celu skrótu klawiszowego Ctrl + Shift + ;.

Rys. nr 7 - Skrót klawiszowy Ctrl+Shift+;

Rys. nr 7 — Skrót klawiszowy Ctrl+Shift+;

Ten skrót zadziała analogicznie do poprzedniego, czyli aktualna godzina nie ulegnie zmianie, ponieważ została w komórkę wstawiona wartość godziny, a nie funkcja. Dodatkowo chcemy, aby nasza godzina wyświetlała również sekundy, więc przy użyciu skrótu Ctrl + 1 otwieramy okno formatowania komórek.

W celu uzyskania aktualnej godziny, która będzie się aktualizować, czyli zmienić zgodnie z mijającym czasem, możemy od funkcji TERAZ odjąć funkcję DZIŚ (Rys. nr 8):

=C2-B2

Rys. nr 8 - Aktualna godzina

Rys. nr 8 — Aktualna godzina

Wynikiem w naszym przypadku jest liczba, aby otrzymać wynik w odpowiedniej formie, musimy użyć formatowania. Możemy sobie skopiować formatowanie z komórki obok. (Rys. nr 9)

Rys. nr 9 - Kopiowanie formatowania

Rys. nr 9 — Kopiowanie formatowania

Otrzymamy wtedy wynik (Rys. nr 10)

Rys. nr 10 - Wynik kopiowania formatowania

Rys. nr 10 — Wynik kopiowania formatowania

Kolejnym krokiem jest zmiana formatu liczb na karcie Narzędzia Główne z listy rozwijanej. Wybieramy format Czas. (Rys. nr 11)

Rys. nr 11 - Zmiana formatowania

Rys. nr 11 — Zmiana formatowania

Jeśli chcemy wyznaczyć teraz za pomocą skrótów klawiszowych, to używamy: aby wstawić datę – Ctrl + ; , następnie Spacja i aby wstawić godzinę — Ctrl + Shift + ;.

Ważne jest, że komórka ta przechowuje wartość, czyli jest stała, natomiast komórka, w której użyliśmy funkcji będzie się aktualizować. (Rys. nr 12)

Rys. nr 12 - Różnica między funkcją TERAZ a skrótem Ctrl+Shift+;

Rys. nr 12 — Różnica między funkcją TERAZ a skrótem Ctrl+Shift+;

Podsumowując, jeśli chcesz aby Excel zapamiętał konkretny czas (np. że tego dnia o konkretnej godzinie coś miało miejsce) należy użyć skrótów klawiszowych. Natomiast gdy chcesz aby data i czas zmieniały się na aktualny powinieneś użyć funkcji DZIŚ i TERAZ.

Na koniec pokażemy ciekawostkę, skrót klawiszowy umożliwiający wstawienie wartości liczbowej z powyższego wiersza, czyli Ctrl + ’ (pojedynczy cudzysłów). Wartość ta jest wstawiana w formatowaniu ogólnym. (Rys. nr 13)

Rys. nr 13 - skrót do wstawiania wartości z komórki powyżej

Rys. nr 13 — skrót do wstawiania wartości z komórki powyżej

 


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

Max i Min wartość dla unikalnych wierszy — Power Query #17

Dziś wyznaczymy, za pomocą Power Query, minimalną i maksymalną wartość dla unikalnych wierszy. W pytaniu widzów 120 robiliśmy to za pomocą tabeli przestawnej. Zadanie to jest nawet prostsze gdy korzystamy z Power Query, bo nie trzeba zmieniać tylu domyślnych ustawień co w przypadku tabel przestawnych.

W przykładowych danych (rys. 1) chcemy wyciągnąć minimalną i maksymalną wartość czasu, ale analogicznie postępujemy dla dowolnej liczby, bądź daty.

rys. 1 – Przykładowe dane

rys. 1 – Przykładowe dane

Nasze dane są tabelą Excela, więc możemy je łatwo zaczytać do Power Query (Excel 2016 – Karta Dane – polecenie Z tabeli). 

Może się zdarzyć, że Power Query źle zinterpretuje dane, ale łatwo wskazać mu ich właściwy format – wystarczy wybrać odpowiednią pozycję menu po kliknięciu w ikonę typu danych w nagłówku kolumny (rys. 2).

rys. 2 – Zmiana typu danych na prawidłowy

rys. 2 – Zmiana typu danych na prawidłowy

Gdy wszystkie kolumny mają już prawidłowy typ danych możemy znaleźć nasz minimnalne i maksymalne wartości po warunkach, tylko przed kliknięciem odpowiedniego polecenia (Grupowanie według – karta Narzędzia główne – rys. 3) warto zaznaczyć kolumny, po których będziemy grupować (kolumny po których chcemy wyznaczać unikalne wartości). Są to kolumny data i osoba.

rys. 3 – polecenie Grupowanie według

rys. 3 – polecenie Grupowanie według

Dzięki temu Power Query będzie od razu wiedział, że chcemy grupować dane po 2 kolumnach i odpowiednio wypełni nam okno grupowania (rys. 4). 

rys. 4 – okno grupowania z wypełnionymi polami na podstawie zaznaczonych wcześniej kolumn

rys. 4 – okno grupowania z wypełnionymi polami na podstawie zaznaczonych wcześniej kolumn

Teraz musimy dodać tylko kolejny poziom agregacji (przycisk Dodawanie agregacji), żeby znaleźć Min i Max po kolumnie Czas (rys. 5)

rys. 5 – Okno grupowania z uzupełnionymi poziomami agregacji/sposobami podsumowania

rys. 5 – Okno grupowania z uzupełnionymi poziomami agregacji/sposobami podsumowania

Dzięki tej operacji mamy interesujący nas wynik i możemy go zaczytać do Excela (rys. 6).

rys. 6 – Wyciągnięte Min i Max dla kolumny Czas pogrupowane po kolumnach Data i Osoba

rys. 6 – Wyciągnięte Min i Max dla kolumny Czas pogrupowane po kolumnach Data i Osoba

Adam Kopeć
Miłośnik Excela
Microsoft MVP

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

Wartość z ostatniego i pierwszego wiersza po warunkach — usuwanie duplikatów — porada #307

W pytaniu widzów 120 rozwiązaliśmy problem jak dla unikalnych wartości znaleźć minimalne i maksymalne wartości. Te wartości występowały odpowiednio jako pierwsze i ostatnie i w tym wpisie chcemy się zająć wyciąganiem wartości na podstawie ich kolejności, a nie wielkości (czy są min czy max).

Dziś posłużymy się jedynie prostymi komendami Excela jak usuwanie duplikatów i sortowanie. Ewentualnie przyda nam się jeszcze umiejętność stworzenia w Excelu kolumny indeksu (liczby porządkowej), ale to też będzie proste zadanie.

Zacznijmy od danych (standardowo zapisałem je jako tabela Excela, ale nie jest to konieczne do dziś wykonywanych przez nas operacji). Chcemy wyciągnąć pierwszy i ostatni kurs notowań w danym dniu dla danej firmy (unikalne wartości po tych kolumnach). Jak możemy to zrobić?

Dane z zaznaczonymi przykładowymi pierwszymi i ostatnimi wierszami danych dla konkretnych firm danego dnia

Dane z zaznaczonymi przykładowymi pierwszymi i ostatnimi wierszami danych dla konkretnych firm danego dnia

Wyciągnięcie pierwszych wartości jest bardzo proste wystarczy usunąć duplikaty po interesujących nas kolumnach, ale zanim to zrobimy stwórzmy sobie dodatkową kolumnę z indeksem (liczbą porządkową), żebyśmy wiedzieli jak to zrobić, gdyby nasze dane nie miały takiej kolumny.

Wystarczy w komórce F2 (czyli w kolumnie tuż obok danych) wpiszemy 1, a w F3 wpiszemy 2. Następnie zaznaczamy te komórki i klikamy dwa razy w prawy dolny róg zaznaczenia.

Tworzenie kolumny z indeksem

Tworzenie kolumny z indeksem

Excel stworzy rosnący ciąg do końca danych (nie ważne, czy dane są w tabeli Excela, czy nie).

My już jedną kolumnę z liczbą porządkową mamy, więc nie potrzebujemy drugiej, dlatego cofniemy naszą operację.
Teraz zrobimy coś dla nas ważniejszego – skopiujemy dane, w nich zaznaczamy dowolną pojedynczą komórkę w tych skopiowanych danych. Kolejnym krokiem będzie kliknięcie w polecenie Usuń duplikaty na karcie Dane.

Polecenie usuwania duplikatów na karcie Dane

Polecenie usuwania duplikatów na karcie Dane

W nim musimy zaznaczyć te kolumny, po których ma identyfikować duplikaty. W naszym przykładzie są to kolumny Data i Firma (rys. 4).

Okno usuwania duplikatów

Okno usuwania duplikatów

Zatwierdzamy nasz wybór przyciskiem OK i Excel usuwa duplikaty, czyli jeśli wartości w kolumnach Data i Firma powtórzyły się we wcześniejszym wierszu, to Excel usuwa ten wiersz. Zostaje tylko jego pierwsze unikalne wystąpienie, czyli dokładnie ten pierwszy wiersz, którego szukaliśmy (rys. 5).

Podsumowania usuwania duplikatów

Podsumowania usuwania duplikatów

Ponownie kopiujemy nasze dane i sortujemy je w odwrotnej kolejności (najprościej po kolumnie z liczbą porządkową) i znów usuwamy duplikaty. Excel znów zostawia tylko pierwsze unikalne wystąpienie wiersza, ale ponieważ zmieniliśmy kolejność sortowania, to są to ostatnie wiersze w danych źródłowych. Możemy porównać wyniki (rys. 6).

Dane po usuwaniu duplikatów od początku i od końca

Dane po usuwaniu duplikatów od początku i od końca

Jeszcze nam odrobinę brakuje do ich pełnego połączenie, ponieważ wiersze nie są w takiej samej kolejności, ale to wystarczy posortować dane, po unikalnych kolumnach (Data i Firma) oba zestawy danych. Jeszcze usunięcie zbędnych kolumn (np.: kolumny Czas) i może przysunąć dane do siebie (usunąć pustą kolumnę je rozdzielającą) (rys. 7).

Połączone pierwsze i ostatnie wiersze

Połączone pierwsze i ostatnie wiersze

Na ostatnim rysunku mamy jeszcze za dużo kolumn, ale to po to, żeby pokazać, że odpowiednie wiersze do siebie pasują. Wystarczy usunąć resztę zbędnych kolumn i zostawić tylko, te których potrzebujemy.
W tym przykładzie kopiowałem całą tabelę, ale nie zawsze jest to potrzebne. Ważne jest, żeby pilnować kolejności wiersze, a ile kolumn kopiujemy jest drugorzędną sprawą.

Adam Kopeć
Miłośnik Excela
Microsoft MVP