☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Как определить случаи с двумя именами в списке, в котором может быть до четырех имен

Этот вопрос является ответвлением более раннего вопроса:Формула Excel — формула разделения имен для людей с многосоставными фамилиями:.

Контекст

У меня есть список из более чем 20 000 контактов, в котором есть один столбец "Полное имя". Мне нужно создать два столбца для имени и фамилии. Однако не все имена соответствуют этому простому шаблону. Список включает в себя такие имена, как:

David Batte
Guy-Mael Jacobe de Naurois
Jean-Marc Lioutier
Thibaud Le Seguillon
Mrs. Manuela Junghaehnel
Hussain Mohammed Dipu Kabir

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

Этот вопрос

Значительная часть полного списка имен содержит только имя и фамилию, которые легко разобрать. Моя цель в этом вопросе — идентифицировать эти имена с двумя именами, чтобы их можно было отделить от остального списка.

решение1

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

Метод 1

Этот вопрос является ответвлением более раннего вопроса (Формула Excel — формула разделения имен для людей с многосоставными фамилиями:), где мы уже обсуждали в комментариях определение случаев одного имени, например, просто имени или фамилии. Если вы уже удалили эти имена из списка, так что каждая запись гарантированно имеет по крайней мере два имени, вы можете использовать здесь похожую технику и развивать ее.

Этот подход ищет пробел, затем, начиная оттуда, ищет другой пробел. Если он может найти второй пробел, запись имеет более двух имен компонентов. Если запись находится в A1, основная формула будет такой:

=FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)

Результатом этого будет либо число (расположение следующего пробела), либо ошибка #VALUE!, если ее нет. Ошибку #VALUE! легко обнаружить, она идентифицирует записи с двумя именами. Обратите внимание, что она также идентифицирует записи с одним именем, поэтому этот метод будет работать только в том случае, если вы уже удалили их.

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

=IF(ISERROR(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)),"Just 2","")

Метод 2

Другой подход заключается в подсчете пробелов. Фактически вы можете использовать этот подход для поиска любого определенного количества пробелов, поэтому простое изменение целевого количества позволит вам также найти записи с одним именем. Этот подход подсчитывает пробелы, удаляя все пробелы и проверяя, на сколько символов сокращается строка. Сравнение длины дает количество пробелов. Вы оборачиваете это проверкой IF, чтобы сделать то, что вам нужно с полученным числом. Формула для пометки записей с двумя именами (т. е. с одним пробелом) будет выглядеть следующим образом:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,"Just 2","")

решение2

Я оставлю более простой метод, которыйможет быть не оптимальнымдля 20000 строк.

  • Разделите имена пробелами, используя Данные -> Текст по столбцам.
  • Имена будут распределены по четырем столбцам: A, B, C и D.
  • нажмите на фильтр и отфильтруйте blanksпо столбцу C
  • у вас будут только два названия слов, показанных в A и B. скопируйте их.
  • Вы можете объединить имена, используя=TEXTJOIN(" ",TRUE,A1:B1)

решение3

Я хотел бы предложить два возможных метода: первый — без использования VBA, а второй — с использованием VBA.

Метод 1:

введите описание изображения здесь

Как это работает:

  • Введите эту формулу в ячейку H2, чтобы создать вспомогательные значения (Определите части имен.) и заполните его.

=LEN(G2)-LEN(SUBSTITUTE(G2," ",""))

  • Запишите критерии 2в ячейку H1.

  • Введите эту формулу массива в ячейку I2, закончите с помощьюCtrl+Shift+Enterи залейте его.

    {=IFERROR(INDEX($G$2:$G$14,SMALL(IF(H$1=$H$2:$H$14,ROW($G$2:$G$14)- MIN(ROW($G$2:$G$14))+1,""), ROW()-1)),"")}

Примечание.

  • Изменяя критерии в ячейке H1на 1или , 3можно получить список из двух и более трех названий деталей.

введите описание изображения здесь

введите описание изображения здесь

Метод 2:

Поскольку вы имеете дело с более чем 20000 строк, то VBA (макрос) будет самым быстрым способом выполнить работу.

Как это работает:

  • Щелкните правой кнопкой мышиВкладка «Лист» и из всплывающего меню нажмитеПосмотреть кодчтобы получить редактор VB.

  • Копировать вставитьэтот код как стандартный модуль, и наконецБЕГАТЬМакрос.

    Sub DeleteMorethan2()

Dim r As Integer, c As Integer, space_count As Integer

For r = 2 To 15
space_count = 0

For c = 1 To Len(Range("A" & r))

   If Mid(Range("A" & r), c, 1) = " " Then space_count = space_count + 1
   If space_count > 1 Then Range("A" & r).ClearContents

  Next
Next

End Sub

Вы получаете это:

введите описание изображения здесь

Примечание.

  • Этот код находится в разработке Rows 2 to 15, и, отредактировав эту часть For r = 2 To 15, вы сможете включить больше строк.
  • Используемый столбец — A, который также можно редактировать.

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

введите описание изображения здесь

  • Введите эту формулу массива в ячейку B2, закончите с помощьюCtrl+Shift+Enterи заполните его.

    {=IFERROR(INDEX(A$2:A$14,SMALL(IF(A$2:A$14<>"",ROW(A$2:A$14)-ROW(A$2)+1),ROWS(B$2:B2))),"")}

При необходимости откорректируйте ссылки на ячейки в формуле.

Связанный контент