Проверьте, существует ли значение в диапазоне в Excel и Google Sheets
В этой статье мы рассмотрим наиболее часто используемые формулы в Excel и Google Таблицах, чтобы проверить, существует ли значение в диапазоне ячеек.
Независимо от того, являетесь ли вы опытным пользователем или только начинаете изучать свою любимую программу для работы с электронными таблицами, знание того, как быстро определить, присутствует или отсутствует конкретное значение в заданном диапазоне, может сэкономить много времени и усилий при анализе данных. Это может быть особенно полезно при работе с большими наборами данных или при проведении исследований. В Microsoft Excel и Google Sheets есть несколько формул для выполнения этой задачи. С помощью нашего пошагового руководства вы сможете легко применить их к своим электронным таблицам независимо от структуры и размера вашего набора данных.
Если значение существует в диапазоне в Excel
Чтобы проверить, существует ли определенное значение в диапазоне ячеек, и если оно существует, то возвращает некоторый текст, а если нет, то возвращает другой текст, вы можете использовать комбинацию функций ЕСЛИ и СЧЁТЕСЛИ.
ЕСЛИ(СЧЁТЕСЛИ(диапазон, значение)>0, “Да”, “Нет”)
В этой формуле СЧЁТЕСЛИ подсчитывает количество вхождений заданного значения в диапазоне. Если количество больше нуля, оператор IF возвращает «Да». Если значение не находится в пределах диапазона, функция СЧЁТЕСЛИ возвращает ноль, а ЕСЛИ выводит «Нет».
Эта общая формула прекрасно работает во всех возможных сценариях.
Проверить, существует ли значение в столбце
Чтобы проверить, присутствует ли значение в C3 в столбце A (точнее, в диапазоне A3: A20), вы можете использовать эту формулу:
=ЕСЛИ(СЧЁТЕСЛИ($A$3:$A$20, C3)>0, “Да”, “Нет”)
Обратите внимание, что мы блокируем ссылку на диапазон ($A$3:$A$20), используя знак $, чтобы формула правильно копировалась в ячейки D4:D7:
Проверить, существует ли значение в строке
Чтобы узнать, появляется ли значение в B5 в строке B2:I2, формула принимает следующий вид:
=ЕСЛИ(СЧЁТЕСЛИ($B$2:$I$2, B5)>0, “Да”, “Нет”)
Проверить, существует ли значение в диапазоне
Чтобы проверить, встречается ли конкретное значение в двумерном диапазоне, укажите ссылку на соответствующий диапазон в первый аргумент функции СЧЁТЕСЛИ. Например:
=ЕСЛИ(СЧЁТЕСЛИ($A$3:$B$11, D3)>0, “Да”, “Нет”)
Дополнительные формулы для поиска значения поиска в диапазоне
Формула ЕСЛИ + СЧЁТЕСЛИ — это самый простой, но не единственный способ определить, появляется ли определённое значение в диапазоне. Ниже приведены несколько альтернативных решений.
ISNUMBER и MATCH
Другой способ проверить, входит ли значение в диапазон, — это использовать ПОИСКПОЗ вместе с функцией ЕЧИСЛО. Например:
=ЕСЛИ(ЧИСЛО(ИСПОЛНЕНИЕ(C3, $A$3:$A$20, 0)), “Да”, “Нет”)
Функция ПОИСКПОЗ возвращает позицию искомого значения (C3) в диапазоне (A3:A20) в виде числа; ошибка #N/A, если совпадение не найдено. Функция ISNUMBER преобразует число в TRUE, а ошибку в FALSE. Обертывание конструкции в ЕСЛИ дает в конечном результате «Да» или «Нет».
ИСНА и ВПР
Чуть более сложное, но все же работающее решение — использование функций ЕСЛИ, ISNA и ВПР в одной формуле:
=ЕСЛИ(ИСНА(ВПР(C3, $A$3:$A$20, 1, ЛОЖЬ)), “Нет”, “Да”)
Здесь функция ВПР ищет искомое значение в указанном массиве и возвращает точное совпадение (ЛОЖЬ) из того же столбца (1). Если совпадений не найдено, возвращается ошибка #Н/Д. (В Excel 365 и Excel 2021 вместо ВПР можно использовать ПРЛПР.) Функция ЕСНА проверяет результат на наличие ошибок #Н/Д и возвращает значение ИСТИНА, если оно положительное, и ЛОЖЬ, если оно положительное. Наконец, функция ЕСЛИ возвращает «Нет» в случае ИСТИНА (нет совпадения) и «Да» в случае ЛОЖЬ (совпадение).
Дополнительные сведения см. в разделе Как использовать ISNA с функцией ВПР в Excel.
ИСНА и МАТЧ
Это решение очень похоже на предыдущее, за исключением того, что вы используете ПОИСКПОЗ вместо ВПР для проверки диапазонов на совпадения:
=ЕСЛИ(ИСНА(ПОИСКПОЗ(C3, $A$3:$A$20, 0)), “Нет”, “Да”)
Кончик. Если вы хотите найти искомое значение в одном столбце и вернуть соответствующее значение из другого столбца, используйте функцию ВПР или ССПР в ее базовой форме.
Если значение существует в диапазоне в Google Sheets
В Google Sheets вы можете проверить, существует ли значение в диапазоне, используя те же самые формулы, которые мы использовали в Excel.
Например, чтобы определить, находится ли значение в D3 в диапазоне A3:B11, формула в E4 выглядит следующим образом:
=ЕСЛИ(СЧЁТЕСЛИ($A$3:$B$11, D3)>0, “Да”, “Нет”)
Вы можете найти еще несколько формул в нашем образце электронной таблицы, который доступен в конце этого руководства.
Проверить, существует ли значение в диапазоне — частичное совпадение
Чтобы увидеть, содержит ли какая-либо ячейка в диапазоне заданную подстроку, поместите подстановочный знак (звездочку) с обеих сторон от искомого значения, чтобы СЧЕТЕСЛИ искал его в любом месте ячейки:
ЕСЛИ(СЧЁТЕСЛИ(диапазон, “*значение*”)>0, “Да”, “Нет”)
Например, приведенная ниже формула проверит, содержит ли какая-либо ячейка в диапазоне A3:B11 слово «яблоко»:
=ЕСЛИ(СЧЁТЕСЛИ(A3:B11, “*яблоко*”)>0, “Да”, “Нет”)
Если вы имеете в виду определенную ячейку вместо жестко заданного значения, соедините звездочки до и после ссылки на ячейку следующим образом:
=ЕСЛИ(СЧЁТЕСЛИ($A$3:$B$11,”*”&D3&”*”)>0, “Да”, “Нет”)
Выделите значения, которые существуют в диапазоне
Более наглядным способом определения значений, существующих в заданном диапазоне, может быть выделение их с помощью условного форматирования Excel. Шаги:
- Выберите ячейки со значениями, которые вы хотите найти в диапазоне (в нашем случае C3: C7).
- На вкладке Главная в группе Стили щелкните Условное форматирование > Новое правило.
- В окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек».
- Введите одну из этих формул в поле Формат значений, где эта формула верна:
=ПОИСКПОЗ(C3, $A$3:$A$20, 0)
=СЧЁТЕСЛИ($A$3:$A$20, C3)>0
Где C3 — самая верхняя ячейка с целевыми значениями, а $A$3:$A$20 — диапазон для проверки.
- Нажмите кнопку Формат… и настройте желаемое форматирование.
- Нажмите OK дважды, чтобы закрыть оба диалоговых окна.
Сделанный! Подсвечиваются все значения, существующие в диапазоне (точное совпадение):
Чтобы выделить частично совпадающие значения, измените формулу, как показано ниже, — соедините звездочку с обеих сторон ссылки на искомое значение:
=ПОИСКПОЗ(“*”&C3&”*”, $A$3:$A$20, 0)
=СЧЁТЕСЛИ($A$3:$A$20, “*”&C3&”*”)>0
Кончик. Эту же формулу можно использовать с условным форматированием Google Таблиц, чтобы выделить точные или частичные совпадения в диапазоне в электронных таблицах Google.
Теперь вы обладаете знаниями и навыками, чтобы легко и уверенно проверять, существует ли значение в столбце, строке или диапазоне. Спасибо за чтение!
Практические рабочие тетради
Проверьте, существует ли значение в диапазоне в Excel (файл .xlsx)
Найти, существует ли значение в диапазоне в Google Sheets (онлайн лист)
