Office: (Office 2010) Summieren mit mehreren Bedingungen

Guten Tag liebe Community,

ich bin neu hier und suche Rat zu einem Excel-Problem. Eine berufliche Veränderung hat zur Folge, dass ein Großteil meines Arbeitstages aus Excel-Arbeiten besteht. Ich habe mich bereits viel umgesehen, und den Standard kann ich ganz gut anwenden.

Nun plage ich mich allerdings schon seit 2 Wochen mit einem Thema, und dachte vlt. kennt ein Profi einen Lösungsansatz.

Aufgabe:
In einer Liste sind 3 Spalten mit Selektionskriterien (Menge, Größe, Farbe), und 1 Spalte mit einem Preis. Mit einer Formel sollen die Preise addiert werden, wenn die 3 Kriterien pro Zeile erfüllt sind. Besonderheit: Es sind mehrere Kriterien pro Spalte zulässig.
In einem anderen Blatt stehen die Kriterien, die pro Spalte erfüllt sein müssen.
Bsp.:
Spalte Farbe: 7000, 8000
Spalte Menge: 2100, 2200
Spalte Größe: 2, 3
Ich möchte nun mit einer Formel die Preise summieren, die den Kriterien entsprechen (wenn jeweils eine der Bedingungen der Spalte erfüllt wird, aber für alle 3 Spalten).

Mein Ansatz:
Mit SUMMEWENNS im Array gebe ich die Kriterienwerte pro Spalte mit. Ich kann (angeblich) sogar mehrere Kriterienwerte pro Spalte angeben.

Problem:
Mehrere Kriterienwerte akzeptiert Excel jedoch nur bei einer Spalte (Kriterienbereich). Bei allen weiteren geht nur 1 Kriterium. Mein Workaround war zunächst, pro Kriterienblock einen SUMMEWENNS-Block aufzumachen. Das klappt, wenn pro Kriterienblock nur 2 Kriterien gewählt werden. Danach
wird die Formel nicht mehr händelbar bzw. die Terme der Formel exponieren sich.

Ich bin sicher, dass es einen charmanten Ansatz dafür gibt und wäre dankbar wenn mir jemand den Weg weist.
Viele Grüße,
Alex

:)

 

Hallo,

wenn ich die Preise der Zeilen addiere, bei denen mindestens eine Bedingung erfüllt ist, dann komme ich auf 0,7 anstatt auf 1,4. Allerdings verstehe ich auch nicht, was es bringt, verschiedene Preise zu addieren.

 

Hallo,
es muss immer 1 Bedingung pro Spalte erfüllt sein, d.h. ein gültiger Preis muss gültige Bedingungswerte aller 3 Spalten enthalten.

In der Excelliste habe ich die gültigen Bedingungen rot markiert, allerdings erfüllen nur die 4 letzten Zeilen alle 3 Bedingungen, die 1. Zeile nicht.

 

hallo, meinst du so..?

PHP:

         =SUMMENPRODUKT(((B2:B7=L2)+(B2:B7=L3))*((C2:C7=M2)+(C2:C7=M3))*((D2:D7=N2)+(D2:D7=N3));E2:E7)


 

Hallo Frank,

vom Ergebnis her genauso. Ich habe darüber hinaus noch versucht, die Anzahl der Bedingungen zu variabilisieren. Im Bsp. gab es pro Spalte je 2 Bedingungen, wie sieht das ganze mit je 5 aus? Oder 10? Da wird die Formel nicht mehr händelbar.
VG,

 

Hallo Frank,

vielen Dank für deine Hilfe!

Ich habe die Formel jetzt mal auf das Minimum der möglichen Kriterien angewendet:
Code:

Sry für die unleserliche Form, die Formel arbeitet blattübergreifend variabel. Daher die vielen Indirekt.

1. Frage: Gibt es irgendeine Möglichkeit, dass zu optimieren/variabilisieren? So dass die Vergleichswerte in einem Zellbereich (Spalte) liegen und ich nicht jede Zelle (hier: B11 bis B18, C9 bis C10, D19) einzeln angegeben muss? Dann könnte ich noch mehr Kriterien aufnehmen ohne völlig den Überblick zu verlieren (z.B. D19 bis D25).
Meine bisherige Formel:
Code:

ist etwas schlanker, weil sie zumindest für den ersten Kriterienbereich noch eine Werteliste (B11:B18) akzeptiert. Für den Rest leider nicht mehr.

2. Frage
Wie kann ich die addierten Werte zählen? Ich habe bisher mit der ZählenWENNS Formel gearbeitet:
Code:

Auch das funktioniert, und man kann den ersten Kriterienbereich als Range angeben. Alle weiteren Kombinationen jedoch wieder einzeln.

Ich kann auch nochmal eine neue Bsp.datei fertig machen, wenn das hilft.

VG,
Alex

 

Hallo Alex,

ich habe mich nun noch einmal mit deiner Fragestellung bezüglich der größtmöglichen Abfrageflexibilität beschäftigt und kam zum Ergebnis, dass du diese am einfachsten mit der Funktion DBSUMME() erreichen kannst.

Da ich diese Funktion eigentlich nie benutze, musste ich mich nun erst einmal etwas schlau machen, um diese Funktion anwenden zu können.

Beiliegende Mappe zeigt dir nun, wie einfach es damit gehen kann. Es gibt dabei allerdings die Tücke, dass es bei den Abfragekriterien keine Leerzeilen dazwischen und auch nicht am Ende geben darf. Deshalb wird der Kriterienbereich in der Formel dynamisch gebildet.

Im Beispiel habe ich für die Menge 2 Abfragespalten vorgesehen. Somit kannst du auch von/bis-Bereiche (>=2000

 

Hallo WS-53,

vielen Dank fürs Anschauen. Das ist ein ganz neuer Lösungsansatz, der eine sehr schmale Formel erlaubt.

Dafür benötigt Excel nun eine Kriterienmatrix, in der alle erlaubten Kombinationen zeilenweise notiert werden.
Habe ich also in jeder der 3 Kriterien-Spalten 5 mögliche Werte/Bedingungen stehen, bedeutet das eine Matrix von 125 Zeilen zu schreiben. Bei je 10 wären es 1.000 Zeilen.
Habe ich den Ansatz richtig verstanden?

VG,
Alex

 

Hallo Alex,

wenn in deinem Datenpool bspw. 10 verschiedene Farben und 10 verschiedene Größen vorkommen und im Ergebnis möchtest du zu 6 Farben und 7 Größen berücksichtigt wissen, dann sind dies 6*7 = 42 Abfragezeilen. Insofern hast du dies richtig verstanden.

Aber wenn häufig so umfangreiche Abfragen hast und die Anzahl nicht durch von-/bis-Bereiche reduzieren kannst, dann könnte der Umfangreiche Kriterienbereich natürlich auch mittels eines Makros aus deiner reduzierten Erfassung (an anderer Stelle) generiert werden.

Ich habe gerade noch einmal getestet und komme zu dem Ergebnis, dass es wohl besser wäre, wenn das horizontale Und als ODER und das vertikale ODER als UND interpretiert würden.

Das entspräche auch eher dem, was ich von meinen Datenbankabfragen auf dem ERP-System gewohnt bin.

 

Hallo Alex,

ich habe noch etwas recherchiert und im Ergebnis das Ganze um etwas VBA-Code angereichert.

Bezüglich der Abfrageflexibilität ist es nun näherungsweise die eierlegende Wollmilchsau.

In der beiliegenden Mappe kannst du nun für die Berechnung mit DBSUMME() deine Abfragekriterien nach der neuen Art von L6:O33339 eintragen. oder auf der von dir vermutlich bevorzugten Art in Q6:S38 eintragen.

Wenn du deine Abfragekriterien hier eingetragen hast, dann musst du anschließend nur die Taste drücken, die mit "Abfragmatrix füllen" betitelt ist. Per Makro werden dann alle sich aus deiner Eingabe ergebenden Kombinationen gebildet und in den Bereich L6:O33339 eingestellt.

 

Hallo Alex,

DBANZAHL anstatt DBSUMME ansonsten identischer Aufbau der Formel.

 

Hallo WS-53,

vielen Dank für die Erklärungen und Mühen. Es hat nun doch eine ganze Weile gedauert, weil ich die Lösungsvorschläge immer nochmal nachbaue. Das hilft mir, die Lösung auch im Detail zu verstehen und nicht nur ein paar Formeln zu übernehmen.

VG,
Alex