Zwischensumme in Excel (Formel, Beispiele) - Verwendung der SUBTOTAL-Funktion

Excel SUBTOTAL-Funktion (Inhaltsverzeichnis)

• Zwischensumme in Excel
• Zwischensumme Formel in Excel
• Wie verwende ich die Funktion SUBTOTAL in Excel?

Zwischensumme in Excel

• Eine Zwischensummenfunktion ist eine eingebaute Funktion in Excel, die unter der Funktion math / trig kategorisiert ist
• Die Zwischensumme summiert nicht nur die Zahlen in einem definierten Bereich von Zellen, sondern hilft auch bei der Ausführung verschiedener arithmetischer Operationen
• Zwischensumme ist vielseitig. Sie können es verwenden, um zu mitteln, zu summieren, eine Reihe von Zellen und andere Berechnungen zu zählen

Definition

Gibt eine Zwischensumme in einer Datenbank oder Liste zurück, dh um die Zwischensumme eines bestimmten Zellenbereichs zu ermitteln. Es führt eine bestimmte Berechnung für eine bestimmte Menge von Werten durch.

Zwischensumme Formel in Excel

Die Formel für die Funktion SUBTOTAL lautet wie folgt:

SUBTOTAL (function_num, ref1, (ref2), …) Wobei

Function_num oder Operationscode: Gibt an, welche Funktion für die Berechnung der Zwischensummen in einer bestimmten Liste verwendet werden soll, oder es handelt sich um eine Art mathematischer Operation, die Sie gerne ausführen

Ein function_num-Argument wird basierend auf den unten genannten zwei Kriterien oder Methoden angewendet

1. Versteckte Werte einschließen Wenn das Argument function_num zwischen 1 und 11 liegt, schließt die Funktion SUBTOTAL die versteckten Werte ein.
2. Ausgeblendete Werte ignorieren Wenn das Argument function_num zwischen 101 und 111 liegt, schließt die Funktion SUBTOTAL die ausgeblendeten Werte aus oder ignoriert sie. Es werden nur unsichtbare Zeilen gefunden, die ausgeblendete Werte in der Berechnung ignorieren.

Fun_num Schließt versteckte Werte ein		Fun_num Schließt versteckte Werte aus		Funktion		Beschreibung
1		101		DURCHSCHNITTLICH		Es berechnet den Durchschnitt eines bestimmten Bereichs.
2		102		ANZAHL		Es zählt die Anzahl der Zellen, die numerische Werte enthalten.
3		103		COUNTA		Es werden nicht leere Zellen im angegebenen Bereich gezählt.
4		104		Max		Es wird der größte Wert im angegebenen Bereich ermittelt.
5		105		MINDEST		Es wird der kleinste Wert im angegebenen Bereich ermittelt.
6		106		PRODUKT		Es berechnet das Produkt der Zellen im angegebenen Bereich.
7		107		STDEV		Es schätzt die Standardabweichung im angegebenen Bereich.
8		108		STDEVP		Es berechnet die Standardabweichung basierend auf der gesamten Grundgesamtheit.
9		109		SUMME		Es berechnet die Summe des angegebenen Bereichs.
10		110		VAR		Sie schätzt die Varianz im angegebenen Bereich.
11		111		VARP		Sie schätzt die Varianz basierend auf der gesamten Population.

ref1, (ref2): Ein oder mehrere Verweise auf Zellen, die die Werte enthalten, die für die Zwischensumme berechnet werden sollen.

Wie verwende ich die Funktion SUBTOTAL in Excel?

Schauen wir uns an, wie die Funktion SUBTOTAL in Excel funktioniert.

Sie können diese Excel-Vorlage für die Zwischensummenfunktion hier herunterladen - Excel-Vorlage für die Zwischensummenfunktion

Beispiel 1 - Zwischensumme Formel für Werte, die mit dem Excel-Autofilter oder durch einen Filter ausgeblendeten Zeilen ausgeblendet wurden

Im folgenden Beispiel enthält die Tabelle die vierteljährlichen Verkaufsdaten, wobei die einzelnen Quartale in Spalte E und die Verkaufsdaten in Spalte F aufgeführt sind.

Die Summenfunktion wird in Zelle "F16" angewendet, dh = SUMME (F4: F15).

Es gibt zurück oder ergibt den Wert 1184 .

SUMME-Funktion gibt den gleichen Wert zurück. dh 1184 anstelle von 194 für Q4-Daten, wenn Zeilen durch einen Filter ausgeblendet werden (siehe unten stehender Screenshot).

Wenn ich nur den korrekten SUMMENWERT von Q4-Daten verwenden möchte, muss ich die Funktion SUBTOTAL verwenden.

Wenden Sie vor dem Anwenden der Funktion SUBTOTAL in der Datensymbolleiste einen Filter für die Kopfzeile an.

Lassen Sie uns nun die Verkaufsdaten für Q1 oder Q2 oder Q3 oder Q4 einzeln für gefilterte Daten berechnen.

Verwenden Sie 109 als Argument function_num in der Funktion SUBTOTAL, um die Daten zusammenzufassen. dh = Zwischensumme (109, C17: C28)

Die Funktion SUBTOTAL ignoriert durch einen Filter ausgeblendete Zeilen und berechnet das richtige Ergebnis.

Hier ändert sich der Zwischensummenwert dynamisch entsprechend dem Filter. Es wird nur eine Summe von Werten in sichtbaren Zeilen gefunden. Ausgeblendete Zeilen werden von der Funktion SUBTOTAL vernachlässigt. (Unten genannt zB)

Q4

Q3

Q2

Q1

Beispiel # 2 - SUBTOTAL Funktion für manuell ausgeblendete Zeilen

In der Excel-Tabelle wurden die Zeilen 15, 16, 19 und 20 mit der Zeilenformatierungsoption ausgeblendet, indem diese Zeilen markiert, mit der rechten Maustaste geklickt und Ausblenden ausgewählt wurden.

In Zelle C23 der Tabelle wird eine Zwischensummenfunktion verwendet, um die Summe der sichtbaren Zellen zu berechnen.

Wir müssen 109 als function_num-Argument in der SUBTOTAL-Funktion verwenden, um die Daten zusammenzufassen.

dh = Zwischensumme (109, C14: C21)

Hier ignoriert die Funktion SUBTOTAL manuell ausgeblendete Zeilen und berechnet das korrekte Ergebnis.

Es wird nur eine Summe von Werten in sichtbaren Zeilen gefunden. Ausgeblendete Zeilen werden von der Funktion SUBTOTAL vernachlässigt

Beispiel # 3 - SUBTOTAL Funktion für normalen Wert

Da alle Zellen im Bereich der vierteljährlichen Verkaufszahlen sichtbar sind, keine von ihnen gefiltert oder Zeilen ausgeblendet sind, enthält es alle Werte.

Im folgenden Beispiel werden mehrere Zwischensummenfunktionen angewendet.

In der Zelle wird die Funktion C14, C15 & C16 SUBTOTAL angewendet.

Zur Berechnung des SUM-Werts wird die Funktion Nummer 9 oder 109 in einer Zwischensummenfunktion verwendet, in der die Summe des angegebenen Bereichs berechnet wird, dh = SUBTOTAL (9, C4: C11).

Es gibt die Summe aller Werte in C4: C11 an, sofern keine gefiltert und die Zeilen in diesem angegebenen Bereich ausgeblendet sind.

Zur Berechnung des MIN-Werts wird die Funktion Nummer 5 oder 105 in einer Zwischensummenfunktion verwendet, in der der kleinste Wert im angegebenen Bereich ermittelt wird. In einer Zelle C15 wird eine SUBTOTAL-Funktion angewendet, dh = SUBTOTAL (5, C4: C11)

Es wird der kleinste Wert im angegebenen Bereich, dh C4: C11, ermittelt

Zur Berechnung des MAX-Werts wird die Funktion Nummer 4 oder 104 in einer Zwischensummenfunktion verwendet, in der der größte Wert im angegebenen Bereich ermittelt wird. In einer Zelle, C16, wird eine SUBTOTAL-Funktion angewendet, dh = SUBTOTAL (4, C4: C11)

Es wird der größte Wert im angegebenen Bereich, dh C4: C11, ermittelt.

Wichtige Hinweise zur Funktion SUBTOTAL

• Eine SUBTOTAL-Funktion kann angewendet werden, um eine Summe von gefilterten Werten zu finden.
• Eine SUBTOTAL-Funktion wird verwendet, um Werte in ausgeblendeten Zeilen zu ignorieren.
• Der Wert des Arguments 'function _ num' sollte nicht größer als 11 oder kleiner als 1 sein. Ebenso sollte er nicht größer als 111 oder kleiner als 101 sein. Andernfalls wird #VALUE ausgegeben! Error.
• Eine SUBTOTAL-Funktion gilt nur für den numerischen Wert.
• Wenn im Bereich SUBTOTAL Zwischensummen vorhanden sind, werden diese vernachlässigt. Wenn also bereits vorhandene SUBTOTAL-Formeln im Eingabebereich einer anderen SUBTOTAL-Formel vorhanden sind, werden diese Werte bei der Berechnung durch die Zwischensummenfunktion vernachlässigt, sodass Doppelzählungen vermieden werden. es verhindert den Fehler der Doppelzählung.
• Mit einer SUBTOTAL-Funktion können Daten dynamisch zusammengefasst werden.
• Leere Zellen und Zellen mit nicht numerischen Werten werden bei der Berechnung von der Zwischensummenfunktion ignoriert.

Empfohlene Artikel

Dies war ein Leitfaden für SUBTOTAL. Hier diskutieren wir die SUBTOTAL-Formel und die Verwendung der SUBTOTAL-Funktion zusammen mit einem Excel-Beispiel und herunterladbaren Excel-Vorlagen. Sie können sich diese nützlichen Funktionen auch in Excel ansehen -

1. Suchfunktion in Excel
2. FIND-Funktion in Excel
3. NICHT in Excel
4. TRANSPOSE in Excel