Как найти ячейку в excel по содержанию
Перейти к содержимому

Как найти ячейку в excel по содержанию

  • автор:

Поиск ячеек по содержимому и вывод нужных значений

Привет. Наверное чаще всего в Эксель или Гугл Таблицах я использую набор формул, призванный по заданному мною тексту находить соответствие в ячейке и отмечать их либо копировать в соседний столбец. Очень удобно использовать для сортировки столбцов данных, когда нужно сделать её быстро без загрузки в Power Query или какой-то дополнительной обработки.

Расскажу подробнее про это и в конце отдам шаблон.

Про маркетинг и аналитику читайте в моём в блоге или Телеграм канале.

Проверьте, содержит ли ячейка текст (без учета регистра)

Предположим, вы хотите убедиться, что столбец содержит текст, а не цифры. Или, возможно, вы хотите найти все заказы, которые соответствуют конкретному продавцу. Если вы не имеете никакого отношения к тексту в верхнем или нижнем регистре, существует несколько способов проверка, если ячейка содержит текст.

Вы также можете использовать фильтр для поиска текста. Дополнительные сведения см. в разделе Фильтрация данных.

Поиск ячеек, содержащих текст

Чтобы найти ячейки, содержащие определенный текст, выполните следующие действия:

  1. Выберите диапазон ячеек, в которые требуется выполнить поиск. Чтобы выполнить поиск по всему листу, щелкните любую ячейку.
  2. На вкладке Главная в группе Редактирование нажмите кнопку Найти & Выбрать, а затем нажмите кнопку Найти.

Поиск параметра на ленте

Примечание: В критериях поиска можно использовать подстановочные знаки.

Примечание: Чтобы остановить поиск, нажмите клавишу ESC.

Проверьте, есть ли в ячейке текст

Для выполнения этой задачи используйте функцию ISTEXT .

Примеры ISTEXT

Проверка соответствия ячейки определенному тексту

Используйте функцию IF , чтобы вернуть результаты для указанного условия.

Примеры IF

Проверка соответствия части ячейки определенному тексту

Для выполнения этой задачи используйте функции IF, SEARCH и ISNUMBER .

Примечание: Функция SEARCH не учитывает регистр.

Найти определенные ячейки в Excel

Найти нужные ячейки по определенному критерию или нескольким — одна из самых часто встречающихся задач при работе в Excel.

Прежде всего стоит отметить, что каждый вкладывает свой смысл в понятие “Найти”:

  • Для кого-то найти – это неким образом отметить нужные ячейки – обычно здесь лучше всего подойдёт условное форматирование;
  • Можно воспользоваться фильтром и отфильтровать определенные ячейки – это тоже в своём роде поиск;
  • Кто-то хочет извлечь данные из ячеек и куда-то перенести, чтобы работать с ними далее.
  • Также можно проставить в столбцах напротив нужных ячеек определенные значения. Здесь помогут различные формулы на основе функции ЕСЛИ.
  • Если нас интересуют полные или частичные дубли ячеек другого диапазона, нам может помочь функция ВПР и похожие на неё.

Теперь давайте разберем понятие “Ячейка”.

Ячейка Excel – сложносоставной объект, имеющий три дочерних объекта — формула, значение и форматирование. Соответственно, и искать ячейки можно тремя глобально различными способами:

  1. По значениям
  2. По формулам
  3. По формату

Это статья даст ответ, как искать и находить нужные ячейки каждым из указанных способов, и даст ссылки на примеры.

Поиск ячеек по значению

Наиболее часто требуется найти определённые ячейки по их значению. При этом значение ячеек может рассматриваться вне зависимости от других ячеек, а может рассматриваться как часть целого диапазона, где между значениями ячеек просматриваются связи и зависимости.

Самое простое и популярное в таких наборах ячеек – поиск ячеек, дублирующих друг друга тем или иным образом.

Найти уникальные и повторяющиеся ячейки

Вне зависимости от типа данных в ячейке, ячейки бывают дублями друг друга (повторяющимися), а могут содержать уникальное значение, встречающееся лишь один раз.

При этом поиск дублей — одна из самых популярных операций в Excel.

Есть два корня различных алгоритма поиска дубликатов, им посвящены две отдельные статьи:

  1. Найти дубли внутри одного диапазона ячеек
  2. Сравнить диапазоны ячеек между собой и выявить дубликаты
    • Построчно
    • По каждой позиции ячейки (если диапазон двумерный, содержит более одного столбца и строки)
    • Найти значения одного столбца в другом (сравнить списки не учитывая позицию).

При этом для всех этих случаев характерно такое явление, как неявные дубли. Настоятельно рекомендую вам почитать, как найти неявные дубли в Excel.

Поиск по текстовым значениям ячеек

Тип содержимого значения влияет на то, как и каким образом мы будем искать ячейки.

Если значение ячеек текстовые, то и критерии поиска будут тоже характерные для текста:

  • Найти ячейки, содержащие определенный текст (поиск по подстроке или нескольким)
  • Найти ячейки, содержащие определённое слово;
  • Найти ячейки, содержащие несколько слов или любое из слов списка;
  • Найти ячейки, начинающиеся на определенный текст или один из нескольких вариантов текста
  • Аналогично, но найти ячейки, заканчивающиеся на определённый текст

Поиск ячеек по одному текстовому значению довольно легко выполняется штатной процедурой найти и заменить. Если у вас два критерия одновременно, то поможет процедура расширенный фильтр.

Но если у вас несколько критериев поиска одновременно, решить задачу можно лишь формулами, либо программировать процедуры. Также можно воспользоваться уже готовыми решениями, одним из которых является моя настройка для Excel !SEMTools.

В статьях выше я даю как формулы, так и соответствующие им процедуры.

Поиск числовых значений

Когда ячейки содержат определённые числа, ситуация более интересная, нежели с ячейками, которые содержат текст. Дело в том, что числовые ячейки можно рассматривать и как текст, и как, собственно, числа. Также ячейки могут быть гибридными — содержать и числа и текст.

Так или иначе к числовым значениям применимые все текстовые критерии поиска, но также добавляется поиск ячеек по числовым критериям:

Числовые фильтры в Excel

  • Равно / не равно
  • Больше / меньше
  • Больше или равно / меньше или равно
  • Выше / ниже среднего
  • Между
  • Первые N наибольших/наименьших значений (по умолчанию первые 10 наибольших)

Число/год/дата в тексте

Если ячейки могут содержать как текст так и цифры, это, пожалуй, наиболее сложная ситуация при обработке, потому что нельзя применять числовые критерии к ячейкам, если в них содержится текст. Например, если у вас в текстовых ячейках присутствует год рождения, и вы хотите отфильтровать только ячейки в которых указан год больше 2005, рекомендуется сначала извлечь цифры из ячейки в отдельный столбец.

Второй вариант – перечислить все годы от 2006 и выше в формуле массива с функцией ПОИСК:

=СЧЁТ(ПОИСК(:"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023">;A1))>0 

Копировать

Функция вернёт ИСТИНА или ЛОЖЬ в зависимости от наличия в ячейке года.

Подробнее о поиске чисел в ячейках читайте тут:

  • Найти ячейки с числами;
  • Найти ячейки с определёнными числами.

Поиск по датам и времени

Поиск по дате и времени на самом деле является таким же поиском по числовым значениям. Потому что дата и время — ни что иное, как формат числовых данных. Тем не менее, для ячеек с датами свойственны некоторые особенности.

Искать среди ячеек с формулами

Формула ячейки и её значение — это разные объекты, но если формулы в ячейке нет, они эквивалентны. Однако, если в ячейке есть формула, её текст не совпадает с возвращаемым ею значением. Поэтому процедура Excel “Найти и заменить” позволяет искать как по значениям, так и по тексту формул.

Достаточно лишь выбрать соответствующий пункт меню (по умолчанию стоит поиск по формулам!)

Найти ячейки по их форматированию

Microsoft Excel предоставляет широчайшие возможности по форматированию ячеек. Это позволяет оживить и преобразить скучные сухие сводки и повысить их презентабельность. Ну что, если в массиве ячеек вам нужно найти такие, которые были выделены определенным способом форматирования, и отфильтровать только их?

Ниже наглядная демонстрация, как найти ячейки с жирным шрифтом:

поиск ячеек по формату на простом примере

Однако это лишь простейший пример, способов форматирования ячейки так много, что на эту тему планируется отдельная статья, ссылка на которую будет здесь позже.

Как найти ячейку в excel по содержанию

Страницы: 1

поиск части содержимого ячейки

22.07.2010 10:22:31

Добрый День!
Подскажите плз как можно сделать поиск по содержимому в ячейке.
Пример:
ячейка содержит например такой текст
ЦЕНТРАЛЬНОЕ ТАМОЖЕННОЕ УПРАВЛЕНИЕ 107140 Г.МОСКВА, КОМСОМОЛЬСКАЯ ПЛОЩАДЬ, 1 А
в другой ячейке надо что то типа :
если ячейка содержит слово «КОМСОМОЛЬСКАЯ» тогда 1
или если кто знает то несколько сложнее (по части слова)
если содержит «КОМСОМ..» тогда 1
Заранее спасибо.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *