Как провалиться в ячейку excel

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

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

Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 запрещено, и если ввести формулу =1/0, Excel вернет #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!и #VALUE!.

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

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

Создание примера ошибки

1. Откройте чистый лист или создайте новый.
2. Введите 3 в ячейку B1, введите 0 в ячейку C1, а в ячейке A1 введите формулу =B1/C1.
   #DIV/0! в ячейке A1 отображается ошибка.
3. Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.
4. После знака равенства (=) введите IFERROR , а затем открываемую скобку.
   IFERROR(
5. Переместите курсор в конец формулы.
6. Введите , 0) — то есть запятая, за которой следует ноль и закрывающая скобка.
   Формула =B1/C1 становится =IFERROR(B1/C1,0).
7. Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
   Теперь содержимое ячейки должно отображать 0 вместо #DIV! должно отображаться значение 0.

Применение условного формата

1. Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.
2. Выберите команду Создать правило.
3. В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.
4. Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
5. Нажмите кнопку Формат.
6. На вкладке Число в списке Категория выберите пункт (все форматы).
7. В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
   Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

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

1. Выделите диапазон ячеек, содержащих значение ошибки.
2. На вкладке Главная щелкните стрелку рядом с элементом Условное форматирование и щелкните Управление правилами.
   Откроется диалоговое окно Диспетчер правил условного форматирования .
3. Выберите команду Создать правило.
   Откроется диалоговое окно Новое правило форматирования .
4. В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
5. В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.
6. Нажмите кнопку Формат и откройте вкладку Шрифт.
7. Щелкните стрелку, чтобы открыть список Цвет , и в разделе Цвета темы выберите белый цвет.

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

В некоторых случаях вы не хотите, чтобы поля ошибок отображались в ячейках, и вы предпочитаете, чтобы вместо этого отображалась текстовая строка, например «#N/A», дефис или строка «NA». Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.

Описание функций

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

Н/Д Эта функция возвращает в ячейке строку «#Н/Д». Синтаксис : =NA().

Скрытие значений ошибок в отчете сводной таблицы

1. Выберите отчет сводной таблицы.
2. На вкладке Анализ сводной таблицы в группе Сводная таблица щелкните стрелку рядом с элементом Параметры и выберите пункт Параметры.
3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
   • Изменение способа отображения ошибок. Установите флажок Для значений ошибки отобразить проверка в разделе Формат. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
   • Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

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

Ячейка с ошибкой в формуле

1. На вкладке Файл выберите Параметры и Формулы.
2. В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

Функция ТИП.ОШИБКИ для определения типа ошибки в ячейках Excel

Функция ТИП.ОШИБКИ в Excel выполняет проверку результатов выполнения выражений или других функций и возвращает число, соответствующее определенному коду ошибки.

Как определить тип ошибки в формуле Excel

Пример 1. Вывести числовые обозначения восьми распространенных ошибок в Excel.

Вид таблицы данных:

Для решения выделим диапазон ячеек B2:B9 и запишем следующую формулу:

• A2:A9 – диапазон ячеек, содержащих коды ошибок, для которых будут найдены их числовые представления.

Как видно выше на рисунке функция возвращает для каждой ошибки ее код в Excel.

Пример определения и обработки ошибок в Excel

Пример 2. Вычислить значение квадратно корня для каждого числа из диапазона значений, хранящихся в таблице. Если число отрицательное, может быть возвращен код ошибки #ЧИСЛО!. В этом случае следует рассчитать корень квадратный для модуля числа. Некоторые ячейки могут содержать текст. В этом случае необходимо вернуть значение 0.

Вид таблицы данных:

Для решения используем следующую формулу массива:

Выражение состоит из формулы ЕСНД, проверяющей результат выполнения функции ЕСЛИ, и нескольких функций ТИП.ОШИБКИ, вычисляющих коды возможных ошибок (3 – ошибка типа данных, 6 – введено некорректное число). В результате проверки нескольких условий функциями ЕСЛИ может быть сгенерирован код для типа #Н/Д, возвращаемый данной функцией, если ошибка не возникла. Перехват данной ситуации выполняет функция ЕСНД.

В результате получим следующие значения:

Данный пример приведен для наглядной демонстрации возможностей функции. В данном случае рациональнее использовать Е-функции (функции проверки условий, например, ЕОШИБКА).

Описание параметров и аргументов функции ТИП.ОШИБКИ в Excel

Функция имеет следующую синтаксическую запись:

• значение_ошибки – единственный аргумент (обязательный для заполнения), принимающий данные кода (например, #ССЫЛ) или ссылку на ячейку, содержащую результат выполнения выражения или функции.

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

Интерпретация результатов выполнения функцией:

• 1 – для #ПУСТО! (возникает в случае, если оператор пересечения диапазонов не обнаружил пересечений);
• 2 – для #ДЕЛ/0! (возникает при делении на число 0);
• 3 – для #ЗНАЧ! (возникает при вводе данных неверного типа);
• 4 – для #ССЫЛКА! (при отсутствии ячейки или диапазона ячеек, на которые ссылается формула);
• 5 – для #ИМЯ? (при использовании нераспознанных имен);
• 6 – для #ЧИСЛО! (возникает при вводе недопустимых чисел);
• 7 – для #Н/Д (возникает в случае выхода за диапазон допустимых значений);
• 8 – для #ОЖИДАНИЕ_ДАННЫХ (возникает при выполнении функций, когда найдены не все данные).

Если рассматриваемая функция получает в качестве аргумента любое другое значение или ссылку на пустую ячейку, будет возвращен код #Н/Д.

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Использование функции ЕСЛИ для проверки ячейки на наличие символов

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

В данном случае мы используем ЕСЛИ вместе с функцией ЕПУСТО:

• =ЕСЛИ(ЕПУСТО(D2);»Пустая»;»Не пустая»)

Эта формула означает: ЕСЛИ(ячейка D2 пуста, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вы также можете легко использовать собственную формулу для состояния «Не пустая». В следующем примере вместо функции ЕПУСТО используются знаки «». «» — фактически означает «ничего».

Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вот пример распространенного способа использования знаков «», при котором формула не вычисляется, если зависимая ячейка пуста:

• =ЕСЛИ(D3=»»;»»;ВашаФормула()) ЕСЛИ(в ячейке D3 ничего нет, не возвращать ничего, в противном случае вычислить формулу).

Как убрать ошибки в ячейках Excel

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

Ошибки в формуле Excel отображаемые в ячейках

В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.

Как убрать #ДЕЛ/0 в Excel

Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.

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

Результат ошибочного вычисления – #ЧИСЛО!

Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.

Несколько практических примеров:

Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).

В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.

В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым Excel не справиться.

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

Как убрать НД в Excel

Значение недоступно: #Н/Д! – значит, что значение является недоступным для формулы:

Записанная формула в B1: =ПОИСКПОЗ(„Максим”; A1:A4) ищет текстовое содержимое «Максим» в диапазоне ячеек A1:A4. Содержимое найдено во второй ячейке A2. Следовательно, функция возвращает результат 2. Вторая формула ищет текстовое содержимое «Андрей», то диапазон A1:A4 не содержит таких значений. Поэтому функция возвращает ошибку #Н/Д (нет данных).

Ошибка #ИМЯ! в Excel

Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:

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

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

В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.

Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!

#ССЫЛКА! – ошибка ссылок на ячейки Excel

Неправильная ссылка на ячейку: #ССЫЛКА! – значит, что аргументы формулы ссылаются на ошибочный адрес. Чаще всего это несуществующая ячейка.

В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.

Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.

Когда та же формула была скопирована под третий диапазон, в ячейку C3 функция вернула ошибку #ССЫЛКА! Так как над ячейкой C3 может быть только 2 ячейки а не 3 (как того требовала исходная формула).

Примечание. В данном случае наиболее удобнее под каждым диапазоном перед началом ввода нажать комбинацию горячих клавиш ALT+=. Тогда вставиться функция суммирования и автоматически определит количество суммирующих ячеек.

Так же ошибка #ССЫЛКА! часто возникает при неправильном указании имени листа в адресе трехмерных ссылок.

Как исправить ЗНАЧ в Excel

#ЗНАЧ! – ошибка в значении. Если мы пытаемся сложить число и слово в Excel в результате мы получим ошибку #ЗНАЧ! Интересен тот факт, что если бы мы попытались сложить две ячейки, в которых значение первой число, а второй – текст с помощью функции =СУММ(), то ошибки не возникнет, а текст примет значение 0 при вычислении. Например:

Решетки в ячейке Excel

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

Так решетки (######) вместо значения ячеек можно увидеть при отрицательно дате. Например, мы пытаемся отнять от старой даты новую дату. А в результате вычисления установлен формат ячеек «Дата» (а не «Общий»).

Неправильный формат ячейки так же может отображать вместо значений ряд символов решетки (######).