Jak korzystać z funkcji AGREGACJA programu Excel -

Podsumowanie

Funkcja Excel AGGREGATE zwraca zagregowane obliczenia, takie jak ŚREDNIA, LICZBA, MAKS itd., Opcjonalnie ignorując ukryte wiersze i błędy. Łącznie dostępnych jest 19 operacji, określonych przez numer funkcji w pierwszym argumencie (patrz tabela opcji).

Cel, powód

Zwróć zagregowane obliczenia

Wartość zwracana

Zależy od określonej funkcji

Składnia

= AGREGUJ (nr_funkcji; opcje; odniesienie1; odniesienie2)

Argumenty

  • numer_funkcji - operacja do wykonania (1-19).
  • opcje - wartości do zignorowania (0-7).
  • ref1 - pierwszy argument.
  • ref2 - drugi argument (k).

Wersja

Excel 2010

Uwagi dotyczące użytkowania

Funkcja AGREGUJ zwraca wynik obliczeń zagregowanych, takich jak ŚREDNIA, LICZBA, MAKS., MIN. Dostępnych jest łącznie 19 operacji, a operacja do wykonania jest określana jako liczba, która pojawia się jako pierwszy argument funkcji. Drugi argument, opcje, kontroluje, jak AGREGUJ obsługuje błędy i wartości w ukrytych wierszach. Zobacz tabele poniżej ze wszystkimi dostępnymi opcjami.

Przykład 1

Aby zwrócić wartość MAX z zakresu A1: A10, ignorując zarówno błędy, jak i ukryte wiersze, podaj 4 dla numeru funkcji i 7 dla opcji:

=AGGREGATE(4,7,A1:A10) // max value

Aby zwrócić wartość MIN z tymi samymi opcjami, zmień numer funkcji na 5:

=AGGREGATE(5,7,A1:A10) // min value

Przykład nr 2

W powyższym przykładzie formuła w D5 to:

=AGGREGATE(4,6,values)

gdzie „wartości” to nazwany zakres B5: B14. Numer funkcji to 4, co oznacza MAX. Opcje są dostarczane jako 6, aby ignorować tylko błędy.

Przykład # 3 - n-ty co do wielkości

Formuły w D8: D10 pokazują, jak zwracać „n-tą największą” wartość:

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Numer funkcji w tym miejscu to 14, która uruchamia funkcję LARGE. Ponieważ funkcja LARGE wymaga argumentu k, pojawia się ona jako ostatni argument w trzech powyższych formułach.

Przykład # 4 - operacja tablicowa

To, co sprawia, że ​​funkcja AGREGUJ jest szczególnie użyteczna w przypadku bardziej złożonych formuł, polega na tym, że obsługuje ona tablice natywnie, gdy numer funkcji wynosi 14–19. Na przykład, aby znaleźć wartość MAX w poniedziałki z danymi zawierającymi daty i wartości, możesz użyć funkcji AGREGUJ w ten sposób:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Tutaj podajemy 14 dla funkcji (DUŻE) i 6 dla opcji (ignoruj ​​błędy). Następnie tworzymy logiczne wyrażenie, używając funkcji TEKST, aby sprawdzić wszystkie daty w poniedziałki. Wynikiem tej operacji jest tablica wartości PRAWDA / FAŁSZ, które stają się mianownikiem pierwotnych wartości. FALSE zwraca zero i rzuca # DIV / 0! błąd. PRAWDA ma wartość 1 i zwraca oryginalną wartość. Ostateczna tablica wartości i błędów działa jak filtr. AGREGUJ ignoruje wszystkie błędy i zwraca największą (maksymalną) z pozostałych wartości. Bardziej kompletny przykład tutaj.

Numery funkcji

Poniższa tabela zawiera listę numerów funkcji dostępnych dla funkcji AGREGATUJ wraz z nazwą powiązanej funkcji. Trzecia kolumna, Ref2, wskazuje wartość drugiego argumentu oczekiwaną w razie potrzeby.

Funkcjonować Funkcjonować Ref2
1 ŚREDNI
2 LICZYĆ
3 COUNTA
4 MAX
5 MIN
6 PRODUKT
7 STDEV.S
8 STDEV.P
9 SUMA
10 VAR.S
11 VAR.P
12 MEDIANA
13 MODE.SNGL
14 DUŻY k
15 MAŁY k
16 PERCENTYL.ODWR k
17 KWARTYL.ODW kwarta
18 PERCENTILE.EXC k
19 KWARTYL.PRZEDZ kwarta

Opcje zachowania

Funkcja AGREGUJ ma wiele opcji ignorowania błędów, ukrytych wierszy i innych funkcji. Możliwe wartości to 0-7, jak pokazano w poniższej tabeli.

Opcja Zachowanie
0 Ignoruj ​​funkcje SUMY CZĘŚCIOWE i AGREGUJ
1 Ignoruj ​​ukryte wiersze, funkcje SUMY CZĘŚCIOWE i AGREGUJ
2 Ignoruj ​​wartości błędów, funkcje SUMY CZĘŚCIOWE i AGREGUJ
3 Ignoruj ​​ukryte wiersze, wartości błędów, funkcje SUMY CZĘŚCIOWE i AGREGUJ
4 Nic nie ignoruj
5 Ignoruj ​​ukryte wiersze
6 Ignoruj ​​wartości błędów
7 Ignoruj ​​ukryte wiersze i wartości błędów

Uwagi

  • AGREGATUJ zwraca #ARG! błąd, jeśli wymagany jest drugi argument funkcji, ale nie został podany.
  • Odniesienia 3D nie działają z AGREGATEM.
  • Funkcja AGREGATOWA służy do projektowania zakresów pionowych, a nie poziomych.

Interesujące artykuły...