Смысл сообщения об ошибке знач

При работе в Excel вы можете столкнуться с ошибкой #ЗНАЧ. В этой статье мы разберемся с несколькими ситуациями, которые приводят к этой ошибке и как ее исправить.

Ситуация 1. Формула СУММЕСЛИ на другой файл

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

Когда оба файла открыты, то все работает отлично, но как только вы закроете их оба и откроете только файл, в котором вы суммировали итоги продаж, то получите ошибку #ЗНАЧ. Это связано с особенностями работы Excel, которые надо знать, и вообще есть несколько формул, которые приведут к ошибке #ЗНАЧ при ссылке на внешний файл:

• СУММЕСЛИ()
• СУММЕСЛИМН()
• СЧЁТЕСЛИ()
• СЧЁТЕСЛИМН()
• СЧИТАТЬПУСТОТЫ()
• СМЕЩ()
• ДВССЫЛ()

Какие есть пути решения ситуации? Первый: перенести базу данных в файл, где формируются итоги, тогда ссылок на внешние книги не будет и ошибка пропадет. Второй: Использовать формулы СУММ() и ЕСЛИ() совместно в массиве. Это несколько сложно для новичков, поэтому на этом способе останавливаться подробно не буду. Третий: Использовать формулу ЕСЛИОШИБКА и прописать в случае ошибки напоминание, что необходимо открыть файл с базой данных для корректной работы. Четвертый: Просто открыть файл, на который ссылается формула и ошибка пропадет

Ситуация 2. Вычитание/сложение дат

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

 Решение проблемы следующее: Выделите ячейки с датами и замените в них пробелы на «ничего». После этого ошибка исчезнет.

Ситуация 3. Лишние пробелы в числах

При копировании данных из различных источников, может сложиться ситуация, что в числах будут лишние пробелы, из-за них арифметические операции с числами будут невозможны и приведут к ошибке #ЗНАЧ. Решение проблемы, как и в ситуации 2 — замените пробелы на «ничего» и проблема пропадет.

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

Спасибо за внимание.

ЗНАЧ в Excel — как исправить? В зависимости от причины требуется исправление проблемы с вычитанием, пробелами и текстом. Может потребоваться определение источника ошибка, замена «ЗНАЧ» другим значением, проверка подключение данных или использование помощи представителей форума сообщества. Ниже рассмотрим, что значите появление такой ошибки, чем она может быть вызвана, и каким способом ее можно устранить.

Что значит и когда возникает

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

• вычитание;
• пробелы или текст;
• появление ошибки ввода;
• неправильные значения и т. д.

Если разобраться, что означает в Экселе ЗНАЧ, можно быстрее справиться с проблемой и устранить ее. В большинстве случаев для решения задачи может потребоваться применение сразу нескольких решений, необходимых для устранения указанной ошибки в Excel.

Как исправить

Теперь разберемся подробнее, как исправить в Эксель ЗНАЧ в формуле, когда не удается сделать вычисление в обычном режиме. Рассмотрим разные проблемы и методы решения.

Вычитание

Наиболее распространенная ситуация, когда ошибка «ЗНАЧ» в Excel возникает из-за трудностей с вычитанием. Чтобы исправить сбой, могут помочь рассмотренные ниже шаги.

Для начала проверьте правильность ввода формулы вычитания. Сделайте следующие шаги, чтобы исправить ситуацию в Excel:

• Вычтите одну ссылку на ячейку из другой. Введите два параметра в 2-х отдельных секциях, а в третьей вычтите одну ссылку на ячейку из другой. К примеру, это может быть =A2-B2.

• Как вариант, используйте опцию «СУММ» с цифрами выше / ниже нуля. Для этого введите параметр выше нуля в одной, а ниже — в другой ячейке. В третьей применяется опция «СУММ» для складывания. Пример: =СУММ (D5;E5).

Еще один способ, как убрать ЗНАЧ в Excel — ввести правильную функцию вычитания. Алгоритм действий такой:

1. Сделайте проверку. Для этого в новом файле введите «3» в А1 и 6 в В1. Если при формуле =В1-А1 появляется ЗНАЧ, переходите к следующему шагу.
2. Войди на Виндовс в раздел «Региональные стандарты». В «десятке» в поисковой строке введите «регион», а после выберите панель управления «Региональные стандарты».
3. На вкладке «Форматы» кликните на «Дополнительные параметры» и найдите «Разделитель элементов списка». При наличии здесь знака «минус» поменяйте его на другой элемент и жмите ОК.
4. Войдите в книгу Excel. Если в ячейке имеется надпись #VALUE!, два раза жмите на нее для внесения правок. В случае, когда вместо «минуса» стоит запятые, замените их на «минус». Далее жмите «Ввод» и повторите эти шаги для других ячеек, для которых возникла ошибка.

Распространенная причина, почему в Экселе выходит ЗНАЧ — неправильные операции с датами. Для решения вопроса сделайте следующее:

1. Вычтите одну ссылку на ячейку из второй.
2. Используйте опцию РАЗНДАТ для 3-ей ячейке, что позволяет отыскать разницу в датах.

Следующий способ, как исправить ЗНАЧ в Excel — убедиться, что программа распознает текст, как дату для проведения проверки. Чтобы исправить сбой, сделайте следующее:

• Два раза жмите на дату, используемую в формуле.
• Разместите курсор на старте и убедитесь, можно ли выбрать один или более пробелов.
• Выделите столбец с датой, нажав на заголовок.
• Кликните на «Данные», а потом — «Текст по столбцам».

• Два раза жмите «Далее».
• На третьем шаге в секции «Формат данных столбца» и переместите переключатель «Дата».
• Выберите формат даты и кликните «Готово».
• Повторите эти шаги для разных столбцов, чтобы убедиться в них нет пробелов возле даты.

Пробелы и текст

Еще одно объяснение, что значит ЗНАЧ в Excel — проблемы с текстами / пробелом. В таком случае воспользуйтесь одним из рассмотренных ниже шагов.

Для начала удалите пробелы, из-за которых возможно появление ошибки ЗНАЧ. К примеру, формула направляет на ячейки, где имеются видимые или скрытые пробелы, как результат, ячейка кажется пустой.

Сделайте следующее в Excel:

• Выделите ячейки, куда направляют ссылки.
• Перейдите в раздел «Главная», а поле жмите «Найти и выделить».
• Кликните «Заменить».
• В поле «Найти» введите пробел, а в поле «Заменить» удалите все, что там имеется.

• Замените одно / все вхождения.
• Если ЗНАЧ не удалось исправить, включите фильтр в разделе «Главная», а далее «Сортировка и фильтр» — «Фильтр».
• Жмите стрелку внизу и удалите флажок с поля «Выделить все», а после установите отметку «Пустые».
• Поставьте флажки в местах без названия.
• Выделите незаполненные ячейки и удалите их.
• Кликните на стрелку вниз и выберите «Удалить фильтр из…».

Если ошибка ЗНАЧ появлялась из-за пробелов, рассмотренным методом удается исправить ситуацию. В ином случае может потребоваться применение другого варианта.

Причиной, почему вы видите ЗНАЧ в Эксель, может быть текст / специальные знаки. Обратите внимание на ячейки и удалите лишнее. Может потребоваться применение функции «Заменить» или «ПЕЧСИМВ» или «ЗАМЕНИТЬ».

Еще один способ, как исправить, если в Excel вместо цифр ЗНАЧ — использовать формулы вместо операций. При наличии «+» или «*» рассчитать ячейки с текстом / пробелом не получится. Для решения проблемы может потребоваться применение специальных функций. К примеру, вместо сложной формулы =А2+В2+С2 укажите =СУММ (А2:С2).

Дополнительные шаги

