Autor: Beate Schmitz --- Aus Excel Standard - Gruppe: Häufige Fragen
Auswerten von Bereichen nach ODER-Kriterien
Sie möchten zählen oder addieren, wenn bezogen auf einen Bereich ein Kriterium von verschiedene Kriterien zutrifft oder auf verschiedene Bereiche bezogen mindestens eine Bedingung WAHR wird. Zum Verständnis empfehle ich Ihnen den Artikel: UND * ODER +.
Für Summenauswertung können Sie dies durch die Addition mehrerer SUMMEWENN-Formeln erreichen oder über die Erweiterung der der Funktion SUMMENPRODUKT durch den Operator ODER. Darüber hinaus kann man den Spezialfilter (s.u.) einsetzen oder die Funktion DBSUMME einsetzen.
Für Anzahlauswertung können Sie dies durch die Addition mehrerer ZÄHLENWENN-Formeln erreichen oder über die Erweiterung der der Funktion SUMMENPRODUKT durch den Operator ODER. Darüber hinaus kann man den Spezialfilter (s.u.) einsetzen oder die Funktion DBANZAHL einsetzen.
Die SUMMENPRODUKT-Variante ergibt wohl die kürzere Formel als SUMMEWENN oder ZÄHLENWENN. Aber da SUMMENPRODUKT eine Array-Formel ist, führt eine Verwendung in großen Bereichen zu Performanz-Einbußen. Deshalb die Empfehlung: Benutzen Sie dann totz der längeren Formel die SUMMEWENN- bzw. ZÄHLENWENN-Lösung.
Beim Einsatz von SUMMENPRODUKT ist wichtig, dass jedes zusammengehörende ODER-Konstrukt wiederum von Klammern eingeschlossen sein muss. Der Operator für ODER ist das "+" Zeichen.
Die Kriterien können Werte (Besonderheit: Daten), Text oder Zellbezüge sein, deren Inhalt verglichen wird. Alle Varianten finden Sie in untenstehenden Beispielen wieder:
Auch über den Spezialfilter können Sie in Kombination mit der Funktion Teilergebnis Ihre Aufgabe lösen. Siehe Spezialfilter (2) - Filtern nach Kriterien und Spezialfilter (3) - Filtern mit Funkionen. Jedes der unten aufgeführten Beispiele könnten Sie mit dem Spezialfilter auf diese Art lösen. Zur Veranschaulichung habe ich in einem Fall ein Beispiel eingebaut. Beachten Sie dabei besonders den Unterschied, wie man man mit Spezialfilter zwischen UND- und ODER-Bedingungen unterscheidet. Vorteil des Spezialfilters ist, dass Sie sehr dynamisch auswerten können, indem Sie die Bedingungen nur im Kriterienbereich ändern und dass es eine sehr schnelle Filtermöglichkeit ist.
Als weitere Lösungsmöglichkeit gibt es die Datenbankfunktionen DBSUMME und DBANZAHL, auf die ich hier nicht weiter eingehe, da einige Besonderheiten zu beachten sind. Bei Interesse siehe: Datenbank Funktionen
Nachstehend stelle ich Ihnen die nachfolgenden Varianten vor:
Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben
Auswerten von Bereichen nach ODER-Kriterien
Autor: Beate Schmitz - Erstellt: ? - Letzte Revision: ?Auswerten von Bereichen nach ODER-Kriterien
Sie möchten zählen oder addieren, wenn bezogen auf einen Bereich ein Kriterium von verschiedene Kriterien zutrifft oder auf verschiedene Bereiche bezogen mindestens eine Bedingung WAHR wird. Zum Verständnis empfehle ich Ihnen den Artikel: UND * ODER +.
Für Summenauswertung können Sie dies durch die Addition mehrerer SUMMEWENN-Formeln erreichen oder über die Erweiterung der der Funktion SUMMENPRODUKT durch den Operator ODER. Darüber hinaus kann man den Spezialfilter (s.u.) einsetzen oder die Funktion DBSUMME einsetzen.
Für Anzahlauswertung können Sie dies durch die Addition mehrerer ZÄHLENWENN-Formeln erreichen oder über die Erweiterung der der Funktion SUMMENPRODUKT durch den Operator ODER. Darüber hinaus kann man den Spezialfilter (s.u.) einsetzen oder die Funktion DBANZAHL einsetzen.
Die SUMMENPRODUKT-Variante ergibt wohl die kürzere Formel als SUMMEWENN oder ZÄHLENWENN. Aber da SUMMENPRODUKT eine Array-Formel ist, führt eine Verwendung in großen Bereichen zu Performanz-Einbußen. Deshalb die Empfehlung: Benutzen Sie dann totz der längeren Formel die SUMMEWENN- bzw. ZÄHLENWENN-Lösung.
Beim Einsatz von SUMMENPRODUKT ist wichtig, dass jedes zusammengehörende ODER-Konstrukt wiederum von Klammern eingeschlossen sein muss. Der Operator für ODER ist das "+" Zeichen.
Die Kriterien können Werte (Besonderheit: Daten), Text oder Zellbezüge sein, deren Inhalt verglichen wird. Alle Varianten finden Sie in untenstehenden Beispielen wieder:
- handelt es sich bei den Kriterien in der Formel um Text, dann sind diese in Anführungszeichen zu setzen
- handelt es sich beim Kriterium um ein Datum, muss man dieses in Anführungszeichen setzen, damit wird das Datum in Text gewandelt und durch die Funktion WERT wieder zu einem Wert gewandelt.
Beispiel: =SUMMENPRODUKT((A2:A8="Bananen")*(D2:D8<(WERT("02.02.2007"))))
Auch über den Spezialfilter können Sie in Kombination mit der Funktion Teilergebnis Ihre Aufgabe lösen. Siehe Spezialfilter (2) - Filtern nach Kriterien und Spezialfilter (3) - Filtern mit Funkionen. Jedes der unten aufgeführten Beispiele könnten Sie mit dem Spezialfilter auf diese Art lösen. Zur Veranschaulichung habe ich in einem Fall ein Beispiel eingebaut. Beachten Sie dabei besonders den Unterschied, wie man man mit Spezialfilter zwischen UND- und ODER-Bedingungen unterscheidet. Vorteil des Spezialfilters ist, dass Sie sehr dynamisch auswerten können, indem Sie die Bedingungen nur im Kriterienbereich ändern und dass es eine sehr schnelle Filtermöglichkeit ist.
Als weitere Lösungsmöglichkeit gibt es die Datenbankfunktionen DBSUMME und DBANZAHL, auf die ich hier nicht weiter eingehe, da einige Besonderheiten zu beachten sind. Bei Interesse siehe: Datenbank Funktionen
Nachstehend stelle ich Ihnen die nachfolgenden Varianten vor:
- Mehrere ODER Kriterien in einer Spalte (auch mit Kurzform mit MTRANS)
- Mehrere Spalten addieren, wenn Bedingung erfüllt ist
- ODER in zwei Spalten - Vermeidung von Doppelzählungen
- Zeilen nur auswerten bei bestimmten Kriterienkombinationen in verschiedenen Spalten
- Zeilen auswerten nach allen möglichen ODER-Kriterienkombinationen in verschiedenen Spalten
Zum Blattanfang
Mehrere ODER Kriterien in einer Spalte
Mehrere ODER Kriterien in einer Spalte
| A | B | C | D | E | F | G | H | |
| 1 | ||||||||
| 2 | Obstsorte | Lager | Menge/Paletten | Zellbereich für Kriterien: | ||||
| 3 | Äpfel | Köln | 5 | Äpfel | Berlin | |||
| 4 | Birnen | Bonn | 3 | Bananen | Bonn | |||
| 5 | Birnen | Stuttgart | 2 | Birnen | Hamburg | |||
| 6 | Bananen | München | 7 | Kiwis | Köln | |||
| 7 | Kiwis | Stuttgart | 4 | München | ||||
| 8 | Birnen | München | 6 | Stuttgart | ||||
| 9 | Bananen | Hamburg | 2 | |||||
| 10 | ||||||||
| 11 | Wieviele Paletten Bananen oder Birnen stehen insgesamt z.V.? | |||||||
| 12 | 20 | (Beispiel mit Text) | ||||||
| 13 | 20 | (Beispiel mit Zellbezug) | ||||||
| 14 | 20 | Alternative mit SUMMEWENN | ||||||
| 15 | ||||||||
| 16 | Interessant ist auch diese Kurzform, insbesondere, wenn es mehr "Oder"-Kriterien sind: | |||||||
| 17 | Wieviel Paletten Birnen stehen in Stuttgart, Köln oder München zur Verfügung? | |||||||
| 18 | 8 | (Beispiel mit Zellbezug - Kurzform) | ||||||
| 19 | Durch Einsatz der Funktion MTRANS kann also auch mit einem abweichend großen Bereich ohne | |||||||
| 20 | den Operator "+" verglichen werden. Beachten Sie die geschweifte Klammer! | |||||||
| 21 | ||||||||
| Formeln der Tabelle | ||||||||||
| ||||||||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||||||||
| Matrix verstehen |
Zum Blattanfang
Mehrere Spalten addieren, wenn Bedingung erfüllt ist
Mehrere Spalten addieren, wenn Bedingung erfüllt ist
| A | B | C | D | E | |
| 1 | |||||
| 2 | Obstsorte | Menge/Paletten 1. Wahl | Menge/Paletten 2. Wahl | ||
| 3 | Äpfel | 5 | 0 | ||
| 4 | Birnen | 3 | 2 | ||
| 5 | Birnen | 2 | 4 | ||
| 6 | Bananen | 7 | 8 | ||
| 7 | Kiwis | 4 | 4 | ||
| 8 | Birnen | 6 | 8 | ||
| 9 | Bananen | 2 | 1 | ||
| 10 | |||||
| 11 | Wieviele Paletten Bananen erster und zweiter Wahl stehen z.V.? | ||||
| 12 | 18 | Lösung mit SUMMEWENN | |||
| 13 | 18 | Lösung mit SUMMENPRODUKT | |||
| 14 | |||||
| Formeln der Tabelle | ||||||
|
Zum Blattanfang
ODER in zwei Spalten - Vermeidung von Doppelzählungen
Sie möchten die Summe oder Anzahl ermitteln, wenn in verschiedenen Spalten verschiedene Kriterien anzuwenden sind. Es soll ausgewertet werden, sobald eine der Bedingungen WAHR ist.
Hier lauert die Gefahr der Doppelzählung, wenn es Zeilen gibt, in denen beide Spalten WAHR ergeben. Deshalb muss vom ermittelten ODER-Ergebnis die Summe oder Anzahl der Treffer, die in beiden Spalten WAHR sind abgezogen werden!
Hier wird nun die gleiche Aufgabe mit mit dem Spezialfilter gelöst:
Durch den Filterprozess ist eine Doppelzählung gar nicht erst möglich, somit bedarf es keines Fehlerabfangs. Der Kriterienbereich liegt in B2:E4; der Listenbereich liegt in B6:E13. Hier sehen Sie die bereits gefilterte Liste mit der TEILERGEBNIS-Auswertung in G3:
ODER in zwei Spalten - Vermeidung von Doppelzählungen
Sie möchten die Summe oder Anzahl ermitteln, wenn in verschiedenen Spalten verschiedene Kriterien anzuwenden sind. Es soll ausgewertet werden, sobald eine der Bedingungen WAHR ist.
Hier lauert die Gefahr der Doppelzählung, wenn es Zeilen gibt, in denen beide Spalten WAHR ergeben. Deshalb muss vom ermittelten ODER-Ergebnis die Summe oder Anzahl der Treffer, die in beiden Spalten WAHR sind abgezogen werden!
| A | B | C | D | E | F | |
| 1 | ||||||
| 2 | Obstsorte | Lager | Menge/Paletten | Liefertermin | ||
| 3 | Äpfel | Köln | 5 | 25.01.2007 | ||
| 4 | Birnen | Bonn | 3 | 27.01.2007 | ||
| 5 | Birnen | Stuttgart | 2 | 29.01.2007 | ||
| 6 | Bananen | München | 7 | 31.01.2007 | ||
| 7 | Kiwis | Stuttgart | 4 | 02.02.2007 | ||
| 8 | Birnen | München | 6 | 04.02.2007 | ||
| 9 | Bananen | Hamburg | 2 | 06.02.2007 | ||
| 10 | ||||||
| 11 | An wievielen Orten lagern Bananen oder Produkte, die vor dem 02.02.2007 geliefert wurden? | |||||
| 12 | 5 | Beispiel mit Datum in Formel | ||||
| 13 | Verzichtet man auf den Fehlerabfang, würde Zeile 6 doppelt gezählt, weil dort beide Kriterien WAHR sind! | |||||
| 14 | Doppelzählung vermeiden Sie auch mit diesen "ODER" Kurzformen, die Subtraktion ist dann überflüssig: | |||||
| 15 | 5 | |||||
| 16 | 5 | |||||
| 17 | ||||||
| Formeln der Tabelle | ||||||||
| ||||||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||||||
| Matrix verstehen |
Hier wird nun die gleiche Aufgabe mit mit dem Spezialfilter gelöst:
Durch den Filterprozess ist eine Doppelzählung gar nicht erst möglich, somit bedarf es keines Fehlerabfangs. Der Kriterienbereich liegt in B2:E4; der Listenbereich liegt in B6:E13. Hier sehen Sie die bereits gefilterte Liste mit der TEILERGEBNIS-Auswertung in G3:
| A | B | C | D | E | F | G | H | |
| 1 | ||||||||
| 2 | Obstsorte | Lager | Menge/Paletten | Liefertermin | Anzahl: | |||
| 3 | Bananen | 5 | ||||||
| 4 | <02.02.2007 | |||||||
| 5 | ||||||||
| 6 | Obstsorte | Lager | Menge/Paletten | Liefertermin | ||||
| 7 | Äpfel | Köln | 5 | 25.01.2007 | ||||
| 8 | Birnen | Bonn | 3 | 27.01.2007 | ||||
| 9 | Birnen | Stuttgart | 2 | 29.01.2007 | ||||
| 10 | Bananen | München | 7 | 31.01.2007 | ||||
| 13 | Bananen | Hamburg | 2 | 06.02.2007 | ||||
| 14 |
| Formeln der Tabelle | ||||
|
Zum Blattanfang
Zeilen nur auswerten bei bestimmten Kriterienkombinationen in verschiedenen Spalten
Hier bezieht sich das ODER auf die Kombination von Kriterien. Im Beispiel wird nur addiert, wenn die Kombinationen Birnen und Bonn oder Bananen und Hamburg zusammentreffen. Dazu setzen wir Arrays in der Formel ein.
Ein Array wird grundsätzlich durch geschweifte Klammern dargestellt:
Bei horizontalen Arrays, wie in unserem Fall, werden die Elemente bei der deutschen Ländereinstellung durch Punkte getrennt. Die erste Position in der ersten geschweiften Klammer wird der ersten Position in der zweiten geschweiften Klammer zugeordnet usw.
Bei vertikalen Arrays werden die Elemente bei der deutschen Ländereinstellung durch Strichpunkte getrennt. In anderen Ländereinstellungen können es andere Trennzeichen sein.
Falls Sie nicht mit der deutschen Ländereinstellung arbeiten, hilft bezüglich der Trennzeichen ein Blick in die Systemsteurung > Regions- und Sprachoptionen.
Guckst du: Matrix als Werte
Die hier eingesetzten geschweiften Klammern sind bei Formeleingabe einzutippen:
Zeilen nur auswerten bei bestimmten Kriterienkombinationen in verschiedenen Spalten
Hier bezieht sich das ODER auf die Kombination von Kriterien. Im Beispiel wird nur addiert, wenn die Kombinationen Birnen und Bonn oder Bananen und Hamburg zusammentreffen. Dazu setzen wir Arrays in der Formel ein.
Ein Array wird grundsätzlich durch geschweifte Klammern dargestellt:
Bei horizontalen Arrays, wie in unserem Fall, werden die Elemente bei der deutschen Ländereinstellung durch Punkte getrennt. Die erste Position in der ersten geschweiften Klammer wird der ersten Position in der zweiten geschweiften Klammer zugeordnet usw.
Bei vertikalen Arrays werden die Elemente bei der deutschen Ländereinstellung durch Strichpunkte getrennt. In anderen Ländereinstellungen können es andere Trennzeichen sein.
Falls Sie nicht mit der deutschen Ländereinstellung arbeiten, hilft bezüglich der Trennzeichen ein Blick in die Systemsteurung > Regions- und Sprachoptionen.
Guckst du: Matrix als Werte
Die hier eingesetzten geschweiften Klammern sind bei Formeleingabe einzutippen:
- über die Tastenkombiantion ALTGR + 7 für {
- bzw. über die Tastenkombination ALTGR + 0 für }
| A | B | C | D | E | F | |
| 1 | ||||||
| 2 | Obstsorte | Lager | Menge/Paletten | |||
| 3 | Äpfel | Köln | 5 | |||
| 4 | Birnen | Bonn | 3 | |||
| 5 | Birnen | Stuttgart | 2 | |||
| 6 | Bananen | München | 7 | |||
| 7 | Kiwis | Stuttgart | 4 | |||
| 8 | Birnen | München | 6 | |||
| 9 | Bananen | Hamburg | 2 | |||
| 10 | ||||||
| 11 | Wieviele Paletten von Birnen in Bonn und Bananen in Hamburg stehen zur Verfügung? | |||||
| 12 | 5 | Kurzform | ||||
| 13 | 5 | Gleiches Ergebnis bei aneinandergereihten SUMMENPRODUKT-Formeln | ||||
| 14 | 5 | Gleiches Ergebnis mit ODER in SUMMENPRODUKT-Formel | ||||
| 15 | ||||||
| Formeln der Tabelle | ||||||||
|
Zum Blattanfang
Zeilen auswerten nach allen möglichen ODER-Kriterienkombinationen in verschiedenen Spalten
Hier zeige ich Ihnen eine Möglichkeit auf, wie sie verschiedene ODER-Kriterien über verschiedene Spalten kombinieren können. Dazu setzen wir wieder Arrays in der Formel ein. Jeder Wert des ersten Arrays und jeder Wert des zweiten Arrays wird auf WAHR geprüft. Dabei können in den verschiedenen Arrays auch verschieden viele Kriterien genannt sein, wie in unserem Beispiel: Zwei Obstsorten, aber drei Städte.
Zeilen auswerten nach allen möglichen ODER-Kriterienkombinationen in verschiedenen Spalten
Hier zeige ich Ihnen eine Möglichkeit auf, wie sie verschiedene ODER-Kriterien über verschiedene Spalten kombinieren können. Dazu setzen wir wieder Arrays in der Formel ein. Jeder Wert des ersten Arrays und jeder Wert des zweiten Arrays wird auf WAHR geprüft. Dabei können in den verschiedenen Arrays auch verschieden viele Kriterien genannt sein, wie in unserem Beispiel: Zwei Obstsorten, aber drei Städte.
| A | B | C | D | E | F | G | H | |
| 1 | ||||||||
| 2 | Obstsorte | Lager | Menge/Paletten | |||||
| 3 | Äpfel | Köln | 5 | |||||
| 4 | Birnen | Bonn | 3 | |||||
| 5 | Birnen | Stuttgart | 2 | |||||
| 6 | Bananen | München | 7 | |||||
| 7 | Kiwis | Stuttgart | 4 | |||||
| 8 | Birnen | München | 6 | |||||
| 9 | Bananen | Hamburg | 2 | |||||
| 10 | ||||||||
| 11 | ||||||||
| 12 | Wieviel Paletten Birnen oder Bananen stehen in Stuttgart, Köln oder München zur Verfügung? | |||||||
| 13 | 15 | |||||||
| 14 | ||||||||
| Formeln der Tabelle | ||||
|
Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben
rechte seite