WYSZUKAJ.PIONOWO gdy nie ma na liście lub komórka jest pusta — sztuczki #16

Co zrobić gdy WYSZUKAJ.PIONOWO zwraca 0 lub #N/D! ?

Kiedy korzystasz z funkcji WYSZUKAJ.PIONOWO, możesz natrafić na 2 trudności. Pierwszą jest sytuacja kiedy wybrana kolumna ma pustą komórkę w miejscu spełnienia warunków wyszukiwania (funkcja zwraca 0).
Drugim sytuacja, kiedy szukanego elementu na ma na liście (funkcja zwraca błąd #N/D!)


WYSZUKAJ.PIONOWO gdy nie ma na liście lub komórka jest pusta — sztuczki #16

Najpierw zajmijmy się 0. Wystarczy wynik funkcji WYSZUKAJ.PIONOWO przyrównać do 0 i umieścić go jako test logiczny funkcji JEŻELI

WYSZUKAJ.PIONOWO zwraca zero lub błąd

WYSZUKAJ.PIONOWO zwraca zero lub błąd

Jeśli warunek ten będzie spełniony, czyli funkcja WYSZUKAJ.PIONOWO zwróci 0 dla pustego wiersza, możesz podstawić dowolną wartość np: "Brak ID", kiedy warunek nie będzie spełniony to wstawić znowu wynik wyszukiwania:

JEŻELI(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)=0;"Brak ID";WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0))

WYSZUKAJ.PIONOWO zamiast zera zwraca Brak ID

WYSZUKAJ.PIONOWO zamiast zera zwraca Brak ID

Pierwsza trudność rozwiązana, teraz zajmijmy się drugą. W Excelu 2007 i nowszym możesz wykorzystać z funkcji JEŻELI.BŁĄD. Wystarczy wcześniejszą formułę wstawić jako jej parametr i ustawić wartość, kiedy pojawi się błąd np: "Brak na liście" i już obie trudności rozwiązane:

=JEŻELI.BŁĄD(JEŻELI(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)=0;"Brak ID";WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0));"Brak na liście")

WYSZUKAJ.PIONOWO zamiast błędu zwraca Brak na liście

WYSZUKAJ.PIONOWO zamiast błędu zwraca Brak na liście

W Excelu 2003 i wcześniejszych jest trudniej, bo nie ma funkcji JEŻELI.BŁĄD, trzeba wykorzystać funkcję CZY.BRAK. Zwróci ona wartość PRAWDA, gdy poda jej się błąd #N/D!, czyli np: Excel nie znajdzie szukanego elementu na liście.

Teraz trzeba odpowiednio opatulić w funkcje JEŻELI:

=JEŻELI(CZY.BRAK(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0));"Brak na liście";JEŻELI(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)=0;"Brak ID";WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)))

Formuły te możesz uprościć gdy powpisujesz wartości w puste komórki. Żeby to zrobić szybko najpierw:

  • zaznacz cały obszar na którym chcesz uzupełnić puste komórki,
  • naciśnij F5
  • wybierz opcje specjalne
  • wybierz puste
  • wpisz wartość, którą chcesz i zatwierdź Ctrl + Enter

Teraz formuły będą wyglądać odpowiednio dla Excela 2007 i nowszego:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0);"Brak na liście")

WYSZUKAJ.PIONOWO gdy puste pozycje zamienione na Brak ID

WYSZUKAJ.PIONOWO gdy puste pozycje zamienione na Brak ID

dla Excela 2003 i starszego:

=JEŻELI(CZY.BRAK(WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0));"Brak na liście";WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0))

P.S.

Wpis na podstawie Excel Magic Trick 990

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 — WYSZUKAJ.PIONOWO gdy nie ma na liście lub komórka jest pusta — sztuczki #16

Formuła do tworzenie sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd — sztuczki #15

Jak stworzyć formułę do tworzenia sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd?

Żeby stworzyć niektóre sekwencje w Excelu trzeba się wykazać pomysłowością. Np: stworzenie przykładowego ciągu 1,1 1,2 1,3 2,1 2,2 2,3 itd wymaga użycia przynajmniej kilku funkcji.

Tworzenie tej sekwencji można podzielić na 2 części, część całkowitą i część po przecinku.


Formuła do tworzenie sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd — sztuczki #15

Aby stworzyć część całkowitą potrzeba najpierw zacząć liczyć od miejsca, z którego chcesz aby zaczynała się sekwencja. Uda ci się to zrobić dzięki dynamicznym obszarom i funkcji ILE.WIERSZY. Zakładając, że zaczynasz w komórce B7 funkcja wyglądać może tak

=ILE.WIERSZY(B$7:B7)

Dynamiczny obszar budujesz przez zalokowanie pierwszej części obszaru (B$7) i pozostawienie drugiej części jako odwołanie względne (B7). Teraz jak będziesz kopiował funkcję w dół obszar będzie się powiększał o nowe wierze i tym samym funkcja ILE.WIERSZY będzie zwracać kolejne liczby całkowite.

