Zobacz kurs wideo Mistrz Excela
[ Ссылка ]

Zobacz fragment książki + 30 filmów z nim powiązanych [ Ссылка ]

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.

=ZNAJDŹ(D2;$A$2:$A$20) ---
{#ARG!;1;#ARG!;#ARG!;#ARG!;18;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!}

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.

=CZY.LICZBA(ZNAJDŹ(D2;$A$2:$A$20)) ---
{FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;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}

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.

=$B$2:$B$20/CZY.LICZBA(ZNAJDŹ(D2;$A$2:$A$20)) ---
{#DZIEL/0!;80;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!;75;#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!}

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:

=AGREGUJ(15;6;$B$2:$B$20/CZY.LICZBA(ZNAJDŹ(D2;$A$2:$A$20));1)

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

=MIN.K(JEŻELI(CZY.LICZBA(ZNAJDŹ(D2;$A$2:$A$20));$B$2:$B$20);1)

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.

Pliki do pobrania na stronie:
[ Ссылка ]

свернуть