3 najciekawsze sposoby na wyznaczenie sprzedaży maksymalnej np. przez handlowca lub produktu

Pierwszy „klasyczny” sposób wyznaczenia maksymalnej wartości będzie wykorzystywał funkcję JEŻELI, w drugim sposobie użyjemy trochę mniej znanych funkcji bazodanowych natomiast w trzecim rozwiązaniu skorzystamy z nowych funkcji wprowadzonych dopiero w Excelu 2019.

Wszystkie zadania omówimy na podstawie tych samych przykładowych danych przedstawionych na rysunku nr 1. Dostępny jest plik do pobrania z trzema arkuszami, gdzie każdy arkusz przedstawia inne rozwiązanie tego samego zadania.

Pierwsze rozwiązanie pokażemy w arkuszu MAX.JEŻELI z pliku do pobrania. W pierwszym kroku użyjemy tutaj funkcji JEŻELI. Zapis funkcji będzie wyglądał następująco:

=JEŻELI($C$2:$C$19=F2;$D$2:$D$19)

Pierwszym argumentem funkcji jest Test_logiczny, czyli musimy sprawdzić czy nasze dane spełniają warunek. Dane do testu logicznego to będzie nic innego jak wszyscy sprzedawcy z kolumny C – Sprzedawca. Za pomocą klawisza F4 blokujemy ten zakres bezwzględnie, ponieważ nie chcemy aby się nam przesuwał przy kopiowaniu formuły na wiersze poniżej. Musimy sprawdzić czy zakres z testu logicznego spełnia warunek, czyli jest równy konkretnemu sprzedawcy, który nas w danym momencie interesuje ($C$2:$C$19=F2).

Ta operacja porównania będzie zwracała wyniki logiczne - wartość logiczna PRAWDA lub wartość logiczna FAŁSZ. Jeśli nasz test logiczny zwróci wartość logiczną PRAWDA, to chcemy aby funkcja zwróciła nam wartość z kolumny Sprzedaż dla odpowiedniego sprzedawcy. Tak więc drugim argumentem funkcji (Wartość_jeżeli_prawda) będą dane z kolumny D – Sprzedaż. Zakres ten blokujemy za pomocą klawisza F4 bezwzględnie, ponieważ chcemy aby te wartości się nie zmieniały. Jeżeli test logiczny zwróci nam wartość logiczną FAŁSZ (to nie będzie ten konkretny sprzedawca), to najlepiej aby funkcja zwróciła nam nic. Ciekawostką jest, że jeśli pominiemy trzeci argument funkcji (Wartość_jeżeli_fałsz), to funkcja automatycznie zwróci nam wartość logiczną FAŁSZ dla niespełnionego testu logicznego w funkcji JEŻELI. Aby lepiej zrozumieć temat możemy podejrzeć sobie wyniki formuły w trybie edycji komórki za pomocą klawisza F9 (rys. nr 2).

W wynikach otrzymamy wartość sprzedaży (wartości oznaczone strzałkami na rysunku nr 3) w sytuacji kiedy sprzedawcą była Magda, natomiast dla innych sprzedawców otrzymamy wartość logiczną FAŁSZ (dla skreślonych komórek na rysunku nr 3).

Podejrzenie formuły cofamy przy użyciu skrótu klawiszowego Ctrl+Z. Po podejrzeniu wyników wiemy, że funkcja działa prawidłowo, więc może ją wstawić do funkcji MAX, bo naszym zadaniem było znalezienie wartości maksymalnej dla konkretnego sprzedawcy. Zapis formuły będzie wyglądał następująco:

=MAX(JEŻELI($C$2:$C$19=F2;$D$2:$D$19))

Musimy pamiętać, że jest to formuła tablicowa, więc zatwierdzamy ją skrótem klawiszowym Ctrl+Shift+Enter. Tylko przy takim zatwierdzeniu formuła zwróci nam prawidłowy wynik. Otrzymany wynik przeciągamy na komórkę poniżej (dla sprzedawcy Wiktor) i otrzymamy wartości maksymalne z ich sprzedaży przedstawione na rysunku nr 4.

