Summenprodukt anhand 2 (oder mehr) Überschriften

Bild

Betrifft: Summenprodukt anhand 2 (oder mehr) Überschriften
von: Joerschi
Geschrieben am: 12.08.2015 16:59:08

Hallo liebes Forum,
Folgendes Problem möchte ich lösen und komme über den Ansatz nicht hinaus (leider keine Musterdatei,da vom Handy aus geschrieben... Pc Inet kaputt :-( ).
Eine Datentabelle a1:j27 hat von a1:j1 die Überschriften X1...X10 und ausschließlich Zahlendaten befinden sich darunter im Wertebereich a2:j27.
Aufgabe:
Es soll per Summenprodukt (bzw per Formel!) die Werte aus zwei Spalten ausgelesen werden, von denen aber nur die Überschrift bekannt ist.
Dazu ist in Zelle L2 und L3 der Nsme der Überschrift manuell vorgegeben (z.b. X2 und X5) sowie Zelle M2 und M3 je ein manueller Mindest-Zahlenwert dazu. Gefunden werden soll (wie bei Summenprodukt üblich) die Anzahl der Zeilen, wo die Werte je Spalte die gesetzten Bedingungen gleichzeitig erfüllen (also im Beispiel die Werte der per Überschrift definierten Spalte größer als Vorgabe sind).
Mein Ansatz klappt allerdings nur bei einer unbekannten Spaltenüberschrift:


Summenprodukt((a1:j1=L2)*(a2:j27>m2))

Diese Formel kann man aber leider nicht so fortführen bei zwei oder mehr unbekannten Spaltenüberschriften...
(Also so:

Summenprodukt((a1:j1=L2)*(a2:j27>m2)*(a1:j1=L3)*(a2:j27>m3)) 
).
Hätte jemand einen Lösungsvorschlag?
Optimal wäre eine Lösung, wo man auch einfach auf drei oder mehr unbekannte Überschriften ausweiten kann.
Danke vorab und liebe Grüße
Joerschi

Bild

Betrifft: Ergänzung
von: Joerschi
Geschrieben am: 12.08.2015 17:06:06
Eine Lösung mit Summenprodukt wäre deswegen interessant, weil es auch noch weitere Fixe Spalten gibt, in denen nach Bedingungen durchsucht wird.

Bild

Betrifft: AW: UND (*) und ODER (+) kombiniert ...
von: der neopa C
Geschrieben am: 12.08.2015 19:00:51
Hallo Joerschi,
... wieder auf "Pirsch" ? ;-)
Hier ist teilweise ODER in Form von + gefragt:
für z.B. drei Vergleichsspalten so:

=SUMMENPRODUKT(((A1:J1=L2)+(A1:J1=L3)+(A1:J1=L4))*(A2:J27>M2)*(A2:J27>M3)*(A2:J27>M4))
Gruß Werner
.. , - ...

Bild

Betrifft: AW: UND (*) und ODER (+) kombiniert ...
von: Joerschi
Geschrieben am: 12.08.2015 20:36:20
Hallo Werner :-)
Ja, sind am Strand und ohne Gehirnjogging geht's nicht. :-)
Zu Deinem Vorschlag: erstmal herzlichen Dank.
Liefert aber scheinbar nicht das richtige Ergebnis (oder habe es ungenau erklärt).
Am Beidpiel: wenn man in allen Spalten von Zeile 2 bis 27 aufsteigend die Zahlen 1 bis 26 einsetzt und beide Vorgaben auf >3 setzt, wäre das gewünschte Ergebnis 23 ( da alle Zeilen ab Zeile 5 größer als 3 sind).
Anhand Deiner Formel kommt das Ergebnis 49 :-(
Dies habe ich adaptiert:


Summenprodukt((a1:j1=l2)+(a1:j1=l3)*(a2:j27>m2)*(a2:j27>m3))

l2 und l5 waren "x2" und "x5".
Irgendwo hakt es noch...
LG
Joerschi

Bild

Betrifft: AW: wir lagen beide neben der Spur ...
von: ... neopa C
Geschrieben am: 13.08.2015 14:21:03
Hallo Joerschi,
... als erster ich. Hab meine Formel gestern Abend ohne Testung eingestellt. Du hast diese mit einer anderen Klammersetzung übernommen. Doch selbst bei einer richtigen Klammersetzung würde nicht das gewünschte Ergebnis erbringen. Sorry.
Nachfolgend nun eine neuer Ansatz auf Basis einer Hilfsspalte. Deren Formel ist gleich so konstruiert, dass Du beliebig die Anzahl der Bedingungen erweitern kannst ohne Formeländerungen vorzunehmen. Wenn es wieder etwas kühler werden sollte, könnte ich auch besser denken und die Hilfsspalte noch einsparen. Mittels zusätzlichem Einsatz von MMULT() sollte das eigentlich realisierbar sein. Aber Dir dürfte es egal sein. Hauptsache eine Lösung. Oder?

 ABCDEFGHIJKLMNOP
1X1X2X3X4X5X6X7X8X9X10 Bedingungen Ergebnis:2
2113031417661808191101 X226  2
3122932427562798292102 X442  3
4132833437463788393103 X773  4
5142734447364778494104 X991  4
6152635457265768595105     3
7162536467166758696106     3
8172437477067748797107     3
9182338486968738898108     2
263565565528556105115125     2
273655666518655106116126     2
28                

Formeln der Tabelle
ZelleFormel
P1=SUMMENPRODUKT(N(P2:P27=ANZAHL(M2:M11)))
P2=SUMMENPRODUKT((A$1:J$1=L$2:L$11)*(A2:J2>M$2:M$11))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...


Bild

Betrifft: dank Dir:Muss ich schauen,ob mit Hilfszeilen mgl
von: Joerschi
Geschrieben am: 13.08.2015 15:48:49
Hi Werner,
Zuerst einmal wieder besten Dank.
Muss ich heute Abend schauen, ob ich das so adaptieren kann, da Hilfszeilen das bestimmt extrem aufblähen.
Mögliches "Problem": Die tatsächlichen Bedingungen und Vorgabewerte in meiner "realen" Tabelle sind ebenfalls tabellarisch angeordnet (hatte das Problem hier wieder stark vereinfacht).
Zum besser vorstellen, wie die Vergleichstabekle aufgebaut ist: von c1:az1 steht X1, X2 usw
Darunter von c2:az2 der jeweilige Vorgabewert.
Genau das gleiche nochmal vertikal: von a3:a51 wieder X1, X2 usw
Von b3:b51 die gleichen Vorgabewerte wie in c2:c100.
Will man also die Bedingung X1 und X2 verglichen, steuert man zum Beispiel dafür die Zellen a3 (mit Vorgabe in b3) und für die zweite Vorgabe d1 (mit Vorgabewert in d2) an.
(Bitte ignorieren, dass es dadurch Dopplungen in der Berechnung gibt - in der tatsächlichen Berechnung muss das so sein.)
Jeder einzelne Wert der Vergleichstabelle wird also eine Berechnung sein wie In Deiner Lösung die Zelle P1.
Es wird also immer tabellarisch verglichen und bei 50*50 Vergleichen macht das 2500 Hilfszeilen, bei einer dritten Bedingung hinzu nochmal *50 usw. :-(
Aber wie gesagt: das sind erstmal die ersten Gedanken vom Handy aus. Ich melde mich dazu.
Liebe Grüße
joerschi

Bild

Betrifft: AW: wir lagen beide neben der Spur ...
von: Joerschi
Geschrieben am: 13.08.2015 20:58:29
Hi Werner,
Grad wild getestet. Wie schon vermutet - mit Hilfszeile bekomme ich in diesem einen Fall leider nicht dargestellt :-(
Falls Du irgendwann noch die Version mit MMULT() ertüfteln könntest, wäre ich Dir sehr dankbar.
Aber wie Du Zeit findest.
falls wenig Zeit, würde ich die Frage sonst wieder auf offen stellen.
Dank Dir wie immer und beste Grüße
Joerschi

Bild

Betrifft: AW: Du schreibst in Deinem vorherigen Beitrag ...
von: ... neopa C
Geschrieben am: 14.08.2015 13:25:17
Hallo Joerschi,
... dass "die tatsächlichen Bedingungen und Vorgabewerte in meiner "realen" Tabelle sind ebenfalls tabellarisch angeordnet" . Wie sieht denn dies genau aus?
Vielleicht solltest Du doch erst eine Datei einstellen oder diese mir auch direkt zukommen lassen. Dann sehen wir weiter. Ok?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Du schreibst in Deinem vorherigen Beitrag ...
von: Joerschi
Geschrieben am: 14.08.2015 16:24:32
Hallo Werner,
So machen wir es (glaube ich gern, dass das aus meiner vorherigen Textbescgreibung nicht einfach hervorgeht).
Wird aber vorr erst Sa in einer Woche, da ich hier am Meer partout nicht mein pc-Internet zum laufen bekomme.
Falls es doch noch klappt, dann früher :-).
Liebe Grüße
joerschi
Ps: vielleicht reaktiviere ich derweil eine "erweiterte" Lösung eines schon bearbeiteten Problems. Da habe ich auch noch Fragen. Mal schauen

Bild

Betrifft: AW: Deine Mail erhalten ...
von: ... neopa C
Geschrieben am: 16.08.2015 08:51:32
Hallo Joerschi,
... schau es mir später an.
Gruß Werner
.. , - ...

Bild

Betrifft: Jetzt mit Skizze + Musterdatei...
von: Joerschi
Geschrieben am: 17.08.2015 20:18:38
Hi Werner,
INet jetzt hinbekommen :-) (die Lösung treibt mich ja um, schlimm sowas :-) )
Wie versprochen hier die Skizze der Auswertungstabelle sowie eine Musterdatei dazu.
Einmal mit zwei zu vergleichenden Kriterien wie oben beschrieben (Zeilen-/Spaltenanzahl nur etwas kleiner skaliert wegen Dateigröße):
Userbild
Und einmal, wenn es dann z. B. drei Vorgabekriterien werden (diese sind in Zeile 3 und 4 grün markiert):
Userbild
Hinweis: Die "?" stellen die späteren Formelfelder dar.
Leerzellen sind schon so verformelt, so dass bei gleichen Namen der Vorgaben eine leere Zelle angezeigt wird.
Musterdatei: https://www.herber.de/bbs/user/99654.xlsx
Kannst Du damit was anfangen?
Beste Grüße
Joerschi

Bild

Betrifft: AW: dazu ...
von: ... neopa C
Geschrieben am: 18.08.2015 09:34:07
Hallo Joerschi,
... melde ich mich später noch einmal. Momentan komme ich nicht dazu.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu ...
von: Joerschi
Geschrieben am: 18.08.2015 15:31:29
Wie Du es schaffst :-)
Dank Dir.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Summenprodukt anhand 2 (oder mehr) Überschriften"