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.
Załóżmy, że potrzebujesz zliczyć komórki w których wystąpiła wielka litera "U", bo ona oznacza dla Ciebie coś istotnego, a mała litera "u", nie jest dla Ciebie istotna. Jak to zrobić.
Rozważmy dwie sytuacje. W pierwszej w komórce znajduje się tylko 1 litera. W drugiej w komórce jest więcej liter.
W pierwszej sytuacji możemy skorzystać z funkcji PORÓWNAJ, która porównuje, czy dwa ciągi znaków są identyczne m.in. pod kątem wielkości liter. Zwykłe porównanie nie zapewniłoby rozróżnienia pomiędzy małymi, a wielkimi literami. Czyli środek naszej formuły będzie wyglądać tak:
=PORÓWNAJ(A2:A10;"U")
Powyższa formuła zwraca wartości PRAWDA, gdy w komórce jest wielka litera "U" i FAŁSZw przeciwnej sytuacji. Funkcja PORÓWNAJ zwraca odpowiedni ciąg tych wartości:
Musimy zsumować prawdy, ale, żeby uniknąć zatwierdzania formuły jako formuły tablicowej będziemy chcieli wspomóc się funkcją SUMA.ILOCZYNÓW, która radzi sobie z operacjami na tablicach, ale niestety ignoruje wartości logiczne PRAWDAi FAŁSZ. Dlatego musimy je zamieć odpowiednio na 1 i 0, wykonując na nich dowolną operację matematyczną. Najszybsza jest podwójna negacja, czyli dołożenie dwóch znaków minus przed funkcją PORÓWNAJ. W takiej sytuacji cała formuła będzie wyglądała tak:
Teraz rozwiążmy drugą sytuację. Musimy sprawdzić, czy w poszczególnych tekstach z interesującego nas zakresu znajduje się wielka litera "U". Tym razem zaczniemy od funkcji ZNAJDŹ, która zwraca pozycję jednego ciągu tekstowego w innym tekście, a dodatkowo zwraca uwagę na wielkość liter.
=ZNAJDŹ("U";D2:D10)
Jeśli funkcja ZNAJDŹ nie znajdzie szukanego tekstu to zwraca błąd argumentu, w przeciwnej sytuacji zwraca liczbę w omawianym przykładzie wynikiem będzie ciąg:
{#ARG!;2;#ARG!;1;#ARG!;#ARG!;2;#ARG!;#ARG!}
Na błędach trudno wykonywać sumowanie, dlatego musimy je przekształcić. W tej sytuacji najprościej skorzystać z funkcji CZY.LICZBA, wtedy uzyskamy wartości logiczne PRAWDA, gdy "U" udało się znaleźć, a FAŁSZ, gdy był błąd.
Zakładamy, że mamy przykładowe liczby (wyniki gry losowej) i chcemy je wstawić uporządkowane w kolumny im przypisane. Jest to całkiem proste bo nagłówki kolumn to dokładnie te same numery, które mogą się trafić w losowaniu.
W pierwszej kolejności musimy sprawdzić czy dana liczba z nagłówka znajduje się na liście wylosowanych liczb – wystarczy, że skorzystamy z funkcji PODAJ.POZYCJĘ. Jeśli liczba z danego nagłówka kolumny znajduje się na liście wylosowanych liczb, to funkcja PODAJ.POZYCJĘ zwróci tą pozycję (numer pozycji na liście), jeśli jej nie ma, to funkcja PODAJ.POZYCJĘ zwróci błąd (#N/D!).
Na razie nasza formuła (pamiętając o odpowiednich rodzajach odwołań) będzie wyglądać tak:
=PODAJ.POZYCJĘ(B$13;$B3:$AO3;0)
Jeśli liczba zostanie odnaleziona, to chcemy zwrócić liczbę z nagłówka, jeśli nie, to chcemy zostawić pustą komórkę. Przyda się nam do tego na pewno funkcja JEŻELI, ale jeszcze będziemy potrzebowali funkcji CZY.LICZBA, która będzie zwracać wartość PRAWDA, gdy liczba zostanie odnaleziona i FAŁSZ, gdy nie zostaje odnaleziona:
Jak znaleźć elementy, które są na obu listach korzystając z filtrów zaawansowanych?
Elementy które są w obu listach — filtry zaawansowane — porada #181
Tym razem do wyjęcia elementów, które są na obu listach wykorzystamy Filtry zaawansowane. Będziemy jeszcze potrzebowali jeszcze odpowiedniej formuły, która zwraca wartość PRAWDA, gdy elementy są na obu listach lub FAŁSZ, gdy nie ma ich na obu listach. Przyda się do tego funkcja PODAJ.POZYCJĘ i funkcja CZY.LICZBA Funkcja PODAJ.POZYCJĘ będzie zwracać liczby, gdy znajdzie element – jego pozycję na liście, a błąd #N/D! gdy nie znajdzie.
=CZY.LICZBA(PODAJ.POZYCJĘ(C2;$A$2:$A$1500;0))
Uzbrojeni w taką formułę możemy podejść do filtrów zaawansowanych. Gdy je włączysz potrzebujesz zaznaczyć obszar, który będziesz filtrował, łącznie z jego nagłówkiem. Filtr, czyli formułę, którą napisaliśmy, plus jedna pusta komórka nad nią oraz ewentualnie miejsce, gdzie chcesz skopiować wynik filtra zaawansowanego.
Jeśli chciałbyś, żeby filtr zaawansowany zwrócił te wartości, które nie zostały znalezione potrzebujesz funkcji CZY.BRAK, która będzie zwracała prawdę, gdy pojawi się błąd #N/D!
=CZY.BRAK(PODAJ.POZYCJĘ(C2;$A$2:$A$1500;0))
Reszta przebiega analogicznie jak wcześniejsze użycie filtrów zaawansowanych.
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.
Jak policzyć przepracowaną liczbę dni w weekendach?
Jak zliczyć pracujące dni weekendu — widzowie #47
Żeby policzyć ile dni weekendowych jest pracujących, przy założeniu, że mamy daty poszczególnych dni i dni pracujące rozróżniamy po liczbie przepracowanych godzin, możemy wykorzystać proste sprawdzenie jaki to dzień tygodnia za pomocą formuły:
=DZIEŃ.TYG($A$2:$A$31;2)>5
oraz czy w danej komórce wstawiono wartość liczbową
=CZY.LICZBA($B$2:$B$31)
Możemy te warunki połączyć w funkcji SUMA.ILOCZYNÓW tylko potrzebujemy zamienić wartości PRAWDA i FAŁSZ zamienić na jedynki i zero, ponieważ funkcja SUMA.ILOCZYNÓW nie radzi sobie z wartościami logicznymi.
Najprościej to zrobić za pomocą podwójnego minusa.
Tej formuły nie musisz zatwierdzać jako formuły tablicowej (Ctrl + Shift + Enter) ponieważ funkcja SUMA.ILOCZYNÓW radzi sobie z operacjami tablicowymi.
Jeśli nie miałbyś dat przy dniach możesz skorzystać z formuły sprawdzającej tylko dni z weekendów, ale jest bardziej uciążliwe, bo musisz zaznaczać poszczególne weekendy osobno:
=SUMA(–CZY.LICZBA(B3:B4);–CZY.LICZBA(B10:B11))
tą formułę musisz zatwierdzić jako formułę tablicową (Ctrl + Shift + Enter)
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.