Funkcja AGREGACJA w programie Excel
AGREGAT Funkcja w programie excel zwraca agregat 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])
Numer_funkcji to liczba oznaczająca konkretną funkcję, której chcemy użyć, jest to liczba z zakresu 1-19
Opcja: jest to również wartość liczbowa 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 referencyjnej, jest to wartość liczbowa lub wartości, na których chcemy wykonać obliczenia, wymagane są co najmniej dwa argumenty, pozostałe argumenty są opcjonalne.
Tablica: to tablica wartości, na których chcemy wykonać operację, jest używana w składni tablicowej funkcji AGREGUJ w programie Excel
K: jest argumentem opcjonalnym i jest 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
Możesz pobrać ten szablon funkcji AGREGATOWA Excel tutaj - szablon funkcji AGREGATYPrzykł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 w tabeli poniżej:
Najpierw obliczyć średnią w wierszu 9 dla wszystkich podanych wartości. Dla średniej function_ num to
W kolumnie C podane są wszystkie wartości i nie będziemy musieli ignorować żadnych wartości, więc wybierzemy 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ą, więc pominie 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 AGREGATY, otrzymamy błąd, ale gdy zostanie użyta odpowiednia opcja, AGREGACJA w Excelu podaje średnią z pozostałych wartości pomijając błąd wartość 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 umieścimy wartość 64 i ukryjemy trzeci wiersz i użyjemy opcji 5, aby zignorować ukryty wiersz, AGREGACJA 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łę AGREGATU 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 sumy zawsze będzie podać całkowitą sumę
Chcemy, aby filtrując kanał, otrzymaliśmy sumę widocznych wartości, więc zamiast używać funkcji SUMA, użyjemy funkcji AGREGUJ, aby uzyskać sumę wartości, które są widoczne, gdy filtr jest stosowany.
Tak więc, zastępując formułę SUMA funkcją AGREGUJ z kodem opcji 5 (ignorując ukryte wiersze i wartości),
Teraz, gdy zastosujemy filtr do różnych kanałów, pokaże on przychody z 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 więc, ż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 korzystania ze wzoru warunkowego.
Załóżmy, że dla tego samego kanału tabeli i przychodów 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 SUMY całkowitych przychodów, teraz aby zignorować błędy, musimy użyć opcji 6
Korzystając z opcji 6 otrzymujemy sumę ignorującą wartości błędów, ale kiedy zastosujemy filtr, na przykład filtrujemy po 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 funkcji _ num o wartości większej niż 19 lub mniejszej niż 1 i podobnie w przypadku numeru opcji nie rozpoznaje wartości większych niż 7 i mniejszych niż 1, jeśli podamy inne wartości, daje #VALUE ! 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.
