Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1440to1444
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summenprodukt anhand 2 (oder mehr) Überschriften

Summenprodukt anhand 2 (oder mehr) Überschriften
12.08.2015 16:59:08
Joerschi
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

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

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

AW: UND (*) und ODER (+) kombiniert ...
12.08.2015 19:00:51
der
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
.. , - ...

AW: UND (*) und ODER (+) kombiniert ...
12.08.2015 20:36:20
Joerschi
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

Anzeige
AW: wir lagen beide neben der Spur ...
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
.. , - ...

Anzeige
dank Dir:Muss ich schauen,ob mit Hilfszeilen mgl
13.08.2015 15:48:49
Joerschi
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

Anzeige
AW: wir lagen beide neben der Spur ...
13.08.2015 20:58:29
Joerschi
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

AW: Du schreibst in Deinem vorherigen Beitrag ...
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
.. , - ...

Anzeige
AW: Du schreibst in Deinem vorherigen Beitrag ...
14.08.2015 16:24:32
Joerschi
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

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

Jetzt mit Skizze + Musterdatei...
17.08.2015 20:18:38
Joerschi
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

Anzeige
AW: dazu ...
18.08.2015 09:34:07
...
Hallo Joerschi,
... melde ich mich später noch einmal. Momentan komme ich nicht dazu.
Gruß Werner
.. , - ...

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

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige