원본 텍스트에서 키워드를 검색하는 것은 데이터로 작업할 때 가장 일반적인 작업 중 하나입니다. 다음 예를 사용하여 여러 가지 방법으로 솔루션을 살펴보겠습니다.
당신과 내가 키워드 목록(자동차 브랜드 이름)과 모든 종류의 예비 부품에 대한 큰 테이블을 가지고 있다고 가정해 보겠습니다. 예비 부품이 둘 이상에 해당하는 경우 설명에 때때로 그러한 브랜드가 하나 이상 포함될 수 있습니다. 자동차 브랜드. 우리의 임무는 지정된 구분 문자(예: 쉼표)를 통해 이웃 셀에서 감지된 모든 키워드를 찾아 표시하는 것입니다.
방법 1. 파워 쿼리
물론 먼저 키보드 단축키를 사용하여 테이블을 동적("스마트")으로 전환합니다. Ctrl 키+T 또는 명령 홈 – 테이블 형식 (홈 — 표 형식), 이름을 지정합니다(예: 우표и 예비 부품) 탭에서 선택하여 Power Query 편집기에 하나씩 로드합니다. 데이터 – 테이블/범위에서 (데이터 — 테이블/범위에서). 파워 쿼리가 별도의 추가 기능으로 설치된 이전 버전의 Excel 2010-2013이 있는 경우 원하는 단추가 탭에 있습니다. 파워 쿼리. 새 버전의 Excel 365가 있는 경우 단추 테이블/레인지에서 지금 거기에 전화 잎으로 (시트에서).
파워 쿼리에서 각 테이블을 로드한 후 다음 명령을 사용하여 Excel로 돌아갑니다. 홈 — 닫기 및 로드 — 닫기 및 로드... — 연결만 생성 (홈 - 닫기 및 로드 - 닫기 및 로드 대상… - 연결만 생성).
이제 중복 요청을 생성해 보겠습니다. 예비 부품마우스 오른쪽 버튼으로 클릭하고 선택하면 중복 요청 (중복 쿼리), 그런 다음 결과 복사 요청의 이름을 다음으로 바꿉니다. 결과 우리는 그와 계속 일할 것입니다.
행동의 논리는 다음과 같습니다.
- 고급 탭에서 열 추가 팀을 선택 맞춤 항목 (열 추가 — 사용자 정의 열) 수식을 입력하십시오 = 브랜드. 클릭 후 OK 우리는 새로운 열을 얻게 될 것입니다. 여기서 각 셀에는 자동차 브랜드라는 키워드 목록이 포함된 중첩 테이블이 있습니다.
- 추가된 열의 머리글에서 이중 화살표가 있는 버튼을 사용하여 모든 중첩 테이블을 확장합니다. 동시에 예비 부품에 대한 설명이 있는 줄에 브랜드 수의 배수가 곱해지고 "예비 부품-브랜드"의 가능한 모든 쌍 조합을 얻을 수 있습니다.
- 고급 탭에서 열 추가 팀을 선택 조건부 열 (조건부 열) 소스 텍스트(파트 설명)에서 키워드(브랜드)의 발생을 확인하기 위한 조건을 설정합니다.
- 대소문자를 구분하지 않고 검색하려면 수식 입력줄에 세 번째 인수를 수동으로 추가하세요. Compare.OrdinalIgnoreCase 발생 확인 기능에 텍스트.포함 (수식 입력줄이 보이지 않으면 탭에서 활성화할 수 있습니다. 검토):
- 결과 테이블을 필터링하여 마지막 열에 하나만 남겨두고 불필요한 열을 제거합니다. 발생.
- 명령을 사용하여 동일한 설명 그룹화 그룹화 기준 탭 변환 (변환 — 그룹화 기준). 집계 작업으로 다음을 선택합니다. 모든 라인 (모든 행). 출력에서 필요한 자동차 제조업체 브랜드를 포함하여 각 예비 부품에 대한 모든 세부 정보가 포함된 테이블이 있는 열을 얻습니다.
- 각 부분의 등급을 추출하려면 탭에 다른 계산 열을 추가하십시오. 열 추가 – 사용자 정의 열 (열 추가 — 사용자 정의 열) 테이블로 구성된 수식을 사용합니다(이는 열에 있습니다. 세부 정보) 및 추출된 열의 이름:
- 결과 열의 헤더에서 이중 화살표가 있는 버튼을 클릭하고 명령을 선택합니다. 값 추출 (값 추출)원하는 구분 문자로 스탬프를 출력하려면:
- 불필요한 열 제거 세부 정보.
- 설명에서 브랜드가 발견되지 않은 사라진 부품을 결과 테이블에 추가하기 위해 쿼리를 결합하는 절차를 수행합니다. 결과 원래 요청으로 예비 부품 단추 결합 탭 홈 (홈 - 쿼리 병합). 연결 타입 - 외부 조인 오른쪽 (오른쪽 외부 조인):
- 남은 것은 여분의 열을 제거하고 나머지 열의 이름을 바꾸고 이동하는 것입니다. 그러면 작업이 해결됩니다.
방법 2. 공식
Excel 2016 이상 버전이 있는 경우 새 기능을 사용하여 매우 간단하고 우아한 방식으로 문제를 해결할 수 있습니다. 콤바인 (텍스트 조인):
이 공식의 논리는 간단합니다.
- 함수 검색 선택 (찾기) 부품의 현재 설명에서 차례로 각 브랜드의 발생을 검색하고 브랜드가 발견된 기호의 일련 번호 또는 #VALUE! 오류를 반환합니다. 브랜드가 설명에 없는 경우.
- 그런 다음 기능을 사용하여 IF (만약) и 에오십카 (이에러) 오류를 빈 텍스트 문자열 ""로 바꾸고 문자의 서수를 브랜드 이름 자체로 바꿉니다.
- 빈 셀과 찾은 브랜드의 결과 배열은 함수를 사용하여 지정된 구분 문자를 통해 단일 문자열로 어셈블됩니다. 콤바인 (텍스트 조인).
속도 향상을 위한 성능 비교 및 파워 쿼리 쿼리 버퍼링
성능 테스트를 위해 100개의 예비 부품 설명 표를 초기 데이터로 사용하겠습니다. 그것에 우리는 다음과 같은 결과를 얻습니다.
- 공식에 의한 재계산 시간(방법 2) – 9초 수식을 전체 열과 2초에 처음 복사할 때. 반복적으로 (버퍼링이 영향을 미칠 것입니다).
- 파워 쿼리 쿼리(방법 1)의 업데이트 시간은 110초로 훨씬 더 나쁩니다.
물론 특정 PC의 하드웨어와 설치된 Office 버전 및 업데이트에 따라 많이 다르지만 전체적인 그림은 분명하다고 생각합니다.
파워 쿼리 쿼리 속도를 높이려면 조회 테이블을 버퍼링하겠습니다. 우표, 쿼리 실행 과정에서 변경되지 않고 지속적으로 다시 계산할 필요가 없기 때문입니다(사실상 파워 쿼리처럼). 이를 위해 우리는 기능을 사용합니다 테이블.버퍼 기본 제공 파워 쿼리 언어 M에서
이렇게 하려면 쿼리를 엽니다. 결과 그리고 탭에서 검토 버튼을 누르십시오 고급 편집기 (보기 — 고급 편집기). 열리는 창에서 새 변수가 있는 줄을 추가합니다. 마키 2, automaker 디렉터리의 버퍼링된 버전이 될 것이며 나중에 다음 쿼리 명령에서 이 새 변수를 사용합니다.
이러한 개선 후 요청의 업데이트 속도가 거의 7배(최대 15초) 증가합니다. 꽤 다른 것 🙂
- 파워 쿼리의 퍼지 텍스트 검색
- 수식으로 대량 텍스트 대체
- List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체