Excel: как проверить, содержит ли диапазон определенное значение
Вы можете использовать следующие формулы, чтобы проверить, содержит ли диапазон в Excel определенное значение:
Способ 1: проверить, содержит ли диапазон значение (возвратить TRUE или FALSE)
Способ 2: проверить, содержит ли диапазон частичное значение (возвратить TRUE или FALSE)
Способ 3: проверить, содержит ли диапазон значение (возврат пользовательского текста)
В следующих примерах показано, как использовать каждую формулу на практике со следующим набором данных в Excel:
Пример 1: проверьте, содержит ли диапазон значение (возврат TRUE или FALSE)
Мы можем использовать следующую формулу, чтобы проверить, содержит ли диапазон названий команд значение «Mavericks»:
На следующем снимке экрана показано, как использовать эту формулу на практике:
Формула возвращает FALSE , так как значение «Mavericks» не существует в диапазоне A2:A15 .
Пример 2. Проверка того, содержит ли диапазон частичное значение (возврат TRUE или FALSE)
Мы можем использовать следующую формулу, чтобы проверить, содержит ли диапазон названий команд частичное значение «avs» в любой ячейке:
На следующем снимке экрана показано, как использовать эту формулу на практике:
Формула возвращает TRUE , поскольку частичное значение «avs» встречается по крайней мере в одной ячейке в диапазоне A2:A15 .
Пример 3. Проверка наличия значения в диапазоне (возврат пользовательского текста)
Мы можем использовать следующую формулу, чтобы проверить, содержит ли диапазон названий команд значение «Шершни» в какой-либо ячейке, и вернуть в результате «Да» или «Нет»:
На следующем снимке экрана показано, как использовать эту формулу на практике:
Формула возвращает Нет , так как значение «Шершни» не встречается ни в одной ячейке в диапазоне A2:A15 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как проверить есть ли значение в столбце excel
Предположим, у меня есть два столбца данных, которые содержат некоторые повторяющиеся значения в обоих столбцах, как показано на скриншоте слева. А теперь я хочу выяснить, существуют ли значения в столбце A и в столбце C. Сравнивать их одно за другим — не лучший метод, здесь я могу рассказать о некоторых удобных приемах решения этой проблемы в Excel.
Проверьте, существует ли значение в другом столбце с формулой
Чтобы проверить, находятся ли значения в другом столбце Excel, вы можете применить следующую формулу для работы с этим заданием.
1. Во-первых, вы можете скопировать два столбца данных и вставить их в столбец A и столбец C отдельно на новом листе, оставив столбец B пустым, чтобы ввести следующую формулу.
2. Введите эту формулу: = ЕСЛИ (ЕСТЬ ОШИБКА (ВПР (A4; $ C $ 4: $ C $ 14; 1; ЛОЖЬ)); «Не существует»; «Существует») в ячейку B2 и нажмите Enter ключ, см. снимок экрана:
Внимание: в приведенной выше формуле, A4 — это ячейка первого столбца, которую вы хотите проверить, находится ли она в другом столбце, и 4 канадских доллара: 14 канадских долларов столбец, с которым вы хотите сравнивать.
3. Затем выберите ячейку B4 и перетащите маркер заполнения к ячейкам, которые вам нужны, чтобы применить эту формулу, вы получите следующий результат: Существовать означает, что значение в списке A существует и в списке B, и Не существует указывает, что значение отсутствует в списке B.
Kutools for Excel‘s Выберите одинаковые и разные ячейки Эта функция может помочь вам выбрать и выделить одинаковые и разные значения ячеек между двумя столбцами по мере необходимости. Нажмите, чтобы скачать Kutools for Excel!
Проверьте, существует ли значение в другом столбце, и выделите их с помощью условного форматирования
В Excel вы также можете выделить повторяющиеся значения в одном столбце, если они обнаружены в другом столбце, с помощью условного форматирования. Пожалуйста, сделайте так:
1. Выделите ячейки в списке A, которые вы хотите выделить также ячейки, из которых выходят значения, в списке B.
2. Затем нажмите Главная > Условное форматирование > Новое правило, В Новое правило форматирования диалоговое окно, щелкните Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила раздел, а затем введите эту формулу = ПОИСКПОЗ (A4; $ C $ 4: $ C $ 14,0) в Формат значений, где эта формула истинна текстовое поле, см. снимок экрана:
3. Затем нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговом окне и выберите нужный цвет под Заполнять вкладку, см. снимок экрана:
4. Затем нажмите OK > OK чтобы закрыть диалоговые окна, и теперь вы можете видеть, что значения как в списке A, так и в списке B выделены из списка A, см. снимок экрана:
Проверьте, существует ли значение в другом столбце, и сразу затените их с помощью Kutools for Excel
Работы С Нами Kutools for ExcelАвтора Выберите одинаковые и разные ячейки функция, мы можем быстро выбрать или заштриховать дубликаты или различия в двух столбцах. Это поможет вам с первого взгляда найти повторяющиеся или уникальные значения.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки, см. снимок экрана:
2. В Выберите одинаковые и разные ячейки диалоговом окне выполните следующие действия:
A: Нажмите с Найдите значения в: (Диапазон A) раздел, чтобы выбрать столбец данных, который вы хотите сравнить с другим столбцом. И нажмите под Согласно: (Диапазон B) раздел, чтобы выбрать ячейки, с которыми вы хотите сравнивать.
B: Выбрать Каждый ряд под на основании .
C: Уточнить Те же ценности из Арендовать .
D: Вы можете заполнить цвет фона или цвет шрифта теми же значениями, которые вам нужны под Обработка результатов раздел, выберите цвет, который вам нравится.
3. После завершения настроек нажмите Ok все значения в столбце A и столбце C затенены в столбце A, как показано на следующем снимке экрана:
Внимание: Если вы хотите заполнить те же значения в столбце C, вам просто нужно заменить Найдите значения в данные столбца и Согласно информации столбец в диалоговом окне, и вы получите следующие результаты:
Проверьте, существует ли значение в другом столбце, и сразу затените их с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Как проверить наличие значения ячейки в столбце таблицы Excel?
Что бы проверить наличие значения ячейки в определенном столбце таблицы Excel воспользуемся функцией ВПР.
Например имеем 2 столбца: 
Нам надо проверить наличие значений “Столбца 2” в “Столбце 1”.
Для этого промаркируем “Стобец 1”, например буквой “х”. Просто поставим эту букву напротив. Выглядеть это будет так: 
Теперь сможем сделать проверку, для этого напротив первого значения “Столбца 2” пишем формулу =ВПР(E2;A:B;2;0) 
Итак разберём формулу:
- Е2 – означает, что мы хотим искать первое значение “Столбца 2”;
- А:В – означает, что мы хотим искать в этих двух столбцах таблицы;
- 2 – означает, что если найдётся совпадение, вывести значение второго столбца “В”, а именно нашу букву “х”;
- 0 – означает, что должно быть точное совпадение.
Теперь протянем формулу ниже для каждого значения “Столбец 2” и получим результат: 
Видим, что там где есть “х” – совпадение есть. Там где “#Н/Д” – совпадений нет. Вот и всё.
Если есть вопросы, задавайте в комментариях.
Ещё статьи по теме:
Оставь свой коммент
Привет читатель! В сложившейся ситуации доходы с рекламы сократились, оплачивать обслуживание сайта приходится из собственного кармана. Если ты получил тут полезную информацию и тебе не безразлично, прошу финансово помочь. Можно задонатить 1$ и более. Спасибо!
Поиск нужных данных в диапазоне
Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.
Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:
Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.
Задача решается при помощи двух функций:
=ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16 . Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.
Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).
Ссылки по теме
Не за что!
А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается.
Спасибо.
Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным — это что?
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900; $C$700:$F$900; ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас — четыре. Что-то лишнее
При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.
При поиске ближайшего наибольшего — по убыванию.
Спасибо Большое Вам, Николай! Не только за этот пример, а в общем — за весь Сайт.
Николай, спасибо за урок!
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.
Добрый день всем!
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!
Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!
Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму — или криво офис на комп встал. или одно из двух. то #ссылку возвращает то #н/д .
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало — непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.
Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.
Функция по потенциалу понравилась, но как отрабатывает конкретно у меня — нет.
ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.
Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.
Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ,
которая подарила мне уйму свободного времени. Очень грамотно.8)
Николай, большое спасибо за Ваши уроки!
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ
Ещё раз благодарю Вас!
С уважением, Вячеслав!
Ну да, хорошее решение, чтобы не считать номер колонки вручную
Здравствуйте, а если шапка таблицы многослойная решение есть?
Многослойная шапка — например в строке 2 условия в столбце 2 условия а не по одному.
т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!
Вы так быстро ответили , что я таблицу неуспел нарисовать ))
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
| Усл 2 | q | q | q | h | h | h | |||
| Усл 4 | x | y | z | x | y | z | |||
| список выбора условия 1 | k | Усл 1 | Усл 3 | ||||||
| список выбора условия 2 | q | j | b | а | б | в | г | д | е |
| список выбора условия 3 | j | s | е | ж | з | и | к | л | |
| список выбора условия 4 | j | f | м | н | о | п | р | с | |
| решение | т | k | b | т | у | ф | х | ц | ч |
| k | s | ш | щ | ъ | ы | ь | э | ||
| k | f | ю | я | — | — | — | — |
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
| Усл 2 | q | q | q | h | h | h | |||
| Усл 4 | x | y | z | x | y | z | |||
| список выбора условия 1 | k | Усл 1 | Усл 3 | ||||||
| список выбора условия 2 | h | j | b | а | б | в | г | д | е |
| список выбора условия 3 | s | j | s | е | ж | з | и | к | л |
| список выбора условия 4 | j | f | м | н | о | п | р | с | |
| решение | #ССЫЛ! | k | b | т | у | ф | х | ц | ч |
| должно быть ы | k | s | ш | щ | ъ | ы | ь | э | |
| k | f | ю | я | — | — | — | — |
Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее
Сам себе и отвечу . =ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
(Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано «»;) в строке.
И тут появляется «НО» — если в строке D1:D13 пустые ячейки появляются пару раз (например:
D1 D2 D3 D4 D5 D6 D7 D8 . D13
(авс) (авс) (авс) ( ) ( ) (авс) (авс) ( ) . (авс), то формула ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) — значение (авс) — ВВОД (Shift+Enter). А как это записать формулой?
| если в строке D1:D13 |
D1:D13 — это столбец, а не строка
Если вы имели ввиду вопрос «как сделать так, чтобы формула находила не первое встретившееся, а последнее значение», то тут проще всего макросом, наверное — писать на VBA функцию аналогичную ВПР.
Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы.
Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению. ):
яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 — это тоже делает отчет удобнее. Теперь можно «играться» с разными значениями, просто выбирая их из выпадающего списка.
Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм
Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:
| Вася | Миша | 2 |
| Миша | ||
| Маша | ||
| Жора | ||
| Миша | ||
| Валя |
Пример (скрин) прилагается. Бьюсь несколько дней, но никак не получается, чтобы найти функцию, указывающую последний порядковый номер соответствующего аргумента в массиве. В нашем примере это «5». Подскажите, пожалуйста, функцию для решения этой задачи. Заранее спасибо!
Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?
При использовании этой формулы в работе с датами, выдает результат 0.1.1900 ( при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов)
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?
P/S На функцию еслиошибка реагирует только #Н/Д , а пустую ячейку все равно выдает как 0.1.1900
СПАСИБО:<>
Добрый день!
Помогите пожалуйста по стоить формулу.
— есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним
-среднее выводиться с отдельную ячейку С69
задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин
B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)
Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.
Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20.
Т.е. как-то так: СУММЕСЛИ(А2:А30; И(«>=10»; «<20») )
Понимаю, что написанное красным неправильно. подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20.
Большое спасибо! Очень помогли!
Большое вам человеческое СПАСИБО!
Давно пользуюсь индексом и поиском позиции, но просто скопировав из чужого примера, меняя ссылки на ячейки, т.к. вообще не понимала как эти функции работают, а встроенная справка в Excel не дает понятной информации.
С помощью вашего примера разобралась с этой функцией: это так легко, просто и безумно полезно. 
Доброго времени суток!
А как быть, если нужно найти максимальное значение?
| Дата | А | В | Г |
| 01.02 | 1 | 1 | 1 |
| 02.02 | 3 | 3 | 1 |
| 03.02 | 1 | 1 | 2 |
| 04.02 | 1 | 1 | 1 |
<=ПОИСКПОЗ("1"&"1"&"1";&[А]&[В]&[Г];0)>— ПОИСКПОЗ находит первое значение
Николай доброго времени суток.
Прошу подсказать как найти все уникальные значения и объединить их как текст в одной ячейке по типу изделия.
| уникальный тип изделия | результат |
| С1 | 785; 786; 787; 788; 789; 790 |
| С2 | 791; 792; 793; 794; 795; 796; 797; 798; 799; 800 |
| С3 | 801; 802; 803; 804; 805; 806; 807; 808; 809; 810; 811; 812 |
| С4 | 813; 814; 815; 816; 817; 818; 819; 820; 821; 822; 823; 824; 825; 826 |
| С5 | 827; 828; 829; 830; 831; 832; 833; 834; 835; 836; 837; 838; 839; 840 |
| С6 | 841; 842; 843; 844; 845; 846; 847; 848; 849; 850 |
| С7 | 851; 852; 853; 854; 855; 856; 857; 858; 859; 860; 861; 862 |
| С8 | 863; 864; 865; 866; 867; 868 |
исходная таблица
| 785 | С1 |
| 786 | С1 |
| 787 | С1 |
| 788 | С1 |
| 789 | С1 |
| 790 | С1 |
| 791 | С2 |
| 792 | С2 |
| 793 | С2 |
| 794 | С2 |
| 795 | С2 |
| 796 | С2 |
| 797 | С2 |
| 798 | С2 |
| 799 | С2 |
| 800 | С2 |
| 801 | С3 |
| 802 | С3 |
| 803 | С3 |
| 804 | С3 |
| 805 | С3 |
| 806 | С3 |
| 807 | С3 |
и т.д.
Добрый день. вопрос
не получается найти значение через эти формулы.
может тогда подскажете решение?
Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000.
среднее значение их 2036
мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению — и этим значением является 2030
ИНДЕКС+ПОИСКПОЗ — находит почему-то только 2000 (при значении»+1″)
при замене на «-1» — выдает Н/Д
0 — даже не ставлю, т.к. точного значения в строке нет