Beispielzusammenfassung von acht Verwendungen der AGGREGATE-Funktion in Excel

Dieser Artikel vermittelt Ihnen relevantes Wissen über Excel und stellt hauptsächlich den relevanten Inhalt der AGGREGATE-Funktion vor. Die Verwendung dieser Funktion ähnelt der ZWISCHENSUMME-Funktion, ist jedoch leistungsfähiger als die ZWISCHENSUMME-Funktion Ich hoffe, es hilft allen.

Verwandte Lernempfehlungen: Excel-Tutorial

Die AGGREGATE-Funktion ähnelt der ZWISCHENSUMME-Funktion, ist jedoch leistungsfähiger als die ZWISCHENSUMME-Funktion. Sie kann nicht nur 19 Funktionen wie SUMME, MITTELWERT, ZÄHLEN, GROSS implementieren , MAX usw. funktionieren und können auch ausgeblendete Zeilen, Fehlerwerte, Nullwerte usw. ignorieren und unterstützen konstante Arrays.

Der erste Parameter dieser Funktion ist eine Zahl zwischen 1 und 19, mit der die zu verwendende Aggregationsmethode angegeben wird:

Der zweite Parameter ist eine Zahl zwischen 0 und 7, die im Berechnungsbereich Welche Arten von angegeben wird Werte sollten ignoriert werden:

Lassen Sie uns als Nächstes über einige typische Verwendungszwecke dieser Funktion sprechen:

1. Mehrere diskontinuierliche Bereiche ignorieren die Fehlerwerte und summieren sie direkt

Das ist die Stärke dieser Funktion Parameter 2 kann Parameter angeben, um die Fehlerwerte zu ignorieren und sie direkt zu zählen. Wie in der Abbildung unten gezeigt, müssen wir diese diskontinuierlichen Bereiche summieren.

Die Formel lautet:

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2、筛选状态下忽略错误值

如下图，在筛选后的数据区域中包含有错误值，如何对可见单元格进行统计呢？

公式为：

=AGGREGATE(9,7,B6:B18)

第一参数使用9，表示求和，第二参数使用7，表示忽略隐藏行和错误值。

3、一个公式解决多种统计效果

如下图，A3:B14单元格区域中是筛选后的的数据，要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。

只要一个公式就够了：

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

注意是区域数组公式，先选取c17:d22区域，然后在编辑栏写上公式，最后按ctrl+shift+enter三键录入。

4、向上求和你们都会，哪怕是筛选下的，向下呢？

=AGGREGATE(9,3,A4:A)*2-AGGREGATE(9,7,A4:A)

除了向下求和的方向外还有隐藏和错误值，这是subtotal+sum(if)都无法实现的统计效果

(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

5、这条开始才是重点-条件极值统计

这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果，而且不需要三键。

如下图，要计算1车间对应的最小值，公式为：

=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

公式中的第一参数使用15，表示使用SMALL函数，第二参数使用6，表示忽略错误值。要统计的区域是B4:B15/(A4:A15=”1车间”)

A4:A15=”1车间”部分，先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于”1车间”，就返回逻辑值TRUE，否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组，结果为：

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}

最后，AGGREGATE函数忽略里面的错误值，得到第一个最小值。

如果要计算1车间对应的第三个最小值，只需要将最后的1，变成3就好了。

如果要计算1车间对应的最大值，咱们可以修改一下第一参数，使用14，就是第k个最大值了。

6、 一对多查询

如果想要一对多查询，很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实，用aggregate函数替代也是能实现的。

如下图，要提取出二车间的所有工号，可以使用以下公式：

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(:)/(A:A=D),ROW(A1))),"")

2. Fehlerwerte im Filterstatus ignorieren🎜🎜Wie unten gezeigt, die gefilterte Daten Der Bereich enthält Fehlerwerte. Wie werden die sichtbaren Zellen gezählt? 🎜🎜Die Formel lautet: 🎜🎜=AGGREGATE(9,7,B6:B18)🎜🎜🎜🎜Der erste Parameter verwendet 9, was Summieren bedeutet, und der zweite Parameter verwendet 7, was bedeutet, dass ausgeblendete Zeilen und Fehlerwerte ignoriert werden . 🎜🎜3. Eine Formel löst mehrere statistische Effekte🎜🎜Wie unten gezeigt, liegen die gefilterten Daten im Zellbereich A3:B14. Das Maximum, das Minimum, der Durchschnitt, die Summe, die Anzahl und die Summe des sichtbaren Bereichs müssen gezählt werden bzw. Median. 🎜🎜Nur eine Formel reicht aus: 🎜🎜=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)🎜🎜🎜🎜Beachten Sie, dass es sich um ein regionales Array handelt Wählen Sie zunächst den Bereich c17:d22 aus, schreiben Sie dann die Formel in die Bearbeitungsleiste und drücken Sie abschließend Strg+Umschalt+Eingabetaste. 🎜🎜4. Ihr wisst alle, wie man nach oben resümiert, auch wenn es gefiltert ist, was ist mit nach unten? 🎜🎜=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)🎜🎜🎜🎜Zusätzlich zur Abwärtssummierungsrichtung gibt es auch versteckte Werte und Fehlerwerte. Dies ist ein statistischer Effekt, der nicht durch Zwischensumme+Summe(wenn)🎜🎜 erreicht werden kann (Die Eingabemethode besteht darin, den Bereich auszuwählen, um den Nullwert zu positionieren, und dann die Formel in die Bearbeitungsleiste zu schreiben, Strg+Eingabetaste, um Stapel auszufüllen) 🎜🎜5. Der Anfang ist der entscheidende Punkt – bedingte Extremwertstatistik 🎜🎜Diese Funktion hat die statistischen Auswirkungen von Maxifs- und Minifs-Funktionen realisiert, die 2016 erst vor fünf Jahren verfügbar waren, und erfordert keine drei Schlüssel. 🎜🎜Wie unten gezeigt, lautet die Formel zur Berechnung des Mindestwerts für 1 Werkstatt: 🎜🎜=AGGREGATE(15,6,B4:B15/(A4:A15="1 Werkstatt"),1) 🎜🎜<img src="https://img.php.cn/upload/image/467/651/536/1652758471757546.jpg" title="1652758471757546.jpg" alt="Beispielzusammenfassung von acht Verwendungen der AGGREGATE-Funktion in Excel"> 🎜🎜Formel Verwenden Sie 15 für den ersten Parameter, was bedeutet, dass Sie die Funktion SMALL verwenden, und verwenden Sie 6 für den zweiten Parameter, was bedeutet, dass Sie den Fehlerwert ignorieren. Der zu zählende Bereich ist der Abschnitt B4:B15/(A4:A15="1 Werkstatt")🎜🎜A4:A15="1 Werkstatt" Vergleichen Sie zunächst, ob die Werkstätten in Spalte A den angegebenen Bedingungen entsprechen. Wenn der Zellbereich A4:A15 gleich „1 Werkstatt“ ist, wird der logische Wert TRUE zurückgegeben, andernfalls wird der logische Wert FALSE zurückgegeben. Teilen Sie dann dieses Speicherarray durch B4:B15, das Ergebnis ist: 🎜🎜{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;# DIV/ 0!;19;47}🎜🎜Abschließend ignoriert die AGGREGATE-Funktion die darin enthaltenen Fehlerwerte und ruft den ersten Minimalwert ab. 🎜🎜Wenn Sie den dritten Mindestwert entsprechend Workshop 1 berechnen möchten, müssen Sie nur die letzten 1 bis 3 ändern. 🎜🎜Wenn wir den Maximalwert entsprechend Workshop 1 berechnen möchten, können wir den ersten Parameter ändern und 14 verwenden, was den k-ten Maximalwert darstellt. 🎜🎜6. Eins-zu-viele-Abfrage🎜🎜Wenn Sie eine Eins-zu-viele-Abfrage wünschen, denken viele Leute an die Drei-Tasten-Kombination aus INDEX+SAMLL+IF-Funktion. Tatsächlich ist es auch möglich, sie durch eine Aggregatfunktion zu ersetzen. 🎜🎜Wie unten gezeigt, können Sie zum Extrahieren aller Arbeitsnummern des zweiten Workshops die folgende Formel verwenden: 🎜🎜<code>=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12) /(A$3 :A$12=D$3),ROW(A1))),"")🎜

Die Idee dieser Formel ist im Grunde die gleiche wie die fünfte Formel.

7. Zählen Sie den Maximalwert in derselben Zelle.

Wie im Bild unten gezeigt, wird der Bewertungsstatus mehrerer Personen in Spalte B in dieselbe Zelle geschrieben und der Maximalwert muss gezählt werden. Die Formel lautet:

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)=AGGREGATE(14,6,--MID(B4,ROW(:),COLUMN(A:AZ)),1)

