Меню

Эксель не показывать ошибки

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

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

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

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

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

  1. Откройте чистый лист или создайте новый.

  2. Введите 3 в ячейку B1, в ячейку C1 — 0, а в ячейку A1 — формулу =B1/C1.
    The #DIV/0! в ячейке A1.

  3. Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

  4. После знака равно (=) введите ЕСЛИERROR и открываю скобку.
    ЕСЛИERROR(

  5. Переместите курсор в конец формулы.

  6. Введите ,0), то есть запятую и закрываюю скобки.
    Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0).

  7. Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
     Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.

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

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

  2. Выберите команду Создать правило.

  3. В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.

  4. Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

  5. Нажмите кнопку Формат.

  6. На вкладке Число в списке Категория выберите пункт (все форматы).

  7. В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
     Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

  1. Выделите диапазон ячеек, содержащих значение ошибки.

  2. На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
     Появится диалоговое окно Диспетчер правил условного форматирования.

  3. Выберите команду Создать правило.
     Откроется диалоговое окно Создание правила форматирования.

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

  5. В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.

  6. Нажмите кнопку Формат и откройте вкладку Шрифт.

  7. Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.

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

Пример

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

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

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

  1. Выберите отчет сводной таблицы.
    Появится область «Инструменты для работы со pivottable».

  2. Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

    Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

  3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

    • Изменение способа отображения ошибок.     В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.

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

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

Ячейка с неправильной формулой

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

  1. В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы.

    In Excel 2007: Click the Microsoft Office button Изображение кнопки Office> Excel Options >Formulas.

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

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

Источник


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

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

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

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

или значение

Пустой текст («»)

.

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

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

Формат ячеек

(см.

файл примера

).

  • для вызова окна

    Формат ячеек

    нажмите

    CTRL

    +

    1

    ;
  • выберите (все форматы).
  • в поле

    Тип

    введите формат

    [Черный]Основной
  • нажмите ОК
  • цвет шрифта ячейки установите таким же как и ее фон (обычно белый)

На рисунке ниже пользовательский формат применен к ячейкам

А6

и

B6

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

Применение вышеуказанного формата не влияет на вычисления. В

Строке формул

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

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

Используя

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

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

  • выделите интересующий диапазон;
  • в меню выберите

    );
  • в появившемся окне выберите

    Форматировать только ячейки, которые содержат

    ;
  • в выпадающем списке выберите

    Ошибки

    (см. рисунок ниже);
  • выберите пользовательский формат (белый цвет шрифта);

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

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

Замечательным свойством значения Пустой текст является, то что оно не отображается в ячейке (например, введите формулу

=»»

). Записав, например, в ячейке

А14

формулу

=ЕСЛИОШИБКА(1/0;»»)

получим пустую ячейку

А14

.

Источник

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

Как скрыть ошибки в Excel

Чтобы продемонстрировать как автоматически скрыть нули и ошибки в Excel, для наглядности возьмем таблицу в качестве примера, в котором необходимо скрыть нежелательные значения в столбце D.

Чтобы скрыть ОШИБКУ в ячейке:

Продажи в днях.

  1. Выделите диапазон ячеек D2:D8, а потом используйте инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».

    2. Создать правило.

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

    3. тип правила.

  3. В разделе «Измените описание правила:» из левого выпадающего списка выберите опцию «Ошибки».
  4. Нажимаем на кнопку «Формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый. На всех окнах «ОК».

Цвет.

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



Как скрыть нули в Excel

Пример автоматического скрытия нежелательных нулей в ячейках таблицы:

  1. Выделите диапазон ячеек D2:D8 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».

    2. Равно.

  2. В левом поле «Форматировать ячейки, которые РАВНЫ:» вводим значение 0.

    3. Форматировать ячейки 0.

  3. В правом выпадающем списке выбираем опцию «Пользовательский формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый.

скрыта ошибка ЗНАЧ.

Документ не содержит ошибочных значений и ненужных нулей. Ошибки и нули скрыты документ готов к печати.

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

Источник

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Ошибки в Excel лого

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

Ошибки в формулах делятся на несколько категорий:

Синтаксические ошибки: Возникают при неправильном синтаксисе формулы. Например, формула имеет несоответствующие скобки, или функция имеет не корректное количество аргументов.

Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.

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

Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?

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

Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.

Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.

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

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

Если вы создали формулу, в которой производится деление на ноль, Excel вернет ошибку #ДЕЛ/0!

Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).

Ошибка деление на ноль

Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!

Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:

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

Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:

Ошибка #Н/Д

Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.

Обычно, ошибка #Н/Д возвращается в результате работы формул подстановки (ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС). В случае, когда совпадение не было найдено.

Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().

Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:

Ошибка #ИМЯ?

Excel может вернуть ошибку #ИМЯ? в следующих случаях:

  • Формула содержит неопределенный именованный диапазон
  • Формула содержит текст, который Excel интерпретирует как неопределенный именованный диапазон. Например, неправильно написанное имя функции вернет ошибку #ИМЯ?
  • Формула содержит текст не заключенный в кавычки
  • Формула содержит ссылку на диапазон, у которого отсутствует двоеточие между адресами ячеек
  • Формула использует функцию рабочего листа, которая была определена надстройкой, но надстройка не была установлена

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

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

ошибка пересечения

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

Ошибка #ЧИСЛО! будет возвращена в следующих случаях:

  • В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
  • В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
  • Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
  • Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.

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

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

  • Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:
  • Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:
  • Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.
  • Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!

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

Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:

  • Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
  • Аргумент функции является диапазоном, когда он должен быть одним значением
  • Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
  • Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
  • Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива

Вам также могут быть интересны следующие статьи

5 комментариев

Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.

Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru

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

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:

Ошибка деления на ноль

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

Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.

Синтаксис функции следующий:

=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )

Так, в нашем примере можно было бы все исправить так:

Перехват ошибки функцией ЕСЛИОШИБКА IFERROR

Все красиво и ошибок больше нет.

Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:

Перехват ошибок в функциями ЕСЛИ и ЕОШ

Такой вариант ощутимо медленне работает и сложнее для понимания, так что лучше использовать новую функцию ЕСЛИОШИБКА, если это возможно.

Какие существуют обозначения ошибок и способы их исправления?

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

##### — Что обозначает и как исправить?

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

  • проверить формулу, если вычисляется число дней между двумя датами;
  • если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.

#ЗНАЧ! — Что обозначает и как исправить?

Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ! .

#ДЕЛ/0! — Что обозначает и как исправить?

Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.

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

#ИМЯ? — Что обозначает и как исправить?

Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.

1 вариант

Если используется имя, которое не было определено, то необходимо сделать следующее:

  • В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
  • В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.

Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».

2 вариант

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

  • В окне открытого листа нажмите клавишу F3.
  • В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
  • Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
  • Для закрепления нажмите клавишу Enter.
3 вариант

Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10) .

  • В окне открытого листа выделите ячейку с ошибкой в написании функции.
  • Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
  • В списке команд выберите пункт «Изменить в строке формул».
  • На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
  • Закрепите результат щелчком по клавише Enter.
4 вариант

Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

5 вариант

Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

6 вариант

Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).

#Н/Д — Что обозначает и как исправить?

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

1 вариант

Если в формулу были введены недостающие данные, а также #Н/Д или НД() , то #Н/Д необходимо заменить новыми данными.

2 вариант

Если в функциях ГПР , ПРОСМОТР , ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.

3 вариант

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

4 вариант

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

5 вариант

Если для просмотра значений в несортированной таблице используются функции ВПР , ГПР или ПОИСКПОЗ , для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ .
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.

6 вариант

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

7 вариант

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

#ССЫЛКА! — Что обозначает и как исправить?

Эти символы сообщают о неверной ссылке на ячейку.

1 вариант

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

2 вариант

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

3 вариант

Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.

4 вариант

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

#ЧИСЛО! — Что обозначает и как исправить?

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

1 вариант

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

2 вариант

Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.

3 вариант

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

#ПУСТО! — Что обозначает и как исправить?

Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.

1 вариант

Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:

  • для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например, СУММ(С1:С20) .
  • для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например, СУММ(С1:С20;D1:D20) .
2 вариант

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

Источник

Работа в Экселе

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

Идеальная электронная таблица Microsoft Excel не содержит ошибок или содержит?

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

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

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

Неправильно использовали формулу? Вместо того, чтобы вернуть неверный результат, Excel выдаст сообщение об ошибке. Например, вы можете увидеть сообщение об ошибке #DIV/0, если попытаетесь разделить значение на ноль. Вы также можете увидеть другие индикаторы ошибок на экране, например значки предупреждений рядом с ячейкой, содержащей ошибку.

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

Чтобы быстро скрыть индикаторы ошибок в Excel:

  1. Откройте электронную таблицу Excel.
  2. Выберите ячейку (или ячейки), содержащие сообщения об ошибках.
  3. Щелкните значок предупреждения, который появляется рядом с выбранными ячейками.
  4. В раскрывающемся списке выберите «Игнорировать ошибку».
    Игнорирование ошибок в Excel

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

Как использовать ЕСЛИОШИБКА в Excel, чтобы скрыть ошибки

Лучший способ остановить появление сообщений об ошибках в Excel — использовать функцию ЕСЛИОШИБКА. ЕСЛИОШИБКА использует логику ЕСЛИ для проверки формулы перед возвратом результата.

Например, если ячейка возвращает ошибку, верните значение. Если он не возвращает ошибку, верните правильный результат. Вы можете использовать ЕСЛИОШИБКА, чтобы скрыть сообщения об ошибках и сделать электронную таблицу Excel безошибочной (по крайней мере, визуально).

Структура формулы ЕСЛИОШИБКА: =ЕСЛИОШИБКА(значение,значение_если_ошибка). Вам нужно будет заменить значение вложенной функцией или вычислением, которое может содержать ошибку. Замените значение_если_ошибка сообщением или значением, которое Excel должен вернуть вместо сообщения об ошибке.

Если вы не хотите, чтобы сообщение об ошибке появлялось, используйте вместо него пустую текстовую строку (например, «»).

Чтобы использовать ЕСЛИОШИБКА в Excel:

  1. Откройте электронную таблицу Excel.
  2. Выберите пустую ячейку.
  3. В строке формул введите формулу ЕСЛИОШИБКА (например, =ЕСЛИОШИБКА(10/0,»»)
  4. Нажмите Enter, чтобы просмотреть результат.
    формула ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА — это простой, но мощный инструмент для сокрытия ошибок Excel. Вы можете вложить в него несколько функций, но для проверки убедитесь, что используемая функция предназначена для возврата ошибки. Если ошибка не появляется, вы будете знать, что это работает.

Как отключить отчеты об ошибках в Excel

Если вы хотите полностью отключить отчеты об ошибках в Excel, вы можете это сделать. Это гарантирует, что в вашей электронной таблице не будет ошибок, но вам не нужно использовать обходные пути, такие как ЕСЛИОШИБКА, чтобы сделать это.

Вы можете сделать это, чтобы подготовить электронную таблицу к печати (даже если есть ошибки). Поскольку ваши данные могут стать неполными или неправильными, если отчеты об ошибках отключены в Excel, мы не рекомендуем отключать их для производственного использования.

Чтобы отключить отчеты об ошибках в Excel:

  1. Откройте документ Excel.
  2. На ленте нажмите Файл.
    Меню файла Excel
  3. В меню «Файл» нажмите «Параметры» (или «Дополнительно» > «Параметры»).
    Меню параметров файла Excel
  4. В параметрах Excel нажмите «Формулы».
  5. Снимите флажок Включить фоновую проверку ошибок.
  6. Нажмите OK, чтобы сохранить.
    Отключить проверку ошибок в Excel

Решение проблем в Microsoft Excel

Если вы выполнили описанные выше шаги, вы сможете игнорировать все ошибки в электронной таблице Excel. Хотя не всегда возможно решить проблему в Excel, вам не нужно ее видеть. ЕСЛИОШИБКА работает хорошо, но если вам нужно быстрое исправление, вы всегда можете полностью отключить отчеты об ошибках.

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

Если у тебя есть Подписка на Microsoft 365, вы также можете попробовать восстановить установку Office. Если файл неисправен, попробуйте вместо этого восстановить файлы документов.

Источник

Ошибки в формулах Excel

Ошибки в формулах Excel

Часто при использовании формул в программе «Excel» из за не корректно введенных значений могут возникать ошибки #ЗНАЧ! и Н/Д в формулах.

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

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

Рассмотрим функцию «Excel» при помощи, которой можно убрать (спрятать) ошибки #ЗНАЧ! и Н/Д в ячейках.

Эта функция обработки ошибок «ЕСЛИОШИБКА()».

Еслиошибка()

Еслиошибка()

Как работает функция ЕСЛИОШИБКА()?

Данная функция позволяет заменить, возникающие ошибки, на любое значение.

Например, дефис («-») или звездочку («*»).

Функция ЕСЛИОШИБКА() работает следующим образом:

В поле «ЗНАЧЕНИЕ» вводиться формула, в которой может возникать ошибка.

Поле значение

Поле значение

В поле «Значение_если_ошибка» вводится то значение, которое присваивается ячейке при возникновении ошибки в формуле.

Значение, если ошибка

Значение, если ошибка

Источник

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

​Смотрите также​ вот такую (её​=СУММ(ЕСЛИ(ЕЧИСЛО(C2;G2;K2;O2);(C2;G2;K2;O2);»»)) — при​=МИН(ЕСЛИ(ЕЧИСЛО(B1:B6);B1:B6;»»))​Значение/формула (выбираем​ «Если значение в​В кулуарах математиков говорят,​ ЕСЛИ.​ на вкладке​ форматирования или собственные​. При работе в​

​Примечания:​, а затем выполните​Щелкните​ Мы стараемся как можно​   . С помощью этой​На вкладке​Рассмотрим ситуацию, когда формулы​ можно не выделять​ таком варианте выдает​Valeria555​

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

​формула​ ячейке B2 равно​ что результат деления​Скрытие нулевых значений в​Число​ предпочтения отображать или​

​ Excel Web App​

  1. ​ ​ следующие действия.​

  2. ​Число > Все форматы​​ оперативнее обеспечивать вас​​ функции можно определить,​​Число​​ в таблице содержат​ как массив, т.е.​​ ошибку в формуле.​​: Спасибо!! Вы меня​
    ​) =Ч(A1)​ 0, тогда формула​ любого числа на​

  3. ​ отчете сводной таблицы​.​ скрывать нулевые значения,​

  4. ​ повторите копирование и​Скрытые значения отображаются только​​Изменение способа отображения ошибок. ​​.​ актуальными справочными материалами​
    ​ содержит ли ячейка​

  5. ​в списке​ заранее ожидаемые значения​

  6. ​ не надо нажимать​​Михаил С.​​ спасли!!​вместо А1 ставите​ возвращает значение 0.​
    ​ ноль является «не​​Щелкните отчет сводной таблицы.​​Скрытие нулевых значений, возвращенных​​ имеется несколько способов​​ вставку для каждой​

  7. ​ в строке формул​    В поле​
    ​В поле​ на вашем языке.​ или возвращает ли​Числовые форматы​

​ ошибок. Они не​

  1. ​ CTRL+SHIFT+ENTER):​: =СУММ(ЕСЛИ(ЕЧИСЛО(C2;G2;K2;O2);(C2;G2;K2;O2);»»))​​Valeria555​​ верхнюю ячейку вашего​​ В противные случаи​​ ограниченным». Любая компьютерная​

  2. ​На вкладке​​ формулой, с помощью​​ реализации всех этих​

  3. ​ ячейки из примера.​​ или в ячейке,​​Формат​​Тип​ Эта страница переведена​​ формула ошибку.​

  4. ​выберите пункт​​ требуют немедленного исправления,​=ЕСЛИ(ЕЧИСЛО(C2);(C2)+ЕСЛИ(ЕЧИСЛО(G2);(G2)+ЕСЛИ(ЕЧИСЛО(K2);(K2)+ЕСЛИ(ЕЧИСЛО(O2);(O2)))))​Максим​​: У меня возникла​ столбца​​ формула должна возвратить​​ программа, при попытке​​Параметры​​ условного форматирования​ требований.​Внимание! Чтобы пример правильно​

  5. ​ если вы редактируете​​установите флажок​​введите выражение​

  6. ​ автоматически, поэтому ее​​НД​​(все форматы)​​ однако требуется улучшить​​, но она​​: То есть это​​ еще одна проблема,​

  7. ​Справа жмем «формат»​​ результат после операции​​ деления на 0,​​в группе​​Выделите ячейку, содержащую нулевое​Отображение и скрытие всех​ работал, его нужно​​ ее содержимое. Эти​​Для ошибок отображать​​0;;;@​​ текст может содержать​
    ​    Эта функция возвращает​.​ представление результатов. Существует​ слишком громоздка. Можно​​ я и пробовал,​​ в соседнем массиве​ =>Шрифт= > черный​ деления значений в​ просто возвращает ошибку.​Параметры сводной таблицы​

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

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

  1. ​ несколько способов скрытия​ ли упростить?​

  2. ​ а потом хотел​​ есть не только​​ цвет => ОК​​ ячейках B2/C2».​​ В Excel данная​щелкните стрелку рядом​​На вкладке​​ листе​​ A1.​​Чтобы снова отобразить скрытые​
    ​ значение, которое нужно​​ОК​​ ошибки. Для нас​

  3. ​ «#Н/Д». Синтаксис функции: =​​Тип​​ значений ошибок и​
    ​Михаил С.​​ сделать без «СУММ»,​​ ДЕЛ/0, но и​

  4. ​Только числовые значения​​Усложним нашу формулу функцией​​ ошибка отображается значением​​ с командой​Главная​​Нажмите​

  5. ​Чтобы переключиться между просмотром​​ значения, выделите ячейки,​​ выводить вместо ошибок.​​.​ важно, чтобы эта​НД()​​введите​​ индикаторов ошибки в​​:​

  6. ​ просто формулой, но​​ просто пустые ячейки,​​ будут окрашиваться черным​​ =ИЛИ(). Добавим еще​​ в ячейке #ДЕЛ/0!.​

  7. ​Параметры​в группе​​кнопку Microsoft Office​​ результатов и просмотром​​ а затем нажмите​​ Для отображения ошибок​

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

​Отображение скрытых значений​ статья была вам​.​;;;​ ячейках.​=сумм(C2;G2;K2;O2)​ ни так, ни​ при чем при​​ цветом .​​ одного торгового агента​​Но при необходимости можно​​и выберите пункт​Стили​

Пример

​, щелкните​

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

​(три точки с​​Формулы могут возвращать ошибки​или в​ так не получается​​ вводе функции МИН​​Гость​

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

  1. ​ с нулевым показателем​
    ​ обойти возникновения ошибки​​Параметры​​щелкните стрелку рядом​

  2. ​Параметры Excel​ результаты, нажмите клавиши​​CTRL+1​​ ячеек удалите из​​Выделите ячейки со скрытыми​​ уделить пару секунд​Появится вкладка​​ запятой) и нажмите​​ по многим причинам.​​О2​​ — пишет ошибку.​

    ​ (как было указано)​: 0mega, спасибо. Все​​ в продажах. Теперь​​ деления на 0​​.​​ с кнопкой​и выберите категорию​​CTRL+`​​или на вкладке​​ поля весь текст.​​ нулевыми значениями.​

  3. ​ и сообщить, помогла​​Работа со сводными таблицами​​ кнопку​ Например, формула =1/0​=если(ечисло(N2*Q11/100);N2*Q11/100;0)​

    • ​Мотя​​ =МИН(ЕСЛИ(ЕЧИСЛО(CO1549:CO1836);CO1549:CO1836;»0″)) выше excel2003​​ получилось.​​ формулу следует изменить​​ в Excel. Просто​​Перейдите на вкладку​Условное форматирование​Дополнительно​(знак ударения) или​Главная​Изменение способа отображения пустых​Вы можете нажать клавиши​

    • ​ ли она вам,​.​​ОК​​ возвращает ошибку #ДЕЛ/0!,​​Максим​: #ДЕЛ/0 — результат​ совсем ничего не​lika1​ на:​ следует пропустить операцию​Разметка и формат​и выберите «Правила​.​ на вкладке​

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

​в группе​ ячеек. ​CTRL+1​ с помощью кнопок​Excel 2016 и Excel 2013:​. Нажмите кнопку​ поскольку деление на​

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

​:​ неграмотного деления в​

  1. ​ выдает, а при​: помогите, как скрыть​Скопируйте эту формулу во​​ деления если в​​, а затем выполните​​ выделения ячеек» >​​В группе​​Формулы​​Ячейки​

    ​    Установите флажок​​или на вкладке​ Изображение кнопки Office​ внизу страницы. Для​​ на вкладке​​ОК​​ 0 недопустимо. Предусмотрены​​Михаил С.,​

  2. ​ EXCEL.​​ формуле МАКС пишет​​ ошибку деления на​​ все ячейки столбца​​ знаменателе находится число​

support.office.com

Отображение и скрытие нулевых значений

​ следующие действия.​​ «Равно».​Показать параметры для следующего​выберите​наведите указатель мыши​Для пустых ячеек отображать​Главная​ удобства также приводим​Анализ​еще раз.​ следующие значения ошибок:​Спасибо за помощь,​При выполнении деления​ Н/Д (((​ ноль​ «Выполнение»:​ 0. Решение реализовывается​Изменение способа отображения ошибок.​В левом поле введите​ листа​Зависимости формул > Показать​

​ на элемент​. Введите в поле​щелкнуть​ ссылку на оригинал​в группе​Значение 0 в​ #ДЕЛ/0!, #Н/Д, #ИМЯ?,​ но мой вариант​ в EXCEL следует​Файл прикрепить не​

​вроде была формула​Теперь независимо где будет​

​ с помощью помещения​     В поле​0​выберите лист, после​ формулы​Формат​ значение, которое нужно​Формат > Формат ячеек​ (на английском языке).​Сводная таблица​

​ ячейке исчезнет. Это​ #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!​ пока что более​

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

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

    • ​ связано с тем,​ и #ЗНАЧ!.​ приемлемый,так как есть​​ школьной арифметики.​ на почту.​короче, как спрятать​​ или в числителе​

    • ​ функции =ЕСЛИ():​установите флажок​В правом поле выберите​​ из указанных ниже​Скопировав пример на пустой​Формат ячеек​​ ячейках. Чтобы они​

​Для применения числового формата,​ отображение нулевых значений​

​ с командой​ что пользовательский формат​Чтобы скрыть значения ошибок,​ разница: переделывать формулу​В EXCEL для​По возможности помогите​ #ДЕЛ/0! и заменить​ формула будет работать​Таким образом формула Excel​Для ошибок отображать​Пользовательский формат​ действий.​

  1. ​ лист, вы можете​. Чтобы применить числовой​ оставались пустыми, удалите​

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

    Кнопка

  3. ​ так как нужно​​ позволяет нам «делить»​​. Введите в поле​

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

​и выберите пункт​​предписывает скрывать любые​

  1. ​ например, в число​ на: =если(ечисло(N2*Q11/100);N2*Q11/100;0) или​

  2. ​ ЕСЛИ.​​V​​если =E21/D21-1 (ссылается​​ пользователю.​​ число на 0​​ значение, которое нужно​​В диалоговом окне​

  3. ​ нулевые значения (0),​ как вам нужно.​ в списке​​ текст. Чтобы отображались​​Число > Общий​​ набор стандартов форматирования,​​Параметры​

​ числа в ячейке.​ 0, а затем​

  1. ​ один итоговый на​Максим​

  2. ​: пустые ячейки не​​ на пустые ячейки)​​Читайте также: Как убрать​ без ошибок. При​​ выводить вместо ошибок.​​Формат ячеек​ установите флажок​1​

  3. ​Категория​​ нулевые значения, снимите​​и нажмите кнопку​

  4. ​ которые требуют скрывать​​.​​ Однако фактическое значение​

  5. ​ применить условный формат,​​ ЕСЛИ(ЕЧИСЛО+ЕСЛИ(ЕЧИСЛО​​:​​ влияют на формулу.​​Микки​

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

​выберите​ этот флажок.​

​ОК​ нулевые значения. Отображать​

Данные в ячейках A2 и A3 на листе Excel

​Excel 2010 и Excel​ (0) по-прежнему хранится​ позволяющий скрыть значение.​Максим​

​Мотя,​

​ Вы формулу вводите​: =ЕСЛИОШИБКА(E21/D21-1;0) для 2007​Данная функция позволяет нам​ на 0 формула​ в виде пустых​Шрифт​ которые содержат нулевые​3​Общий​К началу страницы​.​ и скрывать нулевые​ 2007: на вкладке​ в ячейке.​Создание образца ошибки​: Всё же подскажите,​куда нам научным​ как формулу массива?​

​=ЕСЛИ(ЕОШ(E21/D21-1);0;E21/D21-1)​ расширить возможности первого​

  1. ​ будет возвращать значение​

  2. ​ ячеек удалите из​​.​​ значения​​4​​. Чтобы снова отобразить​Потребность отображать нулевые значения​​Скрытие нулевых значений, возвращенных​​ значения можно разными​​Параметры​​Описанная ниже процедура позволяет​

  3. ​Откройте чистый лист или​​ можно ли заставить​​ сотрудникам до школьной​Valeria555​

    • ​Useful​​ аргумента с условием​​ 0. То есть​​ поля весь текст.​​В поле​​.​5​ дату и время,​ (0) на листах​ формулой​ способами.​в группе​

    • ​ отформатировать ячейки с​ создайте новый.​​ СУММЕСЛИ работать не​​ арифметики​​: Вы имеете в​: =IF(D21<>0,E21/D21-1,»»)​ во функции ЕСЛИ.​ получим такой результат​Изменение способа отображения пустых​Цвет​Чтобы отображать нулевые значения​6​ выберите подходящий формат​

​ возникает не всегда.​

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

​ после деления: 10/0=0.​ ячеек.​выберите белый цвет.​

  1. ​ в виде пустых​​7​​ даты и времени​​ Требуют ли стандарты​​ (0) значение.​​ Office 2010 Office 2007 ​​щелкните стрелку рядом​

  2. ​ чтобы текст в​​3​ но и с​​ открывали-то? Видимо нет.​ нажимаю ctrl+shift+enter и​: А если там​ ячейке с формулой​

    • ​​     Установите флажок​Использование формулы для отображения​​ ячеек, снимите флажок​A​ на вкладке​​ форматирования или собственные​

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

​ вся формула становится​ пусто …или пробел​ D5 первый аргумент​

​Для работы корректной функция​Для пустых ячеек отображать​ нулей в виде​Показывать нули в ячейках,​B​Число​ предпочтения отображать или​Главная​

  1. ​ (0) на листах​Параметры​ белого цвета. В​

  2. ​0​​ ячеек (C2;G2;K2;O2)?​​ таблица шаблонного типа,​​ в фигурные скобочки?​​Useful​​ функции ЕСЛИ теперь​​ ЕСЛИ требует заполнить​

  3. ​. Введите в поле​​ пробелов или тире​​ которые содержат нулевые​​Данные​​.​

  4. ​ скрывать нулевые значения,​​щелкните стрелку рядом​​ возникает не всегда.​​и выберите пункт​

​ результате текст ошибки​​ — в ячейку C1​

  • ​Михаил С.​ в которой рассчитывается​ Да, я так​: =IF(AND(ISNUMBER(D21),D21<>0),E21/D21-1,»»)​ следует читать так:​ 3 ее аргумента:​

  • ​ значение, которое нужно​Для выполнения этой задачи​ значения​10​​Скрытие нулевых значений, возвращенных​​ имеется несколько способов​​ с кнопкой​​ Требуют ли стандарты​​Параметры​​ в таких ячейках​ и формулу​​: Нет, нельзя.​​ влажность отдельных органов,​​ делаю.​​Катя1979​ «Если значения в​Логическое условие.​​ выводить в пустых​​ используйте функцию ЕСЛИ.​​.​​10​ формулой, с помощью​ реализации всех этих​Условное форматирование​ форматирования или собственные​​.​​ становится невидимым.​

​=B1/C1​Мотя​ а потом складывается​

  1. ​V​: /// если =E21/D21-1​

  2. ​ ячейках B5 или​​Действия или значения, которые​​ ячейках. Чтобы они​​Пример​​Скрытие нулевых значений в​Формула​​ условного форматирования​​ требований.​и выберите «Правила​​ предпочтения отображать или​​Откройте вкладку​​Выделите диапазон ячеек, содержащих​​ — в ячейку A1.​

  3. ​: См. файл.​​ сухое вещество. Однако​​: Да я об​

  4. ​ (ссылается на пустые​​ C5 равно ноль,​​ будут выполнены если​

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

  6. ​Отображение и скрытие всех​​ выделения ячеек» >​​ скрывать нулевые значения,​

​Разметка и формат​ значение ошибки.​В ячейке A1​

​Максим​ не все органы​​ этом. Кидайте посмотрю​​ ячейки)​

​ тогда условие возвращает​

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

​=A2-A3​

  1. ​ (0) значение.​ нулевых значений на​ «Равно».​ имеется несколько способов​и выполните одно​На вкладке​
    ​ отобразится значение ошибки​​:​ могут присутствовать и​

    выбор примера из справки в Excel 2013 для Windows

    ​ [email protected]​

  2. ​=если(E21=»»;»»;E21/D21-1)​​ логическое значение ИСТИНА».​​ условие возвращает значение​

  3. ​ текст. Чтобы отображались​ его на пустой​

  4. ​Эти действия позволяют скрыть​Второе число вычитается из​На вкладке​​ листе​​В левом поле введите​ реализации всех этих​ или оба указанных​Главная​ #ДЕЛ/0!.​

  5. ​Мотя,​ поэтому в столбцах​V​oleg-ba​

  6. ​ Ну а дальше​ ИСТИНА.​ нулевые значения, снимите​ лист.​​ нулевые значения в​​ первого (0).​Главная​​Выберите​​0​​ требований.​ ниже действия.​​в группе​

  7. ​Выделите ячейку A1 и​Ваша формула понятна,​ отсутствуют данные. Тем​: у вас в​

​: Извините господа,возможно этот​

​ как прочитать остальную​

​Действия или значения, которые​

​ этот флажок.​

​Копирование примера​

​ выделенных ячейках. Если​

​=ЕСЛИ(A2-A3=0;»»;A2-A3)​

​в группе​

​Файл​

​.​

​Скрытие и отображение всех​

​Измените способ отображения ошибок.​

​Стили​

​ нажмите клавишу F2,​

​ но она никак​

​ не менее формула​ формуле диапазоны разные​

​ вопрос легкий и​

​ часть формулы описано​ будут выполнены, когда​

​Полные сведения о формулах​

​Выделите пример, приведенный в​ значение в одной​

​Возвращает пустую ячейку, если​Стили​>​В правом поле выберите​

​ нулевых значений на​    В группе​

  1. ​щелкните стрелку рядом​

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

  3. ​ значение равно нулю​​щелкните стрелку рядом​​Параметры​Пользовательский формат​

    ​ листе​​Формат​​ с командой​​После знака равенства (=)​​ высказыванию Спасибо за​​ столбцах и чтобы​ одинаковые), отсюда и​ прошу,помогите пожалуйста,не удаляйте!!!​link_90​ значение ЛОЖЬ.​Рекомендации, позволяющие избежать появления​Важно:​

    ​ ненулевым, его формат​=ЕСЛИ(A2-A3=0;»-«;A2-A3)​​ с элементом​​>​​.​Выберите​установите флажок​Условное форматирование​ введите​ один из вариантов​ не подстраиваться под​ ошибка.​ Надо в графах​

​: Подскажите пожалуйста как​

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

​и выберите пункт​ЕСЛИОШИБКА​ решения моей задачи,​

  1. ​ каждый вариант прошу​​=МАКС(ЕСЛИ(ЕЧИСЛО(CO1549:CO1836);CO1549:CO1836;0))​ Изображение кнопки Office​ накладные расходы и​​ убрать #ДЕЛ/0!​​ с условием содержит​​Поиск ошибок в формулах​​ или столбцов.​

  2. ​ числовому формату.​​ значение равно нулю​, наведите указатель на​​.​Формат ячейки​>​. Введите в поле​

    • ​Управление правилами​с открывающей круглой​ но подстановка этой​​ подсказать как подправить​Valeria555​ С/С продукции ,написать​​В таблице вычисляются​

    • ​ проверку значений. Являются​Сочетания клавиш и горячие​Выделение примера в справке​​Выделите ячейки, содержащие нулевые​Дополнительные сведения об использовании​ элемент​​В группе​

​откройте вкладку​Параметры​ значение, которое должно​

​.​ скобкой:​ формулы во все​ формулу, что бы​: А почему тогда​ формулу таким образом,чтобы​ средние значения по​ ли равным 0​

  1. ​ клавиши в Excel​Нажмите клавиши CTRL+C.​ значения (0), которые​

  2. ​ этой функции см.​​Правила выделения ячеек​​Показать параметры для следующего​​Шрифт​​>​​ отображаться вместо ошибок.​​Появится диалоговое окно​​ЕСЛИОШИБКА(​​ ячейки большой таблицы​

  3. ​ не учитывалась ошибка​​ формула МИН не​​ вместо «ДЕЛО»,ставился 0​​ сотрудникам и задень​​ значения ячеек в​

  4. ​Функции Excel (по алфавиту)​​В Excel создайте пустую​​ требуется скрыть.​​ в статье Функция​

​и выберите вариант​​ листа​

  • ​.​Дополнительно​Изображение кнопки​ Чтобы в пустых​Диспетчер правил условного форматирования​Переместите курсор в конец​ где она должна​

  • ​ — #ДЕЛ/0.​ считается?​ обязательно!!. разумеется при​ и за месяц​​ столбце «Продажи». Первый​​Функции Excel (по категориям)​​ книгу или лист.​​Вы можете нажать клавиши​ ЕСЛИ.​​Равно​​выберите лист, после​​В списке​​.​ полях отображались ошибки,​.​​ формулы.​​ быть — трудоёмкая​​Михаил С.​​V​ каком то выпуске:​ (Формулы одинаковые). Есть​ аргумент функции ЕСЛИ​В математике деление на​​Выделите на листе ячейку​​CTRL+1​

​Скрытие нулевых значений в​.​ чего выполните одно​

  1. ​Цвет​В группе​

  2. ​ удалите из поля​​Выберите команду​​Введите​​ операция. Когда можно​​:​: мин считает -​​ 1,2,3 шт.. все​​ сотрудники и дни​ всегда должен иметь​ ноль – невозможно!​

  3. ​ A1 и нажмите​​или на вкладке​​ отчете сводной таблицы​

  4. ​В левом поле введите​​ из указанных ниже​​выберите белый цвет​

  5. ​Показать параметры для следующего​​ весь текст.​​Создать правило​​,0)​​ написать один раз,​

  6. ​Максим,​​ просто ответ ноль.​​ хорошо,а когда выпуска​

​ когда в соответствующих​ операторы сравнения между​ Одним из способов​

​ клавиши CTRL+V.​Главная​

​Щелкните отчет сводной таблицы.​

​0​ действий.​ и нажмите кнопку​ листа​

​Измените способ отображения пустых​

  1. ​.​, т. е. запятую​

​ хоть и, громоздкую​​Начиная с 2010​ Если нули не​

выбор примера из справки в Excel 2013 для Windows

​ нет,не считает,приходиться,скрывать строки​

  1. ​ значениях за день​

  2. ​ двумя значениями, чтобы​ для объяснения данного​

  3. ​Важно:​в группе​На вкладке​

​.​​Чтобы отображать в ячейках​ОК​выберите лист, после​

  1. ​ ячеек.​Откроется диалоговое окно​ с нулем и​ формулу и растянуть​ есть функция АГРЕГАТ,​ учитывать то​​ БЕЗ ВЫПУСКА, только​​ или у одного​ получить результат условия​​ правила является анализ​​ Чтобы пример правильно работал,​

​Ячейки​Параметры​В правом поле выберите​ нулевые значения (0),​

​.​

​ чего выполните одно​

​    Установите флажок​

​Создание правила форматирования​

​ закрывающей круглой скобкой.​

​ её на весь​

​ в вашем случае​

​=МИН(ЕСЛИ(ЕСЛИ(ЕЧИСЛО(CO1549:CO1836);CO1549:CO1836;0)>0;CO1549:CO1836;»»)) или =МИН(ЕСЛИ(ЕЧИСЛО(CO1549:CO1836)*(CL1549:CL1836>0);CO1549:CO1836;»»))​

​ тогда можно просчитать​

​ сотрудника во всём​

​ в качестве значений​

​ процесса, который показывает,​

​ его нужно вставить​

​щелкнуть​

​в группе​

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

​Отображение нулей в виде​

​ из указанных ниже​Для пустых ячеек отображать​

​.​

​Формула​ диапазон.​

​ будет так:​Valeria555​ всю таблицу​ ряду значения 0.​

​ ИСТИНА или ЛОЖЬ.​ что происходит, когда​

  1. ​ в ячейку A1.​

  2. ​Формат > Формат ячеек​​Параметры сводной таблицы​​.​​Показывать нули в ячейках,​​ пробелов или тире​ действий.​​. Введите в поле​​В списке​​=B1/C1​​Мотя​

  3. ​=АГРЕГАТ(9;6;C2;G2;K2;O2)​​: Даже не знаю,​​китин​ Как сделать чтоб​

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

    ​: Эту формулу можно​В более ранних​​ как Вас благодарить!!!​​: попробуйте применить​​ в этом случае​ используется в качестве​ на другое.​ результатов и просмотром​В списке​ с командой​Формат ячеек​ значения​ воспользуйтесь функцией ЕСЛИ.​

См. также

​ нулевые значения (0),​ отображаться в пустых​

​выберите пункт​=ЕСЛИОШИБКА(B1/C1,0)​

​ упростить: 2 варианта.​

​ версиях, по совету​ СПАСИБО ЗА ПОМОЩЬ!!!​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА((I$1*R3)/D3;0)​

​ в ячейках, которые​

support.office.com

Как убрать ошибку деления на ноль в Excel с помощью формулы

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

Ошибка деления на ноль в Excel

​ ячейках. Чтобы в​Форматировать только ячейки, которые​.​1. Для числителя​ Моти, избавляетесь от​V​в ячейке I3​ вычисляют среднее не​ равенства, но могут​ это по сути​ результаты, нажмите клавиши​выберите элемент​и выберите пункт​Шрифт​Чтобы отображать нулевые значения​

  1. ​ значения, для возврата​
  2. ​Показывать нули в ячейках,​
  3. ​ пустых ячейках ничего​
  4. ​ содержат​
  5. ​Нажмите клавишу ВВОД, чтобы​

​ и знаменателя формулы​ ошибок деления в​: я бы на​Nic70y​ было #ДЕЛ/0!​ быть использованы и​ тоже что и​ CTRL+` (знак ударения)​Пользовательский​Параметры​.​ в виде пустых​ пустой ячейки используйте​ которые содержат нулевые​ не отображалось, удалите​.​

  • ​ завершить редактирование формулы.​
  • ​ можно сделать 2​
  • ​ процессе деления, примерно​

​ вашем месте избавился​:​Заранее спасибо!​ другие например, больше>​ вычитание. Например, деление​ или на вкладке​.​.​В поле​ ячеек, снимите флажок​ формулу, например такую:​

Ошибка ДЕЛ 0.

​ значения​ из поля весь​В разделе​Теперь в ячейке​ дополнительных столбца, чтобы​ так: (для​ от ошибок с​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(D3;(I$1*R3)/D3;)​VLad777​ или меньше >.​ числа 10 на​

Формула деления на ноль.

​Формулы​В поле​Перейдите на вкладку​Цвет​Показывать нули в ячейках,​=ЕСЛИ(A2-A3=0;»»;A2-A3)​.​ текст. Чтобы отображались​Измените описание правила​ вместо ошибки #ДЕЛ/0!​

​ формула была «прозрачней».​

Как работает формула для устранения ошибки деления на ноль

​О2​ помощью если​oleg-ba​

  1. ​: добавить еще одно​
  2. ​ Или их комбинации​ 2 является многократным​в группе «Зависимости​Тип​Разметка и формат​
  3. ​выберите белый цвет.​ которые содержат нулевые​Вот как читать формулу.​Чтобы отображать нулевые значения​

​ нулевые значения, снимите​в списке​ должно отображаться значение​С.М.​)​=ЕСЛИ(ЕОШИБКА(CL19/CN19);»»;CL19/CN19) тогда в​: С графой накладные​ если​ – больше или​ вычитанием 2 от​ формул» нажмите кнопку​введите​, а затем выполните​Использование формулы для отображения​ значения​ Если результат вычисления​ в виде пустых​ этот флажок.​Форматировать только ячейки, для​ 0.​:​=если(ечисло(N2*Q11/100);N2*Q11/100;»»)​ формулах не нужно​ получилось!!! Спасибо большое!!!​

​=ЕСЛИ(ЕОШ(СРЗНАЧ(ЕСЛИ(D2:D6;D2:D6)));»»;СРЗНАЧ(ЕСЛИ(D2:D6;D2:D6))) тоже формула​ равно >=, не​ 10-ти. Многократность повторяется​Показать формулы​0;-0;;@​ следующие действия.​ нулей в виде​.​ (A2-A3) равен «0»,​ ячеек, снимите флажок​В левом верхнем углу​ которых выполняется следующее​Применение условного формата​Алексей​Максим​ будет лишний раз​

​Nic70y​ массива.​ равно !=.​ до той поры​.​Примечания:​Изменение способа отображения ошибок.​ пробелов или тире​Скрытие нулевых значений в​ ничего не отображается,​Показывать нули в ячейках,​ ячейки с формулой,​

​ условие​Выделите ячейку с ошибкой​: в ячейках стоят​:​ проверять на ошибку.​: См. файл:​openid.mail.ru/mail/vlad3622340​Если условие в первом​ пока результат не​Скопировав пример на пустой​ ​

Формула для деления на ноль или ноль на число

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

​ и на вкладке​ формулы расчета процента​Михаил С.,​

Формула деления на ноль и ноль на число.

​Valeria555​oleg-ba​: .​ аргументе возвращает значение​ будет равен 0.​ лист, вы можете​

​Скрытые значения отображаются только​Формат​

​ используйте функцию​ помощью числового формата​ и «0» (это​ значения​ появляется треугольник (индикатор​Ошибки​Главная​если слева в​Спасибо за функцию​: Подскажите, что в​: СПАСИБО ВАМ БОЛЬШОЕ!!!!!!!!!!!!!!!!!!!!!!!!!​Guest​ ИСТИНА, тогда формула​ Таким образом необходимо​ настроить его так,​ в формуле отрезков​установите флажок​ЕСЛИ​

exceltable.com

как убрать #ДЕЛ/0!

​Эти действия позволяют скрыть​​ указывается двойными кавычками​.​
​ ошибки). Чтобы отключить​.​нажмите кнопку​ ячейках стоят цифрв,​ АГРЕГАТ — почитал,​ данном случае должно​oleg-ba​: в 2007-м …​ заполнит ячейку значением​ число 2 вычитать​ как вам нужно.​— или в​Для ошибок отображать​.​ нулевые значения в​ «»). В противном​

​Скрытие нулевых значений в​

​ его отображение, выполните​​Нажмите кнопку​Условное форматирование​
​ то все ок​ весьма интересная, но​

​ быть в к​​: Ой сорри,не совсем​

​=ЕСЛИОШИБКА(значение,значение_при_ошибке)​​ со второго аргумента​
​ от десяти ровно​

​1​​ ячейке при редактировании​. Введите в поле​Пример​ выделенных ячейках. Если​ случае отображается результат​ выделенных ячейках​

​ указанные ниже действия.​​Формат​.​

​если слева цифр​​ у меня Microsoft​

planetaexcel.ru

Как сделать прочерк вместо #ДЕЛО/0! ? (Как сделать прочерк)

​А​​ корректно работает…накладные распределяются​link_90​ функции ЕСЛИ. В​
​ 5 раз:​

​2​​ в ячейке и​
​ значение, которое нужно​Чтобы этот пример проще​
​ значение в одной​ вычисления A2-A3. Если​
​Выполняйте эти действия для​Ячейка с ошибкой в​
​и откройте вкладку​​Выберите команду​​ нет, то в​
​ Excel 2003​вычках «если истина»?​ только на строки​
​: Спасибо! но в​ данном примере второй​10-2=8​
​3​ не печатается.​ выводить вместо ошибок.​

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

excelworld.ru

скрыть деление на ноль.

​ скрытия нулевых значений​​ формуле​Шрифт​Создать правило​
​ ячейке процента появля.тся​Избавление от ошибки​
​V​ начиная с прицепов​ ячейке H7 тоже​
​ аргумент содержит число​8-2=6​

​4​​Чтобы снова отобразить скрытые​
​ Для отображения ошибок​

​ его на пустой​​ ненулевым, его формат​

​ оставлять ячейки пустыми,​​ в выделенных ячейках​В Excel 2016, Excel 2013​

​.​​.​

​ решеткаи и пишет​​ =если(ечисло(N2*Q11/100);N2*Q11/100;»» 😉 не​: как написано так​ и далее,а не​
​ должно стоять значение​

planetaexcel.ru

Исправление ошибки #ДЕЛ/0! (Формулы)

​ 0 в качестве​​6-2=4​5​ значения, выделите ячейки,​ в виде пустых​ лист.​ будет аналогичен общему​ но отображать не​ с помощью числового​ и Excel 2010:​Щелкните стрелку, чтобы открыть​В диалоговом окне​ ошибку деления на​ дает результата, т.е.​ и оставляйте. тогда​ верхние строки НЕТ,​ среднее среднего))) а​ значения. Значит ячейка​

​4-2=2​​6​​ а затем на​​ ячеек удалите из​

​Копирование примера​​ числовому формату.​​ "0", а что-то​

​ формата. Если значение​​ выберите​ список​

​Создание правила форматирования​​ 0​

​ сама ошибка не​​ ячейка будет пустой​

​ надо 850 млн​​ ячейки пустые. какую​ в столбце «Выполнение»​2-2=0​7​ вкладке​ поля весь текст.​Выберите пример в этой​Выделите ячейки, содержащие нулевые​ другое, между двойными​

​ в одной из​​Файл​Цвет​выберите параметр​

excelworld.ru

Не учитывать в формуле #ДЕЛ/0

​как сделать так​​ высвечивается, но при​ или вместо «»​ распределить ТОЛЬКО на​ формулу туда вписать?​ просто будет заполнена​Если же попробовать разделить​A​Главная​
​Изменение способа отображения пустых​

​ статье. Если вы​​ значения (0), которые​ кавычками вставьте дефис​
​ этих ячеек меняется​

​ >​​, и в разделе​Форматировать только ячейки, которые​:)

​ чтобы в таких​​ подсчете суммы =C2+G2+K2+O2​ впишите ноль (ячейка​ прицепы и вниз!​link_90​ числом 0 если​ число 10 на​B​в группе​ ячеек.​ копируете пример в​ требуется скрыть.​ «-» или другой​ на ненулевое, оно​Параметры​
​Цвета темы​ содержат​ ячейках просто пусто​
​ (с указанием на​ тоже будет пустой​

​oleg-ba​​: спасибо! уже разобрался​ в ячейке напротив​ 0, никогда мы​Данные​

​Ячейки​​     Установите флажок​ Excel Web App,​Вы можете нажать клавиши​ символ.​ отобразится в ячейке,​ >​выберите белый цвет.​

​.​​ оставалось​ эту ячейку) выдает​ т.к. у вас​

​: Вроде сделал просто,но​​ просто вписать среднее))​ из столбца «Продажи»​ не получим результат​10​наведите указатель мыши​
​Для пустых ячеек отображать​