Liczenie wierszy

Liczenie wierszy

W filmie założone zostało, że sekwencja ma mieć 5 razy jedną liczbę na przedzie zanim przejdzie do następnej, dlatego wynik funkcji trzeba podzielić przez 5. Następnie trzeba go zaokrąglić do góry, tak, żeby zamiast części ułamkowej dostawać całości. Formuła będzie wyglądać tak:

=ZAOKR.W.GÓRĘ(ILE.WIERSZY(B$7:B7)/5;1)

Formuła dla sekwencja części całkowitej

Formuła dla sekwencja części całkowitej

Teraz można zabrać się za 2 część (po przecinku). Będzie to ciąg powtarzający się 1, 2, 3, 4, 5, 1 itd. Łatwo z tego wywnioskować, że przyda się funkcja MOD, która zwraca resztę z dzielenia. Najpierw trzeba zastosować tą samą funkcję jak w części pierwszej, tylko zmniejszoną o 1 ponieważ dla poprawnego wyniku trzeba zacząć liczyć od 0 nie od 1.

=ILE.WIERSZY(D$7:D7)-1

Następnie trzeba wyciągnąć resztę z dzielenia przez 5 i jeszcze zwiększyć ją o 1, żeby uzyskać pożądany ciąg, czyli

=MOD(ILE.WIERSZY(D$7:D7)-1;5)+1

Formuła dla sekwencja części ułamkowej

Formuła dla sekwencja części ułamkowej

Teraz możesz już połączyć formuły ciągiem

&","&

cała formuła dla stworzonego ciągu będzie wyglądać:

=ZAOKR.W.GÓRĘ(ILE.WIERSZY(F$7:F7)/5;1)&","&MOD(ILE.WIERSZY(F$7:F7)-1;5)+1

Połączona formuła sekwencji

Połączona formuła sekwencji

Ważne, że ta formuła zwróci tekst, ponieważ łączenie (&) będzie wykonane na samym końcu. Jeśli chcesz, żeby Twój ciąg był liczbami trzeba tą formułę wziąć w nawiasy i dodać zero (dodawanie Excel wykonuje przed łączeniem, taka jest kolejność działań).

=(ZAOKR.W.GÓRĘ(ILE.WIERSZY(F$7:F7)/5;1)&","&MOD(ILE.WIERSZY(F$7:F7)-1;5)+1)+0

Zapis formuły sekwencji w taki sposób, żeby była traktowana jako liczba

Zapis formuły sekwencji w taki sposób, żeby była traktowana jako liczba

P.S.

Wpis na podstawie Excel Magic Trick 991

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 — Formuła do tworzenie sekwencji 1,1 1,2 1,3 2,1 2,2 2,3 itd — sztuczki #15

Formatowanie warunkowe tabeli na podstawie 3 ostatnich cyfr ID — sztuczki #14

Jak ustawić formatowanie warunkowe dla wierszy, w których 3 ostatnie cyfry ID spełniają kryterium?

Żeby nadać formatowanie warunkowe na wiersze, gdzie sprawdza się kryterium dla 3 ostatnich liczb ID należy zbudować odpowiednią formułę.


Formatowanie warunkowe tabeli na podstawie 3 ostatnich cyfr ID — sztuczki #14

Formuły dla formatowania warunkowego, często dobrze najpierw tworzyć w arkuszu na sucho, żeby sprawdzić poprawność ich działa.

Zasada jest bardzo podobna jaką widziałeś w sztuczce #12

Najpierw trzeba wyciągnąć 3 ostatnie cyfry z ID za pomocą funkcji PRAWY

=PRAWY($B2;3)

a następnie przyrównać do kryterium, które jak pamiętasz należy zamienić na tekst łącząc z pustym ciągiem znaków:

=PRAWY($B2;3)=$D$2&""

Formuła do sprawdzania kryteriun dla 3 ostatnich cyfr ID

Formuła do sprawdzania kryteriun dla 3 ostatnich cyfr ID

Ważne w tej formule jest odpowiednie użycie odwołań mieszanych i bezwzględnych. Kryterium jest jedno, dlatego odwołujesz się do niego bezwzględnie ($D$2), ale funkcja PRAWY musi się zmieniać wraz ze sprawdzaniem kolejnych wierszy, ale ma być niezmienna dla przechodzenia przez kolumny, dlatego odwołanie w niej do komórki wygląda tak: $B2.

Wystarczy, że zaznaczysz odpowiedni obszar zaczynając od lewej górnej komórki i nałożysz formatowanie warunkowe wklejając podaną wyżej funkcję.

Nałożone formatowanie warunkowe

Nałożone formatowanie warunkowe

P.S.

Wpis na podstawie Excel Magic Trick 993

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 — Formatowanie warunkowe tabeli na podstawie 3 ostatnich cyfr ID — sztuczki #14

Wartości powyżej 90 percentylu sztuczki #13

Jak zaznaczyć wartości powyżej 90 percentylu?

Percentyl pomaga Ci wyznaczać liczby, które są np: większe niż 90% całego zbioru. Wszystko byłoby proste gdybym nie uczył się z angielskich filmów o Excelu. W angielskiej wersji Excela wszystkie funkcje z percentylem mają to uwzględnione w nazwie, w polskiej wersji Excela już nie jest tak łatwo dlatego znalezienie odpowiednika może Ci zająć dużo czasu jeśli nie masz takiej przygotowanej przeze mnie tabeli gdzie znajdziesz obok siebie funkcje Excela w 3 językach — polskim, angielskim i niemieckim:


Wartości powyżej 90 percentylu sztuczki #13

Skoro już wiem, jakiej nazywa się funkcja, której użył Mike Girvin w EMT 989

to wszystko staje się proste.
Funkcja po angielsku to:

PERCENTRANK.INC

Funkcja po polsku to:

PROC.POZ.PRZEDZ.ZAMK

Widać podobieństwo prawda?

Funkcja sprawdzająca, czy liczba jest większa od 90 percentylu

Funkcja sprawdzająca, czy liczba jest większa od 90 percentylu

Żeby teraz utworzyć formatowanie warunkowe, które zaznaczy Ci wszystkie liczby powyżej 90 percentylu wystarczy użyć funkcji dla podanego w filmie przykładu:

=PROC.POZ.PRZEDZ.ZAMK($A$2:$F$15;A2)>0,9

zaznaczyć obszar od górnej lewej komórki i wpisać tą funkcję w formatowanie warunkowe i wszystko gotowe.

Nałożone formatowanie na liczby powyżej 90 percentylu

Nałożone formatowanie na liczby powyżej 90 percentylu

P.S.

Wpis na podstawie Excel Magic Trick 989

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 — Wartości powyżej 90 percentylu sztuczki #13

Sumowanie po 3 ostatnich cyfrach ID — sztuczki #12

Jak sumować zyski przyjmując jako kryterium 3 ostatnie cyfry ID?

Wydawałoby się, że żeby sumować po 3 ostatnich liczbach ID wystarczy skorzystać z funkcji SUMA.JEŻELI i sprytnego łączenia symbolu wieloznacznego "*" z kryterium 3 ostatnich liczb ID.

=SUMA.JEŻELI(B2:B8;"*"&D2;A2:A8)

Niestety takie połączenie nie działa na liczbach.


Sumowanie po 3 ostatnich cyfrach ID — sztuczki #12

Ponieważ jednak chodzi o sprawdzenie 3 ostatnich cyfr, to łatwo jest je wyciągnąć korzystając z funkcji PRAWY. Można nawet przeprowadzić tą operację na całym przeszukiwanym zakresie:

PRAWY(B2:B8;3)

Tabela, z której chcemy wyciągnąć sumę

Tabela, z której chcemy wyciągnąć sumę

Warto tutaj zwrócić uwagę na to, że wynikiem nie są liczby tylko tekst. Możesz to zobaczyć zaznaczając całą formułę i naciskając F9

{"696";"943";"869";"696";"875";"398";"696"}

Podwójne cudzysłowy dają znać, że Excel traktuje te liczby jak tekst, dlatego przy sprawdzaniu kryterium trzeba je najpierw zamienić z liczby na tekst. Najłatwiej to zrobić łącząc ją z pustym ciągiem znaków, czyli z dwoma podwójnymi cudzysłowami:

D2&""

przyrównanie tych wartości daje wynik w postaci tabeli wartości PRAWDA i FAŁSZ, w zależności, czy są takie same, czy różne.

Dalej jednak nie można skorzystać z funkcji SUMA.JEŻELI, gdyż nie przyjmie ona tablicy jako parametru. W takiej sytuacji można wykorzystać funkcję SUMA.ILOCZYNÓW. Najpierw przemnożyć tabelę prawd i fałszów przez wartości ilości, a następnie zsumować. Tylko zanim się do tego zabierzesz najpierw wartości PRAWDA i FAŁSZ trzeba zamienić na 1 i 0. Najprościej i najszybciej to osiągnąć stawiając przed tablicą dwa znaki minus.

=–(PRAWY(B2:B8;3)=D2&"")

Formuła zwróci wtedy ciąg wartości 1 i 0

{1;0;0;1;0;0;1}

  • 1 — tam, gdzie była PRAWDA
  • 2 — tam, gdzie był FAŁSZ

Końcowa funkcja będzie miała taki wygląd:

=SUMA.ILOCZYNÓW(A2:A8;–(PRAWY(B2:B8;3)=D2&""))

P.S.

Wpis na podstawie Excel Magic Trick 992

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 — Sumowanie po 3 ostatnich cyfrach ID — sztuczki #12