보다 최근에 우리는 인터넷에서 XML 데이터를 가져오기 위해 FILTER.XML 함수를 사용하는 것에 대해 논의했습니다. 실제로 이 함수가 의도한 주요 작업입니다. 그러나 그 과정에서 이 기능의 예상치 못한 또 다른 아름다운 사용이 나타났습니다. 즉, 끈적한 텍스트를 셀로 빠르게 나누는 것입니다.
다음과 같은 데이터 열이 있다고 가정해 보겠습니다.
물론 편의를 위해 회사 이름, 도시, 거리, 집과 같은 별도의 열로 나누고 싶습니다. 다양한 방법으로 이 작업을 수행할 수 있습니다.
- 열별 텍스트 탭에서 Data (데이터 — 텍스트를 열로) 그리고 세 걸음 텍스트 파서. 그러나 내일 데이터가 변경되면 전체 프로세스를 다시 반복해야 합니다.
- 이 데이터를 파워 쿼리에 로드하고 분할한 다음 시트에 다시 업로드한 다음 데이터가 변경되면 쿼리를 업데이트합니다(이미 더 쉽습니다).
- 즉석에서 업데이트해야 하는 경우 매우 복잡한 수식을 작성하여 쉼표를 찾고 그 사이의 텍스트를 추출할 수 있습니다.
그리고 FILTER.XML 함수를 사용하면 더 우아하게 할 수 있지만 그것과 무슨 관련이 있습니까?
FILTER.XML 함수는 XML 코드(특수 태그와 속성으로 표시된 텍스트)를 초기 인수로 받은 다음 이를 구성 요소로 구문 분석하여 필요한 데이터 조각을 추출합니다. XML 코드는 일반적으로 다음과 같습니다.
XML에서 각 데이터 요소는 태그로 묶어야 합니다. 태그는 꺾쇠 괄호로 묶인 일부 텍스트(위의 예에서는 관리자, 이름, 이익)입니다. 태그는 항상 여는 것과 닫는 쌍으로 옵니다(시작 부분에 슬래시가 추가됨).
FILTER.XML 함수는 필요한 모든 태그의 내용(예: 모든 관리자의 이름)을 쉽게 추출하고 (가장 중요하게) 한 목록에 모두 한 번에 표시할 수 있습니다. 따라서 우리의 임무는 소스 텍스트에 태그를 추가하여 FILTER.XML 함수에 의한 후속 분석에 적합한 XML 코드로 변환하는 것입니다.
목록에서 첫 번째 주소를 예로 들면 다음 구성으로 변환해야 합니다.
전역 열기 및 닫기 모든 텍스트 태그를 호출했습니다. t, 그리고 각 요소를 구성하는 태그는 s., 그러나 다른 명칭을 사용할 수 있습니다. 중요하지 않습니다.
이 코드에서 들여쓰기와 줄 바꿈을 제거하면 완전히 선택적이며 명확성을 위해 추가되면 이 모든 것이 한 줄로 바뀝니다.
그리고 쉼표를 몇 개의 태그로 대체하여 소스 주소에서 이미 비교적 쉽게 얻을 수 있습니다. 기능을 사용하여 대용품 (대리자) 그리고 기호로 붙이기 & 여는 태그와 닫는 태그의 시작과 끝:
결과 범위를 수평으로 확장하기 위해 표준 함수를 사용합니다. 트랜스 (바꾸어 놓다), 그 안에 공식을 래핑:
이 전체 디자인의 중요한 기능은 동적 배열을 지원하는 Office 2021 및 Office 365의 새 버전에서 입력을 위해 특별한 제스처가 필요하지 않다는 것입니다. 입력하고 클릭하기만 하면 됩니다. 엔터 버튼 – 수식 자체는 필요한 셀 수를 차지하며 모든 것이 잘 작동합니다. 아직 동적 배열이 없었던 이전 버전에서는 수식을 입력하기 전에 먼저 충분한 수의 빈 셀을 선택해야 하며(여백이 있을 수 있음) 수식을 생성한 후 키보드 단축키를 누릅니다. Ctrl 키+변화+엔터 버튼배열 수식으로 입력합니다.
줄 바꿈을 통해 함께 붙어 있는 텍스트를 하나의 셀로 분리할 때도 유사한 트릭을 사용할 수 있습니다.
이전 예제와의 유일한 차이점은 쉼표 대신 보이지 않는 Alt + Enter 줄 바꿈 문자를 대체한다는 것입니다. 이 문자는 코드 10의 CHAR 함수를 사용하여 수식에서 지정할 수 있습니다.
- Excel에서 줄 바꿈(Alt + Enter) 작업의 미묘함
- Excel에서 열로 텍스트 나누기
- 텍스트를 SUBSTITUTE로 바꾸기