Passendsten Wert finden und Zielbereich ermitteln

[prometheus]

Hallo Forum,

auf Anraten eröffne ich zu diesem Thema einen neuen Thread. Macht ja auch Sinn. (Danke balu)
Hallo balu,
Ich habe also eine recht große Tabelle (100x100). Unterhalb dieser werden die Extrema der jeweiligen Spalte rausgesucht. Ich habe somit also bis zu 100 min(und 100 max) Werte. Diese möchte ich nun mit einem Referenzwert vergleichen, den passendsten ermitteln und die Spalte, welche diesen Treffer enthält, kopieren.
Meine Versuche mit WVerweis bringen es irgendwie nicht. Ich bin eben absoluter Neuling.

Wie sieht es denn aus, wenn man die MAX-Werte unterhalb der Tabelle einfach nochmal sortiert in eine Zeile, oder auch Spalte auflistet, dort dann sucht und den nun gefundenen Wert als exakten Wert für eine erneute Scuhe in der gesamten Tabelle nutzt, um so an die Koordinaten der Zelle zu kommen, die diesen Wert enthält (damit ich den Bereich oberhalb der Zelle adressieren kann) ? Leider bekomme ich keine automatische Sortierung (in einer Zeile!)hin.

noch eine andere Idee: Vllt. kann man sich ja auch den Charakter des Toleranzfeldes zu nutze machen. Diese sieht aus wie eine typische GAUSS-Verteilung (müsste sogar spiegelsymmetrisch sein) min-max-min. Wverweis liefert mir nur leider immer einen Wert zu weit links. Er scheint also von "links nach rechts" die Zeile zu durchsuchen und gibt mir den wert der linken Nachbarzelle aus.

Zur Not könnte man doch einfach die Zeile transponieren, sortieren und durchsuchen. Leider fehlt mir das Wissen, die Sache so zu formulieren, dass es automatisch klappt.

beste Grüße und Dank!

prome

NB@balu: Metaller ? Ich bin Maschbauer.

[balu]

@Alle
Beispieltabellen befinden sich in diesem Thread:
Tabelle aktualisiert sich nicht. IDEEN ?



Hallo prome,

eins vorweg. Ich habe mir bis jetzt so reichlich Gedanken (und auch test-tabellen) wegen deinem Problem gemacht, aber das Ergebnis ist nicht zufriedenstellend, da noch etwas grundsätzliches geklärt werden muss.

Diese möchte ich nun mit einem Referenzwert vergleichen

Wie kann der Referenzwert aussehen? Ist er eine Ganzzahl (15), oder eine Dezimalzahl (12,038)?
Auch wenns wahrscheinlich bei der Problembeseitigung nicht viel hilft, aber dennoch: Mit was für einen +/- Toleranzwert in Prozent möchtest Du ihn rausfinden? Oder vor mir aus auch in Dezimalschreibweise von z.B. -0,012 bis +0,050.

Und ganz ehrlich gesagt, bin ich momentan am zweifeln ob ich dich überhaupt richtig verstehe (weil ich beim experimentieren wohl vom rechten pfad abgekommen bin).
Wenn Du unterhalb der Tabelle die MAX-Werte stehen hast, soll sich der Referenzwert auf diese Werte beziehen?
Und was soll geschehen, wenn der MAX-Wert mehrfach vorkommt? Was nicht unmöglich ist. Wie willst Du dann das Suchergebnis, oder das Suchkriterium, eingrenzen?



Gruß
balu



p.s:
Ich IMGF (industriemech.)

[prometheus]

Hallo,

hab Dank für die Mühen balu. Zum Thema:

Der Referenzwert kann beides sein. Er berechnet sich wie folgt:
Ich habe unter der Tabelle nicht nur die MAX-Werte der jeweiligen Spalte, sondern eben auch die MIN-Werte. Diese beiden Zeilen stehen wiederum untereinander und ich ermittle via ABS(MIN-Wert - Max-Wert) die maximale Differenz. Konkret also das größte Toleranzfeld am jeweiligen Zahn. Diese Differenz wird nun mit bestimmten Vorfaktoren versehen, um Offset-Werte für Ansys zu erhalten. Zum Beispiel 2/3.
Der Referenzwert ist somit 3/4*Differenz_max.