Teraz rozpatrzymy sytuację, kiedy mamy więcej warunków. Jeden warunek to konkretny sprzedawca, a drugi to województwo. Warunki te musimy połączyć wewnątrz funkcji JEŻELI. Zapis funkcji będzie wyglądał następująco:

=JEŻELI(($C$2:$C$19=$F7)* ($A$2:$A$19=G$6) ;$D$2:$D$19)

Pierwszy argumentem funkcji jest Test_logiczny, czyli sprawdzamy czy wartość z kolumny Sprzedawca jest równa konkretnemu sprzedawcy (identycznie jak w przypadku dla jednego warunku powyżej). Naszą formułę będziemy przeciągać w dół ale tym razem też w bok, więc musimy sobie zablokować naszą daną F7 po kolumnie, czyli $F7, aby pozostać w naszych danych sprzedażowych.

Pierwszy test logiczny wygląda następująco: ($C$2:$C$19=$F7). Teraz musimy go połączyć z drugim testem logicznym na zasadzie „i”. Zrobimy to za pomocą mnożenia. Operacje porównania w działaniach w Excelu są wykonywane na samym końcu, więc nasze operacje porównania musimy wstawić w dodatkowe nawiasy, aby operacje porównania zostały wykonane przed mnożeniem.

W drugim teście logicznym chodzi nam o warunek województwa, czyli zaznaczamy zakres z kolumny A – Województwo i przyrównujemy go do konkretnego województwa, dla którego chcemy otrzymać wynik. Zakres z kolumny Województwo blokujemy bezwzględnie za pomocą klawisza F4, natomiast komórkę do której porównujemy ten zakres blokujemy po wierszach G$6, ponieważ będziemy formułę przesuwać w bok. Drugi test logiczny będzie wyglądał następująco: ($A$2:$A$19=G$6). Dwa testy logiczne pozwalają znaleźć wynik dla konkretnego sprzedawcy i konkretnego województwa jednocześnie.

W sytuacji kiedy oba warunki zostaną spełnione, chcemy aby funkcja zwróciła nam wartość z kolumny D – Sprzedaż (Wartość_jeżeli_prawda), czyli jako drugi argument funkcji wpisuje zakres z tej właśnie kolumny ($D$2:$D$19). Trzeci argument funkcji (Wartość_jeżeli_fałsz) możemy pominąć, ponieważ funkcja przy pominięciu argumentu (w sytuacji, kiedy oba warunki nie zostały spełnione) automatycznie zwróci nam wartość logiczną FAŁSZ.

Możemy podejrzeć sobie wyniki w trybie edycji komórki za pomocą klawisza F9 (rys. nr 5).

W wynikach tych w każdej sytuacji kiedy oba warunki nie zostały spełnione, funkcja zwraca nam wartość logiczną FAŁSZ. Natomiast w sytuacji spełnionych obu warunków dostajemy wynik sprzedaży z kolumny Sprzedaż (komórki zaznaczone zieloną strzałką na rysunku nr 6).

Podejrzenie wyników cofamy za pomocą skrótu klawiszowego Ctrl+Z. W kolejnym kroku musimy naszą funkcję wstawić do funkcji MAX (jak wyżej). Zapis funkcji będzie wyglądał następująco:

=MAX(JEŻELI(($C$2:$C$19=$F7)* ($A$2:$A$19=G$6) ;$D$2:$D$19))

Jest to formuła tablicowa, więc musimy pamiętać o jej zatwierdzeniu za pomocą klawiszy Ctrl+Shift+Enter. Przeciągamy formułę w dół na drugiego sprzedawcę i w bok dla pozostałych województw i otrzymujemy wyniki przedstawione na rysunku nr 7.

Przedstawiliśmy rozwiązanie problemu za pomocą funkcji MAX i testów logicznych z funkcji JEŻELI. Teraz zajmiemy się rozwiązaniem za pomocą funkcji bazodanowej BD.MAX, co zostało przedstawione w arkuszu BD.MAX w pliku do pobrania. Istnieje cała rodzina funkcji bazodanowych przedstawionych na rysunku nr 8, działają one na takiej samej zasadzie, ale obliczają różne wartości.

W naszym rozwiązaniu użyjemy funkcji BD.MAX, która służy do obliczenia wartości maksymalnej po kryteriach, które sobie ustalimy. Zapis funkcji będzie wyglądał następująco:

=BD.MAX($A$1:$D$19; "Sprzedaż";F1:F2)

Pierwszym argumentem funkcji jest Baza_danych, czyli dane zapisane w tabeli. Zaznaczamy cała tabelę, włącznie z nagłówkami, abyśmy mieli dostęp do wszystkich wierszy, które będą dla nas istotne. Zakresu tego nie musimy blokować, będziemy pisać formuły dla pojedynczych kryteriów - nie będziemy kopiować formuł.

Kolejnym argumentem funkcji jest Pole, czyli kolumna, z której chcemy wyciągnąć interesującą nas informację. W naszym przykładzie chcemy otrzymać maksymalną wartość sprzedaży, więc w miejscu argumentu Pole wpisujemy nazwę kolumny „Sprzedaż”.

Trzeci argument funkcji to Kryteria, czyli warunki jakie muszą spełnić nasze dane.

Zasadą do budowania kryteriów funkcji bazodanowych jest używanie nagłówków danych i samych danych, czyli dla naszego przykładu będą to komórki F1 i F2 – nagłówek kryterium i samo kryterium (zaznaczone strzałkami na rysunku nr 9).

Tak przygotowaną formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter i otrzymujemy wynik przedstawiony na rysunku nr 10.

W tej funkcji budujemy warunki na takich zasadach jak do filtrów zaawansowanych. Możemy budować dużo bardziej skomplikowane warunki, składające się z kilku jednocześnie, używając kryteriów „i” oraz „lub”.

Analogicznie wyglądać będzie sytuacja dla drugiego sprzedawcy. Zapis funkcji będzie wyglądał następująco:

=BD.MAX($A$1:$D$19;G4;F4:F5)

W tym przykładzie użyliśmy innego sposobu odwołania w drugim argumencie funkcji (Pole). Możemy się odwołać bezpośrednio do komórki z nagłówkiem kolumny Sprzedaż (D1) lub do dowolnej innej komórki, która taką samą wartość przechowuje (np. G4).

Zapis funkcji będzie się różnił trzecim argumentem funkcji (Kryterium). Pamiętamy, że w Kryterium musimy zaznaczyć nagłówek kryterium i samo kryterium. Formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter i otrzymamy wynik przedstawiony na rysunku nr 11.

Teraz pokażemy działanie funkcji BD.MAX przy dwóch kryteriach. Zapis funkcji będzie wyglądał następująco:

=BD.MAX($A$1:$D$19;4;F7:G8)

Pierwszy argument funkcji (Baza_danych) będzie taki sam jak dla dwóch przypadków powyżej, czyli zakres całej tabeli włącznie z nagłówkami.

Jeszcze innym sposobem odwołania do kolumny Sprzedaż w drugim argumencie funkcji (Pole) jest wpisanie numeru tej kolumny w tabeli licząc wszystkie po kolei od lewej strony. W naszym przykładzie będzie to liczba 4 (numery kolumn zostały przedstawione na rysunku nr 12).

Trzeci argument funkcji (Kryterium) jest bardziej skomplikowany, bo musi zawierać dwa warunki. Wystarczy zaznaczyć zakres z nagłówkami pierwszego oraz drugiego kryterium i odpowiadające im kryteria pod nimi. W naszym przykładzie będzie to zakres F7:G8. Zatwierdzamy formułę skrótem klawiszowym Ctrl+Enter i otrzymujemy wyniki przedstawione na rysunku nr 13.

Analogicznie uzyskamy dla tych warunków wartości minimalne, wystarczy zmienić funkcję BD.MAX na BD.MIN, która ma takie same argumenty funkcji.

