Lernen Sie die erstaunliche AGGREGATE-Funktion in Excel kennen, eine ist 19 wert!

Im vorherigen Artikel „Praktische Excel-Kenntnisse weitergeben: Verwenden von „Suchen und Ersetzen“ zum Filtern von Datumsdaten“ haben wir einige praktische Vorgänge von „Suchen und Ersetzen“ kennengelernt. Heute werden wir über eine magische Excel-Statistikfunktion sprechen, die tatsächlich den Wert 19 haben kann. Es ist einfach ein Artefakt! Sammeln Sie es schnell ein.

Die Funktion, die ich euch heute vorstellen möchte, heißt AGGREGATE. Obwohl es sich um eine Funktion handelt, die in Excel 2010 vorhanden ist, wissen nicht viele Leute über diese Funktion Bescheid, da die AGGREGATE-Funktion nicht nur die Funktionen von 19 Funktionen wie SUM, AVERAGE, COUNT, LARGE usw. implementieren kann . Und Sie können ausgeblendete Zeilen, Fehlerwerte, Nullwerte usw. ignorieren. Wenn der Bereich Fehlerwerte enthält, geben Funktionen wie SUM einen Fehler zurück, und die AGGREGATE-Funktion ist in diesem Fall sehr praktisch.

Reden Sie einfach und üben Sie keine falschen Bewegungen. Werfen wir einen Blick auf die Fähigkeiten von AGGREGATE.

Wir verwenden eine Bewertungstabelle, um die grundlegende Verwendung von AGGREGATE zu veranschaulichen. Die Datenquelle ist wie in der Abbildung dargestellt:

Jeder Schüler absolviert sechs Tests und basierend auf den Ergebnissen die fünf statistischen Inhalte des blauen Bereichs Ich glaube, dass es für die meisten Schüler nützlich sein wird, dieses Formular auszufüllen. Es ist nichts weiter als die Beherrschung einiger Grundfunktionen: AVERAGE (Durchschnittspunktzahl), SUM (Gesamtpunktzahl). (höchste Punktzahl), MIN (niedrigste Punktzahl) und COUNT (tatsächliche Punktzahl) führen jeweils Statistiken zu den fünf Elementen durch. Möglicherweise gibt es einige neue Freunde, die die fünf oben genannten Funktionen nicht verstehen. Für alle Fälle müssen Sie also nur die AGGREGATE-Funktion lernen, um die Statistiken der oben genannten Daten zu realisieren.

Das Grundformat von AGGREGATE ist: = AGGREGATE (statistische Funktion, ignorierte Werte, Datenbereich) Schauen wir uns an, wie die fünf statistischen Inhalte im Beispiel vervollständigt werden. = AGGREGATE(统计功能，忽略哪些值，数据区域)，以下分别来看看如何完成例子中的五项统计内容。

一、统计平均分

当前表格平均分统计公式为：=AGGREGATE(1,,B2:G2)。在H2单元格中输入公式再整列填充公式即可获得各学生的平均分。

说明：当统计功能为1的时候，函数实现计算平均值的功能。本例中我们并不需要指定忽略统计的数据，因此第二参数可以省略（此处写了两个逗号，中间省略了一个参数），最后一个参数就是要计算的数据区域B2:G2，函数用法非常简单，那么结果是否正确呢？不妨使用AVERAGE函数来验证一下：

可以看到，结果完全一致！

接下来我们再看看如何用AGGREGATE函数统计总分。

二、统计总分

当前表格总分统计公式为：=AGGREGATE(9,,B2:G2)。在I2单元格中输入公式再整列填充公式即可获得各学生总分。

只需要将第一个参数改为9即可，因为9对应的就是求和功能。

说到这里，可能会有些朋友担心，第一个参数里1代表平均值， 9代表求和，这个函数一共有19个功能，会不会很难记住。

实际上完全不需要有这种担心，Excel为我们提供了非常智能的提醒功能，当我们输入函数之后，就有对应参数功能的选项：

只要对照这个提示，选择自己需要的功能即可。

三、统计最高分

了解这个功能以后，最后的三个统计项目就很容易完成了，最高分肯定是选择4，因此J2单元格公式为：=AGGREGATE(4,,B2:G2)

四、统计最低分

最低分选择5，K2单元格公式为：=AGGREGATE(5,,B2:G2)

1. Statistische Durchschnittspunktzahl

Die aktuelle Tabellendurchschnittspunktzahl-Statistikformel lautet: =AGGREGATE(1,,B2:G2). Geben Sie die Formel in Zelle H2 ein und füllen Sie die gesamte Spalte mit der Formel aus, um die durchschnittliche Punktzahl jedes Schülers zu erhalten.

🎜Beschreibung : Wenn die statistische Funktion 1 ist, implementiert die Funktion die Funktion zur Berechnung des Durchschnitts. In diesem Beispiel müssen wir die Daten nicht angeben, um Statistiken zu ignorieren, daher kann der zweite Parameter weggelassen werden (hier werden zwei Kommas geschrieben und ein Parameter in der Mitte weggelassen). Der letzte Parameter ist der Datenbereich B2:G2 zu berechnen. Funktionsverwendung Sehr einfach, ist das Ergebnis also korrekt? Sie können es auch mit der AVERAGE-Funktion überprüfen: 🎜🎜🎜🎜Sie können sehen, dass die Ergebnisse genau gleich sind! 🎜🎜Als nächstes schauen wir uns an, wie man die AGGREGATE-Funktion verwendet, um die Gesamtpunktzahl zu zählen. 🎜🎜🎜🎜2. Statistische Gesamtpunktzahl 🎜🎜🎜🎜Die aktuelle Statistikformel für die Gesamtpunktzahl der Tabelle lautet: =AGGREGATE(9,,B2:G2). Geben Sie die Formel in Zelle I2 ein und füllen Sie die gesamte Spalte mit der Formel aus, um die Gesamtpunktzahl jedes Schülers zu erhalten. 🎜🎜🎜🎜Nur Sie müssen den ersten Parameter auf 9 ändern, da 9 der Summationsfunktion entspricht. 🎜🎜Apropos, einige Freunde sind vielleicht besorgt, dass 1 im ersten Parameter den Durchschnitt und 9 die Summe darstellt. Diese Funktion hat insgesamt 19 Funktionen, daher wird es schwierig sein, sich daran zu erinnern. 🎜🎜Tatsächlich besteht darüber überhaupt kein Grund zur Sorge. Excel bietet uns eine sehr intelligente Erinnerungsfunktion. Nachdem wir die Funktion eingegeben haben, gibt es Optionen für die entsprechende Parameterfunktion: 🎜🎜🎜🎜Folgen Sie einfach dieser Aufforderung und wählen Sie die gewünschte Funktion aus . 🎜🎜🎜🎜3. Statistik der höchsten Punktzahl🎜🎜🎜🎜Nachdem Sie diese Funktion verstanden haben, können die letzten drei statistischen Elemente problemlos 4 sein, daher lautet die J2-Zellformel: 4,,B2:G2)🎜🎜🎜🎜🎜🎜4. Statistik der niedrigsten Punktzahl🎜🎜🎜🎜Wählen Sie 5 für die niedrigste Punktzahl. Die Formel in Zelle K2 lautet: =AGGREGATE(5,,B2:G2) 🎜 🎜🎜🎜🎜🎜🎜5. Statistiken zu tatsächlichen Referenzthemen🎜🎜🎜

Das tatsächliche Referenzkonto ist die Anzahl der Zahlen im statistischen Datenbereich. Verwenden Sie die COUNT-Funktion und wählen Sie 2 aus, sodass die Formel lautet: =AGGREGATE(2,,B2:G2)=AGGREGATE(2,,B2:G2)

好了，通过以上五个例子，朋友们对于AGGREGATE的基本用法应该有所掌握，虽然说只用了一个函数就完成了五个函数的工作，相比之前要分别使用五个函数来完成工作提高了一定的效率，但每个公式还是要修改一下才能用。如果能够使用一个公式右拉下拉的话，那才爽呢。（有同感的朋友可以在文末留言哦）

六、五种统计一步到位

对于有这种想法的朋友，应该提出表扬，毕竟我们学习Excel的函数公式，不仅仅是为了完成工作，更加希望能够提高效率。那么有没有可能使用一个公式右拉下拉来完成例子中的五项统计呢？答案是肯定的：有！不过要用到一对函数组合，那就是choose和column。

在揭晓公式之前，先对问题进行简单的分析，在我们使用AGGREGATE完成五项数据统计的公式中，只有第一参数也就是统计方式在发生变化，依次为：1、9、4、5、2。如果要想使用一个公式右拉下拉来完成的话，就得让公式在右拉时第一参数按照这个顺序来进行变化（下拉时不需要变化，因为统计方式相同）。

通常要使用公式右拉得到顺序变化的数据时就会用到column这个函数：

Column这个函数的作用是得到参数对应的列号，例如column（a1）就得到a1这个单元格的列号也就是1，右拉时由于a1会变成b1、c1……，公式结果就会按照1、2、3……这个顺序变化。

在本例中，我们需要得到的并不是一个很有规律的数列，而是1、9、4、5、2这样一个无序的数列，这时候就要用到choose函数来实现：

Choose函数的基本格式为：=choose(选择指数，值1，值2，值3……)

Choose函数根据第一个参数的数字来返回参数列表中的值。例如上图，当第一参数为1时，就返回参数列表中的第1个值“1”；当第一参数为2时，就返回参数列表中的第2个值“9”，以此类推，使用column作为choose的第一参数，就可以返回指定的序列了。

以上是对choose和column这对函数组合的说明，现在回到我们的问题，可以用来右拉下拉的这个公式就是：=AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)

可能有些新手还是会觉得晕乎乎的，这很正常，相信通过持续地学习，你就可以对这种公式运用自如了。

七、第一参数功能集锦

通过以上介绍，可以看到当我们合理运用了AGGREGATE函数之后，工作效率成倍增长。这个函数的第一参数到底有哪19种功能呢，通过下面这个对照表可以一目了然：

实际上比较常用的就是那么几种。

八、第二参数功能集锦

接下来我们再来看看第二参数又是什么功能，还是通过一个对照表来直观地了解：

1.忽略空值

以下通过两个例子看看如何使用第二参数来选择忽略的内容：=AGGREGATE(9,1,B2:B15)

Okay, durch das oben Gesagte Fünf Beispiele: Freunde sollten die grundlegende Verwendung von AGGREGATE verstehen. Obwohl nur eine Funktion zum Abschließen der Arbeit von fünf Funktionen verwendet wird, hat sich im Vergleich zur vorherigen Verwendung von fünf Funktionen zum Abschließen der Arbeit eine gewisse Effizienz verbessert Die Formel muss noch geändert werden, bevor sie verwendet werden kann. Es wäre großartig, wenn Sie eine Formel verwenden könnten, um nach rechts zu ziehen. (Freunde, denen es genauso geht, können am Ende des Artikels eine Nachricht hinterlassen)

Sechs oder fünf Arten von Statistiken in einem Schritt stark>

Für diejenigen, die diese Art von Freunden haben, die Ideen haben, sollte gelobt werden. Schließlich lernen wir Excel-Funktionsformeln nicht nur, um die Arbeit abzuschließen, sondern auch, um die Effizienz zu verbessern. Ist es also möglich, eine Formel zu verwenden, um die fünf Statistiken im Beispiel nach rechts zu vervollständigen? Die Antwort lautet ja: ja! Es sind jedoch zwei Funktionskombinationen erforderlich, nämlich „select“ und „column“.

Bevor wir die Formel offenlegen, führen wir eine einfache Analyse des Problems durch. In der Formel verwenden wir AGGREGATE, um fünf Datenstatistiken zu vervollständigen. Nur der erste Parameter, die statistische Methode, ändert sich in der Reihenfolge: 1, 9, 4 , 5 ,2. Wenn Sie eine Formel zum Herunterziehen nach rechts verwenden möchten, müssen Sie den ersten Parameter der Formel beim Herunterziehen nach rechts in dieser Reihenfolge ändern (beim Herunterziehen sind keine Änderungen erforderlich, da die statistische Methode dieselbe ist). ). 🎜🎜Wenn Sie eine Formel verwenden müssen, um nach rechts zu ziehen und sich sequentiell ändernde Daten zu erhalten, verwenden Sie normalerweise die Spaltenfunktion: 🎜🎜🎜🎜Column Diese Funktion wird verwendet, um die dem Parameter entsprechende Spaltennummer abzurufen. Beispielsweise erhält Spalte (a1) die Spaltennummer der Zelle a1, die 1 ist. Beim Ziehen nach rechts ändern sich die Formelergebnisse in der Reihenfolge 1, 2, 3, da a1 zu b1, c1... wird. 🎜🎜In diesem Beispiel benötigen wir keine sehr regelmäßige Sequenz, sondern eine ungeordnete Sequenz von 1, 9, 4, 5, 2. Zu diesem Zeitpunkt müssen wir die Auswahlfunktion verwenden, um Folgendes zu erreichen: 🎜 🎜🎜🎜Wählen Sie die Grundfunktion Das Format lautet: =choose (wählen Sie Index, Wert 1, Wert 2, Wert 3...)🎜🎜Die Funktion „Choose“ gibt den Wert in der Parameterliste basierend auf der Nummer des ersten Parameters zurück. Wenn in der Abbildung oben beispielsweise der erste Parameter 1 ist, wird der erste Wert „1“ in der Parameterliste zurückgegeben. Wenn der erste Parameter 2 ist, wird der zweite Wert „9“ in der Parameterliste zurückgegeben Wenn Sie die Spalte als ersten Parameter von „select“ verwenden, kann die angegebene Sequenz zurückgegeben werden. 🎜🎜Das Obige ist eine Erklärung der Funktionskombination von „Auswählen“ und „Spalte“. Nun zurück zu unserem Problem: Die Formel, die für das rechte Pulldown verwendet werden kann, lautet: =AGGREGATE(CHOOSE(COLUMN(A1),1, 9, 4,5,2),,$B2:$G2)🎜🎜🎜🎜Manchen Anfängern kann es immer noch schwindlig sein, was normal ist. Ich glaube, dass Sie diese Formel durch kontinuierliches Lernen frei verwenden können. 🎜🎜7. Erste Parameter-Funktionssammlung🎜🎜Anhand der obigen Einführung können Sie sehen, dass wir die AGGREGATE-Funktion richtig verwenden Die Arbeitseffizienz steigt exponentiell. Was sind die 19 Funktionen des ersten Parameters dieser Funktion? Sie können es anhand der folgenden Vergleichstabelle deutlich sehen: 🎜🎜🎜🎜Tatsächlich gibt es nur wenige häufig verwendete. 🎜🎜8. Zweite Parameter-Funktionssammlung🎜🎜Als nächstes schauen wir uns an, welche Funktion der zweite Parameter ist, oder Get ein visuelles Verständnis durch eine Vergleichstabelle: 🎜🎜🎜🎜1. Nullwerte ignorieren🎜🎜Im Folgenden wird an zwei Beispielen gezeigt, wie der zweite Parameter zum Auswählen ignorierter Inhalte verwendet wird: =AGGREGATE(9,1, B2 :B15)🎜🎜🎜🎜🎜Wählen Sie 9 für den ersten Parameter, der die Summierung darstellt, und wählen Sie 1 für den zweiten Parameter, der das Ignorieren ausgeblendeter Zeilen darstellt, verwenden Sie die AGGREGATE-Funktion zum Summieren und die SUM-Funktion Die Ergebnisse sind konsistent (die Gesamtpunktzahl in Zeile 16 wird mithilfe der SUM-Funktion summiert). Wenn wir bestimmte Datenzeilen ausblenden, können wir den Unterschied erkennen: 🎜🎜🎜🎜

Nachdem die Zeilen 4, 8 und 11 ausgeblendet wurden, fasst die Formel =AGGREGATE(9,1,B2:B15) nur die aktuell angezeigten Daten zusammen.

Apropos: Schüler, die die ZWISCHENSUMME-Funktion gelernt haben, werden definitiv denken, dass ZWISCHENSUMME auch eine solche Funktion hat. Die heute erschienene AGGREGATE-Funktion ist jedoch leistungsfähiger als die SUBTOTAL-Funktion, da SUBTOTAL Fehlerwerte und verschachtelte Zusammenfassungen nicht verarbeiten kann, AGGREGATE jedoch immer noch.

2. Ignorieren Sie den Fehlerwert

Das letzte Beispiel heute, sehen wir uns an, was passiert, wenn ein Fehlerwert auftritt:

Wie in der Abbildung oben gezeigt, werden die chinesischen Ergebnisse jedes Schülers aus dem erhalten vlookup-Funktion mithilfe der vlookup-Funktion Aus der Punktetabelle erhalten (diese Funktion wurde bereits in einem Tutorial besprochen, wenn Sie sie noch nicht kennen, können Sie auf den Link klicken, um mehr darüber zu erfahren: Link einfügen). Wenn der Name nicht in der Bewertungstabelle enthalten ist, wird ein Fehlerwert erhalten, z. B. Li Si und Zhang San. Unabhängig davon, ob wir die SUMME-Funktion oder die ZWISCHENSUMME-Funktion verwenden, können wir zu diesem Zeitpunkt nicht die korrekte Gesamtpunktzahl ermitteln Nur der chinesische Score kann den Fehlerwert ignorieren. Natürlich können Sie iferror und andere Funktionen zur Verarbeitung verwenden und dann SUM zum Summieren verwenden, aber das verdeckt nicht die Leistungsfähigkeit von AGGREGATE.

19 Arten von Statistikfunktionen plus 7 Arten von ignorierten Elementen Diese unglaubliche Integrationsfunktion ist wirklich nicht mit gewöhnlichen Funktionen vergleichbar! AGGREGATE ist der wohlverdiente König der statistischen Funktionen, holen Sie es sich jetzt!

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonLernen Sie die erstaunliche AGGREGATE-Funktion in Excel kennen, eine ist 19 wert!. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!