​ копируйте и вставляйте​​CTRL+1​Скрытие нулевых значений в​ а формат его​

​Формулы​​Возможны ситуации, когда необходимо,​Убедитесь, что в разделе​Спасибо​ ошибку #ЗНАЧ!​
​ в настройках стоит​

​ ячеек конечно расплодил,проще​​Guest​ будет 0 продаж.​ равен 0, так​:) :) :)

​10​​ на элемент​. Введите в поле​ ячейки по одной.​или на вкладке​
​ отчете сводной таблицы​ будет аналогичен общему​.​ чтобы вместо значений​

​Форматировать только ячейки, для​​vikttur​Можно ли подправить​ «скрывать нули»).​​ не смог.​​:​

​Если условие в первом​​ как при вычитании​Формула​Формат​ значение, которое нужно​Внимание!​Главная​Выберите отчет сводной таблицы.​ числовому формату.​В Excel 2007 нажмите​

​ ошибок в ячейках​​ которых выполняется следующее​​:​
​ формулу =СУММ(ЕСЛИ(ЕЧИСЛО так,​Максим​Valeria555​Гость​ аргументе возвращает значение​ 10-0 всегда будет​
​Описание (результат)​и выберите​ выводить в пустых​   Не выделяйте заголовки строк​
​щелкнуть​На вкладке​Выделите ячейки, содержащие нулевые​

​кнопку Microsoft Office​​ отображалась текстовая строка,​

​ условие​​=ЕСЛИ(где-то<>0;делим;»»)​ что бы она​:​: Добрый день! Помогите​: Прошу помочь,как сделать​ ЛОЖЬ, тогда используется​ 10. Бесконечное количество​=A2-A3​

​Формат ячеек​​ ячейках. Чтобы они​ или столбцов.​Формат > Формат ячеек​
​Анализ​ значения (0), которые​и выберите пункты​ например «#Н/Д», прочерк​
​в первом списке​Pankratd​ работала не только​

​V,​​ мне пожалуйста! Как​​ прочерк(ноль,что-то заменяющее) вместо​​ значение из третьего​ раз вычитаний ноля​Второе число вычитается из​;)​. Чтобы применить числовой​ оставались пустыми, удалите​
​Выделение примера в справке​.​в группе​ требуется скрыть.​Параметры Excel​ или «НД». Сделать​ выбран пункт​: Для 2007 =ЕСЛИОШИБКА(делимое/делитель;0)​ с диапазоном?​А как использовать​ сделать чтобы в​ #ДЕЛО.​ аргумента функции ЕСЛИ.​ от десяти не​ первого (0).​ формат по умолчанию,​ из поля весь​Нажмите клавиши​В списке​

​Сводная таблица​​Вы можете нажать клавиши​​ >​​ это можно с​Значение ячейки​для 2003 =если(делитель=0;0,делимое/делитель)​Потому что так​
​ данную формулу если​
​ excel2003 при вводе​Спасибо.​ В данном случаи​ приведет нас к​=ЕСЛИ(A2-A3=0;»»;A2-A3)​ в списке​​ текст. Чтобы отображались​​CTRL+C​
​Категория​

​щелкните стрелку рядом​​CTRL+1​​Формулы​
​ помощью функций​, а во втором —​Алексей​ работает:​ работаешь не с​
​ формулы МИН в​0mega​ — это значение​ результату =0. Всегда​Возвращает пустую ячейку, если​Категория​ нулевые значения, снимите​.​выберите элемент​
​ с командой​или на вкладке​.​ЕСЛИОШИБКА​равно​
​: а куда эту​=СУММ(ЕСЛИ(ЕЧИСЛО(C2:O2);(C2:O2);»»))​
​ диапазоном чисел, а​
​ массиве данных не​: =ЕСЛИ(ЕОШИБКА(A1/A2);»-«;A1/A2)​
​ формируется после действия​

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

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

​=ЕСЛИ(A2-A3=0;»-«;A2-A3)​​Общий​​К началу страницы​
​ лист.​.​и выберите пункт​щелкнуть​Контроль ошибок​НД​ поле справа введите​ формулы? или нет​ работает:​

​ (указывать через Ctrl)?​​ которых находится ДЕЛ/0?​ условное форматирование​ столбца «Продажи» на​ после операции вычитания​Возвращает дефис (-), если​. Чтобы снова отобразить​Потребность отображать нулевые значения​

​На листе выделите ячейку​​В поле​

​Параметры​​Формат​

​снимите флажок​​, как показано в​​ значение 0.​
​Казанский​=СУММ(ЕСЛИ(ЕЧИСЛО(C2;G2;K2;O2);(C2;G2;K2;O2);»»))​Пример: сумма 4-ёх​Заранее благодарна.​Выделяете нужный столбец​ показатель из столбца​ =10:​ значение равно нулю​ дату и время,​ (0) на листах​ A1 и нажмите​Тип​.​>​Включить фоновый поиск ошибок​ примере ниже.​Нажмите кнопку​

​: Часть до, часть​​Максим​ ячеек в одной​
​V​ указываете белые чернила.​ «План».​10-0=10​Дополнительные сведения об использовании​

​ выберите подходящий формат​​ возникает не всегда.​:)

planetaexcel.ru

Убрать ошибку деления на 0

​ клавиши​​введите​Перейдите на вкладку​
​Формат ячеек​.​Описание функции​
​Формат​ после​: Что бы формула​ из которых иногда​: вариант формула массива​=> меню: формат​
​Таким образом данную формулу​10-0=10​ этой функции см.​ даты и времени​
​ Требуют ли стандарты​

​CTRL+V​​0;-0;;@​​Разметка и формат​

​.​​Примечание:​
​ЕСЛИОШИБКА​

​.​​Можно макросом:​ работала пришлось сделать​ может быть #ДЕЛ/0​

​ вводится ctrl+shift+enter​​ => условное форматирование​ следует читать так:​:)
​∞ бесконечность.​

planetaexcel.ru

​ в статье Функция​

Источник

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

Скрыть ошибки с помощью функции ЕСЛИОШИБКА

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

В этом примере функция ВПР вернула значение ошибки # Н / Д.

# Н / Д ошибка отображается из ВПР

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

Функция ЕСЛИОШИБКА может обрабатывать любое значение ошибки, включая # ССЫЛКА !, # ЗНАЧЕНИЕ !, # ДЕЛ / 0! И другие. Требуется значение для проверки наличия ошибки и действия, которое следует выполнить вместо ошибки, если она обнаружена.