Ostatnim sposobem wyznaczenia wartości maksymalnej jaki przedstawimy jest użycie nowej funkcji wprowadzonej dopiero w Excelu 2019 – MAKS.WARUNKÓW. Zapis funkcji będzie wyglądał następująco:

=MAKS.WARUNKÓW($D$2:$D$19;$C$2:$C$19;F2)

Pierwszym argumentem funkcji jest zakres_maks, zakres po którym będziemy szukać wartości maksymalnej. W naszym przykładzie jest to kolumna sprzedażowa – zakres D2:D19. Blokujemy bezwzględnie te dane klawiszem F4.

Drugim argumentem jest Zakres_kryteriów1, czyli kolumna z naszym kryterium – Sprzedawca. Zakres ten blokujemy bezwzględnie za pomocą klawisza F4.

Trzecim argumentem jest Kryteria1, wartość która jest naszym kryterium, w naszym przykładzie jest to wartość w komórce F2.

Na tym kończymy naszą formułę, ponieważ mamy w tym przykładzie tylko jedno kryterium. Funkcja ta jest przygotowana do użycia wielu kryteriów, są one opcjonalne, zapisane w nawiasach kwadratowych. Formułę zatwierdzamy za pomocą tylko przycisku Enter i przeciągamy na komórkę poniżej (dla drugiego sprzedawcy). Otrzymamy wyniki przedstawione na rysunku nr 14.

Na tym etapie możemy napisać formułę funkcji MAKS.WARUNKÓW dla dwóch kryteriów, dla sprzedawcy i województwa. Zapis funkcji będzie wyglądał następująco:

=MAKS.WARUNKÓW($D$2:$D$19;$A$2:$A$19;G$6;$C$2:$C$19;$F7)

Pierwszym argumentem funkcji jest zakres_maks, zakres po którym będziemy szukać wartości maksymalnej. W naszym przykładzie jest to kolumna sprzedażowa – zakres D2:D19 (tak samo jak wyżej dla jednego warunku). Blokujemy bezwzględnie te dane klawiszem F4.

Drugim argumentem jest Zakres_kryteriów1, czyli kolumna z naszym kryterium. Kryteria w funkcji MAKS.WARUNKÓW zawsze łączymy na zasadzie „i” więc nie ma znaczenia w jakiej kolejności je sobie zapiszemy. Pierwszym zakresem jest kolumna z województwami ($A$2:$A$19). Zakres ten blokujemy bezwzględnie za pomocą klawisza F4.

Trzecim argumentem jest Kryteria1, czyli wartość która jest naszym kryterium – nagłówek z nazwą województwa, w naszym przykładzie jest to wartość w komórce G$6. Blokujemy w tej komórce numer wiersza.

Teraz musimy dodać drugie kryterium, czyli podajemy czwarty argument Zakres_kryteriów2. Jest to kolumna ze sprzedawcami ($C$2:$C$19). Blokujemy ten zakres bezwzględnie za pomocą klawisza F4.

Piątym argumentem jest Kryteria2, czyli wartość która jest naszym kryterium – nagłówek z nazwą sprzedawcy, w naszym przykładzie jest to wartość w komórce $F7. Blokujemy tą komórkę po kolumnie.

Funkcja MAKS.WARUNKÓW działa analogicznie do funkcji JEŻELI, czyli bierze pod uwagę tylko te wartości które spełniają oba warunki jednocześnie a następnie wyciąga z nich wartość maksymalną (dla sprzedawcy Magda i województwa mazowieckiego wartości brane pod uwagę zaznaczone na rysunku nr 15 zielonymi prostokątami).

Zatwierdzamy formułę i przeciągamy na komórkę na dole i na komórki z boku. Otrzymamy wyniki przedstawione na rysunku nr 16.

We wszystkich omówionych przykładach i sposobach rozwiązania otrzymujemy takie same wyniki. Wybór rozwiązania zależy od wersji Excela, którą dysponujemy oraz tego, które rozwiązanie jest dla nas najwygodniejsze.

Polecamy także:

• Jak prezentować podsumowania w Tabelach Przestawnych?
• Kolorowanie naprzemienne – jak poprawić przejrzystość danych?