Die Funktion des SUMMESLE sowie der SUMMESLES nach zwei Kriterien

  1. Suche nach Tags
Tricks »11. Juni 2011 Dmitry 243582 Ansichten

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

Artikel geholfen? Teile den Link mit deinen Freunden! Video-Tutorials

{"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

Geh Zugang Apfeluhr Multex Ausblick Power Query und Power BI VBA arbeiten im Editor VBA-Code-Management Kostenlose Add-Ins Datum und Uhrzeit Diagramme und Grafiken Hinweise Datenschutz Das internet Bilder und Objekte Bettlaken und Bücher Makros und VBA Add-Ons Anpassung Drucken Daten suchen Datenschutzerklärung Mail Programme Arbeiten Sie mit Anwendungen Mit Dateien arbeiten Anwendungsentwicklung Übersichtstabellen Listen Schulungen und Webinare Finanziell Formatierung Formeln und Funktionen Excel-Funktionen VBA-Funktionen Zellen und Bereiche MulTEx-Aktien Datenanalyse Fehler und Störungen in Excel links Darf die Platzhalterzeichen "*" und "?
Quot;?
Wenn Sie "mas?
Wofür ist es?
2007. Wie können dann unglückliche Benutzer früherer Versionen in solchen Fällen sein?
Финансовый аналитик: новая профессия для новой экономики


Профессия будущего: Системный аналитик - профессия будущего, системный аналитик - Trend Club
Будущее - уже определено сегодня, остается только его реализовать. Что делать сегодняшнему студенту или ученику для того, чтобы получить интересную, захватывающую работу, которая будет приносить

Политический анализ и политическое прогнозирование
Под прогнозом в науке понимается вероятностное, научно обоснованное суждение о будущем. Именно вероятностный характер прогноза и его научная обоснованность отличает данный вид знания от других форм предвидения.

Политический анализ и политическое прогнозирование
Под прогнозом в науке понимается вероятностное, научно обоснованное суждение о будущем. Именно вероятностный характер прогноза и его научная обоснованность отличает данный вид знания от других форм предвидения.

Маркетинговые исследования - это... Этапы, результаты, пример маркетингового исследования
Маркетинговые исследования - это поиск, сбор, систематизация и анализ информации о ситуации на рынке с целью принятия управленческих решений в сфере производства и сбыта продукции. Стоит четко понимать,

Форекс брокер Альпари отзывы и обзор услуг и сервисов компании
Проголосовали: 902 За 88 %, Против 12 % За Против

Разница между науками о финансах и экономике. Экономическая статья на английском с переводом
Сайт Деловой английский www.delo-angl.ru Опубликовано: 14.04.2016 Хотя зачастую они изучаются и представляются как совершенно раздельные дисциплины, экономика и финансы, как науки, взаимосвязаны, разделяют

12 лучших сервисов для веб-аналитики
В статье вы узнаете о самых популярных инструментах для веб-аналитики, их функциональности и стоимости. И начнём мы с двух самых известных сервисов – Google Analytics и Яндекс.метрика. Вероятно, что каждый

Big Data – технология, рождающая новый тип бизнеса::БИТ 03.2014
Big Data – технология, рождающая новый тип бизнеса Главная / Архив номеров / 2014 / Выпуск №3 (36) / Big Data – технология, рождающая новый тип бизнеса Рубрика: Тема номера /  Системы хранения

Big Data и блокчейн — прорыв в области анализа данных
Постоянное ускорение роста объема данных является неотъемлемым элементом современных реалий. Социальные сети, мобильные устройства, данные с измерительных устройств, бизнес-информация

Добро пожаловать , Гость !

Мы настоятельно рекомендуем Вам зарегистрироваться, если вы ещё этого не сделали. регистрация откроет Вам новые функции.

Войти или Зарегистрироваться

www.silvio-berluskoni.ru © Все права защищены © 2016.