公式中的MID(B4,ROW(:),COLUMN(A:AZ))部分，使用MID函数，依次从第1~50个字符处开始，各提取长度为1~50的字符串，得到一个巨长的内存数组。再使用两个负号，把内存数组中的文本变成错误值，数值仍然是其本身的值。

最后使用AGGREGATE函数，忽略内存数组中的错误值，计算出其中的第一个最小值。

8、同时统计指定条件的最大最小值

如下图所示，要同时统计1车间对应的最大和最小值。

先同时选中F4:G4单元格，编辑栏输入以下公式，按Ctrl+Shift+回车。

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

MID(B4,ROW() in die Formel $1:$50), COLUMN(A:AZ)), verwenden Sie die MID-Funktion, um Zeichenfolgen mit einer Länge von 1 bis 50, beginnend beim 1. bis 50. Zeichen, zu extrahieren und ein riesiges Speicherarray zu erhalten. Verwenden Sie dann zwei negative Vorzeichen, um den Text im Speicherarray in einen Fehlerwert umzuwandeln, und der Wert ist immer noch sein eigener Wert.

Verwenden Sie abschließend die AGGREGATE-Funktion, um die Fehlerwerte im Speicherarray zu ignorieren und den ersten Mindestwert unter ihnen zu berechnen.

8. Zählen Sie gleichzeitig die Maximal- und Minimalwerte der angegebenen Bedingungen

Wie in der Abbildung unten gezeigt, müssen die Maximal- und Minimalwerte für 1 Werkstatt gleichzeitig gezählt werden.

Wählen Sie zunächst gleichzeitig die Zellen F4:G4 aus, geben Sie die folgende Formel in die Bearbeitungsleiste ein und drücken Sie Strg+Umschalt+Eingabetaste. =AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

🎜🎜🎜🎜AGGREGATE verwendet das konstante Array {16,15 als erster Parameter}, der die Berechnungsregeln für die Verwendung des Maximalwerts bzw. des Minimalwerts angibt. 🎜🎜Das Endergebnis ist auch ein Speicherarray, daher müssen zwei Zellen gleichzeitig für die Eingabe ausgewählt werden. 🎜🎜Das Merkmal dieser Funktion ist, dass der vierte Parameter verwendet werden kann, wenn der erste Parameter 14 bis 19 ist. Zu diesem Zeitpunkt unterstützt der vierte Parameter Arrays, sodass er in verschiedenen Anwendungen verwendet werden kann, um den Fehlerwert zu ersetzen, der nicht möglich ist werden direkt ignoriert. 🎜🎜Verwandte Lernempfehlungen: 🎜Excel-Tutorial🎜🎜

Das obige ist der detaillierte Inhalt vonBeispielzusammenfassung von acht Verwendungen der AGGREGATE-Funktion in Excel. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!