Folgethread: Summenprodukt anhand 2+ Überschriften

Bild

Betrifft: Folgethread: Summenprodukt anhand 2+ Überschriften
von: Joerschi
Geschrieben am: 21.08.2015 12:49:57

Fortfolge von nachfolgenden Link/Thread und Fragestellung, da der "alte" Thread mittlerweile geschlossen ist.
https://www.herber.de/forum/archiv/1440to1444/t1442040.htm
@ neopa
Es ist ähnlich wie das "kleinere" Problem heute morgen.
Es soll am Ende diejenige Anzahl von Zeilen (aus der Ausgangstabelle) bestimmt werden, wo alle Mindestvorgaben (Zahlenwerte) stimmen.
Allerdings ist die "erhöhte" Schwierigkeit, dass je Vorgabe zusätzlich die Spaltenüberschriften beachtet werden müssen, damit eine richtige Zuordnung möglich ist.
Konkret am Beispiel Vorgabe-Tab1:
In Zelle D3 ist die Anzahl Zeilen gesucht, wo gleichzeitig
a) in der Datentabelle in der Spalte mit Überschrift "X2" die Werte größergleich 5,0 sind (Vorgaben in D1 und D2)
b) in der Datentabelle in der Spalte mit Überschrift "X1" die Werte größergleich 2,0 sind (Vorgaben in A3 und B3)
(alle anderen Spalten werden in der Betrachtung ignoriert)
Ehe ich es nachschieße: in L1 und M1 wird wieder eine fixe Vorgabe gemacht. Also die Einträge in der Datentabelle müssen exakt stimmen.
Grün markiert sind die richtigen Vorgaben, umrahmt die Anzahl der Zeilen, wo ALLES passt:
Userbild
also 9 Zeilen (Ergebnis der Zelle D3 ist 9).
Unten die überarbeitete Musterdatei dazu.
Wichtig ist, dass die Vorgaben wie a) und b) oben auch beliebig erweitert werden können (oder zumindest bis 10 Stück).
Hilfszeilen sind diesmal "kontrapoduktiv", da in der finalen Tabelle die Anzahl sofort in die Tausende gehen würde.
Hilft das in der Erklärung etwas weiter?
Beste Grüße, Joerschi
Musterdatei: https://www.herber.de/bbs/user/99756.xlsx

Bild

Betrifft: AW: jetzt ist klarer, trotzdem nachgefragt ...
von: ... neopa C
Geschrieben am: 21.08.2015 13:47:01
Hallo Joerschi,
... und zwar:
1.) die Daten in der Datentabelle A:J sind je Spalte stets aufwärts sortiert wie im Beispiel oder doch wohl eher wild durcheinander?
2.) Wie viele Datensatze und wie viele Spalten können es denn max werden?
Ich befürchte nämlich fast, dass eine Auswertung zumindest für mehr als zwei Vorgaben dann mit einer Formellösung (so ich denn eine mögliche finden sollte, doch heute wahrscheinlich eher nicht) den PC evtl. zum "Glühen" bringen könnte.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: jetzt ist klarer, trotzdem nachgefragt ...
von: Joerschi
Geschrieben am: 21.08.2015 13:57:35
gern doch:
a) ja, sind wild durcheinander. Im beispiel hatte ich es mir einfach gemacht.
b) die Zieltabelle hat 300 Spalten zu je 2000 Zeilen...
Das mit dem Glühen kann man nur probieren :-) (32 GB RAM)
Wenn gar nichts anderes geht, müsste man es wohl doch über Hilfsspalten machen. Ist halt fraglich, ob er da nicht genauso abhebt.
Dank Dir für den Aufwand!
Viele Grüße, Joerschi

Bild

Betrifft: AW: zusätzlich mit MMULT() ...
von: ... neopa C
Geschrieben am: 21.08.2015 15:19:51
Hallo Joerschi,
... dann teste zunächst mal in der Tabelle Vorgabe-Tba1 und da folgende Formel. Dann sehen wir weiter.

 ABCDE
1  X1X2X3
2  2,05,03,0
3X12,0 99
4X25,09 9

Formeln der Tabelle
ZelleFormel
C3=WENN($A3=C$1;"";SUMMENPRODUKT((MMULT((Datentabelle!$A$2:$J$49>=C$2)*(Datentabelle!$A$1:$J$1=C$1)+(Datentabelle!$A$2:$J$49>=$B3)*(Datentabelle!$A$1:$J$1=$A3); ZEILE(A$1:A$10)^0)=2)*(Datentabelle!$L$2:$L$49=Datentabelle!$L$1)*(Datentabelle!$M$2:$M$49=Datentabelle!$M$1)))


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

Bild

Betrifft: AW: zusätzlich mit MMULT() ...
von: Joerschi
Geschrieben am: 21.08.2015 15:32:52
Das sieht schon mal sehr gut aus :-)
Funktioniert im Test (und auch mit anderen Werten). Auf die "große" Livetabelle habe ich es jetzt aber auch noch nicht rüber gezogen.
Was hat es mit dem Ausdruck

ZEILE(E$1:E$10)^0

auf sich?
Ist das die Anzahl der Datenspalten?
Die 3-Vorgaben-Variante sieht ähnlich aus?
Viele Grüße
Jörg

