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ę.
W dzisiejszym poście nauczymy się sprawdzać dane tekstowe pod kątem tego, czy zawierają cyfry. Chodzi o to, by sprawdzić czy dany tekst (kod) zawiera cyfry. Jeśli tekst rzeczywiście zawiera cyfry, może to oznaczać, że był wydawany po określonej dacie albo dotyczy to konkretnego działu lub usługi. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
W tym celu
możemy użyć funkcji ZNAJDŹ, która pozwala wyszukać jeden tekst w innym
lub funkcji SZUKAJ.TEKST, która działa na takiej samej zasadzie. Różnica
między funkcjami polega na tym, że funkcja SZUKAJ.TEKST nie rozróżnia
wielkości liter.
W naszym
przykładzie wykorzystamy funkcję ZNAJDŹ, która ma krótszy zapis.
Pierwszym argumentem funkcji jest szukany_tekst, czyli jakaś cyfra.
Drugi argument funkcji to w_tekście, czyli komórka w jakiej chcemy
znaleźć tą cyfrę (u nas komórka A2). Zapis funkcji powinien wyglądać
następująco:
=ZNAJDŹ(1;A2)
Powyższą
formułę zatwierdzamy i otrzymamy wynik przedstawiony na rys. nr 2.
Rys. nr 2 – wynik funkcji ZNAJDŹ
Funkcja ZNAJDŹ zwróci nam numer pozycji szukaj cyfry, oczywiście pod warunkiem, że szukana cyfra pojawia się w przeszukiwanym tekście. Nas nie interesuje pojedyncza cyfra, tylko wiele cyfr. Ponadto jeśli funkcja ZNAJDŹ nie znajdzie szukanej cyfry to zwróci nam błąd argumentu — #ARG! (rys. nr 3).
Rys. nr 3 – błąd zwrócony przez funkcję ZNAJDŹ w sytuacji kiedy nie ma w tekście szukanej cyfry
Tak jak
wspominaliśmy nie szukamy jednej cyfry, ale szukamy dowolnej cyfry, więc musimy
stworzyć tablicę wszystkich możliwych cyfr i zapisać ją jako pierwszy argument
funkcji. Ułatwieniem jest to że w funkcji wpisujemy konkretne cyfry a nie
tekst. Zapis funkcji z tablicą powinien wyglądać następująco:
=ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2)
Kolejność
cyfr w zapisie nie ma znaczenia. Jeśli przy takim zapisie podejrzymy sobie
wyniki funkcji w trybie edycji komórki za pomocą klawisza F9 otrzymamy tablicę
wyników przedstawioną na rys. nr 4.
Rys. nr 4 – podgląd wyników funkcji ZNAJDŹ
Funkcja
ZNAJDŹ zwróci nam błędy w przypadku, kiedy danej cyfry nie znajdzie oraz pozycję
(numer miejsca w tekście), kiedy dana cyfra wystąpi w tekście. Z podglądu
wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Dla nas
istotne jest tylko, czy cyfry zostały znalezione, a nie potrzebujemy pozycji
danej cyfry.
Skorzystamy
tutaj z funkcji CZY.LICZBA, która zwróci nam wartości logiczne PRAWDA i
FAŁSZ. Zapis funkcji powinien wyglądać następująco:
=CZY.LICZBA(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2))
Kiedy podejrzymy
sobie wyniki formuły w trybie edycji komórki za pomocą klawisza F9 otrzymamy
tablicę wartości logicznych przedstawioną na rys. nr 5, gdzie w sytuacji kiedy
cyfra zostanie znaleziona funkcja zwróci nam wartość logiczną PRAWDA, natomiast
w odwrotnej sytuacji otrzymamy wartość logiczną FAŁSZ.
Rys. nr 5 – Wartości logiczne zwrócone przez funkcję CZY.LICZBA
Z podglądu
wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Wartości
logiczne może być ciężko zliczyć, więc za pomocą podwójnej negacji zamieniamy
tablicę wartości logicznych na tablicę wartości 0 i 1. Zapis funkcji powinien
wyglądać następująco:
=–CZY.LICZBA(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9;A2))
Kiedy
podejrzymy sobie wyniki formuły w trybie edycji komórki za pomocą klawisza F9
otrzymamy tablicę wartości 0 i 1 przedstawioną na rys. nr 6.
Rys. nr 6 – tablica wartości 0 i 1 po zastosowaniu podwójnej negacji
Wartości
logiczne FAŁSZ zostają zamienione na wartość 0, natomiast wartości logiczne
PRAWDA na wartość 1. Z podglądu wyników formuły wychodzimy za pomocą skrótu
klawiszowego Ctrl+Z. Na tym etapie wystarczy zsumować te wartości, korzystając
z funkcji SUMA.ILOCZYNÓW. Używamy tej funkcji, aby nie trzeba było
pamiętać, aby zatwierdzać naszą formułę, jako tablicową (skrót klawiszowy
Ctrl+Shift+Enter). Zapis funkcji powinien wyglądać następująco:
Otrzymamy
wynik przedstawiony na rys. nr 7, czyli otrzymamy ilość znalezionych cyfr w
tekście.
Rys. nr 7 – wynik funkcji SUMA.ILOCZYNÓW (ilość znalezionych cyfr w tekście)
Po skopiowaniu formuły na wiersze poniżej otrzymamy wyniki przedstawione na rys. nr 8.
Rys. nr 8 – wyniki funkcji SUMA.ILOCZYNÓW dla całej tabeli
Co łatwo
zauważyć w wierszu 6 pojawia się nam błąd. Wynika on z tego, że funkcja ta
zwraca tylko pierwsze wystąpienie danej cyfry, więc w wierszu nr 6 mamy tylko 2
różne cyfry (0 i 1), funkcja nie bierze pod uwagę powtarzającej się cyfry 0.
Podsumowując funkcja sprawdza czy w tekście jest dany znak, a nie ile razy on
występuje.
Na koniec
musimy zrobić test logiczny, żeby sprawdzić czy wartość otrzymana z naszej
formuły jest większa od 0. Zapis powinien wyglądać następująco:
Dzięki użyciu
testu logicznego otrzymamy wartość logiczną PRAWDA dla sytuacji kiedy cyfry
występują w tekście lub wartość logiczną FALSZ, kiedy cyfry nie występują (rys.
nr 9).
Rys. nr 9 – sprawdzenie czy tekst zawiera cyfrę
Naszym zadaniem było sprawdzenie czy dany tekst zawiera jakąś cyfrę. Nie jest istotne ile jest tych cyfr oraz jaką cyfrę zawiera tekst.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście nauczymy się jak poprawić dane, a
konkretnie usuwać ulice z adresów. W przykładowych danych mamy podane nazwy
miast i ulic, chcemy usunąć nazwy ulic, a zostawić tylko nazwy miast (rys. nr 1).
rys. nr 1 — Przykładowe dane
W naszych danych mamy też błędy, brakuje spacji pomiędzy
danymi, nie mamy też identycznie rozpoczynających się nazw, bo raz jest ulica
raz aleja albo plac.
Zaczniemy od tego że chcemy znaleźć ulicę, czyli użyjemy
funkcji ZNAJDŹ. Jako pierwszy argument Szukany_tekst wpiszemy "ul.".
Drugi argument to miejsce gdzie szukamy tego tekstu, czyli komórka A2. Formuła
będzie wyglądać następująco:
=ZNAJDŹ("ul.";A2)
Zatwierdzamy formułę i kopiujemy na wiersze poniżej. Funkcja
ta zadziała prawidłowo, ale tylko dla sytuacji kiedy mamy w adresie
"ul." Co widać na rysunku nr 2.
rys. nr 2 — Działanie funkcji ZNAJDŹ
Liczba 8 oznacza że "ul." Pojawia nam się w danym
wierszu na miejscu ósmego znaku. Kiedy mamy w adresach plac lub aleje albo nie
mamy żadnej nazwy ulicy to funkcja zwraca nam błąd #ARG! Innymi oznaczeniami
zajmiemy się później, najpierw poprawimy przypadki gdzie mamy do czynienia ze
skrótem "ul.".
W naszym przykładzie w pierwszym wierszu mamy "ul."
dopiero na 8 znaku, ale między nazwą miasta a ulicy jest jeszcze spacja. Dałoby
się ją usunąć gdyby nie przypadki, gdzie w naszych danych brakuje odstępu
między nazwą miasta a ulicy. Czyli usuwając wszystko za nazwą "ul." trafimy
na spację lub nie za ostatnią literę nazwy miasta.
Do naszej funkcji ZNAJDŹ musimy dołożyć funkcję do wyciągania
tekstu LEWY. Zapis formuły będzie wyglądał następująco:
=LEWY(A2; ZNAJDŹ("ul.";A2)-1)
Z tekstu w komórce A2 chcemy wyciągnąć tyle znaków ile
znalazła funkcja ZNAJDŹ minus 1, czyli o jeden wcześniej niż się zaczyna
"ul.". Zatwierdzamy Ctrl+Enter i kopiujemy formułę (rys. nr 3).
rys. nr 3 — Działanie funkcji LEWY
Musimy pamiętać, że za częścią nazw miast jest jeszcze
spacja, aby ją usunąć musimy skorzystać z kolejnej funkcji USUŃ.ZBĘDNE.ODSTĘPY.
Zapis formuły będzie wyglądał następująco:
Funkcja USUŃ.ZBĘDNE.ODSTĘPY usunie spację, która została,
między nazwami miast a skrótami ul., natomiast jeśli spacji nie będzie nie
usunie nic – i tak otrzymamy prawidłowy wynik jak na rysunku nr 4.
rys. nr 4 — Działanie funkcji USUŃ.ZBĘDNE
Mamy wyciągnięte miasta z ulicą w adresie, a my chcemy pozbyć
się też nazw placów czy też alei. Użyjemy tutaj funkcji JEŻELI.BŁĄD, która w
przypadku kiedy wartość formuły dla ulic zwróciła nam błąd, zwróci nam tekst
pełnego adresu. Zapis funkcji będzie wyglądał następująco:
Zatwierdzamy formułę Ctrl+Enter i otrzymamy wyniki pokazane
na rysunku nr 5.
rys. nr 5 — Działanie funkcji JEŻELI.BŁĄD
W sytuacji gdzie mieliśmy błąd, mamy teraz powtórzone adresy.
Teraz możemy napisać analogiczne formuły dla sytuacji ze skrótami
"pl." oraz "al.". Możemy też poradzić sobie w inny sposób
przy założeniu, że chcemy się pozbyć tych dodatkowych danych, usunąć je i do
nich nie wracać. Zaznaczamy całą kolumnę z tymi danymi i wciskamy skrót
klawiszowy Ctrl+H. Otworzy nam się okno Znajdowanie i zamienianie, w którym
wpisujemy, żeby Excel zamienił nam każdy skrót pl. na ul. Następnie wciskamy
polecenie Zamień wszystko – punkt 3 na rysunku nr 6.
rys. nr 6 — Znajdowanie i zamienianie
Pojawi nam się komunikat (rys. nr 7). Skróty zostaną
zamienione i kiedy to się stanie zadziała nam formuła dla skrótu ul.
rys. nr 7 Komunikat Excela
Psujemy nasze dane zamieniając w danych adresy z placów na
ulice, ale zakładamy że te dane nie będą nam później potrzebne i że taką
operację wykonujemy tylko raz. Analogicznie postępujemy dla nazw alei w
adresach i otrzymujemy same nazwy miast, bo kolejny raz po zmianie nazw
zadziała nam formuła dla ulic (rys. nr 8).
rys. nr 8 — Wynik usunięcia nazw ulic
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Chcemy wyciągnąć liczbę z początku lub końca tekstu. Najpierw zajmiemy się pisaniem formuły do wyciągnięcia liczby z końca tekstu.
Jednym ze sposobów jest zaczęcie od znalezienie pozycji kolejnych cyfr. Możemy wykorzystać funkcję SZUKAJ.TEKST lub ZNAJDŹ. Obie znajdują pozycję szukanego tekstu (w naszym przykładzie liczby) w przeszukiwanym tekście, ale funkcja SZUKAJ.TEKST nie zwraca uwagi na wielkość liter, a funkcja ZNAJDŹ zwraca uwagę na wielkość liter. Ponieważ szukamy cyfr skorzystamy z funkcji, która ma krótszą nazwę.
Dalej ważne jest, że chcemy znaleźć pierwszą pozycję każdej cyfry. Żeby to zrobić musimy wpisać wszystkie cyfry w nawiasach klamrowych oddzielone slashami
{0\1\2\3\4\5\6\7\8\9}
Można sobie ułatwić życie i stworzyć serię cyfr w wierszu, odwołać się do niej, a następnie zaznaczyć całe odwołanie do tej serii i nacisnąć klawisz F9 – wtedy Excel zamiast odwołania wstawi potrzebną tablicę.
To, że zamiast odwołania do komórek skorzystamy z tablicy wpisanej na stałe (hardkodowej) pozwoli nam zatwierdzać formułę zwyczajnie, a nie jako formułę tablicową (za pomocą kombinacji klawiszy Ctrl + Shift + Enter).
=ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2)
Jednak przy wyszukiwaniu poszczególnych cyfr pojawią się błędy, gdy danej cyfry nie ma w przeszukiwanym tekście.
Obsługa błędów może być kłopotliwa, dlatego skorzystamy ze sztuczki, która ułatwi nam zadanie – do oryginalnego tekstu dołożymy na koniec ciąg wszystkich cyfr.
=ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2&"0123456789")
Dzięki temu zawsze znajdziemy każdą cyfrę, co najwyżej poza długością przeszukiwanego tekstu.
={10\13\14\15\16\11\18\19\20\21}
Ponieważ nas interesuje pierwsze wystąpienie jakiejkolwiek cyfry dlatego naszą dotychczasową formułę wstawimy do funkcji MIN.
Teraz żeby odnaleźć ile znaków chcemy wyciągnąć z prawej strony tekstu (funkcja PRAWY) wystarczy znalezioną pozycję pierwszej cyfry odjąć od długości całego tekstu i dodać korygującą jedynkę.
Jak wyciągnęliśmy liczbę z końca tekstu to możemy teraz w łatwy sposób wyciągnąć sam tekst odejmując od długości całego tekstu długość liczby.
=LEWY(A2;DŁ(A2)-DŁ(C2))
Teraz zajmiemy się przypadkiem, gdy nasza liczba jest na początku tekstu. Musimy podejść do tego inaczej. Zaczniemy od tego, że usuniemy z tekstu po kolei każdą cyfrę za pomocą funkcji PODSTAW i pustego ciągu znaków:
=PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};"")
Uzyskamy wtedy dziesięć tekstów, w których nie będą się znajdować poszczególne cyfry, czyli w większości będzie to cały tekst, bo danej cyfry już na początku nie będzie w tekście.
Tylko, że nam nie są potrzebne same teksty, tylko ich długość, czyli naszą formułę wstawiamy do funkcji DŁ. Znowu ponieważ wpisaliśmy na stałe tablicę cyfr (hardkodowanie) nie musimy zatwierdzać naszej formuły jako formuły tablicowej.
=DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))
Dzięki temu uzyskamy długość tekstu po usunięciu każdej kolejnej cyfry.
={12\13\13\13\13\12\13\13\13\13}
Czasami widać różnicę, do oryginalnej długości tekstu i właśnie tej różnicy szukamy. Wystarczy, że od długości oryginalnego tekstu odejmiemy te poszczególne długości.
=DŁ(I2)-DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))
Wynikiem będzie tablica różnic
={1\0\0\0\0\1\0\0\0\0}
Zostaje nam ją tylko zsumować i mamy długość całej liczby, która znajduje się na początku tekstu.
Kontynuujemy temat znajdowania elementów listy A, które zawierają elementy listy B. Dzięki wysiłkowi użytkownika YT Bill Szysz dostałem formułę, która wyciąga elementy z listy A bez kolumny pomocniczej.
Budowanie rozwiązania zaczniemy od zbudowania formuły, która oblicza ilość elementów, które spełniają postawiony warunek.
Zaczynamy od tego, że korzystamy z funkcji ZNAJDŹ (zwraca uwagę na wielkość liter) ewentualnie SZUKAJ.TEKST (nie zwraca uwagi na wielkość liter), żeby znaleźć elementy listy B w elementach listy A. W formule skorzystam z funkcji ZNAJDŹ, bo ma krótszy zapis, a wielkość liter jest identyczna w przykładowych listach.
Pojawia się jednak problem przy korzystaniu z funkcji ZNAJŹ ponieważ jeśli podamy pierwszy i drugi argument jako listy (kolumny), to wynik zwracany przez funkcję ZNAJDŹ nam nie pomoże, ponieważ Excel będzie porównywał nam poszczególne elementy na listach, a nie każdy element listy B z każdym elementem listy A. Żeby porównać listy w ten sposób jedna musi mieć orientację poziomą (być wierszem), a druga mieć orientację pionową (być kolumną). Zapisywanie list w odmiennej orientacji (pion-poziom) jest uciążliwe, dlatego pozostawiamy je jako listy w kolumnach, ale zmieniamy orientację listy B w formule za pomocą funkcji TRANSPONUJ.
=ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7)
Dzięki temu wynikiem funkcji ZNAJDŹ jest macierz (tablica) mająca tyle kolumn ile jest elementów na liście B i tyle wierszy ile jest elementów na liście A. Na skrzyżowaniu uzyskujemy informację, czy dany element listy B został odnaleziony w danym elemencie listy A. Wynik zwracany przez aktualną formułę jest mało czytelny ({1\#ARG!;1\#ARG!;#ARG!\#ARG!;#ARG!\1;#ARG!\7;#ARG!\#ARG!}) dlatego dodatkowo przedstawiam go w postaci zakresu w Excelu, któray ułatwi zrozumienie tego wyniku (w powyższym zapisie backslash (\) oznacza nową kolumnę, a średnik (;) nowy wiersz).
Formuła, którą zbudujemy do obliczenia ilości elementów nie będzie najkrótszą formułą, ale będzie częścią formuły, którą musimy stworzyć, żeby wyciągać kolejne elementy listy A spełniające założenia.
Dalszym krokiem będzie zsumowanie wartości w poszczególnych wierszach pomijając błędy (przyjmując, że mają wartość zero). Czyli wynik będzie przedstawiał się jako jedna kolumna, którą widać obok wcześniej pokazywanych danych:
Żeby osiągnąć taki wynik w pierwszej kolejności musimy zamienić wartości błędów na zera – najprościej można to zrobić za pomocą funkcji JEŻELI.BŁĄD.
Dopiero wtedy możemy zsumować wartości w poszczególnych wierszach. Chyba najprościej można to zrobić przez pomnożenie przez macierz (tablicę) zawierającą w kolumnie tyle jedynek ile jest elementów na liście B. Taką macierz można stworzyć np: podnosząc numery wierszy elementów listy B do zerowej potęgi:
WIERSZ($C$2:$C$3)^0
Teraz musimy przemnożyć przez siebie stworzone macierz (więcej o mnożeniu macierzy w Porada #102 — Mnożenie macierzy przez siebie MACIERZ.ILOCZYN, ich zapis i formuły tablicowe)
Teraz musimy zsumować wartości większe od zera. To już proste zadanie bo, wystarczy prosta operacja porównania. Tylko musimy pamiętać, że wynikowy wartości PRAWDA i FAŁSZ nie są sumowane przez funkcję SUMA, dlatego musimy je zamienić na zera i jedynki np.: dodając zero:
Musimy pamiętać, żeby tą formułę zatwierdzać kombinacją klawisz Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.
W ten sposób dowiedzieliśmy się ile jest elementów, które spełniają nasze założenia. Teraz potrzebujemy te elementy wyciągnąć. Potrzebujemy do tego części formuły zwracającej wynik mnożenia macierzy.
Teraz nie chcemy sumować wartości większych od zera tylko zwrócić dla nich numer wiersza odpowiadającego im elementom listy A. Wystarczy, że wynik funkcji MACIERZ.ILOCZYN włożymy do funkcji JEŻELI i odpowiednio policzymy numery wierszy za pomocą funkcji WIERSZ. Dla wartości chcemy, żeby funkcja JEŻELI zwracała wartości logiczne FAŁSZ, więc nie wypełniamy trzeciego argumentu tej funkcji.
Teraz, ponieważ mamy ponumerowane po kolei interesujące nas elementy listy A, wystarczy je wyciągać za pomocą funkcji INDEKS i funkcji MIN.K, której będziemy zwiększać argument k licząc kolejne wiersze funkcją ILE.WIERSZY:
Naszą formułę dopracujemy, żeby nie pokazywała błędów, jeśli dojdziemy do wiersza, który przekroczy ilość elementów, które spełniają za łożenia (to już obliczyliśmy). Wystarczy do tego funkcja JEŻELI i sprawdzanie ilości wiersza:
Dziś mamy trudne zadanie ponieważ musimy znaleźć minimalną cenę produktu, na podstawie jego ID, który znajduje się w opisie i może pojawić się w różnych miejscach tego opisu i w wielu komórkach. Będziemy działać na prostej bazie przykładowej.
Przede wszystkim musimy znaleźć komórki, które zawierają w opisie szukane ID. Możemy się do tego posłużyć funkcją ZNAJDŹ lub SZUKAJ.TEKST, w zależności czy ma dla znaczenie, czy rozróżniamy małe i wielkie litery – funkcja ZNAJDŹ je rozróżnia, funkcja SZUKAJ.TEKST nie. W naszym przykładzie posłużymy się funkcją ZNAJDŹ, ponieważ w ID i opisach litery zawsze są albo wielkie, albo małe. Poza tym ta funkcja jest trochę krótsza w zapisie.
Czyli za pomocą funkcji ZNAJDŹ szukamy konkretnego ID w opisach produktów.
Jeśli funkcja ZNAJDŹ odnajdzie ID w komórce w opisie, to zwróci pozycję początkowego znaku, czyli jeśli ID jest na samym początku opisu to będzie liczba 1, bo już pierwszy znak należy do szukanego ID, jeśli ID będzie dalej w opisie to funkcja ZNAJDŹ zwróci odpowiednią inną liczbę.
Dla nas istotne jest, że jeśli ID został znaleziony mamy zwracaną liczbę, jeśli nie to błąd argumentu (#ARG!). Do dalszego kroku potrzebujemy wartości PRAWDA lub FAŁSZ, możemy je uzyskać wstawiając funkcję ZNAJDŹ do funkcji CZY.LICZBA.
Wiemy gdzie występuje nasz szukany ID, ale nie wiemy jakie są dla niego wartości. Musimy też zignorować wartości FAŁSZ. Będziemy chcieli skorzystać z funkcji AGREGUJ, więc wykorzystamy sztuczkę z podzieleniem cen przez powyższą tablicę prawd i fałszów. Wynikiem będą ceny dla wartości PRAWDA i błędy dzielenia przez zero () dla wartości FAŁSZ, ponieważ przy dowolnej operacji matematycznej Excel zamienia wartości PRAWDA na 1, a FAŁSZ na 0.
Tą tablicę możemy już wstawić do funkcji AGREGUJ z odpowiednimi parametrami. Ponieważ chcemy znaleźć minimalną wartość i pracujemy na tablicach jako pierwszy argument wpisujemy wartość 15, czyli będziemy korzystać z funkcji MIN.K (funkcja MIN w funkcji AGREGUJ nie radzi sobie z operacjami tablicowymi). Drugi argument ustawiamy na wartość 6, ponieważ chcemy zignorować wszystkie błędy. Trzeci argument to tablica, a czwarty to parametr k funkcji MIN.K, czyli numer minimalnej wartości, my chcemy uzyskać najmniejszą wartość, więc wpisujemy tu 1. Cała nasza formuła będzie wyglądać tak:
Jeśli masz wersję Excela wcześniejszą niż 2010, to nie będziesz miał jeszcze dostępu do funkcji AGREGUJ. W takiej sytuacji musisz skorzystać z formuły, którą musisz zatwierdzać jako formułę tablicową, czyli kombinacją klawiszy Ctrl + Shift + Enter
Jedną z zalet rozwiązania z funkcją AGREGUJ jest to, że możesz zatwierdzić formułę zwyczajnie, a funkcja AGREGUJ sama poradzi sobie z operacjami na tablicach.