Требования к функции ЕСЛИОШИБКА

В этом примере функция ВПР — это значение, которое нужно проверить, а вместо ошибки отображается «0».

Функция ЕСЛИОШИБКА для отображения 0 вместо ошибки

Использование «0» вместо значения ошибки гарантирует, что все другие вычисления и потенциально другие функции, такие как диаграммы, будут работать правильно.

Значения ошибок скрыты с помощью ЕСЛИОШИБКА

Фоновая проверка ошибок

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

Зеленый индикатор возможной ошибки Excel

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

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

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

Смарт-тег для параметров ошибки

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

Варианты обработки ошибки

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

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

Если вы не хотите, чтобы Excel предупреждал вас об этих потенциальных ошибках, вы можете отключить их.

Щелкните Файл> Параметры. Затем выберите категорию «Формулы». Снимите флажок «Включить фоновую проверку ошибок», чтобы отключить все фоновые проверки ошибок.

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

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

По умолчанию все проверки ошибок включены, кроме «Формулы, относящиеся к пустым ячейкам».

Отключить определенные правила проверки ошибок

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

Дополнительная информация о проверке ошибок

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

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

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

Источник

Skip to content

Проверка данных в Excel: как сделать, использовать и убрать

Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи, исправлять и удалять проверку данных.

При настройке рабочей книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все введенные данные точны и непротиворечивы. Кроме того, вы можете захотеть разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Возможно, вы даже захотите предоставить заранее определенный список допустимых значений, чтобы исключить возможные ошибки. Проверка данных Excel позволяет выполнять все эти действия во всех версиях Microsoft Excel 365, 2019, 2016, 20013, 2010 и более ранних версиях.

Что такое проверка данных в Excel?

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

Вот лишь несколько примеров того, что может сделать проверка данных в Excel:

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

Например, вы можете настроить правило, которое ограничивает ввод данных 3-значными числами от 100 до 999. Если пользователь вводит что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что было сделано неправильно:

Как сделать проверку данных в Excel

Чтобы добавить проверку данных в Excel, выполните следующие действия.

1. Откройте диалоговое окно «Проверка данных».

Напомним, где находится кнопка проверки данных в Excel. Выбрав одну или несколько ячеек для проверки, перейдите на вкладку «Данные» > группа «Работа с данными» и нажмите кнопку «Проверка данных».

2. Создайте правило проверки Excel.

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

  • Значения — введите числа в поля критериев, как показано на снимке экрана ниже.
  • Ссылки на ячейки — создание правила на основе значения или формулы в другой ячейке.
  • Формулы — позволяют выразить более сложные условия.

В качестве примера создадим правило, разрешающее пользователям вводить только целое число от 100 до 999:

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

3. Подсказка по вводу (необязательно).

Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте соответствующую вкладку  и выполните следующие действия:

  • Убедитесь, что установлен флажок Отображать подсказку при выборе ячейки.
  • Введите заголовок и текст сообщения в соответствующие поля.
  • Нажмите OK, чтобы закрыть диалоговое окно.

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

4. Отображение предупреждения об ошибке (необязательно)

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

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

Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку «Сообщение об ошибке» и задайте следующие параметры:

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

И теперь, если пользователь введет недопустимые значения, Excel отобразит специальное предупреждение с объяснением ошибки (как показано в начале этого руководства).

Примечание. Если вы не введете собственное сообщение, появится стандартное предупреждение Stop со следующим текстом: Это значение не соответствует ограничениям проверки данных, установленным для этой ячейки.

Как настроить ограничения проверки данных Excel

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

Как вы уже знаете, критерии проверки определяются на вкладке «Параметры» диалогового окна «Проверка данных» (вкладка «Данные» > «Проверка данных»).

В первую очередь нужно настроить проверку типа записываемых данных.

К примеру, чтобы ограничить ввод данных целым или десятичным числом, выберите соответствующий элемент в поле Тип данных. Затем выберите один из следующих критериев в поле Данные:

  • Равно или не равно указанному числу
  • Больше или меньше указанного числа
  • Между двумя числами или вне, чтобы исключить этот диапазон чисел

Например, вот как выглядят ограничения по проверке данных Excel, которые допускают любое целое число больше 100:

Проверка даты и времени в Excel

Чтобы проверить даты, выберите «Дата» в поле «Тип данных», а затем выберите соответствующий критерий в поле «Значение». Существует довольно много предопределенных параметров на выбор: разрешить только даты между двумя датами, равные, большие или меньшие определенной даты и т. д.

Точно так же, чтобы проверить время, выберите Время в поле Значение, а затем определите необходимые критерии.

Например, чтобы разрешить только даты между датой начала в B1 и датой окончания в B2, примените это правило проверки даты Excel:

Разрешить только будни или выходные

Чтобы разрешить пользователю вводить даты только  будних или выходных дней, настройте пользовательское правило проверки на основе функции ДЕНЬНЕД (WEEKDAY).

Если для второго аргумента установлено значение 2, функция возвращает целое число в диапазоне от 1 (понедельник) до 7 (воскресенье). Так, для будних дней (пн-пт) результат формулы должен быть меньше 6, а для выходных (сб и вс) — больше 5.

Таким образом, разрешить только рабочие дни:

=ДЕНЬНЕД( ячейка ; 2)<6

Разрешить только выходные :

=ДЕНЬНЕД( ячейка ; 2)>5

Например, чтобы разрешить ввод только рабочих дней в ячейки C2:C8, используйте следующую формулу:

=ДЕНЬНЕД(A2;2)<6

Проверить даты на основе сегодняшней даты

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

Например, чтобы ограничить ввод данных через 6 дней (7 дней, включая сегодняшний день), мы можем использовать встроенное правило даты с критериями в виде формул:

  • Выберите Дата в поле Тип данных
  • Выберите в поле Значение – между
  • В поле Начальная дата введите выражение    =СЕГОДНЯ()
  • В поле Конечная дата  введите   =СЕГОДНЯ() + 6

Аналогичным образом вы можете ограничить пользователей вводом дат до или после сегодняшней даты. Для этого выберите меньше или больше, чем в поле Значение, а затем введите =СЕГОДНЯ() в  поле Начальная дата или Конечная дата соответственно.

Проверка времени на основе текущего времени

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

В поле Тип данных выберите Время .

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

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

Чтобы проверить дату и время на основе текущей даты и времени:

=ТДАТА()

Чтобы проверить время на основе текущего времени, используйте выражение:

=ВРЕМЯ(ЧАС(ТДАТА());МИНУТЫ(ТДАТА());СЕКУНДЫ(ТДАТА()))

Проверка длины текста

Чтобы разрешить ввод данных определенной длины, выберите Длина текста в поле Тип данных и укажите критерии проверки в соответствии с вашей бизнес-логикой.

Например, чтобы ограничить ввод до 15 символов, создайте такое правило:

Примечание. Параметр «Длина текста» ограничивает количество символов, но не тип данных. Это означает, что приведенное выше правило разрешает как текст, так и числа до 15 символов или 15 цифр соответственно.

Список проверки данных Excel (раскрывающийся список)

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

  1. Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
  2. На вкладке «Настройки» выберите «Список» в поле «Тип данных».
  3. В поле Источник введите элементы списка проверки Excel, разделенные точкой с запятой. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да; Нет; Н/Д.
  4. Убедитесь, что выбрана опция Список допустимых значений, чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
  5. Нажмите ОК.

Выпадающий список проверки данных Excel будет выглядеть примерно так:

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

Другие способы создания списка проверки данных в Excel

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

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

Во всех этих случаях вы просто записываете соответствующую ссылку на диапазон либо элемент таблицы в поле Источник.

Разрешить только числа

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

Удивительно, но ни одно из встроенных правил проверки данных Excel не подходит для очень типичной ситуации, когда вам нужно ограничить пользователей вводом только чисел в определенные ячейки. Но это можно легко сделать с помощью пользовательской формулы проверки данных, основанной на функции ЕЧИСЛО(), например:

=ЕЧИСЛО(C2)

Где C2 — самая верхняя ячейка диапазона, который вы хотите проверить.

Примечание. Функция ЕЧИСЛО допускает любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, а также даты и время, которые также являются числами в Excel.

Разрешить только текст

Если вы ищете обратное — разрешить только текстовые записи в заданном диапазоне ячеек, то создайте собственное правило с функцией ЕТЕКСТ (ISTEXT), например:

=ЕТЕКСТ(B2)

Где B2 — самая верхняя ячейка выбранного диапазона.

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

Если все значения в определенном диапазоне должны начинаться с определенного символа или подстроки, выполните проверку данных Excel на основе функции СЧЁТЕСЛИ с подстановочным знаком:

=СЧЁТЕСЛИ(A2; » текст *»)

Например, чтобы убедиться, что все идентификаторы заказов в столбце A начинаются с префикса «AРТ-», «арт-», «Aрт-» или «aРт-» (без учета регистра), определите пользовательское правило с этой проверкой данных.

=СЧЁТЕСЛИ(A2;»АРТ-*»)

Формула проверки с логикой ИЛИ (несколько критериев)

В случае, если есть 2 или более допустимых префикса, добавьте несколько функций СЧЁТЕСЛИ, чтобы ваше правило проверки данных Excel работало с логикой ИЛИ:

=СЧЁТЕСЛИ(A2;»АРТ-*»)+СЧЁТЕСЛИ(A2;»АБВ-*»)

Проверка ввода с учетом регистра

Если регистр символов имеет значение, используйте СОВПАД  (EXACT) в сочетании с функцией ЛЕВСИМВ, чтобы создать формулу проверки с учетом регистра для записей, начинающихся с определенного текста:

=СОВПАД(ЛЕВСИМВ(ячейка; число_символов); текст)

Например, чтобы разрешить только те коды заказов, которые начинаются с «AРТ-» (ни «арт-», ни «Арт-» не допускаются), используйте эту формулу:

=СОВПАД(ЛЕВСИМВ(A2;4);»АРТ-«)

В приведенной выше формуле функция ЛЕВСИМВ извлекает первые 4 символа из ячейки A2, а СОВПАД выполняет сравнение с учетом регистра с жестко заданной подстрокой (в данном примере «AРТ-«). Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит успешно; в противном случае возвращается ЛОЖЬ и проверка завершается неудачно.

Разрешить только значения, содержащие определенный текст

Чтобы разрешить ввод значений, которые содержат определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ЕЧИСЛО (ISNUMBER) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH) в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:

Проверка без учета регистра:

ЕЧИСЛО(ПОИСК( текст ; ячейка ))

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

ЕЧИСЛО(НАЙТИ( текст ; ячейка ))

В нашем примере, чтобы разрешить только записи, содержащие текст «AР» в ячейках A2: A8, используйте одну из следующих формул, создав правило проверки в ячейке A2:

Без учета регистра:

=ЕЧИСЛО(ПОИСК(«ар»;A2))

С учетом регистра:

=ЕЧИСЛО(НАЙТИ(«АР»;A2))

Формулы работают по следующей логике:

Вы ищете подстроку «AР» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке. Если текст не найден, возвращается ошибка. Если поиск успешен и «АР» найден в ячейке, мы получаем номер позиции в тексте, где эта подстрока была найдена. Далее функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае, если подстроку не удалось найти, результатом будет ошибка и ЕЧИСЛО возвращает ЛОЖЬ. Запись не будет разрешена в ячейке.

Разрешить только уникальные записи и запретить дубликаты

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

=СЧЁТЕСЛИ( диапазон ; самая верхняя_ячейка )<=1

Например, чтобы убедиться, что в ячейки с A2 по A8 вводятся только уникальные идентификаторы заказов, создайте настраиваемое правило со следующей формулой проверки данных:

=СЧЁТЕСЛИ($A$2:$A$8; A2)<=1

При вводе уникального значения формула возвращает ИСТИНА, и проверка проходит успешно. Если такое же значение уже существует в указанном диапазоне (счетчик больше 1), функция СЧЁТЕСЛИ возвращает ЛОЖЬ, и вводимые данные не проходят проверку.

Обратите внимание, что мы фиксируем диапазон абсолютными ссылками на ячейки (A$2:$A$8) и используем относительную ссылку для верхней ячейки (A2), чтобы формула корректно изменялась для каждой ячейки в проверяемом диапазоне.

Как отредактировать проверку данных в Excel

Чтобы изменить правило проверки Excel, выполните следующие действия:

  1. Выберите любую из проверенных ячеек.
  2. Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
  3. Внесите необходимые изменения.
  4. Установите флажок Применить эти изменения ко всем другим ячейкам с теми же параметрами, чтобы скопировать внесенные вами изменения во все остальные ячейки с исходными критериями проверки.
  5. Нажмите OK, чтобы сохранить изменения.

Например, вы можете отредактировать список проверки данных Excel, добавив или удалив элементы из поля «Источник», и применить эти изменения ко всем другим ячейкам, содержащим тот же раскрывающийся список.

Как скопировать правило проверки данных Excel в другие ячейки

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

Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

  1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + С , чтобы скопировать его.
  2. Выберите другие ячейки, которые вы хотите проверить. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
  3. Щелкните выделенный фрагмент правой кнопкой мыши, выберите «Специальная вставка» и выберите параметр «Условия на значения».

Либо используйте комбинацию клавиш Ctrl + Alt + V,  и затем — Н.

  1. Нажмите ОК.

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

Как найти ячейки с проверкой данных в Excel

Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите на вкладку «Главная» > группа «Редактирование» и нажмите «Найти и выделить» > «Проверка данных» :

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

Как убрать проверку данных в Excel

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

Способ 1: Обычный способ отключить проверку данных

Обычно, чтобы удалить проверку данных на листах Excel, выполните следующие действия:

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

Советы:

  1. Чтобы удалить проверку данных из всех ячеек на текущем листе, используйте инструмент «Найти и выделить», чтобы выбрать все ячейки с проверкой.
  2. Чтобы удалить определенное правило проверки данных, выберите любую ячейку с этим правилом, откройте диалоговое окно «Проверка данных», установите флажок «Применить эти изменения ко всем другим ячейкам с такими же настройками» и нажмите кнопку «Очистить все».

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

Способ 2: Удалить правила проверки данных при помощи Специальной вставки

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

  1. Выберите пустую ячейку без проверки данных и нажмите Ctrl + С, чтобы скопировать ее в буфер обмена.
  2. Выберите ячейки, в которых вы хотите отключить проверку данных.
  3. Нажмите Ctrl + Alt + V, и затем Н. Эта комбинация клавиш вызовет «Специальная вставка» > «Проверка данных».
  4. Нажимаем ОК.

Советы по проверке данных в Excel

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

Проверка данных на основе другой ячейки

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

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

Например, чтобы разрешить любое целое число, находящееся между 100 и 999, выберите критерии «минимум» и «максимум» в вкладке «Параметры» и введите в них адреса ячеек с этими числами:

Рис18

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

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можете ввести формулу =СЕГОДНЯ() непосредственно в поле Конечная дата, что будет иметь тот же эффект.

Правила проверки на основе формул

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

Например, чтобы ограничить ввод минимальным и максимальным значениями в существующем списке чисел, скажем, A1:A10, используйте следующие формулы:

=МИН($С$2:$С$10)

=МАКС($С$2:$С$10)

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

Как найти неверные данные на листе

Хотя Microsoft Excel позволяет применять проверку данных к ячейкам, в которых уже есть данные, он не уведомит вас, если некоторые из уже существующих значений не соответствуют критериям проверки.

Чтобы найти недействительные данные, которые попали в ваши рабочие листы до того, как вы добавили проверку данных, перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Обвести неверные данные».

Это выделит все ячейки, которые не соответствуют критериям проверки.

Как только вы исправите неверную запись, отметка автоматически исчезнет. Чтобы удалить все отметки, перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Удалить обводку неверных данных».

Как поделиться книгой с проверкой данных

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

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

Почему проверка данных Excel не работает?

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

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

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

Хотя нет способа отключить клавиши копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого выберите «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» и снимите флажок «Включить маркер заполнения и перетаскивания ячеек».

Проверка данных Excel не активна

Кнопка «Проверка данных» не активна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

Неправильные формулы проверки данных

При проверке данных на основе формул в Excel необходимо проверить три важные вещи:

  • Формула проверки не возвращает ошибок.
  • Формула не ссылается на пустые ячейки.
  • Используются правильные ссылки на ячейки.

Ручной пересчет формул включен

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр расчета Excel на автоматический, перейдите на вкладку «Формулы» > группу «Расчет», нажмите кнопку «Параметры расчета» и выберите «Автоматически».

Проверьте правильность формулы проверки данных

Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, такую ​​как #Н/Д, #ЗНАЧ или #ДЕЛ/0!.

Если вы создаете пользовательское правило , формула должна возвращать логические значения ИСТИНА и ЛОЖЬ или приравненные к ним значения 1 и 0 соответственно.

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

Формула проверки данных не должна ссылаться на пустую ячейку

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

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

Абсолютные и относительные ссылки на ячейки в формулах проверки

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

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

Чтобы лучше проиллюстрировать это, рассмотрим следующий пример.

Предположим, вы хотите ограничить ввод данных в ячейках с A2 по A8 целыми числами от 100 (минимальное значение) до 999. Чтобы проще было при необходимости изменить критерии проверки, используем ссылки на ячейки с этими значениями, как показано на скриншоте ниже:

Это правило будет корректно выполняться только для первой ячейки диапазона – A2. Для ячейки A3 критерии максимума и минимума изменятся на E3 и F3. Поскольку эти ячейки пусты, то условие ввода теперь – любое число больше либо меньше нуля. Правда, символьные значения и дробные числа вы вводить не сможете, так как продолжает действовать ограничение на тип данных – целые числа.

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы зафиксировать их: =$E$2 и $F$2. Или используйте клавишу  F4 для переключения между различными типами ссылок.

Вот как вы можете добавить и использовать проверку данных в Excel. Я благодарю вас за чтение и надеюсь ещё увидеть вас в нашем блоге!

Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ятрогенная патология врачебные ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Эксель не подчеркивает ошибки красным

    • ➤