Microsoft Excel

Herbers Excel/VBA-Archiv

Anfrage an neopa C zum ltzten Thema Formel gesucht


Betrifft: Anfrage an neopa C zum ltzten Thema Formel gesucht
von: Frank
Geschrieben am: 17.04.2019 13:48:11

Hallo Werner,

ich hoffe ich verstoße gegen keine Regeln, wenn ich dich direkt anschreibe.
Du hast mir neulich so eine schöne Formel mit der Funktion Aggregat zusammengestellt. Ich habe _ jetzt mit einer kleineren Datenmenge etwas experimentiert, aber so ganz verstehe ich nicht was Excel da genau rechnet wenn man bspw. sowas eingibt

AGGREGAT(15;6;ZEILE(A4:A2501)/(B4:B2501>=E2)
Der Bereich mit dem Bruchstrich verstehe ich nicht bzw. was man damit erreicht.

Ich habe nochmals eine Tabelle hinterlegt. Es gibt zwei Dinge dich ich auslese. Zum Einen einen Wert bei einem bestimmten Weg, in diesem Fall bei 0,5mm. Und als Zweites lese ich die minimale Kraft in einem Bereich aus, in diesem Fall zwischen 0,5 und 3,5mm.

Es gibt genau einen Sonderfall und zwar wenn die minimale Kraft genau bei 0,5mm ist. In diesem Fall müsste sich in D8 das gleiche Ergebnis wie in D6 errechnen. Hättest du eine Idee wie man diesen Sonderfall abfängt? Das Problem bei diesem Sonderfall ist das dann in D6 und D8 logischerweise zwei unterschiedliche Ergebnisse rauskommen, weil in Zelle D8 der nächstgrößere Wert für 0,501 ausgelesen wird.

In Worten gesprochen Wenn die minimale Kraft des Bereiches bei 0,5 liegt dann interpoliere die Zahl so wie in Zelle D6, ansonsten verwende die Formel aus D8.

http://www.herber.de/bbs/user/129223.xls

Ich hoffe ich konnte mein Anliegen verständlich erläutern.
Vielen Dank und viele Grüße
Frank

  

Betrifft: ich habe es so gelöst...
von: Frank
Geschrieben am: 17.04.2019 13:56:48

=WENN(UND(INDEX(B:B;AGGREGAT(14;6;ZEILE(A4:A2501)/(B4:B2501<=E2);1))=E2);1))>E2);WENN(E2=0;"";WENNFEHLER(INDEX(A:A;AGGREGAT(15;6; ZEILE(A4:A2501)/(B4:B2501>=E2);1))-(INDEX(A:A;AGGREGAT(15;6;ZEILE(A4:A2501)/(B4:B2501>=E2);1)) -INDEX(A:A;AGGREGAT(14;6;ZEILE(A4:A2501)/(B4:B2501<=E2);1)))/(AGGREGAT(15;6;B4:B2501/(B4:B2501>=E2); 1)-AGGREGAT(14;6;B4:B2501/(B4:B2501<=E2);1))*(AGGREGAT(15;6;B4:B2501/(B4:B2501>=E2);1)-E2);INDEX(A:A; VERGLEICH(E2;B:B;0))));AGGREGAT(15;6;A4:A2503/(B4:B2503>=E2)/(B4:B2503<=E3);1)) 



  

Betrifft: AW mit der hier dargestellten sicherlich nicht ...
von: neopa C
Geschrieben am: 17.04.2019 14:41:35

Hallo Frank,

... denn wenn ich diese in Deine einsetze Datei einsetze, ergibt sich lediglich eine Fehlermeldung.
Ich schau es mir später mit etwas mehr Ruhe noch einmal an.

Gruß Werner
.. , - ...


  

Betrifft: AW: AW mit der hier dargestellten sicherlich nicht ...
von: Frank
Geschrieben am: 17.04.2019 15:02:51

Ich verstehe das nicht. Ich gehe in die Zelle D8 und kopiere die komplette Funktion und füge sie hier im Forum ein. Wenn ich dann die hochgeladene Funktion wieder zurück kopiere steht da was ganz anderes...

Auf deine andere lange Antwort antworte ich noch. Anbei nochmal die Datei mit funktionierender Formel in D8.

http://www.herber.de/bbs/user/129225.xls

Vielen Dank und viele Grüße
Frank


  

Betrifft: AW: hierzu nun ...
von: neopa C
Geschrieben am: 17.04.2019 15:26:32

Hallo Frank,

... in der Datei ist Deine angepasste Formel noch wesentlich länger als Du sie ohne diese mir vermitteln wolltest. Möglicherweise ist das ein Problem der Forumssoftware, dass Du diese Formel nicht direkt (ohne Aufbereitung) in den Text einfügen kannst.

Nun Deine erweiterte Formel ermittelt das gleiche Ergebnis, wie die in D6. Wozu also die Erweiterungen (die ich mir jetzt nicht näher angeschaut habe)?

Gruß Werner
.. , - ...


  

Betrifft: AW: hierzu nun ...
von: Frank
Geschrieben am: 17.04.2019 17:13:54

Hallo Werner,

vielen Dank für die Erläuterungen. Es besteht offensichtlich ein Problem mit der Forumssoftware, weil ganze Teile meiner Funktion fehlen wie bspw. Index. aber gut ich habe ja die Möglichkeit das Excelfile hochzuladen.

Zu deiner Frage:
Ich habe es in der Exceldatei und auch im Text beschrieben. Ich suche zwei Kraftwerte heraus. Einmal einen festen Wert der interpoliert wird, in diesem Fall bei 0,5mm.
Dann suche ich noch die minimale Kraft in einem Bereich, in diesem Fall zwischen 0,5 und 3,5mm. Und hier gibt es den Sonderfall, dass der Minimalwert bei 0,5mm liegt. Die aufsteigende Tabelle hat aber nicht den genauen Wert für 0,5 und sucht mir den Wert für 0,501 heraus, weil die "Bereichssuchfunktion" nicht interpoliert. Und somit ergibt sich für D6 und D8 ein anderes Ergebnis (Exceldatei 1 von heute) obwohl das selbe herauskommen müsste.

Ich hoffe ich habe das verständlich erklärt.
Vielen Dank und viele Grüße
Frank


  

Betrifft: AW: nachgefragt ...
von: neopa C
Geschrieben am: 17.04.2019 17:32:27

Hallo Frank,

... gibt es denn einen anderen Kraft/Weg Verlauf als in Deinem hochgeladenen Beispiel, dass Du schreibst, "dass die minimale Kraft bei 0,501000400160066 auftritt"?
Im Beispiel liegt das MINIMUM eindeutig bei exakt 0,5 und dieser Kraftwert wird doch schon mit der Formel in D6 mittels Interpolation ermittelt. Wozu also noch eine Formel?

Gruß Werner
.. , - ...


  

Betrifft: AW: AW mit der hier dargestellten sicherlich nicht ...
von: Frank
Geschrieben am: 17.04.2019 15:09:46

Das ist wirklich eigenartig. Um das Forum jetzt nicht vollzumüllen habe ich das eben nochmal _ versucht und bin auf Vorschau gegangen. Ich kopiere die Formel aus D8 und kopiere sie hier in das Textfeld und füge noch Code

  
ein. Dann gehe ich auf Vorschau und kopiere mir die Formel wieder zurück und siehe da es ist eine ganz andere Formel? Das hat jetzt zwar nichts mit meiner Anfrage zu tun, aber wie kann denn sowas sein?


  

Betrifft: AW: AW mit der hier dargestellten sicherlich nicht ...
von: Frank
Geschrieben am: 17.04.2019 15:12:35




  

Betrifft: probier nochmal
von: Frank
Geschrieben am: 17.04.2019 14:57:59

=WENN(UND(INDEX(B:B;AGGREGAT(14;6;ZEILE(A4:A2501)/(B4:B2501<=E2);1))=E2);1))>E2);WENN(E2=0;"";WENNFEHLER(INDEX(A:A;AGGREGAT(15;6; ZEILE(A4:A2501)/(B4:B2501>=E2);1))-(INDEX(A:A;AGGREGAT(15;6;ZEILE(A4:A2501)/(B4:B2501>=E2);1)) -INDEX(A:A;AGGREGAT(14;6;ZEILE(A4:A2501)/(B4:B2501<=E2);1)))/(AGGREGAT(15;6;B4:B2501/(B4:B2501>=E2); 1)-AGGREGAT(14;6;B4:B2501/(B4:B2501<=E2);1))*(AGGREGAT(15;6;B4:B2501/(B4:B2501>=E2);1)-E2);INDEX(A:A; VERGLEICH(E2;B:B;0))));AGGREGAT(15;6;A4:A2503/(B4:B2503>=E2)/(B4:B2503<=E3);1)) 



  

Betrifft: AW: wie zuvor schon auch owT
von: neopa C
Geschrieben am: 17.04.2019 15:20:38

Gruß Werner
.. , - ...


  

Betrifft: AW: Anfrage an neopa C zum ltzten Thema Formel gesucht
von: Frank
Geschrieben am: 17.04.2019 13:59:14

Es war nun doch nicht so schwer wie ich dachte. Dennoch würde ich mich freuen, wenn du mir das mit dem Bruchstrich bei Aggregat etwas erläutern könntest. Ich bin in den Formeleditor gegangen und habe mir die Formel mit F9 schrittweise aufgelöst (für einen kleineren Bereich). Da stehen, dann die Zahlen aus der Spalte und WAHR und FALSCH. Aber was geschieht bei der Division?


  

Betrifft: AW: prima ...
von: neopa C
Geschrieben am: 17.04.2019 14:35:18

Hallo Frank,

... wenn Du inzwischen selbst schon eine Lösung gefunden hast (auf ich im andern "Ast" noch eingehe).

Zunächst zu: "ich hoffe ich verstoße gegen keine Regeln, wenn ich dich direkt anschreibe"
Nein, hast Du nicht. Da Dein ursprünglicher thread wohl viel älter als 6 Tage ist, wird er nicht mehr in der aktuellen Forumsliste angezeigt und so war und ist es für Dich die beste Möglichkeit mich auf Deine Zusatzfrage aufmerksam zu machen.

Aber in so einem Fall ist es meist zweckmäßig wie sinnvoll den Link auf diesen vorherigen thread mit anzugeben. Du hast noch Zugriff (nur keinen aktiven mehr) auf diesen thread (unter dem Button DEINE BEITRÄGE). Derjenige den Du aber aufmerksam machen willst, müsste sehr lange suchen, um diesen vorherigen thread wieder zu finden. Also ich bin da meist zu faul zu.

Nun zu Deiner Frage:

Leider werden die Möglichkeiten die sich durch die AGGREGAT()-Funktion in seiner sogenannten Matrixversion in der MSO nicht aufgezeigt. Offensichtlich deshalb nicht, weil diese von MS so auch nicht wirklich angestrebt war, wie sie jetzt genutzt werden.

Die Funktionsweise einer AGGREGAT()-Formel funktioniert einerseits ähnlich, wie eine klassische Matrixformel. Mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=26 und ff
Die AGGREGAT()-Formel bedarf aber nicht des spez. Formelabschlusses, weil die Funktion intern die Matrixauswertungsfunktionalität schon beherrscht. Dies z.B. wie die etwas einfacheren Funktionen ZÄHLENWENN(), SUMMEWENN() ...

Zugegebener weise gibt es eine (anfängliche) ähnliche etwas größere Hürde die Funktionsweise der AGGREGAT()-Formeln wie auch die klassische Matrixformeln zu verstehen.

Besonders die Nutzung von "/" also der Division der Bedingungsvergleiche in den AGGREGAT()-Formeln, scheinen auf ersten Blick etwas rätselhaft. Denn die Divisionen erzeugen intern Fehlerwerte für die nichtzutreffenden Bedingungen. Aber gerade dadurch sind ein maßgebliches Lösungs-Grundprinzip dieses Formeltyps. Denn die AGGREGAT()-Funktion mit der 6 als 2. Argument ist in der Lage derartige interne Fehlerwerte "auszufiltern" und dadurch die angestrebten zutreffenden Ergebniswerte zu ermitteln.

In der von Dir dargestellten Formel werden also zunächst alle Daten in B4:B2501 geprüft, ob diese größer gleich E2 sind. Da wo das nicht der Fall ist, ergibt sich ein FALSCH. Dieses FALSCH wird durch die übergeordnete Division ein Wert 0 und eine Division durch 0 ergibt bekanntermaßen eine Fehlermeldung. Dies werden nun aber wie geschrieben ignoriert und die restlichen Bedingungsvergleiche ergeben WAHR. Damit wird nun dafür die kleinste Zeilennummer gefunden, wo dies zutreffend ist, weil ZEILE(A4:A2501) da durch 1 geteilt wird, welche sich infolge der Division aus WAHR ergibt.

Gruß Werner
.. , - ...


  

Betrifft: AW: Anfrage an neopa C zum ltzten Thema Formel gesucht
von: Daniel
Geschrieben am: 17.04.2019 17:56:36

Hi
da deine Liste ja nach Weg aufsteigend sortiert ist, halte ich es prinzipell für sinnvoller, über Vergleich die Postion der Grenzwerte in der Tabelle zu ermitteln und damit zu arbeiten.
das dürfte bei großen Datenmengen etwas schneller sein.

hier die Gesamtformel für die Lösung:

=MIN(INDEX(A:A;VERGLEICH(E2-0,00000000001;$B$3:$B$2502;1)+2)+(INDEX(A:A;VERGLEICH(E2-0,00000000001; $B$3:$B$2502;1)+2+1)-INDEX(A:A;VERGLEICH(E2-0,00000000001;$B$3:$B$2502;1)+2))*(0,5-INDEX(B:B; VERGLEICH(E2-0,00000000001;$B$3:$B$2502;1)+2))/(INDEX(B:B;VERGLEICH(E2-0,00000000001;$B$3:$B$2502;1) +2+1)-INDEX(B:B;VERGLEICH(E2-0,00000000001;$B$3:$B$2502;1)+2));INDEX(A:A;VERGLEICH(E3-0,00000000001; $B$3:$B$2502;1)+2)+(INDEX(A:A;VERGLEICH(E3-0,00000000001;$B$3:$B$2502;1)+2+1)-INDEX(A:A; VERGLEICH(E3-0,00000000001;$B$3:$B$2502;1)+2))*(0,5-INDEX(B:B;VERGLEICH(E3-0,00000000001; $B$3:$B$2502;1)+2))/(INDEX(B:B;VERGLEICH(E3-0,00000000001;$B$3:$B$2502;1)+2+1)-INDEX(B:B; VERGLEICH(E3-0,00000000001;$B$3:$B$2502;1)+2));INDEX(A:A;VERGLEICH(E2-0,00000000001;$B$3:$B$2502;1) +2+1):INDEX(A:A;VERGLEICH(E3-0,00000000001;$B$3:$B$2502;1)+2))

die Herleitung der Formel ergibt sich aus der Tabelle D18:H23 über mehrere zwischenschritte.
die Gesamtformel kann man dann bilden, indem man in der Endergebnisformel die Zellbezüge auf die Zwischenschritte durch deren Formeln ersetzt.
(wobei ich dann solche Formelmonster vermeiden würde und lieber mit den zwischenschritten arbeite, dann lassen sich die Ergebnisse besser kontrollieren)

http://www.herber.de/bbs/user/129227.xls

Gruß Daniel


  

Betrifft: AW: korrektur der Formel
von: Daniel
Geschrieben am: 17.04.2019 18:35:41

in der Gesamtformel sind noch ein paar Grenzwerte (0,5) als Fixwert drin, diese müssten auf den Bezug E2 umgewandelt werden, damit die Formel bei geänderten Grenzwerten richtig rechnet.
Gruß Daniel


  

Betrifft: AW: korrektur der Formel
von: Frank
Geschrieben am: 17.04.2019 20:21:55

Guten Abend Daniel,

vielen Dank für Deinen Beitrag. Deine einzelnen bzw. deine "Monsterformel" macht genau das was es soll und durch die von dir einzeln aufgeführten Schritte konnte ich es auch sehr gut nachvollziehen. Vielen Dank an dieser Stelle. Die Schachtelfunktion kann man so super verwenden.

Dennoch habe ich auf der Heimfahrt weiter über den Ansatz von Werner nachgedacht und nun auch eine Schachtelfunktion (mittels Aggregat) gefunden, die ich gern online stellen wollen würde. Da ich aber das Problem hatte, dass sich die Funktion beim Hochladen "verwandelt", wollte ich Dich fragen wie du deine megalange Funktion hochgeladen hast?

Eine letze Frage sei erlaubt. Wieso ist es erforderlich in der VERGLEICH Funktion -0,00000000001 zu rechnen.

Vielen Dank an Dich und einen entspannten Feierabend.
Frank


  

Betrifft: AW: korrektur der Formel
von: Daniel
Geschrieben am: 17.04.2019 20:42:59

Hi

ich habe meine Formel ganz normal reinkopiert.
da sie keine Zeichen enthält, die auch für die HTML-Steuerung des Forums verwendet werden, geht das Problemlos.

über das -0,000000001 stelle ich sicher, dass die erste gefundene Stelle immer kleiner ist als der gesuchte Grenzwert, auch wenn der gesuchte Grenzwert genau in der Tabelle vorhanden ist. Wenn in diesem fall der Grenzwert erst auf der zweiten Stelle erscheint, passt das besser in mein Formelwerk.
der abgezogene Betrag muss aber kleiner sein, als die kleinstmögliche Differenz zwischen den einzelnen Werten, sonst könnte es zu falschen Ergebnissen kommen.

außerdem gehe ich davon aus, dass bei großen Datenmengen meine Methode schneller rechnet, weil der Vergleich in sortierten Listen eine bessere Suchmethode verwendet, als jeden Wert einzeln zu vergleichen.


Gruß Daniel


  

Betrifft: AW: korrektur der Formel
von: Frank
Geschrieben am: 17.04.2019 21:16:59

Guten Abend Daniel,

vielen Dank für Deine Erläuterungen... Ich habe meine Formel auch ganz normal rein kopiert und dennoch gab es Probleme bzw. sah die Formel nach dem Hochladen anders aus. Ich weiß gerade nicht, welche Zeichen ich verwende die Du nicht verwendest bzw. im Forum erlaubt sind. Sei es drum, ich kann ja meine Datei hochladen. In der Zelle D13 findest Du die zusammengebaute Lösung über Werners Ansatz. In D15 findest Du im Prinzip Deine Formel. Nun ist es so, dass wenn der Sonderfall eintritt bzw. eine Kraft interpoliert werden muss, auch der Weg interpoliert werden muss... Eventuell gibt es einen wesentlich leichteren Ansatz als den den ich verwendet habe. Ich habe bei der INDEX Funktion testweise auf B:B verwiesen, aber es kommt 0,5 heraus, obwohl wie in der angehangenen Beispieldatei in D15 0,509003601440578 herauskommen sollte.

http://www.herber.de/bbs/user/129229.xls

Vielleicht hättest Du die Muße Dir das noch einmal anzusehen. Ich wäre Dir sehr dankbar.
Vielen Dank und viele Grüße
Frank


  

Betrifft: AW: korrektur der Formel
von: Daniel
Geschrieben am: 17.04.2019 21:23:38

Hi

naja, wenn du Formatierungen hier verwendest, stehen diese in <...>
deshalb tut sich der Forumsedior schwer damit, diese beiden Zeichen als normale Zeichen darzustellen.
Ein weg ist ist innerhalb der Code-Tags.
aber dann baut der Editor die komischen Zeilenumbrüche ein.
irgendwo in der FAQ zu dieser Seite müsste auch beschrieben stehen, was man tun muss, um <...> als normale Zeichen einzusetzen.

Gruß Daniel


  

Betrifft: AW: nochmal nachgefragt ...
von: neopa C
Geschrieben am: 22.04.2019 11:07:56

Hallo Frank,

... meine Formel D6 ermittelt doch für Deine Daten genau das, was Du als Ergebnis anstrebst.
Bei Deinen Daten interessiert für die Ermittlung der min. Kraft lediglich der vorgegebene min Weg zu dessen Ermittlung. Weder Deine verlängerte Formel noch die Formel von Daniel ermittelt ein anderes Ergebnis als die Formel in D6.

Also warum suchst Du nach einer anderen Formel?

Gruß Werner
.. , - ...