내용
여러 셀의 텍스트를 빠르게 하나로 붙이고 반대로 긴 텍스트 문자열을 구성 요소로 구문 분석하는 방법에 대해 이미 썼습니다. 이제 가깝지만 약간 더 복잡한 작업, 즉 특정 지정된 조건이 충족될 때 여러 셀의 텍스트를 붙이는 방법을 살펴보겠습니다.
하나의 회사 이름이 직원의 여러 다른 이메일에 해당할 수 있는 고객 데이터베이스가 있다고 가정해 보겠습니다. 우리의 임무는 회사 이름으로 모든 주소를 수집하고 연결(쉼표 또는 세미콜론으로 구분)하여 예를 들어 고객을 위한 메일링 리스트를 만드는 것입니다. 즉, 다음과 같은 출력을 얻습니다.
즉, 조건에 따라 텍스트를 접착(링크)하는 도구가 필요합니다. 수메슬리 (수미프), 그러나 텍스트의 경우.
방법 0. 공식
아주 우아하지는 않지만 가장 쉬운 방법입니다. 다음 행의 회사가 이전 행과 다른지 여부를 확인하는 간단한 공식을 작성할 수 있습니다. 다르지 않으면 쉼표로 구분된 다음 주소를 붙입니다. 다르면 다시 시작하여 누적을 "재설정"합니다.
이 접근 방식의 단점은 명백합니다. 얻은 추가 열의 모든 셀에서 각 회사의 마지막 셀(노란색)만 필요합니다. 목록이 큰 경우 빠르게 선택하려면 함수를 사용하여 다른 열을 추가해야 합니다. DLSTR (렌), 누적된 문자열의 길이 확인:
이제 그것들을 걸러내고 추가 사용을 위해 필요한 주소를 복사할 수 있습니다.
방법 1. 하나의 조건에 의한 접착의 매크로 기능
원래 목록이 회사별로 정렬되어 있지 않으면 위의 간단한 공식이 작동하지 않지만 VBA에서 작은 사용자 정의 함수로 쉽게 돌아다닐 수 있습니다. 바로 가기 키를 눌러 Visual Basic Editor를 엽니다. Alt + F11 또는 버튼을 사용하여 Visual Basic을 탭 개발자 (개발자). 열리는 창에서 메뉴를 통해 새 빈 모듈을 삽입하십시오. 삽입 – 모듈 거기에 우리 함수의 텍스트를 복사하십시오:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " 접착이 서로 같지 않음 - 오류로 종료 If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Function End If '모든 셀을 통과하여 조건을 확인하고 변수 OutText에 텍스트를 수집합니다. For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i '마지막 구분 기호 없이 결과 표시 MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End 기능
이제 Microsoft Excel로 돌아가면 기능 목록(버튼 fx 수식 입력줄 또는 탭에서 수식 – 함수 삽입) 우리의 기능을 찾을 수 있습니다 MergeIf 카테고리 사용자 정의 (사용자 정의). 함수에 대한 인수는 다음과 같습니다.
방법 2. 정확하지 않은 조건으로 텍스트 연결
매크로의 13번째 줄에 있는 첫 번째 문자를 바꾸면 = 근사 일치 연산자에 처럼, 그러면 초기 데이터와 선택 기준이 부정확하게 일치하여 접착을 수행할 수 있습니다. 예를 들어 회사 이름을 다른 변형으로 작성할 수 있는 경우 하나의 기능으로 모두 확인하고 수집할 수 있습니다.
표준 와일드카드가 지원됩니다.
- 별표(*) – 임의의 수의 문자를 나타냅니다(없음 포함).
- 물음표(?) – 단일 문자를 나타냅니다.
- 파운드 기호(#) – 한 자리 숫자(0-9)를 나타냅니다.
기본적으로 Like 연산자는 대소문자를 구분합니다. 예를 들어 "Orion"과 "orion"을 서로 다른 회사로 이해합니다. 대소문자를 무시하려면 Visual Basic 편집기에서 모듈의 맨 처음에 행을 추가할 수 있습니다. 옵션 비교 텍스트, Like가 대소문자를 구분하지 않도록 전환됩니다.
이러한 방식으로 조건을 확인하기 위해 매우 복잡한 마스크를 구성할 수 있습니다. 예를 들면 다음과 같습니다.
- ?1##??777RUS – 777부터 시작하는 1 지역의 모든 번호판 선택
- LLC* – 이름이 LLC로 시작하는 모든 회사
- ##7## – 세 번째 숫자가 7인 XNUMX자리 디지털 코드가 있는 모든 제품
- ???? – 다섯 글자의 모든 이름 등
방법 3. 두 가지 조건에서 텍스트를 붙이기 위한 매크로 기능
작업에서 하나 이상의 조건으로 텍스트를 연결해야 하는 경우 문제가 있을 수 있습니다. 예를 들어, 이전 표에서 도시가 포함된 열이 하나 더 추가되었고 주어진 회사뿐만 아니라 주어진 도시에 대해서도 접착을 수행해야 한다고 가정해 보겠습니다. 이 경우, 다른 범위 검사를 추가하여 기능을 약간 현대화해야 합니다.
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimiter As String, i As Long Delimeter = ", " '구분 문자(공백 또는 ; 등으로 대체 가능) e.) '검증 범위와 접착 범위가 서로 같지 않으면 오류와 함께 종료 If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If '모든 셀을 살펴보고 모든 조건을 확인하고 변수 OutText에 텍스트를 수집합니다. For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i '마지막 구분 기호 없이 결과 표시 MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
정확히 같은 방식으로 적용됩니다. 이제 인수만 더 지정하면 됩니다.
방법 4. 파워 쿼리에서 그룹화 및 붙이기
무료 파워 쿼리 추가 기능을 사용하면 VBA로 프로그래밍하지 않고도 문제를 해결할 수 있습니다. Excel 2010-2013의 경우 여기에서 다운로드할 수 있으며 Excel 2016에서는 기본적으로 이미 내장되어 있습니다. 작업 순서는 다음과 같습니다.
파워 쿼리는 일반 테이블을 사용하는 방법을 모르기 때문에 첫 번째 단계는 테이블을 "스마트" 테이블로 바꾸는 것입니다. 이렇게하려면 선택하고 조합을 누르십시오 Ctrl 키+T 또는 탭에서 선택 홈 – 테이블 형식 (홈 — 표 형식). 그러면 나타나는 탭에서 건설자 (디자인) 당신은 테이블 이름을 설정할 수 있습니다 (나는 표준 표 1):
이제 테이블을 파워 쿼리 추가 기능에 로드해 보겠습니다. 이렇게 하려면 탭에서 Data (Excel 2016이 있는 경우) 또는 파워 쿼리 탭(Excel 2010-2013이 있는 경우)에서 테이블에서 (데이터 — 테이블에서):
열리는 쿼리 편집기 창에서 헤더를 클릭하여 열을 선택합니다. 기업 정보 그리고 위의 버튼을 누르면 그룹 (그룹화 기준). 그룹화에서 새 열의 이름과 작업 유형을 입력하십시오. 모든 라인 (모든 행):
확인을 클릭하면 각 회사에 대한 그룹화된 값의 미니 테이블이 표시됩니다. 결과 열에서 셀의 흰색 배경(텍스트가 아님!)을 마우스 왼쪽 버튼으로 클릭하면 테이블의 내용이 명확하게 표시됩니다.
이제 열을 하나 더 추가해 보겠습니다. 여기서 함수를 사용하여 각 미니 테이블에 있는 주소 열의 내용을 쉼표로 구분하여 붙입니다. 이렇게 하려면 탭에서 열 추가 우리는 누릅니다 맞춤 항목 (열 추가 — 사용자 정의 열) 표시되는 창에서 새 열의 이름과 파워 쿼리에 기본 제공되는 M 언어의 커플링 수식을 입력합니다.
모든 M-함수는 Excel과 달리 대소문자를 구분합니다. 클릭 후 OK 연결된 주소가 있는 새 열을 얻습니다.
이미 불필요한 열을 제거하는 것이 남아 있습니다. 테이블 주소 (제목 오른쪽 클릭) 열 삭제) 탭을 클릭하여 결과를 시트에 업로드합니다. 홈 — 닫기 및 다운로드 (홈 — 닫기 및 로드):
중요한 뉘앙스: 이전 방법(함수)과 달리 파워 쿼리의 테이블은 자동으로 업데이트되지 않습니다. 나중에 소스 데이터에 변경 사항이 있는 경우 결과 테이블의 아무 곳이나 마우스 오른쪽 버튼으로 클릭하고 명령을 선택해야 합니다. 업데이트 및 저장 (새로 고치다).
- 긴 텍스트 문자열을 부분으로 나누는 방법
- 다른 셀의 텍스트를 하나로 붙이는 여러 가지 방법
- Like 연산자를 사용하여 마스크에 대해 텍스트 테스트