Fehlersuche Matrixformel

Bild

Betrifft: Fehlersuche Matrixformel
von: Joerschi
Geschrieben am: 29.10.2015 22:05:33

Hallo liebes Forum,
Excel regt mich wieder mal auf...
Vielleicht könnt Ihr mir ja helfen, wo der Fehler in meiner Formel liegt.
Skizze + Musterdatei vorab, Erklärung darunter:
Userbild
https://www.herber.de/bbs/user/101129.xlsx
Es gibt eine Zeile mit fixen Zahlenwerten (A4:O4) und zugehörige Überschriften (A2:O4) je Zahlenwert.
Außerdem gibt es in U4 und V4 je einen fest vorgegeben Zahlenwert.
Ausgegeben werden soll per Formel in Zelle Q4 die Überschrift desjenigen Zahlenwertes, welcher als Erstes größergleich der Vorgabe in U4 ist.
In der richtigen Lösung wäre das die Überschrift "2".
Analog soll in Zelle R4 die Überschrift desjenigen Zahlenwertes ausgegeben werden, welcher als Letztes kleinergleich der Vorgabe in V4
ist. Die richtige Lösung wäre demnach die Überschrift "9".
Meine Formel für die Zelle Q4 - welche funktioniert - lautet:

{=MIN(WENN(A4:O4>=U4;A$2:O$2))}
Allerdings rechnet das Pendent für Zelle R4 falsch:
{=Max(WENN(A4:O4<=v4;A$2:O$2))}
Ich komme einfach darauf, warum das so ist.
In der Formelanalyse schon in den Prozedurschritten nachgeschaut und da werden die Zellen K4:M4 richtigerweise als "FALSCH" benannt (und damit für die Ausgabe ignoriert), aber die beiden Zellen N4:O4 unsinnigerweise mit 0,000 angezeigt, wonach dann eben die Überschrift in O2 auch so ausgegeben wird.
Hätte jemand einen Tip, wo der (Denk?)fehler liegt?
Formatierung? Formelproblem?
Danke vorab und liebe Grüße
Joerschi

Bild

Betrifft: AW: Fehlersuche Matrixformel
von: Luschi
Geschrieben am: 29.10.2015 22:38:49
Hallo Joerschi,
hier mal 1 Lösungsvorschhlage für 'Q4'
=AGGREGAT(15;6;1/(A4:O4>=U4)*(A2:O2);ZEILE(A1))
Abschluß nur mit Enter, das die Aggregatfunktion ab Funktions-Nr. 14 Matrixfunktionalität hat.
Gruß von Luschi
aus klein-Paris

Bild

Betrifft: AW: Fehlersuche Matrixformel
von: Joerschi
Geschrieben am: 29.10.2015 22:45:04
Danke Luschi, aber das Problem ist ja die (falsche) Formellösung in R4.
Wenn ich Deine Formel entsprechend umbaue, rechnet sie ebenso falsch (und ebenso verstehe ich nicht, warum das auch hier so ist...):

=AGGREGAT(14;6;1/(A4:O4<=V4)*(A2:O2);ZEILE(A1))
Liebe Grüße
Joerschi

Bild

Betrifft: Zuerst die schlechten Nachrichten, ...
von: Luc:-?
Geschrieben am: 30.10.2015 02:03:50
…Joerschi;
beide Fmln liefern das richtige Ergebnis, nur sind auch beide falsch! Die 1. liefert nur zufällig den erwarteten Wert, denn die Fmln enthalten einen schwerwiegenden Denkfehler.
Etwas komplizierter dürfen sie schon sein, denn du willst ja nicht das Min- bzw Maximum der Nrn der Spalten, deren Werte in Zeile 4 die Bedingungen erfüllen. Wären das keine Nrn, hättest du das gar nicht erst versucht. So hast du dir selbst ins Knie geschossen… ;-]
Q4:{=INDEX(A2:O2;VERGLEICH(MIN(WENN(A4:O4>=U4;A4:O4));A4:O4;0))}
R4:{=INDEX(A2:O2;VERGLEICH(MAX(WENN(A4:O4<=V4;A4:O4));A4:O4;0))}
Morrn, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: und dann die guten ;-) ...
von: ... neopa C
Geschrieben am: 30.10.2015 07:51:58
Hallo Luc,
... bei Joerschi sind es aber im Regelfall Zahlen, so wie ich seine bisherigen Problemstellungen kenne.
Aber selbst wenn es keine Zahlen wären, kann ich dank AGGREGAT() auf die leidliche {} verzichten.
Für Min so:


=INDEX(2:2;AGGREGAT(15;6;SPALTE(A1:O1)/(A4:O4>=U4)/(A4:O4>0);1))

für Max so:

=INDEX(2:2;AGGREGAT(14;6;SPALTE(A1:O1)/(A4:O4<=V4)/(A4:O4>0);1))
Gruß Werner
.. , - ...

Bild

Betrifft: AW: allerdings reicht für MIN ...
von: ... neopa C
Geschrieben am: 30.10.2015 08:20:38
Hallo,
... natürlich auch einfach:


=INDEX(2:2;AGGREGAT(15;6;SPALTE(A1:O1)/(A4:O4>=U4);1))
Gruß Werner
.. , - ...

Bild

Betrifft: Danksagung @ alle :-)
von: Joerschi
Geschrieben am: 30.10.2015 08:31:34
...und kaum schaut man früh in den PC, schon gibts hier eine Litanei an Lösungen :-))
Herzlichen Dank vorab @ Werner und @ Luc !
Habe auch schon probiert und sowohl Luc´s als auch Werners Lösungen funktionieren prima.
Aus Vereinfachungsgründen werde ich mich natürlich auf die kürzeste Variante stürzen :-)
(bzw. "Nicht-Matrix" ist auch immer sexy, wenn es sich vermeiden lässt)
Liebe Grüße
joerschi
PS @Luc: Das man da auch noch beachten muss, ob Zahlen oder Text, wäre ich nicht drauf gekommen.
Idee war einfach, dass lt. Formel aus der Werteliste der richtige Wert ermittelt wird und dann von diesem stumpf nach oben auf die Überschrift verwiesen - egal was da steht.

Bild

Betrifft: Dein Problem war, dass du Min/Max falsch ...
von: Luc:-?
Geschrieben am: 30.10.2015 19:16:20
…gebildet hast, Joerschi,
nämlich mit den falschen Werten! Das macht dann auch AGGREGAT nicht anders.
Anmerk: Die Abneigung gg MatrixFmln ist für mich nicht nachvollziehbar, denn dabei handelt es sich nur um sinnvolle, xl-immanente Mechanismen. Wenn man eine 1zellige MatrixFml durch eine ersetzen kann, die die MatrixFktionalität nicht benötigt — meinethalben. Aber das Ersetzen einer mehrzelligen MatrixFml durch lauter 1zellige Nicht-MatrixFmln ist oft kontraproduktiv, zumindest dann, wenn jede EinzelFml immer wieder denselben größeren Bereich behandelt. So etwas sollte die Ausnahme für Spezialfälle bleiben!
Gruß, Luc :-?

Bild

Betrifft: Nun, Werner, ...
von: Luc:-?
Geschrieben am: 30.10.2015 19:20:19
…eine gute Nachricht hatte ich ihm auch schon vermittelt. Ich habe absichtlich seine ursprüngliche Fml zur Basis meiner Lösung gemacht, um ihm mit der Korrektur seines Ansatzes zu zeigen, wo sein Denkfehler lag.
Gruß, Luc :-?

Bild

Betrifft: AW: mein Betreff war mit einem ;-) versehen ...
von: ... neopa C
Geschrieben am: 31.10.2015 12:48:09
Hallo Luc,
... und so meinte ich es auch.
Und zu Deiner Aussage in Deinem Beitrag an Joerschi "Die Abneigung gg MatrixFmln..." die ich natürlich auch etwas auf mich bezog, kann ich nur feststellen, ich habe keine Abneigung. Aus verschiedenen Gründen, die Dir auch bekannt sein dürften, versuche ich seit geraumer Zeit "{}-"Matrixformeln durch alternative Lösungsformeln zu ersetzen. Und zum Einsatz von mehrzelligen MatrixFml habe ich nach wie vor eine etwas andere Meinung. Aber darüber habe wir auch schon diskutiert. Es gibt immer Gründe für und gegen etwas, damit ist aber nicht gesagt, dass allgemein das eine oder andere zu bevorzugen wäre. Bleiben wir doch dabei, es sind beides vertretbare Alternativen.

Gruß Werner
.. , - ...

Bild

Betrifft: Ich habe dein ;-) nicht übersehen, ...
von: Luc:-?
Geschrieben am: 31.10.2015 15:15:01
…und ging deshalb davon aus, Werner,
dass dein Betreff sich dir als Ergänzung zu meinem anbot. Also war ich selber schuld… ;-)
Eine mehrzellige MatrixFml wird idR nur 1-2× (wie auch jede NormalFml) berechnet und ihr Ergebnis in den gesamten markierten Bereich eingetragen (als ZellEigenschaft .Value). Bei Ersatz (wenn überhpt mal möglich) durch 1zellige (Matrix-)Fmln wird jede FmlZelle einzeln berechnet.
Das merkt man schon, wenn man mit VBA FmlTeile austauscht. Die mehrzellige MatrixFml wird nur 1× angesprochen. Im Falle von 1zelligen Matrix­Fmln oder Fmln mit der von dir so genannten Matrix(-Fml-)Fktionalität kann das einen immensen Berechnungs­mehr­aufwand bewirken.
Der einzige „Vorteil“ dieser Heran­gehens­weise besteht in der Separier­barkeit der Einzel­Ergebnisse, weshalb nur bei zwingendem Erfordernis (oder kleinen Berechnungs­anzahlen) darauf zurück­gegriffen wdn sollte.
Für 1zellige MatrixFmln wäre der normalisierte (→ von FmlAbweichungen zwischen MxFml und Pendant bereinigte) Berechnungs­aufwand genausogroß wie für ihr Pendent ohne MatrixFmlForm.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: nochmal zu mehrzelligen Matrixformeln ...
von: ... neopa C
Geschrieben am: 02.11.2015 10:04:17
Hallo Luc,
... ich sehe den Hauptnachteil dieser Lösungsform darin, dass diese zu starr bzgl. nachträglich notwendiger Strukturänderungen in der jeweiligen Tabelle sind. D.h. man kann in solchen Bereichen keine Zeile/Spalte einfügen/löschen ohne zuvor die Lösungsform aufgehoben zu haben. Hinzu kommt, dass man visuell auch nicht gleich erkennen kann, wo so eine Lösungsform eingesetzt ist. Damit ist zumindest für mich der von Dir berechtigt ins Feld geführte Vorteil mehr als aufgehoben. Aber das kann natürlich jeder für sich anders empfinden.
Gruß Werner
.. , - ...

Bild

Betrifft: Ich sagte ja, hängt auch vom Umfang ab! ;-) owT
von: Luc:-?
Geschrieben am: 02.11.2015 13:44:18
:-?

Bild

Betrifft: AW: für AGGREGAT() fehlte noch eine Bedingung ...
von: ... neopa C
Geschrieben am: 30.10.2015 07:45:15
Hallo Joerschi,
... richtig z.B. so: =AGGREGAT(14;6;A2:O2/(A4:O4<=V4)/(A4:O4>0);1)
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Fehlersuche Matrixformel"