1. Suche nach Tags

Stellen Sie sich eine Tabelle vor, in der die Namen von Abteilungen (oder Konten oder etwas anderem) zeilenweise aufgelistet sind.

Summieren Sie die Zellen nach Kriterium
Es ist notwendig, den Gesamtbetrag für jede Abteilung zu berechnen. Viele machen das mit einem Filter und schreiben mit Stiften in die Zellen.
Dies ist zwar einfach und unkompliziert mit nur einer Funktion möglich - SUMMESLI .
SUMMESLES (SUMIF) - Fasst Zellen zusammen, die eine bestimmte Bedingung erfüllen (es kann nur eine Bedingung angegeben werden). Diese Funktion kann auch verwendet werden, wenn die Tabelle nach Perioden in Spalten unterteilt ist (monatlich, in jedem Monat drei Spalten - Einnahmen | Ausgaben | Differenz) und Sie den Gesamtbetrag für alle Perioden nur nach Einnahmen, Ausgaben und Differenz berechnen müssen.

Insgesamt gibt es drei Argumente für den SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMME (A1: A20000, A1, B1: B20000)

• Bereich (A1: A20000) - gibt den Bereich mit den Kriterien an. Dh Die Spalte, in der nach dem im Kriterium- Argument angegebenen Wert gesucht werden soll.
• Das Kriterium (A1) ist der Wert (Text oder Zahl sowie das Datum), der im Bereich gefunden werden muss. Darf die Platzhalterzeichen "*" und "?" Enthalten. Dh Angabe des Kriteriums "* Masse *" zur Zusammenfassung der Werte, in denen das Wort "Masse" vorkommt. Gleichzeitig kann das Wort „Masse“ an einer beliebigen Stelle im Text vorkommen, oder es kann nur ein Wort in einer Zelle vorhanden sein. Bei Angabe von "Masse *" werden alle Werte, die mit "Masse" beginnen, aufsummiert. "?" - ersetzt nur ein Zeichen, d. h. Wenn Sie "mas? a" angeben, können Sie die Zeilen mit dem Wert "mass" und dem Wert "mask" usw. zusammenfassen.
  Wenn das Kriterium in eine Zelle geschrieben ist und Sie weiterhin Platzhalterzeichen verwenden müssen, können Sie eine Verknüpfung zu dieser Zelle herstellen, indem Sie die erforderliche hinzufügen. Angenommen, Sie müssen die Werte zusammenfassen, die das Wort "total" enthalten. Das Wort "total" wird in Zelle A1 geschrieben, während es in Spalte A verschiedene Schreibweisen geben kann, die das Wort "total" enthalten: "totals for June", "totals for July", "totals for March". Die Formel sollte dann so aussehen:
  = SOMMER (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - Das & -Zeichen (kaufmännisches Und) kombiniert mehrere Werte zu einem. Dh Das Ergebnis ist "* result *".
  Um das Prinzip der Funktionsweise von Formeln besser zu verstehen, empfiehlt es sich, das Werkzeug Formel berechnen zu verwenden: So zeigen Sie die Schritte zum Berechnen von Formeln an
  Alle Textkriterien und Kriterien mit logischen und mathematischen Vorzeichen müssen in doppelte Anführungszeichen gesetzt werden (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Wenn das Kriterium eine Zahl ist, sind keine Anführungszeichen erforderlich. Wenn Sie ein Fragezeichen oder ein Sternchen direkt finden möchten, müssen Sie eine Tilde (~) davor setzen.
  Informationen zur Tilde und ihren Funktionen finden Sie in diesem Artikel: Wie ersetze / entferne / finde ich ein Sternchen?
• Sum_Range (B1: B20000) (optionales Argument) - Gibt den Bereich der zu summierenden Summen oder numerischen Werte an.

So funktioniert es: Die Funktion durchsucht den Bereich nach dem Wert, der durch das Kriterium- Argument angegeben ist, und summiert, wenn eine Übereinstimmung gefunden wird, die Daten, die durch das Range_Amount-Argument angegeben sind. Dh Wenn wir in Spalte A einen Abteilungsnamen und in Spalte B einen Betrag haben, ergibt die Angabe der Entwicklungsabteilung als Kriterium die Summe aller Werte in Spalte B, gegenüber denen sich die Entwicklungsabteilung in Spalte A befindet. Tatsächlich hat das SumArrangement möglicherweise nicht die gleiche Größe wie das Range-Argument, und dies führt nicht zu einem Fehler der Funktion selbst. Wenn Sie jedoch Zellen für die Summierung definieren, wird die obere linke Zelle des Range_Amount-Arguments als Startzelle für die Summierung verwendet. Anschließend werden die Zellen summiert, die in Größe und Form dem Range-Argument entsprechen.

Einige Funktionen
Das letzte Argument der Funktion (Sum_And_Band: B1: B20000) ist optional. Dies bedeutet, dass es nicht angegeben werden kann. Wenn Sie es nicht angeben, addiert die Funktion die im Range- Argument angegebenen Werte. Wofür ist es? Beispielsweise müssen Sie die Summe nur der Zahlen abrufen, die größer als Null sind. In Spalte A des Betrages. Dann sieht die Funktion so aus:
= SOMMER (A1: A20000; "> 0")

Was ist zu beachten ? Range_summing und range sollten in der Anzahl der Zeilen gleich sein. Andernfalls können Sie das falsche Ergebnis erhalten. Optimal, wenn es so aussieht wie in den Formeln, die ich angegeben habe: Der Bereich und der Bereich der Summierungen beginnen in einer Zeile und haben die gleiche Anzahl von Zeilen: A1: A20000; B1: B20000

Summation über zwei oder mehr Kriterien
Aber was tun, wenn die Kriterien für die Summierung 2 und mehr? Angenommen, Sie müssen nur die Beträge zusammenfassen, die zu einer Abteilung gehören, und zwar nur für ein bestimmtes Datum. Glückliche Besitzer von Office-Versionen ab 2007 können die SUMMESLIMN-Funktion verwenden:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Das erste Argument gibt den Zellenbereich an, der die Beträge enthält, die zu einem zusammengefasst werden.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Gibt den Zellenbereich an, in dem Sie nach einem Kriterium nach einer Übereinstimmung suchen möchten.
$ I $ 3, $ H8 - Kriterium. Hier sind wie in SUMMESLI die Platzhalterzeichen * und ? Erlaubt . und sie arbeiten genauso.

Besonderheiten bei der Angabe von Argumenten: Zuerst wird der Kriterienbereich angegeben (sie sind nummeriert), dann wird der Wert (das Kriterium) direkt in Semikolon angegeben, der in diesem Bereich gefunden werden muss - $ A $ 2: $ A $ 50; $ I $ 3. Und sonst nichts. Sie sollten nicht versuchen, zuerst alle Bereiche und dann die Kriterien für sie anzugeben - die Funktion gibt entweder einen Fehler aus oder fasst nicht zusammen, was erforderlich ist.

Alle Bedingungen werden nach dem Prinzip I verglichen. Das heißt, wenn alle aufgeführten Bedingungen erfüllt sind. Wenn mindestens eine Bedingung nicht erfüllt ist, überspringt die Funktion die Zeile und fügt nichts hinzu.
Wie bei den SUMMERS sollten die Summen- und Kriterienbereiche in der Anzahl der Zeilen gleich sein.

Weil SUMMESLIMN erschien erst in Excel-Versionen ab 2007. Wie können dann unglückliche Benutzer früherer Versionen in solchen Fällen sein? Ganz einfach: Verwenden Sie eine andere Funktion - SUMPRODUCT. Ich werde die Argumente nicht malen, weil Es gibt viele von ihnen und alle sind Arrays von Werten. Diese Funktion multipliziert die durch die Argumente angegebenen Arrays. Ich werde versuchen, das allgemeine Prinzip der Verwendung dieser Funktion zu beschreiben, um Daten zu mehreren Bedingungen zusammenzufassen.
Um das Summationsproblem nach mehreren Kriterien zu lösen, sieht die Funktion folgendermaßen aus:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - Datumsbereich. $ I $ 3 ist das Datum des Kriteriums, für das die Daten summiert werden müssen.
$ B $ 2: $ B $ 50 - die Namen der Abteilungen. H5 - der Name der Abteilung, deren Daten summiert werden müssen.
$ C $ 2: $ C $ 50 - Bereich mit Beträgen.

Wir analysieren die Logik, weil Für viele wird es völlig unklar sein, wenn man nur diese Funktion betrachtet. Wenn nur, weil in der Hilfe diese Anwendung nicht beschrieben wird. Reduzieren Sie zur besseren Lesbarkeit die Größe der Bereiche:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Der Ausdruck ($ A $ 2: $ A $ 5 = $ I $ 3) und ($ B $ 2: $ B $ 5 = H5) sind also logische und Rückgabearrays mit den logischen Werten FALSE und TRUE. WAHR, wenn die Zelle des Bereichs $ A $ 2: $ A $ 5 dem Wert der Zelle $ I $ 3 entspricht und die Zelle des Bereichs $ B $ 2: $ B $ 5 dem Wert der Zelle H5 entspricht. Dh wir haben folgendes:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Wie Sie sehen, gibt es im ersten Array zwei Übereinstimmungen für die Bedingung und im zweiten. Weiterhin werden diese beiden Arrays multipliziert (hierfür ist das Multiplikationszeichen (*) verantwortlich). Wenn eine Multiplikation auftritt, erfolgt die implizite Umwandlung der Arrays FALSE und TRUE in die numerischen Konstanten 0 bzw. 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Wie Sie wissen, erhalten wir, wenn wir mit Null multiplizieren, Null. Und das Ergebnis ist ein einzelnes Array:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Dann wird das Array {0; 0; 1; 0} mit einem Array von Zahlen im Bereich von $ C $ 2: $ C $ 50 multipliziert:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Als Ergebnis erhalten wir 30. Was wir brauchten - wir erhalten nur den Betrag, der das Kriterium erfüllt. Wenn mehr als eine Summe das Kriterium erfüllt, werden sie aufsummiert.

Vorteil von SUMMYROIZV
Wenn die Argumente das Pluszeichen anstelle des Multiplikationszeichens haben:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
dann werden die Bedingungen gemäß dem OR-Prinzip verglichen: d.h. Gesamtsummen werden summiert, wenn mindestens eine Bedingung erfüllt ist: entweder $ A $ 2: $ A $ 5 entspricht dem Zellenwert $ I $ 3 oder Zelle des Bereichs $ B $ 2: $ B $ 5 entspricht dem Zellenwert H5.
Dies ist der Vorteil von SUMMPRODUCT gegenüber SUMMESLIMN. SUMMESLIMN kann keine Werte nach dem ODER-Prinzip summieren, sondern nur nach dem UND-Prinzip (alle Bedingungen müssen erfüllt sein).

Nachteile
SUMPRODUCT kann keine Platzhalter * und? Verwenden. Eine genauere Verwendung ist möglich, sie werden jedoch nicht als Sonderzeichen, sondern als Sternchen und Fragezeichen wahrgenommen. Ich halte dies für einen erheblichen Nachteil. Und obwohl dies umgangen werden kann, verwende ich andere Funktionen in SUMPRODUCT - es wäre trotzdem großartig, wenn die Funktion irgendwie Platzhalter verwenden könnte.

In diesem Beispiel finden Sie einige Funktionsbeispiele zum besseren Verständnis der obigen Ausführungen.

Laden Sie ein Beispiel herunter

Menge nach mehreren Kriterien (41,5 KiB, 10.477 Downloads)

Siehe auch:
Summe der Zellen nach Füllfarbe
Summe der Zellen nach Schriftfarbe
Aufsummierung der Zellen nach Zellenformat
Berechnen Sie die Anzahl der Zellen anhand der Füllfarbe
Berechnen Sie die Anzahl der Zellen anhand der Schriftfarbe
Wie man Daten aus mehreren Blättern zusammenfasst, auch nach Bedingung

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedlidelideirection " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectduration2: 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; Hintergrundfarbe: # 333333; Deckkraft: 0,6; Filter: a lpha (opacity = 60); "," titlecss ":" display: block; Position: relativ; Schriftart: fett gedruckt 14px "Lucida Sans Unicode", "Lucida Grande", serifenlos, Arial; color: #fff; "," descriptioncss ":" display: block; Position: relativ; Schriftart: 12px "Lucida Sans Unicode", "Lucida Grande", serifenlos, Arial; Farbe: #fff; Rand oben: 8px; "," buttoncss ":" Anzeige: Block; Position: relativ; Rand oben: 8px; "," texteffectresponsive ": true," texteffectresponsive ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Suche nach Tags