Как добавить исключение в excel

Обновлено: 07.07.2024

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

Выделите ячейку или целую область, которую Excel должен проверить при вводе данных. Теперь перейдите к ленте меню «Данные | Работа с данными | Проверка данных». В следующем окне установите условия проверки. В поле «Тип данных» выберите между такими опциями, как «Целое число», «Действительное», «Список», «Дата», «Время», «Длина текста» или «Другой».

При определении условия допустим любой тип данных. Если выбрать опцию «Другой», то можно еще более точно установить значения. Так, например, выставить дату только от 01.01.2017 до 31.12.2017. Если нужно установить точные данные, выберите опцию «Список» и введите данные, разделяя их точкой и с запятой. При определении условия допустим любой тип данных. Если выбрать опцию «Другой», то можно еще более точно установить значения. Так, например, выставить дату только от 01.01.2017 до 31.12.2017. Если нужно установить точные данные, выберите опцию «Список» и введите данные, разделяя их точкой и с запятой.

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

Теперь рядом с указанным полем при проверке данных появилось развертывающееся меню, из которого можно выбирать необходимое значение. Если требуется удалить данные для проверки, выделите ячейки, которые Excel больше не должен проверять. Теперь перейдите к ленте меню «Данные | Работа с данными | Проверка данных» и нажмите на «Очистить все».

Исключите ввод ошибочных данных в Excel. Проверка данных в Excel поможет пользователям избежать ошибок в работе с таблицами Исключите ввод ошибочных данных в Excel. Проверка данных в Excel поможет пользователям избежать ошибок в работе с таблицами

Предположим, у вас есть два списка данных, как показано на скриншоте слева. Теперь вам нужно удалить или исключить имена в столбце A, если имя существует в столбце D. Как этого добиться? А что, если два списка находятся на двух разных листах? Эта статья предлагает вам два метода.

Исключить значения в одном списке из другого с помощью формулы

Для этого вы можете применить следующие формулы. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку, которая находится рядом с первой ячейкой списка, который вы хотите удалить, затем введите формулу. = СЧЁТЕСЛИ ($ D $ 2: $ D $ 6; A2) в панель формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ D $ 2: $ D $ 6 - это список, на основе которого вы удаляете значения, A2 - это первая ячейка списка, который вы собираетесь удалить. Пожалуйста, измените их по своему усмотрению.

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

3. Продолжайте выбирать список результатов, затем щелкните Данные > Сортировка от А до Я.

Затем вы можете увидеть, что список отсортирован, как показано на скриншоте ниже.

4. Теперь выберите все строки имен с результатом 1, щелкните правой кнопкой мыши выбранный диапазон и нажмите Удалить чтобы удалить их.

Теперь вы исключили значения из одного списка на основе другого.

Внимание: Если «список для удаления» находится в диапазоне A2: A6 другого листа, такого как Sheet2, примените эту формулу = IF (ISERROR (VLOOKUP (A2; Sheet2! $ A $ 2: $ A $ 6,1; FALSE)), «Сохранить», «Удалить») получить все Сохранить и Удалить результатов, отсортируйте список результатов от A до Z, а затем вручную удалите все строки имен, содержащие результат удаления на текущем листе.

Быстро исключать значения в одном списке из другого с помощью Kutools for Excel

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

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки. Смотрите скриншот:

2. в Выберите одинаковые и разные ячейки диалоговое окно, вам необходимо:

2.1 Выберите список, из которого вы удалите значения в Найдите значения в коробка;
2.2 Выберите список, значения которого вы удалите, на основе Согласно информации коробка;
2.3 выберите Однокамерная вариант в на основании раздел;
2.4 Щелкните значок OK кнопка. Смотрите скриншот:

3. Затем появляется диалоговое окно, в котором указывается, сколько ячеек было выбрано, нажмите OK кнопку.

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

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

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

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

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

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))

Имя аргумента

лог_выражение (обязательно)

Условие, которое нужно проверить.

значение_если_истина (обязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

значение_если_ложь (необязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

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

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Примеры

Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.

Ниже приведены формулы с расшифровкой их логики.

Если A2 (25) больше нуля и B2 (75) меньше 100, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба условия имеют значение ИСТИНА, поэтому функция возвращает значение ИСТИНА.

Если A3 ("синий") = "красный" и B3 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только одно условие имеет значение ИСТИНА, поэтому возвращается значение ЛОЖЬ.

Если A4 (25) больше нуля или B4 (75) меньше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только первое условие имеет значение ИСТИНА, но поскольку для ИЛИ требуется, чтобы только один аргумент был истинным, формула возвращает значение ИСТИНА.

Если значение A5 ("синий") равно "красный" или значение B5 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае второй аргумент имеет значение ИСТИНА, поэтому формула возвращает значение ИСТИНА.

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 ("синий") НЕ равно "красный", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

Если A3 больше B2 И меньше C2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба аргумента истинны, поэтому формула возвращает значение ИСТИНА.

Если A4 больше B2 ИЛИ меньше B2+60, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае первый аргумент равен ИСТИНА, а второй — ЛОЖЬ. Так как для оператора ИЛИ требуется, чтобы один из аргументов был истинным, формула возвращает значение ИСТИНА. Если вы используете мастер вычисления формул на вкладке "Формулы", вы увидите, как Excel вычисляет формулу.

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Использование операторов И, ИЛИ и НЕ с условным форматированием

Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.

На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

Вот как будут выглядеть формулы для примеров с датами:

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

Если A3 больше B2 И меньше C2, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A4 больше B2 ИЛИ меньше B2 + 60, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.

Примечание: Распространенная ошибка — ввод формулы в условное форматирование без знака "равно" (=). При этом вы увидите, что в диалоговом оке Условное форматирование в формулу будут добавлены знаки "равно" и кавычка: ="ИЛИ(A4>B2;A4<B2+60)",поэтому вам потребуется удалить кавычка, чтобы формула правильно отреагирует.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Функция XOR возвращает логическое монопольно или все аргументы.

Синтаксис

ИСКЛИЛИ(логическое_значение1;[логическое значение2];. )

Аргументы функции ИСКЛИЛИ описаны ниже.

Логическое_значение1, логическое_значение2,… Логическое_значение 1 — это обязательный аргумент, последующие логические значения необязательные. От 1 до 254 проверяемых условий, которые могут иметь значение ИСТИНА или ЛОЖЬ и могут быть логическими значениями, массивами или ссылками.

Замечания

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

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

Можно воспользоваться функцией ИСКЛИЛИ в качестве формулы массива, чтобы проверить, имеется ли в нем то или иное значение.

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

Аргумент функции ИСКЛИЛИ имеет значение ИСТИНА, если число вводов ИСТИНА нечетное, и значение ЛОЖЬ, если число вводов ИСТИНА четное.

Пример

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

Так как оба теста оцениваются как истина, возвращается ложь.

Так как все результаты проверки возвращаются как ЛОЖЬ, возвращается ложь. Чтобы получить истина, по крайней мере один из результатов проверки должен получить результат ИСТИНА.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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