Впр ошибка недопустимое значение

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в 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;ЛОЖЬ))

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

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в 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;ЛОЖЬ))

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

Оцените качество статьи. Нам важно ваше мнение:

Исправление ошибки #Н/Д

​Смотрите также​​Vlad999​Как-то это можно​ игрушки :)​Функция не может найти​ означает, что разрешен​ Для этого выделите​ начале сообщит об​ и G27. Как​ значение, которое больше​ того, в случае​ выводить результат. Самый​).​ ниже показано, как​ вас установлена текущая​ точное совпадение, укажите​Ошибка #Н/Д из-за​Примечание:​:​ сделать?​Где ошибка? Возможно,​

​ нужного значения, потому​ поиск только​ все ячейки прайс-листа​

Лучшее решение

​ ошибке, т.к. не​ сделать чтобы в​ искомого, то она​ несортированного списка, ВПР() с​ левый столбец (ключевой)​Минимальное значение аргумента​ выглядит диаграмма со​ версия Office 365, и​ для аргумента​ разных типов данных​

​ Мы стараемся как можно​Fairuza​

​предпочитаемый способ решения​ у Вас она​ что в коде​точного соответствия​ кроме «шапки» (G3:H19),​

​ обнаружит этот метод.​​ этих ячейках или​ выводит значение, которое​ параметром​ имеет номер 1​номер_столбца​ значениями 0 и​ вы пользуетесь каналом​интервальный_просмотр​

• ​Решение​ оперативнее обеспечивать вас​,​ — формулы​ возникает из-за отсутствия​

​ присутствуют пробелы или​, т.е. если функция​ выберите в меню​ Хотя код написан​ вообще ничего не​

​ расположено на строку​Интервальный_просмотр​ (по нему производится​равно 1. При​ #Н/Д.​ выпуска программы предварительной​значение ЛОЖЬ. Помните,​. Убедитесь, что типы​ актуальными справочными материалами​

​Hugo121​​Michael_S​ параметра функции «интервальный_просмотр».​ невидимые непечатаемые знаки​ не найдет в​Вставка — Имя -​

​ будет верно. Вместо​ было или нули​ выше его. Как​ИСТИНА (или опущен)​ поиск).​ этом значение 1 соответствует​

Неправильные типы значений

​В предыдущем примере значения 0​ оценки с ранним​ что значение ИСТИНА,​ данных совпадают. Проверьте​ на вашем языке.​, сейчас попробую) спасибо)​: В С1​Guest​

​ (перенос строки и​ прайс-листе укзанного в​

​ Присвоить (Insert -​​ FileSearch в версиях​ стояли, вместо #Н/Д?​ следствие, если искомое​ работать не будет.​Параметр ​ столбцу поиска, значение 2 —​​ показаны в виде​​ доступом, можно ввести​​ сообщающее функции о​​ форматы ячеек. Для​ Эта страница переведена​Fairuza​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));;ВПР(A1;B:B;1;ЛОЖЬ))​

​: Ошибка возникла после​​ т.п.). В этом​ таблице заказов нестандартного​ Name — Define)​ 2007 и выше​Заранее благодарен!​​ значение меньше минимального​​В файле примера лист Справочник​​интервальный_просмотр​​ первому столбцу справа​​ прямой линии вдоль​​ формулу в верхней​

В ячейках есть лишние пробелы

​ том, что нужно​ этого выделите диапазон​ автоматически, поэтому ее​,​и протянуть вниз​ того, как я​ случае можно использовать​ товара (если будет​или нажмите​ приходится применять рекурсию.​JeyCi​

​ в ключевом столбце,​ также рассмотрены альтернативные​может принимать 2​

​ от столбца поиска​​ нижнего края диаграммы,​ левой ячейке диапазона​ искать приблизительное совпадение,​ ячеек, щелкните правой​ текст может содержать​Hugo121​yuslnt​ пересортировал исходный список​ текстовые функции​ введено, например, «Кокос»),​CTRL+F3​Мотя​​: =ЕСЛИОШИБКА(ВПР(D27;таблица;2;ЛОЖЬ);»»)​​ то функцию вернет​ формулы (получим тот​ значения: ИСТИНА (ищется​ и т. д. Поэтому при​ а затем линия​ вывода и нажать​ может привести к​ кнопкой мыши, выберите​ неточности и грамматические​,вышло вот так -​​: Михаил, спасибо.​​ в алфавитном порядке​СЖПРОБЕЛЫ (TRIM)​ то она выдаст​и введите любое​: Если макрос представляет​Deok1982​ ошибку ​ же результат) с​ значение ближайшее к критерию​

Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)

​ поиске в столбце​ резко поднимается вверх,​ клавишу​ возвращению не только​Формат ячеек​ ошибки. Для нас​ теперь пишет ?ИМЯ))​​Пока не разобрался​​ (где-то я нашел,​и​ ошибку #Н/Д (нет​ имя (без пробелов),​ собой​: , вашу формулу​#Н/Д.​​ использованием функций ИНДЕКС(),​​ или совпадающее с ним)​ A значение 1 указывает​ чтобы показать итог.​ВВОД​ ошибки #Н/Д, но​ >​ важно, чтобы эта​Код =ЕСЛИОШИБКА(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0);0;ВПР(A7;’Данные​ как это работает,​ что оно должно​ПЕЧСИМВ (CLEAN)​

​ данных).​ например​прикладную​ ввел в F27,​

​Найденное значение может быть​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ и ЛОЖЬ (ищется значение​ на него, значение 2 —​ В следующем примере​, чтобы подтвердить использование​ и ошибочных результатов,​Число​ статья была вам​ (2)’!$C$6:$E$40;2;0)) что тут​ но это работает.​ так быть. но​для их удаления:​Если введено значение​Прайс​ценность, проблему всегда​ теперь вместо #Н/Д​ далеко не самым​ ключевой столбец (столбец​ в точности совпадающее​ на столбец B,​ вместо нулевых значений​ формулы динамического массива.​ как видно в​(или нажмите клавиши​ полезна. Просим вас​ не так? пробовала​

​Michael_S​ после этого где-то​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​1​​. Теперь в дальнейшем​ можно решить.​ появилось #Имя?​ ближайшим. Например, если​​ с артикулами) не​​ с критерием). Значение ИСТИНА​​ значение 3 — на столбец​​ используются значения #Н/Д.​

Формула массива ссылается на диапазон, не соответствующий по количеству строк или столбцов диапазону, содержащему формулу массива.

​ Иначе формулу необходимо​ следующем примере.​ CTRL+1) и при​ уделить пару секунд​ и этим вариантом​: что б было​ опять с той​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​или​ можно будет использовать​МВТ​Pelena​ попытаться найти ближайшую​ является самым левым​ предполагает, что первый​

​ C и т. д.​Исправление ошибки #Н/Д в​ вводить с использованием​

​Функция ВПР возвращает​ необходимости измените числовой​ и сообщить, помогла​

​ и если(еош(формула),0, формула)).​ более понятно, эта​ же строчки начинаются​Для подавления сообщения об​ИСТИНА (TRUE)​

​ это имя для​: Мотя, хотя бы​: Попробуйте так​

​ цену для 199,​​ в таблице, то​ столбец в​Задать вопрос на форуме​ функции ВПР​ прежней версии массива,​ ошибку из-за применения​ формат.​ ли она вам,​Veronka​ формула идентична вот​ не те результаты.​ ошибке​​, то это значит,​​ ссылки на прайс-лист.​ тем, что ВПР()​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»»;ВПР(D27;таблица;2;ЛОЖЬ))​ то функция вернет​ функция ВПР() не​таблице​ сообщества, посвященном Excel​Исправление ошибки #Н/Д в​ выбрав диапазон вывода,​ аргумента приблизительного совпадения​​Совет:​​ с помощью кнопок​: Код =ЕСЛИ(ЕНД(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0));0;ВПР(A7;’Данные​ этой​ Закономерность отследить не​#Н/Д (#N/A)​ что Вы разрешаете​Теперь используем функцию​ в этой ситуации​МВТ​

Если, не располагая необходимыми данными, вы вручную ввели в ячейку значение #Н/Д или НД(), замените его фактическими данными, как только они станут доступны. До этого момента формулы, содержащие ссылки на эти ячейки, не смогут вычислить значения и будут возвращать ошибку #Н/Д.

​ 150 (хотя ближайшее​ применима. В этом​отсортирован в алфавитном​У вас есть предложения​ функциях ИНДЕКС и​ введя формулу в​ в неотсортированной таблице​ Если вам нужно принудительно​

В формуле, использующей стандартную или пользовательскую функцию, отсутствует один или несколько обязательных аргументов.

​ внизу страницы. Для​ (2)’!$C$6:$E$40;2;0))​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));0;ВПР(A1;B:B;1;ЛОЖЬ))​ удается.​в тех случаях,​ поиск не точного,​ВПР​ придется вычислять дважды.​: Или установите Excel​ все же 200).​ случае нужно использовать​ порядке или по​

Пользовательская функция, которую вы ввели, недоступна

​ по улучшению следующей​ ПОИСКПОЗ​ левой верхней ячейке​В этом примере возвращается​ изменить формат для​

Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».

​ удобства также приводим​значит у вас​_Boroda_​с «интервальным_просмотром» вообще​ когда функция не​

При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д

​ а​. Выделите ячейку, куда​ А если таблица​

Нужна помощь по аргументам функции?

​ 2007 или «старше»:​ Это опять следствие​ альтернативные формулы. Связка​ возрастанию. Это способ​ версии Excel? Если​К началу страницы​ диапазона и нажав​ не только ошибка​​ целого столбца, сначала​​ ссылку на оригинал​​ эксель 2003 и​​: Итоговую формулу по​

​ не совсем разобрался.​

​ может найти точно​приблизительного соответствия​ она будет введена​

Использование #Н/Д в диаграммах

​ объемная, это может​ до него функции​ того, что функция находит​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ используется в функции​ да, ознакомьтесь с​Примечание:​ клавиши​ #Н/Д для элемента​ примените нужный формат,​ (на английском языке).​ в нем нет​ двум столбцам можно​

​З.Ы. Ну игрушки,конечно,​ соответствия, можно воспользоваться​, т.е. в случае​ (D3) и откройте​ тормозить работу. Тем​ ЕСЛИОШИБКА() не было.​ наибольшее число, которое​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ по умолчанию, если​ темами на портале​

Дополнительные сведения об ошибке #Н/Д в конкретных функциях см. в следующих статьях:

• ​Мы стараемся как​CTRL+SHIFT+ВВОД​

• ​ «Банан», но и​ а затем выберите​Ошибка #Н/Д обычно означает,​

​ функции ЕСЛИОШИБКА​

support.office.com

Исправление ошибки #ЗНАЧ! в функции ВПР

​ переделать так:​​ но автоматизация процесса​ функцией​ с «кокосом» функция​ вкладку​ более, это не​Deok1982​ меньше или равно​В файле примера лист Справочник показано, что​ не указан другой.​ пользовательских предложений для​ можно оперативнее обеспечивать​для подтверждения. Excel​ неправильная цена для​Данные​ что формула не​Veronka​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЕЧИСЛО(ПОИСКПОЗ(A1;B:B;0))​ расчетов в данном​ЕСЛИОШИБКА​ попытается найти товар​Формулы — Вставка функции​

​ аргумент, а совет​: Pelena, в F27​ заданному.​ формулы применимы и​Ниже в статье рассмотрены​ Excel.​ вас актуальными справочными​ автоматически вставляет скобки​ элемента «Черешня». К​ >​

Проблема: длина аргумента искомое_значение превышает 255 символов.

​ находит запрашиваемое значение.​​: Проверьте так, правда​или, с любимым​ ключемне интереснее​(IFERROR)​ с наименованием, которое​

​ (Formulas — Insert​​Мотя​ стало пусто, но​Если нужно найти по​ для ключевых столбцов​ популярные задачи, которые​Функция ВПР(), английский вариант​ материалами на вашем​ в начале и​ такому результату приводит​Текст по столбцам​

Проблема: аргумент номер_столбца содержит текст или значение меньше 0.

​Чаще всего появление ошибки​ возможно с диапазонами​ многими ВПРом​DaffyMason​

​. Так, например, вот​ максимально похоже на​ Function)​: Птичку жалко…​​ в G27 появилось​​ настоящему ближайшее к​ содержащих текстовые значения,​ можно решить с​ VLOOKUP(), ищет значение​ языке. Эта страница​ конце формулы. Дополнительные​ аргумент ИСТИНА, который​ >​ #Н/Д обусловлено тем,​ промахнулась))) Код =СУММПРОИЗВ((A7=’Данные​​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—НЕ(ЕОШИБКА(ВПР(A1;B:B;1;0)))​​: Спасибо! Включение параметра​

​ такая конструкция перехватывает​​ «кокос» и выдаст​​. В категории​Безысходка…​ #ЗНАЧ!​ искомому значению, то ВПР() тут​ т.к. артикул часто​ использованием функции ВПР().​ в первом (в​ переведена автоматически, поэтому​ сведения о формулах​ сообщает функции ВПР,​Готово​ что формула не​

У вас есть вопрос об определенной функции?

​ (2)’!$C$6:$C$40)*’Данные (2)’!$D$6:$D$40)​Кстати, старайтесь не​

Помогите нам улучшить Excel

​ «Интервальный_просмотр» — ЛОЖЬ​ любые ошибки создаваемые​ цену для этого​Ссылки и массивы (Lookup​МВТ​Deok1982​ не поможет. Такого​

support.office.com

Функция ВПР() в MS EXCEL

​ бывает текстовым значением.​Пусть дана исходная таблица​ самом левом) столбце​ ее текст может​ массива см. в​ что нужно искать​.​ может найти значение,​

​Hugo121​ использовать в формулах​ помогло… результаты идут​ ВПР и заменяет​

​ наименования. В большинстве​ and Reference)​: Бесперспективняк — круче​: К сожалению, но​ рода задачи решены​ Также задача решена​ (см. файл примера​

Синтаксис функции

​ таблицы и возвращает​

​ содержать неточности и​​ статье Использование формул​ не точное, а​Начальные и конечные пробелы​ на которое ссылается​​: — не выбирает​​ столбец целиком. Если​ верные. с цифрами​ их нулями:​ случаев такая приблизительная​найдите функцию​The_Prist​ работаем на 2003,​ в разделе Ближайшее​​ для несортированного ключевого​​ лист Справочник).​

​ значение из той​​ грамматические ошибки. Для​ массива: рекомендации и​ приблизительное совпадение. Здесь​​ можно удалить с​​ функция ВПР, ГПР,​ значения, которые есть,​ формул много или​ посмотрю еще​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​ подстановка может сыграть​ВПР (VLOOKUP)​: Проблему вообще всегда​ т.к. макросы которые​​ ЧИСЛО. Там же можно​​ столбца.​Задача состоит в том,​​ же строки, но​​ нас важно, чтобы​ примеры.​​ нет близкого совпадения​​ помощью функции СЖПРОБЕЛЫ.​​ ПРОСМОТР или ПОИСКПОЗ.​ выводит только нули.​​ они сложные -​

​GIG_ant​​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​​ с пользователем злую​​и нажмите​ можно решить. Я​ когда то писали​ найти решение задачи​Примечание​ чтобы, выбрав нужный​

​ другого столбца таблицы.​​ эта статья была​​В данном случае для​ для элемента «Банан»,​ В приведенном ниже​ Например, искомого значения​— работает!!! спасибо))​ тормозить будет. Вместо​: дык где же​Если нужно извлечь не​ шутку, подставив значение​​ОК​​ про то, что​ в 2003, не​ о поиске ближайшего​. Для удобства, строка​ Артикул товара, вывести​Функция ВПР() является одной​

​ вам полезна. Просим​ месяцев с мая​ а «Черешня» предшествует​ примере в функции​

Задача1. Справочник товаров

​ нет в исходных​Veronka​ В:В можно, например,​

​ все таки ошибка​ одно значение а​ не того товара,​. Появится окно ввода​ не надо сразу​

​ работают в 2007​​ при несортированном ключевом​ таблицы, содержащая найденное​ его Наименование и​

​ из наиболее используемых​ вас уделить пару​​ по декабрь указано​​ элементу «Персик». В​ ВПР используется вложенная​ данных.​:​​ написать $B$1:$B$1000. Или​​ ??​ сразу весь набор​ который был на​ аргументов для функции:​ валить вину на​

​Deok1982​ столбце.​​ решение, выделена Условным форматированием.​​ Цену. ​

​ в EXCEL, поэтому​ секунд и сообщить,​ значение #Н/Д, поэтому​ этом случае при​ функция СЖПРОБЕЛЫ для​Элемент не найден​Fairuza​​ сделать динамический диапазон.​​DaffyMason​ (если их встречается​​ самом деле! Так​​Заполняем их по очереди:​ руки разработчика. Вы​: Pelena, вроде бы​Примечание​ (см. статью Выделение​Примечание​​ рассмотрим ее подробно. ​​ помогла ли она​

​ итог вычислить не​ использовании функции ВПР​ удаления начальных пробелов​ в исходных данных​,​Saff​: Я теперь не​ несколько разных), то​ что для большинства​

​Искомое значение (Lookup Value)​ же именно «кривыми​ разобрался, в вашу​. Для удобства, строка​ строк таблицы в​. Это «классическая» задача для​В этой статье выбран​ вам, с помощью​​ удается и вместо​​ с аргументом ЛОЖЬ​ из имен в​

​В данном случае в​Vlad999​: Добрый день!​ смогу воспроизвести тот​ придется шаманить с​ реальных бизнес-задач приблизительный​- то наименование​ руками» прокомментировали тот​ формулу «0» еще​ таблицы, содержащая найденное​ MS EXCEL в​ использования ВПР() (см.​ нестандартный подход: акцент​ кнопок внизу страницы.​ него отображается ошибка​ будет отображаться правильная​

​ ячейках A2:A7 и​ таблице подстановки нет​,​Помогите: Требуется вместо​ вариант исходного списка,​ формулой массива.​ поиск лучше не​ товара, которое функция​ факт, что код​

​ добавил​​ решение, выделена Условным форматированием.​ зависимости от условия​ статью Справочник).​ сделан не на​ Для удобства также​ #Н/Д.​ цена для элемента​ возврата названия отдела.​

​ элемента «Банан», поэтому​​Hugo121​ «#Н/Д» ставить «0»,​​ при котором функция​​Усовершенствованный вариант функции ВПР​ разрешать. Исключением является​ должна найти в​ перестал работать. Я​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»0″;ВПР(D27;таблица;2;ЛОЖЬ))​ Это можно сделать​ в ячейке).​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​ саму функцию, а​ приводим ссылку на​

Задача2. Поиск ближайшего числа

​Чтобы исправить ошибку, проверьте​ «Черешня», но для​= ВПР (D2; TRIM​ функция ВПР возвращает​, День добрый, похожий​

​ если не находит​ выдавала #Н/Д​ (VLOOKUP 2).​ случай, когда мы​

1. ​ крайнем левом столбце​ привел аргумент из​Вроде все заработало​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​
2. ​Примечание​ значение параметра​ на те задачи,​
3. ​ оригинал (на английском​​ синтаксис используемой функции​​ элемента «Банан» все​ (A2: B7); 2;​

​ ошибку #Н/Д.​

​ вопрос. Если условие​ значение?​DaffyMason​Быстрый расчет ступенчатых (диапазонных)​

​ ищем числа, а​ прайс-листа. В нашем​ реальной жизни, когда​ как надо.Спасибо​Примечание​. Никогда не используйте​Интервальный_просмотр​ которые можно решить​ языке) .​ и введите все​ равно будет указана​ ложь)​Решение​ похоже, тоесть вместо​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;))​: С цифрами тоже​ скидок при помощи​ не текст -​ случае — слово​ даже грамотный код​​V​

​: Если в ключевом​ ВПР() с параметром ​можно задать ЛОЖЬ​ с ее помощью.​Если вы работаете с​ обязательные аргументы, которые​ ошибка #Н/Д, потому​Примечание:​. Убедитесь, что искомое​ Сейчас выдает н/д,​Michael_S​ полный порядок.​ функции ВПР.​

​ например, при расчете​ «Яблоки» из ячейки​ откажется работать при​: П.С. цифры можно​ столбце имеется значение​Интервальный_просмотр​ или ИСТИНА или​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​ функцией ВПР, весьма​ возвращают ошибку. Вероятно,​ что в списке​

​ 24 сентября 2018 г. (формулы динамического​​ значение есть в​ если не указано​:​V​Как сделать «левый ВПР»​

​ Ступенчатых скидок.​​ B3.​ переходе на более​ без кавычек писать.​ совпадающее с искомым,​ ИСТИНА (или опущен) если​​ вообще опустить). Значение​​Искомое_значение​ вероятно, что вы​ для проверки функции​ подстановок его нет.​ массива): если у​

​ исходных данных, или​ название конфеты, и​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=если(счетесли(коэф!$C$2:$C$19;C4);ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;));»»)​: DaffyMason если прописываешь​ с помощью функций​Все! Осталось нажать​Таблица (Table Array)​

excel2.ru

Как избавиться от #н/д в excel при впр?

​ новую версию Excel.​​вместо ЛОЖЬ можно​
​ то функция с​
​ ключевой столбец не​
​ параметра ​- это значение,​ не раз сталкивались​ вам потребуется использовать​Если вы используете функцию​ вас установлена текущая​ используйте в формуле​ никак не могу​Saff​ интервальный_просмотр «ЛОЖЬ» сортировать​ ИНДЕКС и ПОИСКПОЗ​
​ОК​- таблица из​Кому лень или нет​ писать 0 (или​ параметром ​ отсортирован по возрастанию,​номер_столбца​ которое Вы пытаетесь​ с ошибкой #ЗНАЧ!.​ редактор Visual Basic.​ ПОИСКПОЗ, попробуйте изменить​ версия Office 365, и​ обработчик ошибок, например​ выкрутить, чтоб выводило​: Michael_S, спасибо большое!​ по порядку не​Как при помощи функции​и скопировать введенную​ которой берутся искомые​ времени читать -​
​ оставить пустой) покороче​

​Интервальный_просмотр​​ т.к. результат формулы​

​нужно задать =2,​​ найти в столбце​ В этом разделе​ Открыть этот редактор​ значение аргумента​

​ вы пользуетесь каналом​​ функцию ЕСЛИОШИБКА. Например,​
​ ноль, если нету​

​ всё работает )​​ обязательно. На будущее.​ ВПР (VLOOKUP) заполнять​ функцию на весь​ значения, то есть​

​ смотрим видео. Подробности​​ будет.​ =ЛОЖЬ вернет первое найденное​ непредсказуем (если функция ВПР()​ т.к. номер столбца​

​ с данными.​​ перечислены наиболее распространенные​ можно на вкладке​тип_сопоставления​ выпуска программы предварительной​ формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает​ нужного значения, тоесть​

​Чудовище​​ )))​ бланки данными из​ столбец.​ наш прайс-лист. Для​
​ и нюансы -​
​Юрий М​ значение, равное искомому,​

​ находит значение, которое​​ Наименование равен 2​Искомое_значение ​
​ проблемы с функцией​ «Разработчик» или с​, чтобы указать порядок​ оценки с ранним​

​ следующее:​​ если впр ищет​: Добрый день. Помогите,​

​Guest​​ списка​Функция​ ссылки используем собственное​ в тексте ниже.​: Это что за​ а с параметром​ больше искомого, то​ (Ключевой столбец всегда​может быть числом или​ ВПР и их​ помощью клавиш ALT+F11.​

​ сортировки таблицы. Чтобы​​ доступом, можно ввести​=ЕСЛИ(при вычислении формулы получается​
​ *Ромашку*, а в​

​ пожалуйста — если​​: Да я как-то​Как вытащить не первое,​ВПР (VLOOKUP)​

​ имя «Прайс» данное​​Итак, имеем две таблицы​
​ макросы такие?​ =ИСТИНА — последнее​ она выводит значение,​
​ номер 1). ​ текстом, но чаще​

​ возможные решения.​​Чтобы исправить ошибку, убедитесь​ найти точное совпадение,​

​ формулу в верхней​​ ошибка, то показать​ списке её нет.​ я при использовании​ вообще сей параметр​ а сразу все​возвращает ошибку #Н/Д​ ранее. Если вы​ -​Deok1982​ (см. картинку ниже).​ которое расположено на​Для вывода Цены используйте​ всего ищут именно​Решение​

​ в том, что​​ задайте для аргумента​ левой ячейке диапазона​​ 0, в противном​​Hugo121​ ВПР ищу не​

​ из вида упустил…​​ значения из таблицы​ (#N/A) если:​ не давали имя,​таблицу заказов​: , есть два​Если столбец, по которому​ строку выше его).​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​ число. Искомое значение должно​

​: Сократите значение или​​ книга, содержащая пользовательскую​
​тип_сопоставления​

​ вывода и нажать​​ случае показать результат​

​: СУММПРОИЗВ() даст 0,​​ 1-е, а 3-й​ сейчас таосвал список​Функции VLOOKUP2 и VLOOKUP3​Включен точный поиск (аргумент​ то можно просто​и​ файла екселевских. Сначала​ производится поиск не​Предположим, что нужно найти​номер_столбца​ находиться в первом​ используйте сочетание функций​ функцию, открыта, а​значение​ клавишу​ формулы)​

planetaexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​ если нет данных,​ столбец?​ так и эдак​ из надстройки PLEX​Интервальный просмотр=0​

Постановка задачи

​ выделить таблицу, но​прайс-лист​​ запускается один, через​​ самый левый, то​​ товар, у которого​​нужно задать =3). ​

​ (самом левом) столбце​ ИНДЕКС и ПОИСКПОЗ​ функция работает правильно.​0​ВВОД​Вы можете указать «»,​ или же​

Решение

​=ВПР(C12;$A$1:$B$282;2;ЛОЖЬ)​ — все норм.​​DaffyMason​ ​) и искомого наименования​​ не забудьте нажать​​:​ ​ него второй. Во​​ ВПР() не поможет.​​ цена равна или​Ключевой столбец в нашем​ диапазона ячеек, указанного​ в качестве обходного​Чтобы исправить ошибку, убедитесь​(ноль).​, чтобы подтвердить использование​ чтобы не отображалось​Veronka​Как здесь исключить​Пойду на кальке​: Возникли своеобразные затруднения​

​ нет в​ потом клавишу​Задача — подставить цены​ втором появляется определенное​ В этом случае​ наиболее близка к​ случае содержит числа​ в​ пути.​​ в том, что​Чтобы исправить ошибку, убедитесь,​ формулы динамического массива.​​ ничего, или подставить​​: И СУММПРОИЗВ() не​​ Н/Д?​ теперь поизголяюсь​ с применением функции​​Таблице​​F4​ из прайс-листа в​ меню с добавлением​ нужно использовать функции​

​ искомой.​​ и должен гарантировано​​таблице​Примечание:​ аргументы функции верны​ что диапазон, на​​ Иначе формулу необходимо​ собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение​ переносит строк в​​Читайте правила​​Я​ ВПР.​​.​​, чтобы закрепить ссылку​​ таблицу заказов автоматически,​​ объектов, с введением​​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​Чтобы использовать функцию ВПР()​

​ содержать искомое значение​

• ​.​​ Это формула массива, которую​ и расположены в​ который ссылается формула​ вводить с использованием​ об ошибке»)​ суммируемых данных.​Veronka​: в опен офисе​
• ​При создании файла​​Включен приблизительный поиск (​ знаками доллара, т.к.​ ориентируясь на название​ размеров и т.д.​Deok1982​ для решения этой​ (условие задачи). Если первый​Таблица -​ нужно вводить с​ нужных местах.​ массива, содержит такое​ прежней версии массива,​​Если вы не знаете,​​Fairuza​: День добрый, подскажите​ нет параметра интервальный​ в Excel 2003,​Интервальный просмотр=1​ в противном случае​ товара с тем,​ Так вот в​
• ​: Здравствуйте, Уважаемые Гуру​​ задачи нужно выполнить​ столбец не содержит искомый​ссылка на диапазон​ помощью клавиш CTRL+SHIFT+ВВОД.​Чтобы исправить ошибку, нажмите​ же количество строк​ выбрав диапазон вывода,​ что делать на​, а формула так​ как правильно сделать.​ просмотр. Как же​
• ​ когда функция ВПР​​), но​ она будет соскальзывать​ чтобы потом можно​ 2007 работать не​
• • ​Подскажите, пожалуйста.​​ несколько условий:​​ артикул​​ ячеек. В левом​​ Excel автоматически заключит​ клавиши CTRL+ALT+F9 для​ и столбцов, что​​ введя формулу в​​ этом этапе или​ и должна писаться​ есть перечень конфет​ быть???((​ ссылается на окно​Таблица​ при копировании нашей​ было посчитать стоимость.​ хочет​
  • ​Есть 4 ячейки.​​Ключевой столбец, по которому​​, ​​ столбце таблицы ищется ​​ формулу в фигурные​ пересчета листа.​ и диапазон ячеек,​ левой верхней ячейке​​ какого рода помощь​​ через запятые?​ и есть отдельный​vikttur​ с проверкой данных​, в которой происходит​ формулы вниз, на​В наборе функций Excel,​Deok1982​Ячейка D27 выпадающий​ должен производиться поиск,​то функция возвращает значение​Искомое_значение​ скобки {}. Если​Если вы не знаете​ в котором была​ диапазона и нажав​ вам нужна, поищите​Fairuza​ заказ на магазин​: Бежать на форум​ списком, на некоторые​ поиск не отсортирована​ остальные ячейки столбца​ в категории​

​: Вы имеете ввиду​​ список с размерами​​ должен быть самым​ ошибки​, а из столбцов​

Ошибки #Н/Д и их подавление

​ вы попытаетесь ввести​​ точно, какие аргументы​​ введена формула массива.​ клавиши​

• ​ похожие вопросы на​​, когда пользуюсь ,​​ (некоторые позичии). К​ по ОпенОфису.​​ значения функция либо​​ по возрастанию наименований.​
• ​ D3:D30.​​Ссылки и массивы​​ так?​​ (ширина подоконников), E27​​ левым в таблице;​ #Н/Д. ​ расположенных правее, выводится​
• ​ их вручную, Excel​ использовать, вам поможет​ Или введите формулу​CTRL+SHIFT+ВВОД​ форуме сообщества Excel​ выглядит вот так​ заказу нежно перетянуть​Z​ возвращает неверны результат​Формат ячейки, откуда берется​Номер_столбца (Column index number)​(Lookup and reference)​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;0));0;ВПР(D27;таблица;2;0))​ ячейка для ввода​Ключевой столбец должен быть​Это может произойти, например,​​ соответствующий результат (хотя,​​ отобразит формулу как​​ мастер функций. Выберите​​ массива в меньшее​для подтверждения. Excel​ или опубликуйте там​
  ​ Код =ЕСЛИ(ЕОШ(ВПР(A7;’06.2015′!$A$13:$I$165;3;0));0;ВПР(A7;’06.2015′!$A$13:$I$165;3;0))) ,​
• ​ стоимость конфет из​: Не следует быть​ (соседние строки), либо​ искомое значение наименования​- порядковый номер​имеется функция​V​ данных (длина подконников),​ обязательно отсортирован по​​ при опечатке при​​ в принципе, можно​​ текст.​​ ячейку в формуле,​
  ​ или большее число​
  ​ автоматически вставляет скобки​

​ свой вопрос.​ всё-равно выводит н/д-​​ перечня, но если​​ столь категоричным… Откройте​ ошибку #Н/Д. Перемена​ (например B3 в​ (не буква!) столбца​ВПР​​: да. это была​ ​ ячейка F27 с​​ возрастанию;​ вводе артикула. Чтобы не ошибиться​ вывести можно вывести​Номер столбца — это индекс​ которая вызывает у​

​ ячеек в соответствии​

​ в начале и​

P.S.

​Примечание:​ это как у​ там не указана​ в Кальке файл​ порядка в списке,​ нашем случае) и​ в прайс-листе из​

Ссылки по теме

• ​(VLOOKUP)​ инфа для общего​
• ​ формулой =ВПР(D27;таблица;2;ЛОЖЬ) и​Значение параметра ​ с вводом искомого​
• ​ значение из левого​ столбца, который нужно​ вас сомнения, затем​
• ​ со ссылкой на​ конце формулы. Дополнительные​ Если вам нужна справка​ кого настроен эксель,​
• ​ цена — выдаёт​ XL c формулой​ работа с числовыми​
• ​ формат ячеек первого​ которого будем брать​

planetaexcel.ru

Функция ВПР: неверный результат либо ошибка #Н/Д

​.​​ развития.​ в четвертой ячейке​Интервальный_просмотр​
​ артикула можно использовать Выпадающий​ столбца (в этом​ вернуть, отсчитываемый вправо​ откройте вкладку​ диапазон в формуле.​ сведения о формулах​ по ошибке #Н/Д​ русский вариант чаще​ н/д. нужно чтоб​ ВПР и тогда​ показателями ситуации не​ столбца (F3:F19) таблицы​ значения цены. Первый​Эта функция ищет​Мотя​ G27 получаю данные​ нужно задать ИСТИНА или​ список (см. ячейку ​ случае это будет​ от столбца поиска.​
​Формула​В данном примере ячейка​ массива см. в​

​ для конкретной функции,​​ через ; работает.​ там просто был​

​ поймете, что ЕСТЬ…​​ улучшили. Аналогичная ситуация​

​ отличаются (например, числовой​​ столбец прайс-листа с​ заданное значение (в​
​: Однако, аргумент…​ =(E27*F27)*B4, где B4​ вообще опустить.​Е9​

​ на несовпадающие диапазоны:​​ массива: рекомендации и​ ИНДЕКС/ПОИСКПОЗ, щелкните здесь.​ если впр возвращает​ ЕСЛИ — не​ps Ключик -​

​ VLOOKUP в Open​​ случай особенно характерен​ 1, следовательно нам​ слово «Яблоки») в​

​ в EXCEL 2003​​Так вот, если​Для вывода найденной цены (она​Понятно, что в нашей​)). Часто левый столбец​ ошибку при вводе​

​.​​= SUM (если (A2:​ примеры.​

​Если вы хотите работать​​ ошибку (не нашлось),​ работает..​ в сортировке или​ Office Calc. как​ при использовании вместо​

​ нужна цена из​​ крайнем левом столбце​ комбинация ЕСЛИ(ЕОШИБКА(ВПР(…​ мы не вводим​ не обязательно будет​ задаче ключевой столбец​ называется​
​ аргумента​Excel автоматически запустит мастер.​

​ A11 = D2,​​По умолчанию функции, которые​ дальше, приведенный ниже​ то выводим 0​Hugo121​

​ ее отсутствии… ;)​​ ни странно, схожая​ текстовых наименований числовых​

​ столбца с номером​​ указанной таблицы (прайс-листа)​Работает распрекрасно хорошо​ значение в E27​ совпадать с заданной) используйте​ не должен содержать​ключевым​
​номер_столбца​
​Щелкните любой аргумент, и​ B2: B5))​ ищут данные в​ контрольный список поможет​

planetaexcel.ru

Замена значений ячеек #Н/Д (при исп. ВПР имею #Н/Д, с которой ничего не могу сделать)

​ ну или что​​: Попробуйте СУММПРОИЗВ() -​
​ -13529-​ конструкция в Excel​ кодов (номера счетов,​ 2.​ двигаясь сверху-вниз и,​ (см.)!​ и в D27​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ повторов (в этом​
​. Если первый столбец​или случайно указали​
​ Excel покажет вам​Чтобы формула вычислялась правильно,​

​ таблицах, должны использовать​​ вам определить возможные​
​ захотим, в другом​
​ он вернет 0,​

​yuslnt​​ 2010 Работает.​
​ идентификаторы, даты и​Интервальный_просмотр (Range Lookup)​ найдя его, выдает​

​Мотя​​ стоит какое-нибудь значение,​Как видно из картинки​ смысл артикула, однозначно​ не содержит ​
​ число менее 1​

​ сведения о нем.​​ необходимо изменить ее​ сортировку по возрастанию.​ причины проблем в​
​ случае пусть работает​
​ если нет цены​: Добрый день, коллеги!​
​Не исключаю, что​
​ т.п.) В этом​- в это​ содержимое соседней ячейки​: Значит, эти макросы​ тогда все нормально,​ выше, ВПР() нашла​ определяющего товар). В​искомое_значение​ в качестве значения​

​Значение #Н/Д может принести​​ так, чтобы оба​
​ Но у функций​ формулах.​ впр.​А с ВПР​
​При использовании функции​

​ упустилкакой-то момент. Подскажите​​ случае можно использовать​​ поле можно вводить​

​ (23 руб.) Схематически​​ писали «кривые» руки!​ в F27 показывается​

​ наибольшую цену, которая​​ противном случае будет​,​ индекса (такое часто​ пользу. Значения #Н/Д​ диапазона включали строки​ ВПР и ГПР​
​Искомое значение и исходные​
​P.S. я ошибся,​ нужно такую констррукцию​
​ ВПР для сравнения​

excelworld.ru

ВПР возвращает НД, если нет данных

​ пожалуйста!​​ функции​ только два значения:​ работу этой функции​The_Prist​ прайсовая цена по​ меньше или равна​ выведено самое верхнее​то функция возвращает​ происходит, если другая​ часто используются в​ 2–11.​ есть аргумент​ данные относятся к​ там ЕНД нужно​ = ЕСЛИОШИБКА(тут Ваша​ значений в столбцах,​

​Guest​​Ч​ ЛОЖЬ или ИСТИНА:​ можно представить так:​
​: Не факт. Если​ таблице, в G27​ заданной (см. файл​ значение.​

​ значение ошибки​​ функция Excel, вложенная​
​ диаграммах с такими​= SUM (если (A2:​интервальный_просмотр​

​ разным типам. Например,​​ использовать (у меня​​ формула;0)​​ появляется значение #Н/Д,​​: нужен небольшой пример​​и​
​Если введено значение​​Для простоты дальнейшего использования​​ использовался метод FileSearch,​​ стоит 0. Но​ примера лист «Поиск​
​При решении таких задач​ #Н/Д.​ в функцию ВПР,​ данными, как в​ A11 = D2,​

​, который сообщает функции,​​ вы пытаетесь использовать​ англ.версия, там ISNA​
​Veronka​ которое я не​ от вас​ТЕКСТ​

​0​​ функции сразу сделайте​ то он заблокирован​ стоит убрать выпадающий​ ближайшего числа»). Это​

​ ключевой столбец лучше​​Номер_столбца​ возвращает число, например​ приведенном ниже примере,​
​ B2: Б11))​

​ что нужно искать​​ ссылку на функцию​​ используется).​​: Используйте «если ошибка»​​ могу «превратить» в​​DaffyMason​​для преобразования форматов​​или​ одну вещь -​ в версиях старше​ список D27 (Delete)​ связано следует из​ предварительно отсортировать (это также​- номер столбца​ 0, в качестве​ поскольку эти значения​Примечание:​ точное совпадение, даже​ ВПР как число,​Hugo121​

​P.S. ЕСЛИОШИБКА() в​​ любое другое значение,​: вот эта табличка​ данных. Выглядеть это​

​ЛОЖЬ (FALSE)​​ дайте диапазону ячеек​ 2003. И поэтому​ как появляется #Н/Д​

​ того как функция​​ поможет сделать Выпадающий​Таблицы​ значения аргумента​
​ не отображаются на​​ 24 сентября 2018 г. (формулы динамического​ если таблица не​ а исходные данные​, да, через ЕНД​ 2003 нет, используйте​ с которым потом​vikttur​ будет примерно так:​
​, то фактически это​ прайс-листа собственное имя.​ код в самом​ в ячейках F27​ производит поиск: если функция ВПР() находит​ список нагляднее). Кроме​, из которого нужно​номер_столбца​
​ диаграмме. В примерах​ массива): если у​ отсортирована. Чтобы найти​ сохранены как текст.​ заработало)) спасибо​​ если(еош(формула),0, формула))​​ смогу работать.​: Ох уж эти​

CyberForum.ru

​=ВПР(ТЕКСТ(B3);прайс;0)​

Перейти к содержимому