Note: The other languages of the website are Google-translated. Back to English

Як перевірити, чи містить комірка одне з кількох значень у Excel?

Припустимо, у вас є список текстових рядків у стовпці A, тепер ви хочете перевірити кожну клітинку, якщо вона містить одне з кількох значень на основі іншого діапазону D2: D7. Якщо він містить будь-який конкретний текст у D2: D7, він відображатиме True, інакше відображатиметься False, як показано на наступному скріншоті. У цій статті я розповім про те, як ідентифікувати клітинку, якщо вона містить одне з кількох значень в іншому діапазоні.


Перевірте, чи містить комірка одне з кількох значень зі списку з формулами

Щоб перевірити, чи містить вміст комірки якесь із текстових значень в іншому діапазоні, вам можуть допомогти такі формули, зробіть так:

Введіть формулу нижче в порожню комірку, де ви хочете знайти результат, наприклад, B2, а потім перетягніть маркер заповнення вниз до комірок, до яких ви хочете застосувати цю формулу, і якщо комірка має якесь із текстових значень в іншому певний діапазон, він отримає значення True, інакше - False. Дивіться знімок екрана:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0

Порада:

1. Якщо ви хочете використовувати «Так» або «Ні» для позначення результату, застосуйте наступну формулу, і ви отримаєте такий результат, як вам потрібно, див. Знімок екрана:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")

2. У наведених формулах D2: D7 - конкретний діапазон даних, на основі якого потрібно перевірити комірку, та A2 - це клітина, яку потрібно перевірити.


Відображення збігів, якщо комірка містить одне з кількох значень зі списку з формулами

Часом, можливо, ви захочете перевірити, чи містить клітинка значення у списку, а потім повертає це значення, якщо кілька значень збігаються, тоді всі відповідні значення у списку відображаються, як показано на знімку екрана. Як ви могли вирішити це завдання в Excel?

Щоб відобразити всі відповідні значення, якщо комірка містить один із конкретного тексту, застосуйте формулу нижче:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$D$2:$D$7&"*"), $D$2:$D$7, ""))

примітки: У наведеній вище формулі, D2: D7 - конкретний діапазон даних, на основі якого потрібно перевірити комірку, та A2 - це клітина, яку потрібно перевірити.

Потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім перетягніть маркер заповнення до комірок, до яких потрібно застосувати цю формулу, див. знімок екрана:

Порада:

Вищевказана функція TEXTJOIN доступна лише для Excel 2019 та Office 365, якщо у вас попередні версії Excel, слід застосувати формулу нижче:

=IFERROR(INDEX($D$2:$D$7, SMALL(IF(COUNTIF($A2, "*"&$D$2:$D$7&"*"), MATCH(ROW($D$2:$D$7), ROW($D$2:$D$7)), ""), COLUMNS($F$1:F1))), "")

примітки: У наведеній вище формулі, D2: D7 - конкретний діапазон даних, на основі якого потрібно перевірити комірку, та A2 - це клітина, яку потрібно перевірити.

Потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім перетягніть комірку формули вправо, поки не відобразиться порожня комірка, а потім перетягніть ручку заповнення вниз до інших комірок, і всі відповідні значення будуть відображені, як показано на знімку екрана нижче:


Виділіть збіги, якщо комірка містить одне з кількох значень зі списку з зручною функцією

Якщо ви хочете виділити певний колір шрифту для відповідних значень, якщо комірка містить одне з кількох значень з іншого списку, у цьому розділі я представив просту функцію, Позначити ключове слово of Kutools для Excel, за допомогою цієї утиліти ви можете виділити конкретне одне або кілька ключових слів одночасно в комірках.

Примітка:Застосувати ці Позначити ключове слово По-перше, ви повинні завантажити Kutools для Excel, а потім швидко та легко застосувати функції.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Клацання Кутулс > текст > Позначити ключове слово, див. скріншот:

2, в Позначити ключове слово діалоговому вікні, виконайте такі дії:

  • Виберіть діапазон даних, для якого потрібно виділити відповідні тексти з Діапазон текстове вікно;
  • Виділіть клітинки містить ключові слова, на основі яких ви хочете виділити, ви також можете ввести ключові слова вручну (відокремлюючи їх комами) у ключове слово текстове поле
  • Нарешті, вам слід вказати колір шрифту для виділення текстів за допомогою прапорця Позначте кольори ключових слів варіант. 

3. Потім натисніть кнопку Ok , всі відповідні тексти були виділені, як показано на знімку екрана нижче:


Більше відносних статей:

  • Порівняйте два або більше текстових рядків в Excel
  • Якщо ви хочете порівняти два або більше текстових рядків на аркуші з чутливими до регістру чи не чутливими до регістру, як показано на наведеному нижче знімку екрана, у цій статті я розповім про деякі корисні формули для вирішення цього завдання в Excel.
  • Якщо комірка містить текст, тоді відобразиться в Excel
  • Якщо у вас є список текстових рядків у стовпці А та рядок ключових слів, то зараз вам потрібно перевірити, чи є ключові слова в текстовому рядку. Якщо ключові слова з'являються в комірці, відображаючи їх, якщо ні, порожня комірка відображається, як показано на наступному знімку екрана.
  • Знайти та замінити кілька значень у програмі Excel
  • Зазвичай функція «Знайти та замінити» може допомогти вам знайти певний текст і замінити його іншим, але, іноді, вам може знадобитися знайти та замінити кілька значень одночасно. Наприклад, щоб замінити весь текст "Excel" на "Excel 2019", "Outlook" на "Outlook2019" тощо, як показано нижче. У цій статті я представив формулу для вирішення цього завдання в Excel.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (50)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Що робити, якщо стовпець "конкретний текст" містить фрази? Наприклад, "видалити дублікат", "перетворити текст", "знайти поперек", як тоді перевірити?
Цей коментар був мінімізований модератором на сайті
Замість «Правда» чи «Неправда» я хочу надрукувати назву знайденого тексту, як це зробити?
Цей коментар був мінімізований модератором на сайті
перевірте формулу, яку дає skyyang... Це добре працює
Цей коментар був мінімізований модератором на сайті
Дуже корисна формула.
Чи можемо ми отримати це значення, знайдене з пошуку? (тобто я хочу перевірити, чи поле адреси, що містить 4-значний поштовий індекс, знаходиться в діапазоні, якщо так, то показати значення 4-значного поштового індексу.)
Девід
Цей коментар був мінімізований модератором на сайті
Привіт, Девід,
Моя формула масиву be this може допомогти вам:
=IF(SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))>0,INDEX($D$2:$D$4,SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))))
Після вставлення формули натисніть разом клавіші Shift + Ctrl + Enter.
Ця формула є дещо складною, будь ласка, змініть посилання на клітинки відповідно до ваших потреб.
Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Дивно, він працює саме так, як мені було потрібно. Дякую, Чемп.
Цей коментар був мінімізований модератором на сайті
Рада допомогти вам!
Цей коментар був мінімізований модератором на сайті
привіт, не могли б ви пояснити, як ви отримали цю функцію вище? Я намагаюся зробити те ж саме, але не можу правильно змінити посилання на клітинки.
Цей коментар був мінімізований модератором на сайті
Так, будь ласка, мені це теж потрібно. Я не розумію, чому $D$2 заблоковано?
Цей коментар був мінімізований модератором на сайті
Це дивно! Одна велика проблема для мене: що робити, якщо в моєму списку "пошуку" D2:D4 є порожні клітинки. Для мене це руйнує формулу, і я просто отримую "NUM" у кількох місцях. СПАСИБІ!
Цей коментар був мінімізований модератором на сайті
чи працює ця формула, коли є пара слів у клітинках пошуку та пара слів у пошукових клітинках? по суті, у мене є списки інгредієнтів і я хочу знайти певний інгредієнт у цьому списку, і конкретний інгредієнт може складатися з кількох слів
Цей коментар був мінімізований модератором на сайті
У мене є список інгредієнтів у стовпці A, кожна клітинка містить цілі інгредієнти продукту, тому це не лише одне слово, у стовпці D у мене є список інгредієнтів, які я намагаюся знайти в стовпці A, як це зробити? ця формула, здається, не працює на 100% для цього
Дякую
Цей коментар був мінімізований модератором на сайті
Ми з іншою дівчиною протягом 2 днів намагаємося вирішити проблему з даними, яку ми повинні вирішити. Дякую за цей вираз. Це саме те, що нам потрібно.
Цей коментар був мінімізований модератором на сайті
Чи може список у стовпці D складатися з 15000 XNUMX слів, але формула залишається такою ж, як зазначено вище? Я отримую очікувані результати лише з кількома значеннями, але в момент, коли список скаче до тисячі, все показує #REF.
Цей коментар був мінімізований модератором на сайті
Скайян,

Не знаю, чи прочитаєш ти це коли-небудь, але дуже дякую!

Ви геній!!!
Цей коментар був мінімізований модератором на сайті
Як би я змусила це працювати, якби в стовпці D були порожні клітинки?
Цей коментар був мінімізований модератором на сайті
hi
див. вкладений файл, я намагаюся з’ясувати, чи існує якийсь із інгредієнтів із стовпця D у стовпці A, і відобразити будь-який інгредієнт, який є в стовпці B, чи можливо це?
Цей коментар був мінімізований модератором на сайті
Перше вкладення не завантажено, дивіться вкладення нижче
Цей коментар був мінімізований модератором на сайті
감사합니다 정말 진심으로 감사드립니다
한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바렇게 바렇게 바렇게 바렇게 바렇게 바렇게 바렇게 바렇게 바렇게 바렇게 바렇을 구글을 구글을
Цей коментар був мінімізований модератором на сайті
Дуже дякую, це спрацювало.
Цей коментар був мінімізований модератором на сайті
유용한 자료 감사합니다!
Цей коментар був мінімізований модератором на сайті
використано =SUMPRODUCT(--ISNUMBER(SEARCH(список,A2))) разом з умовним форматуванням, щоб допомогти порівняти значення одного стовпця з іншим. чудово працює з Excel 2007. Я ламав мозок протягом кількох днів.
Цей коментар був мінімізований модератором на сайті
ТАК, НІ 가아닌 메칭되는 값(KTE, видалити) 들어있는 값이 보이게 넣을수는 없나요?

중복되는 값 배열일 경우 배열로 B열 C열......... 기재는 가능한가요
Цей коментар був мінімізований модератором на сайті
Будь-який спосіб повернути текст, який відповідає в клітинці, а не так чи ні?
Цей коментар був мінімізований модератором на сайті
Те саме питання; Чи можемо ми повернути відповідний текст, а не так/ні?
Цей коментар був мінімізований модератором на сайті
Чи можна було б змінити результат цієї формули з "TRUE" , "FALSE", "Так" або "Ні" на значення конкретного тексту?


Дякую
Цей коментар був мінімізований модератором на сайті
Привіт, Нільс,
Якщо ви хочете використовувати інший текст замість «Так» або «Ні», вам просто потрібно замінити «Так», «Ні» іншими текстами, спробуйте наведену нижче формулу:
=IF(SUMPRODUCT(--ISNUMBER(ПОШУК($D$2:$D$6,A2))),"ваш конкретний текст1","ваш конкретний текст2")

Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Джейн запитала нижче: "Те саме питання; чи можемо ми повернути відповідний текст, а не так/ні?" – також цікавить відповідь
Цей коментар був мінімізований модератором на сайті
=ЯКЩО(SUMPRODUCT(--ISNUMBER(ПОШУК($D$2:$D$6,A2))),"Так","Ні")
Цей коментар був мінімізований модератором на сайті
Гей,
Я спробував цю формулу, але вона показує TRUE для всього?
Цей коментар був мінімізований модератором на сайті
Будь ласка, перевірте
Цей коментар був мінімізований модератором на сайті
Формула неправильна. Він показує "true" для будь-якого значення
Цей коментар був мінімізований модератором на сайті
Привіт, Дд,
Формула добре працює на моєму робочому аркуші Excel, чи можете ви надати свій аркуш проблеми як знімок екрана тут?
Дякую!
Цей коментар був мінімізований модератором на сайті
привіт, у мене така ж проблема. Він повертає true щоразу, навіть якщо має бути False. Це формула:
=SUMPRODUCT(--ISNUMBER(SEARCH($AH$2:$AH$122;C2)))>0

$AH$2:$AH$12 — список слів
C2 - це текст із твіту.

він повертає true, навіть якщо твіт не містить жодного слова зі списку
Цей коментар був мінімізований модератором на сайті
саме те, що я шукав! працює ідеально
Цей коментар був мінімізований модератором на сайті
Ідеально!! Комплексний попит узгоджується з поєднанням кількох добре відомих формул
Цей коментар був мінімізований модератором на сайті
Це було дуже корисно, але мені просто не вистачає останньої частини.
Як розгорнути IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Так","Ні"), щоб також перевірити чутливість до регістру значень у списку?
Спасибо заранее
Цей коментар був мінімізований модератором на сайті
Привіт, Скотт,
Щоб перевірити значення з чутливістю до регістру, застосуйте наведену нижче формулу:
=ЯКЩО(SUMPRODUCT(--ISNUMBER(ЗНАЙТИ($D$2:$D$6,A2))),"Так","Ні")

Будь ласка, спробуйте, дякую!
Цей коментар був мінімізований модератором на сайті
Привіт! Що робити, якщо мені потрібно шукати в рядку ключові слова, а не лише клітинку, тобто A2 - P2 замість просто A2 у цій формулі? =ЯКЩО(SUMPRODUCT(--ISNUMBER(ПОШУК($D$2:$D$6,A2))),"Так","Ні")
Цей коментар був мінімізований модератором на сайті
Привіт, Марго,
Щоб застосувати цю формулу до даних рядка, вам просто потрібно замінити посилання на одну клітинку посиланням на рядок, як показано нижче:
=IF(SUMPRODUCT(--ISNUMBER(ПОШУК($R$2:$R$6,A2:P2))),"Так","Ні")
Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Чи можна це зробити за допомогою часткової відповідності ключових слів? Цей метод є лише точним збігом
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL