Найти нужные ячейки по определенному критерию или нескольким — одна из самых часто встречающихся задач при работе в 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 предоставляет широчайшие возможности по форматированию ячеек. Это позволяет оживить и преобразить скучные сухие сводки и повысить их презентабельность. Ну что, если в массиве ячеек вам нужно найти такие, которые были выделены определенным способом форматирования, и отфильтровать только их?
Ниже наглядная демонстрация, как найти ячейки с жирным шрифтом:
Однако это лишь простейший пример, способов форматирования ячейки так много, что на эту тему планируется отдельная статья, ссылка на которую будет здесь позже.