Excel - SVERWEIS mit Intervall als Suchkriterium oder alternative Funktion
Guten Tag,
Ich versuche eine Excel-Tabelle zu erstellen aber habe Schwierigkeiten meine Idee umzusetzen.
Das Resultat stelle ich mir so vor: Ich gebe einen Wert (bzw. eine Reihe von Werten) ein und Excel sucht in einer vorher erstellten Tabelle diesen Wert und zeigt einen dazugehörigen Wert an. Mit SVERWEIS wäre dies einfach umzusetzen aber es gibt ein Problem. Da es sich bei meinen Werten um Messungen handelt sind diese nicht 100% genau. Darum möchte ich, dass ein ähnlicher Wert mit der maximalen Abweichung von z.B. 0,1 gesucht wird. Ist ein solcher Wert in meiner Tabelle nicht zu finden, soll beispielsweise "-" angezeigt werden.
Ich bedanke mich im Vorraus und freue mich auf eure Vorschläge!
4 Antworten
Hallo,
mir fallen da zwei Möglichkeiten ein:
1.: Kannst Du in der Datentabelle eine zusätzliche Spalte einfügen? Dann füge dort die gerundeten Messwerte ein "=runden(A1;1)" und referenziere darauf statt auf die Original-Messdaten-Spalte.
2.: Du rundest in der sverweis-Abfrage, wobei Du dann mit einer Matrixfunktion arbeiten musst. Annahmen:
1) Deine Originalmessdaten stehen in den Zellen A1 bis A99
2) Die zugehörigen Werte, die Du mit SVERWEIS auslesen willst, stehen direkt daneben in B1 bis B99
3) Dein Suchkriterium steht in Zelle G1, und den dazu gesuchten Wert willst Du in H1 anzeigen lassen, dann lautet die Funktion in H1:
=WENN(ISTNV(SVERWEIS(G1;RUNDEN($A$1:$B$99;1);2;FALSCH));"-";SVERWEIS(G1;RUNDEN($A$1:$B$99;1);2;FALSCH))
Zuviel auf einmal?
Okay, die eigentliche SVERWEIS-Funktion lautet:
SVERWEIS(G1;RUNDEN($A$1:$B$99;1);2;FALSCH)
Und die bettest Du ein in eine Wenn-Prüfung, ob nämlich der gesuchte Messwert nicht vorhanden ist (und dann ein #NV anzeigen lassen würde). Wenn ja, dann soll "-" das Ergebnis sein, wenn nein, dann der SVERWEIS-Wert.
Ganz wichtig: Die Formel oben ist noch nicht ganz vollständig: Du musst sie nicht einfach mit "enter" beenden, sondern mit Steuerung - Umschalt - Enter. Dann wird sie als Matrix-Funktion wirksam, erkennbar an den geschweiften Klammern um die Formel herum.
Eventuell musst Du Anpassungen vornehmen:
1) Die ";2;" in den beiden SVERWEIS-Funktionen gilt nur, wenn die zugeordneten Werte direkt in der benachbarten Spalte stehen - ansonsten musst Du die Zahl entsprechend erhöhen (aber SVERWEIS kennst Du ja offenbar schon).
2) Die ";1)" in den beiden RUNDEN- Funktionen gilt dafür, dass auf eine Stelle hinterm Komma gerundet wird. Dann muss natürlich auch in der Abfrage (Zelle G1) eine Stelle hinterm Komma eingegeben werden.
Variante 2 funktioniert aber nur, wenn die zugeordneten Werte (in B1 bis B99) auch Zahlenwerte sind. Bei Text funktioniert das bei mir nicht - was mir nicht unbedingt logisch ist. Vielleicht weiß ja jemand anderes bescheid.
Löst das Dein Problem?
Vielen Dank für die ausführliche Antwort. Runden kam mir auch zuerst in den Sinn aber was passiert wenn der Originalwert 100,049 ist, mein Messwert allerdings 100,050. So würden die Werte auf 100,0 und 100,1 gerundet werden und dementsprechend nicht übereinstimmen, obwohl sie sehr nah bei einander liegen
Hi :)
dazu brauchst du eine verschachtelte Funktion das heißt
SVERWEIS und die WENN Funktion
bzw. wenn die Wertangaben 10, 20,30 wären müsste in die Formel eingebaut werden ist der wert kleiner als 10 = das und das wichtig ist das deine Tabelle mit 0 anfängt
wie würde die Formel denn aussehen? =SVERWEIS(WENN(???);matrix;spalte;0)
also was gebe ich für ??? ein?
Probier mal diese Formel (bei mir funktioniert es):
=SUMMENPRODUKT((A1:A10>=C1-0,1)*(A1:A10<=C1+0,1)*(B1:B10))
In diesem Beispiel steht in C1 das Suchkriterium, dass in A1:A10 gesucht werden soll. Dann wird der Wert aus B der entsprechenden Zeile ausgegeben.
Das funktioniert allerdings nur, wenn die Werte in A eindeutig sind und nicht mehrfach vorkommen. Das Problem hättes es aber auch beim SVERWEIS gegeben.
Wenn nichts gefunden wird, ist das Ergebnis 0.
So eine Aufgabe erledigst du schnell und elegant mit einem VBA-Makro! Du kööntst sehr schnell ermitteln, wieviele deiner Messwerte deinem Wert entsprechen und nebenbei statistische Werte ermitteln.