☰

최대 4개의 이름이 포함될 수 있는 목록에서 이름이 2개인 사례를 식별하는 방법

tag-icon • tag-icon microsoft-excel worksheet-function
최대 4개의 이름이 포함될 수 있는 목록에서 이름이 2개인 사례를 식별하는 방법

이 질문은 이전 질문의 파생물입니다.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입니다! 없으면 오류가 발생합니다. 가치! 오류는 쉽게 발견할 수 있으며 이름이 두 개인 항목을 식별합니다. 단일 이름 항목도 식별하므로 이 방법은 해당 항목을 이미 제거한 경우에만 작동합니다.

오류 조건에 대한 결과를 테스트한 다음 이름이 두 개인 경우에 대한 텍스트 메시지나 공백을 표시하여 이를 시각적으로 더욱 매력적으로 만들 수 있습니다. 위의 수식을 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 4개 열로 분할됩니다.
  • blanksC 열에서 필터 및 필터를 클릭하십시오.
  • A와 B에 두 개의 단어 이름만 표시됩니다. 이를 복사하세요.
  • 다음을 사용하여 이름을 결합할 수 있습니다.=TEXTJOIN(" ",TRUE,A1:B1)

답변3

저는 두 가지 가능한 방법을 제안하고 싶습니다. 첫 번째는 Non VBA이고 다른 하나는 VBA입니다.

방법 1:

여기에 이미지 설명을 입력하세요

작동 방식:

  • 셀에 이 공식을 입력하여 H2도우미 값(이름의 일부 식별) 그리고 내용을 채워주세요.

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

  • 2셀에 기준을 작성합니다 H1.

  • Cell 에 이 배열 수식을 입력하고 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)),"")}

주의

  • Cell의 기준을 또는 H1로 변경하면 2개 또는 3개 이상의 부품 이름 목록을 얻을 수 있습니다.13

여기에 이미지 설명을 입력하세요

여기에 이미지 설명을 입력하세요

방법 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이며 편집도 가능합니다.

또한 빈 행을 제거하여 이름 목록을 다시 만드는 배열 수식을 제안하고 싶습니다.

여기에 이미지 설명을 입력하세요

  • Cell 에 이 배열 수식을 입력하고 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))),"")}

필요에 따라 수식에서 셀 참조를 조정합니다.

관련 정보