Что это такое в Excel

Обновлено: 20.11.2024

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

Введение

Значения ошибок Excel

Вот список встроенных значений ошибок формулы Excel. Microsoft называет их хеш-ошибками из-за символа в начале.

Ниже приведено краткое примечание о каждой хеш-ошибке и ссылка на соответствующую страницу справки Microsoft,

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

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

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

Значение. Делитель равен нулю, а Excel не может делить на ноль.

Значение. Формула ссылается на недопустимую ячейку. Возможно, столбец или строка удалены, или диапазон ВПР содержит 3 столбца, а вы запрашиваете результат из столбца 4.

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

Значение. Формула относится к несуществующему диапазону. Часто вызывается оператором пересечения (символом пробела)

Создать собственные хеш-ошибки

Вместо встроенных хеш-ошибок Excel вы можете создавать свои собственные хеш-ошибки. Спасибо UniMord за это предложение.

Предупреждение. Используйте этот метод с осторожностью, потому что он может не понравиться вашим коллегам! Кроме того, не забудьте добавить примечания в книгу или комментарии к ячейкам, объясняющие, что означают ваши пользовательские хеш-ошибки.

В приведенных ниже примерах показано, как создавать пользовательские хеш-ошибки с помощью функции ЕСЛИ и пользовательского числового формата, и вы можете загрузить образец файла, чтобы увидеть оба примера.

Функция ЕСЛИ

Поскольку ошибка выглядит как встроенная ошибка хеширования Excel, люди могут обратить на нее внимание.

Использовать собственный числовой формат

Вы также можете отобразить пользовательскую хеш-ошибку с условным форматированием. В этом примере в ячейке Итого (B8) есть правило условного форматирования, чтобы проверить, превышает ли итоговое значение лимит бюджета (D2)

=B8>D2

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

Загрузить образец файла

Загрузите заархивированный файл ошибок формул Excel. Заархивированный файл имеет формат xlsx и не содержит макросов.

Ресурсы по ошибкам Excel

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

FastExcel

Чарльз Уильямс – эксперт по вычислениям в Excel, и его надстройка FastExcel поможет вам найти узкие места в расчетах в книге, а также упростить понимание и отладку сложных формул.

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

Ошибка проверки системы

Не тратьте время на поиск ошибок после завершения работы с файлом Excel. Проверяйте ошибки с самого начала.

В своем онлайн-курсе Кен Пульс показывает, как создать систему проверки ошибок в рабочих тетрадях. Узнайте, как создать раздел проверки ошибок на каждом листе и глобальную контрольную точку для всей книги. Система даже проверяет сводные таблицы, чтобы увидеть, были ли они обновлены — я столкнулся с этой проблемой!

Эта небольшая инвестиция может сэкономить часы головной боли! Есть две версии курса:

видео из Интернета с загружаемыми книгами Excel , с загружаемыми книгами Excel

RefTreeAnalyser

Используйте надстройку Excel RefTreeAnalyser для аудита формул. Доступна бесплатная пробная версия.

Эта надстройка упрощает аудит зависимых формул и прецедентов, поиск циклических ссылок, проверку несоответствий формул и многие другие задачи аудита.

Формула

=IFNA(значение, значение_если_на)

Функция IFNA использует следующие аргументы:

Как использовать функцию IFNA в Excel?

Это встроенная функция, которую можно использовать как функцию рабочего листа в Excel. Возьмем пример:

Когда мы используем IFNA для обеих строк, результат будет таким:

Использование функции IFNA в Excel

Чтобы понять, как использовать функцию IFNA, рассмотрим несколько примеров:

Пример 1

Если нам нужно узнать цены на два товара, используя данные ниже:

Здесь используется следующая формула:

По аналогии мы можем использовать формулу HLOOKUP в сочетании с IFNA.

Пример 2

Рассмотрите данные ниже:

Когда мы используем формулу ЕСЛИОШИБКА, как показано ниже, результатом будет 0, поскольку ЕСЛИОШИБКА учитывает все ошибки.

Однако ЕСЛИОШИБКА посчитала это ошибкой и вернула 0.

