Найти ячейки по условию в Excel формулой проще всего в соседнем столбце: содержит ли ячейка текст — =ЕЧИСЛО(ПОИСК("диван";A2)), повторяется ли значение — =СЧЁТЕСЛИ($A$2:$A$100;A2)>1, попадает ли число в интервал — =И(B2>=100;B2<=500), есть ли в ячейке формула — =ЕФОРМУЛА(A2). Формула возвращает ИСТИНА или ЛОЖЬ, и по этому столбцу удобно фильтровать. Ниже — пример для каждого вида поиска.
Найти нужные ячейки по определенному критерию или нескольким — одна из самых часто встречающихся задач при работе в Excel.
Найти ячейки в Excel (штатные процедуры и функции)
Прежде всего стоит отметить, что каждый вкладывает свой смысл в понятие "Найти":
- Для кого-то найти - это неким образом отметить нужные ячейки - обычно здесь лучше всего подойдёт условное форматирование;
- Можно воспользоваться фильтром и отфильтровать определенные ячейки - это тоже в своём роде поиск;
- Кто-то хочет извлечь данные из ячеек и куда-то перенести, чтобы работать с ними далее.
- Также можно проставить в столбцах напротив нужных ячеек определенные значения. Здесь помогут различные формулы на основе функции ЕСЛИ.
- Если нас интересуют полные или частичные дубли ячеек другого диапазона, нам может помочь функция ВПР и похожие на неё.
Теперь давайте разберем понятие "Ячейка".
Ячейка Excel - сложносоставной объект, имеющий три дочерних объекта — формула, значение и форматирование. Соответственно, и искать ячейки можно тремя глобально различными способами:
- По значениям
- По формулам
- По формату
Это статья даст ответ, как искать и находить нужные ячейки каждым из указанных способов, и даст ссылки на примеры.
Поиск ячеек по значению
Наиболее часто требуется найти определённые ячейки по их значению. При этом значение ячеек может рассматриваться вне зависимости от других ячеек, а может рассматриваться как часть целого диапазона, где между значениями ячеек просматриваются связи и зависимости.
Самое простое и популярное в таких наборах ячеек - поиск ячеек, дублирующих друг друга тем или иным образом.
Найти уникальные и повторяющиеся ячейки
Вне зависимости от типа данных в ячейке, ячейки бывают дублями друг друга (повторяющимися), а могут содержать уникальное значение, встречающееся лишь один раз.
При этом поиск дублей — одна из самых популярных операций в Excel.
Есть два корня различных алгоритма поиска дубликатов, им посвящены две отдельные статьи:
- Найти дубли внутри одного диапазона ячеек
- Сравнить диапазоны ячеек между собой и выявить дубликаты
- Построчно
- По каждой позиции ячейки (если диапазон двумерный, содержит более одного столбца и строки)
- Найти значения одного столбца в другом (сравнить списки не учитывая позицию).
При этом для всех этих случаев характерно такое явление, как неявные дубли. Настоятельно рекомендую вам почитать, как найти неявные дубли в Excel.
Поиск по текстовым значениям ячеек
Тип содержимого значения влияет на то, как и каким образом мы будем искать ячейки.
Если значение ячеек текстовые, то и критерии поиска будут тоже характерные для текста:
- Найти ячейки, содержащие определенный текст (поиск по подстроке или нескольким)
- Найти ячейки, содержащие определённое слово;
- Найти ячейки, содержащие несколько слов или любое из слов списка;
- Найти ячейки, начинающиеся на определенный текст или один из нескольких вариантов текста
- Аналогично, но найти ячейки, заканчивающиеся на определённый текст
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Есть «диван»? | Есть слово из списка? | Список |
| 2 | купить диван | ИСТИНА=ЕЧИСЛО(ПОИСК("диван";A2)) | ИСТИНА=СЧЁТ(ПОИСК($D$2:$D$3;A2))>0 | диван |
| 3 | кресло-кровать | ЛОЖЬ=ЕЧИСЛО(ПОИСК("диван";A3)) | ИСТИНА=СЧЁТ(ПОИСК($D$2:$D$3;A3))>0 | кресло |
| 4 | стол обеденный | ЛОЖЬ=ЕЧИСЛО(ПОИСК("диван";A4)) | ЛОЖЬ=СЧЁТ(ПОИСК($D$2:$D$3;A4))>0 |
ПОИСК не различает регистр и находит фрагмент в любом месте ячейки. Подробнее — поиск по нескольким условиям.
В английском Excel: =ISNUMBER(SEARCH("диван",A2))
Поиск ячеек по одному текстовому значению довольно легко выполняется штатной процедурой найти и заменить. Если у вас два критерия одновременно, то поможет процедура расширенный фильтр.
Но нужно найти ячейки по нескольким условиям одновременно, решить задачу можно лишь формулами, либо программировать процедуры. Также можно воспользоваться уже готовыми решениями, одним из которых является моя настройка для Excel !SEMTools.
В статьях выше я даю как формулы, так и соответствующие им процедуры.
Поиск числовых значений
Когда ячейки содержат определённые числа, ситуация более интересная, нежели с ячейками, которые содержат текст. Дело в том, что числовые ячейки можно рассматривать и как текст, и как, собственно, числа. Также ячейки могут быть гибридными — содержать и числа и текст.
Так или иначе к числовым значениям применимые все текстовые критерии поиска, но также добавляется поиск ячеек по числовым критериям:
- Равно / не равно
- Больше / меньше
- Больше или равно / меньше или равно
- Выше / ниже среднего
- Между
- Первые N наибольших/наименьших значений (по умолчанию первые 10 наибольших)
| A | B | C | |
|---|---|---|---|
| 1 | Сумма | От 100 до 500 | Выше среднего |
| 2 | 90 | ЛОЖЬ=И(A2>=100;A2<=500) | ЛОЖЬ=A2>СРЗНАЧ($A$2:$A$6) |
| 3 | 150 | ИСТИНА=И(A3>=100;A3<=500) | ЛОЖЬ=A3>СРЗНАЧ($A$2:$A$6) |
| 4 | 480 | ИСТИНА=И(A4>=100;A4<=500) | ИСТИНА=A4>СРЗНАЧ($A$2:$A$6) |
| 5 | 620 | ЛОЖЬ=И(A5>=100;A5<=500) | ИСТИНА=A5>СРЗНАЧ($A$2:$A$6) |
| 6 | 260 | ИСТИНА=И(A6>=100;A6<=500) | ЛОЖЬ=A6>СРЗНАЧ($A$2:$A$6) |
Среднее по столбцу — 320. И требует выполнения обоих условий; для «или» используйте ИЛИ.
В английском Excel: =AND(A2>=100,A2<=500)
Число/год/дата в тексте
Если ячейки могут содержать как текст так и цифры, это, пожалуй, наиболее сложная ситуация при обработке, потому что нельзя применять числовые критерии к ячейкам, если в них содержится текст. Например, если у вас в текстовых ячейках присутствует год рождения, и вы хотите отфильтровать только ячейки в которых указан год больше 2005, рекомендуется сначала извлечь цифры из ячейки в отдельный столбец.
Второй вариант - перечислить все годы от 2006 и выше в формуле массива с функцией ПОИСК:
=СЧЁТ(ПОИСК({"2006":"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023":"2024":"2025":"2026"};A1))>0
Функция вернёт ИСТИНА или ЛОЖЬ в зависимости от наличия в ячейке года.
| A | B | |
|---|---|---|
| 1 | Текст | Есть год 2006–2026? |
| 2 | отчёт за 2024 | ИСТИНА=СЧЁТ(ПОИСК({"2006":"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023":"2024":"2025":"2026"};A2))>0 |
| 3 | iPhone 15 | ЛОЖЬ=СЧЁТ(ПОИСК({"2006":"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023":"2024":"2025":"2026"};A3))>0 |
| 4 | модель 2010 года | ИСТИНА=СЧЁТ(ПОИСК({"2006":"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023":"2024":"2025":"2026"};A4))>0 |
Список годов перечислен в фигурных скобках — добавляйте в него новые годы. Короче то же делает регулярное выражение =REGEXMATCH(A2;"20[0-2]\d").
Подробнее о поиске чисел в ячейках читайте тут:
Поиск по значению даты и времени
Поиск по дате и времени на самом деле является таким же поиском по числовым значениям. Потому что дата и время — ни что иное, как формат числовых данных. Тем не менее, для ячеек с датами свойственны некоторые особенности.
| A | B | C | |
|---|---|---|---|
| 1 | Дата | В октябре 2026? | Выходной? |
| 2 | 03.10.2026 | ИСТИНА=И(A2>=ДАТА(2026;10;1);A2<=ДАТА(2026;10;31)) | ИСТИНА=ДЕНЬНЕД(A2;2)>5 |
| 3 | 21.10.2026 | ИСТИНА=И(A3>=ДАТА(2026;10;1);A3<=ДАТА(2026;10;31)) | ЛОЖЬ=ДЕНЬНЕД(A3;2)>5 |
| 4 | 02.11.2026 | ЛОЖЬ=И(A4>=ДАТА(2026;10;1);A4<=ДАТА(2026;10;31)) | ЛОЖЬ=ДЕНЬНЕД(A4;2)>5 |
Дата для Excel — число, поэтому её сравнивают обычными знаками. ДЕНЬНЕД с типом 2 нумерует дни с понедельника, и суббота с воскресеньем дают 6 и 7.
В английском Excel: =WEEKDAY(A2,2)>5
Найти ячейки с формулами
Формула ячейки и её значение — это разные объекты, но если формулы в ячейке нет, они эквивалентны. Однако, если в ячейке есть формула, её текст не совпадает с возвращаемым ею значением. Поэтому процедура Excel "Найти и заменить" позволяет искать как по значениям, так и по тексту формул.
Достаточно лишь выбрать соответствующий пункт меню (по умолчанию стоит поиск по формулам!)
| A | B | |
|---|---|---|
| 1 | Значение | Формула? |
| 2 | 200=100*2 | ИСТИНА=ЕФОРМУЛА(A2) |
| 3 | 150 | ЛОЖЬ=ЕФОРМУЛА(A3) |
Включите переключатель «Формулы», чтобы увидеть, что в A2 формула, а в A3 — число. Все формулы листа сразу выделяет F5 → «Выделить» → «Формулы».
В английском Excel: =ISFORMULA(A2)
Найти ячейки по формату
Microsoft Excel предоставляет широчайшие возможности по форматированию ячеек. Это позволяет оживить и преобразить скучные сухие сводки и повысить их презентабельность. Ну что, если в массиве ячеек вам нужно найти такие, которые были выделены определенным способом форматирования, и отфильтровать только их?
Ниже наглядная демонстрация, как найти ячейки с жирным шрифтом:
Однако это лишь простейший пример, способов форматирования ячейки так много, что на эту тему планируется отдельная статья, ссылка на которую будет здесь позже.
Найти ячейки с !SEMTools
Раздел «Найти ячейки» в надстройке !SEMTools позволяет находить ячейки в Excel по содержимому, структуре, повторяемости или соответствию регулярным выражениям. Это мощный набор инструментов, аналогов которым среди штатных функций Excel или нет, или решение будет слишком громоздким и займёт много времени.
Найти ячейки по содержимому
Функции из этой группы позволяют находить ячейки, в которых содержится определённый текст:
- Содержащие (указать список) — ищет ячейки, содержащие хотя бы один элемент из заданного списка. Например, найти все ячейки, содержащие слова «купи», «купл», «заказ», «недорог».
- Начинающиеся на... — ищет ячейки, которые начинаются с заданных паттернов. Поддерживаются как произвольные списки, так и встроенный список предлогов.
- Заканчивающиеся на... — аналогично, но поиск по окончанию текста. Это удобно, например, для обнаружения уточнений или геометок в конце фраз («в москве», «для дома»).
- По паттерну REGEX — поиск с использованием регулярных выражений, например: найти ячейки, в которых есть числа, символы определённой длины, шаблоны URL и т.п.
Найти ячейки по встречаемости
Встречаемость - это атрибут значения ячейки исходя из её отношения к диапазону, в котором она находится. При этом здесь есть минимум два важных дочерних признака. Это уникальна ячейка или нет, и какая она по счёту в диапазоне среди идентичных себе - первая или последняя.
Для 3 и 4 пункта, если ячейка уникальна (не повторяется), то она будет считаться и первой, и последней (всегда будет возвращаться ИСТИНА). Что в целом логично.
1. Не повторяющиеся
Находим уникальные ячейки, которые встречаются только один раз.
2. Повторяющиеся
Выделяем ячейки, которые встречаются дважды и более.
3. Первая
Возвращаем ИСТИНА только напротив первого значения в диапазоне.
4. Последняя
Аналогично отмечаем только последнее из повторяющихся значений в диапазоне.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Город | Уникальная | Повторяется | Первая | Последняя |
| 2 | Москва | ЛОЖЬ=СЧЁТЕСЛИ($A$2:$A$6;A2)=1 | ИСТИНА=СЧЁТЕСЛИ($A$2:$A$6;A2)>1 | ИСТИНА=СЧЁТЕСЛИ($A$2:A2;A2)=1 | ЛОЖЬ=СЧЁТЕСЛИ(A2:$A$6;A2)=1 |
| 3 | Казань | ИСТИНА=СЧЁТЕСЛИ($A$2:$A$6;A3)=1 | ЛОЖЬ=СЧЁТЕСЛИ($A$2:$A$6;A3)>1 | ИСТИНА=СЧЁТЕСЛИ($A$2:A3;A3)=1 | ИСТИНА=СЧЁТЕСЛИ(A3:$A$6;A3)=1 |
| 4 | Москва | ЛОЖЬ=СЧЁТЕСЛИ($A$2:$A$6;A4)=1 | ИСТИНА=СЧЁТЕСЛИ($A$2:$A$6;A4)>1 | ЛОЖЬ=СЧЁТЕСЛИ($A$2:A4;A4)=1 | ЛОЖЬ=СЧЁТЕСЛИ(A4:$A$6;A4)=1 |
| 5 | Тула | ИСТИНА=СЧЁТЕСЛИ($A$2:$A$6;A5)=1 | ЛОЖЬ=СЧЁТЕСЛИ($A$2:$A$6;A5)>1 | ИСТИНА=СЧЁТЕСЛИ($A$2:A5;A5)=1 | ИСТИНА=СЧЁТЕСЛИ(A5:$A$6;A5)=1 |
| 6 | Москва | ЛОЖЬ=СЧЁТЕСЛИ($A$2:$A$6;A6)=1 | ИСТИНА=СЧЁТЕСЛИ($A$2:$A$6;A6)>1 | ЛОЖЬ=СЧЁТЕСЛИ($A$2:A6;A6)=1 | ИСТИНА=СЧЁТЕСЛИ(A6:$A$6;A6)=1 |
Для «первой» диапазон растёт вниз от закреплённого начала, для «последней» — сужается к закреплённому концу. Уникальное значение одновременно и первое, и последнее.
В английском Excel: =COUNTIF($A$2:A2,A2)=1
Найти пустые ячейки
Процедура отмечает все пустые ячейки в выделенном диапазоне. Применяется при проверке неполных данных, недозаполненных таблиц, подготовке отчетов.
| A | B | C | |
|---|---|---|---|
| 1 | Ячейка | ЕПУСТО | Пустая или пробелы |
| 2 | Москва | ЛОЖЬ=ЕПУСТО(A2) | ЛОЖЬ=ДЛСТР(СЖПРОБЕЛЫ(A2))=0 |
| 3 | ИСТИНА=ЕПУСТО(A3) | ИСТИНА=ДЛСТР(СЖПРОБЕЛЫ(A3))=0 | |
| 4 | ЛОЖЬ=ЕПУСТО(A4) | ИСТИНА=ДЛСТР(СЖПРОБЕЛЫ(A4))=0 |
В A4 только пробелы: ЕПУСТО считает ячейку заполненной, а проверка длины после СЖПРОБЕЛЫ — пустой.
В английском Excel: =ISBLANK(A2)
Найти дубли
Раздел «Найти дубликаты» ищет повторяющиеся значения по более тонким критериям, чем штатный функционал Excel.
Дубли внутри диапазона:
- Все кроме первого — выделяет все дубли, начиная со второго вхождения.
- Все включая первый — подсвечивает все повторяющиеся ячейки.
- Неявные дубли — ищет фразы с теми же словами в другом порядке (например, «купить диван» и «диван купить»), оставляя только первое вхождение. Особенно полезно при чистке ключевых слов и устранении клонов.
Дубли из другого диапазона:
- Полное совпадение — сравнивает два диапазона и находит дубли.
- Неявные дубли — сравнивает фразы с перестановкой слов. Позволяет выявить семантически одинаковые, но по-разному записанные фразы между двумя источниками данных.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Список 1 | Есть во 2-м списке? | Список 2 | |
| 2 | Москва | ИСТИНА=СЧЁТЕСЛИ($D$2:$D$4;A2)>0 | Москва | |
| 3 | Казань | ЛОЖЬ=СЧЁТЕСЛИ($D$2:$D$4;A3)>0 | Тула | |
| 4 | Тула | ИСТИНА=СЧЁТЕСЛИ($D$2:$D$4;A4)>0 | Самара | |
| 5 | Пермь | ЛОЖЬ=СЧЁТЕСЛИ($D$2:$D$4;A5)>0 |
СЧЁТЕСЛИ ищет каждое значение первого списка во втором. Неявные дубли (с перестановкой слов или опечатками) так не находятся — для них есть отдельная статья: неявные дубли.
В английском Excel: =COUNTIF($D$2:$D$4,A2)>0
Этот раздел значительно ускоряет ручную работу по чистке, удалению повторов и приведению данных к уникальному и однородному виду.
Поиск ячеек в Excel — частые вопросы
Как найти ячейки, содержащие текст?
Формулой =ЕЧИСЛО(ПОИСК("текст";A2)) или через Ctrl+F → «Найти все».
Как найти повторяющиеся ячейки?
Формулой =СЧЁТЕСЛИ($A$2:$A$100;A2)>1 или условным форматированием «Повторяющиеся значения». Подробнее — найти дубликаты.
Как найти ячейки с формулами?
Функцией =ЕФОРМУЛА(A2) или F5 → «Выделить» → «Формулы».
Как найти пустые ячейки?
F5 → «Выделить» → «Пустые ячейки». Ячейки с пробелами найдёт =ДЛСТР(СЖПРОБЕЛЫ(A2))=0.
Найти ячейки по содержимому, встречаемости, дублям внутри диапазона и из другого диапазона, а также пустые — с результатом ИСТИНА/ЛОЖЬ для фильтра — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как найти ячейки в Excel
| Что ищем | Формула |
|---|---|
| Текст в ячейке | ЕЧИСЛО(ПОИСК("текст";A2)) |
| Слово из списка | СЧЁТ(ПОИСК(список;A2))>0 |
| Число в интервале | И(A2>=мин;A2<=макс) |
| Дата в периоде, выходной | И(…ДАТА…), ДЕНЬНЕД(A2;2)>5 |
| Формула в ячейке | ЕФОРМУЛА(A2) |
| Повторы, первое, последнее | СЧЁТЕСЛИ с закреплёнными диапазонами |
| Пустые ячейки | ЕПУСТО или ДЛСТР(СЖПРОБЕЛЫ(A2))=0 |
Надеюсь, на этой странице вы нашли то, что искали. Все формулы возвращают ИСТИНА или ЛОЖЬ — включите фильтр по этому столбцу, и нужные ячейки окажутся перед глазами.