Excel에서 연결(Connection)과 쿼리(Query)란 무엇인가?
연결(Connections)은 통합 문서 내부의 관계뿐만 아니라 다른 통합 문서와의 외부 관계를 구축하는 데 사용되는 동적 링크입니다.
쿼리(Queries)는 여러 통합 문서에서 데이터를 가져온 뒤, 필요에 따라 데이터를 추가·삭제·변경할 수 있는 강력한 도구입니다. 아래 예시를 통해 이 두 용어를 더욱 쉽게 이해할 수 있습니다.
이번 글에서는 연결과 쿼리를 활용하여 하나의 데이터 테이블을 만들어 보겠습니다. Excel의 파워쿼리(Power Query) 기능으로 두 개의 테이블을 병합할 것입니다.
표 1에는 학생 ID, 학생 이름, 수학 점수 정보가 담겨 있으며, 첫 번째 테이블의 이름은 Math_Scores입니다.
표 2에는 물리와 화학 점수가 담겨 있으며, 두 번째 테이블의 이름은 Physics_Chem_scores입니다. 여기서 Name(이름) 열이 두 테이블을 잇는 공통 열 역할을 합니다.
1단계: Excel에서 표 만들기
- 데이터 범위를 표 형식으로 변환합니다.
- 데이터 범위 안의 아무 셀이나 선택한 후 Ctrl + T를 누릅니다.
- 표 만들기(Create Table) 대화 상자가 나타나면 확인(OK)을 클릭합니다.
- 데이터 범위가 표로 변환됩니다.
- 같은 방법으로 두 번째 데이터 범위도 표 2로 변환합니다.
2단계: 새 워크시트에 데이터 로드하기
- 표 1의 아무 셀이나 선택합니다.
- 데이터(Data) 탭으로 이동하여 테이블/범위에서(From Table/Range)를 선택합니다.
- 파워쿼리 편집기(Power Query Editor) 창이 열립니다.
- 창 왼쪽 패널에서 Math_Scores 테이블을 확인할 수 있습니다.
- 파워쿼리 창에서 홈(Home) 탭으로 이동한 뒤 닫기 및 로드(Close & Load) 아이콘을 클릭하여 드롭다운 메뉴를 엽니다.
- 메뉴에서 닫기 및 다음으로 로드...(Close & Load To)를 선택합니다.
- 데이터 가져오기(Import Data) 대화 상자가 나타납니다.
- 연결만 만들기(Only Create Connection)를 선택하고 확인(OK)을 눌러 진행합니다.
- 두 번째 테이블에도 동일한 과정을 반복합니다.
- 쿼리 및 연결(Queries & Connections) 창에서 두 테이블을 모두 확인할 수 있습니다.
- Math_Scores가 첫 번째 테이블이고, Physics_Chem_scores가 두 번째 테이블입니다.
3단계: 파워쿼리 편집기에서 테이블 병합하기
- 데이터(Data) 탭으로 이동하여 데이터 가져오기(Get Data)를 선택하면 드롭다운 메뉴가 나타납니다.
- 메뉴에서 쿼리 결합(Combine Queries) >> 병합(Merge)을 차례로 선택하면 병합(Merge) 창이 열립니다.
- 병합 창에서 테이블과 일치시킬 열을 선택하여 병합 테이블을 만듭니다.
- 첫 번째 드롭다운 메뉴에서 Math_Scores를 선택합니다.
- 두 번째 드롭다운 메뉴에서 Physics_Chem_scores를 선택합니다.
- 조인 종류(Join Kind) 항목에서 왼쪽 외부(첫 번째의 모든 행, 두 번째의 일치 행 / Left Outer)를 선택합니다.
- 두 테이블 모두에서 Name 열을 선택합니다.
- 확인(OK)을 클릭하여 진행합니다.
- 그 결과 파워쿼리 편집기에서 병합된 테이블을 볼 수 있습니다. 다만 Physics_Chem_scores 열에는 아직 값이 없습니다.
- 해당 열에 값을 채우려면 확장(Expand) 아이콘을 클릭합니다.
- 메시지 상자가 나타나면 Name 열의 선택을 해제합니다.
- 원래 열 이름을 접두사로 사용(Use original column name as prefix) 옵션에 체크합니다.
- 확인(OK)을 클릭하여 진행합니다.
- 이제 창에서 병합이 완료된 테이블을 확인할 수 있습니다.
4단계: 워크시트에 병합 테이블 삽입하기
- 파워쿼리 편집기 창의 홈(Home) 탭에서 닫기 및 로드(Close & Load) 아이콘을 클릭합니다.
- 드롭다운 메뉴에서 닫기 및 다음으로 로드...를 선택합니다.
- 데이터 가져오기 대화 상자에서 테이블(Table)과 새 워크시트(New worksheet)를 선택합니다.
- 이 데이터를 데이터 모델에 추가(Add this data to the Data Model)에 체크합니다.
- 로드(Load)를 클릭하여 진행합니다.
- 마지막으로 Merge 1이라는 이름의 새 시트에서 병합된 테이블을 확인할 수 있습니다.
5단계: 연결과 쿼리의 차이 확인하기
- 모든 작업을 마치면 쿼리 및 연결(Queries & Connections) 창을 통해 결과를 확인할 수 있습니다.
- 쿼리(Queries) 탭에는 3개의 쿼리가 표시됩니다. 이처럼 테이블은 쿼리로 분류됩니다.
- 연결(Connections) 탭에는 통합 문서 데이터 모델이라는 1개의 연결이 표시됩니다.
연습용 통합 문서 다운로드
직접 실습해 보고 싶다면 아래 통합 문서를 다운로드하세요.
함께 읽으면 좋은 글
- Excel에서 데이터 원본 만드는 방법
- 다른 Excel 파일과 데이터 연결 설정하는 방법
- 파일을 열지 않고 Excel 데이터 연결 새로 고치는 방법
- [해결!] Excel에서 외부 데이터 연결이 비활성화된 경우
- Excel 데이터 연결이 새로 고침되지 않을 때 대처법
- Excel VBA: 모든 데이터 연결 한 번에 새로 고치는 방법
<< Excel 데이터 연결 | Excel 데이터 가져오기 | Excel 배우기로 돌아가기