Excel Subtotal Function mit Formel Beispielen

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.

• Subtotale Funktion in Excel - Syntax und verwendet
• Top 3 Gründe für die Verwendung der subtotalen Funktion in Excel
• Beispiele für subtotale Formel
• Excel -Subtotal funktioniert nicht

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:

Subtotal (Funktion_Num, Ref1, [Ref2],…)

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.

Funktion_Num		Funktion	Beschreibung
1	101	DURCHSCHNITT	Gibt den Durchschnitt der Zahlen zurück.
2	102	ZÄHLEN	Zählt Zellen, die numerische Werte enthalten.
3	103	Counta	Zählt nicht leere Zellen.
4	104	Max	Gibt den größten Wert zurück.
5	105	Min	Gibt den kleinsten Wert zurück.
6	106	PRODUKT	Berechnet das Produkt von Zellen.
7	107	Stdev	Gibt die Standardabweichung einer Population zurück, die auf einer Stichprobe von Zahlen basiert.
8	108	Stdevp	Gibt die Standardabweichung auf der Grundlage einer ganzen Zahlenpopulation zurück.
9	109	SUMME	Addiert die Zahlen.
10	110	Var	Schätzt die Varianz einer Population basierend auf einer Stichprobe von Zahlen.
11	111	VAG	Schätzt die Varianz einer Bevölkerung auf der Grundlage einer ganzen Zahlenpopulation.

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!