Bild

Betrifft: AW: ja, denn das ergibt eine Matrix von 1en, ...
von: ... neopa C
Geschrieben am: 21.08.2015 15:45:32
Hallo Joerschi,
... die für die MMULT()-Teilformel benötigt wird und in der Menge der 1en der Anzahl der Spalten der auszuwertenden Datenmatrix entsprechen muss. Im Beispiel deshalb ZEILE(Z1:Z10)^0.
Für mehr als zwei Vorgaben schaue ich mir erst noch an, sollte aber im Prinzip analog funktionieren.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ja, denn das ergibt eine Matrix von 1en, ...
von: Joerschi
Geschrieben am: 21.08.2015 20:54:52
hmm, bastle grad und versuche zu "verstehen".
Die "=2", weil es 2 Kriterien sind?
LG, Joerschi

Bild

Betrifft: AW: ja, denn das ergibt eine Matrix von 1en, ...
von: Joerschi
Geschrieben am: 21.08.2015 22:55:19
Hi Werner,
die Formel an sich zu adaptieren ist (wohl) nicht allzu schwer, aber mit dem Teil

ZEILE(A$1:A$10)^0)=2)

stehe ich auf Kriegsfuß.
Ständig bekomme ich eine #Wert-Fehlermeldung.
In meiner "realen" Auswertungstabelle befinden sich die Formeln im Bereich von D4:EQ147.
In Spalte B4&C147 sowie Zeile C2&EQ3 befinden sich die Vorgabewerte wie im Beipsiel beschrieben.
Bedeutet, meine Adaption von obigen Vorschlag von Dir müsste (hoffentlich) lauten:
"ZEILE(C$2:C$145)^0)=2)"
Klappt aber nicht. Irgendwo hängt es oder ich habe falsch adaptiert:-(.
Weißt Du, wo genau?
LG; Joerschi

Bild

Betrifft: selbst gelöst, trotzdem nachgefragt "=2" ?
von: Joerschi
Geschrieben am: 22.08.2015 08:27:43
meine letzte Frage hat sich erledigt.
Erstes Mal mmult verwendet und statt die Matriz der Datentabelle jene der Auswertungstabelle verwendet. Klar, dass das nicht klappen kann.
Aber dennoch zum Verständnis - was hat es mit dem "=2" auf sich?
(bekomme zwar jetzt Werte, aber die weichen immer noch leicht vom richtigen Ergebnis ab. Daher denke ich, in dem Part "=2" habe ich noch nicht richtig adaptiert)
Viele Grüße
Joerschi

Bild

Betrifft: AW: weil 2 Bedingungen geprüft werden ...
von: ... neopa C
Geschrieben am: 22.08.2015 09:21:07
Hallo Joerschi,
... mit der Formel innerhalb von MMULT() werden (...) + (...) zusammengeführt, die beide zutreffen müssen. Somit also nur bei WAHR + WAHR = 2 als Ergebnis ergibt, welches wiederum damit Voraussetzung für die übergeordnete Bedingungsabfrage ist.
Gruß Werner
.. , - ...

Bild

Betrifft: 3 Bedingungen jetzt auch adaptiert
von: Joerschi
Geschrieben am: 22.08.2015 10:48:04
war gar nicht mehr schwer, wenn Prinzip einmal klar.
Danke Dir noch einmal ausdrücklich für Deine Hilfe :-)
Liebe Grüße, Joerschi
PS: yup, der PC raucht tatsächlich. Habe nun die Anzahl der Auswertungstabellen-Berechnungen von 23700 Formeln (in der Datentabelle mit je 300 Spalten x 2000 Zeilen) runtergeschraubt auf 4000. Dann ist es verkraftbar mit einem Kaffee daneben :-)

Bild

Betrifft: AW: damit hatte ich gerechnet ...
von: ... neopa C
Geschrieben am: 23.08.2015 08:23:30
Hallo Joerschi,
... aber nicht nur, dass Du für die Auswertung dann etwas Geduld benötigst, sondern auch dass du Die Lösung für 3 Bedingungen nun selbst schaffen kannst. Dies ist fast immer die befriedigendste Lösung.
Nun nur noch nachgefragt. Damit sind alle bisher offene Fragen inklusive der aus der PN abgehakt?
Bin dann gleich wieder offline und erst Morgen wieder online. Schönen Sonntag dann noch.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: damit hatte ich gerechnet ...
von: Joerschi
Geschrieben am: 23.08.2015 09:13:41
ja, jetzt alles (vorerst) abgehakt. Bis zur nächsten Frage dann :-)
Die Dupletten-Geschichte konnte ich mit einer Adaption Daniels Lösung selbst hinbiegen, so dass nun auch 5 oder 10 verschiedene Nummernkombinationen leicht als Dupletten erkannt werden und aussortiert.
Viele liebe Grüße
Joerschi
PS: Es braucht dringend leistungsstärkere PC´s auf dem Markt.
Hoffentlich hält die Ankündigung von Intel, was sie verspricht...
http://business.chip.de/news/1.000-mal-schneller-als-Flash-Neuer-Intel-Speicher-haengt-SSDs-locker-ab_81515742.html

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Folgethread: Summenprodukt anhand 2+ Überschriften"