Скрытие значений и индикаторов ошибок в ячейках
Предположим, что в формулах с электронными таблицами есть ошибки, которые вы ожидаете и которые не нужно исправлять, но вы хотите улучшить отображение результатов. Существует несколько способов скрытие значений ошибок и индикаторов ошибок в ячейках.
Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 не допускается, и если ввести формулу =1/0, Excel возвращает #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!.
Преобразование ошибки в нулевое значение и использование формата для скрытия значения
Чтобы скрыть значения ошибок, можно преобразовать их, например, в число 0, а затем применить условный формат, позволяющий скрыть значение.
Создание примера ошибки
- Откройте чистый лист или создайте новый.
- Введите 3 в ячейку B1, в ячейку C1 — 0, а в ячейку A1 — формулу =B1/C1.
The #DIV/0! в ячейке A1. - Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.
- После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR( - Переместите курсор в конец формулы.
- Введите ,0), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0). - Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.
Применение условного формата
- Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.
- Выберите команду Создать правило.
- В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.
- Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
- Нажмите кнопку Формат.
- На вкладке Число в списке Категория выберите пункт (все форматы).
- В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.
Скрытие значений ошибок путем изменения цвета текста на белый
Для форматирования ячеек с ошибками используйте следующую процедуру, чтобы текст в них отображался белым шрифтом. В этом случае текст ошибки в этих ячейках практически невидим.
- Выделите диапазон ячеек, содержащих значение ошибки.
- На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования. - Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования. - В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
- В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.
- Нажмите кнопку Формат и откройте вкладку Шрифт.
- Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.
Отображение прочерка, строки «#Н/Д» или «НД» вместо значения ошибки
Иногда вы не хотите, чтобы в ячейках появлялись оценки ошибок и вместо них должна отображаться текстовая строка, например «#N/Д», тире или строка «0». Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.
Описание функций
ЕСЛИERROR С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.
НД Эта функция возвращает в ячейке строку «#Н/Д». Синтаксис =NA().
Скрытие значений ошибок в отчете сводной таблицы
- Выберите отчет сводной таблицы.
Появится область «Инструменты для работы со pivottable». - Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры. Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.
- Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
- Изменение способа отображения ошибок. В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Скрытие индикаторов ошибок в ячейках
В левом верхнем углу ячейки с формулой, которая возвращает ошибку, появляется треугольник (индикатор ошибки). Чтобы отключить его отображение, выполните указанные ниже действия.
Ячейка с ошибкой в формуле
- В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы. In Excel 2007: Click the Microsoft Office button >Excel Options >Formulas.
- В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.
Исправление ошибки #Н/Д
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 Еще. Меньше
Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
Лучшее решение
Чаще всего появление ошибки #Н/Д обусловлено тем, что формула не может найти значение, на которое ссылается функция ПРОСМОТРX, ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ. Например, искомого значения нет в исходных данных.
В данном случае в таблице подстановки нет элемента «Банан», поэтому функция ВПР возвращает ошибку #Н/Д.
Решение: Убедитесь, что искомое значение есть в исходных данных, или используйте в формуле обработчик ошибок, например функцию ЕСЛИОШИБКА. Например, формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает следующее:
- =ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)
Вы можете указать «», чтобы не отображалось ничего, или подставить собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение об ошибке»)
- Если вам нужна справка по ошибке #Н/Д для конкретной функции, например ВПР или ИНДЕКС/ПОИСКПОЗ, выберите один из указанных вариантов.
- Кроме того, может быть полезно узнать о некоторых распространенных функциях, вызывающих эту ошибку, таких как ПРОСМОТРX, ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ.
- Исправление ошибки #Н/Д в функции ВПР
- Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ
Если вы не знаете, что делать на этом этапе или какая помощь вам нужна, вы можете найти аналогичные вопросы в сообществе Майкрософт или опубликовать один из своих собственных.
Если вам по-прежнему нужна помощь с устранением этой ошибки, приведенный ниже контрольный список поможет вам определить возможные причины проблем в формулах.
Неправильные типы значений
Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию ВПР как число, а исходные данные сохранены как текст.
Решение: Убедитесь, что типы данных совпадают. Проверьте форматы ячеек. Для этого выделите диапазон ячеек, щелкните правой кнопкой мыши, выберите Формат ячеек > Число (или нажмите клавиши CTRL+1) и при необходимости измените числовой формат.
Совет: Если вам нужно принудительно изменить формат для целого столбца, сначала примените нужный формат, а затем выберите Данные > Текст по столбцам > Готово.
В ячейках есть лишние пробелы
Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.
В этом примере возвращается не только ошибка #Н/Д для элемента «Банан», но и неправильная цена для элемента «Черешня». К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Здесь нет близкого совпадения для элемента «Банан», а «Черешня» предшествует элементу «Персик». В этом случае при использовании функции ВПР с аргументом ЛОЖЬ будет отображаться правильная цена для элемента «Черешня», но для элемента «Банан» все равно будет указана ошибка #Н/Д, потому что в списке подстановок его нет.
Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).
Формула массива ссылается на диапазон, не соответствующий по количеству строк или столбцов диапазону, содержащему формулу массива.
Чтобы исправить ошибку, убедитесь, что диапазон, на который ссылается формула массива, содержит такое же количество строк и столбцов, что и диапазон ячеек, в котором была введена формула массива. Или введите формулу массива в меньшее или большее число ячеек в соответствии со ссылкой на диапазон в формуле.
В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:
В данном случае для месяцев с мая по декабрь указано значение #Н/Д, поэтому итог вычислить не удается и вместо него отображается ошибка #Н/Д.
В формуле, использующей стандартную или пользовательскую функцию, отсутствует один или несколько обязательных аргументов.
Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите все обязательные аргументы, которые возвращают ошибку. Вероятно, для проверки функции вам потребуется использовать редактор Visual Basic. Открыть этот редактор можно на вкладке «Разработчик» или с помощью клавиш ALT+F11.
Пользовательская функция, которую вы ввели, недоступна
Чтобы исправить ошибку, убедитесь в том, что книга, содержащая пользовательскую функцию, открыта, а функция работает правильно.
Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».
Чтобы исправить ошибку, убедитесь в том, что аргументы функции верны и расположены в нужных местах.
При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д
Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.
Нужна помощь по аргументам функции?
Если вы не знаете точно, какие аргументы использовать, вам поможет мастер функций. Выделите ячейку с формулой, а затем перейдите на вкладку Формулы и нажмите кнопку Вставить функцию.
Excel автоматически запустит мастер.
Щелкните любой аргумент, и Excel покажет вам сведения о нем.
Использование #Н/Д в диаграммах
Значение #Н/Д может принести пользу. Значения #Н/Д часто используются в диаграммах с такими данными, как в приведенном ниже примере, поскольку эти значения не отображаются на диаграмме. В примерах ниже показано, как выглядит диаграмма со значениями 0 и #Н/Д.
В предыдущем примере значения 0 показаны в виде прямой линии вдоль нижнего края диаграммы, а затем линия резко поднимается вверх, чтобы показать итог. В следующем примере вместо нулевых значений используются значения #Н/Д.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как убрать ошибки #ЗНАЧ! и Н/Д в формулах «Excel»
Часто при использовании формул в программе «Excel» из за не корректно введенных значений могут возникать ошибки #ЗНАЧ! и Н/Д в формулах.
В идеале следует вносить информацию для расчетов таким образом, чтобы ошибок не возникало, но бывают случаи, что ошибки должны возникать при определенных условиях в динамично заполняемых файлах и избавиться от них нельзя. Так же нельзя и удалить сами формулы из ячеек.
В таких случаях файл с ошибками выглядит не очень презентабельно и их нужно прятать от посторонних глаз, чтобы не смущать пользователей.
Рассмотрим функцию «Excel» при помощи, которой можно убрать (спрятать) ошибки #ЗНАЧ! и Н/Д в ячейках.
Эта функция обработки ошибок «ЕСЛИОШИБКА()».
Как работает функция ЕСЛИОШИБКА()?
Данная функция позволяет заменить, возникающие ошибки, на любое значение.
Например, дефис («-») или звездочку («*»).
Функция ЕСЛИОШИБКА() работает следующим образом:
В поле «ЗНАЧЕНИЕ» вводиться формула, в которой может возникать ошибка.
В поле «Значение_если_ошибка» вводится то значение, которое присваивается ячейке при возникновении ошибки в формуле.
Автор Master Of Exc Опубликовано 26.10.2018 Рубрики Функции
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Как удалить #N/A в Excel
Одной из наиболее распространенных ошибок, возникающих при работе с таблицами в Excel, является сообщение об ошибке «#N/A». Эта ошибка указывает на то, что в ячейке содержится недопустимое или неправильное значение, которое невозможно обработать. Как избавиться от этой ошибки и заменить ее на более подходящее значение?
Существует несколько способов убрать ошибку #N/A в Excel. Один из самых простых способов — это использование функции IFERROR. Функция IFERROR позволяет проверить значение ячейки на наличие ошибки и, в случае ее обнаружения, заменить ее на другое значение. Например, если в ячейке A1 содержится формула, результат которой может быть равен ошибке #N/A, то вы можете использовать следующую формулу в ячейке B1: =IFERROR(A1, 0). Эта формула проверит значение ячейки A1 на наличие ошибки #N/A и, в случае ее обнаружения, заменит ее на 0.
Еще один способ заменить ошибку #N/A — это использование функции IF и функции ISNA. Функция ISNA возвращает TRUE, если значение ячейки является ошибкой #N/A, и FALSE в противном случае. Функция IF позволяет выполнить условное действие в зависимости от результата проверки. Например, если в ячейке A1 содержится формула, результат которой может быть равен ошибке #N/A, то вы можете использовать следующую формулу в ячейке B1: =IF(ISNA(A1), 0, A1). Эта формула проверит значение ячейки A1 на наличие ошибки #N/A и, в случае ее обнаружения, заменит ее на 0, в противном случае оставит значение ячейки A1 без изменений.
Решение проблемы с ошибкой #N/A в Excel
Чтобы убрать или заменить ошибку #N/A на 0 в Excel, можно использовать функцию IFERROR. Эта функция позволяет проверить, содержит ли ячейка ошибку #N/A, и выполнить определенные действия в зависимости от результата проверки.
Читайте также: Как увеличить число на 1 в Excel
Вот как выглядит формула с использованием функции IFERROR:
=IFERROR(формула, значение_если_ошибка)
Например, чтобы заменить ошибку #N/A на 0, нужно использовать следующую формулу:
=IFERROR(формула, 0)
Если в ячейке, на которую ссылается формула, содержится ошибка #N/A, то возвращается значение 0. Если же ошибка отсутствует, то возвращается значение, вычисленное по формуле.
Как убрать ошибку в экселе НД и заменить на 0
Ошибка #N/A в Excel может быть причиной неудобств и затруднений при работе с данными. В основном она возникает, когда формула не может найти или обработать требуемые данные. Одна из частых ошибок, с которой можно столкнуться, это «НД» или «н/д», что означает «недоступные данные».
Однако, существует простой способ убрать эту ошибку и заменить ее на 0 в Excel. Для этого можно использовать функцию IFERROR. Функция IFERROR проверяет, есть ли ошибка в ячейке, и если она есть, то заменяет ее на заданное значение, в данном случае на 0.
Ниже приведен пример использования функции IFERROR для замены ошибки «НД» на 0:
- Вставьте формулу в ячейку, где требуется убрать ошибку и заменить на 0: =IFERROR(формула, 0)
- Замените «формула» на фактическую формулу, которую вы используете в ячейке.
- Нажмите Enter, чтобы применить формулу.
Теперь, если в ячейке была ошибка «НД», она будет заменена на 0. Если нет ошибки, то формула будет работать как обычно и вы получите результат, который она возвращает.
Как избавиться от ошибки «Недостаточно данных» в Excel
Ошибка «Недостаточно данных» (#N/A) в Excel может возникать, когда в формуле или функции используются недопустимые аргументы или данные отсутствуют. Эта ошибка может привести к неправильным результатам и затруднить работу с таблицами и данными.
Если вы столкнулись с ошибкой «Недостаточно данных» (#N/A) в Excel, вот несколько способов, как ее убрать или заменить на значение 0:
1. Проверьте аргументы и формулы:
Первым шагом в исправлении ошибки «Недостаточно данных» в Excel является проверка аргументов и формул, используемых в ячейках. Убедитесь, что все аргументы правильно указаны и имеют правильный формат. Если вы используете функции, убедитесь, что они применяются к правильным данным и ячейкам.
Читайте также: Как сделать книгу из листа Excel
2. Используйте функцию IFERROR:
Функция IFERROR позволяет заменить ошибку «Недостаточно данных» (#N/A) на другое значение, например, на 0. Формат функции IFERROR выглядит следующим образом:
Например, чтобы заменить ошибку «Недостаточно данных» (#N/A) на значение 0, вы можете использовать следующую формулу:
3. Используйте функцию ISNA:
Функция ISNA позволяет проверить, является ли значение ячейки ошибкой «Недостаточно данных» (#N/A). Если значение ячейки является ошибкой, функция возвращает TRUE, иначе FALSE. Вы можете использовать эту функцию вместе с условным оператором IF для замены значения на 0. Формат функции ISNA выглядит следующим образом:
=IF(ISNA(ячейка), 0, ячейка)
Например, чтобы заменить ошибку «Недостаточно данных» (#N/A) на значение 0, вы можете использовать следующую формулу:
С помощью этих методов вы можете убрать ошибку «Недостаточно данных» (#N/A) в Excel и заменить ее на значение 0. Это позволит вам продолжить работу с данными без проблем и получить правильные результаты.
Как убрать ошибку НД в Excel и заменить на 0
Существует несколько способов, как можно убрать ошибку НД и заменить ее на 0:
| Способ | Описание |
|---|---|
| Использование функции IFERROR | Функция IFERROR позволяет проверить, содержит ли ячейка ошибку НД, и если содержит, то заменить ее на заданное значение, в данном случае на 0. Формула выглядит следующим образом: =IFERROR(формула;0). |
| Использование функции IF с функцией ISNA | Функция IF с функцией ISNA также позволяет проверить, содержит ли ячейка ошибку НД, и если содержит, то заменить ее на заданное значение. Формула выглядит следующим образом: =IF(ISNA(ячейка);0;ячейка). |
| Использование условного форматирования | Условное форматирование позволяет задать определенное форматирование для ячейки, если она содержит ошибку НД. В данном случае мы можем задать форматирование, которое будет отображать ячейку со значением 0. Для этого нужно выбрать ячейки, применить условное форматирование и задать правило: «Форматировать только ячейки, содержащие» НД «и задать форматирование на значение 0. |
Читайте также: Как установить пароль на Excel
Как простым способом убрать ошибку «Недоступные данные» в Excel
Ошибка «Недоступные данные» (#N/A) в Excel может возникать при работе с большим объемом информации или при использовании формул, которые не могут обработать определенные значения. Эта ошибка может вызывать неудобства и затруднения при анализе данных. Однако, существует простой способ убрать эту ошибку и заменить ее на ноль.
Для того чтобы убрать ошибку «Недоступные данные» (#N/A) в Excel и заменить ее на ноль, следуйте следующим шагам:
- Выделите столбец или диапазон ячеек, в которых содержатся ошибки #N/A.
- Нажмите правую кнопку мыши на выделенном столбце или диапазоне ячеек и выберите пункт «Формат ячейки».
- В открывшемся окне «Формат ячейки» выберите вкладку «Число» и выберите формат «Общий».
- Нажмите на кнопку «OK», чтобы сохранить изменения.
После выполнения этих шагов, все ячейки, содержащие ошибку #N/A, будут заменены на ноль. Теперь вы сможете работать с данными без неудобств и использовать формулы, которые ранее не могли обработать ошибку #N/A.
Как заменить НД на 0 в экселе
Для начала откройте таблицу Excel, в которой присутствуют значения с ошибкой НД. Выделите столбец или ячейки, в которых нужно заменить ошибочные значения.
Далее, выберите вкладку «Редактирование» в верхнем меню Excel и нажмите на кнопку «Замена». Откроется диалоговое окно «Заменить».
В поле «Найти что» введите #N/A, а в поле «Заменить на» введите 0. После этого нажмите на кнопку «Заменить все». Excel автоматически заменит все ошибочные значения на ноль в выбранных ячейках или столбце.
Теперь ошибки НД заменены на ноль, и формулы и функции в Excel будут работать корректно. Вы можете сохранить таблицу или продолжить работу с данными.
Замена ошибки НД на ноль в Excel — простой способ убрать некорректные значения и обеспечить правильную работу формул и функций. Используйте этот метод, когда вам необходимо избавиться от ошибки «Недостаточно данных» в Excel и заменить ее на ноль.