Wert aus Matrix suchen

Bild

Betrifft: Wert aus Matrix suchen
von: Stefan
Geschrieben am: 26.06.2015 12:33:59

Hallo liebe Excel Freunde,
ich habe mal wieder ein Excel-Problem.
In einer Matrix ist in unterschiedlichen Spalten ein "x" zu finden.
Ich möchte das so verformeln, dass wenn ein "x" auftaucht,
das dazugehörige Datum (was auch in der Matrix an anderer Stelle steht)
ausgegeben wird. (Bezug zu einer anderen Zelle)
Das komplexe daran ist, (zumindest für mich)
- Es sind mehrere "x" möglich
- Es sollen keine Leerzeilen auftauchen
- Die Datumswerte sollen fortlaufend untereinanderstehen
Dazu habe ich folge Tabelle vorbereitet und mit hochgeladen.
https://www.herber.de/bbs/user/98505.xlsx
Kann mir dazu jmd. Hilfestellung leisten ?
Viele Grüße
Stefan

Bild

Betrifft: dazu wäre erst noch zu klären ...
von: der neopa C
Geschrieben am: 26.06.2015 13:27:00
Hallo Stefan,
... was ist, wenn mehrere "x" einem Datum zugeordnet sind? Wie soll dann verfahren werden? Nur einmal das Datum oder genau so oft wie es "x" in der Zeile gibt?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu wäre erst noch zu klären ...
von: Stefan
Geschrieben am: 26.06.2015 17:11:51
Hallo Werner,
dann sollen genauso viele Datum angezeigt werden wie es "x" gibt.
Viele Grüße
Stefan

Bild

Betrifft: meine Frage hätte ich mir ersparen können...
von: der neopa C
Geschrieben am: 26.06.2015 20:40:50
Hallo Stefan,
... wenn ich in Deiner Tabelle weiter nach unten gescrollt hätte :-( Sorry.
Unten steht ja gut und genau aufgezeigt wie Du Du es haben möchtest. :-)
In diese Deine händische Ergebnistabelle habe ich nun meine Formellösung eingebaut. Zwei Formeln sind dazu notwendig, mit jeweils einer benutzerdefiniertes Zahlenformat.
- Die Formel C40 mit dem benutzerdefiniertes Zahlenformat TTTT nach unten ziehend kopieren.
- Die Formel D40 mit dem benutzerdefiniertes Zahlenformat TT.MM.JJJJ;; (beachte 2 abschließende Semikoli) nach unten und rechts ziehend kopieren:

 CDEFGH
40Freitag 02.01.2015   
41Montag    05.01.2015
42Dienstag    06.01.2015
43Donnerstag   08.01.2015 
44Dienstag13.01.2015    
45Mittwoch14.01.2015    
46Donnerstag 15.01.201515.01.201515.01.201515.01.2015
47Freitag  16.01.2015  

Formeln der Tabelle
ZelleFormel
C40=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(B$1:$B$33)/(D$1:$H$33="x")/ISTNV(VERGLEICH(B$1:B$33;J$7:J7;)); 1)); "")
D40=(SUMMENPRODUKT((D$3:D$33="x")*($B$3:$B$33=$C40))=1)*$C40


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

Bild

Betrifft: Ich hatte das so gelöst, wobei meine C40.C47-...
von: Luc:-?
Geschrieben am: 26.06.2015 22:23:50
…(Matrx-)Fml wohl kürzer ist als Werners, dafür die andere dann umso länger, Stefan… ;-]

 CDEFGH
39 F1F2F3F4F5
40Freitag 02.01.2015   
41Montag    05.01.2015
42Dienstag    06.01.2015
43Donnerstag   08.01.2015 
44Dienstag13.01.2015    
45Mittwoch14.01.2015    
46Donnerstag 15.01.201515.01.201515.01.201515.01.2015
47Freitag  16.01.2015  
48C40:C47: {=INDEX(C3:C33;KKLEINSTE(WENN(D3:D33&E3:E33&F3:F33&G3:G33&H3:H33="";"";ZEILE(1:31));ZEILE(1:8)))}
49D40:H47: {=WENN(INDEX(D3:H33;KKLEINSTE(WENN(D3:D33&E3:E33&F3:F33&G3:G33&H3:H33≠"";ZEILE(1:31));ZEILE(1:8));SPALTE(A:E))="x";
50                    INDEX(B3:B33;KKLEINSTE(WENN(D3:D33&E3:E33&F3:F33&G3:G33&H3:H33≠"";ZEILE(1:31));ZEILE(1:8)));"")}
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: aber ...
von: der neopa C
Geschrieben am: 27.06.2015 13:44:30
Hallo Luc,
... mein Lösungsvorschlag kommt aber ganz ohne MATRIXFormel aus und ist schon deswegen effektiver ;-)
Gruß Werner
.. , - ...

Bild

Betrifft: Was effektiver ist, ist nur b.großer Datenmenge...
von: Luc:-?
Geschrieben am: 27.06.2015 14:18:53
…wirklich beurteilbar, Werner,
denn die alten Xl-Fktt haben alle irgendwo historisch bedingte Grenzen, die MS wohl nicht erweitert hat. Wenn eine Container-Fkt wie AGGREGAT da keine spezielle, erweiternde Routine mitbringt, gelten die dann auch dafür.
Ob MatrixFml oder nicht, ist dabei kein entscheidendes Kriterium, denn diese Form teilt Xl nur mit, ob mit der ganzen Matrix oder nur Teilen davon gerechnet wdn soll, denn im Prinzip scheint Xl generell die ganze Matrix zu benutzen (bzw zu erkennen). Wichtig ist eher, ob unnötigerweise mit ganzen Matrizen (was uU bestimmte Grenzen überschreiten könnte) gerechnet wird, wenn auch nur Teile von ihnen, wie das bei INDEX-Verwendung der Fall sein dürfte, zur ErgebnisErmittlung ausreichen würden.
Sicher ist nur, dass deine (gefragte) 2.Fml kürzer ist… ;-)
(Was PH von solchen Diskussionen hält, weißt du ja…)
Gruß, Luc :-?

Bild

Betrifft: AW: ich glaube, da irrst du, Werner
von: Daniel
Geschrieben am: 27.06.2015 14:33:52
eine Matrixformel liegt immer dann vor, wenn Excel zur Berechnung der Formel einen Zellbereich in einer Schleife durchlaufen und mit jeder Einzelzelle dieses Bereichs weitere Berechungen anstellen muss um zum Ergebnis zu kommen.
dh sobald eine funktionierende Formel einen Ausdruck wie diesen enthält: D$1:$H$33="x" hast du eine Matrixformel.
jetzt gibt des Formeln, die diesen "Matrix-Berechnungsmodus" automatisch auslösen (Summenprodukt, Aggregat) und Formeln, die das nicht automatisch machen und man dann Excel explizit mitteilen muss, dass es eine Matrixformel sein soll (durch Abschluss der Eingabe mit STRG+SHIFT+ENTER).
dh auch wenn du kein STRG+SHIFT+ENTER eingeben musst, kann deine Formel trotzdem eine Matrixformel sein.
Gruß Daniel

Bild

Betrifft: Im Prinzip richtig; d.Begriff 'Matrixformel' ...
von: Luc:-?
Geschrieben am: 27.06.2015 15:45:04
…ist aber eindeutig definiert, Daniel,
und meint genau diesen „Anstoß von außen“ (Xl-Steuerung). Eine Funktion, die ganze Matrizen verarbeiten kann (wie die meisten Standard-XlFktt) begründet keine Matrixformel, sondern höchstens die „Wernersche Matrix(formel)­funktionalität“. Sie sind also strenggenommen Matrixfktt*, auch wenn sie lt ihrer Kategorie anders eingeordnet wurden. Die begriffliche Unsicherheit entsteht durch die Existenz vor allem 1zelliger (stets 2zellige sind ein Sonderfall!) Matrixformeln, denn bei mehrzelligen ist das ja eindeutig.
* Eine Formel ohne Fktt, die mit Matrizen (Bereichen bzw Datenfeldern aus Matrix­Konstanten) operiert, bedarf idR der MatrixFmlForm, falls auch mehr als nur eines (das erste) ihrer Ergebnisse angezeigt wdn soll (Bereiche verhalten sich hier anders als Matrix­Konstanten und viele andere Datenfelder, weshalb diese Regel für sie nicht unbedingt gelten muss, auch beim Datenfeld aus deinem Bsp nicht, hier wird einfach der primäre Bereich indiziert)!
Gruß, Luc :-?

