Найти нужные ячейки по определенному критерию или нескольким — одна из самых часто встречающихся задач при работе в Excel.
Найти ячейки в Excel (штатные процедуры и функции)
Прежде всего стоит отметить, что каждый вкладывает свой смысл в понятие “Найти”:
- Для кого-то найти – это неким образом отметить нужные ячейки – обычно здесь лучше всего подойдёт условное форматирование;
- Можно воспользоваться фильтром и отфильтровать определенные ячейки – это тоже в своём роде поиск;
- Кто-то хочет извлечь данные из ячеек и куда-то перенести, чтобы работать с ними далее.
- Также можно проставить в столбцах напротив нужных ячеек определенные значения. Здесь помогут различные формулы на основе функции ЕСЛИ.
- Если нас интересуют полные или частичные дубли ячеек другого диапазона, нам может помочь функция ВПР и похожие на неё.
Теперь давайте разберем понятие “Ячейка”.
Ячейка Excel – сложносоставной объект, имеющий три дочерних объекта — формула, значение и форматирование. Соответственно, и искать ячейки можно тремя глобально различными способами:
- По значениям
- По формулам
- По формату
Это статья даст ответ, как искать и находить нужные ячейки каждым из указанных способов, и даст ссылки на примеры.
Поиск ячеек по значению
Наиболее часто требуется найти определённые ячейки по их значению. При этом значение ячеек может рассматриваться вне зависимости от других ячеек, а может рассматриваться как часть целого диапазона, где между значениями ячеек просматриваются связи и зависимости.
Самое простое и популярное в таких наборах ячеек – поиск ячеек, дублирующих друг друга тем или иным образом.
Найти уникальные и повторяющиеся ячейки
Вне зависимости от типа данных в ячейке, ячейки бывают дублями друг друга (повторяющимися), а могут содержать уникальное значение, встречающееся лишь один раз.
При этом поиск дублей — одна из самых популярных операций в Excel.
Есть два корня различных алгоритма поиска дубликатов, им посвящены две отдельные статьи:
- Найти дубли внутри одного диапазона ячеек
- Сравнить диапазоны ячеек между собой и выявить дубликаты
- Построчно
- По каждой позиции ячейки (если диапазон двумерный, содержит более одного столбца и строки)
- Найти значения одного столбца в другом (сравнить списки не учитывая позицию).
При этом для всех этих случаев характерно такое явление, как неявные дубли. Настоятельно рекомендую вам почитать, как найти неявные дубли в Excel.
Поиск по текстовым значениям ячеек
Тип содержимого значения влияет на то, как и каким образом мы будем искать ячейки.
Если значение ячеек текстовые, то и критерии поиска будут тоже характерные для текста:
- Найти ячейки, содержащие определенный текст (поиск по подстроке или нескольким)
- Найти ячейки, содержащие определённое слово;
- Найти ячейки, содержащие несколько слов или любое из слов списка;
- Найти ячейки, начинающиеся на определенный текст или один из нескольких вариантов текста
- Аналогично, но найти ячейки, заканчивающиеся на определённый текст
Поиск ячеек по одному текстовому значению довольно легко выполняется штатной процедурой найти и заменить. Если у вас два критерия одновременно, то поможет процедура расширенный фильтр.
Но если у вас несколько критериев поиска одновременно, решить задачу можно лишь формулами, либо программировать процедуры. Также можно воспользоваться уже готовыми решениями, одним из которых является моя настройка для Excel !SEMTools.
В статьях выше я даю как формулы, так и соответствующие им процедуры.
Поиск числовых значений
Когда ячейки содержат определённые числа, ситуация более интересная, нежели с ячейками, которые содержат текст. Дело в том, что числовые ячейки можно рассматривать и как текст, и как, собственно, числа. Также ячейки могут быть гибридными — содержать и числа и текст.
Так или иначе к числовым значениям применимые все текстовые критерии поиска, но также добавляется поиск ячеек по числовым критериям:
- Равно / не равно
- Больше / меньше
- Больше или равно / меньше или равно
- Выше / ниже среднего
- Между
- Первые N наибольших/наименьших значений (по умолчанию первые 10 наибольших)
Число/год/дата в тексте
Если ячейки могут содержать как текст так и цифры, это, пожалуй, наиболее сложная ситуация при обработке, потому что нельзя применять числовые критерии к ячейкам, если в них содержится текст. Например, если у вас в текстовых ячейках присутствует год рождения, и вы хотите отфильтровать только ячейки в которых указан год больше 2005, рекомендуется сначала извлечь цифры из ячейки в отдельный столбец.
Второй вариант – перечислить все годы от 2006 и выше в формуле массива с функцией ПОИСК:
=СЧЁТ(ПОИСК({"2006":"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023"};A1))>0
Функция вернёт ИСТИНА или ЛОЖЬ в зависимости от наличия в ячейке года.
Подробнее о поиске чисел в ячейках читайте тут:
Поиск по значению даты и времени
Поиск по дате и времени на самом деле является таким же поиском по числовым значениям. Потому что дата и время — ни что иное, как формат числовых данных. Тем не менее, для ячеек с датами свойственны некоторые особенности.
Искать среди ячеек с формулами
Формула ячейки и её значение — это разные объекты, но если формулы в ячейке нет, они эквивалентны. Однако, если в ячейке есть формула, её текст не совпадает с возвращаемым ею значением. Поэтому процедура Excel “Найти и заменить” позволяет искать как по значениям, так и по тексту формул.
Достаточно лишь выбрать соответствующий пункт меню (по умолчанию стоит поиск по формулам!)
Найти ячейки по формату
Microsoft Excel предоставляет широчайшие возможности по форматированию ячеек. Это позволяет оживить и преобразить скучные сухие сводки и повысить их презентабельность. Ну что, если в массиве ячеек вам нужно найти такие, которые были выделены определенным способом форматирования, и отфильтровать только их?
Ниже наглядная демонстрация, как найти ячейки с жирным шрифтом:
Однако это лишь простейший пример, способов форматирования ячейки так много, что на эту тему планируется отдельная статья, ссылка на которую будет здесь позже.
!SEMTools для Excel – “Найти ячейки”
Раздел «Найти ячейки» в надстройке !SEMTools позволяет находить ячейки в Excel по содержимому, структуре, повторяемости или соответствию регулярным выражениям. Это мощный набор инструментов, аналогов которым среди штатных функций Excel или нет, или решение будет слишком громоздким и займёт много времени.
Найти ячейки по содержимому
Функции из этой группы позволяют находить ячейки, в которых содержится определённый текст:
- Содержащие (указать список) — ищет ячейки, содержащие хотя бы один элемент из заданного списка. Например, найти все ячейки, содержащие слова «купи», «купл», «заказ», «недорог».
- Начинающиеся на… — ищет ячейки, которые начинаются с заданных паттернов. Поддерживаются как произвольные списки, так и встроенный список предлогов.
- Заканчивающиеся на… — аналогично, но поиск по окончанию текста. Это удобно, например, для обнаружения уточнений или геометок в конце фраз («в москве», «для дома»).
- По паттерну REGEX — поиск с использованием регулярных выражений, например: найти ячейки, в которых есть числа, символы определённой длины, шаблоны URL и т.п.
Найти ячейки по встречаемости
Встречаемость – это атрибут значения ячейки исходя из её отношения к диапазону, в котором она находится. При этом здесь есть минимум два важных дочерних признака. Это уникальна ячейка или нет, и какая она по счёту в диапазоне среди идентичных себе – первая или последняя.
Для 3 и 4 пункта, если ячейка уникальна (не повторяется), то она будет считаться и первой, и последней (всегда будет возвращаться ИСТИНА). Что в целом логично.
1. Не повторяющиеся
Находим уникальные ячейки, которые встречаются только один раз.
2. Повторяющиеся
Выделяем ячейки, которые встречаются дважды и более.
3. Первая
Возвращаем ИСТИНА только напротив первого значения в диапазоне.
4. Последняя
Аналогично отмечаем только последнее из повторяющихся значений в диапазоне.
Найти пустые ячейки
Процедура отмечает все пустые ячейки в выделенном диапазоне. Применяется при проверке неполных данных, недозаполненных таблиц, подготовке отчетов.
Найти дубли
Раздел «Найти дубликаты» ищет повторяющиеся значения по более тонким критериям, чем штатный функционал Excel.
Дубли внутри диапазона:
- Все кроме первого — выделяет все дубли, начиная со второго вхождения.
- Все включая первый — подсвечивает все повторяющиеся ячейки.
- Неявные дубли — ищет фразы с теми же словами в другом порядке (например, «купить диван» и «диван купить»), оставляя только первое вхождение. Особенно полезно при чистке ключевых слов и устранении клонов.
Дубли из другого диапазона:
- Полное совпадение — сравнивает два диапазона и находит дубли.
- Неявные дубли — сравнивает фразы с перестановкой слов. Позволяет выявить семантически одинаковые, но по-разному записанные фразы между двумя источниками данных.
Этот раздел значительно ускоряет ручную работу по чистке, удалению повторов и приведению данных к уникальному и однородному виду.
Эта статья также доступна на EN