Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Häufige Fragen

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:
Zum Blattanfang

Mehrere ODER Kriterien in einer Spalte

 ABCDEFGH
1        
2 ObstsorteLagerMenge/Paletten Zellbereich für Kriterien: 
3 ÄpfelKöln5 ÄpfelBerlin 
4 BirnenBonn3 BananenBonn 
5 BirnenStuttgart2 BirnenHamburg 
6 BananenMünchen7 KiwisKöln 
7 KiwisStuttgart4  München 
8 BirnenMünchen6  Stuttgart 
9 BananenHamburg2    
10        
11 Wieviele Paletten Bananen oder Birnen stehen insgesamt z.V.? 
12 20(Beispiel mit Text) 
13 20(Beispiel mit Zellbezug) 
14 20Alternative 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
ZelleFormel
B12=SUMMENPRODUKT(((B3:B9="Bananen")+(B3:B9="Birnen"))*D3:D9)
B13=SUMMENPRODUKT(((B3:B9=F4)+(B3:B9=F5))*D3:D9)
B14=SUMMEWENN(B3:B9;F4;D3:D9)+SUMMEWENN(B3:B9;F5;D3:D9)
B18{=SUMMENPRODUKT((B3:B9=F5)*(C3:C9=MTRANS(G6:G8))*D3:D9)}
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

 ABCDE
1     
2 ObstsorteMenge/Paletten
1. Wahl
Menge/Paletten
2. Wahl
 
3 Äpfel50 
4 Birnen32 
5 Birnen24 
6 Bananen78 
7 Kiwis44 
8 Birnen68 
9 Bananen21 
10     
11 Wieviele Paletten Bananen erster und zweiter Wahl stehen z.V.? 
12 18Lösung mit SUMMEWENN 
13 18Lösung mit SUMMENPRODUKT 
14     

Formeln der Tabelle
ZelleFormel
B12=SUMMEWENN(B3:B9;"Bananen";C3:C9)+SUMMEWENN(B3:B9;"Bananen";D3:D9)
B13=SUMMENPRODUKT((B3:B9="Bananen")*((C3:C9)+(D3:D9)))

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!
 ABCDEF
1      
2 ObstsorteLagerMenge/PalettenLiefertermin 
3 ÄpfelKöln525.01.2007 
4 BirnenBonn327.01.2007 
5 BirnenStuttgart229.01.2007 
6 BananenMünchen731.01.2007 
7 KiwisStuttgart402.02.2007 
8 BirnenMünchen604.02.2007 
9 BananenHamburg206.02.2007 
10      
11 An wievielen Orten lagern Bananen oder Produkte, die vor dem 02.02.2007 geliefert wurden? 
12 5Beispiel 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
ZelleFormel
B12=SUMMENPRODUKT((B3:B9="Bananen")+(E3:E9<(WERT("02.02.2007"))))-SUMMENPRODUKT((B3:B9="Bananen")*(E3:E9<(WERT("02.02.2007"))))
B15=SUMMENPRODUKT(N((B3:B9="Bananen")+(E3:E9<WERT("02.02.2007"))>0))
B16{=SUMME(N((B3:B9="Bananen")+(E3:E9<WERT("02.02.2007"))>0))}
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:
 ABCDEFGH
1        
2 ObstsorteLagerMenge/PalettenLiefertermin Anzahl: 
3 Bananen    5 
4    <02.02.2007   
5        
6 ObstsorteLagerMenge/PalettenLiefertermin   
7 ÄpfelKöln525.01.2007   
8 BirnenBonn327.01.2007   
9 BirnenStuttgart229.01.2007   
10 BananenMünchen731.01.2007   
13 BananenHamburg206.02.2007   
14        

Formeln der Tabelle
ZelleFormel
G3=TEILERGEBNIS(3;B7:B13)

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:
  • über die Tastenkombiantion ALTGR + 7 für {
  • bzw. über die Tastenkombination ALTGR + 0 für }
 ABCDEF
1      
2 ObstsorteLagerMenge/Paletten  
3 ÄpfelKöln5  
4 BirnenBonn3  
5 BirnenStuttgart2  
6 BananenMünchen7  
7 KiwisStuttgart4  
8 BirnenMünchen6  
9 BananenHamburg2  
10      
11 Wieviele Paletten von Birnen in Bonn und Bananen in Hamburg stehen zur Verfügung? 
12 5Kurzform 
13 5Gleiches Ergebnis bei aneinandergereihten SUMMENPRODUKT-Formeln 
14 5Gleiches Ergebnis mit ODER in SUMMENPRODUKT-Formel 
15      

Formeln der Tabelle
ZelleFormel
B12=SUMMENPRODUKT((B3:B9={"Birnen"."Bananen"})*(C3:C9={"Bonn"."Hamburg"})*D3:D9)
B13=SUMMENPRODUKT((B4:B10="Birnen")*(C4:C10="Bonn")*D4:D10)+SUMMENPRODUKT((B4:B10="Bananen")*(C4:C10="Hamburg")*D4:D10)
B14=SUMMENPRODUKT(((B3:B9="Birnen")*(C3:C9="Bonn")+(B3:B9="Bananen")*(C3:C9="Hamburg"))*D3:D9)

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.
 ABCDEFGH
1        
2 ObstsorteLagerMenge/Paletten    
3 ÄpfelKöln5    
4 BirnenBonn3    
5 BirnenStuttgart2    
6 BananenMünchen7    
7 KiwisStuttgart4    
8 BirnenMünchen6    
9 BananenHamburg2    
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
ZelleFormel
B13=SUMMENPRODUKT((ISTZAHL(VERGLEICH(B3:B9;{"Birnen";"Bananen"};0)))*(ISTZAHL(VERGLEICH(C3:C9;{"Stuttgart";"Köln";"München"};0)))*(D3:D9))


Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben