Как убрать н д в excel

Как убрать ошибки #ЗНАЧ! и Н/Д в формулах «Excel»

Часто при использовании формул в программе «Excel» из за не корректно введенных значений могут возникать ошибки #ЗНАЧ! и Н/Д в формулах.

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

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

Рассмотрим функцию «Excel» при помощи, которой можно убрать (спрятать) ошибки #ЗНАЧ! и Н/Д в ячейках.

Эта функция обработки ошибок «ЕСЛИОШИБКА()».

Как работает функция ЕСЛИОШИБКА()?

Данная функция позволяет заменить, возникающие ошибки, на любое значение.

Например, дефис («-») или звездочку («*»).

Функция ЕСЛИОШИБКА() работает следующим образом:

В поле «ЗНАЧЕНИЕ» вводиться формула, в которой может возникать ошибка.

В поле «Значение_если_ошибка» вводится то значение, которое присваивается ячейке при возникновении ошибки в формуле.

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Excel убрать н д

​ исправления, вы можете​​ встречается значение ошибки​ сразу во всех​ Нужно: чтобы в​ тоже подсказали решение:​Еще вопрос по​ избавится от «Н/Д»​Michael_S​ обнаружит этот метод.​ в 2003, не​Подскажите, пожалуйста.​ЕСЛИ в ячейке A3​ соответствующая ячейка не​Зависимости формул​нажмите кнопку​.​Убрать стрелки​ улучшить представление результатов,​ #Н/Д (значение недоступно),​ ячейках (они разбросаны​ тех ячейках,где отображается​ в ячейке текст​

​ тому, где в​ в формуле =ЕСЛИ(ЕНД(ВПР(A441;’Цена​: В С1​ Хотя код написан​ работают в 2007​Есть 4 ячейки.​ указано значение, возвращается​ пуста.​нажмите кнопку​

​Убрать стрелки​Щелкните сводную таблицу.​.​ скрыв значения ошибок​ то функция СУММ()​​ по всему листу​​ значение #Н/Д, менялось​ кирилличей — «#Н/Д»​ столбце М значение​ металла’!$B$3:$E$116;4))=»м.п»;»м.п»;ВПР(A441;’Цена металла’!$B$3:$E$116;4;0))*K441*I441​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));;ВПР(A1;B:B;1;ЛОЖЬ))​ будет верно. Вместо​Deok1982​Ячейка D27 выпадающий​ результат формулы, в​Измените ссылку в формуле,​Убрать стрелки​

​.​На вкладке​Выделите ячейку со значением​

Скрытие индикаторов ошибок в ячейках

​ и индикаторы ошибок​ также вернет ошибку.​ — не по​ хотя бы на​ а в коде​ м2:​mus013​и протянуть вниз​ FileSearch в версиях​

​: Pelena, вроде бы​

​ список с размерами​​ противном случае возвращается​​ чтобы она указывала​​.​​Выделите ячейку со значением​

​Анализ сводной таблицы​​ ошибки.​​ в ячейках.​​ Используем функцию СУММЕСЛИ()​ ​ порядку).​​ 0.​​ на удаление надо​

​Цитата​​: Д вечер,могу поделиться​yuslnt​ 2007 и выше​ разобрался, в вашу​ (ширина подоконников), E27​ ноль.​ на ячейку, не​​Щелкните сводную таблицу.​​ ошибки.​​нажмите кнопку​​Добавьте формулу в ячейке​​Формулы могут возвращать ошибки​​ для обработки таких​

Дополнительные параметры

Отображение прочерка, строки «#Н/Д» или «НД» вместо значения ошибки

​ писать «#N/A»​ASM_0408, 27.05.2014 в​​ собственным опытом-выделяю столбец-ставлю​​: Михаил, спасибо.​

​ приходится применять рекурсию.​ ​ формулу «0» еще​

​ ячейка для ввода​Вы также можете избежать​

​ содержащую ноль (0)​

​Добавьте формулу в ячейке​Параметры​

​ (​ по разным причинам.​

​ ситуаций.​: Нужно выделить ячейку​

​: Набираем в окне​и тогда Replace​

​ 22:51, в сообщении​ фильтр выбираю по​

​Пока не разобрался​Мотя​

Изменение способа отображения значений ошибки в сводной таблице

​ данных (длина подконников),​​ ошибки, вложив операцию​​ или пустое значение.​​Сводная таблица​​ (​

​.​​старая_формула​​ Например, деление на​​Некоторые функции ПРОСМОТР(), ПОИСКПОЗ()​​ с формулой правой​

​ поиска «вместо #Н/Д».​

​ удаляет без проблем!​

​ «Н/Д»-удаляю-​ как это работает,​

​: Если макрос представляет​

​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»0″;ВПР(D27;таблица;2;ЛОЖЬ))​ ячейка F27 с​

​ деления в функцию​​Введите в ячейку, которая​в разделе​старая_формула​На вкладке​) в следующую формулу:​ 0 запрещено, поэтому​ или ВПР() могут​​ кнопкой мыши, скопировать,​​ Дальше — на​​ПалычЪ​​ столбца M м2​​копирую данные столбца​​ но это работает.​

Изменение способа отображения пустых ячеек в сводной таблице

​Вроде все заработало​​ формулой =ВПР(D27;таблица;2;ЛОЖЬ) и​​ ЕСЛИОШИБКА. Так, для​​ используется в формуле​​Данные​

​) в следующую формулу:​​Отображение​​=ЕСЛИ​​ при вводе формулы​​ вернуть ошибку #Н/Д​

​ затем выделить, удерживая​

​: Господа форумчане добрый​

​Но вдруг пойдет​ значением. Удачи.​Michael_S​

​ как надо.Спасибо​ в четвертой ячейке​

​ расчета отношения A2/A3​

​ в качестве делителя,​​нажмите кнопку​​=ЕСЛИ​

Скрытие индикаторов ошибок в ячейках

​установите флажок​(ЕОШИБКА( старая_формула),»», старая_формула)​=1/0​ (значение недоступно).​ клавишу Ctrl, нужные​Юрий М​ день! Вопрос: как​ такая формула:​shurikus​

​ценность, проблему всегда​​V​​ G27 получаю данные​​ будет использоваться формула​​ значение​

​Параметры​​(ЕОШИБКА( старая_формула),»», старая_формула)​​Для пустых ячеек отображать​​Выполните одно из указанных​ ​Excel возвращает #ДЕЛ/0.​​При сложении значений из​​ ячейки и вставить.​

​: Вот одна из​​ переделать формулу в​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(I441<>»»;ЕСЛИ(F441=»м2″;I441*ВПР(A441;’Цена металла’!$B$3:$H$107;4;0);ЕСЛИ(F441=»м.п.»;J441*K441*ВПР(A441;’Цена металла’!$B$3:$H$107;4;0);));)​: А может эта​ более понятно, эта​ можно решить.​: П.С. цифры можно​ =(E27*F27)*B4, где B4​​=ЕСЛИОШИБКА(A2/A3;0):​​#Н/Д​​.​​Выполните одно из указанных​​и сделайте следующее:​ ​ ниже действий.​

Дополнительные параметры

Отображение прочерка, строки «#Н/Д» или «НД» вместо значения ошибки

​ Существуют следующие значения​ диапазона, содержащих ошибку​

​cvetik​ ссылок:​​ желтых ячейках, что​​Авдей​

​ ошибка что-нибудь полезное​ ​ формула идентична вот​

​МВТ​ без кавычек писать.​

​ это курс валют.​

​если вычисление по​

​, чтобы изменить результат​На вкладке​

​ ниже действий.​Отображаемые элементы​

​Отображаемые элементы​ ошибок: #ДЕЛ/0!, #Н/Д,​

​ #Н/Д, функцией СУММ(),​: Не так все​

​Guest​ бы когда голубые​

​: Всем привет!​ указывает. ​

Изменение способа отображения значений ошибки в сводной таблице

​: Мотя, хотя бы​​вместо ЛОЖЬ можно​​Так вот, если​​ формуле вызывает ошибку,​​ формулы на #Н/Д,​​Отображение​​Отображаемые элементы​

​Действия​​Действия​​ #ИМЯ?, #ПУСТО!, #ЧИСЛО!,​​ получим ошибку #Н/Д.​​ просто. Каждая формула​

​: А для начинающих​

​ ячейки были пустыми,​

​В процессе работы​

​Если нет, то​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));0;ВПР(A1;B:B;1;ЛОЖЬ))​

​ писать 0 (или​ мы не вводим​

​ возвращается значение «0»,​​ указывая на недоступность​установите флажок​Действия​Значение в пустых ячейках​Прочерк, если значение содержит​ #ССЫЛКА! и #ЗНАЧ!​Простейшим решением является использование​​ создана для конкретного​​ и неопытных -можно​​ в желтых ячейках​​ с кучей файлов​​ поставьте:​ ​_Boroda_​

Изменение способа отображения пустых ячеек в сводной таблице

​ в этой ситуации​

​ оставить пустой) покороче​​ значение в E27​​ в противном случае —​​ делителя.​​Для пустых ячеек отображать​​Прочерк, если значение содержит​​Введите значение, которое будет​

​ ошибку​​ Ячейки, у которых​​ функции СУММЕСЛИ(), например,​​ наименования. А при​​ поконкретней?​

​ не было слова​

​ с использованием ВПР​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ЕСЛИ(ЕНД(ВПР(A441;’Цена металла’!$B$3:$E$116;4))=»м.п»;»м.п»;ВПР(A441;’Цена металла’!$B$3:$E$116;4;0))*K441*I441;»»)​

​: Итоговую формулу по​ придется вычислять дважды.​ будет.​

​ результат вычисления.​Часто ошибки #ДЕЛ/0! нельзя​

​и сделайте следующее:​

​ ошибку​​ отображаться в пустых​​Введите дефис (-) внутри​

См. также

​ есть индикаторы ошибок,​ так =СУММЕСЛИ(E4:E8;»<>#Н/Д»). Предполагается,​

​ копировании присваивается одно​Есть формула =ЕСЛИ(ПОИСКПОЗ(«воскресенье»;Лист2!$B:$B;0);»воскресенье»).​ ЛОЖЬ? Помогите пожалуйста!​

​ перебором (больше 400​

​AlexM​ двум столбцам можно​

Исправление ошибки #ДЕЛ/0!

​ А если таблица​Юрий М​ стоит какое-нибудь значение,​В версиях до Excel 2007​ избежать, поскольку ожидается,​Отображаемые элементы​Введите дефис (-) внутри​​ ячейках.​​ кавычек в формуле.​ то есть треугольники​ что данные находятся​ и то же​ Что в ней​ И просьба если​

​ файлов), столкнулся с​: Мне кажется, то​

​ переделать так:​ объемная, это может​: Это что за​ тогда все нормально,​ можно использовать синтаксис​ что вы или​

​Действия​ кавычек в формуле.​Пустые ячейки​»НД», если значение содержит​ в левом верхнем​

​ в диапазоне​ наименование.​ надо поменять, чтобы​ возможно, формулы в​​ необходимостью чистить #Н/Д..Вручную​​ вы потеряли нолик​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЕЧИСЛО(ПОИСКПОЗ(A1;B:B;0))​ тормозить работу. Тем​ макросы такие?​

​ в F27 показывается​ ЕСЛИ(ЕОШИБКА()):​ другой пользователь введете​Значение в пустых ячейках​»НД», если значение содержит​Удалите все символы в​ ошибку​ углу, содержат формулы​E4:E8​kim​

Оценка знаменателя на наличие нуля или пустого значения

​ не отображалось #Н/Д.​ желтых ячейках должны​ все чистится отлично,​ в формуле. попробуйте​или, с любимым​ более, это не​Deok1982​ прайсовая цена по​=ЕСЛИ(ЕОШИБКА(A2/A3);0;A2/A3)​ значение. В этом​Введите значение, которое будет​ ошибку​

​ поле.​Введите «НД» внутри кавычек​​ с ошибками.​​.​: Естественно, откуда Экселю,​​Дмитрий Д​​ так и остаться​ но макросом данные​​ так​​ многими ВПРом​ аргумент, а совет​: , есть два​ таблице, в G27​​(см. статью Функции​​ случае нужно сделать​ отображаться в пустых​Введите «НД» внутри кавычек​Нуль в пустых ячейках​​ в формуле.​​Более новые версии​​Другой вариант – формула​ а уж тем​: ЕНД — определяет​ на основе формул​ действия уже не​

Использование функции ЕСЛИОШИБКА для подавления ошибки #ДЕЛ/0!

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A441;’Цена металла’!$B$3:$E$116;4;0))=»м.п»;»м.п»;ВПР(A441;’Цена металла’!$B$3:$E$116;4;0))*K441*I441​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—НЕ(ЕОШИБКА(ВПР(A1;B:B;1;0)))​Мотя​ файла екселевских. Сначала​ стоит 0. Но​ Е).​​ так, чтобы сообщение​​ ячейках.​ в формуле.​Снимите флажок​»#Н/Д», если значение содержит​ Office 2011 г.​

​ массива =СУММ(ЕСЛИ(ЕНД(E4:E8);0;E4:E8))​ более нам, знать,​ при #Н/Д ИСТИНА,​​ ЕСЛИ и ВПР. ​​ работают, есть куча​Точнее можно сказать​

​Кстати, старайтесь не​: Птичку жалко. ​ запускается один, через​ стоит убрать выпадающий​Примечание. При использовании функции​ об ошибке не​Пустые ячейки​»#Н/Д», если значение содержит​Для пустых ячеек отображать​ ошибку​ ​После ввода формулы,​ какое именно наименование​

​ поэтому ЕСЛИ(ЕНД(ПОИСКПОЗ(«воскресенье»;Лист2!$B:$B;0);»воскресенье»);»»; ЕСЛИ(ПОИСКПОЗ(«воскресенье»;Лист2!$B:$B;0);»воскресенье»).​​ хотя и длинные​ макросов которые чистят​ при наличии файла​​ использовать в формулах​Безысходка. ​ него второй. Во​​ список D27 (Delete)​​ ЕСЛИОШИБКА или синтаксиса​ отображалось. Добиться этого​Удалите все символы в​

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

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

​Замените кавычки в формуле​Если ячейка содержит формулу,​ нужно вместо​ Вам нужно. Поэтому​ Вроде так.​ от этого они)))Заранее​ перебором каждую ячейку.Ну​

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

​ASM_0408​​ столбец целиком. Если​
​МВТ​
​ втором появляется определенное​
​ как появляется #Н/Д​ ЕСЛИ(ЕОШИБКА()) подавляются любые​ можно несколькими способами.​ поле.​Замените кавычки в формуле​Если ячейка содержит формулу,​ функцией НД().​ которая нарушает правило,​ENTER​ это дело прийдется​cvetik​
​ спасибо! Пример во​ тут проблема, из​: Всем спасибо! Сам​ формул много или​: Бесперспективняк — круче​ меню с добавлением​ в ячейках F27​ возможные ошибки, а​Самый простой способ избавиться​Нуль в пустых ячейках​ функцией НД().​ которая нарушает правило,​Щелкните сводную таблицу.​ используемое Excel для​нажать​ править вручную. Хотя​: Пробовала ввести такую​ вложении.​ 10 секунд затрачиваемых​ так и не​
​ они сложные -​

​The_Prist​​ объектов, с введением​

​ и G27. Как​​ не только #ДЕЛ/0!.​ от ошибки #ДЕЛ/0! —​Снимите флажок​Щелкните сводную таблицу.​

​ используемое Excel для​​На вкладке​
​ проверки на наличие​

​CTRL+SHIFT+ENTER​​ если увидеть реальный​ формулу, но появляется​VEKTORVSFREEMAN​ на обработку 1​

​ добил. Суть в​​ тормозить будет. Вместо​: Проблему вообще всегда​ размеров и т.д.​ сделать чтобы в​

​ Перед использованием различных​​ воспользоваться функцией ЕСЛИ​Для пустых ячеек отображать​На вкладке​ проверки на наличие​Анализ сводной таблицы​ проблем, в ее​

​.​​ файл, то может​ сообщение «Для данной​: так​ файла, где то​
​ следующем при наличии​
​ В:В можно, например,​ можно решить. Я​

​ Так вот в​​ этих ячейках или​ способов исключения ошибок​
​ для оценки знаменателя.​.​Сводная таблица​ проблем, в ее​

​нажмите кнопку​​ левом верхнем углу​Еще один вариант с​

​ быть возможно более​​ функции введено Слишком​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(B19=1;ВПР(C19;$K$3:$T$8;4;0);ЕСЛИ(B19=2;ВПР(C19;$K$3:$T$8;5;0);ЕСЛИ(B19=3;ВПР(C19;$K$3:$T$8;6;0);ЕСЛИ(B19=4;ВПР(C19;$K$3:$T$8;7;0);ЕСЛИ(B19=5;ВПР(C19;$K$3:$T$8;8;0);ЕСЛИ(B19=6;ВПР(C19;$K$3:$T$8;9;0);ЕСЛИ(B19=7;ВПР(C19;$K$3:$T$8;10;0);»»)))))))​ 8 уходит именно​ значения в столбце​ написать $B$1:$B$1000. Или​ про то, что​ 2007 работать не​ вообще ничего не​ следует проверить работоспособность​ Если он равен​Удаление или разрешение циклической​

​в разделе​​ левом верхнем углу​Параметры​
​ отображается треугольник. Вы​

​ применением формулы массива =СУММ(ЕСЛИ(ЕЧИСЛО(E4:E8);E4:E8))​​ простое решение. Но​ много аргументов».​Pelena​

​ на чистку циклом​​ I это надо​
​ сделать динамический диапазон.​ не надо сразу​ хочет​
​ было или нули​ формулы. В противном​

​ нулю или пустой,​​ ссылки​Данные​

​ отображается треугольник. Вы​​.​ можете скрыть такие​Примечание:​ видимо данные секретны,​Неужели ничего нельзя​: Формулу в Е19​ (больше 10 тыс​ перемножить на столбец​Saff​ валить вину на​Deok1982​ стояли, вместо #Н/Д?​ случае вы можете​ в результате вычислений​

​Добавление планок погрешностей или​​нажмите кнопку​ можете скрыть такие​​На вкладке​​ индикаторы.​Мы стараемся как​

​ а потому не​​ сделать?​ можно сократить​ строк, 2 столбца)​ K и столбец​: Добрый день!​ руки разработчика. Вы​: Вы имеете ввиду​Заранее благодарен!​ не заметить, что​

​ можно отображать значение​​ вверх или вниз​
​Параметры​

​ индикаторы.​​Отображение​

​Ячейка с индикатором ошибки​​ можно оперативнее обеспечивать​ судьба.​kim​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(C19;$K$3:$T$8;B19+3;ЛОЖЬ);»»)​Перелазил по куче​ Е листа Цена​Помогите: Требуется вместо​ же именно «кривыми​ так?​JeyCi​ формула не работает​ «0» или не​ погрешностей на диаграмму​.​Ячейка с индикатором ошибки​установите флажок​

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

​В меню​​ вас актуальными справочными​
​cvetik​: cvetik​О разных способах​ форумов и книг,​ металла событие должно​ «#Н/Д» ставить «0»,​ руками» прокомментировали тот​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;0));0;ВПР(D27;таблица;2;0))​: =ЕСЛИОШИБКА(ВПР(D27;таблица;2;ЛОЖЬ);»»)​
​ нужным образом.​ выводить ничего. В​
​Исправление несогласованной формулы​На вкладке​

​В меню​​Для ошибок отображать​
​Excel​
​ материалами на вашем​

​: Что ж, спасибо​​Скажите, чего конкретно​
​ скрыть или заменить​ и ничего не​ происходить при значении​

​ если не находит​​ факт, что код​V​Deok1982​Совет:​
​ противном случае будет​

​Отображение и скрытие нулевых​​Отображение​Excel​и сделайте следующее:​
​выберите пункт​
​ языке. Эта страница​ и на этом.​
​ вы хотите добиться​
​ значение ошибки можно​ нашел.​ столбца M м2,​ значение?​ перестал работать. Я​: да. это была​: , вашу формулу​ Если в Microsoft​ вычислен результат формулы.​

​ значений​​установите флажок​
​выберите пункт​Отображаемые элементы​Параметры​ переведена автоматически, поэтому​
​vikttur​

​ этой формулой?​​ прочитать в статье​ ​Ну неужели нет​

​ при значении м.п.​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;))​ привел аргумент из​

​ инфа для общего​​ ввел в F27,​ Excel включена проверка​Например, если ошибка возникает​Ошибка #ДЕЛ/0! возникает в​Для ошибок отображать​Параметры​
​Действия​
​.​ ее текст может​
​: cvetik, не отчаивайтесь,​

Как убрать «Н/Д» в формуле определения массы изделия (Формулы/Formulas)

​Попробуйте ввести: =ЕСЛИ(ЕНД(ПОИСКПОЗ(«воскресенье»;Лист2!$B:$B;0));»»;»воскресенье»)​​VEKTORVSFREEMAN​ способа чистить СРАЗУ​ перемножаются столбцы J​Michael_S​ реальной жизни, когда​

​ развития.​​ теперь вместо #Н/Д​ ошибок, нажмите кнопку​ в формуле​ Microsoft Excel, когда​
​и сделайте следующее:​.​

​Определенное значение вместо ошибок​​В разделе​ содержать неточности и​ не все так​
​cvetik​: в вашем файле​
​ весь диапазон от​

​ и K и​​:​ даже грамотный код​Мотя​ появилось #Имя?​ ​рядом с ячейкой,​
​=A2/A3​ число делится на​

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

​Формулы и списки​​ отображаться вместо ошибок.​щелкните​ нас важно, чтобы​ секреты на апельсины/бананы,​

​Это то, что​​ и эту формулу​Авдей​

​ Цена металла.​​Saff​ переходе на более​А чем плоха​: Попробуйте так​ ошибка. Выберите пункт​
​ формулой​ происходит, если вы​
​Определенное значение вместо ошибок​щелкните​Пустая ячейка вместо ошибок​Поиск ошибок​​ эта статья была​​ пиво/водку, цветики/конфетики и​ нужно. ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(L$3:T$8;ПОИСКПОЗ(C19;K$3:K$8;0);ПОИСКПОЗ(ВЫБОР(B19;N$2;O$2;P$2;Q$2;R$2;S$2;T$2);N$2:T$2;0));»»)​: Попробуй так:​
​AlexM​: Michael_S, спасибо большое!​
​ новую версию Excel.​

Быстрое удаление #Н/Д

​ в EXCEL 2003​​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»»;ВПР(D27;таблица;2;ЛОЖЬ))​
​Показать этапы вычисления​=ЕСЛИ(A3;A2/A3;0)​ вводите простую формулу,​Введите значение, которое будет​Поиск ошибок​Удалите все символы в​и снимите флажок​ вам полезна. Просим​ показывайте народу. Уверен​kim​ПалычЪ​Range(«B2:D5»).SpecialCells(xlCellTypeFormulas, 16).ClearContentsВместо «B2:D5″​: У меня конвертер​ всё работает )​yuslnt​ комбинация ЕСЛИ(ЕОШИБКА(ВПР(. ​МВТ​, если он отобразится,​, чтобы возвращать 0,​ например​
​ отображаться вместо ошибок.​и снимите флажок​ поле.​Включить фоновый поиск ошибок​
​ вас уделить пару​ — помогут, хотя​: Еще добавлю:​: Спасибо Вам господа​

​ вставь свой диапазон.​​ не преобразует ваш​
​Чудовище​: Добрый день, коллеги!​

​Работает распрекрасно хорошо​​: Или установите Excel​ а затем выберите​ или формулой​=5/0​Пустая ячейка вместо ошибок​
​Включить фоновый поиск ошибок​Совет:​.​
​ секунд и сообщить,​
​ бы добрым советом.​
​Дабы не вводить​

​ волшебники. все теперь​bariki​ файл в формат​: Добрый день. Помогите,​При использовании функции​ (см.)!​ 2007 или «старше»:​
​ подходящее решение.​=ЕСЛИ(A3;A2/A3;»»)​

Как скрыть значение #Н/Д на данном примере. (Формулы/Formulas)

​, или если формула​​Удалите все символы в​.​ После того как вы​Совет:​ помогла ли она​:)​ вручную дни недели,​ норм. все варианты​: Ну как так​ xls​ пожалуйста — если​ ВПР для сравнения​Мотя​ до него функции​Задать вопрос на форуме​, чтобы возвращать пустую​ ссылается на ячейку​ поле.​Совет:​

​ определили ячейку, которая​​ После того как вы​
​ вам, с помощью​

​: — Официант, один​​ в ячейке B3​ правильные. Но первые​
​ Сколько раз вводил​
​ASM_0408​ я при использовании​ значений в столбцах,​: Значит, эти макросы​

​ ЕСЛИОШИБКА() не было.​​ сообщества, посвященном Excel​ строку. Также можно​ с 0 или​
​Примечание:​

​ После того как вы​​ вызывает проблемы, вы​ определили ячейку, которая​ кнопок внизу страницы.​ бифштекс и один​ листа 4 введите​ два для меня​ этот код..и не​: Блин файл не​ ВПР ищу не​

замена ошибки Н/Д в ячейке на 0

​ появляется значение #Н/Д,​​ писали «кривые» руки!​Deok1982​У вас есть предложения​ выводить произвольное сообщение.​ пустую ячейку, как​ После того как вы​ определили ячейку, которая​ также можете скрыть​ вызывает проблемы, вы​

​ Для удобства также​​ добрый совет, пожалуйста.​ такую формулу:​ понятнее. так как арсенал​ работало..а тут работает!​

​ сжимается до 100кб.​​ 1-е, а 3-й​ которое я не​

​The_Prist​​: Pelena, в F27​ по улучшению следующей​ Пример:​
​ показано на этом​ определили ячейку, которая​ вызывает проблемы, вы​ влияющие и зависимые​

​ также можете скрыть​​ приводим ссылку на​- Вот Ваш​=ЕСЛИ(СЧЁТЕСЛИ(Лист2!B:B;ТЕКСТ(СТРОКА(2:2);»ДДДД»))>0;ТЕКСТ(СТРОКА(2:2);»ДДДД»);»») и скопируйте​ формул что я​

​ что за. ​​shurikus​ столбец?​ могу «превратить» в​: Не факт. Если​ стало пусто, но​
​ версии Excel? Если​=ЕСЛИ(A3;A2/A3;»Ожидается значение.»)​

​ рисунке.​​ вызывает проблемы, вы​
​ также можете скрыть​ стрелки трассировки. На​ влияющие и зависимые​
​ оригинал (на английском​

​ бифштекс​​ ее вниз.​
​ знаю пока не​Сегодня несколько кодов​

​: Не совсем понял​​=ВПР(C12;$A$1:$B$282;2;ЛОЖЬ)​
​ любое другое значение,​ использовался метод FileSearch,​ в G27 появилось​ да, ознакомьтесь с​. В первой приведенной​
​Есть несколько способов исправления​ также можете скрыть​

​ влияющие и зависимые​​ вкладке​ стрелки трассировки. На​

​ языке) .​- А добрый​cvetik​ полный. Спасибо!​ перепробовал и этот​ зачем в вашей​Как здесь исключить​ с которым потом​

​ то он заблокирован​​ #ЗНАЧ!​ темами на портале​ здесь формуле также​ этой ошибки.​ стрелки трассировки от​ стрелки трассировки. На​

​Формулы​​ вкладке​Если формулы содержат ошибки,​ совет?​: kim,дни недели -​cvetik​ в том числе..​ формуле искать «м.п.»​

​ Н/Д?​​ смогу работать.​ в версиях старше​Deok1982​ пользовательских предложений для​ можно использовать функцию​Убедитесь в том, что​ влияющих и зависимых​ вкладке​в группе​Формулы​ о которых вы​- Не ешьте​ это для примера.​: Есть меняющийся каждый​

​Добавлено через 1 минуту​​ в столбце Е​Читайте правила​

​Как-то это можно​​ 2003. И поэтому​: К сожалению, но​ Excel.​ ЧАСТНОЕ:​ делитель в функции​ ячеек. На вкладке​Формулы​Зависимости формул​

​в группе​​ знаете и которые​ Вы этот бифштекс​А не подскажите,​
​ день диапазон значений.​только у меня​
​ листа «цена металла»:​ASM_0408​
​ сделать?​ код в самом​

Функция ВПР не работает – способы устранения ошибок Н/Д, ИМЯ и ЗНАЧ

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (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 (СЖПРОБЕЛЫ):

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

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(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»Ничего не найдено. Попробуйте еще раз!»)
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»Ничего не найдено. Попробуйте еще раз!»)

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:

=IF(ISERROR(VLOOKUP формула),»Ваше сообщение при ошибке»,VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);»Ваше сообщение при ошибке»;ВПР формула)

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

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

Как в Экселе исправить ошибку Н/Д

Всем привет. Эта статья – одна из важнейших, что есть на этом сайте. Уже многие годы я учу вас пользоваться функциями поиска: ВПР, ПОИСКПОЗ и др. Эти функции разыскивают нужное значение в таблице и возвращают что-то в зависимости от результата поиска. Это важнейшие инструменты для любого пользователя Excel, вам нужно иметь полное представление о том, как они работают.

Часто функции поиска возвращают ошибку #Н/Д. Значит, программа не смогла найти искомое значение. Это неприятно, ведь все последующие расчёты становятся невозможными. Многие пугаются такой ошибки, т.к. мало о ней знают. А напрасно.

Это очень полезная ошибка, она возникает, когда в расчётах, или в данных что-то не так, и функция не может найти искомое значение. Чтобы разобраться с этим, задайте себе вопрос: возможно ли, что нужного значения нет в таблице? В зависимости от ответа, имеем два сценария.

Отсутствие значения в исходной таблице маловероятно или исключено

Если значение должно быть, а его нет, значит нужно проверить:

1. Правильно ли записана формула, не пропущены аргументы;
2. Правильно ли вы используете функцию. Почитайте о том, как она работает и сверьте с тем, как её применяете. Список полезных статей о функциях поиска я прикреплю в конце этого материала;
3. Оцените таблицу с исходными данными, при возможности, визуально оцените её полноту, сверьте с первоисточником, если такой есть. Например, сравните количество строк;
4. Проверьте правильность записи искомого значения. Часто в таких данных появляются лишние пробелы, непечатаемые символы. Их сложно заметить сразу, но они могут быть причиной ошибки #Н/Д;
5. Аналогично, проверьте правильность записи искомых значений в таблице с данными. Здесь есть высокая вероятность непечатаемых символов, которые могут появиться при выгрузке таблиц из базы данных;
6. Диапазон для поиска, заданный в функции, не охватывает всю таблицу, некоторые строки или столбцы «выпали» из неё. Такое может быть результатом механической ошибки, либо неправильного использования относительных ссылок. Вообще, относительные и абсолютные ссылки очень полезны, но часто приводят к ошибкам у новичков. Разберитесь с ними, если хотите хорошо владеть Эксель, это простая и важная тема. Вот вам статья в помощь;
7. Искомое значение и проверяемые в таблице – одного и того же типа. Часто, при сохранении таблиц из базы данных, числа сохраняются, как текст. Сопоставление данных разных типов приводит к появлению #Н/Д;
8. Вы используете правильный режим поиска в функциях? Необдуманный выбор режимов может привести к ошибке, или вернуть неверные результаты. В последнем случае, вы даже не заметите, что что-то пошло не так. Больше о режимах поиска – в статьях из списка ниже;
9. Обдумайте прочие возможные причины. При возможности, обратитесь за помощью к более опытным пользователям.

Отсутствие значения в исходной таблице возможно

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

Обход #Н/Д с помощью функции ЕСЛИОШИБКА

Эта функция универсальна и позволяет вам определить, что вывести, если значение не найдено:

• Значение – результат или функция поиска
• Вывести если ошибка – что вернуть вместо сообщения об ошибке

На примере ниже мы ищем человека в списке и выводим его возраст. Если имя не найдено – будет ошибка #Н/Д:

Можно использовать ЕСЛИОШИБКА, чтобы вывести «НЕ НАЙДЕНО» вместо ошибки:

Выглядит лучше, чем в первом случае, не находите?

Перехват функцией ЕСНД

Предыдущий способ хорош, но имеет недостаток: он обрабатывает ошибки всех типов. Значит, мы можем случайно скрыть проблемы, которые должны были обработать вручную, разобраться. Получаем непредсказуемый результат. Потому, лучше использовать функцию ЕСНД. Она работает так же, как и предыдущая, но «отлавливает» лишь одну ошибку — #Н/Д:

Не выводить ничего в случае ошибки

В предыдущих двух способах мы вывели информативное сообщение, когда получили ошибку. Альтернативой этому может быть пустая ячейка вместо ошибки. Это выглядит лучше, но здесь вы не знаете наверняка, пустота – из-за ошибки, или формула поиска вернула её. Это неопределенность, но часто ею можно пренебречь. Вот два варианта, как выводить пустое значение:

Напоследок: если вам зачем-то понадобится принудительно вернуть #Н/Д, используйте функцию НД(). Не знаю, зачем это может быть нужным, я так ни разу и не воспользовался ею. Может у Вас есть идеи, для чего может пригодиться такая функция? Пишите в комментариях! Если что-то из этого материала у Вас вызвало затруднения – пишите, обязательно отвечу!