[Gelöst] Vergleich von 3 Werten und kleinster gemeinsamer Nenner finden

Deutsch
, , , ,
#1

Hallo zusammen,

ich habe eine Tabelle mit 4 Spalten. In der ersten Spalte steht die “Zahnfarbe” und in den darauffolgenden 3 Spalten jeweils ein Wert, der der Zahnfarbe in der gleichen Zeile zugeordnet ist. Nun möchte ich in ein Suchfeld einen gemessenen Wert (aus 3 Zahlen) eintragen, wobei diese 3 Zahlen mit jeder Zeile aus der Tabelle abgeglichen werden sollen. Die Zeile, die den Werten am nächsten kommt, sollte dann angezeigt werden.

Ich hoffe mein Vorhaben ist verständlich erklärt.

LG Jorek
Farbring Heraceram1.ods

#2

Nicht gelöst sind auch noch ein paar Dinge:
-1- Das Vorliegen einer “vollständig befriedigenden” Antwort wird in einer AskBot Q&R-Site (anders als in vielen Foren) nicht durch Hinzufügung eines Schlüsselworts zum Betreff gekennzeichnet, sondern durch Markieren der betreffenden Antwort als korrekt (Häkchen klicken!).
-2- Der Betreff (Wortlaut)sollte verbessert werden:
-2a- Die irreführende Erwähnung des kgV sollte entfernt werden.
-2b- Dass es um Zahnfarben bzw. um LBA-Werte geht, sollte erwähnt werden. So könnte jemand, der dazu eine existierende fachliche Lösung kennt, zu einer Antwort animiert werden.
-2c- Vorschlag: Zu einem Tripel (3 Zahlen, LBA / Dentinfarbe) das bestpassende aus einer Liste wählen.

#3

Eine mathematische Lösung besteht in der Berechnung des harmonischen Mittelwertes: Von jedem Wert der “Dentin”-Tabelle (Vektor L, A, B) wird die quadratische Differenz zum Messwert berechnet. Anschliessend werden die Quadrate summiert und dann das Minimum dieser Reihe festgestellt. Das Minimum wäre dem entsprechenden Farbwert recht nahe.
Hier die Formel:
(L(A1)-L(Messwert))**2 + (A(A1)-A(Messwert))**2 + (B(A1)-B(Messwert))**2.
Die Formel ist für jede Zeile (A1 bis D4) anzuwenden. Ein anschliessender Vergleich würde dann den Ergebnisvektor liefern.
(Kann leider meine Tabelle gerade nicht ins Netz stellen, ich hoffe aber, dass ich deutlich genug den Weg beschrieben habe)
Übrigends: das Ergebnis des mitgelieferten Messwertes ist C1.

(Zur besseren Lesbarkeit geringfügig editiert durch @Lupp.)
Anmerkung: In manchen Programmiersprachen dient ** als Operator der Potenzierung. In Calc (und auch in LibreOffice Basic) ist das ^.

#4

Wow! Superschnell und auch verständlich! Ich probiere es gleich aus. Vielen lieben Dank!

#5

Werner, ich bräuchte bitte nochmal deine Hilfe!

In Spalte O5:O20 habe ich alle errechneten Werte. In O21 möchte ich via sVerweis den Wert nahe 0 ermitteln. In meiner Tabelle zeigt er mir nun -0,6 an… Zelle O8 ist doch aber dichter an 0. Woran liegt das?

Alles in allem, möchte ich, dass mir A27 die passende Zahnfarbe auswählt. Wie komme ich zu meinem Ziel?

ps: ich habe die Tabelle nochmal aktualisiert und unter eingangs erwähnten Link hochgeladen

#6

Eine(!) mögliche Formel für O21 könnte lauten: - Allerdings: Ein Problem mit dieser Formel tritt auf, wenn ein Wert mal mit positiven und mal mit negativen Vorzeichen auftritt. Dann wird derjenige gefunden, der zuerst inO5:O20steht, Warum DeinVLOOKUPden Wert-0,60zu Tage fördert ist mir jetzt auch nach Lesen der OASIS Spezifikation ein Rätsel zumal nach manueller Sortierung tatsächlich der erwartete Wert-0,18` geliefert wird (Das ist auch in OpenOffice 4.1.7 schon so). Da ich (Denk-)Fehler in Sachen Calc Formeln eigentlich immer erstmal bei mir suche, traue ich mich noch nicht von einem Bug zu sprechen.

Alles in allem, möchte ich, dass mir A27 die passende Zahnfarbe auswählt

Was hat dann O21 mit D27 zu tun. Wie ist der Zusammenhang des Wertes mit der Zahl? Ist es schlicht dieselbe Zeile in der der Minimumwert und die Dentinname stehen - Dann in Zelle A27: =OFFSET(A5;MATCH(SUMPRODUCT(MIN(ABS(O5:O20)));ABS(O5:O20);0)-1;0)

#7

Vorab vielen Dank @anon73440385
Um dieses Vorhaben von Grund auf zu erläutern:
Ich besitze ein Dentallabor.
Diese Tabelle dient der Erstellung eines individuellem Farbmischungsverhältnises. Es wird digital ein Farbwert im LAB Farbraum gemessen, der der Situation im Patientenmund entspricht. Dieser Farbwert soll nun reproduziert werden… d.h. ich gleiche die Patientenmessung mit dem Farbwert der uniformen Keramiken ab und erstelle mithilfe dieser Tabelle ein individuelles Farbmischungsverhältnis, um den Farbwert im Patientenmund zu erreichen. Die uniforme Keramik wird mit den ermittelten Farben modifiziert. In meiner Tabelle soll also erst ein Farbwert der Keramik gefunden werden, der dem Farbwert des Patienten am nächsten kommt und danach ein Mischungsverhältnis berechnet werden, um den Farbwert exakt zu treffen. D27 (Dropdown) bildet die Farbwerte der Keramiken ab. O21 stellt in meinem Fall ein Vergleichswert zwischen Patientenzahnfarbe und Keramikfarbe da.

#8

Zurück zum Thema:

Leider entstehen bei der Eingabe der gemessenen Werte in dieser Formel negative, sowie positive Vorzeichen. Problematisch dabei ist, dass ich diese Werte nicht sortieren kann, da ich sonst keine Zuordung zu den in Spalten A5:D20 definierten Farben herstellen kann. D27 dient lediglich der Auswahl einer Farbe der Keramik zur Berechnung des Mischungsverhältnisses.

#9

@jorek - danke für die Erläuterung, das habe im Grundsatz schon so verstanden, dass Du eine passende Zahnfarbe suchst. Sorry - ich habe mich natürlich mal wieder verschrieben. In meinem Kommentar sollte es naürlich A27 statt D27 heißen. Mir isr nur nicht vollständig klar, rein technisch gesehen, wie Dich jetzt der in Zelle O21 mit -0.18 gefundene Wert dazu bewegt (in der aktuellen Version der Datei) in A27 eine bestimmten Wert auszuwählen. Ist es die Tatsache dass der Wert -0,18 (4.ter Wert in O5:O20) in derselben Rehe steht wie A3,5. Anders: Der Index (=Reihe) in O5:O20 bestimmt den (ist gleich dem) Dentin Index (=Reihe) in A4:A20?

Soweit ich Deinen letzten Kommentar verstehe, habe ich das richtig interpretiert und Du kannst den Dropdown durch meine zweite Formel ersetzen.

PS: Du musst mit meiner Formel nicht sortieren. Nur im Zweifel muss Du sagen, wass passieren soll, wenn ein Wert (völlig identisch) mal positiv, mal negativ vorkommt. Welcher soll dann gelten

#10

Ich sehe gerade: mein erster Kommentar oben ist ja völlig durcheinander geraten (und bearbeiten kann ich das nicht mehr). Daher nochmal die Formel, die da stand oder stehen sollte:

O21 =OFFSET(O5;MATCH(SUMPRODUCT(MIN(ABS(O5:O20)));ABS(O5:O20);0)-1;0)
A27 =OFFSET(A5;MATCH(SUMPRODUCT(MIN(ABS(O5:O20)));ABS(O5:O20);0)-1;0)

(Nachdem was ich bisher glaube verstanden zu haben, braucht es O21 eigentlich gar nicht, da es ja um eine automatische Bestimmung von A27 geht).

#11

Du hast es richtig interpretiert. -0,18 ist der Zahnfarbe A3,5 zugeordnet. Allerdings macht mir mein Verstand mittlerweile einen Knoten in den Kopf! :smiley:
Setze ich deine Formel in A27 ein, dann erscheint die Fehlermeldung “#WERT!”.

Zudem ist mir aufgefallen, dass wenn mein Messwert in D28 kleiner als D27 ist, auch ein Fehler produziert wird.

PS: Ich habe die Tabelle nochmal in übersichtlicher Form hochgeladen

#12

Hallo, schau Dir bitte diese modifizierte Datei an. Farbring-Heraceram1-Moifiziert.ods

Ich habe da die beiden Formeln aus meinem letzten Kommentar eingearbeitet und die Daten -> Gültigkeit -Einstellungen so, angepasst, dass Du wählen kannst: Formel nehmen oder Wert (Falls man mal etwas überschreiben will).

PS: Schau bitte immer auch in die Formelzeile, um genau zu erkennen, was in A27 wirklich selektiert ist:

Und zur Sicherheit - folgende Information: Getestet mit LibreOffice:

Version: 6.3.4.2,Build-ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa
CPU-Threads: 8; BS: Linux 4.12; UI-Render: Standard; VCL: kde5; 
Gebietsschema: de-DE (de_DE.UTF-8); UI-Sprache: de-DE, Calc: threaded
#13

Fantastisch! Jetzt macht die Tabelle genau das was sie soll! Ich werde mir das nochmal im Detail anschauen und nachlesen, da ich eigentlich ein Freund von autodidaktem Lernen bin. Vielen Dank für die umfangreiche Hilfe!

#14

Ich musste nach meiner Mail ganz schnell meinen AP verlassen (Kunde) hat gerufen. (Auch jetzt bin ich unterwegs, es reicht also nur mal so für einen schnellen Überblick. Meine angegebene Formel wurde falsch interpretiert, dadurch entstehen negative Werte. Statt dieses falschen Ansatzes ist in Spalte O besser die Formel =WURZEL(QUADRATESUMME(B5-$B$28;C5-$C$28;D5-$D$28)) zu verwenden. Der restliche Weg kann dann unverändert übernommen werden.(die angegebene Formel multipliziert die Differenz nur, statt das Quadrat zu bilden. In diesem Fall kommt zufällig das selben Ergebnis heraus.)
Ich kann mich erst heute Nacht wieder melden!

#15

@jorek - Wenn es Dir geholfen hat, wäre es nett, das durch Anklicken des kleinen Häkchens (:heavy_check_mark:) direkt neben der Antwort (wird grün dabei) kenntlich zu machen. Vielen Dank …

#16

“Kleinstes gemeinsames Vielfaches” ist ein feststehender Begriff aus der Welt der ganzen Zahlen, Dafür gibt es in Calc die Standardfunktion (en: Least Connon Divisor )LCM() (de wohl:) KGV(). Einschlägiger Partner ist GCD() bzw. GGT().
Das harmonische Mittel wiederum, das manchmal in Matematik und Statistik benutzt wird ist auch als Standardfunktion vertreten: (en:) HARMEAN(). (Da weiß ich grade den “lokalisierten” Funktionsnamen nicht.)
Was @Werner_RJP dann näher beschreibt, die “Abweichungsquadratsumme” ist einAusdruck, der z.B. zu einem Standardschätzverfahren für die Parameter zur Auswahl einer Funktion “Methode der Kleinsten Quadrate” passt. Mit dem harmonischen Mittel finde ich da keinen Kontakt.
Ich vermute, dass der Betreff der Frage und die Einleitung der Antwort auch bei anderen Lesern Verwirrung stiften können.

#17

Die Zeile, die den Werten am nächsten kommt, sollte dann angezeigt werden

@Lupp stellt natürlich mal wieder implizit die richtige Frage: Was heißt die den Werten am nächsten kommt genau und für mich macht es da auf keinen Fall Sinn mit absoluten Größen und Abweichungen zu arbeiten, sondern nur mit relativen Abweichungen - also etwas wie =QUADRATESUMME((B5-$B$28)/B5;(C5-$C$28)/C5;(D5-$D$28)/D5).

#18

@Lupp: Haste Recht, ich bin nicht so schnell auf den Begriff gekommen, da ich auf dem Sprung war. Ich habe recht umfangreich mit dem harmonischen Mittelwert und der Standardabweichnung gearbeitet, sodass ich den falschen Begriff “eingeworfen” habe. Aber der Ansatz war letztlich doch hilfreich.Dennoch wäre dem Threaderöffner zu empfehlen, die Methode der Summe der kleinsten Quadrate, bzw. die Standardabweichung der Differenzen zu verwenden und nicht die Differenz mal 2. Dadurch entstehen keine negativen Vorzeichen.
Zu @anon73440385 durch die “Normierung” mit dem Vektor B5/C5/D5 werden die Werte gegenüber B28… größenmäßig zueinander verändert und es kommen ganz andere Vergleichswerte raus. Man sollte daher in der Größenordnung besser bleiben.
Um ein Ergebnis ohne weiteres “Probieren” zu erhalten, würde ich die Spalten N4:21 und O4:21 vertauschen (O4:21 vor N4:21 setzen, also M4:21) und in das Feld A27 =Sverweis(M21;M5:N20;2) einfügen.
Die Gültigkeit in Zelle A27 wäre dann zu löschen.

#19

@Werner_RJP - das mit der Normierung sehe ich völlig anders. Eine Abweichung von zum Beipiel in Spalte C des Messwert von 100 Prozent von einem Bezugswert hat wegen der geringen absoluten Größe kaum eine Bedeutung im Vergleich zu einer 30%igen Abweichung in B. Gerade die Betrachtung der relativen Größen bewirkt eine - wenn man es so nennen will - Normierung auf 1. Aber am Ende beantwortet aber auch das nicht die technische Frage, was im Sinn der Zahnfärbung unter *die den Werten am nächsten kommt * wirklich sein soll.

#20

@Lupp vs @Lupp: “(en: Least Connon Divisor )LCM()” ist doch Schmarrn.
Ja, aber wie ich das bemerkt habe, war der Kommentar schon eingefroren. “Least Common Multiple” muss es natürlich heißen.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.