HLOOKUP: дает неточные данные в зависимости от формулы для поиска значения

772
PonNab

Я столкнулся с этой странной проблемой, пытаясь работать с функцией HLOOKUP. Я построил простой случай здесь, чтобы продемонстрировать.

В столбце, начиная с C7, я введу функции hlookup (показано в столбце D). значения поиска начинаются в столбце B. Формулы в столбце A - это те, которые я ввел в столбце B.

Таблица для справки находится в двух верхних строках. Значение поиска соответствует первой строке, а соответствующее значение во второй строке возвращает результат.

Excel Screenshot

Я попытался найти значения 0,01 и 0,02 в таблице.

Если он работал правильно, он должен вернуть «B» для 0,01 и «C» для 0,02

Но, как видно здесь, я получаю правильный ответ только в зависимости от того, какую формулу я использую для поиска.

Например, в случае 0,01 я получаю правильный ответ, когда использую формулы «.01» и «.31-.3», но не для других, таких как «.11-.1»

Аналогично для .02 я получаю правильный ответ, когда использую формулы ".02" и ".32-.3". Все остальное дает мне значение в ячейке, предшествующей правильной.

Пожалуйста, дайте мне знать, что здесь не так!

2
что вы написали в B только значения или формулу для преобразования A yass 7 лет назад 0
B содержит формулу в A (без кавычек). Таким образом, B9 будет иметь "= 0,21-0,2" PonNab 7 лет назад 0
Я обновил снимок экрана, чтобы показать, как выглядит функция для ячейки в столбце B PonNab 7 лет назад 0
= HLOOKUP (ROUND (B15,2), $ A $ 1: $ D $ 2,2) используйте Round (B15,2), чтобы получить точное число десятичных цифр, или в B15 = Round (0,22-0,2,2) yass 7 лет назад 0
Это интересно, потому что «0.11-0.1» и «0.21-0.2» возвращают A, а «0.31-0.3» возвращает B Vylix 7 лет назад 0
Ади Нугрохо - дает правильное решение. Я добавил комментарий, почему он работает в нижней части его решения. Индекс поиска ищет значения INTEGERS или TEXT или STRING, а не числа с двойной точностью или действительные числа. Это поисковый индекс, поэтому он быстрый и не сложный и в лексическом порядке от маленького до большого. ejbytes 7 лет назад 0

2 ответа на вопрос

2
Vylix

Я немного поэкспериментировал и обнаружил, что лучший и непротиворечивый способ - обернуть как значение поиска, так и массив поиска в TEXTфункцию.

Array formula of TEXT

TEXT(B4, "0.00") ссылается на значение поиска и форматирует значение с двумя десятичными знаками.

TEXT($A$1:$D$2, "0.00") ссылается на таблицу поиска и форматирует все числа до двух десятичных знаков.

ВАЖНЫЙ! Вы должны использовать тот же формат, и вы должны Ctrl + Shift + Enterвместо того, Enterчтобы вводить формулу массива.

РЕДАКТИРОВАТЬ: индекс поиска таблицы (вещь, которую он ищет в начальном поиске) ищет TEXTили INTEGER, а не longили doubleцифры или действительные числа.

Если вы импортируете таблицу поиска или значение из текста или csv, рассмотрите возможность импорта их как TEXT. Вы можете конвертировать обратно в число с помощью VALUEфункции. Или вы можете конвертировать в число, используя другие методы .

Я только что опубликовал аналогичный ответ, но этого вполне достаточно, поэтому я удалил свой. Причина, по которой VLOOKUP и HLOOKUP ведут себя так, как это указано в вашем решении. Индекс поиска в таблице (то, что он ищет в начальном поиске) ищет TEXT или INTEGER, а не длинные или двойные числа или действительные числа. Вы можете поместить этот комментарий в свое решение, если хотите. Я проголосовал за ваше решение по одному, потому что это правильное решение. ejbytes 7 лет назад 0
Также комментарий для ОП или следствие вашего решения. Противоположное также работает. Вы можете использовать текст (как при импорте данных) в качестве значения в «заголовках» таблицы вместо чисел с двойной точностью. Но тогда вы не могли бы искать на основе математики (например, AB = 0,001) - и вам придется прибегнуть к решению выше. ejbytes 7 лет назад 0
Еще один комментарий для Ади. Также еще одна «причина» и включение для полного ответа. При поиске и INDEX в таблице в заданном алгоритме Excel, таком как HLOOKUP / VLOOKUP, индекс, состоящий из чисел двойной точности (например, 0,001, 0,002 ...), даст результат CEILING в неточном совпадении (например, 0,001 становится целым 1, 0,01 становится 1, или даже 0,000001 становится 1). ejbytes 7 лет назад 0
Большое спасибо, Ади. В итоге я написал длинный макрос, чтобы делать то, что хотел, но я буду помнить об этом в следующий раз. PonNab 7 лет назад 0
@PonNab, вы можете пометить этот ответ как ответ, нажав на отметку рядом с кнопкой голосования вверх Vylix 7 лет назад 0
@ejbytes Это "потолок"? Разве это не зависит от того, как отсортирована таблица поиска? Попробовав `LOOKUP` для` 0.1` в `0`,` 0.5` и `1`, он возвращает` 0` вместо `0.5` или` 1` Vylix 7 лет назад 0
@AdiNugroho Ах, понятно. Я предполагаю, что я принимал некоторые на пару тестов. Я предполагаю, что алгоритм не так ясен, как я предполагал. Забавно, что после того, как я отформатировал ячейки, попробовал несколько вещей и вернулся к ним позже ... он работал без какого-либо специального ТЕКСТА (...) или чего-либо ... как бы сказать, что мы все были неправы, и это работает просто. Но это не так. Excel имеет "странную" функциональность. Даже переформатирование ячеек не занимает некоторое время, если каждая ячейка не обновляется. ejbytes 7 лет назад 0
`TEXT` - это то же самое, что и форматирование ячейки, как` TEXT`. Вы можете отформатировать таблицу как TEXT, но вы не можете использовать таблицу для расчета. Я специально не включил это и оставил исходную таблицу поиска такой, какой она есть, потому что решение должно лежать в формуле, а не изменять таблицу. Если кто-то изменит таблицу, поиск будет прерван. Не с моим решением. Vylix 7 лет назад 0
1
Aganju

HLOOKUP (а также VLOOKUP) имеет четвертый (необязательный) параметр, который определяет, будет ли он искать точное совпадение или последнее значение, не превышающее искомое значение; по умолчанию последний.

В основном это работает как задумано . Прочитайте справку и установите четвертый параметр, чтобы достичь желаемого результата.

Размещение точного соответствия в качестве четвертого параметра давало значения ошибок для всех формул (например, ".11 -.1") ... это работало только тогда, когда значение было введено напрямую PonNab 7 лет назад 0

Похожие вопросы