Wenn der gesuchte Wert doppelt vorkommt ( das ist aufgrund der Symmetrie _immer_ der Fall, ist es egal welcher der beiden ausgewählt wird, denn damit sind auch die Spalten identisch.
(Mir ist klar, dass ich somit eigentlich nur die Hälfte der tabelle benötigen würde, aber dies war nicht allein meine Entscheidung. Es hat natürlich für grafische Auswertungen auch Vorteile, das ganze Zahnrad zu betrachten und die Daten zu haben.)
Eingrenzen kann ich die Suche ja nur, indem ich verlange, dass Refrenzwert-Prüfwert=minimal ist. Dann hab ich ja den passendsten Wert.

Ich hoffe, ich konnte die Situation aufhellen. Nochmal : Vielen Dank!

prome

[prometheus]

Hallo,

ich denke, ich bin der Sache etwas näher gekommen.
Ich habe eine normale WVERWEIS-Suche benutzt und finde ja nun, da die Werte in der Zeile von links nach rechts bis zur Mitte (bei z/2)aufsteigen, einen Wert und SVERWEIS liefert mir den nächstlinken davon zurück. Somit bin ich tendenziell immer ein wenig unterhalb des passendsten Treffers, der sich eine Position weiter rechts befindet.
Wenn ich nun mit dynamischen Zellbezügen eine WENN-Bedingung formuliere, die mir die Differnzen vom Suchkriterium zum SVERWEIS-Treffer mit dem rechten Nachbarn vergleicht, müsste ich doch den optimalen Treffer haben. Dass Werte doppelt vorkommen könnte ich ja umgehen, indem ich den Suchbereich ebenfalls dynamisch gestalte (in Abhängigkeit von z)
Vllt hat ja jemand eine Idee wie ich eine dynamische Adressierung des Nachbarn hinbekomme.

@balu: Sieht das für Dich nach einer mögl. Lösung aus ?

Schönen Abend!

prome

[balu]

Hallo Prome,

also irgendwie schwirrt mir der Kopf. Momentan versteh ich nix mehr (wohl zu lange an der datei gearbeitet).

und ich ermittle via ABS(MIN-Wert - Max-Wert) die maximale Differenz

Habe ich jetzt auch gemacht. Jedoch nicht unter der Tabelle, sondern oberhalb. Da muss man nicht andauernd runterrollen, um was zu sehen.

Diese Differenz wird nun mit bestimmten Vorfaktoren versehen, um Offset-Werte für Ansys zu erhalten. Zum Beispiel 2/3.

Verstehen tu ichs im mom gar net, aber vielleicht liege ich hiermit ja richtig.

Code: Alles auswählen

=ABS(F11-F12)*2/3

Weiss abba heut net wat i damit mochen soll.

Der Referenzwert ist somit 3/4*Differenz_max

In etwa so?

Code: Alles auswählen

=ABS(F11-F12)*3/4

Ich habe jetzt aus der MAX-Zeile den MAX-Wert rausgesucht, und dann noch mit 3/4 multipliziert.
Ob das deinen Vorstellungen entspricht, weiss ich jetzt nicht.

Ich glaube das irgendwo in der Datei ein Fehler von mir ist, den ich aber heute nicht mehr raussuche. Habe jetzt auch keine große Lust etwas zu der Datei zu sagen. Schau sie dir mal an, und es geht dann die Tage weiter.

@balu: Sieht das für Dich nach einer mögl. Lösung aus ?

Sorry, abba heut nix mähr verstahn. Später


Gruß
balu

[balu]

Hallo prome,

jetzt weill ich noch ne kurze Erklärung zu der Datei geben.

Bevor Du diesen Thread hier eröffnet hast, hatte ich mir so meine Gedanken bezüglich Referenzwert gemacht. Und ich hatte das bis gestern so verstanden, dass ein x-beliebiger eingegebener Wert in dem Datenbereich E16 bis CZ115 gesucht wird, und anschließend die gefundene Spaltennummer ausgegeben werden soll.

Bei Ganzen Zahlen ohne Komma (10 12...) wäre das ja kein großes Problem gewesen. Jedoch bei einer Dezimalzahl mit Komma (11,1123) als Suchkriterium und auch bei solchen Zahlen im Datenbereich, wird es schon etwas schwieriger. Und wenn jetzt auch noch eine Zahl gesucht wird die NICHT exakt im Datenbereich vorhanden ist, dann wird es echt schwer eine übereinstimmende Zahl mit einer minimalen Abweichung zu finden. Und genau daran hatte ich rumgebastelt wie in Verrückter.

Das momentanige Ergebnis, welches mir bis jetzt am ehesten gefällt, ist in der Beispieldatei zu sehen. Im Prinzip ist es eigentlich recht einfach, welches ich aber noch ausbauen werde.

Verfahren und Funktionsweise:
Wenn man also unterschiedliche Dezimalzahlen suchen und vergleichen will, dann muss man versuchen den unterschied so gering wie möglich zu gestallten. Und das geschieht am besten dadurch, indem man die Nachkommastellen auf ein minimum reduziert. Für dieses Verfahren stellt Calc verschiedene Methoden zu Verfügung, um die Zahlen zu Runden.
Als da wären:
AUFRUNDEN, ABRUNDEN, RUNDEN, VRUNDEN, OBERGRENZE und UNTERGRENZE.

Blos für welche soll man sich da entscheiden?
Am besten testen.
Und momentan habe ich mich einfach für RUNDEN entschieden.
Nur mit dieser Funktion alleine kann man nicht viel anfangen, es fehlt noch die geeignete Suchfunktion, und ich habe mich für SUMMENPRODUKT entschieden, nach dem ich aber auch andere getestet hatte.

Und nun kann gesucht werden. Das nach folgendem Verfahren funktioniert.
Zu erst wird das Maximum pro Spalte ermittelt und in der jeweiligen Spalte ausgegeben. Jetzt wird das Maximum aller Maximas gesucht, und anschließend mit 3/4 Multipliziert. Dieser Wert ist jetzt der Referenzwert.

Nun wird dieser Wert durch RUNDEN bis auf eine Stelle hinter dem Komma gerundet. Jetzt wird auch die gesammte Spalte mit RUNDEN gerundet. Und nun vergleicht SUMMENPRODUKT den einen Wert mit der gesamten Spalte und summiert die Anzahl der übereinstimmungen. Wenn keine übereinstimmungen, dann wird auch nix summiert und eine leere Zelle ausgegeben.

Und zu guter letzt wird mit einer anderen Formel die Zeile mit den SUMMENPRODUKT-Formeln durchsucht, und die dementsprechende Spalte mit der erst gefunden Zahl zurückgegeben.

Da die erste Datei einen dummen Fehler hatte (formeln nicht als matrixformel abgeschlossen), habe ich das geändert und jetzt erneut angehangen.



Gruß
balu

[prometheus]

Hallo,

danke für die umfassende Erklärung. Ich habe gestern Abend angefangen, ein wenig rumzuspielen und zu probieren und habe jetzt vorläufigen Lösungsweg gefunden:

Ich bilde ebenfalls MIN- und MAX-Werte, bilde daraus die maximale Differenz, multipliziere diese mit einem Faktor (3/4) und suche mit WVERWEIS in der Zeile der maximalen Differenzen den nächsten Wert. Dieser ist aufgrund der Symmetrie des Feldes immer kleiner als der Refrenzwert. Deshalb vergleiche ich den gefundenen Wert UND seinen rechten(nächstgrößeren) Nachbarn mit dem Referenzwert und wähle den Wert mit der kleineren Differenz aus.
Das scheint erstmal zu funktionieren.
Siehst du dabei Probleme ? Der gefundene Wert existiert zwar mehrfach in der Zeile, das stört WVERWEIS aber nicht, denn er beginnt links und ich übergebe als Parameter eine sortierte Zeile (das ist sie ja auch bis zur Mitte). Da in der Mitte immer das Maximum liegt, finde ich meinen 3/4*Max Wert natürlich immer links davon.

besten Gruß

prome

[balu]

Hallo prome,

Siehst du dabei Probleme ?

Sorry, aber ich sehe gar nichts .
Es ist besser wenn Du meine Datei nimmst, und deine Formeln da reinbaust und dann hier anhängst.



Gruß
balu

[prometheus]

Moin,

ich stelle einfach mal meine Datei hier rein. Sollten beim Durchschauen Fehler auftreten, bitte ich um Kundmachung.

Gruß

prome

[balu]

Hallo Prome,

Idee gut .
Ausführung auch nicht schlecht .
Kleine Verbesserungen möglich? Ja.

Du arbeitest in deinen Formeln mit zu vielen überflüssigen Klammern.

Code: Alles auswählen

=INDIREKT(ADRESSE(118;((VERGLEICH(D121;ABS_DIFFERENZEN)+5))))

=WENN(z>=B16;INDIREKT(ADRESSE((ZEILE(B16));((VERGLEICH(diff_gef_opt;ABS_DIFFERENZEN)+4))));"")

Das Du dabei nicht den überblick verlierst, wundert mich. Wenn Du die von Hand gesetzt hast, dann gewöhn dir das mal bitte schnell ab. Die Gefahr das Du dich mit ihnen vertust, ist sehr groß.
Hier die gleichen Formeln ohne überflüssige Klammern.

Code: Alles auswählen

=INDIREKT(ADRESSE(118;VERGLEICH(D121;ABS_DIFFERENZEN)+5))

=WENN(z>=B16;INDIREKT(ADRESSE(ZEILE(B16);VERGLEICH(diff_gef_opt;ABS_DIFFERENZEN)+4));"")

Die letzt zitierte Formel hast Du im Blatt Aufbereitung ab D132. Und genau da hab ich dir jetzt mal was eingebaut, was das Leben erleichtert. Denn 100 mal das gleiche vergleichen und suchen, kann man auch beim Einsatz von INDIREKT vereinfachen.
Das Problem dabei ist ja der Spaltenname, den man ja nicht so simpel auslesen kann wie die Zeilennummer. Aber auch das ist machbar.

Hier mal das vereinfachte Verfahren für nur einen Spaltenbuchstaben.

Wenn es sich nur um den Spaltenbereich A bis Z handeln würde, dann könnte man ja ganz einfach die gefundene Spaltennummer mit der Codezahl für den dementsprechenden Buchstaben verrechnen. Und das würde dann so funktionieren.

Beispiel:
Gefundene Spaltennummer ist gleich 11.
Der 11. Buchstabe ist K, und hat die Code-Nummer 75.
Der erste Buchstabe im Alfabet ist das A, und hat die Code-Nummer 65.
Damit nicht immer die differenz der gefundenen Spalte zu der ersten Spalte ausgerechnet werden muss, reduzieren wir den Code-Wert vom Buchstaben A auf 64. Und schon können wir ganz easy die Spaltennummer 11 dazu addieren und bekommen den Code-Wert von 75 raus. Und mit ZEICHEN() wird aus dieser Zahl ein Buchstabe, nämlich K.

Formel wäre wie folgt.

Code: Alles auswählen

=ZEICHEN(64+VERGLEICH(D121;A118:CZ118;0))

Der einfachheitshalber fängt die Suchzeile bei Spalte A an. Mann kann auch bei E anfangen, nur muss das dann an der richtigen Stelle wiederum ausgeglichen werden (z.B. +4).

Da jetzt der Buchstabe ja bekannt ist, können wir das in der INDIREKT-Formel anpassen. Dazu sagen wir, das die Formel für den Buchstaben in G122 steht. Und die Formel in D132 wird dann jetzt auf das hier geändert.

Code: Alles auswählen

=WENN(z>=B16;INDIREKT($G$122&ZEILE(B16));"")

Ist ganz schön kurz geworden, gelle .

In der angehängten Version ist natürlich dieser einfache Schritt nicht drin. Denn dort ist nämlich schon das Verfahren komplett ausgebaut drin, welches automatisch auch die Spaltennamen ausgibt, die oberhalb von Z liegen. Also der Bereich von AA bis CZ. Da ich das nicht in einer Zelle lösen wollte, habe ich das auf 3 Zellen aufgeteilt.
Die Formel in D132 ist aber mit dieser hier vollkommen identisch.

Wenn Du die ganzen Formeln in den 3 zusätzlichen Zellen nicht wirklich verstehst, dann sag bescheid .


Gruß
balu

[balu]

Ich bins noch mal.

Fals dir meine neue Methode (das Indirekte addressieren) nicht gefällt, dann gibts auch noch eine Alternative. Die mir persönlich wohl nicht so gut gefällt, die ich dir aber dann dennoch präsentieren würde.

Gruß
balu

[prometheus]

Hallo balu,

danke für die Verbesserungsvorschläge - sind schon drin.
Ich denke man sieht recht deutlich, wie mein Klammer-,Formelwust entsteht: Da ich Neuling in Calc bin, muss ich mit dem wenigen bereits gelernten Handwerkszeug auskommen und bastel deshlab keine sehr eleganten Lösungen zusammen. Ich denke aber doch, dass sich das noch verbessern wird. Allerdings muss ich sagen, dass mir Klammern bei der Übersichtlichkeit doch helfen. Aber Du hast mit Deinem Argument schon recht - auch in der Mathematik gilt Schlichtheit als schön. In diesem Sinne hab ich sie rausgenommen.

Zurück zum Thema:
Meiner Meinung nach können wir es an dieser Stelle erstmal dabei belassen. Die nächsten Schritte erfordern sicherlich einen neuen Thread.Mir graut es jetzt schon

Abschließend also nochmal herzlichen Dank für die kompetente, umfassende und zeitnahe Hilfe. (In jedem Sinne) Wirklich vorbildlich!

prome