Подробнее о функции IFNA в Excel

Функция IFNA помогает перехватывать функции ошибок и очень полезна при обработке больших объемов данных.
Помните, что если пользователь укажет value_if_na как пустую ячейку, функция обработает ее как пустое строковое значение ("").
Это быстрее, когда дело доходит до устранения неполадок, поскольку оно более конкретно по сравнению с IFERROR, ISERROR, ISERR и т. д.

Дополнительные ресурсы

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

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

Если вы предпочитаете помощь один на один или более немедленную помощь, в Excelchat доступен чат.

Когда этот запрос изменяется на что-то действительное, например на «Футбол», эта ошибка исчезает, и возвращается правильный результат.

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

Искомое значение написано правильно и не содержит лишних пробелов.
Значения в таблице поиска написаны правильно и не содержат лишних пробелов.
Ваша таблица поиска содержит необходимые данные
Вы правильно назвали диапазон поиска и ссылаетесь на него.
Тип соответствия (точное или приблизительное) правильный

Если вы предпочитаете, чтобы Excel возвращал пустую ячейку вместо сообщения, когда результат не найден или возникла ошибка, вы можете изменить инструкции на пустую строку следующим образом:

Числа в текстовом формате

Есть несколько способов исправить числовые значения в виде текста в Excel. Первый — выделить свои числа и щелкнуть правой кнопкой мыши. Выберите «Формат ячеек», а затем настройте форматирование соответствующим образом. Вы также можете выделить свои ячейки и перейти на вкладку «Данные». Выберите «Текст в столбцы» и нажмите «Готово».

Дополнительные пробелы в таблице данных

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

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

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

Проблема

Функция Vlookup находится в ячейке B2, но показана в ячейке A4, чтобы вы могли ее увидеть.

Проблема в том, что в ячейку A2 ничего не было введено для цвета, поэтому мы получаем ошибку в ячейке B2, которая совершенно бесполезна.

Исправить ошибку Vlookup в Excel 2007, 2010, 2013 и более поздних версиях

Чтобы удалить ошибку, мы можем использовать функцию ЕСЛИОШИБКА(), которая появилась в Excel 2007.

В качестве первого аргумента мы используем нашу оригинальную функцию Vlookup.

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

Вот результат:

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

Вот код для первого примера, который возвращает пробел:

Вот код для второго примера, который возвращает текст:

Когда мы вводим цвет, ВПР работает так, как должно:

Исправить ошибку Vlookup в Excel 2003

Если вы используете Excel 2003, вам нужно будет использовать две функции, чтобы заставить это работать (в Excel 2003 нет функции ЕСЛИОШИБКА()).

На этот раз нам нужно использовать функцию ЕСЛИ() и функцию ЕОШИБКА().

Во-первых, функция IF():

Затем функция ЕОШИБКА():

Затем наша функция ВПР:

Затем закройте функцию ЕОШИБКА скобками, введите запятую для перехода к следующему аргументу и введите значение, которое появится, если функция ВПР вызовет ошибку:

Затем введите запятую, чтобы перейти к последнему аргументу оператора IF, еще раз введите функцию Vlookup и добавьте закрывающую скобку для оператора IF:

Нажмите Enter, и готово!

ВАУ, работы было намного больше!

Вот полная формула, которую мы только что ввели:

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

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

Примечания

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

Если вы используете Excel 2007 или более позднюю версию, обязательно используйте метод ЕСЛИОШИБКА.

Помните, что в методе Excel 2003 оба ВПР должны быть абсолютно одинаковыми.

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

Удалить комментарии из книги в Excel — все комментарии удалены — макрос VBA
Макрос: макрос Excel, который удалит все комментарии из всей книги Excel.

Удалить все проверки данных из ячейки в Excel
Макрос: удалить все проверки данных из ячейки в Excel с помощью этого бесплатного макроса Excel. Это здорово.

Удалить все фильтры с рабочего листа в Excel
Макрос: этот макрос Excel удаляет все фильтры с рабочего листа в Excel. Это позволяет компл.

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

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

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

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