Huh… I honestly don’t see anything “wrong” with it. I’ve literally copy-n-pasted the VLOOKUP argument so I know it’s identical, except for the column from which it’s pulling that cell’s requested data.
Here’s what I’ve got:
=IF(H6=0,"",(VLOOKUP(H6,$'Data Lookup'.A:$'Data Lookup'.M,14,0)))
Everything stating M.13.0 and less (down to M.2.0, since M.1.0 is the initial search criterion) works perfectly. Just 14 and up do not work and return the 502 error.
This spreadsheet has six sheets to it.
The first sheet is a data entry sheet, where I type in the store number (along with first day of that week and team members I’m working with), and it’s broken out to give me five days, and then these are grouped into four groups (i.e. four weeks).
The second, third, fourth, and fifth sheets are weekly result tables which I can print.
The sixth sheet is the Data Lookup sheet, and it’s a massive table of store numbers (first field) and then all the other pertinent data for each of those stores (district, phone number, address, and so on).
Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.
В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.
В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.
- Исправляем ошибку #Н/Д
- Исправляем ошибку #ЗНАЧ! в формулах с ВПР
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (проблемы, ограничения и решения)
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Содержание
- Исправляем ошибку #Н/Д функции ВПР в Excel
- 1. Искомое значение написано с опечаткой
- 2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
- 3. Ошибка #Н/Д при поиске точного совпадения с ВПР
- 4. Столбец поиска не является крайним левым
- 5. Числа форматированы как текст
- 6. В начале или в конце стоит пробел
- Ошибка #ЗНАЧ! в формулах с ВПР
- 1. Искомое значение длиннее 255 символов
- 2. Не указан полный путь к рабочей книге для поиска
- 3. Аргумент Номер_столбца меньше 1
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (ограничения, оговорки и решения)
- 1. ВПР не чувствительна к регистру
- 2. ВПР возвращает первое найденное значение
- 3. В таблицу был добавлен или удалён столбец
- 4. Ссылки на ячейки исказились при копировании формулы
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
- ВПР: работа с функцией ЕСЛИОШИБКА
- ВПР: работа с функцией ЕОШИБКА
Исправляем ошибку #Н/Д функции ВПР в Excel
В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.
1. Искомое значение написано с опечаткой
Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.
2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:
- Искомое значение меньше наименьшего значения в просматриваемом массиве.
- Столбец поиска не упорядочен по возрастанию.
3. Ошибка #Н/Д при поиске точного совпадения с ВПР
Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.
4. Столбец поиска не является крайним левым
Как Вы, вероятно, знаете, одно из самых значительных ограничений ВПР это то, что она не может смотреть влево, следовательно, столбец поиска в Вашей таблице должен быть крайним левым. На практике мы часто забываем об этом, что приводит к не работающей формуле и появлению ошибки #Н/Д.
Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.
5. Числа форматированы как текст
Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.
Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.
Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:
Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.
Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.
Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.
6. В начале или в конце стоит пробел
Это наименее очевидная причина ошибки #Н/Д в работе функции ВПР, поскольку зрительно трудно увидеть эти лишние пробелы, особенно при работе с большими таблицами, когда большая часть данных находится за пределами экрана.
Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)
Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)
Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.
Ошибка #ЗНАЧ! в формулах с ВПР
В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.
1. Искомое значение длиннее 255 символов
Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.
Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Не указан полный путь к рабочей книге для поиска
Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.
Вот полная структура функции ВПР для поиска в другой книге:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Настоящая формула может выглядеть так:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.
Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).
Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.
3. Аргумент Номер_столбца меньше 1
Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.
Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.
Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).
Ошибка #ИМЯ? в ВПР
Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.
Решение очевидно – проверьте правописание!
ВПР не работает (ограничения, оговорки и решения)
Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.
1. ВПР не чувствительна к регистру
Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.
Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.
2. ВПР возвращает первое найденное значение
Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).
3. В таблицу был добавлен или удалён столбец
К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.
Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.
4. Ссылки на ячейки исказились при копировании формулы
Этот заголовок исчерпывающе объясняет суть проблемы, правда?
Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.
ВПР: работа с функцией ЕСЛИОШИБКА
Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.
Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Если Вы хотите показать собственное сообщение вместо стандартного сообщения об ошибке функции ВПР, впишите его в кавычках, например, так:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
ВПР: работа с функцией ЕОШИБКА
Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.
Оцените качество статьи. Нам важно ваше мнение:
Такая проблема:есть основной файл, в который собирается информация из стороннего файла,и в этом основном файле,первоначально при открытии, в некоторых ячейках отображается ошибка 502. Допустим,если пробовать внести изменения в какую-нибудь ячейку, то файл как бы обновляется и всё отображается нормально.Вроде мелочь,можно и так работать, но суть в том, что информации в основном файле много и, когда надо быстро скопировать готовый отчет,забываешь,что там изначально отображается эта ошибка 502 и копируешь с ошибкой и получаешь недовольство начальства.
Примечание: когда открыт сторонний файл откуда собирается информация и после этого открываешь основной файл информация сразу отображается корректно, а при закрытом первоначально идёт Ошибка 502 как описано выше.
Помогите пофиксить данную проблему,чтобы информация сразу отображалась корректно при закрытом стороннем файле.
P.s. Справа у меня вся информация собирается, а слева сцепляется в готовый отчет.
P.s.s. Извиняюсь за такие скрины, фоткал на работе, а там нет возможности перекинуть файл.
 |
Номер ошибки: | Ошибка 502 |
| Название ошибки: | Excel Error 502 | |
| Описание ошибки: | Ошибка 502: Возникла ошибка в приложении Microsoft Excel. Приложение будет закрыто. Приносим извинения за неудобства. | |
| Разработчик: | Microsoft Corporation | |
| Программное обеспечение: | Microsoft Excel | |
| Относится к: | Windows XP, Vista, 7, 8, 10, 11 |
Анализ «Excel Error 502»
Как правило, практикующие ПК и сотрудники службы поддержки знают «Excel Error 502» как форму «ошибки во время выполнения». Разработчики Microsoft Corporation обычно оценивают Microsoft Excel с помощью серии модулей для удаления ошибок в соответствии с отраслевыми стандартами. Как и во всем в жизни, иногда такие проблемы, как ошибка 502, упускаются из виду.
Ошибка 502 может столкнуться с пользователями Microsoft Excel, если они регулярно используют программу, также рассматривается как «Excel Error 502». Когда это происходит, конечные пользователи программного обеспечения могут сообщить Microsoft Corporation о существовании ошибки 502 ошибок. Затем они исправляют дефектные области кода и сделают обновление доступным для загрузки. Поэтому, когда вы сталкиваетесь с запросом на обновление Microsoft Excel, это обычно связано с тем, что это решение для исправления ошибки 502 и других ошибок.
В большинстве случаев вы увидите «Excel Error 502» во время загрузки Microsoft Excel. Мы рассмотрим основные причины ошибки 502 ошибок:
Ошибка 502 Crash — это типичная ошибка «Excel Error 502», которая приводит к полному завершению работы программы. Если Microsoft Excel не может обработать данный ввод, или он не может получить требуемый вывод, это обычно происходит.
Утечка памяти «Excel Error 502» — этот тип утечки памяти приводит к тому, что Microsoft Excel продолжает использовать растущие объемы памяти, снижая общую производительность системы. Возможные причины включают сбой Microsoft Corporation для девыделения памяти в программе или когда плохой код выполняет «бесконечный цикл».
Ошибка 502 Logic Error — Логическая ошибка возникает, когда ПК производит неправильный вывод, даже когда пользователь вводит правильный вход. Это связано с ошибками в исходном коде Microsoft Corporation, обрабатывающих ввод неправильно.
Как правило, ошибки Excel Error 502 вызваны повреждением или отсутствием файла связанного Microsoft Excel, а иногда — заражением вредоносным ПО. Основной способ решить эти проблемы вручную — заменить файл Microsoft Corporation новой копией. Помимо прочего, в качестве общей меры по профилактике и очистке мы рекомендуем использовать очиститель реестра для очистки любых недопустимых записей файлов, расширений файлов Microsoft Corporation или разделов реестра, что позволит предотвратить появление связанных с ними сообщений об ошибках.
Распространенные проблемы Excel Error 502
Excel Error 502 Проблемы, связанные с Microsoft Excel:
- «Ошибка Excel Error 502. «
- «Excel Error 502 не является программой Win32. «
- «Извините за неудобства — Excel Error 502 имеет проблему. «
- «Excel Error 502 не может быть найден. «
- «Отсутствует файл Excel Error 502.»
- «Ошибка запуска программы: Excel Error 502.»
- «Excel Error 502 не выполняется. «
- «Excel Error 502 выйти. «
- «Ошибка пути программного обеспечения: Excel Error 502. «
Эти сообщения об ошибках Microsoft Corporation могут появляться во время установки программы, в то время как программа, связанная с Excel Error 502 (например, Microsoft Excel) работает, во время запуска или завершения работы Windows, или даже во время установки операционной системы Windows. Запись ошибок Excel Error 502 внутри Microsoft Excel имеет решающее значение для обнаружения неисправностей электронной Windows и ретрансляции обратно в Microsoft Corporation для параметров ремонта.
Источник ошибок Excel Error 502
Эти проблемы Excel Error 502 создаются отсутствующими или поврежденными файлами Excel Error 502, недопустимыми записями реестра Microsoft Excel или вредоносным программным обеспечением.
Особенно ошибки Excel Error 502 проистекают из:
- Недопустимая (поврежденная) запись реестра Excel Error 502.
- Зазаражение вредоносными программами повредил файл Excel Error 502.
- Excel Error 502 ошибочно удален или злонамеренно программным обеспечением, не связанным с приложением Microsoft Excel.
- Другая программа находится в конфликте с Microsoft Excel и его общими файлами ссылок.
- Microsoft Excel (Excel Error 502) поврежден во время загрузки или установки.
Продукт Solvusoft
Загрузка
WinThruster 2022 — Проверьте свой компьютер на наличие ошибок.
Совместима с Windows 2000, XP, Vista, 7, 8, 10 и 11
Установить необязательные продукты — WinThruster (Solvusoft) | Лицензия | Политика защиты личных сведений | Условия | Удаление
В этой статье представлена ошибка с номером Ошибка 502, известная как Ошибка Excel 502, описанная как Ошибка 502: Возникла ошибка в приложении Microsoft Excel. Приложение будет закрыто. Приносим свои извинения за неудобства.
О программе Runtime Ошибка 502
Время выполнения Ошибка 502 происходит, когда Microsoft Excel дает сбой или падает во время запуска, отсюда и название. Это не обязательно означает, что код был каким-то образом поврежден, просто он не сработал во время выполнения. Такая ошибка появляется на экране в виде раздражающего уведомления, если ее не устранить. Вот симптомы, причины и способы устранения проблемы.
Определения (Бета)
Здесь мы приводим некоторые определения слов, содержащихся в вашей ошибке, в попытке помочь вам понять вашу проблему. Эта работа продолжается, поэтому иногда мы можем неправильно определить слово, так что не стесняйтесь пропустить этот раздел!
- Excel — только для вопросов по программированию с объектами или файлами Excel или по разработке сложных формул.
Симптомы Ошибка 502 — Ошибка Excel 502
Ошибки времени выполнения происходят без предупреждения. Сообщение об ошибке может появиться на экране при любом запуске %программы%. Фактически, сообщение об ошибке или другое диалоговое окно может появляться снова и снова, если не принять меры на ранней стадии.
Возможны случаи удаления файлов или появления новых файлов. Хотя этот симптом в основном связан с заражением вирусом, его можно отнести к симптомам ошибки времени выполнения, поскольку заражение вирусом является одной из причин ошибки времени выполнения. Пользователь также может столкнуться с внезапным падением скорости интернет-соединения, но, опять же, это не всегда так.
(Только для примера)
Причины Ошибка Excel 502 — Ошибка 502
При разработке программного обеспечения программисты составляют код, предвидя возникновение ошибок. Однако идеальных проектов не бывает, поскольку ошибки можно ожидать даже при самом лучшем дизайне программы. Глюки могут произойти во время выполнения программы, если определенная ошибка не была обнаружена и устранена во время проектирования и тестирования.
Ошибки во время выполнения обычно вызваны несовместимостью программ, запущенных в одно и то же время. Они также могут возникать из-за проблем с памятью, плохого графического драйвера или заражения вирусом. Каким бы ни был случай, проблему необходимо решить немедленно, чтобы избежать дальнейших проблем. Ниже приведены способы устранения ошибки.
Методы исправления
Ошибки времени выполнения могут быть раздражающими и постоянными, но это не совсем безнадежно, существует возможность ремонта. Вот способы сделать это.
Если метод ремонта вам подошел, пожалуйста, нажмите кнопку upvote слева от ответа, это позволит другим пользователям узнать, какой метод ремонта на данный момент работает лучше всего.
Обратите внимание: ни ErrorVault.com, ни его авторы не несут ответственности за результаты действий, предпринятых при использовании любого из методов ремонта, перечисленных на этой странице — вы выполняете эти шаги на свой страх и риск.
Метод 1 — Закройте конфликтующие программы
Когда вы получаете ошибку во время выполнения, имейте в виду, что это происходит из-за программ, которые конфликтуют друг с другом. Первое, что вы можете сделать, чтобы решить проблему, — это остановить эти конфликтующие программы.
- Откройте диспетчер задач, одновременно нажав Ctrl-Alt-Del. Это позволит вам увидеть список запущенных в данный момент программ.
- Перейдите на вкладку «Процессы» и остановите программы одну за другой, выделив каждую программу и нажав кнопку «Завершить процесс».
- Вам нужно будет следить за тем, будет ли сообщение об ошибке появляться каждый раз при остановке процесса.
- Как только вы определите, какая программа вызывает ошибку, вы можете перейти к следующему этапу устранения неполадок, переустановив приложение.
Метод 2 — Обновите / переустановите конфликтующие программы
Использование панели управления
- В Windows 7 нажмите кнопку «Пуск», затем нажмите «Панель управления», затем «Удалить программу».
- В Windows 8 нажмите кнопку «Пуск», затем прокрутите вниз и нажмите «Дополнительные настройки», затем нажмите «Панель управления»> «Удалить программу».
- Для Windows 10 просто введите «Панель управления» в поле поиска и щелкните результат, затем нажмите «Удалить программу».
- В разделе «Программы и компоненты» щелкните проблемную программу и нажмите «Обновить» или «Удалить».
- Если вы выбрали обновление, вам просто нужно будет следовать подсказке, чтобы завершить процесс, однако, если вы выбрали «Удалить», вы будете следовать подсказке, чтобы удалить, а затем повторно загрузить или использовать установочный диск приложения для переустановки. программа.
Использование других методов
- В Windows 7 список всех установленных программ можно найти, нажав кнопку «Пуск» и наведя указатель мыши на список, отображаемый на вкладке. Вы можете увидеть в этом списке утилиту для удаления программы. Вы можете продолжить и удалить с помощью утилит, доступных на этой вкладке.
- В Windows 10 вы можете нажать «Пуск», затем «Настройка», а затем — «Приложения».
- Прокрутите вниз, чтобы увидеть список приложений и функций, установленных на вашем компьютере.
- Щелкните программу, которая вызывает ошибку времени выполнения, затем вы можете удалить ее или щелкнуть Дополнительные параметры, чтобы сбросить приложение.
Метод 3 — Обновите программу защиты от вирусов или загрузите и установите последнюю версию Центра обновления Windows.
Заражение вирусом, вызывающее ошибку выполнения на вашем компьютере, необходимо немедленно предотвратить, поместить в карантин или удалить. Убедитесь, что вы обновили свою антивирусную программу и выполнили тщательное сканирование компьютера или запустите Центр обновления Windows, чтобы получить последние определения вирусов и исправить их.
Метод 4 — Переустановите библиотеки времени выполнения
Вы можете получить сообщение об ошибке из-за обновления, такого как пакет MS Visual C ++, который может быть установлен неправильно или полностью. Что вы можете сделать, так это удалить текущий пакет и установить новую копию.
- Удалите пакет, выбрав «Программы и компоненты», найдите и выделите распространяемый пакет Microsoft Visual C ++.
- Нажмите «Удалить» в верхней части списка и, когда это будет сделано, перезагрузите компьютер.
- Загрузите последний распространяемый пакет от Microsoft и установите его.
Метод 5 — Запустить очистку диска
Вы также можете столкнуться с ошибкой выполнения из-за очень нехватки свободного места на вашем компьютере.
- Вам следует подумать о резервном копировании файлов и освобождении места на жестком диске.
- Вы также можете очистить кеш и перезагрузить компьютер.
- Вы также можете запустить очистку диска, открыть окно проводника и щелкнуть правой кнопкой мыши по основному каталогу (обычно это C
- Щелкните «Свойства», а затем — «Очистка диска».
Метод 6 — Переустановите графический драйвер
Если ошибка связана с плохим графическим драйвером, вы можете сделать следующее:
- Откройте диспетчер устройств и найдите драйвер видеокарты.
- Щелкните правой кнопкой мыши драйвер видеокарты, затем нажмите «Удалить», затем перезагрузите компьютер.
Метод 7 — Ошибка выполнения, связанная с IE
Если полученная ошибка связана с Internet Explorer, вы можете сделать следующее:
- Сбросьте настройки браузера.
- В Windows 7 вы можете нажать «Пуск», перейти в «Панель управления» и нажать «Свойства обозревателя» слева. Затем вы можете перейти на вкладку «Дополнительно» и нажать кнопку «Сброс».
- Для Windows 8 и 10 вы можете нажать «Поиск» и ввести «Свойства обозревателя», затем перейти на вкладку «Дополнительно» и нажать «Сброс».
- Отключить отладку скриптов и уведомления об ошибках.
- В том же окне «Свойства обозревателя» можно перейти на вкладку «Дополнительно» и найти пункт «Отключить отладку сценария».
- Установите флажок в переключателе.
- Одновременно снимите флажок «Отображать уведомление о каждой ошибке сценария», затем нажмите «Применить» и «ОК», затем перезагрузите компьютер.
Если эти быстрые исправления не работают, вы всегда можете сделать резервную копию файлов и запустить восстановление на вашем компьютере. Однако вы можете сделать это позже, когда перечисленные здесь решения не сработают.
Другие языки:
How to fix Error 502 (Excel Error 502) — Error 502: Microsoft Excel has encountered a problem and needs to close. We are sorry for the inconvenience.
Wie beheben Fehler 502 (Excel-Fehler 502) — Fehler 502: Microsoft Excel hat ein Problem festgestellt und muss geschlossen werden. Wir entschuldigen uns für die Unannehmlichkeiten.
Come fissare Errore 502 (Errore di Excel 502) — Errore 502: Microsoft Excel ha riscontrato un problema e deve essere chiuso. Ci scusiamo per l’inconveniente.
Hoe maak je Fout 502 (Excel-fout 502) — Fout 502: Microsoft Excel heeft een probleem ondervonden en moet worden afgesloten. Excuses voor het ongemak.
Comment réparer Erreur 502 (Erreur Excel 502) — Erreur 502 : Microsoft Excel a rencontré un problème et doit se fermer. Nous sommes désolés du dérangement.
어떻게 고치는 지 오류 502 (엑셀 오류 502) — 오류 502: Microsoft Excel에 문제가 발생해 닫아야 합니다. 불편을 드려 죄송합니다.
Como corrigir o Erro 502 (Erro Excel 502) — Erro 502: O Microsoft Excel encontrou um problema e precisa fechar. Lamentamos o inconveniente.
Hur man åtgärdar Fel 502 (Excel-fel 502) — Fel 502: Microsoft Excel har stött på ett problem och måste avslutas. Vi är ledsna för besväret.
Jak naprawić Błąd 502 (Błąd programu Excel 502) — Błąd 502: Microsoft Excel napotkał problem i musi zostać zamknięty. Przepraszamy za niedogodności.
Cómo arreglar Error 502 (Error de Excel 502) — Error 502: Microsoft Excel ha detectado un problema y debe cerrarse. Lamentamos las molestias.
Об авторе: Фил Харт является участником сообщества Microsoft с 2010 года. С текущим количеством баллов более 100 000 он внес более 3000 ответов на форумах Microsoft Support и создал почти 200 новых справочных статей в Technet Wiki.
Следуйте за нами: 
Рекомендуемый инструмент для ремонта:
Этот инструмент восстановления может устранить такие распространенные проблемы компьютера, как синие экраны, сбои и замораживание, отсутствующие DLL-файлы, а также устранить повреждения от вредоносных программ/вирусов и многое другое путем замены поврежденных и отсутствующих системных файлов.
ШАГ 1:
Нажмите здесь, чтобы скачать и установите средство восстановления Windows.
ШАГ 2:
Нажмите на Start Scan и позвольте ему проанализировать ваше устройство.
ШАГ 3:
Нажмите на Repair All, чтобы устранить все обнаруженные проблемы.
СКАЧАТЬ СЕЙЧАС
Совместимость
Требования
1 Ghz CPU, 512 MB RAM, 40 GB HDD
Эта загрузка предлагает неограниченное бесплатное сканирование ПК с Windows. Полное восстановление системы начинается от $19,95.
ID статьи: ACX04267RU
Применяется к: Windows 10, Windows 8.1, Windows 7, Windows Vista, Windows XP, Windows 2000
Skip to content
Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).
Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»
В первую очередь, сообщение об ошибке вы увидите, если данные, которое вы ищете, действительно отсутствуют в указанном диапазоне поиска. Здесь мы бессильны.
Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.
Итак, почему ВПР не находит значение, хотя оно есть?
- Неточность при вводе данных.
- Опечатка при вводе функции.
- Неверные ссылки.
- Неверно указан параметр «интервальный просмотр».
- Столбец поиска не является первым слева столбцом диапазона поиска.
- Несовпадение форматов данных.
- Преобразуем число в текст.
- Преобразуем текст в число.
- Если числовой и текстовый форматы беспорядочно перемешаны?
- Лишние пробелы и непечатаемые знаки.
- Неправильно указан номер столбца.
- Неверная ссылка на данные из другой таблицы
- После изменения таблицы функция ВПР перестала работать
- Как убрать сообщение #Н/Д в ВПР?
Неточность при вводе данных.
В этом случае ничего найти не получится, и ВПР возвратит ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе критерия поиска.
Это вполне может случиться, особенно если вы записываете его прямо в формулу.
Опечатка при вводе функции.
Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.
Проверьте синтаксис, и всё будет в порядке.
Неверные ссылки.
Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.
Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.
Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.
Неверно указан параметр «интервальный просмотр».
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.
Об этой проблеме мы подробно говорили ранее.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что ВПР ищет совпадающие данные только в первом слева столбце диапазона, который вы указали.
Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.
Это важное ограничение нельзя забывать.
Несовпадение форматов данных.
Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.
Преобразуем число в текст.
Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.
Как решить эту проблему со случайным несоответствием в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любой тип данных в текстовый вид.
Используем формулу
=ВПР(ТЕКСТ(D6;»#»);$A$3:$B$21;2;ЛОЖЬ)
Как видно, обычную ссылку на D6 мы заменяем конструкцией
TEXT(D6,»#»)
Первый аргумент – это адрес, а второй аргумент означает формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).
Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.
=ВПР(D6&»»;$A$3:$B$21;2;ЛОЖЬ)
Запомните простое правило:
Если какую-либо текстовую операцию мы пытаемся провести над ячейкой, в которой записано число, то Excel обязательно попытается превратить это число в текст.
Если несовпадение форматов встречается редко, то можно просто исправить формат записи данных. Измените формат ячейки на текстовый и пересохраните ее содержание (F2 и затем ENTER). Содержимое будет преобразовано в текст.
Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.
Преобразуем текст в число.
А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.
Добавьте перед проблемным значением двойной минус. Это означает — дважды умножить на минус 1. А еще со школы мы помним, что минус на минус дает плюс. В результате ничего не изменится. Но есть важный момент.
Если какую-либо математическую операцию мы пытаемся провести над ячейкой, в которой записан текст, то Excel обязательно попытается преобразовать его в число, чтобы выполнить вычисления.
Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:
=ВПР(—A2;$D$2:$E$8;2;0)
Если числовой и текстовый форматы беспорядочно перемешаны?
А теперь — самый сложный случай. И здесь тоже есть простое решение.
Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.
=ЕСЛИОШИБКА(ВПР(—O2;$R$2:$S$8;2;0);ВПР(O2&»»;$R$2:$S$8;2;0))
В результате получаем немного громоздкий, но зато вполне универсальный вариант, который позволит избежать ошибок в формуле ВПР из-за несовпадения форматов значений.
Лишние пробелы и непечатаемые знаки.
Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
В нашем примере на картинке чуть выше формула ВПР возвратила ошибку, так как в D8 при вводе данных случайно были добавлены два пробела после слова. И, поскольку сравниваются символьные значения, поиск, естественно, завершился неудачно.
Такая неточность может встречаться довольно часто, и обнаружить её визуально практически невозможно. К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.
Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие неточности ввода вы не сможете.
В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо
=ВПР(D8;$A$3:$B$21;2;ЛОЖЬ)
вводим
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D8));$A$3:$B$21;2;ЛОЖЬ)
или
=VLOOKUP(TRIM(CLEAN(D8)),$A$3:$B$21,2,FALSE)
Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.
Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных неточностей ввода данных.
Неправильно указан номер столбца.
Иногда функция ВПР не срабатывает, потому что неправильно указан номер столбца, значение из которого вы хотите получить (третий аргумент).
Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!
Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.
И в любом случае – это число должно быть больше 1. Вряд ли кто-то из вас будет руками вводить 0 или -1, но если вы получаете его при помощи каких-то вычислений, вложенных в формулу, то здесь-то и может быть проблема.
Неверная ссылка на данные из другой таблицы
Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Excel.
Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.
Это будет означать, что вы ссылаетесь на неверный диапазон, который Excel найти не может.
После изменения таблицы функция ВПР перестала работать
Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или столбцов в вашу таблицу, все работало верно.
Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?
А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.
Кроме того, в третьем аргументе вы указываете порядковый номер столбца, из которого нужно извлечь данные. Но если вы вставили дополнительные колонки, то и количество столбцов, и их порядковые номера изменятся. А у вас в формуле используются «старые» номера.
Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.
Важно! Всегда используйте абсолютные ссылки, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.
Как убрать сообщение #Н/Д в ВПР?
Если функция не находит точного совпадения или искомого объекта просто нет в таблице, то она выдает сообщения, о которых мы говорили выше. С одной стороны, это хорошо, поскольку отрицательный результат- это тоже результат.
Однако, иногда это не очень удобно, т.к. такие сообщения потом порождают новые проблемы в расчете итогов, попадают в распечатку и т.д. Ведь если на эту ячейку ссылаются какие-то другие формулы, то они будут неправильно работать.
Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.
Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.
=ЕСЛИОШИБКА([выражение];[значение_если_ошибка])
Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.
В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.
Вот как нужно будет преобразовать наши формулы, чтобы все выглядело красиво.
=ЕСЛИОШИБКА(ВПР(A4;$D$2:$E$7;2;0);0)
Естественно, вместо нуля можно вписать «не найдено» либо любое другое сообщение, а можно просто поставить пробел. Но в случае, когда на основе этих ячеек затем производятся какие-то вычисления, ноль, возможно, будет предпочтительнее.
Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».
[the_ad_group id=»48″]
Еще полезные примеры:
Общая информация о ВПР (VLOOKUP)
Функция ВПР — это одна из наиболее популярных функций ссылок и массивов. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется VLOOKUP.
Уровень сложности по шкале BRP ADVICE — 3 из 7.
ВПР (VLOOKUP) позволяет найти в таблице с данными значение из нужной строки и столбца. При этом нужную строчку ВПР (VLOOKUP) найдет сам, а вот столбец вам придется указать самостоятельно.
Чтобы разобраться с ВПР (VLOOKUP), сначала надо разобраться с тем, как работают все функции ссылок и массивов.
Как работают функции ссылок и массивов
В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах вы можете ссылаться на ячейку, чтобы получить ее значение или использовать в расчетах. Обычно, в функциях вы указываете ссылки на ячейки вида A1, B17, G34, Z52. Некоторые привыкли работать со ссылками вида R1C1, R17C2, R34C7, R52C26. При этом вы указываете номер строки и букву/номер столбца с начала листа. Указав ячейку, вы даете программе точное указание, что вам нужно значение именно этой ячейки, находящейся на пересечении нужной строки и столбца. То есть выглядит все это примерно так:
Функции ссылок и массивов работают немного по-другому. В функциях ссылок и массивов вы задаете новую таблицу на листе, которая может находиться, где угодно. Назовем эту новую таблицу — внутренняя таблица. Вся нумерация строк и столбцов в этой внутренней таблице начинается заново. От 1 и до последней строки, от 1 и до последнего столбца. При этом совершенно не важно, где начинается внутренняя таблица: в самом начале листа, в его середине или ближе к концу. Всегда первая ячейка этой таблицы образует пересечение первой строки и первого столбца. Вот как это можно схематически изобразить:
К сожалению, ни один табличный редактор не нарисует вам такой подсказки. Все это и вам, и программе придется держать в голове.
Как указать, где находится внутренняя таблица? Для этого вам понадобится указать ее адрес при помощи стандартных ссылок вида A1 или R1C1. На картинке выше адрес внутренней таблицы — это диапазон J17:O25.
Что же происходит в этой внутренней таблице? Каждая ячейка получает новый адрес, который состоит из номера строки и номера столбца. Именно так: сначала номер строки, затем номер столбца.
Что же делают функции ссылок и массивов? Их конечная цель — получить значение по его внутреннему адресу. При этом функции ссылок и массивов могут как найти нужную строку и нужный столбец сами, так и использовать введенные пользователем значения. Для разных задач используются разные функции. Но в конечном счете получается примерно так, Excel, Google Sheets, LibreOffice, OpenOffice определяют, что вам нужна пятая строка и третий столбец и выдают значение из такой ячейки:
Как работает ВПР (VLOOKUP)
Самый простой способ разобраться с ВПР (VLOOKUP) — это рассмотреть его на примерах. Рассмотрим один пример с точным поиском, второй пример — с приблизительным поиском.
Пример 1 — точный поиск
Ссылка на файл-пример приведена в конце описания этого примера.
Допустим, есть таблица по сотрудникам вашей организации и их окладам. В этой таблице указан каждый сотрудник (его ФИО) и его оклад. Первый столбец этой таблицы — ФИО сотрудника, второй — оклад. То есть ваша таблица выглядит так:
ВПР (VLOOKUP) позволит вам найти оклад, указывая ФИО сотрудника. Конечно, когда у вас в таблице 5 строк и задача разовая, глазами вы найдете нужное значение очень быстро. А если у вас сотрудников 200? Или 5000? ВПР (VLOOKUP) поможет упростить вам жизнь.
Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?
Надо написать формулу
=ВПР(«Иванов С.А.»;C3:D9;2;ЛОЖЬ)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(«Иванов С.А.»;C3:D9;2;FALSE) .
Кстати, в некоторых версиях Excel, вместо «;» должна использоваться «,».
После этого программа вернет вам ответ 21 000.
Что значат все аргументы ВПР (VLOOKUP)?
1. Искомое значение. В нашем примере это «Иванов С.А.» — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.
Кстати, «Иванов С.А.» у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.
2. Таблица. В нашем примере это C3:D9. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).
3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — оклад). Посчитать вам его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это ФИО, второй — оклад. Значит ставим цифру 2.
4. Интервальный просмотр. В нашем примере это ЛОЖЬ (FALSE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». То есть ИСТИНА (TRUE) означает, что нам подойдет и Иванов С.А., и Иванов С.И., и Иванова О.П., и может быть кто-то еще (кого из них выберет Excel, смотрите в Примере №2). Сейчас нам нужен именно Иванов С.А., поэтому мы поставили ЛОЖЬ (FALSE).
Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.
Что именно делает ВПР (VLOOKUP) в этом примере?
ВПР (VLOOKUP) просматривает каждую ячейку первого столбца сверху вниз. Он смотрит в первой строке «Газоев И.В.»=»Иванов С.А.»? Видит, что нет. Тогда смотрит вторую: «Ромашкина Б.О.»=Иванов С.А.»? И так далее, пока не доходит до нужной строки. Когда ВПР (VLOOKUP) видит, что «Иванов С.А.»=»Иванов С.А.», он останавливается и запоминает номер строки, в которой это произошло. В нашем примере это 4 строка. Четвертая, потому что подсчет начинается не с начала листа, а с начала внутренней таблицы. И, наконец, ВПР (VLOOKUP) возвращает значение внутренней таблицы, которое находится на пересечении 4 строки (то, что он нашел) и 2 столбца (то, что мы указали ему аргументом). А это значение 21 000. Все, задача решена.
А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.
Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Пример 2 — приблизительный поиск
Ссылка на файл-пример приведена в конце описания этого примера.
Допустим, у вас в компании менеджер по продажам получает премию в зависимости от объема продаж. Если продажи за месяц больше 100, то премия составляет 4% от продаж. Если продажи больше 200, то премия — 5%. Больше 300 — 6%. Больше 400 — 7%. И при любых продажах больше 500 — 8%. При продажах меньше 100, менеджер премию не получает. При помощи ВПР (VLOOKUP) можно быстро узнать, какую премию получит менеджер при его фактических продажах.
Для этого вначале нам потребуется составить таблицу с уровнями плана и ставками премии. Такая таблица будет выглядеть вот так:
Теперь мы можем найти премию Иванова С.А. в зависимости от его фактических результатов.
Основным отличием от предыдущего примера является то, что фактические продажи Иванова могут быть не только ровно 100, 200, 300, 400 или 500. Они могут находиться между указанными нами значениями. Например, фактические продажи Иванова составят 350.
Для того, чтобы найти ставку премии при продажах 350, можно использовать такую формулу:
=ВПР(350;C3:D8;2;ИСТИНА)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(350;C3:D9;2;TRUE) .
Не забывайте, что в некоторых версиях Excel, вместо «;» должна использоваться «,».
После этого программа вернет вам ответ 6%. Все что вам остается сделать, это перемножить продажи 350 и ставку премии 6%. Это и будет премия Иванова С.А. при продажах равных 350.
Что значат все аргументы ВПР (VLOOKUP)?
1. Искомое значение. В нашем примере это 350 — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.
Кстати, на этот раз 350 у нас написано без кавычек, потому что 350 — это число, а в кавычки мы берем только текст.
2. Таблица. В нашем примере это C3:D8. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).
3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — ставка премии). Посчитать его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это «При продажах больше», второй — «ставка премии». Значит ставим цифру 2.
4. Интервальный просмотр. На этот раз нам нужна ИСТИНА (TRUE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». Мы используем ИСТИНА (TRUE), потому что нам нужно найти не только значения 100, 200 и другие, прямо указанные в таблице, но и все, что находится между ними. То есть ИСТИНА (TRUE) означает, что нам подойдет и 300, и 350, и 380 и так далее, и может быть что-то еще (какое из них выберет Excel, смотрите ниже).
Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.
Что именно делает ВПР (VLOOKUP) в этом примере?
Как и в прошлый раз ВПР (VLOOKUP) последовательно просматривает все ячейки первого столбца нашей таблицы. Но на этот раз он не ищет точного соответствия, а выполняет такие проверки:
100, указанное в таблице, меньше 350? Да, — отвечает ВПР (VLOOKUP), — тогда запоминаем строку 1 и смотрим следующее значение.
200 меньше 350? Да, тогда забываем строку 1, запоминаем 2 и смотрим следующее.
300 меньше 350? Да, тогда забываем строку 2, запоминаем 3 и смотрим следующее.
400 меньше 350? Нет! Тогда возвращаемся к строке 3.
И, наконец, ВПР (VLOOKUP) возвращает значение, указанное на пересечении 3 строки (то, что он нашел) и 2 столбца (то, что мы указали в функции).
Примерно вот так, можно описать логику вычислений ВПР (VLOOKUP) при работе с приблизительным поиском. Обратите внимание, что ВПР (VLOOKUP) не просматривает всю таблицу, а смотрит до тех пор пока значения не оказываются больше искомого. Иными словами, ВПР (VLOOKUP) ищет ближайшее наименьшее. Но для того, чтобы все работало правильно, вам нужно отсортировать таблицу по возрастанию значений в первом столбце.
А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.
Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Типичные ошибки
Какие ошибки мы чаще всего встречаем при работе с ВПР (VLOOKUP)?
1. Это ошибки, связанные с неверной работой с 4 аргументом, с интервальным просмотром. Часто путаются в разнице между ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Запомните такой вопрос: «Мы ищем приблизительно такое же значение, верно?», пусть это будет вам подсказкой.
Часто еще случайно не указывают четвертый аргумент вообще. И это может приводить к совершенно разным последствиям.
Первый случай, когда вы работаете с ВПР (VLOOKUP) как будто в нем всего 3 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так:
=ВПР(«Иванов С.А.»;C3:D9;2)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(«Иванов С.А.»;C3:D9;2) .
В этом случае Excel решает, что надо использовать значение 4 аргумента по умолчанию и подставляет в качестве него ИСТИНА (TRUE). Тогда вы можете найти не Иванова С.А., а кого-то чья фамилия будет примерно похожа на его (смотри как работает ВПР (VLOOKUP) с приблизительным поиском).
Второй случай, когда вы ставите третью точку с запятой, но не указываете сам аргумент ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Например, формула выглядит так:
=ВПР(350;C3:D8;2;)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(350;C3:D9;2;) .
В этом случае ВПР (VLOOKUP) решит, что 4 аргумент равен нулю. А ноль ВПР (VLOOKUP) превратит в ЛОЖЬ (FALSE) и будет искать именно 350 в вашей таблице, а не ближайшее меньшее.
Наш совет, чтобы не допускать таких ошибок, всегда указывайте последний, четвертый, аргумент функции ВПР (VLOOKUP). И пишите его словом, не заменяйте его на цифры, так вашу формулу будет легче прочитать и понять, что же она делает.
2. Другая частая ошибка — это попытка применять ВПР (VLOOKUP), чтобы найти второе или более позднее совпадение во внутренней таблице. Например, есть таблица для ведения складского учета, в которой отражаются поступающие материалы и цены на них. Выглядеть она будет примерно так:
При помощи ВПР (VLOOKUP) иногда пытаются вернуть цену в последнем поступлении. Например, сколько стоили гвозди в последней закупке. Но ВПР (VLOOKUP) вернет вам результат только для первого совпадения. Он не покажет вам данные о второй, третьей или более поздней строчке, только первое совпадение. Поэтому использовать ВПР (VLOOKUP) в такой задаче и в задачах складского учета нужно очень аккуратно. Да, в сочетании с еще несколькими функциями и промежуточными вычислениями, вы сможете получить нужный результат, но мы называем такие подходы «танцы с бубном».
3. Иногда неправильно указывают номер столбца (третий аргумент ВПР (VLOOKUP)). Номер столбца не может быть меньше 1 и не может быть больше, чем столбцов во внутренней таблице. Если номер столбца указан неверно, то ВПР (VLOOKUP) возвращает ошибку #ССЫЛКА! (#REF!). Когда вы видите такую ошибку, пересчитайте количество столбцов в таблице, которую вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР (VLOOKUP).
Что происходит, когда ВПР (VLOOKUP) не находит значение?
В случаях, когда Excel, Google Sheets, LibreOffice, OpenOffice не может найти точное совпадение при 4 аргументе ЛОЖЬ (FALSE), ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A). Такую же ошибку ВПР (VLOOKUP) вернет, если вы используете приблизительный поиск и ваша таблица начинается со значения, которое больше искомого.
Как убрать ошибку #Н/Д (#N/A)
Во-первых, проверьте адрес внутренней таблицы. Действительно искомое значение находится в первом столбце внутренней таблицы.
Во-вторых, проверьте правильно ли вы указали тип интервального просмотра: ИСТИНА (TRUE) или ЛОЖЬ (FALSE).
В-третьих, если вы все сделали правильно, но во внутренней таблице нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:
=ЕСЛИОШИБКА(ВПР(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника»)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=IFERROR(VLOOKUP(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника») .
В этом случае Excel, Google Sheets, LibreOffice, OpenOffice вместо #Н/Д (#N/A) будут писать, что такого сотрудника нет. Это поможет сделать ваши расчеты более информативными и надежными.
Чем дополнить и заменить ВПР (VLOOKUP)?
Дополнить ВПР (VLOOKUP) можно функцией ЕСЛИОШИБКА (IFERROR) и функцией ПОИСКПОЗ (MATCH). В особо сложных случаях возможно использование в комбинации с функцией СМЕЩ (OFFSET).
Основные варианты замены функции ВПР (VLOOKUP): функция ПРОСМОТР (LOOKUP), комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), а также в некоторых случаях — функция ГПР (HLOOKUP), функция СУММЕСЛИ (SUMIF).
При использовании функции ВПР (VLOOKUP) большую автоматизацию и надежность вашим файлам может добавить проверка данных и выпадающие списки, а также условное форматирование.
Быстрые ссылки на файлы-примеры:
Пример 1 — применение ВПР с точным поиском
Пример 2 — применение ВПР с приблизительным поиском
Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.
Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.
Желаем вам успешной работы!
Ваш Виктор Рыбцев
и команда Учебного центра BRP ADVICE.
Обновлено: 30.01.2023
Люди часто предпочитают ссылаться на «Libreoffice Error 502» как на «ошибку времени выполнения», также известную как программная ошибка. Разработчики программного обеспечения пытаются обеспечить, чтобы программное обеспечение было свободным от этих сбоев, пока оно не будет публично выпущено. Поскольку разработчики программного обеспечения пытаются предотвратить это, некоторые незначительные ошибки, такие как ошибка 502, возможно, не были найдены на этом этапе.
Почему возникает ошибка времени выполнения 502?
Сбой устройства или LibreOffice обычно может проявляться с «Libreoffice Error 502» в качестве проблемы во время выполнения. Следующие три наиболее значимые причины ошибок выполнения ошибки 502 включают в себя:
Ошибка 502 Crash — она называется «Ошибка 502», когда программа неожиданно завершает работу во время работы (во время выполнения). Это возникает, когда LibreOffice не реагирует на ввод должным образом или не знает, какой вывод требуется взамен.
Утечка памяти «Libreoffice Error 502» — Когда LibreOffice обнаруживает утечку памяти, операционная система постепенно работает медленно, поскольку она истощает системные ресурсы. Потенциальные триггеры могут быть «бесконечным циклом», или когда программа выполняет «цикл» или повторение снова и снова.
Ошибка 502 Logic Error — Логическая ошибка вызывает неправильный вывод, даже если пользователь дал действительные входные данные. Это происходит, когда исходный код LibreOffice вызывает уязвимость при обработке информации.
Такие проблемы Libreoffice Error 502 обычно вызваны повреждением файла, связанного с LibreOffice, или, в некоторых случаях, его случайным или намеренным удалением. Как правило, любую проблему, связанную с файлом LibreOffice, можно решить посредством замены файла на новую копию. Мы также рекомендуем выполнить сканирование реестра, чтобы очистить все недействительные ссылки на Libreoffice Error 502, которые могут являться причиной ошибки.
Типичные ошибки Libreoffice Error 502
Libreoffice Error 502 Проблемы, связанные с LibreOffice:
- «Ошибка в приложении: Libreoffice Error 502»
- «Libreoffice Error 502 не является программой Win32. «
- «Извините за неудобства — Libreoffice Error 502 имеет проблему. «
- «Не удается найти Libreoffice Error 502»
- «Libreoffice Error 502 не может быть найден. «
- «Ошибка запуска программы: Libreoffice Error 502.»
- «Не удается запустить Libreoffice Error 502. «
- «Отказ Libreoffice Error 502.»
- «Неверный путь к приложению: Libreoffice Error 502.»
Libreoffice Error 502 Истоки проблем
Проблемы LibreOffice и Libreoffice Error 502 возникают из отсутствующих или поврежденных файлов, недействительных записей реестра Windows и вредоносных инфекций.
Ширины ячейки не хватает для отображения содержимого.
Символ в формуле недействителен.
Недопустимый аргумент функции. Например, отрицательное число в функции SQRT() (в этом случае следует использовать IMSQRT()).
Недопустимая операция с плавающей запятой
Вычисление приводит к переполнению определенного диапазона значений.
Ошибка в списке параметров
Недопустимый параметр функции, например, текст вместо числа или доменная ссылка вместо ссылки на ячейку.
Ошибка: нет пары
Отсутствует скобка: например, есть закрывающие скобки, но нет открывающих скобок.
Отсутствует оператор: например, в выражении «=2(3+4) * » нет оператора между символами «2» и «(«.
Нет переменной, например, в случае, когда два оператора стоят рядом «=1+*2».
Функция требует большего количества переменных, например, AND() и OR().
Слишком длинная формула
Компилятор: общее количество внутренних токенов (то есть операторов, переменных, скобок) в формуле превышает 8192.
Слишком длинная строка
Компилятор: идентификатор в формуле по размеру превышает 64 КБ. Интерпретатор: результат строковой операции по размеру превышает 64 КБ.
Операция сортировки, предпринятая на слишком большом количестве числовых данных (максимально 100000), или переполнение стека вычислений.
Внутренняя ошибка синтаксиса
В стеке вычислений предполагается матрица, но она недоступна.
Внутренняя ошибка синтаксиса
Неизвестный код: например, документ с новой функцией загружен в старую версию, не содержащую этой функции.
Внутренняя ошибка синтаксиса
Формула возвращает значение, не соответствующее определению, или ячейка, на которую ссылается формула, содержит текст вместо числа.
Внутренняя ошибка синтаксиса
Компилятор создал неизвестный код компиляции.
Внутренняя ошибка синтаксиса
Формула прямым или косвенным образом ссылается на себя, и не настроен параметр Циклы в разделе LibreOffice — Параметры Сервис — Параметры — LibreOffice Calc — Вычислить.
Процедура вычисления не сходится
Функция потеряла подбираемое значение или циклические ссылки не доходят до минимальных изменений для заданного максимального числа шагов.
Компилятор: не удалось определить имя описания столбца или строки. Интерпретатор: в формуле отсутствует столбец, строка или лист, содержащий ссылочную ячейку.
Идентификатор не может быть оценен (например, нет допустимой ссылки, нет допустимого доменного имени, нет подписи столбца/строки, нет макроса, неправильный десятичный разделитель, не найдена надстройка).
Внутренняя ошибка синтаксиса
Устарела, уже не используется, но может возникнуть из старых документов, если результатом является формула из домена.
Интерпретатор: ссылки (например, ссылка ячейки на ячейку) чрезмерно инкапсулированы.
Деление на ноль
Оператор деления/если знаменатель равен 0.
Эта ошибка возвращается некоторыми функциями, например:
VARP с менее чем 1 аргументом
STDEVP с менее чем 1 аргументом
ВАР с менее чем 2 аргументами
STDEV с менее чем 2 аргументами
STANDARDIZE с stdev=0
NORMDIST с stdev=0
Вложенные массивы не поддерживаются
Ошибка: Размер массива или матрицы
Неподдерживаемое содержимое встроенного массива
Внешнее содержимое отключено
Происходит, если встречается функция, требующая (повторной) загрузки внешних источников, а пользователь еще не подтвердил перезагрузку внешних источников.
«OpenOffice Error 502» — это стандартная ошибка времени выполнения. Разработчики тратят много времени и усилий на написание кода, чтобы убедиться, что OpenOffice стабилен до продажи продукта. Хотя эти превентивные действия принимаются, иногда ошибки, такие как ошибка 502, будут пропущены.
Что на самом деле вызывает ошибку времени выполнения 502?
Ошибки выполнения при запуске OpenOffice — это когда вы, скорее всего, столкнетесь с «OpenOffice Error 502». Мы можем определить происхождение ошибок ошибки 502 во время выполнения следующим образом:
Ошибка 502 Crash — ошибка 502 блокирует любой вход, и это может привести к сбою машины. Обычно это происходит, когда OpenOffice не может распознать, что ему дается неправильный ввод, или не знает, что он должен производить.
Утечка памяти «OpenOffice Error 502» — при утечке памяти OpenOffice это может привести к медленной работе устройства из-за нехватки системных ресурсов. Возможные искры включают сбой освобождения, который произошел в программе, отличной от C ++, когда поврежденный код сборки неправильно выполняет бесконечный цикл.
Ошибка 502 Logic Error — «логическая ошибка», как говорят, генерируется, когда программное обеспечение получает правильный ввод, но генерирует неверный вывод. Он материализуется, когда исходный код Apache ошибочен из-за неисправного дизайна.
Такие проблемы OpenOffice Error 502 обычно вызваны повреждением файла, связанного с OpenOffice, или, в некоторых случаях, его случайным или намеренным удалением. Основной способ решить эти проблемы вручную — заменить файл Apache новой копией. Запуск сканирования реестра после замены файла, из-за которого возникает проблема, позволит очистить все недействительные файлы OpenOffice Error 502, расширения файлов или другие ссылки на файлы, которые могли быть повреждены в результате заражения вредоносным ПО.
Типичные ошибки OpenOffice Error 502
Наиболее распространенные ошибки OpenOffice Error 502, которые могут возникнуть на компьютере под управлением Windows, перечислены ниже:
- «Ошибка OpenOffice Error 502. «
- «Ошибка программного обеспечения Win32: OpenOffice Error 502»
- «Возникла ошибка в приложении OpenOffice Error 502. Приложение будет закрыто. Приносим извинения за неудобства.»
- «OpenOffice Error 502 не может быть найден. «
- «OpenOffice Error 502 не найден.»
- «Проблема при запуске приложения: OpenOffice Error 502. «
- «OpenOffice Error 502 не выполняется. «
- «Ошибка OpenOffice Error 502. «
- «Неверный путь к приложению: OpenOffice Error 502.»
Проблемы OpenOffice OpenOffice Error 502 возникают при установке, во время работы программного обеспечения, связанного с OpenOffice Error 502, во время завершения работы или запуска или менее вероятно во время обновления операционной системы. Отслеживание того, когда и где возникает ошибка OpenOffice Error 502, является важной информацией при устранении проблемы.
OpenOffice Error 502 Истоки проблем
Проблемы OpenOffice Error 502 могут быть отнесены к поврежденным или отсутствующим файлам, содержащим ошибки записям реестра, связанным с OpenOffice Error 502, или к вирусам / вредоносному ПО.
- Если в формуле используется имя, которое было удалено или не определено.
Ошибки в Excel – Использование имени в формуле
Устранение ошибки: определите имя. Как это сделать описано в этой статье.
Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ
Устранение ошибки: проверьте правильность написания функции.
- В ссылке на диапазон ячеек пропущен знак двоеточия (:).
Ошибки в Excel – Ошибка в написании диапазона ячеек
Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).
- В формуле используется текст, не заключенный в двойные кавычки. Excel выдает ошибку, так как воспринимает такой текст как имя.
Ошибки в Excel – Ошибка в объединении текста с числом
Устранение ошибки: заключите текст формулы в двойные кавычки.
Ошибки в Excel – Правильное объединение текста
- Используете отрицательное число, когда требуется положительное значение.
Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ
Устранение ошибки: проверьте корректность введенных аргументов в функции.
- Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.
Ошибки в Excel – Ошибка в формуле из-за слишком большого значения
Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.
Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.
- Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:
Ошибки в Excel – Суммирование числовых и текстовых значений
Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.
- В аргументе функции введен диапазон, а функция предполагается ввод одного значения.
Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения
Устранение ошибки: укажите в функции правильные аргументы.
- При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.
Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .
Ошибки в Excel – Использование формулы массива
Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А
Устранение ошибки: измените формулу.
Данная ошибка Excel возникает при делении на ноль, то есть когда в качестве делителя используется ссылка на ячейку, которая содержит нулевое значение, или ссылка на пустую ячейку.
Устранение ошибки: исправьте формулу.
- При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:
Ошибки в Excel – Искомого значения нет в просматриваемом массиве
Устранение ошибки: задайте правильный аргумент искомое значение.
Устранение ошибки: см. раздел посвященный ошибкам функции ВПР
- Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:
Ошибки в Excel – Ошибки в формуле массива
Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.
- В функции не заданы один или несколько обязательных аргументов.
Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента
Устранение ошибки: введите все необходимые аргументы функции.
Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны
Устранение ошибки: проверьте правильность написания формулы.
Причины возникновения ошибки
- Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.
Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке
Устранение ошибки: увеличение ширины столбца/столбцов.
- Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.
Ошибки в Excel – Разница дат и часов не должна быть отрицательной
Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.
Читайте также:
- Как закрыть фотошоп если он завис
- Vba word ширина таблицы
- Как запустить кс через стим браузер
- Программа для телефона мобильный банк
- Прошить honor jat lx1
Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.
В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.
В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.
- Исправляем ошибку #Н/Д
- Исправляем ошибку #ЗНАЧ! в формулах с ВПР
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (проблемы, ограничения и решения)
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Содержание
- Исправляем ошибку #Н/Д функции ВПР в Excel
- 1. Искомое значение написано с опечаткой
- 2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
- 3. Ошибка #Н/Д при поиске точного совпадения с ВПР
- 4. Столбец поиска не является крайним левым
- 5. Числа форматированы как текст
- 6. В начале или в конце стоит пробел
- Ошибка #ЗНАЧ! в формулах с ВПР
- 1. Искомое значение длиннее 255 символов
- 2. Не указан полный путь к рабочей книге для поиска
- 3. Аргумент Номер_столбца меньше 1
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (ограничения, оговорки и решения)
- 1. ВПР не чувствительна к регистру
- 2. ВПР возвращает первое найденное значение
- 3. В таблицу был добавлен или удалён столбец
- 4. Ссылки на ячейки исказились при копировании формулы
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
- ВПР: работа с функцией ЕСЛИОШИБКА
- ВПР: работа с функцией ЕОШИБКА
Исправляем ошибку #Н/Д функции ВПР в Excel
В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.
1. Искомое значение написано с опечаткой
Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.
2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:
- Искомое значение меньше наименьшего значения в просматриваемом массиве.
- Столбец поиска не упорядочен по возрастанию.
3. Ошибка #Н/Д при поиске точного совпадения с ВПР
Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.
4. Столбец поиска не является крайним левым
Как Вы, вероятно, знаете, одно из самых значительных ограничений ВПР это то, что она не может смотреть влево, следовательно, столбец поиска в Вашей таблице должен быть крайним левым. На практике мы часто забываем об этом, что приводит к не работающей формуле и появлению ошибки #Н/Д.
Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.
5. Числа форматированы как текст
Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.
Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.
Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:
Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.
Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.
Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.
6. В начале или в конце стоит пробел
Это наименее очевидная причина ошибки #Н/Д в работе функции ВПР, поскольку зрительно трудно увидеть эти лишние пробелы, особенно при работе с большими таблицами, когда большая часть данных находится за пределами экрана.
Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)
Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)
Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.
Ошибка #ЗНАЧ! в формулах с ВПР
В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.
1. Искомое значение длиннее 255 символов
Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.
Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Не указан полный путь к рабочей книге для поиска
Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.
Вот полная структура функции ВПР для поиска в другой книге:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Настоящая формула может выглядеть так:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.
Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).
Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.
3. Аргумент Номер_столбца меньше 1
Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.
Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.
Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).
Ошибка #ИМЯ? в ВПР
Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.
Решение очевидно – проверьте правописание!
ВПР не работает (ограничения, оговорки и решения)
Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.
1. ВПР не чувствительна к регистру
Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.
Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.
2. ВПР возвращает первое найденное значение
Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).
3. В таблицу был добавлен или удалён столбец
К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.
Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.
4. Ссылки на ячейки исказились при копировании формулы
Этот заголовок исчерпывающе объясняет суть проблемы, правда?
Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.
ВПР: работа с функцией ЕСЛИОШИБКА
Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.
Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Если Вы хотите показать собственное сообщение вместо стандартного сообщения об ошибке функции ВПР, впишите его в кавычках, например, так:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
ВПР: работа с функцией ЕОШИБКА
Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.
Оцените качество статьи. Нам важно ваше мнение:
Huh… I honestly don’t see anything “wrong” with it. I’ve literally copy-n-pasted the VLOOKUP argument so I know it’s identical, except for the column from which it’s pulling that cell’s requested data.
Here’s what I’ve got:
=IF(H6=0,"",(VLOOKUP(H6,$'Data Lookup'.A:$'Data Lookup'.M,14,0)))
Everything stating M.13.0 and less (down to M.2.0, since M.1.0 is the initial search criterion) works perfectly. Just 14 and up do not work and return the 502 error.
This spreadsheet has six sheets to it.
The first sheet is a data entry sheet, where I type in the store number (along with first day of that week and team members I’m working with), and it’s broken out to give me five days, and then these are grouped into four groups (i.e. four weeks).
The second, third, fourth, and fifth sheets are weekly result tables which I can print.
The sixth sheet is the Data Lookup sheet, and it’s a massive table of store numbers (first field) and then all the other pertinent data for each of those stores (district, phone number, address, and so on).
Skip to content
Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).
Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»
В первую очередь, сообщение об ошибке вы увидите, если данные, которое вы ищете, действительно отсутствуют в указанном диапазоне поиска. Здесь мы бессильны.
Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.
Итак, почему ВПР не находит значение, хотя оно есть?
- Неточность при вводе данных.
- Опечатка при вводе функции.
- Неверные ссылки.
- Неверно указан параметр «интервальный просмотр».
- Столбец поиска не является первым слева столбцом диапазона поиска.
- Несовпадение форматов данных.
- Преобразуем число в текст.
- Преобразуем текст в число.
- Если числовой и текстовый форматы беспорядочно перемешаны?
- Лишние пробелы и непечатаемые знаки.
- Неправильно указан номер столбца.
- Неверная ссылка на данные из другой таблицы
- После изменения таблицы функция ВПР перестала работать
- Как убрать сообщение #Н/Д в ВПР?
Неточность при вводе данных.
В этом случае ничего найти не получится, и ВПР возвратит ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе критерия поиска.
Это вполне может случиться, особенно если вы записываете его прямо в формулу.
Опечатка при вводе функции.
Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.
Проверьте синтаксис, и всё будет в порядке.
Неверные ссылки.
Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.
Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.
Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.
Неверно указан параметр «интервальный просмотр».
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.
Об этой проблеме мы подробно говорили ранее.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что ВПР ищет совпадающие данные только в первом слева столбце диапазона, который вы указали.
Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.
Это важное ограничение нельзя забывать.
Несовпадение форматов данных.
Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.
Преобразуем число в текст.
Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.
Как решить эту проблему со случайным несоответствием в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любой тип данных в текстовый вид.
Используем формулу
=ВПР(ТЕКСТ(D6;»#»);$A$3:$B$21;2;ЛОЖЬ)
Как видно, обычную ссылку на D6 мы заменяем конструкцией
TEXT(D6,»#»)
Первый аргумент – это адрес, а второй аргумент означает формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).
Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.
=ВПР(D6&»»;$A$3:$B$21;2;ЛОЖЬ)
Запомните простое правило:
Если какую-либо текстовую операцию мы пытаемся провести над ячейкой, в которой записано число, то Excel обязательно попытается превратить это число в текст.
Если несовпадение форматов встречается редко, то можно просто исправить формат записи данных. Измените формат ячейки на текстовый и пересохраните ее содержание (F2 и затем ENTER). Содержимое будет преобразовано в текст.
Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.
Преобразуем текст в число.
А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.
Добавьте перед проблемным значением двойной минус. Это означает — дважды умножить на минус 1. А еще со школы мы помним, что минус на минус дает плюс. В результате ничего не изменится. Но есть важный момент.
Если какую-либо математическую операцию мы пытаемся провести над ячейкой, в которой записан текст, то Excel обязательно попытается преобразовать его в число, чтобы выполнить вычисления.
Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:
=ВПР(—A2;$D$2:$E$8;2;0)
Если числовой и текстовый форматы беспорядочно перемешаны?
А теперь — самый сложный случай. И здесь тоже есть простое решение.
Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.
=ЕСЛИОШИБКА(ВПР(—O2;$R$2:$S$8;2;0);ВПР(O2&»»;$R$2:$S$8;2;0))
В результате получаем немного громоздкий, но зато вполне универсальный вариант, который позволит избежать ошибок в формуле ВПР из-за несовпадения форматов значений.
Лишние пробелы и непечатаемые знаки.
Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
В нашем примере на картинке чуть выше формула ВПР возвратила ошибку, так как в D8 при вводе данных случайно были добавлены два пробела после слова. И, поскольку сравниваются символьные значения, поиск, естественно, завершился неудачно.
Такая неточность может встречаться довольно часто, и обнаружить её визуально практически невозможно. К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.
Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие неточности ввода вы не сможете.
В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо
=ВПР(D8;$A$3:$B$21;2;ЛОЖЬ)
вводим
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D8));$A$3:$B$21;2;ЛОЖЬ)
или
=VLOOKUP(TRIM(CLEAN(D8)),$A$3:$B$21,2,FALSE)
Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.
Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных неточностей ввода данных.
Неправильно указан номер столбца.
Иногда функция ВПР не срабатывает, потому что неправильно указан номер столбца, значение из которого вы хотите получить (третий аргумент).
Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!
Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.
И в любом случае – это число должно быть больше 1. Вряд ли кто-то из вас будет руками вводить 0 или -1, но если вы получаете его при помощи каких-то вычислений, вложенных в формулу, то здесь-то и может быть проблема.
Неверная ссылка на данные из другой таблицы
Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Excel.
Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.
Это будет означать, что вы ссылаетесь на неверный диапазон, который Excel найти не может.
После изменения таблицы функция ВПР перестала работать
Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или столбцов в вашу таблицу, все работало верно.
Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?
А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.
Кроме того, в третьем аргументе вы указываете порядковый номер столбца, из которого нужно извлечь данные. Но если вы вставили дополнительные колонки, то и количество столбцов, и их порядковые номера изменятся. А у вас в формуле используются «старые» номера.
Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.
Важно! Всегда используйте абсолютные ссылки, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.
Как убрать сообщение #Н/Д в ВПР?
Если функция не находит точного совпадения или искомого объекта просто нет в таблице, то она выдает сообщения, о которых мы говорили выше. С одной стороны, это хорошо, поскольку отрицательный результат- это тоже результат.
Однако, иногда это не очень удобно, т.к. такие сообщения потом порождают новые проблемы в расчете итогов, попадают в распечатку и т.д. Ведь если на эту ячейку ссылаются какие-то другие формулы, то они будут неправильно работать.
Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.
Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.
=ЕСЛИОШИБКА([выражение];[значение_если_ошибка])
Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.
В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.
Вот как нужно будет преобразовать наши формулы, чтобы все выглядело красиво.
=ЕСЛИОШИБКА(ВПР(A4;$D$2:$E$7;2;0);0)
Естественно, вместо нуля можно вписать «не найдено» либо любое другое сообщение, а можно просто поставить пробел. Но в случае, когда на основе этих ячеек затем производятся какие-то вычисления, ноль, возможно, будет предпочтительнее.
Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».
[the_ad_group id=»48″]
Еще полезные примеры:
Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft… 
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном… 
Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.… 
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом… 
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.
Причины и пути решения в Excel
Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.
Наиболее эффективный метод
В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.
Ошибка в типе параметров
Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:
- Выберите ячейку (одну или несколько).
- Жмите правой кнопкой мышки.
- Выберите формат ячеек, а дальше Число.
- Измените формат.
Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.
Лишние пробелы
Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.
Ошибки метода поиска совпадения
Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.
Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.
При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.
Не соответствие числа строк / столбцов заданному диапазону
В ситуации, когда не работает ВПР в Excel и вылетает ошибка Н / Д, сделайте дополнительную проверку. Вам нужно убедиться, что диапазон, в отношении которого ссылается формула, правильный. Как вариант, можно ввести формулу массива в меньшее / большее количество ячеек с учетом ссылки на диапазон.
В ячейке введена надпись Н / Д или Н Д
В Эксель ВПР часто не работает, если пользователь вручную ввел в ячейку параметр #Н / Д или НД (). Для решения проблем его нужно поменять на фактические данные, как только они будут доступны. До этого момента формулы, в которых содержатся ссылки на эти ячейки, не смогут вычислить этот параметр. При этом будет возвращаться ошибка Н Д.
Другие ошибки
Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:
- В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
- Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
- Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
- Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
- Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
- Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.
Выше рассмотренные основные причины, почему не работает ВПР в Excel, а также описаны базовые шаги для устранения ошибки Н Д. Всегда начинайте с проверки правильности ввода параметра, а после переходите к другим вариантам.
Что за функция
В завершение кратко рассмотрим, что это за опция ВПР в Excel, и как она работает. Простыми словами, это опция, позволяющая переставлять данные из одной таблицы в соответствующие параметры другой. Английское название опции звучит как VLOOKUP. Это очень полезная опция, позволяющая сэкономить время и одновременно обработать большое количество параметров.
К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.
В комментариях расскажите, приходилось ли вам пользовался опцией ВПР, и случалась ли ситуация, когда она не работает в Excel, и появляется ошибка Н Д. Отдельно поделитесь, какие варианты решения вопроса можно использовать.
Отличного Вам дня!