Das Tutorial erkl?rt die Spezifit?ten der subtotalen Funktion in Excel und zeigt, wie subtotale Formeln verwendet werden, um Daten in sichtbaren Zellen zusammenzufassen.

Im vorherigen Artikel haben wir eine automatische M?glichkeit er?rtert, Subtotale in Excel mithilfe der subtotalen Funktion in Excel einzusetzen. Heute lernen Sie, wie Sie selbst subtotale Formeln selbst schreiben und welche Vorteile dies Ihnen gibt.

Excel Subtotale Funktion - Syntax und verwendet

Microsoft definiert Excel -Subtotal als die Funktion, die eine Subtotale in einer Liste oder Datenbank zurückgibt. In diesem Zusammenhang ist "subtotal" nicht nur die Gesamtzahlen in einem definierten Zellbereich. Im Gegensatz zu anderen Excel -Funktionen, die nur für eine bestimmte Sache ausgelegt sind, ist subtotal erstaunlich vielseitig - es kann unterschiedliche arithmetische und logische Operationen wie das Z?hlen von Zellen, die Berechnung des Durchschnitts, die Ermittlung des Mindest- oder Maximalwerts und mehr durchführen.

Die subtotale Funktion ist in allen Versionen von Excel 2016, Excel 2013, Excel 2010, Excel 2007 und Lower erh?ltlich.

Die Syntax der excel -subtotalen Funktion lautet wie folgt:

Wo:

• Funktion_num - eine Zahl, die angibt, welche Funktion für die Subtotale verwendet werden soll.
• Ref1, Ref2,… - eine oder mehrere Zellen oder Bereiche bis subtotal. Das erste Ref -Argument ist erforderlich, andere (bis zu 254) sind optional.

Das Function_Num -Argument kann zu einem der folgenden S?tze geh?ren:

• 1 - 11 ignorieren gefilterte Zellen, enthalten jedoch manuell versteckte Zeilen.
• 101 - 111 Ignorieren Sie alle versteckten Zellen - filtriert und manuell versteckt.

Tats?chlich müssen nicht alle Funktionsnummern auswendig gelernt werden. Sobald Sie mit der Eingabe einer subtotalen Formel in einer Zelle oder in der Formelleiste beginnen, zeigt Microsoft Excel eine Liste der verfügbaren Funktionsnummern für Sie an.

So k?nnen Sie beispielsweise eine subtotale 9 -Formel erstellen, um die Werte in den Zellen C2 bis C8 zusammenzufassen:

Um der Formel eine Funktionsnummer hinzuzufügen, doppelklicken Sie darauf, geben Sie ein Komma ein, geben Sie einen Bereich an, geben Sie die Schlie?klammern und drücken Sie die Eingabetaste. Die abgeschlossene Formel sieht folgenderma?en aus:

=SUBTOTAL(9,C2:C8)

In ?hnlicher Weise k?nnen Sie eine subtotale 1-Formel schreiben, um eine durchschnittliche, subtotale 2 zu erhalten, um Zellen mit Zahlen, subtotalen 3 zu z?hlen, um Nichtblanks zu z?hlen, und so weiter. Der folgende Screenshot zeigt einige andere Formeln in Aktion:

Notiz. Wenn Sie eine subtotale Formel mit einer Zusammenfassungsfunktion wie Summe oder Durchschnitt verwenden, berechnet sie nur Zellen mit Zahlen, die Blanks und Zellen ignorieren, die nicht numerische Werte enthalten.

Nachdem Sie wissen, wie Sie in Excel eine subtotale Formel erstellen k?nnen, ist die Hauptfrage: Warum sollte man sich die Mühe machen, sie zu lernen? Warum nicht einfach eine regul?re Funktion wie Summe, Z?hlung, Maximum usw. verwenden? Sie finden die Antwort direkt unten.

Top 3 Gründe für die Verwendung von Subtotal in Excel

Im Vergleich zu den herk?mmlichen Excel -Funktionen bietet subtotal die folgenden wichtigen Vorteile.

1. Berechnen Sie die Werte in gefilterten Zeilen

Da die excel-subtotale Funktion Werte in gefilterten Zeilen ignoriert, k?nnen Sie eine dynamische Datenübersicht erstellen, bei der subtotale Werte automatisch gem?? dem Filter neu berechnet werden.

Wenn wir beispielsweise die Tabelle filtern, um den Umsatz nur für die Ostregion zu zeigen, wird die subtotale Formel automatisch so anpassen, dass alle anderen Regionen aus der Gesamtsumme entfernt werden:

Notiz. Da beide Funktionsnummerns?tze (1-11 und 101-111) gefilterte Zellen ignorieren, k?nnen Sie in diesem Fall Ether Subtotal 9 oder Subtotal 109-Formel verwenden.

2. Berechnen Sie nur sichtbare Zellen

Wie Sie sich erinnern, ignorieren subtotale Formeln mit Funktion_Num 101 bis 111 alle versteckten Zellen - filtriert und manuell versteckt. Wenn Sie also die Feature von Excel aus der Ausblenden verwenden, um irrelevante Daten aus der Ansicht zu entfernen, verwenden Sie die Funktionsnummer 101-111, um Werte in versteckten Zeilen aus Subtotalen auszuschlie?en.

Das folgende Beispiel hilft Ihnen dabei, mehr Verst?ndnis dafür zu erlangen, wie es funktioniert: Subtotal 9 vs. Subtotal 109.

3. Ignorieren Sie die Werte in verschachtelten subtotalen Formeln

Wenn der Bereich der subtotalen Excel -Formel andere subtotale Formeln enth?lt, werden diese verschachtelten Subtotale ignoriert, sodass die gleichen Zahlen nicht zweimal berechnet werden. Genial, nicht wahr?

Im folgenden Screenshot ignoriert die gro?e durchschnittliche Formel SUBTOTAL(1, C2:C10) die Ergebnisse der subtotalen Formeln in den Zellen C3 und C10, als h?tten Sie eine durchschnittliche Formel mit 2 separaten Bereichendurchschnitt AVERAGE(C2:C5, C7:C9) .

Verwenden von subtotalen Beispielen in Excel - Formel

Wenn Sie zum ersten Mal subtotal begegnen, mag es komplex, schwierig und sogar sinnlos erscheinen. Aber sobald Sie zu Messing -Tacks gekommen sind, werden Sie erkennen, dass es nicht so schwierig ist, zu meistern. Die folgenden Beispiele zeigen Ihnen einige hilfreiche Tipps und inspirierende Ideen.

Beispiel 1. Subtotal 9 vs. Subtotal 109

Wie Sie bereits wissen, akzeptiert Excel Subtotal 2 S?tze von Funktionsnummern: 1-11 und 101-111. Beide S?tze ignorieren gefilterte Zeilen, aber die Zahlen 1-11 enthalten manuell versteckte Zeilen, w?hrend 101-111 sie ausschlie?en. Um den Unterschied besser zu verstehen, betrachten wir das folgende Beispiel.

In den gesamten filtrierten Zeilen k?nnen Sie entweder subtotale 9 oder subtotale 109 -Formel verwenden, wie im folgenden Screenshot gezeigt:

Wenn Sie jedoch irrelevante Elemente manuell versteckt haben, indem Sie den Befehl aus ausblenden Zeilen auf der Registerkarte Startseite > Zellen Gruppe> Format > Ausblenden & unhide verwenden oder mit der rechten Maustaste auf die Zeilen klicken und dann auf Ausblenden klicken, und nun nur in sichtbaren Zeilen die Gesamtwerte sind, ist Subtotal 109 die einzige Option:

Andere Funktionsnummern funktionieren auf die gleiche Weise. Zum Beispiel, um nicht-mlankfilterte Zellen , entweder subtotale 3 oder subtotale 103-Formel, zu z?hlen. Aber nur subtotale 103 k?nnen sichtbare Nichtblanks ordnungsgem?? z?hlen, wenn im Bereich versteckte Zeilen vorhanden sind:

Notiz. Die excel-subtotale Funktion mit function_num 101-111 vernachl?ssigt die Werte in versteckten Zeilen, jedoch nicht in versteckten Spalten . Wenn Sie beispielsweise eine Formel wie SUBTOTAL(109, A1:E1) verwenden, um Zahlen in einem horizontalen Bereich zu summieren, hat das Verstecken einer Spalte keinen Einfluss auf die Subtotal.

Beispiel 2. Wenn Sie Daten dynamisch zusammenfassen

Wenn Sie einen Zusammenfassungsbericht oder ein Dashboard erstellen, in dem Sie verschiedene Datenzusammenfassungen anzeigen müssen, aber keinen Platz für alles haben, ist der folgende Ansatz m?glicherweise eine L?sung:

• Erstellen Sie in einer Zelle eine Dropdown-Liste, die die Funktionsnamen wie Total, Max, Min usw. enth?lt.
• Geben Sie in einer Zelle neben dem Dropdown eine verschachtelte If-Formel mit den eingebetteten subtotalen Funktionen ein, die den Funktionsnamen in der Dropdown-Liste entsprechen.

Die Annahme, dass die Werte zu Subtotal in den Zellen C2: C16 sind, und die Dropdown-Liste in A17 enth?lt Gesamt- , Durchschnitts- , Max- und MIN- Elemente, die "dynamische" subtotale Formel lautet wie folgt:

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

Und nun berechnet die entsprechende subtotale Funktion, je nachdem, welche Funktion Ihr Benutzer aus der Dropdown-Liste ausw?hlt, die Werte in gefilterten Zeilen berechnen:

Tipp. Wenn pl?tzlich die Dropdown-Liste und die Formelzelle aus Ihrem Arbeitsblatt verschwinden, w?hlen Sie diese in der Filterliste.

Excel Subtotal funktioniert nicht - gemeinsame Fehler

Wenn Ihre subtotale Formel einen Fehler zurückgibt, liegt dies wahrscheinlich aus einem der folgenden Gründe:

#VALUE! - Das Function_num -Argument ist anders als eine Ganzzahl zwischen 1 - 11 oder 101 - 111; oder eines der REF-Argumente enth?lt eine 3-D-Referenz.

#DIV/0! - tritt auf, wenn eine bestimmte Zusammenfassungsfunktion eine Teilung durch Null ausführen muss (z. B. Berechnung einer durchschnittlichen oder Standardabweichung für einen Bereich von Zellen, der keinen einzigen numerischen Wert enth?lt).

#NAME? - Der Name der subtotalen Funktion ist falsch geschrieben - Der leichtere Fehler zu beheben :)

Tipp. Wenn Sie sich mit der subtotalen Funktion noch nicht wohl fühlen, k?nnen Sie die integrierte subtotale Funktion verwenden und die Formeln automatisch für Sie einfügen lassen.

So verwenden Sie die subtotalen Formeln in Excel, um Daten in sichtbaren Zellen zu berechnen. Um die Beispiele zu erleichtern, k?nnen Sie unten unserem Proben -Arbeitsbuch unten herunterladen. Danke fürs Lesen!

Arbeitsbuch üben

Beispiele für excel subtotale Formel (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel Subtotal Function mit Formel Beispielen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!