Berechnungszusammenfassung im Excel-Filterstatus

Dieser Artikel vermittelt Ihnen relevantes Wissen über Excel. Es wird hauptsächlich erläutert, wie Sie nach dem Filtern Seriennummern hinzufügen und nach den Bedingungen zählen alle helfen.

Verwandte Lernempfehlungen: Excel-Tutorial

1 Seriennummern nach dem Filtern hinzufügen

Um fortlaufende Seriennummern im gefilterten Zustand beizubehalten, können wir zuerst den Filter aufheben und ihn hinzufügen Geben Sie in Zelle D2 die folgende Formel ein und ziehen Sie sie dann nach unten:

=ZWISCHENSUMME(3,E$1:E2)-1

Die ZWISCHENSUMME-Funktion zählt nur sichtbare Zellinhalte.

Der erste Parameter verwendet 3, was die Berechnungsregel für die Ausführung der COUNTA-Funktion angibt, d. h. das Zählen der Anzahl sichtbarer Zellen für den zweiten Parameter.

Der zweite Parameter verwendet einen dynamisch erweiterten Bereich E$1:E2. Wenn die Formel nach unten gezogen wird, wird dieser Bereich zu E$1:E3, E$1:E4, E$1:E5,...

Die Formel berechnet immer Die Anzahl der sichtbaren, nicht leeren Zellen in Spalte E von der ersten Zeile bis zu der Zeile, in der sich die Formel befindet. Subtrahieren Sie 1 vom Ergebnis. Das berechnete Ergebnis entspricht der Seriennummer und bleibt nach dem Filtern kontinuierlich.

Hinweis: Wenn diese Formel in =SUBTOTAL(3,E$2:E2) geändert wird, d. h. beginnend mit der Zeile, in der sich die Formel befindet, ist das Ergebnis der Seriennummer in Ordnung, die letzte Zeile jedoch Wird von Excel beim Filtern immer als Zusammenfassungszeile behandelt.

2. Nach dem Filtern multiplizieren

Wie in der Abbildung unten gezeigt, müssen Sie nach dem Filtern in Spalte E den Gesamtbetrag multipliziert mit dem Stückpreis berechnen.

E2-Zellformel lautet:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

Um das gefilterte Produkt zu berechnen, wird das Der Schlüssel zum Problem besteht darin, festzustellen, ob die Daten sichtbar sind.

Wie ist dieser sichtbare Status zu beurteilen?

Die Funktionen OFFSET und ZWISCHENSUMME müssen kombiniert werden.

Verwenden Sie zunächst die Funktion OFFSET, nehmen Sie Zelle E3 als Basispunkt und versetzen Sie die Zeilen 1 bis 13 nach unten, um eine mehrdimensionale Referenz zu erhalten. Diese mehrdimensionale Referenz enthält 13 Referenzbereiche mit einer Zeile und einer Spalte, d. h. es werden jeweils einzelne Zellen von E4 bis E16 referenziert.

Als nächstes verwenden Sie die ZWISCHENSUMME-Funktion. Verwenden Sie 3 als ersten Parameter, d. h. zählen Sie die Anzahl der sichtbaren Zellen in jeder Zelle von E4 bis E16. Wenn sich die Zelle im angezeigten Zustand befindet, ist das statistische Ergebnis für diese Zelle 1 Ansonsten ist das statistische Ergebnis 0. Erhalten Sie ein Speicherarray, das dem folgenden Effekt ähnelt:

{1;0;1;1;1;1;0;0;1;1;0;1;0}

Verwenden Sie das obige Ergebnis multipliziert mit der Zahl und Summe von Spalte F Der Einheitspreis von Spalte G entspricht, wenn sich die Zelle im Anzeigestatus befindet, 1*Menge*Stückpreis, andernfalls entspricht er 0*Menge*Stückpreis.

Verwenden Sie abschließend die SUMPRODUCT-Funktion, um die Produkte zu summieren.

3. Zählung nach Bedingungen nach der Filterung

Wie in der Abbildung unten dargestellt, muss nach der Filterung der Abteilungen in Spalte E die Anzahl der Personen mit mehr als 3 Dienstjahren gezählt werden.

E2-Zellenformel lautet:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

Das Berechnungsprinzip der ersten Hälfte und das obige Beispiel ist das gleiche, der Kern besteht auch darin, zu bestimmen, ob die Zelle sichtbar ist.

Die statistischen Bedingungen in der zweiten Hälfte der Formel (G4:G16>3) werden mit den Beurteilungsergebnissen in der ersten Hälfte multipliziert, was darauf hinweist, dass beide Bedingungen gleichzeitig erfüllt sind, d. h. die Anzahl der Elemente in sichtbarer Zustand und Spalte G ist größer als 3.

4. Korrigieren Sie den Titel nach dem Filtern automatisch. Wie in der Abbildung unten gezeigt, soll sich der Titel von Zelle D1 automatisch in den entsprechenden Abteilungsnamen ändern =LOOKUP(1,0 /SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&"Statistical Table"

Die Kombination aus SUBTOTAL- und OFFSET-Funktionen, die Der Zweck besteht weiterhin darin, die Einheit der Spalte D zu bestimmen, ob das Raster sichtbar ist. Holen Sie sich ein Speicherarray bestehend aus 0 und 1:

{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}

Verwenden Sie 0/diesen Speicher Array, erhalten Sie ein neues Speicherarray bestehend aus 0 und dem Fehlerwert:

{#DIV/0!;0;#DIV/0!…;0;0;0;0;#DIV/0!;0;# DIV /0!;0;#DIV/0!}

Die LOOKUP-Funktion verwendet 1 als Abfragewert, findet die Position der letzten 0 im obigen Speicherarray und gibt den Inhalt der Spalte E an der entsprechenden Position zurück.

Das ultimative Ziel besteht darin, den Inhalt der zuletzt angezeigten Zelle nach dem Filtern zu extrahieren.

Verknüpfen Sie den extrahierten Inhalt mit „Statistische Tabelle“ und wandeln Sie ihn in einen automatisch aktualisierten Tabellentitel um.

Verwandte Lernempfehlungen:

Excel-Tutorial

Das obige ist der detaillierte Inhalt vonBerechnungszusammenfassung im Excel-Filterstatus. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!