HLOOKUP: gibt ungenaue Daten abhängig von der Formel für den Nachschlagewert

864
PonNab

Bei dem Versuch, mit der Funktion HLOOKUP zu arbeiten, bin ich auf dieses seltsame Problem gestoßen. Ich habe hier einen einfachen Fall erstellt, um das zu demonstrieren.

In der Spalte, die in C7 beginnt, gebe ich die hlookup-Funktionen ein (in Spalte D gezeigt). Die Nachschlagewerte beginnen in Spalte B. Die Formeln in Spalte A sind die, die ich in Spalte B eingegeben habe.

Die Referenztabelle befindet sich in den ersten beiden Zeilen. Der Nachschlagewert entspricht der ersten Zeile und der entsprechende Wert in der zweiten Zeile wird als Ausgabe zurückgegeben.

Excel-Screenshot

Ich habe versucht, den Wert .01 und .02 in der Tabelle nachzuschlagen.

Wenn es richtig funktioniert hat, sollte "B" für .01 und "C" für .02 zurückgegeben werden

Aber wie ich hier sehe, bekomme ich nur die richtige Antwort, abhängig davon, welche Formel ich für den Nachschlagewert verwende.

Im Fall von .01 bekomme ich beispielsweise die richtige Antwort, wenn ich die Formeln ".01" und ".31-.3" verwende, nicht aber für andere wie ".11-.1".

Ebenso für .02 bekomme ich die richtige Antwort, wenn ich die Formeln ".02" und ".32-.3" verwende. Alles andere gibt mir den Wert in der Zelle vor der richtigen.

Bitte lassen Sie mich wissen, was hier los ist!

2
Was hast du in B geschrieben, nur Werte oder eine Formel, um A zu konvertieren yass vor 7 Jahren 0
B enthält die Formel in A (ohne Anführungszeichen). B9 hätte also "= 0,21 - 0,2" PonNab vor 7 Jahren 0
Ich habe den Screenshot aktualisiert, um zu zeigen, wie die Funktion für eine Zelle in der Spalte B aussieht PonNab vor 7 Jahren 0
= HLOOKUP (RUND (B15,2), $ A $ 1: $ D $ 2,2) Verwenden Sie Round (B15,2), um die genaue Anzahl der Dezimalstellen zu erhalten, oder in B15 = Round (0.22-0.2,2). yass vor 7 Jahren 0
Es ist interessant, weil "0.11-0.1" und "0.21-0.2" A zurückgeben, aber "0.31-0.3" B zurückgibt Vylix vor 7 Jahren 0
Adi Nugroho - gibt die richtige Lösung. Ich fügte einen Kommentar hinzu, warum es am Ende seiner Lösung funktioniert. Der Suchindex durchsucht INTEGERS- oder TEXT- oder STRING-Werte, keine Zahlen mit doppelter Genauigkeit oder reelle Zahlen. Es handelt sich um einen Suchindex, also schnell und nicht komplex und in einer lexikalischen Reihenfolge von klein bis groß. ejbytes vor 7 Jahren 0

2 Antworten auf die Frage

2
Vylix

Ich habe ein bisschen experimentiert und herausgefunden, dass die beste und konsistente Methode darin besteht, sowohl den Lookup-Wert als auch das Lookup-Array in die TEXTFunktion zu integrieren

Array formula of TEXT

TEXT(B4, "0.00") bezieht sich auf den Nachschlagewert und formatiert den Wert in zwei Dezimalstellen.

TEXT($A$1:$D$2, "0.00") bezieht sich auf die Nachschlagetabelle und formatiert alle Zahlen in zwei Dezimalstellen.

WICHTIG! Sie müssen das gleiche Format verwenden und Sie müssen Ctrl + Shift + Enterstatt Enterzur Eingabe einer Matrixformel.

EDIT: Ein Suchindex der Tabelle (das, was es in dem ersten Suche sucht) sucht TEXToder INTEGERnicht longoder doubleZahlen oder reelle Zahlen.

Wenn Sie die Nachschlagetabelle oder den Wert aus Text csvimportieren oder importieren, sollten Sie sie als importieren TEXT. Sie können mit der VALUEFunktion wieder in eine Zahl umwandeln . Oder Sie können mit anderen Methoden in eine Zahl konvertieren .

Ich habe gerade eine ähnliche Antwort gepostet, aber dies reicht aus, also habe ich meine gelöscht. Der Grund, warum sich VLOOKUP und HLOOKUP so verhalten, ist in Ihrer Lösung genau angegeben. Der Suchindex einer Tabelle (der Gegenstand, nach dem die erste Suche gesucht wird) sucht nach TEXT oder INTEGER, nicht nach langen oder doppelten Zahlen oder reellen Zahlen. Sie können diesen Kommentar in Ihre Lösung einfügen, wenn Sie möchten. Ich habe Ihre Lösung einmal gewählt, weil es die richtige Lösung ist. ejbytes vor 7 Jahren 0
Auch ein Kommentar für das OP oder eine Folge zu Ihrer Lösung. Das Gegenteil funktioniert auch. Sie können Text (als ob Sie Daten importieren) als Wert in den "Kopfzeilen" der Tabelle anstelle von Zahlen mit doppelter Genauigkeit verwenden. Sie könnten dann aber nicht auf der Grundlage von Mathematik suchen (z. B. AB = 0,001) - und Sie müssten erneut zu der obigen Lösung greifen. ejbytes vor 7 Jahren 0
Noch ein Kommentar für Adi. Auch ein anderer "Grund" und für eine vollständige Antwort einzufügen. Bei der Suche nach INDEX in einer Tabelle in einem bestimmten Excel-Algorithmus wie HLOOKUP / VLOOKUP führt ein Index, der aus Zahlen mit doppelter Genauigkeit besteht (z. B. 0,001, 0,002 ...), zu einem CEILING-Ergebnis in einer nicht genauen Übereinstimmung (z. B. 0,001 wird zu int 1, 0,01 wird 1 oder sogar 0,000001 wird 1). ejbytes vor 7 Jahren 0
Vielen Dank Adi. Am Ende habe ich ein langes Makro geschrieben, um das zu tun, was ich wollte, aber ich werde es mir für das nächste Mal merken. PonNab vor 7 Jahren 0
@PonNab Sie können diese Antwort als Antwort markieren, indem Sie auf das Häkchen neben der Schaltfläche "Abstimmen" klicken Vylix vor 7 Jahren 0
@ ejbytes Ist es "DECKEN"? Ist das nicht davon abhängig, wie die Nachschlagetabelle sortiert ist? "LOOKUP" für "0.1" in "0", "0.5" und "1" ausprobiert, gibt "0" anstelle von "0.5" oder "1" zurück Vylix vor 7 Jahren 0
@AdiNugroho Ah, ich verstehe. Ich vermute, ich habe einige Tests angenommen. Ich vermute, der Algorithmus ist nicht so klar, wie ich es vermutet habe. Eine lustige Sache, nachdem ich die Zellen formatiert, ein paar Dinge ausprobiert und später darauf zurückgekommen bin ... es funktionierte ohne besonderen TEXT (...) oder irgendetwas ... um zu sagen, dass wir uns alle geirrt haben und es funktioniert einfach. Das ist aber nicht der Fall. Excel verfügt über eine "seltsame" Funktionalität. Selbst das Neuformatieren von Zellen dauert nicht lange, wenn nicht jede Zelle aktualisiert wird. ejbytes vor 7 Jahren 0
"TEXT" entspricht dem Formatieren der Zelle als "TEXT". Sie können die Tabelle als TEXT formatieren, die Tabelle kann jedoch nicht zur Berechnung verwendet werden. Ich habe das absichtlich nicht hinzugefügt und die ursprüngliche Nachschlagetabelle belassen, da die Lösung in der Formel liegen sollte und nicht die Tabelle ändern sollte. Wenn jemand die Tabelle ändert, wird die Suche abgebrochen. Nicht bei meiner Lösung. Vylix vor 7 Jahren 0
1
Aganju

HLOOKUP (und auch VLOOKUP) hat einen vierten (optionalen) Parameter, der definiert, ob entweder nach einer exakten Übereinstimmung gesucht wird oder nach dem letzten Wert, der nicht größer als der Suchwert ist. Die Standardeinstellung ist Letzteres.

Grundsätzlich funktioniert es wie geplant . Lesen Sie die Hilfe und stellen Sie den vierten Parameter ein, um das gewünschte Ergebnis zu erzielen.

Wenn Sie den vierten Parameter als exakte Übereinstimmung festlegen, wurden Fehlerwerte für alle Formeln (z. B. ".11 -.1") angegeben. Es funktionierte nur, wenn der Wert direkt eingegeben wurde PonNab vor 7 Jahren 0

Verwandte Probleme