Funkcja AGREGACJA w programie Excel
AGREGAT Funkcja w programie excel zwraca agregację z podanej tabeli danych lub list danych, ta funkcja ma również pierwszy argument jako numer funkcji, a dalsze argumenty dotyczą zakresu zestawów danych, należy pamiętać o numerze funkcji, aby wiedzieć, której funkcji użyć .
Składnia
Istnieją dwie składnie formuły AGREGATU:
- Składnia odwołań
= AGREGUJ (nr_funkcji, opcje, odniesienie1, odniesienie2, odniesienie (3),…)
- Składnia tablicy
= AGREGUJ (nr_funkcji, opcje, tablica, (k))
Function_num to liczba oznaczająca określoną funkcję, której chcemy użyć; jest to liczba od 1 do 19
Opcja: jest również wartością liczbową z zakresu od 0 do 7 i określa, które wartości mają być ignorowane podczas obliczeń
Ref1, ref2, ref (3): jest argumentem podczas używania składni odwołania; jest to wartość liczbowa lub wartości, na których chcemy wykonać obliczenia, wymagane są co najmniej dwa argumenty. Pozostałe argumenty są opcjonalne.
Array: to tablica wartości, na których chcemy operować; jest używany w składni tablicowej funkcji AGREGUJ w programie Excel.
K: jest opcjonalnym argumentem i wartością liczbową; jest używany, gdy używana jest funkcja, taka jak LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC lub QUARTILE.EXC w programie Excel.
Przykłady
Przykład 1
Załóżmy, że mamy listę liczb i obliczymy Średnia, Licznik, czyli liczbę komórek zawierających wartość, Counta - liczba komórek, które nie są puste, Maksimum, Minimum, iloczyn i sumę podanych wartości liczbowych . Wartości podano poniżej w tabeli:
Najpierw obliczyć średnią w wierszu 9 dla wszystkich podanych wartości. Dla średniej function_ num wynosi 1
W kolumnie C podane są wszystkie wartości i nie będziemy musieli ignorować żadnych wartości, aby wybrać opcję 4 (nic nie ignoruj)
I wybierając zakres wartości C1: C8 jako tablicę wartości liczbowych
Ponieważ `` k '' jest argumentem opcjonalnym i jest używane, gdy używana jest funkcja taka jak LARGE, SMALL w programie Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC lub QUARTILE.EXC, ale w tym przypadku obliczamy średnią do pomiń wartość k.
Tak więc średnia wartość to
Podobnie, dla zakresu D1: D8 ponownie wybierzemy opcję 4.
Dla zakresu E1: E8 komórka E6 zawiera wartość błędu. Jeśli użyjemy tej samej formuły AGREGAT, pojawi się błąd. Mimo to, gdy używana jest odpowiednia opcja, AGREGACJA w Excelu podaje średnią z pozostałych wartości pomijając wartość błędu w E6.
Aby zignorować wartości błędów, mamy opcję 6.
Podobnie dla zakresu G1: G8 użyjemy opcji 6 (zignoruj wartości błędów)
Teraz, dla zakresu H3, jeśli wstawimy wartość 64 i ukryjemy trzeci wiersz i użyjemy opcji 5, aby zignorować ukryty wiersz, AGREGAT w Excelu, podamy średnią wartość tylko dla widocznych wartości liczbowych.
Wyjście bez ukrywania wiersza 3
Wyjście po ukryciu wiersza 3
Stosując formułę AGREGAT do innych operacji mamy
Przykład - nr 2
Załóżmy, że mamy tabelę przychodów generowanych w różnych terminach z różnych kanałów, jak podano poniżej
Teraz chcemy sprawdzić przychody generowane dla różnych kanałów. Tak więc, kiedy stosujemy funkcję sumy, otrzymujemy całkowity wygenerowany przychód, ale w przypadku, gdy chcemy sprawdzić przychody wygenerowane dla kanału organicznego lub kanału bezpośredniego lub innego, gdy zastosujemy filtry w programie Excel dla tego samego, funkcja suma zawsze poda całkowitą sumę
Chcemy, aby podczas filtrowania kanału otrzymaliśmy sumę widocznych wartości, więc zamiast używać funkcji SUMA, użyjemy funkcji AGREGACJA, aby uzyskać sumę wartości, które są widoczne po zastosowaniu filtru.
Tak więc, zastępując formułę SUMA funkcją AGREGUJ z kodem opcji 5 (ignorując ukryte wiersze i wartości), otrzymaliśmy:
Teraz, gdy zastosujemy filtr do różnych kanałów, pokaże on przychody dla tego kanału tylko wtedy, gdy pozostałe wiersze zostaną ukryte.
Całkowite przychody z kanału bezpośredniego:
Całkowite przychody z bezpłatnego kanału:
Całkowite przychody z płatnego kanału:
Widzimy, że funkcja AGGREGATE oblicza różne wartości Sum dla przychodów generowanych dla różnych kanałów po ich przefiltrowaniu. Tak więc funkcja AGREGUJ może być dynamicznie używana do zastępowania różnych funkcji dla różnych warunków bez użycia formuły warunkowej.
Załóżmy, że dla tej samej tabeli niektóre z naszych wartości przychodów zawierają błąd, teraz musimy zignorować błędy, a jednocześnie, jeśli chcemy zastosować filtr, funkcja AGREGACJA powinna również zignorować ukryte wartości wierszy.
Kiedy używamy opcji 5, otrzymujemy błąd SUMA całkowitych przychodów. Aby zignorować błędy, musimy skorzystać z opcji 6
Korzystając z opcji 6, otrzymujemy sumę pomijającą wartości błędów. Mimo to, gdy zastosujemy filtr, na przykład filtruj według wartości kanału Direct, otrzymamy tę samą sumę ignorując błędy, ale jednocześnie musimy zignorować również wartości ukryte.
Tak więc w tym przypadku użyjemy opcji 7, która ignoruje wartości błędów i jednocześnie ukryte wiersze.
Rzeczy do zapamiętania
- Funkcja AGGREGATE nie rozpoznaje wartości funkcji _ num większej niż 19 lub mniejszej niż 1. Podobnie w przypadku numeru opcji nie identyfikuje wartości większych niż 7 i mniejszych niż 1; jeśli podamy inne wartości, daje #ARG! Błąd
- Zawsze przyjmuje wartość liczbową i zawsze zwraca wartość liczbową jako wyjście
- AGREGAT w Excelu ma ograniczenia; ignoruje tylko ukryte wiersze, ale nie ignoruje ukrytych kolumn.
