Как сопоставить частичную строку в ячейке со списком Excel 2013?

Question

У меня есть следующая информация в ячейке

| John Smith 34, Manager  | 
| Jane Doe 25, Assistant  |
| Lucio 32, Web Developer |

У меня также есть другой список со списком имен

| Lucio            |
| John Smith       |
| Jane Doe         |
| Samuel L Jackson |

[отредактировано, чтобы уточнить вопрос]

Я хочу проанализировать данные в первом наборе ячеек, сравнить имена с информацией во втором наборе ячеек и вывести 1, если они совпадают.

Как бы я сделал это в Excel 2013?

РЕДАКТИРОВАТЬ: Я думаю, что многие из ответов, из-за моей ошибки, неправильно поняли, что списки должны соответствовать ячейка за ячейкой. Мне просто нужно убедиться, что имена в первом списке верны и что они совпадают хотя бы с одним во втором списке.

Answer 1

Если NameList ссылается на диапазон (например: $ J $ 8: $ J $ 10), который содержит ваш список имен:

Эта формула должна быть введена в массив:

=COUNT(FIND(NameList,A1))

---

Для ввода массива введите формулу, после ввода формулы в ячейку или строку формулы удерживайте нажатой клавишу. Если вы сделали это правильно, Excel поместит фигурные скобки {...} вокруг формулы.

Answer 2

Это может работать для вас.

Если ваш первый список находится в A1:A3, а ваш второй список в D1:D3, введите его в ячейку E1:

=IFERROR(IF(MATCH(D1,LEFT($A$1:$A$3,LEN(D1)),0)>0,1,0),0)

Используйте CTRL+Shift+Enter, чтобы сделать его формулой массива. Затем скопируйте и вставьте.

Я проверил это с неправильным значением в дополнительной ячейке D4=Donald и он вернет 0, если нет точного соответствия.

РЕДАКТИРОВАТЬ:

Я, вероятно, должен объяснить, что эта формула работает так: он возьмет исходный список A1:A3 и сократит его до количества символов в ячейке, которое мы хотим сравнить с D1 .

LEFT($A$1:$A$3,LEN(D1)) вернется с массивом из 3 значений:

{"John Smith";"Jane Doe 2";"Lucio 32, "} .

Из этого списка мы ищем John Smith и точное совпадение (без учета регистра), и, так как есть только один, он возвращает 1 .

Например, в D2 результирующий массив новых значений для проверки:

{"John Smi";"Jane Doe";"Lucio 32"}

Так как мы ищем Jane Doe , есть одно совпадение, и поэтому возвращается 1 .

Answer 3

Из того, что вы описываете, самый простой способ сделать то, что вы просите, это написать макрос VBA в качестве формулы. Но ваш вопрос слишком расплывчатый. Например, вам нужно сказать, содержит ли первый список имена, которых нет во втором списке. Если второй список содержит имена не в первом списке. Также в вашем примере элемент 1 первого списка соответствует элементу 1 второго списка и так далее для элементов 2 и 3. Вы просто совпадаете с соответствующей строкой во втором списке или со всем списком? Я мог бы сделать предположения, но, надеюсь, вы получите то, что я говорю, когда скажу, что ваш вопрос слишком расплывчатый.

Answer 4

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

| John Smith 34, Manager  | John Smith |
| Jane Doe 25, Assistant  | Jane Doe   |
| Lucio 32, Web Developer | Lucio      |

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

= LEN(haystack) - LEN( SUBSTITUTE(haystack, needle, "") ) > 0

  // Analysis of a matching example

  - LEN("John Smith 34, Manager") = 22
  - SUBSTITUTE("John Smith 34, Manager", "John Smith", "") = " 34, Manager"
  - LEN(" 34, Manager") = 10
  - (22 - 10) > 0 = TRUE

  // Analysis of a non-matching example

  - LEN("John Smith 34, Manager") = 22
  - SUBSTITUTE("John Smith 34, Manager", "Lucio", "") = "John Smith 34, Manager"
  - LEN("John Smith 34, Manager") = 22
  - (22 - 22) > 0 = FALSE

В приведенном выше примере вы просто используете =LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))>0 . Также обратите внимание, что вы можете выполнять сравнения без учета регистра с помощью SUBSTITUTE(UPPER(haystack), UPPER(needle), "")

Я предпочитаю этот подход, а не iferror(find(needle, haystack)>0,false) , потому что обработка ошибок является неотъемлемой частью - больше философии, что обработка ошибок должна выполняться на макроуровне, а не на формуле.

Кроме того, если вы предпочитаете двоичный вывод (0, 1), а не логический выход (TRUE, FALSE), вы можете заключить вашу функцию в INT() (то есть INT(TRUE) = 1, INT(FALSE) = 0).