Bild

Betrifft: AW: Im Prinzip richtig; d.Begriff 'Matrixformel' ...
von: Daniel
Geschrieben am: 27.06.2015 16:45:14
Hi
da es Werner um die Effektivität geht und nicht um Begrifflichkeiten, spielt es keine Rolle, ob die Matrix-Berechnungmodus von aussen angestossen werden muss oder automatisch von Excel erkannt wird.
Gruß Daniel

Bild

Betrifft: Sag ich ja! ;-) owT
von: Luc:-?
Geschrieben am: 29.06.2015 15:32:05
:-?

Bild

Betrifft: dann wären wir uns mal eing???
von: Daniel
Geschrieben am: 29.06.2015 15:35:48
xxx

Bild

Betrifft: Siehe unten! ;-] owT
von: Luc:-?
Geschrieben am: 29.06.2015 16:06:33
:-?

Bild

Betrifft: AW: also ja.
von: Daniel
Geschrieben am: 29.06.2015 16:51:55
xxx

Bild

Betrifft: Ah, noch was, ...
von: Luc:-?
Geschrieben am: 29.06.2015 16:03:56
Daniel (und Werner);
ich kann auch nicht glauben, dass es effektiver ist, eine mehrzellige MatrixFml durch viele 1zellige zu ersetzen, wie es mitunter (speziell auch von Werner) vorgeschlagen wird. Aus einer Berechnung mit sagen wir mal 100 Ergebnissen (die nur in die Zellen geschrieben wdn müssen), wdn so 100(!) Berechnungen mit 1 Ergebnis. Glaube kaum, dass das „effektiv“ ist; es ist nur kein geschlossener Block, was Vorteile haben mag (ggf auch erforderlich sein kann)…
Außerdem sind 1zellige MxFmln oft instabil, d.h., wahren nicht immer die MxFml-Form. Ist diese nicht erforderlich, ist es sicherer. Auch deshalb ist bei 1zelligen MxFmln die Nicht-MxFml-Form vorzuziehen. Bei mehrzelligen MxFmln steht das ja ohnehin nicht zur Wahl. Allerdings sollte eine mehrzellige MxFml einer 1zelligen Fml (egal, ob mit oder ohne MxFmlForm) aus Effektivitätsgründen idR vorgezogen wdn, weil dann nur eine Berechnung für alle Ergebnisse erforderlich ist. Xl „weiß“ das auch und ersetzt in mehrzelligen MxFmln auch stets nur Werte in der 1.Fml, nicht überflüssigerweise in allen, wenn man das mal mit Ersetzen überprüft. Dazu folgende TestFmln:
1. mehrzellige MxFml: A1:A3:{=ZEILE(1:3)} → Ersetzen von = durch = in A1:A3 → 1 Ersetzung
2. 1zellige Fmln: B1:B3:=INDEX({1;2;3};ZEILE()) → dito in B1:B3 → 3 Ersetzungen
Tja, Werner, was ist nun effektiver…? ;-)
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: bitte ...
von: der neopa C
Geschrieben am: 29.06.2015 17:26:04
Hallo Luc,
... ich kenne Deine Meinung und Du meine.
Da Du mich aber explizit noch mal angesprochen hast, wiederhole ich mich nachfolgend sicherlich zumindest teilweise.
Mehrzellige Matrixformeln sind mir persönlich in den aller meisten Fällen viel zu starr bzgl. möglicher Datenstrukturanpassung. Ich kann mich darüber hinaus auch kaum an keinen praktischen Fall (aus Foren) erinnern, wo deren Anwendung sinnvoll oder gar zwingend notwendig gewesen wäre. Dagegen aber an zig Problemlösungen, wo Matrixfunktionalität mit einzelligen Formeln realisiert werden konnte (ich schreibe nicht: "zwingend notwendig"). Möglicherweise sind mehrzellige Matrixformeln bei einigen statistischen Auswertungen notwendig und sinnvoll, aber da kenne ich in der Basismaterie sowieso nicht aus.
Meine Erfahrungen aus Forenthreads müssten mich sehr täuschen, wenn diese Art von Formelauswertung nicht generell eine untergeordnete Rolle spielt. Insofern ist für mich ihre evtl. diesbzgl. bessere "Effektivität", wie Du sie siehst, momentan nebensächlich und auf Grund ihre "Starrheit" auch nicht interessant.
Das "einzellige MxFmln oft instabil sind" kann ich so nicht nachvollziehen, zumindest dann nicht, wenn sie richtig konstruiert sind.
Außerdem interessieren mich momentan eher (einzellige) Matrixfunktion(alität)sformeln ;-), als ("echte") Matrixformeln und für diese gilt meine vorherige Aussage erst Recht.
Das z.B. SUMMENPRODUKT()-Formeln (wo solche möglich sind, einer alternativen {SUMME()-Formel} bzgl. der Auswertung effektivir ist, das vertrittst Du z.B. ja auch selbst. Ohne es jetzt wirklich nachgewiesen zu haben, unterstelle ich eben einfach allen Formeln, die auf Basis von Matrixfunktionen erstellt werden, dass diese gegenüber vergleichbaren Matrixformeln "effektiver" sind. Da wird es möglicherweise auch Ausnahmen geben, aber das würde ich dann momentan auch als nachrangig ansehen.
Und bzgl. Deiner Frage: "was ist nun effektiver…?" gebe ich auch noch folgendes zu bedenken. Am "effektivsten" ist als erstes immer eine korrekte Lösung, die möglichst optimal und schnell als Problemlösung entwickelt wurde und leicht an evtl. Bedingungsänderung angepasst werden kann. Das hängt aber oft von soviel Faktoren ab, dass dann eine spez. Lösungsformelart eine völlig untergeordnete Rolle spielen dürfte.
Vorgenanntes ist nur ein Teil (m)einer Meinung, die ich auch noch nachjustieren müsste und weder Dir noch anderen aufzwingen will und erst Recht nicht kann. Bloß Deine Argumente und Gegenargumente verstehe ich teilweise nicht bzw. überzeugen mich andererseits diese eben auch nicht.
Aber bitte, ich will auf keinen Fall wieder eine lange Diskussion hier lostreten.
Gruß Werner
.. , - ..

Bild

Betrifft: Mein Bsp bedeutet, Xl berechnet eine mehr-...
von: Luc:-?
Geschrieben am: 29.06.2015 18:11:51
…zellige MxFml nur 1×, Werner,
und verteilt dann nur noch die Ergebnisse auf die betroffenen, markierten Zellen. Ersetzt du sie durch viele 1zellige Fmln, wird jede einzeln berechnet, was uU mehr Rechenaufwand bedeutet. Das ist auch ein Grund, warum MxFmln in jeder anspruchsvollen Calc-Software vorkommen, nur ihre Auslösung und mitunter auch Fml-Darstellung* ist unterschiedlich.
* In einer Linux-GNU-Software wird zB der eigentlichen Fml dann eine Angabe des Ergebnis­darstellungs­bereichs nachgestellt → ein mehr als deutlicher Hinweis darauf, was hier passiert.
Übrigens, wenn man sich mit der Pgmierung von zellFml-tauglichen Fktt (UDFs) befasst, kommt man über kurz oder lang zu derartigen Einsichten. Auch die Xl-Pgmierer „kochen nur mit Wasser“, d.h., können nur im Rahmen der von der Basis-Software und der von ihnen verwendeten Sprache vorgegebenen Bedingungen handeln.
Luc :-?

Bild

Betrifft: nicht wirklich ...
von: der neopa C
Geschrieben am: 28.06.2015 18:25:07
Hallo Daniel,
... meine gestrige Aussage war direkt an Luc gerichtet und enthält abschließend auch ein ;-).
Ich hatte mit Luc vor nicht all zu langer Zeit einen längeren Disput in Online-Excel-Forum zu diesem Thema, welches grob umrissen folgendes zum Inhalt hatte: Wie sollte/kann man derartige einzelligen Formeln bezeichnen, die ich u.a. mit INDEX() oder MMULT() oder AGGREGAT()-Funktion in Kombination dieser Funktionen oder mit anderen Funktionen des öfteren konstruiere und dadurch ("echte") Matrixformeln ersetze. Also nicht gemeint sind Formeln die lediglich mit einer Funktion konstruiert sind, die eine MATRIXVersion besitzen. Also z.B. keine keine "reinen" SUMMENPRODUKT()-Formeln oder "reine" AGGREGAT()-Formeln ...
Nach einigem hin und her, hat Luc meinem Kompromissvorschlag: MATRIXfunktion(alität)sformel irgendwie akzeptiert oder nennen wir es "geduldet".
Vorgenannten Begriff hatte ich in den letzten Wochen auch hier im Forum schon das eine oder andere Mal entsprechend verwendet. Er bezeichnet aus meiner Sicht u.a. den "Typ-Charakter" derartiger Formeln wie auch deren Basis (Funktionen die MATRIXversionen besitzen) und trotzdem auch der (äußerliche) Unterschied zu (echten) MATRIXformeln aus dem Begriff erkennbar ist.
Gruß Werner
.. , - ...

Bild

Betrifft: Siehe oben! ;-) owT
von: Luc:-?
Geschrieben am: 29.06.2015 16:07:33
:-?

Bild

Betrifft: AW: Wert aus Matrix suchen
von: Stefan
Geschrieben am: 28.06.2015 21:13:21
Hallo ihr lieben,
ich bin eben erst dazu gekommen mir alles anzusehen.
Vorweg "Herzlichen Dank" für den Excel Stoff und die wirklich interessante Nachhilfestunde. :-)
Eine Frage zu der Aufgabe habe ich allerdings noch.
Ich habe wie von euch beschrieben, beide Lösungswege in jeweils ein neues Sheet kopiert.
Bei dem Lösungsweg von Werner wird immer nur ein Tag angezeigt?
Und bei dem Lösungswegs von "Luc" habe ich das ungleich Zeichen durch <> ersetzt.
Aber irgendetwas scheine ich immer noch verkehrt zu machen.
So ganz funktionieren möchte es noch nicht.
Kann mir einer sagen was ich falsch mache?
https://www.herber.de/bbs/user/98533.xlsx
Hmm,.. (ratlos auf den Rechner schau)
Gruß
Stefan

Bild

Betrifft: ein Kopier-/Schreibfehler meinerseits ...
von: der neopa C
Geschrieben am: 29.06.2015 07:53:11
Hallo Stefan,
... der möglicherweise dadurch entstanden war, dass ich die Lösungsformel zunächst in einem anderen Zellbereich entwickelt hatte. Sorry.
Die Formel in C40 muss natürlich anstelle J$7:J7 richtig: C$39:C39 lauten.
Also die gesamte Formel in C40 so:
=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(B$1:$B$33)/(D$1:$H$33="x")/ISTNV(VERGLEICH(B$1:B$33; C$39:C39;)); 1)); "")
und diese nach unten kopieren.
Die Formeln in D40:H47 ergeben dann automatisch das korrekte Ergebnis.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Wert aus Matrix suchen
von: Stefan
Geschrieben am: 29.06.2015 11:52:56
Super, jetzt funktioniert es bestens.
Habt vielen Dank :-)
Gruß
Stefan

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Wert aus Matrix suchen"