Okresowo w Excelu możesz chcieć przeprowadzić analizę w oparciu o określone kryteria. Na przykład, jeśli chciałem zsumować tylko dane z wykresu 1 w oparciu o miesiąc równy styczeń, SUMIF ma mnie objęte. Mógłbym użyć COUNTIF do zliczenia liczby transakcji w styczniu, lub w programie Excel 2007, a następnie użyć funkcji AVERAGEIF do uśrednienia sprzedaży tylko w styczniu. Co jednak zrobić, jeśli chcę określić największą kwotę tylko za miesiąc styczeń? Chociaż nie ma gotowej funkcji, takiej jak MAXIF, mogę łatwo dostosować funkcję MAX, aby to osiągnąć.
Rysunek 1: Przeprowadzimy analizę tych danych w oparciu o określone kryteria.
Najpierw spójrzmy na kilka funkcji, o których wspomniałem. Użytkownicy nieświadomi SUMIF mogą dodawać wartości dla stycznia w oparciu o prostą arytmetykę, np. wzór jak =B2+B6+B10. Formuła ta jest wystarczająco łatwa do napisania dla małych zbiorów danych, ale szybko staje się nieporęczna w miarę wzrostu zbiorów danych. Lepszą alternatywą jest funkcja SUMIF, która posiada trzy argumenty:
Zasięg. Często wyjaśniam to jako gdzie szukać . W tym przypadku określimy komórki A2:A13.
Kryteria. Zwykle wyjaśniam to jako czego szukać , co w tym przypadku może być «styczeń» (należy pamiętać o dołączeniu podwójnych cytatów). Lepszym podejściem jest odwołanie się do komórki, która zawiera słowo January, takiej jak komórka D1.
Sum_Range. Pomyślcie o tym jak co sumować . W tym przypadku określimy komórki B2:B13.
.
Po wpisaniu stycznia w komórce D1, możemy wprowadzić ten wzór w komórce E1:
=SUMIF(A$2:A$13,D1,B$2:B$13)
Zauważysz, że postawiłem znaki dolara przed niektórymi numerami rzędów. Zrobiłem to na wypadek, gdybym zdecydował się wejść do komórki D2 w lutym, D3 w marcu, a D4 w kwietniu. Następnie mogę kliknąć na komórkę E1, a następnie dwukrotnie kliknąć na Wypełnij uchwyt, jak pokazano na rysunku 2, aby skopiować formułę w dół dodatkowych wierszy. Jeśli pominąłbym znaki dolara, to w komórce E2 wzór stałby się =SUMIF(A3:A14,D2,B3:B14). Teraz chciałem, aby D1 zmieniło się na D2, ale nie chciałem, aby A2:A13 lub B2:B13 zmieniło się na wiersze odniesienia od 3 do 14. To oznacza, że mógłbym w końcu pozostawić niektóre dane poza moją analizą.
Rysunek 2: Funkcja SUMIF może sumować dane w oparciu o określone kryteria, którymi w tym przypadku jest styczeń.
Kiedy już zrozumiesz SUMIF, możesz z łatwością używać również COUNTIF. Ponieważ nie ma nic do podsumowania, COUNTIF posiada tylko argument Zakres i Kryteria, który zwróci liczbę razy, gdy styczeń pojawi się w komórkach A2:A13.
AVERAGEIF używa tych samych argumentów co SUMIF, ale zwróci średnie kwoty za styczeń. Powiedzmy jednak, że chcesz najwyższą kwotę tylko za miesiąc styczeń. Excel nie posiada funkcji MAXIF, ale można napisać specjalny rodzaj formuły znany jako formuła tablicowa, która zrobi ten trick zamiast tego.
Po pierwsze, spójrzmy na funkcję MAX, która zwraca największą wartość z zakresu komórek lub serii liczb oddzielonych przecinkami. Na podstawie danych z rysunku 1 wzór =MAX(B2:B13) zwracałby 462, co jest największą liczbą z tego zakresu liczb. Aby uzyskać największe wartości dla miesiąca stycznia, będziemy musieli włączyć do naszego wzoru oświadczenie IF, jak pokazano na rysunku 3:
=MAX(IF(A2:A13=D1,B2:B13))
Po wpisaniu tej formuły należy wpisać Ctrl-Shift-Enter, aby program Excel wiedział, że jest to formuła tablicowa. Jeśli po prostu naciśniesz Enter, w wyniku czego program Excel zwróci zero. Formuły tablicowe pozwalają na wykonywanie skomplikowanych obliczeń, które w innym przypadku nie byłyby możliwe w jednej komórce arkusza.
Kiedy już opanujesz koncepcję, łatwo będzie użyć tej techniki z innymi funkcjami, takimi jak znalezienie najmniejszej wartości dla miesiąca stycznia. Excel nie posiada funkcji MINIF, ale można ją przekształcić w taki sam sposób, jak w przypadku funkcji MAX:
=MIN(IF(A2:A13=D1,B2:B13))
Rysunek 3: Program Excel nie posiada funkcji MAXIF, ale można dołączyć do niej instrukcję IF.
