Сопоставление сообщений об ошибках и их причин в Excel

Обновлено: 21.11.2024

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

Чем больше формул вы пишете, тем больше ошибок вы столкнетесь :)

Хотя ошибки в формулах и разочаровывают, они полезны, потому что они ясно говорят вам, что что-то не так. Это намного лучше, чем не знать. Самые серьезные ошибки Excel обычно происходят из-за нормально выглядящих формул, которые незаметно возвращают неверные результаты.

Если вы столкнулись с ошибкой в формуле, не паникуйте. Сохраняйте спокойствие и методично расследуйте, пока не найдете причину. Спросите себя: «О чем мне говорит эта ошибка?» Экспериментируйте методом проб и ошибок. По мере накопления опыта вы сможете избегать многих ошибок и быстрее исправлять возникающие ошибки.

Исправление ошибок

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

<р>1. Найдите ошибки. Вы можете использовать «Перейти к специальному» > «Формула», как описано ниже.

<р>2. Проследите ошибку до ее источника. Если это сложно, попробуйте функцию трассировки ошибок.

<р>3. Выясните, что вызывает ошибку. При необходимости разбейте формулу на части.

<р>4. Исправьте ошибку в источнике.

Поиск всех ошибок

Вы можете найти все ошибки сразу с помощью Go To Special. Используйте сочетание клавиш Control + G, затем нажмите кнопку «Специальная». Excel отобразит диалоговое окно со многими параметрами, показанными ниже. Чтобы выбрать только ошибки, выберите «Формулы + ошибки», затем нажмите «ОК»:

Отслеживание ошибок

Перехват ошибок — это способ "отлавливать" ошибки, чтобы они не появлялись в первую очередь. Это имеет смысл, когда вы знаете, что вероятны определенные ошибки, и хотите, чтобы сообщения об ошибках не появлялись. Существует два основных подхода:

<р>2. Перехватите ошибку с помощью IFERROR или ISERROR. При таком подходе вы отслеживаете ошибку и предоставляете альтернативу при обнаружении ошибки. На этой странице показан пример ВПР.

<р>3. Запретить расчет до тех пор, пока не будут доступны требуемые значения. В этом случае вместо того, чтобы отслеживать ошибку, вы пытаетесь предотвратить ее появление, сначала проверяя значения. На этой странице показано несколько примеров.

Коды ошибок Excel

Есть 9 кодов ошибок, с которыми вы, вероятно, столкнетесь в какой-то момент при работе с формулами Excel. В этом разделе показаны примеры каждой ошибки формулы, а также информация и ссылки о том, как исправить ошибку.

Например, на листе ниже ошибка DIV отображается в ячейке D4, потому что ячейка C4 пуста. Пустые ячейки оцениваются Excel как ноль, а ячейка B4 не может быть разделена на ноль:

Если значение в E3 изменить на "кофе", "яйца" и т. д., функция ВПР будет работать нормально и получать стоимость товара.

Ниже функция МЕСЯЦ не может извлечь значение месяца из "яблока", так как "яблоко" не является датой:

Когда "яблоко" будет удалено из D5, формула будет работать нормально и вернет "Джо", "Сэм" и "Мэри".

Если формула настроена для фильтрации по группе "А", формула будет работать нормально:

Примеры функции ПОИСКПОЗ

Прежде чем мы начнем устранять неполадки с функцией ПОИСКПОЗ, посмотрите короткое видео, демонстрирующее, как работает эта функция. Он содержит четыре примера ПОИСКПОЗ, поэтому вы можете увидеть различные способы его использования.

Числа или текст

А теперь давайте займемся поиском и устранением неполадок с ПОИСКПОЗОМ.

Одной из частых причин ошибки функции ПОИСКПОЗ является попытка сопоставления:

настоящие цифры в одном месте
напишите «числа» в другом месте

Excel воспринимает их как совершенно разные вещи, даже если они выглядят одинаково на листе, поэтому они не "СООТВЕТСТВУЮТ".

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

Хронология видео

00:00 Введение
00:23 Ошибка ПОИСКПОЗ
00:58 Число или текст
01:46 Исправить ошибку
02:21 Минус
02:51 Другие формулы
03:31 Сопоставление чисел с текстом
04:34 Получить рабочую тетрадь

Ненастоящие числа

Например, в вашей таблице поиска могут быть «числа», которые на самом деле являются текстом.

Как узнать?

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

Ошибка функции ПОИСКПОЗ

На этом снимке экрана желтые ячейки содержат текст, а синие — действительные числа.

Формула ПОИСКПОЗ в ячейке G9 дает ошибку, поскольку действительное число в ячейке F9 не соответствует текстовому числу 123 в таблице поиска, ячейке G4.

Исправить данные

Один из способов решить эту проблему — изменить текстовые числа на действительные числа, если вы можете это сделать.

В этом видео показан один из способов преобразования текста в числа — использование специальной вставки

Изменить формулу ПОИСКПОЗ

Если вы не можете преобразовать данные в действительные числа, можно изменить формулу ПОИСКПОЗ.

Вот исходная формула ПОИСКПОЗ в ячейке G9, которая возвращает ошибку.

Чтобы решить эту проблему, добавьте в формулу пустую строку ("") после искомого значения. Это изменяет реальное число на текстовое число, поэтому оно найдет совпадение в таблице поиска.

Вот формула в ячейке G10, где F10 – число, и формула ПОИСКПОЗ работает правильно:

ПРИМЕЧАНИЕ. Измененная формула также работает для ячеек, содержащих текстовые числа, например в ячейке F11.

Таблица поиска с действительными числами

Как можно решить противоположную проблему — таблицу поиска с реальными числами и текстовое число для поиска?

Вот пример с числами в синих ячейках и текстом в желтых ячейках.

Формула ПОИСКПОЗ в ячейке B9 возвращает ошибку, поскольку в ячейке A9 есть текст, а в таблице поиска есть числа (B4:B7).

Исправить формулу ПОИСКПОЗ для чисел

Чтобы решить эту проблему, введите в формулу два знака минус перед искомым значением. Это изменяет текстовое число на реальное число, чтобы Excel мог найти совпадение.

Вот формула в ячейке B10, где A10 — текст, и функция ПОИСКПОЗ работает правильно:

ПРИМЕЧАНИЕ. Измененная формула также работает для ячеек, содержащих действительные числа, таких как ячейка F11.

Еще одна проблема с MATCH

Если числа не являются проблемой для вашей функции ПОИСКПОЗ, проверьте наличие лишних пробелов:

Одно из значений может содержать начальные пробелы (либо конечные, либо встроенные пробелы)
Другое значение не содержит этих пробелов

Если проблема в этом и вы не можете удалить пробелы, используйте функцию ОБРЕЗ с ПОИСКПОЗОМ. Это удалит все начальные, конечные и повторяющиеся пробелы.

Это устранило проблему в ячейке B8, где название элемента в ячейке A8 имело лишний пробел между словами.

Получить образец файла

Чтобы получить образец книги для функции ПОИСКПОЗ и еще один совет по устранению неполадок ПОИСКПОЗ, перейдите на страницу ИНДЕКС и ПОИСКПОЗ на моем сайте Contextures.

В разделе «Скачать» получите первый файл — INDEX/MATCH Examples. Рабочая книга имеет формат xlsx и не содержит макросов

Примеры устранения ошибок функции ПОИСКПОЗ в Excel

Одна мысль о «Примеры устранения неполадок функции ПОИСКПОЗ в Excel»

Вау, наконец-то нашел простое решение. Отличная статья, большое-большое спасибо!

Оставить ответ Отменить ответ

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel для Windows Phone 10 Более. Меньше

Проблема: нет данных для сопоставления

Если вы считаете, что данные есть в электронной таблице, но ПОИСКПОЗ не может их найти, это может быть связано с тем, что:

В ячейке есть неожиданные символы или скрытые пробелы.

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

РЕШЕНИЕ. Чтобы удалить неверные символы или скрытые пробелы, используйте функции ОЧИСТИТЬ или ОБРЕЗАТЬ соответственно. Кроме того, убедитесь, что ячейки отформатированы как правильные типы данных.

Вы использовали формулу массива, не нажимая Ctrl+Shift+Enter

При использовании массива в ИНДЕКС, ПОИСКПОЗ или комбинации этих двух функций необходимо нажать Ctrl+Shift+Enter на клавиатуре. Excel автоматически заключит формулу в фигурные скобки <>. Если вы попытаетесь ввести скобки самостоятельно, Excel отобразит формулу в виде текста.

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

Проблема: несоответствие типа соответствия и порядка сортировки данных

Если match_type равно 1 или не указано, значения в lookup_array должны быть в возрастающем порядке. Например, -2, -1, 0 , 1 , 2…, A, B, C…, FALSE, TRUE и многие другие.

Если match_type равно -1, значения в lookup_array должны быть в порядке убывания.

В следующем примере функция ПОИСКПОЗ

=ПОИСКПОЗ(40,B2:B10,-1)

РЕШЕНИЕ. Либо измените аргумент match_type на 1, либо отсортируйте таблицу по убыванию. Затем повторите попытку.

Нужна дополнительная помощь?

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше

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

Решение. Либо убедитесь, что искомое значение существует в исходных данных, либо используйте в формуле обработчик ошибок, например ЕСЛИОШИБКА. Например, =ЕСЛИОШИБКА(ФОРМУЛА(),0), что означает:

=ЕСЛИ(ваша формула дает ошибку, затем отображается 0, в противном случае отображается результат формулы)

Вы можете использовать "", чтобы ничего не отображать, или заменить свой собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),"Сообщение об ошибке")

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

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

Значение поиска и исходные данные — это разные типы данных. Например, вы пытаетесь использовать функцию ВПР для ссылки на число, но исходные данные сохраняются в виде текста.

Решение. Убедитесь, что типы данных совпадают. Вы можете проверить форматы ячеек, выбрав ячейку или диапазон ячеек, затем щелкните правой кнопкой мыши и выберите «Формат ячеек» > «Число» (или нажмите Ctrl+1) и при необходимости измените числовой формат.

Совет. Если вам нужно принудительно изменить формат для всего столбца, сначала примените нужный формат, затем вы можете использовать Данные > Текст в столбцах > Готово.

Вы можете использовать функцию TRIM, чтобы удалить все начальные или конечные пробелы. В следующем примере используется функция TRIM, вложенная в функцию ВПР, чтобы удалить начальные пробелы из имен в A2:A7 и вернуть название отдела.

, и его необходимо вводить с помощью CTRL+SHIFT+ENTER." />

Примечание. 24 сентября 2018 г. — Формулы динамического массива. Если у вас установлена текущая версия Microsoft 365 и вы находитесь на канале выпуска Insiders Fast, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона. , затем нажмите Enter, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите Ctrl+Shift+Enter для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента match_type, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, установите для аргумента match_type значение 0 (ноль).

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

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

В этом примере ячейка E2 содержит ссылки на несовпадающие диапазоны:

Чтобы формула вычислялась правильно, ее необходимо изменить так, чтобы оба диапазона отражали строки 2–11.

Чтобы исправить это, проверьте синтаксис формулы используемой функции и введите все необходимые аргументы в формулу, которая возвращает ошибку. Это может потребовать входа в редактор Visual Basic (VBE) для проверки функции. Вы можете получить доступ к VBE на вкладке "Разработчик" или с помощью ALT+F11.

Чтобы исправить это, убедитесь, что рабочая книга, содержащая пользовательскую функцию, открыта и что функция работает правильно.

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

Вы редактируете защищенный файл, содержащий такие функции, как CELL, и содержимое ячеек превращается в ошибки N/A

Чтобы это исправить, нажмите Ctrl+Atl+F9, чтобы пересчитать лист

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

Excel автоматически загрузит мастер:

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

Нужна дополнительная помощь?

Читайте также: