다양한 정도의 "직관성"으로 초기 데이터가 기록된 목록이 있다고 가정합니다(예: 주소 또는 회사 이름:
 |  |
동일한 도시 또는 회사가 여기에 잡종 변형으로 존재한다는 것을 분명히 알 수 있으며, 이는 분명히 미래에 이러한 테이블로 작업할 때 많은 문제를 일으킬 것입니다. 그리고 조금만 생각해보면 다른 분야에서도 비슷한 작업의 예를 많이 찾을 수 있습니다.
이제 이러한 비뚤어진 데이터가 정기적으로 제공된다고 상상해 보십시오. 즉, 이것은 일회성 "수동으로 수정하고 잊어버리십시오"라는 이야기가 아니라 정기적으로 많은 수의 셀에서 발생하는 문제입니다.
무엇을 할까요? "찾기 및 바꾸기" 상자를 통해 또는 Ctrl 키+H?
이러한 상황에서 가장 먼저 생각나는 것은 부정확하고 올바른 옵션을 일치시키는 미리 컴파일된 참고서에 따라 대량 대체를 하는 것입니다. 다음과 같습니다.
불행히도 이러한 작업이 널리 퍼져 있기 때문에 Microsoft Excel에는 이를 해결하기 위한 간단한 기본 제공 방법이 없습니다. 먼저 VBA 또는 Power Query에서 매크로 형태의 "중포병"을 사용하지 않고 수식을 사용하여 이 작업을 수행하는 방법을 알아보겠습니다.
사례 1. 일괄 전체 교체
상대적으로 간단한 경우부터 시작하겠습니다. 오래된 비뚤어진 텍스트를 새 텍스트로 교체해야 하는 상황입니다. 충분히.
두 개의 테이블이 있다고 가정해 보겠습니다.
첫 번째 - 회사의 원래 잡색 이름. 두 번째 - 서신의 참고서. 첫 번째 테이블의 회사 이름에서 열의 단어를 찾으면 찾다, 그런 다음 이 비뚤어진 이름을 올바른 이름으로 완전히 바꿔야 합니다. 대용품 두 번째 조회 테이블.
편의상:
- 두 테이블 모두 키보드 단축키를 사용하여 동적("스마트")으로 변환됩니다. Ctrl 키+T 또는 팀 삽입 – 테이블 (삽입 — 표).
- 표시되는 탭에서 건설자 (디자인) 이름이 지정된 첫 번째 테이블 Data, 그리고 두 번째 참조 테이블 - 대체.
공식의 논리를 설명하기 위해 조금 멀리 가자.
A2 셀의 첫 번째 회사를 예로 들어 나머지 회사는 잠시 잊어버리고 열에서 어떤 옵션을 결정해 보겠습니다. 찾다 그곳에서 만납니다. 이렇게하려면 시트의 빈 부분에서 빈 셀을 선택하고 거기에 기능을 입력하십시오 찾다 (찾기):
이 함수는 주어진 부분 문자열이 포함되어 있는지 확인합니다(첫 번째 인수는 열의 모든 값입니다) 찾다)를 소스 텍스트(데이터 테이블의 첫 번째 회사)에 넣고 텍스트를 찾은 문자의 서수를 출력하거나 하위 문자열을 찾을 수 없는 경우 오류를 출력해야 합니다.
여기서 트릭은 첫 번째 인수로 하나가 아닌 여러 값을 지정했기 때문에 이 함수도 결과로 하나의 값이 아니라 3개의 요소로 구성된 배열을 반환한다는 것입니다. 동적 배열을 지원하는 최신 버전의 Office 365가 없는 경우 이 수식을 입력하고 엔터 버튼 시트에서 바로 이 배열을 볼 수 있습니다.
이전 버전의 Excel이 있는 경우 엔터 버튼 결과 배열의 첫 번째 값만 볼 수 있습니다(예: 오류 #VALUE! (#값!).
두려워하지 않아도 됩니다 🙂 사실, 우리 공식은 작동하며 공식 막대에서 입력한 함수를 선택하고 키를 누르면 전체 결과 배열을 계속 볼 수 있습니다. F9(단, 누르는 것을 잊지 마세요. 야후!공식으로 돌아가기):
결과의 결과 배열은 원래 비뚤어진 회사 이름에서 (GK 모로스코 OAO) 열의 모든 값 중 찾다 두 번째에서만 발견 (모로스코), 그리고 연속 4번째 문자부터 시작합니다.
이제 수식에 함수를 추가해 보겠습니다. 관람(조회):
이 함수에는 세 가지 인수가 있습니다.
- 원하는 값 – 충분히 큰 숫자를 사용할 수 있습니다(중요한 것은 소스 데이터의 텍스트 길이를 초과한다는 것입니다).
- 본_벡터 – 원하는 값을 찾고 있는 범위 또는 배열. 이전에 소개된 기능은 다음과 같습니다. 찾다, {#VALUE!:4:#VALUE!} 배열을 반환합니다.
- 벡터_결과 – 원하는 값이 해당 셀에 있는 경우 값을 반환하려는 범위입니다. 다음은 열의 올바른 이름입니다. 대용품 우리의 참조 테이블.
여기서 주요하고 분명하지 않은 기능은 관람 정확히 일치하지 않으면 항상 가장 가까운 가장 작은(이전) 값을 찾습니다.. 따라서 원하는 값으로 많은 숫자(예: 9999)를 지정하면 강제로 관람 {#VALUE!:4:#VALUE!} 배열에서 가장 가까운 숫자(4)를 가진 셀을 찾고 결과 벡터에서 해당 값을 반환합니다. 즉, 열에서 올바른 회사 이름을 반환합니다. 대용품.
두 번째 뉘앙스는 기술적으로 공식이 배열 공식이라는 것입니다. 찾다 하나가 아닌 세 개의 값으로 구성된 배열을 결과로 반환합니다. 하지만 기능부터 관람 기본 배열을 지원하므로 키보드 단축키를 사용하여 이 공식을 고전적인 배열 공식으로 입력할 필요가 없습니다. Ctrl 키+변화+엔터 버튼. 간단한 것으로 충분할 것입니다 엔터 버튼.
그게 다야. 논리를 잡으시기 바랍니다.
완성 된 수식을 열의 첫 번째 셀 B2로 전송하는 것이 남아 있습니다. Fixed – 그리고 우리의 과제가 해결되었습니다!
물론 일반(스마트하지 않은) 테이블의 경우 이 공식도 훌륭하게 작동합니다(키 F4 관련 링크 수정):
사례 2. 대량 부분 교체
이 경우는 조금 더 까다롭습니다. 다시 두 개의 "스마트" 테이블이 있습니다.
수정해야 하는 비뚤어진 주소가 있는 첫 번째 테이블(나는 그것을 Data2). 두 번째 테이블은 참조서로, 이에 따라 주소 내부의 부분 문자열을 부분적으로 교체해야 합니다(저는 이 테이블을 교체2).
여기서 근본적인 차이점은 원본 데이터의 일부만 교체하면 된다는 것입니다. 예를 들어 첫 번째 주소에 잘못된 "성. 상트페테르부르크” 오른쪽에 "성. 상트페테르부르크”, 나머지 주소(우편번호, 거리, 집)는 그대로 둡니다.
완성된 공식은 다음과 같습니다. 다른+엔터 버튼):
여기서 주요 작업은 표준 Excel 텍스트 기능에 의해 수행됩니다. 대용품 (대리자), 3개의 인수가 있습니다.
- 소스 텍스트 – 주소 열에서 첫 번째 비뚤어진 주소
- 우리가 찾고 있는 것 – 여기서 우리는 함수와 함께 트릭을 사용합니다. 관람 (조회)열에서 값을 가져오는 이전 방법에서 찾다, 곡선 주소의 조각으로 포함됩니다.
- 무엇으로 대체할 것인가 – 같은 방식으로 열에서 해당 값에 해당하는 올바른 값을 찾습니다. 대용품.
이 공식을 입력하십시오. Ctrl 키+변화+엔터 버튼 사실 배열 수식이지만 여기에서도 필요하지 않습니다.
그리고 이러한 공식은 우아함에도 불구하고 몇 가지 단점이 있음을 분명히 알 수 있습니다(이전 그림의 #N/A 오류 참조).
- 함수 SUBSTITUTE는 대소문자를 구분합니다., 따라서 끝에서 두 번째 줄의 "Spb"는 교체 테이블에서 찾을 수 없습니다. 이 문제를 해결하기 위해 다음 중 하나를 사용할 수 있습니다. 자메니트 (바꾸다), 또는 미리 두 테이블을 동일한 레지스터로 가져옵니다.
- 텍스트가 처음에 정확하거나 그 안에 있는 경우 교체할 조각이 없습니다 (마지막 줄), 그러면 공식에서 오류가 발생합니다. 이 순간은 함수를 사용하여 오류를 가로채고 교체하여 중화할 수 있습니다. 오류 (IF 오류):
- 원본 텍스트가 포함된 경우 한 번에 디렉토리의 여러 조각, 그러면 우리 공식은 마지막 공식만 대체합니다(8번째 줄, Ligovsky «가로수 길« 로 변경 "프-티",하지만 "S-Pb" on "성. 상트페테르부르크” 더 이상, 왜냐하면 “S-Pb"는 디렉토리에서 상위에 있음). 이 문제는 자체 공식을 다시 실행하여 해결할 수 있지만 이미 열을 따라 Fixed:
완벽하지 않고 번거롭지만 같은 수동 교체보다 훨씬 낫지 않습니까? 🙂
PS
다음 기사에서는 매크로와 파워 쿼리를 사용하여 이러한 대량 대체를 구현하는 방법을 알아보겠습니다.
- SUBSTITUTE 함수가 텍스트를 대체하는 방법
- EXACT 함수를 사용하여 정확히 일치하는 텍스트 찾기
- 대소문자 구분 검색 및 대체(대소문자 구분 VLOOKUP)