엑셀 크로스테이블 추출, 마이크로소프트 쿼리 KO 모든 기능

테이블 전체에서 데이터를 추출할 때 많은 파트너의 첫 번째 반응은 VLOOKUP 또는 INDEX+SMALL+IF 수식과 같은 함수입니다. 사실, 여러 열의 데이터를 추출한다면 오랫동안 많은 사람들의 구석에 방치되어 있던 Microsoft Query가 왕입니다! 작동이 쉽고 "일대다" 문제를 쉽게 해결할 수 있을 뿐만 아니라, 생성된 결과 테이블은 데이터 소스가 변경되면 결과가 동적으로 업데이트됩니다.

오늘 저는 소수의 사람들이 사용하지만 기적적인 효과를 주는 기능을 여러분과 공유하고 싶습니다. 두 테이블의 "일대다" 데이터 추출을 해결하는 데 도움이 되는 Microsoft Query 한 테이블을 사용하여 다른 테이블과 일치시키는 문제 테이블에서 특정 데이터를 생성하는 관행.

아래 그림과 같이 동일한 통합 문서에 두 개의 워크시트가 있습니다. "부서 인사 정보 표"에는 각 부서의 직원과 해당 감독자의 이름이 나열되어 있으며, "지방 판매 데이터 표"에는 담당 업무가 나열되어 있습니다. 여러 지역의 각 직원과 해당 지역의 3개월 매출 데이터입니다. 이제 이름 컬럼을 기준으로 두 테이블을 하나의 테이블로 요약해야 합니다.

오리지널 시계

필수 결과

Microsoft Query를 어떻게 사용하나요?

STEP 01 Microsoft Query 활성화 및 데이터 로드

(1) 새 통합 문서를 만들고 [데이터 가져오기] 그룹에서 "다른 소스에서" 드롭다운 메뉴를 클릭합니다. ] 탭(Microsoft)

[데이터 소스 선택] 창의 "데이터베이스" 옵션에서 "Excel 파일"을 클릭하고 아래의 "[쿼리 마법사]를 사용하여 쿼리 생성/편집"을 체크한 후 확인을 클릭하세요.

[워크북 선택] 창 오른쪽 디렉터리에서 데이터 소스 위치를 찾아 왼쪽 데이터베이스 이름에서 파일을 찾아 확인을 클릭합니다.

(2) 시스템이 가끔 다음과 같은 창이 뜹니다: "데이터 소스는 포함되지 않습니다. "표시되는 테이블", 이에 대해서는 걱정하지 마세요. 확인을 클릭하세요.

왼쪽 아래 [쿼리 마법사] 창에 들어가서 "옵션"을 클릭하세요 아래 버튼을 누른 후 오른쪽의 [테이블 옵션] 창을 열고 "시스템 테이블"을 체크한 후 "확인"을 클릭하세요.

데이터 소스의 워크시트가 [쿼리 마법사] 창에 나타납니다. 이는 Excel이 호출하기 때문입니다. 해당 워크시트 "시스템 테이블"을 확인하면 볼 수 있습니다.

다음으로 두 개의 워크시트를 선택하고 가운데의 ">" 버튼을 클릭하여 왼쪽에 "사용 가능한 테이블 및 열"을 추가합니다.

"양식을 쿼리에 연결할 수 없기 때문에 쿼리 마법사를 계속할 수 없습니다."라는 또 다른 창이 나타납니다. Microsoft에 있어야 합니다. 수동으로 연결하려면 쿼리의 테이블 간에 필드를 끌어서 연결하세요. "걱정하지 마시고 확인을 누르세요.

STEP 02 필수 항목에 따라 데이터를 매칭합니다

이때 엑셀과 같은 메뉴바가 있는 Microsoft Query 창으로 들어갑니다. 상단에는 테이블 영역이 있고, 가운데에는 현재 추가한 두 테이블과 해당 필드가 표시되어 있습니다. 아래 데이터 영역은 두 테이블을 병합한 결과입니다. 두 테이블을 지정하지 마십시오. 두 테이블은 이름 열

을 통해 일대일 대응입니다.

(1) 왼쪽의 "부서인사정보표"에서 "이름"을 마우스로 선택하고, 오른쪽 표의 "시도판매자료표"의 "이름"에 드래그한 후 마우스를 떼세요. . 이때 두 테이블의 '이름' 필드 사이에는 양쪽 끝에 작은 노드가 있는 연결선이 나타납니다. 아래 데이터 영역은 즉시 업데이트됩니다.

(2) 이름이 같은 열이 2개 있으므로 그 중 하나를 선택하고 메뉴 바의 [기록]에서 "열 삭제"를 클릭합니다.

STEP 03 결과 데이터를 Excel 워크시트로 반환합니다.

마지막으로 할 일은 결과를 EXCEL로 반환하는 것입니다.

(1) 메뉴바의 "SQL" 좌측 버튼을 클릭하면 데이터가 엑셀로 반환됩니다.

(2) EXCEL에 [데이터 가져오기] 창이 나타납니다. 이를 "테이블"로 표시하고 기존 워크시트에 배치하도록 선택했습니다.

반환된 결과는 다음과 같습니다.

이 간단한 3단계를 통해 필요한 데이터 일치를 완료하고 새 데이터 테이블을 생성했습니다.

기쁨

Microsoft Query에서 생성된 데이터가 슈퍼 테이블이고 피벗 테이블이나 피벗 차트를 직접 만들 수도 있다는 사실을 발견했습니다.

동시에 이 테이블은 데이터 소스에 동적으로 연결됩니다. 예를 들어 원본 데이터를 수정하는 경우 저장을 클릭하여 닫습니다.

새로고침하려면 반환된 결과를 마우스 오른쪽 버튼으로 클릭하세요.

이러한 방식으로 데이터가 동기화됩니다.

적용 조건

이 방법을 사용할 때는 데이터 소스의 표준화가 보장되어야 한다는 점에 유의해야 합니다. 워크시트에는 데이터 원본과 관련되지 않은 데이터가 포함될 수 없으며 테이블의 첫 번째 줄은 열 머리글이어야 합니다. 동적 연결을 구현하려는 경우 통합 문서와 워크시트의 이름과 위치를 수정할 수 없습니다.

어때요, 배워보셨나요? PQ보다 단순합니까, 아니면 함수보다 단순합니까?

관련 학습 권장 사항: excel 튜토리얼

위 내용은 엑셀 크로스테이블 추출, 마이크로소프트 쿼리 KO 모든 기능의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!