Ошибка ref в excel vlookup
ВПР просто не может выдавать ошибки из-за несоответствия данных; он вернет ошибки.
Давайте подробно обсудим каждую ошибку на примере —
У меня есть простая таблица отчета о продажах в таблице 1. В таблице 2 я применил формулу VLOOKUP и попытался извлечь значения из таблицы 1.
- Шаг 1: Примените формулу LEN excel и найдите, сколько символов содержится в ячейках A4 и E4.
В ячейке C4 я применил функцию LEN, чтобы проверить, сколько символов находится в ячейке A4, и аналогичным образом я применил функцию LEN в ячейке D4, чтобы узнать, сколько символов в ячейке E4.
В ячейке A4 у меня 11 символов, но в ячейке E4 у меня 12 символов. Один лишний символ находится в ячейке E4 при сравнении с ячейкой A4.
С самого начала оба выглядят похожими друг на друга. Однако там есть один лишний символ, и это должен быть конечный пробел.
Мы можем просто отредактировать ячейку E4 и удалить пробел. Если мы удалим это лишнее пространство, мы получим результат.
Но это неправильный способ решения проблемы.
- Шаг 2: Мы можем удалить конечные пробелы, используя функцию TRIM в excel. Применяя VLOOKUP вместе с функцией TRIM, мы можем автоматически удалять пробелы.
Функция TRIM удаляет лишнее нежелательное пространство.
Эта ошибка возникает из-за того, что в функции отсутствует один из параметров. Взгляните, например, на приведенную ниже таблицу.
ВПР начинается со значения ПРОСМОТР, затем диапазона таблицы, за которым следует порядковый номер столбца и тип соответствия. Если вы посмотрите на изображение выше, параметры формулы не в идеальном порядке. На месте таблицы значений поиска находится диапазон, в месте диапазона таблицы — номер индекса столбца и так далее.
Нам может просто нужно правильно указать формулу, чтобы устранить эту ошибку.
Значение поиска идеальное; диапазон таблицы идеален, но порядковый номер столбца здесь не идеален. Я выбрал диапазон таблиц от A3 до B8, то есть только диапазон таблиц от A3 до B8, то есть только два столбца, которые я выбрал.
Решение: Решение простое; нам просто нужно проверить написание формулы.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку "Отменить" на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
Пример функции ВПР с неправильными ссылками на диапазоны
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ
Откройте книгу, на которые ссылается ссылка. Эта же ошибка будет возникнуть при ссылке на закрытую книгу с динамической функцией массива.
Проблемы с OLE
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы dDE
Примечание. Динамические Exchange (DDE)— это протокол, который позволяет обмениваться данными между Windows программами Майкрософт.
Проблемы с макросами
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel for iPad Excel for iPhone Excel for Android tablets Excel 2010 Excel 2007 Excel for Mac 2011 Excel for Android phones Excel for Windows Phone 10 Excel Mobile Excel Starter 2010 More. Less
The following example uses the formula =SUM(B2,C2,D2) in column E.
If you accidentally deleted rows or columns, you can immediately click the Undo button on the Quick Access Toolbar (or press CTRL+Z) to restore them.
Adjust the formula so that it uses a range reference instead of individual cells, like =SUM(B2:D2). Now you could delete any column within the sum range and Excel will automatically adjust the formula. You could also use =SUM(B2:B5) for a sum of rows.
Example - VLOOKUP with incorrect range references
Adjust the range to be larger, or reduce the column lookup value to match the reference range. =VLOOKUP(A8,A2:E5,5,FALSE) would be a valid reference range, as would =VLOOKUP(A8,A2:D5,4,FALSE).
Example - INDEX with incorrect row or column reference
Adjust the row or column references so they're inside the INDEX lookup range. =INDEX(B2:E5,4,4) would return a valid result.
Example – Referencing a closed workbook with INDIRECT
Open the referenced workbook. You'll encounter the same error if you reference a closed workbook with a dynamic array function.
OLE Issues
Note: OLE is a technology that you can use to share information between programs.
DDE Issues
Note: Dynamic Data Exchange (DDE)is an established protocol for exchanging data between Microsoft Windows-based programs.
Macro Issues
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
В какой-то момент мы все посмотрели на функцию VLOOKUP и подумали, что ищем другой язык. Если вы ранее не работали с функцией VLOOKUP, это может быть немного утомительно и часто приводит к разочарованию при возникновении проблем.
Понимание аргументов VLOOKUP
Прежде чем обсуждать различные решения для работы с VLOOKUP, важно понять, что делает VLOOKUP и как он работает. Давайте взглянем и разберем функцию.
Lookup_Value
Это значение, на которое вы хотите посмотреть. Это может быть значение ячейки или статическое значение, указанное в формуле.
= ВПР («Собака»,DogTable, 2,0)
= ВПР (A2,DogTable, 2,0) где A2 = собака
таблица_массив
Это диапазон, в котором вы хотите найти свой Lookup_Value. Примечание: искомое значение должно быть в крайнем левом столбце диапазона.
Предполагая, что таблица выше называется «DogTable». Эта именованная таблица представляет диапазон A3: C7.
Итак, наша формула может быть:
= ВПР (С1, DogTable, 2,0)
Обе формулы работают, однако, используя именованные диапазоны и диапазоны таблиц для вашего Table_Array, являются более динамичными и универсальными. Мы рекомендуем это, а не абсолютные диапазоны.
Col_Index_Num
Индекс столбца — это столбец, в который вы хотите получить данные, если ваше значение найдено в массиве Table_Array.
Если вы хотите найти значение «Dog» в DogTable и вернуть его размер, вы будете указывать номер столбца, начиная с первого столбца диапазона.
Таким образом, если самый дальний левый столбец — Animal, а следующий столбец — Size, ваше значение будет равно 2. Это 2-й столбец, из которого можно найти Lookup_Value. Если бы в таблице выше были Animal, Cost, а затем Size, то значение было бы 3.
диапазон_просмотра
Значение по умолчанию для range_lookup всегда будет 1, если не указано. Это позволит найти относительное соответствие и, как правило, не очень точно для большинства целей. Рекомендуется искать точное совпадение, используя 0 или FALSE.
Это будет происходить время от времени, и может быть сложно отследить, если у вас есть сложные формулы, связанные с VLOOKUP. Давайте посмотрим на приведенный ниже пример и посмотрим, в чем проблема и как ее решить.
В приведенном ниже примере у нас есть другой набор данных, где мы хотим найти стоимость животного. Итак, мы собираемся использовать VLOOKUP для ссылки на нашу таблицу базы данных «DogTable» и получения информации о ценах. Как видно ниже, мы используем = VLOOKUP (S10, DogTable, 3,0). S10 содержит значение Bird. S9 содержит значение Dog.
Let’s first look at the working example of the VLOOKUP function.
Figure 1. The working example of the VLOOKUP function
As we can see in the picture, we have VLOOKUP formula in the cell F3. The purpose is to get the Sales value for Product B from the table in the range B2:C7. Our lookup columns is “Product” column. As the Sales is $500, we got that value in the cell F3.
Now we will see what happens if we delete the table in which we’re looking up (B2:C7).
We will again first look at the working example of the VLOOKUP function:
Figure 3. The working example of the VLOOKUP function
Similarly to the first example, we have VLOOKUP formula in the cell G3. The purpose is to get the Sales value for Product B from the table in the range B2:D7. As the Sales is $500, we got that value in the cell G3.
Now we will see what happens if we pass table B2:C7 by mistake, instead of B2:D7. If we do that, we omit the column which value we want to retrieve (D).
As we can see from the previous explanation, this is the second most possible cause of the error. We should check if we have selected the whole table in the VLOOKUP function.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.
Читайте также: