내 친구나는 그가 Excel에서 데이터로 작업할 때 직면하는 실제 문제에 대해 자주 연락합니다.
다른 독자들에게도 도움이 될 수 있기 때문에 여러 번 그의 쿼리를 이 사이트의 Excel 자습서로 번역합니다.
이것은 또한 그러한 튜토리얼 중 하나입니다.
제 친구가 지난 주에 다음과 같은 질문으로 저에게 전화를 걸었습니다.
Excel의 열에 주소 데이터가 있고 주소에 반복되는 텍스트 문자열(단어)이 포함된 셀을 식별/필터링하고 싶습니다.
다음은 반복되는 텍스트 문자열이 있는 셀(빨간색 화살표가 있는 셀)을 필터링하려는 유사한 데이터세트입니다.
지금 이것을 어렵게 만드는 것은 데이터가 일관성이 없다는 것입니다.이것은 영업 담당자가 수동으로 생성한 데이터 세트의 편집이므로 데이터 세트에 불일치가 있을 수 있습니다.
그것에 대해 생각하십시오:
- 이 데이터세트에서 모든 텍스트 문자열을 반복할 수 있습니다.예를 들어 지역 이름이나 도시 이름 또는 둘 다일 수 있습니다.
- 단어는 공백 문자로 구분되며 도시 이름이 XNUMX자인지 XNUMX자인지 일관성이 없습니다.
- 이러한 레코드가 수천 개 있으며 반복되는 텍스트 문자열이 있는 레코드를 필터링해야 합니다.
텍스트에서 열 및 수식과 같은 많은 옵션을 고려한 후 마침내 VBA를 사용하여 작업을 수행하기로 결정했습니다.
그래서 사용자 지정 VBA 함수('IdDuplicate')를 만들어 이러한 셀을 구문 분석하고 텍스트 문자열에 중복 단어가 있으면 TRUE를 제공하고 중복 단어가 없으면 FALSE를 제공합니다(아래 참조).
이 사용자 정의 기능은 텍스트 문자열의 각 단어를 분석하고 텍스트에서 해당 단어가 몇 번 나타나는지 확인합니다.개수가 1보다 크면 TRUE를 반환합니다.그렇지 않으면 FALSE를 반환합니다.
또한 XNUMX자 이상의 단어만 셀 수 있도록 만들었습니다.
TRUE/FALSE 데이터가 있으면 TRUE인 모든 레코드를 쉽게 필터링할 수 있습니다.
이제 Excel에서 이 작업을 수행하는 방법을 보여 드리겠습니다.
사용자 정의 함수에 대한 VBA 코드
이것은 VBA에서 사용자 정의 함수를 생성하여 수행됩니다.이 함수는 Excel의 다른 워크시트 함수로 사용할 수 있습니다.
다음은 VBA 코드입니다.
함수 IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 단계 -1 If Len(StringtoAnalyze(i)) < minWordLen then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: 기능 종료
이 VBA 코드를 사용하는 방법
이제 VBA 코드가 있으므로 Excel의 백엔드에 넣어야 일반 워크시트 기능으로 작동할 수 있습니다.
백엔드에 VBA 코드를 넣는 단계는 다음과 같습니다.
- 개발자 탭으로 이동합니다.
- Visual Basic을 클릭합니다(단축키 ALT+F11을 사용할 수도 있습니다).
- 열려 있는 VB 편집기 백엔드에서 통합 문서 개체를 마우스 오른쪽 버튼으로 클릭합니다.
- "삽입"으로 이동하여 "모듈"을 클릭하십시오.그러면 통합 문서의 모듈 개체가 삽입됩니다.
- 모듈 코드 창에서 위에서 언급한 VBA 코드를 복사하여 붙여넣습니다.
백엔드에 VBA 코드가 있으면 'IdDuplicates' 기능을 다른 일반 워크시트 기능으로 사용할 수 있습니다.
이 함수는 텍스트가 있는 셀의 셀 참조인 하나의 매개변수를 사용합니다.
이 함수의 결과는 TRUE(중복 단어가 있는 경우) 또는 FALSE(중복이 없는 경우)입니다.이 TRUE/FALSE 목록이 있으면 TRUE로 필터링하여 텍스트 문자열이 반복되는 모든 셀을 가져올 수 있습니다.
참고: 내가 만든 코드는 XNUMX자보다 긴 단어를 설명하기 위한 것입니다.이렇게 하면 텍스트 문자열에 길이가 1, 2 또는 3자인 단어가 있는 경우(예: 12 A, KGM 또는 LDA) 중복을 계산할 때 해당 단어가 무시됩니다.필요한 경우 코드에서 이를 쉽게 변경할 수 있습니다.
이 기능은 모듈의 코드를 복사한 통합 문서에서만 사용할 수 있습니다.이 코드를 다른 통합 문서에서도 사용하려면 이 코드를 복사하여 해당 통합 문서에 붙여넣어야 합니다.또는 추가 기능을 만들 수 있습니다(이를 활성화하면 시스템의 모든 통합 문서에서 기능을 사용할 수 있음).
또한 이 통합 문서를 .xlsm 확장명으로 저장해야 합니다(매크로 코드가 포함되어 있기 때문에).