Рассматривая пути, как исправить ошибку ЗНАЧ в Excel, можно использовать и другие пути решения проблемы:

• Определите источник ошибки. Для начала выделите секцию, где имеется параметр ЗНАЧ, а после этого жмите на «Формулы», «Вычислить формулы», а после — «Вычислить». В этом случае Эксель обрабатывает каждую часть формулы отдельно. При этом можно увидеть, где проблема, а после исправить ее.

• Замените ЗНАЧ другим параметром Excel, чтобы исправить проблему. Попробуйте добавить формулу ЕСЛИОШИБКА, которая проверят наличие сбоев и заменяет ЗНАЧ другим значением, к примеру, двумя дефисами.
• Восстановите подключение. Попросите создателя файла Excel сделать новую книгу, чтобы в ней были только параметры без подключений. Для этого копируйте ячейки и вставьте только значения. Это происходит следующим образом — «Главная», а потом «Вставить» и «Специальная вставка» — «Значения».

Как вариант, можно зайти в сообщество Excel по ссылке go.microsoft.com/fwlink/?linkid=827514, где можно задать вопрос и исправить проблему с Excel.

Теперь вы знаете, почему в Экселе пишет ЗНАЧ и не считает формулу, как исправить ситуацию, и какие шаги необходимо осуществлять в первую очередь. Более подробные сведения можно найти на официальном сайте по ссылке support.microsoft.com/ru-ru/office/исправление-ошибки-знач-ошибка-15e1b616-fbf2-4147-9c0b-0a11a20e409e.

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

Отличного Вам дня!

Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

1. При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter.

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

Данная ошибка Excel возникает при делении на ноль, то есть когда в качестве делителя используется ссылка на ячейку, которая содержит нулевое значение, или ссылка на пустую ячейку.

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

Устранение ошибки: задайте правильный аргумент искомое значение.

1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

1. Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:

Ошибки в Excel – Ошибки в формуле массива

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

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

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

1. Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.

Содержание

• Как исправить ошибку #ПЕРЕНОС! в Excel
• Как исправить ошибку #ЗНАЧ! в Excel
• Как исправить ошибку #ПУСТО! в Excel
• Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

У ошибки может быть множество причин. Поиск решения будет зависеть от используемой версии Excel.

• Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
• Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

1. нажмите на любую ячейку в таблице,
2. в меню в верхней части окна выберите «Конструктор»,
3. выберите команду «Преобразовать в диапазон».

Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

• что-то не так с ячейкой (ячейками), на которую ссылается формула,
• что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

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

Решение: использовать вместо формулы (уравнения, составленного пользователем) функцию (формулу, заранее заданную Excel). Функции по умолчанию игнорируют большую часть текстовых значений и производят расчеты лишь с числами.

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Скриншот: Zapier.com

Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

1. выделите диапазон ячеек, к которому обращается формула;
2. нажмите на иконку с биноклем, нажмите «Найти и выделить»;
3. в окне «Найти и заменить» выберите «Заменить»;
4. в поле «Найти» вставьте пробелы;
5. поле «Заменить на» оставьте пустым;
6. нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.

Ячейка, на которую ссылается формула, удалена

Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.

Решение 1: Если ячейки удалены случайно, отмените это действие.

Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.

Формула содержит относительные ссылки

Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.

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

Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.

Решение: обновите формулу, чтобы включить в нее абсолютные значения. Это позволит созранить исходные ссылки, даже если формула будет перенесена. Для этого вставьте символ $ перед каждой буквой, обозначающей столбец, и номером ряда.

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

Название формулы содержит опечатку

Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

Решение: найти правильное имя формулы и обновить ее.

Вот как это сделать:

1. выберите в меню раздел «Формулы»;
2. нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
3. выберите и вставьте нужную функцию;
4. в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Источник.

Фото на обложке: Aihr.com

Обзор ошибок, возникающих в формулах Excel

​Смотрите также​ за месяц.​ выложить его из​ «Результативность» открыть файл​ новых позиции, (которых​»»​ Создать правило… );​​Скрыть значения ошибки можно​​Решение:​.​В этом разделе приводятся​#ЗНАЧ!​ которая при вычислении​ не имеют точек​.Например, на рисунке ниже​ на ноль. Это​

Несоответствие открывающих и закрывающих скобок

​ исправить формулу самостоятельно.​Ошибки в Excel возникают​CapMorgan​ дома после 19:00​ АСАО ПАБ, то​ до этого никогда​, вы тем самым​в появившемся окне выберите​ используя пользовательский формат, Условное​Удалите аргумент​Если функции не удается​

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

​: Господа, удалил все​ (мск).​ все формулы сразу​ не было) автоматически​ как-то влияте на​ Форматировать только ячейки,​ форматирование или значение​нач_позиция​ найти искомый текст​ распространенных случаях возникновения​ распространенных ошибок, встречающихся​ и не может​#ПУСТО!​ массив C4:C11 больше,​

Ячейка заполнена знаками решетки

​ явное деление на​ слепо полагаться на​ наверняка, замечали странные​ лишнее, размещаю файлы​Borisovich​

1. 1. ​ же рассчитываются корректно.​ попадали в первую​ тип данных в​ которые содержат;​ Пустой текст («»).​, если он не​

      ​ в указанной строке,​ ошибки #ЗНАЧ! в​

1. ​ в Excel. Она​ вычислить результат. Ярким​.​ чем аргументы массива​ ноль, так и​ это исправление ни​ значения в ячейках,​ для предметного обсуждения.​: У меня такая​ Подскажите пожалуйста, как​ колонку листа «Итого»,​

​ ячейках и типы​в выпадающем списке выберите​Пользовательский формат вводим через​

Ошибка #ДЕЛ/0!

​ нужен, или присвойте​​ она выводит ошибку​​ функциях НАЙТИ, НАЙТИБ,​ возникает, когда значение​ примером таких функций​Также данная ошибка возникнет,​ A4:A8 и B4:B8.​ деление на ячейку,​ в коем случае​ вместо ожидаемого результата,​JayBhagavan​

Ошибка #Н/Д

​ ошибка возникает при​​ можно уйти от​​ также из 6-ой​ данных в ячейках​ Ошибки (см. рисунок​ диалоговое окно Формат​ ему правильное значение.​​ #ЗНАЧ!.​​ ПОИСК и ПОИСКБ.​

1. ​ одного из аргументов​ в Excel являются​ если случайно опустить​​Нажав комбинацию клавиш​​ которая содержит ноль​ нельзя. Например, на​​ которые начинались со​​: Предлагаю заменить СЧЁТЗ()​ разных форматах файлов​
2. ​ данной проблемы? Всегда​ колонки всех листов,​ становятся​ ниже);​​ ячеек (см. файл​​Например, рассмотрим следующую функцию:​
3. ​Например, рассмотрим следующую функцию:​Функции НАЙТИ и ПОИСК​ формулы или функции​СТАВКА​ один из операторов​Ctrl+Shift+Enter​ или пуста.​ следующем рисунке Excel​​ знака​​ для одной ячейки​ xls и xlsx​ ли необходимо открывать​ в колонку 2​Текстовый​

   

   ​выберите пользовательский формат (белый​​ примера).​​=НАЙТИ(«и»;»Функции и формулы»;25)​

   

Ошибка #ИМЯ?

​=НАЙТИ(«перчатки»;»Перчатки (детские)»;1)​​ очень похожи. Они​​ содержит недопустимые значения.​и​ в формуле. К​, получим следующий результат:​

1. ​Ошибка​ предложил нам неправильное​#​
2. ​ на, например: =—(ДЛСТР(‘[АСАО​ — проверьте​ файл АСАО? Можно​ листа «Итого». И​

​(хотя у вас​ цвет шрифта);​

1. ​для вызова окна Формат​Она ищет букву «и»​
2. ​Она выведет ошибку #ЗНАЧ!,​ работают одинаково: находят​

Ошибка #ПУСТО!

​ Самые распространенные случаи​​ВСД​​ примеру, формулу​Ошибка​#Н/Д​ решение.​

1. ​. Это говорит о​​ ПАБ цех №53(2).xlsx]Январь’!$E$5)>0)Работает​​JayBhagavan​ ли как-то этого​ при этом существующая​ установлен для ячеек​Теперь ошибочные значения отображаются​ ячеек нажмите​ в строке «Функции​ так как в​​ символ или текстовую​​ возникновения ошибки​
2. ​.​=А1*А2*А3​#ИМЯ?​возникает, когда для​Бывают случаи, когда ячейка​​ том, что формула​​ с закрытым файлом,​​: CapMorgan, почитайте .​​ избежать, скажем, изменив​

Ошибка #ЧИСЛО!

​ формула добавления количество,​​ тип данных​​ белым шрифтом и​CTRL+1​ и формулы» (​

1. ​ строке нет слова​ строку в другой​#ЗНАЧ!​Ошибка​записать как​возникает, когда в​
2. ​ формулы или функции​​ в Excel полностью​​ возвращает ошибку. Чтобы​ на который ссылается.​ЗЫ На почту​ формулы или выполнив​​ добавляла данное количество.​​Числовой​ не видны на​

​;​просматриваемая_строка​ «перчатки», но есть​ текстовой строке. Различие​

1. ​:​#ССЫЛКА!​​=А1*А2 A3​​ формуле присутствует имя,​ недоступно какое-то значение.​ заполнена знаками решетки.​ избавиться от ошибки,​JayBhagavan​ не надо. Спасибо.​ некие преобразования? Буду​​ В примере, новые​​), а вычисления над​​ стандартном белом фоне.​​выберите (все форматы).​

Ошибка #ССЫЛКА!

​) начиная с 25-го​​ слово «Перчатки». Помните,​​ между ними в​Формула пытается применить стандартные​возникает в Excel,​.​ которое Excel не​

1. ​ Приведем несколько случаев​ Это означает один​ Вы должны исправить​: Для столбцов BW,​

   ​CapMorgan​ Вам очень признателен​​ позиции во втором​​ текстом производить нельзя.​

   

2. ​ Если фон поменять,​в поле Тип введите​ символа (​ что функция НАЙТИ​ том, что функция​ математические операторы к​

   ​ когда формула ссылается​Ошибка​ понимает.​ возникновения ошибки​ из двух вариантов:​​ ее причину, а​​ BX предлагаю использовать​: Проверил, оба файла​ за помощь. Файлы​

   

Ошибка #ЗНАЧ!

​ листе — откуда,​​ Поэтому замените​​ то ошибки станут​ формат [Черный]Основной​нач_позиция​ учитывает регистр, поэтому​ НАЙТИ учитывает регистр,​ тексту.​ на ячейку, которая​#ЧИСЛО!​Например, используется текст не​​#Н/Д​​Столбец недостаточно широк для​

1. ​ они могут быть​ такую конструкцию: =—(‘[АСАО​ формата .xlsx.​
2. ​ прикрепить не удается​ и в листе​»»​ снова видны. Даже​нажмите ОК​​), но возвращает ошибку​​ значение аргумента​ а функция ПОИСК —​
3. ​В качестве аргументов функции​ не существует или​возникает, когда проблема​ заключенный в двойные​:​ отображения всего содержимого​ самыми разными.​​ ПАБ цех №53(2).xlsx]Январь’!$I$2=BW$2)​​The_Prist​

​ из-за их веса​ Итого — куда,​на​ если просто выделить​цвет шрифта ячейки установите​ #ЗНАЧ!, так как​искомый_текст​ нет. Поэтому если​

​ используются данные несоответствующего​

office-guru.ru

Исправление ошибки #ЗНАЧ! в функциях НАЙТИ, НАЙТИБ, ПОИСК и ПОИСКБ

​ удалена.​ в формуле связана​ кавычки:​Функция поиска не находит​ ячейки. Для решения​Самым распространенным примером возникновения​

Некоторые важные сведения о функциях НАЙТИ и ПОИСК

• ​CapMorgan​: Вы явно используете​ (архивом вестят 340​ отмеченные желтым цветом.​0​ диапазон, то ошибки​ таким же как​ в строке всего​должно иметь точное​ при поиске нужно​ типа. К примеру,​Например, на рисунке ниже​ со значением.​Функция ссылается на имя​ соответствия. К примеру,​

• ​ проблемы достаточно увеличить​ ошибок в формулах​: Бегло поупражнялся, «=—(‘[АСАО​ формулы, который не​ Кб). Могу отправить,​CapMorgan​.​ будут слегка видны.​ и ее фон​ 22 знака.​ совпадение в строке,​ сопоставлять символы без​

• ​ номер столбца в​​ представлена формула, которая​​Например, задано отрицательное значение​ диапазона, которое не​​ функция​ ширину столбца, чтобы​ Excel является несоответствие​ ПАБ цех №53(2).xlsx]Январь’!$I$2=BW$2)»​​ работают с закрытыми​

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

​ скажите куда? Спасибо.​: Уважаемые сограждане, доброго​Код =ЕСЛИ(ЕОШИБКА(C4+E4-F4);0;C4+E4-F4) в​Замечательным свойством значения Пустой​ (обычно белый) ​

​Совет:​

• ​ указанной в аргументе​

​ учета регистра, используйте​ функции​ суммирует значения двух​ там, где должно​ существует или написано​ВПР​ все данные отобразились…​ открывающих и закрывающих​​ сработала. Я правильно​​ книгами. СУММЕСЛИ, СЧЁТЕСЛИ,​JayBhagavan​ времени суток!​​ 2003-м,​​ текст является, то​

​На рисунке ниже пользовательский​  Чтобы определить общее​просматриваемый_текст​ функцию ПОИСК.​ВПР​

• ​ ячеек.​

​ быть положительное. Яркий​​ с опечаткой:​при точном поиске​

Проблема: значение аргумента нач_позиция равно нулю (0)

​…или изменить числовой формат​​ скобок. Когда пользователь​​ предполагаю, что если​ ДВССЫЛ и т.п.​: На файлообменник, например,​Обращаюсь к Вам​в 2007 Код​ что оно не​ формат применен к​ количество символов в​

​.​​Если вам нужно вернуть​​задан числом меньше​​Если удалить столбец B,​ пример – квадратный​В данном примере имя​

Проблема: длина значения нач_позиция превышает длину значения просматриваемый_текст

​ вернет ошибку​

• ​ ячейки.​

​ вводит формулу, Excel​ мне по образу​ Если знать сами​​ мэйл.ру.​​ за помощью. Уверен,​ =ЕСЛИОШИБКА(C4+E4-F4);0)Огромное Вам спасибо,​​ отображается в ячейке​​ ячейкам​ текстовой строке, используйте​Однако функция ПОИСК вернет​ строку, содержащую указанное​

​ 1.​​ формула вернет ошибку​ корень из отрицательного​ диапазон не определено.​#Н/Д​

​В ячейке содержится формула,​​ автоматически проверяет ее​ и подобию сделать​

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

​ формулы и цель​alexthegreat​

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

​ у Вас есть​ оба варианта работают.​ (например, введите формулу​А6B6​ функцию ДЛСТР.​ в этом случае​ количество символов, используйте​

support.office.com

Скрытие в MS EXCEL ошибки в ячейке

​Аргумент функции должен иметь​#ССЫЛКА!​ числа.​Адрес указан без разделяющего​, если соответствий не​

​ которая возвращает некорректное​ синтаксис и не​ для категории «Специалисты»​ их применения можно​

Пользовательский формат

​: Можно открыть оба​ чёткое понимание решения​В данной таблице​ =»»). Записав, например,​

• ​. Как видно, на​Решение:​​ значение 1, так​​ функцию ПСТР вместе​
• ​ единственное значение, а​
• ​.​К тому же, ошибка​
• ​ двоеточия:​
• ​ найдено.​ значение даты или​ даст закончить ввод,​ и «ТОП-менеджеры», то​

​ заменить другими(например СУММЕСЛИ​ файла, (если Вам​ моей трудности. Дело​​ я не учел​​ в ячейке​ значения, которые не​Исправьте начальную позицию​ как она не​

​ с функцией НАЙТИ.​ вместо этого ему​Еще пример. Формула в​#ЧИСЛО!​В имени функции допущена​Формула прямо или косвенно​ времени. Думаю, Вы​

Условное форматирование

​ пока в ней​ будет считать корректно?​ и СЧЁТЕСЛИ можно​

• ​ формулы не нужны​
• ​ в следующем: имеется​ еще один момент,​А14​
• ​ являются ошибкой, форматирование​ необходимым образом.​ учитывает регистр:​
• ​ Сведения об использовании​ присваивают целый диапазон.​ ячейке B2 ссылается​
• ​возникает, когда возвращается​ опечатка:​

​ обращается к ячейке,​ знаете, что Excel​ присутствует синтаксическая ошибка.​Спасибо,​ заменить на СУММПРОИЗВ).​ будут и связь​ 2 файла и​ это когда поступают​формулу =ЕСЛИОШИБКА(1/0;»») получим​ не повлияло.​

Значение Пустой текст («»)

​Задать вопрос на форуме​=ПОИСК(«перчатки»;»Перчатки (детские)»;1)​ функций ПСТР и​ На рисунке ниже​ на ячейку B1,​ слишком большое или​Ошибка​​ в которой отображается​​ не поддерживает даты​Например, на рисунке выше​​JayBhagavan​​CapMorgan​

excel2.ru

Как убрать #ЗНАЧ!

​ не нужна) в​​ в одном из​
​ новые позиции, а​ пустую ячейку​Применение вышеуказанного формата не​

​ сообщества, посвященном Excel​​Решение:​​ НАЙТИ в сочетании​​ в качестве искомого​ т.е. на ячейку,​​ слишком малое значение.​​#ПУСТО!​ значение​ до 1900 года.​ мы намеренно пропустили​: CapMorgan, чего предполагать​: Все верно, а​​ зависимом файле выделить​​ них (АСАО ПАБ​ заводить дважды в​А14​​ влияет на вычисления.​​У вас есть предложения​Исправьте синтаксис необходимым​ друг с другом​​ значения функции​​ расположенную выше на​​ Например, формула​​возникает, когда задано​

​#Н/Д​ Поэтому, если результатом​
​ закрывающую скобку при​ то — пробуйте.​ именно «СЧЁТЕСЛИ». Эту​
​ весь лист -​ цех №53 Результативность)​ таблицу это будет​.​ В Строке формул,​ по улучшению следующей​ образом.​ и примеры приведены​ВПР​ 1 строку.​=1000^1000​
​ пересечение двух диапазонов,​.​ формулы оказывается такая​ вводе формулы. Если​ Сделайте резервную копию​ формул применил, так​ Копировать — Спец.​ есть ссылки/связи на​ неудобно, так как​vasilyev​ по-прежнему, будет отображаться​ версии Excel? Если​Аргумент​ в разделе справки​используется диапазон A6:A8.​Если мы скопируем данную​вернет как раз​ не имеющих общих​При работе с массивами​ дата, то Excel​ нажать клавишу​ исходного файла и​ как необходимо скалькулировать​ вставка — Вставить​

CyberForum.ru

Исправление ошибки «ЗНАЧ#» при использовании связей файлов Excel

​ другой файл (АСАО​​ нужно еще понимать,​: Доброе время суток)​
​ формула, хотя в​ да, ознакомьтесь с​нач_позиция​ по функции НАЙТИ.​Вот и все! Мы​ формулу в любую​ эту ошибку.​ точек.​ в Excel, когда​ возвращает подобный результат.​Enter​ смело на нём​ сколько было проведено​ только значения.​ ПАБ цех №53).​ где новая позиция,​Подскажите пожалуйста как​ ячейке ничего не​ темами на портале​является необязательным. Если​Синтаксис этих функций одинаков:​ разобрали типичные ситуации​ ячейку 1-й строки​Не забывайте, что Excel​Например,​ аргументы массива имеют​В данном случае увеличение​, Excel выдаст следующее​ экспериментируйте.​ аудитов работниками той​CapMorgan​ Проблема в том,​ а где старая.​ убрать в строках​ будет отображаться.​ пользовательских предложений для​ его опустить, используется​искомый_текст; просматриваемый_текст; [нач_позиция]​ возникновения ошибок в​ (например, ячейку D1),​

​ поддерживает числовые величины​​=А1:А10 C5:E5​ меньший размер, чем​

​ ширины столбца уже​​ предупреждение:​CapMorgan​ или иной категории​: Спасибо. С рабочего​ что когда я​Как сделать так​ #ЗНАЧ!​Используя Условное форматирование, также​ Excel.​ значение по умолчанию,​

​). Обычным языком это​​ Excel. Зная причину​ формула вернет ошибку​ от -1Е-307 до​– это формула,​ результирующий массив. В​ не поможет.​В некоторых случаях Excel​: , внес правки​ служащих. Если категория​

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

​ равное 1. Однако​​ можно выразить так:​
​ ошибки, гораздо проще​#ССЫЛКА!​

​ 1Е+307.​​ использующая оператор пересечения,​ этом случае в​

​Ошибка​​ предлагает свой вариант​ в Файл, все​ «Руководитель подразделения», то​ могу. Может быть​ формулы в столбцах​ листов (от 1​:​ же результата.​ ячейке значения ошибки:​ если этот аргумент​что нужно найти; где​

​ исправить ее. Успехов​​, т.к. в ней​Еще одним случаем возникновения​ которая должна вернуть​ незадействованных ячейках итогового​#ДЕЛ/0!​ исправления ошибки. Вы​ заработало! Огромное, большущее​ нужно в «Результативности»​ уместно, если я​ «BW», «BX», «BY»,​ листа до 31​vasilyev​выделите интересующий диапазон;​

​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​​ указан и его​ это нужно найти;​ Вам в изучении​

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

​ вернуть «1», а​​ Вам направлю его​ возвращают «ЗНАЧ#». Если​ листа) с 5-ой​, видимо вставляя пустую​

​в меню выберите Главная/​​ #ЧИСЛО!, #ИМЯ? Сделаем​ значение равно 0,​ с какой позиции​ Excel!​ на несуществующую ячейку.​#ЧИСЛО!​ на пересечении двух​#Н/Д​ Excel происходит деление​
​ с Excel, либо​

​ помощь!!!!!!​​ потом посчитать итого​ эл.почтой? Или могу​ при открытом файле​ колонки при добавлении​ строку​ Стили/ Условное форматирование/​

​ это несколькими способами.​​ возникнет ошибка #ЗНАЧ!.​ следует начать​Автор: Антон Андронов​Ошибка​является употребление функции,​

planetaexcel.ru

​ диапазонов. Поскольку диапазоны​

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

.

В этой статье Вы найдёте простые объяснения ошибок #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))

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

В большинстве случаев, 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;ЛОЖЬ)

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

Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.

Трудно представить ситуацию, когда кто-то вводит значение меньше 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;ЛОЖЬ))

Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.ablebits.com/office-addins-blog/2014/08/27/excel-vlookup-not-working/ Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel