Obliczanie największych wartości sprzedaży. Najlepsze triki

Z lekcji dowiesz się:

• Do czego służy funkcja MAX.K?

• Jak używać formatowania warunkowego?

• Jaką wartość zwraca funkcja PODAJ.POZYCJĘ?

• Jak użyć argumentu funkcji TABLICA?

Przykład 1

Zadaniem, które zamierzamy rozwiązać - korzystając z podanego zestawu danych - jest zsumowanie trzech największych wartości ze sprzedaży w każdym wierszu. Zagadnienie to omówimy na podstawie przykładowych danych z arkusza Top3 w pliku do pobrania, przedstawionych na rysunku 1.

(Rys. 1)

Najłatwiej można to policzyć za pomocą funkcji MAX.K, która zwraca k-tą największą wartość. Zapis funkcji będzie wyglądał następująco:

=MAX.K(B2:H2;1)+MAX.K(B2:H2;2)+ MAX.K(B2:H2;3)

Pierwszym argumentem funkcji jest tablica, czyli zakres danych, z których chcemy wyciągnąć największe wartości, w naszym przykładzie to zakres B2:H2.

Drugi argument funkcji to k, czyli kolejny numer największego wyniku (zamierzamy otrzymać trzy największe wyniki: pierwszy, drugi i trzeci).

Aby otrzymać sumę trzech największych wyników należy dodać do siebie trzy funkcje MAX.K dla odpowiednio pierwszego, drugiego i trzeciego najwyższego wyniku sprzedaży.

Po zatwierdzeniu formuły otrzymamy wynik przedstawiony na rysunku 2.

(Rys. 2)

Zdarza się, że musimy dodać do siebie dużo większą ilość maksymalnych wyników. Zapis formuły jak w przykładzie powyżej byłby bardzo długi. Aby skrócić taki zapis możemy się posłużyć tablicą w argumencie k funkcji MAX.K. Wystarczy, że w miejscu drugiego argumentu wpiszemy w nawiasach klamrowych wszystkie trzy wartości rozdzielone znakiem średnika. Zapis będzie wyglądał następująco:

=MAX.K(B2:H2;{1;2;3})

Tak zapisaną formułę można podejrzeć w trybie edycji komórki za pomocą klawisza F9. Otrzymamy wyniki przedstawione na rysunku 3.

(Rys. 3)

Excel zwróci nam trzy maksymalne wartości. Cofnięcie podglądu następuje za pomocą skrótu klawiszowego Ctrl+Z.

Pojawia się tutaj problem, mianowicie Excel nie jest w stanie zamieścić tylu wyników w jednej komórce, ale wystarczy, że otrzymane wyniki wstawimy do funkcji SUMA, żeby uzyskać interesujący nas efekt. Zapis formuły będzie wyglądał następująco:

=SUMA(MAX.K(B2:H2;{1;2;3}))

Dzięki temu, że argument k jest hardkodowaną tablicą to nie trzeba jej zatwierdzać jak formuły tablicowej. Wystarczy zatwierdzenie w standardowy sposób, a następnie skopiowanie jej na komórki poniżej. Otrzymamy wtedy wyniki przedstawione na rysunku 4.

(Rys. 4)

Innym sposobem rozwiązania tego zadania jest użycie funkcji SEKWENCJA, która jest dostępna od najnowszego update’a dla Office 365. Istotny będzie jednak tylko pierwszy argument funkcji. Jej zapis będzie wyglądał następująco:

=SEKWENCJA (3)

Nowy sposób działania formuł tablicowych sprawi, że funkcja SEKWENCJA zwróci tablicę danych. Kiedy podejrzymy wyniki funkcji w trybie edycji komórki za pomocą klawisza F9 otrzymamy wyniki przedstawione na rysunku 5.

(Rys. 5)

Nowy sposób wyświetlania danych w formułach tablicowych sprawia, że wyniki funkcji SEKWENCJA trafiają do wierszy poniżej – otrzymujemy tablicę przedstawioną na rysunku 6.

(Rys. 6)

Wystarczy, że wstawimy funkcję SEKWENCJA zamiast drugiego argumentu funkcji MAX.K, czyli zamiast hardkodowanej tablicy. W funkcji SEKWENCJA możemy w łatwy sposób ustawić odpowiednią liczbę interesujących nas danych, niezależnie czy jest 5 czy 10 największych wartości. Jest to dużo łatwiejsze niż hardkodowanie tablicy na stałe.

Zapis funkcji MAX.K z użyciem funkcji SEKWENCJA będzie wyglądał następująco:

=MAX.K(B2:H2;SEKWENCJA(3))

Kiedy podejrzymy zapis funkcji w trybie edycji komórki za pomocą klawisza F9, otrzymamy wyniki przedstawione na rysunku 7.

(Rys. 7)

Przed zatwierdzeniem formuły należy cofnąć podgląd wyników za pomocą skrótu klawiszowego Ctrl+Z oraz dołożyć funkcję SUMA. Spowoduje to zsumowanie 3 największych wartości sprzedaży. Formuła będzie wygląda

następująco:

=SUMA(MAX.K(B2:H2;SEKWENCJA(3)))

Następnie zatwierdzamy formułę i kopiujemy na wiersze poniżej. Otrzymujemy wyniki przedstawione na rysunku 8.

(Rys. 8)

Podsumowując - otrzymaliśmy dokładnie takie same wyniki. Pierwszy rezultat za pomocą wpisania ręcznie tablicy w argument k w funkcji MAX.K, natomiast drugi z wykorzystaniem funkcji SEKWENCJA.

Należy pamiętać, że drugi sposób na razie jest dostępny tylko dla posiadaczy najnowszej subskrypcji Microsoft Office 365.

Przykład 2

Kolejnym zadaniem będzie zaznaczenie maksymalnej wartości w konkretnym wierszu. Przykładowe dane do tego zagadnienia przedstawione zostały na rysunku 9.

(Rys. 9)

Najpierw należy stworzyć odpowiednią formułę żeby następnie wkleić ją do formatowania warunkowego. Polecamy użycie funkcji MAX. Argumentami tej funkcji są liczby z zakresu, który zaznaczymy, czyli w danym przypadku obszar jednego wiersza. Formuła będzie wyglądać następująco:

=MAX($B2:$H2)

Trzeba pamiętać, że formuła ta używana będzie do formatowania warunkowego, czyli będzie ona przeciągana w dół – na wiersze poniżej. Natomiast zakresy nie mogą przesuwać się na boki (patrz rys. 10). Aby formuła działała prawidłowo trzeba zablokować bezwzględnie kolumny (po to, żeby zostać w obszarze wiersza), a pozostawić swobodne wiersze. Zakresy blokujemy za pomocą klawisza F4.

(Rys. 10)

W kolejnym etapie planujemy sprawdzić czy ta maksymalna obliczona wartość równa się konkretnej komórce. Zapis formuły będzie wyglądał następująco:

=MAX($B2:$H2)=B2

Komórkę do której porównamy wartość maksymalną pozostawiamy nie zablokowaną ponieważ ma się ona przesuwać po całym zakresie danych (w dół i na boki). Po zatwierdzeniu formuły skrótem klawiszowych Ctrl+Enter otrzymujemy wynik w postaci wartości logicznej FAŁSZ. Po przeciągnięciu formuły na wiersze poniżej, w każdym przypadku otrzymujemy taki sam wynik - czyli wartości sprzedaży w Warszawie nie są maksymalne (rys. 11).

(Rys. 11)

Wystarczy, że przeciągniemy formułę również w bok i pojawią nam się inne wartości, czyli wartości logiczne PRAWDA - dla spełnionego warunku (rys. 12).

(Rys. 12)

Cofamy rozszerzanie wyników na bok skrótem klawiszowym Ctrl+Z. W trybie edycji komórki kopiujemy formułę za pomocą skrótu klawiszowego Ctrl+C, następnie wychodzimy z trybu edycji komórki. Zaznaczamy zakres, który zamierzamy sformatować warunkowo (B2:H13) i rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rysunku 13) z karty Narzędzia główne, a następnie z listy rozwijanej wybieramy polecenie Nowa reguła (punkt 3 na rysunku 13).

(Rys. 13)

Otworzy się okno Nowej reguły formatowania, gdzie ustawiamy parametry nowej reguły. W polu Wybierz typ reguły wybieramy polecenie Użyj formuły do określenia komórek, które należy sformatować. W polu Edytuj opis reguły wklejamy wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Następnie klikamy przycisk Formatuj, po czym otworzy się okno Formatowania komórek, gdzie w karcie Wypełnienie wybieramy kolor wypełnienia komórek i zatwierdzamy przyciskiem OK (rys. 14).

(Rys. 14)

Tak przygotowane parametry Formatowania warunkowego zatwierdzamy przyciskiem OK (rys. 15).

(Rys. 15)

Otrzymamy sformatowane dane przedstawione na rysunku 16.

(Rys. 16)

Otrzymaliśmy podświetlone w wybranym przez nas kolorze maksymalne wartości dla każdego wiersza. Ważne jest, że jeśli zdarzy się duplikat w wierszu (taka sama maksymalna wartość w kilku komórkach), to Excel podświetli wszystkie maksymalne wyniki w tym wierszu.

Przykład 3

Zadaniem zaplanowanym w tym zadaniu jest nauczenie się jak podświetlić w Excelu tylko pierwszą maksymalną wartość w danym wierszu. Zadanie to wykonamy na przykładowych danych z rysunku 17.

(Rys. 17)

W podanych danych znajdują się dwa wiersze z duplikatami. Dążymy do tego, żeby Excel podświetlił tylko pierwszą wartość maksymalną, czyli w tym przykładzie tą z lewej strony. Druga wartość (duplikat) ma pozostać niepodświetlona. Potrzebna będzie do tego funkcja MAX, której zapis wygląda następująco:

=MAX($B2:$H13)

Formuła ta zostanie użyta do formatowania warunkowego, wpisujemy ją w pierwszej komórce danych. Formuła ta będzie się przesuwać w dół i w bok (rys. 18), więc należy odpowiednio zablokować zakres danych. Blokujemy kolumny za pomocą klawisza F4.

(Rys. 18)

Tak przygotowaną formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter i przeciągamy na wiersze poniżej. Otrzymamy wyniki przedstawione na rysunku 19.

(Rys. 19)

Otrzymaliśmy podświetlone zduplikowane wartości, więc w kolejnym etapie musimy odnaleźć pozycję - pierwszej maksymalnej wartości. Użyjemy do tego funkcji PODAJ.POZYCJĘ. Zapis funkcji będzie wyglądał następująco:

=PODAJ.POZYCJĘ(MAX($B2:$H13); $A2:$H2;0)

Pierwszym argumentem funkcji PODAJ.POZYCJĘ jest szukana_wartość, czyli wartość maksymalna uzyskana za pomocą funkcji MAX.

Drugi argument funkcji to przeszukiwana_tab, czyli zakres w którym funkcja ma szukać wartości. Aby ułatwić pracę, zaznaczamy zakres wiersza od początku arkusza, razem z nazwą miesiąca. Zaznaczony zakres należy zablokować po kolumnach za pomocą klawisza F4.

Trzeci argument funkcji to typ_porównania, w tym przykładzie wybieramy dopasowanie dokładne, czyli wpisujemy w formułę wartość 0 (rys. 20).

(Rys. 20)

Funkcja PODAJ.POZYCJĘ zwraca pierwszą znalezioną wartość. Tak przygotowaną formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter i przeciągamy na wiersze poniżej. Następnie musimy jeszcze zmienić formatowanie na liczbowe ogólne za pomocą skrótu klawiszowego Ctrl+Shift+~ (tylda). Otrzymamy dane przedstawione na rysunku 21.

(Rys. 21)

W wynikach formuły otrzymujemy numer kolumny, w której znajduje się pierwsza maksymalna wartość.

W kolejnym etapie należy użyć funkcji NR.KOLUMNY, która zwraca numer kolumny z komórki. Zapis funkcji będzie wyglądał następująco:

=PODAJ.POZYCJĘ(MAX($B2:$H13); $A2:$H2;0)=NR.KOLUMNY(B2)

Obliczenia zaczynamy od komórki B2. Zatwierdzamy formułę i przeciągamy w bok, aby sprawdzić poprawność wyników (rys. 22).

(Rys. 22)

W wynikach pojawiają się wartości logiczne PRAWDA i właśnie komórki odpowiadające tym wartościom - będziemy chcieli zaznaczyć za pomocą formatowania warunkowego.

Utworzoną formułę kopiujemy w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C, wychodzimy z trybu edycji komórki, a następnie zaznaczamy zakres danych, które sformatujemy warunkowo.

Rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rysunku 23) z karty Narzędzia główne, a następnie a listy rozwijanej wybieramy polecenie Nowa reguła (punkt 3 na rysunku 23).

(Rys. 23)

Otworzy się okno Nowej reguły formatowania, gdzie ustawiamy parametry nowej reguły. W polu Wybierz typ reguły (punkt 1 na rysunku 24) wybieramy polecenie Użyj formuły do określenia komórek, które należy sformatować. W polu Edytuj opis reguły (punkt 2 na rysunku 24) wklejamy wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Następnie klikamy przycisk Formatuj (punkt 3 na rysunku 24). Otworzy się okno Formatowania komórek, gdzie w karcie Wypełnienie - wybieramy kolor wypełnienia komórek i zatwierdzamy przyciskiem OK (rys. 25).

(Rys. 24)

(Rys. 25)

Tak przygotowane parametry Formatowania warunkowego zatwierdzamy przyciskiem OK.

Otrzymamy odpowiednio sformatowane dane przedstawione rysunku 26.

(Rys. 26)

Dzięki użyciu funkcji MAX, PODAJ.POZYCJĘ oraz NR.KOLUMNY otrzymaliśmy dane z zaznaczoną tylko pierwszą wartością maksymalną w każdym wierszu (duplikaty zostały pominięte).

Polecamy także:

• Kolorowanie naprzemienne – jak poprawić przejrzystość danych?

• Wyszukaj.pionowo – jak znajdować dane w tabeli?