구글 시트와 엑셀의 연동을 통한 실시간 데이터 관리 및 병합

구글 시트와 엑셀의 연동을 통해 실시간으로 데이터를 관리하는 방법은 데이터 분석과 보고 작업에서 매우 유용하다. 특히 파워쿼리(Power Query)를 활용하면 구글 시트의 데이터를 실시간으로 가져와 엑셀에서 업데이트하고, 동일한 구조의 데이터를 하나의 시트로 병합할 수 있다. 이번 글에서는 구글 시트의 여러 시트를 엑셀로 가져오고, 동일한 구조의 데이터를 실시간으로 병합하는 방법을 중점적으로 다룰 것이다.

구글 시트와 엑셀의 연동을 통한 실시간 데이터 관리 및 병합

1. 파워쿼리(Power Query)를 사용한 실시간 데이터 연동

엑셀에서 파워쿼리(Power Query)는 실시간 데이터를 연동하는 데 매우 유용한 도구이다. 특히 구글 시트에서 데이터를 가져와 엑셀에서 관리할 때, 수동으로 데이터를 복사/붙여넣기 할 필요 없이 실시간으로 업데이트할 수 있다. 이 방법을 사용하면 엑셀 내에서 간편하게 구글 시트 데이터를 관리할 수 있다.

단계 1: 구글 시트의 공유 링크 생성

구글 시트의 데이터를 엑셀로 가져오려면, 먼저 구글 시트의 공유 링크를 생성해야 한다. 이를 통해 구글 시트의 데이터를 CSV 형식으로 엑셀에 연동할 수 있다.

1. 구글 시트 열기: 엑셀로 가져오려는 구글 시트를 연다.
2. 공유 설정:
   • 우측 상단의 공유(Share) 버튼을 클릭한다.
   • 링크 공유 옵션에서 “링크가 있는 모든 사람에게 보기 권한 부여”를 선택한다.
3. CSV 형식 링크 생성:
   • 공유된 링크에서 /edit 부분을 제거하고, /gviz/tq?tqx=out:csv로 변경한다. 이 링크는 구글 시트 데이터를 CSV 형식으로 제공한다.

단계 2: 파워쿼리로 구글 시트 데이터를 가져오기

이제 구글 시트에서 생성한 CSV 링크를 엑셀의 파워쿼리를 사용해 데이터를 가져온다.

1. 엑셀에서 파워쿼리 실행:
   • 데이터(Data) 탭에서 데이터 가져오기(Get Data) > 웹에서 가져오기(From Web)를 선택한다.
2. 구글 시트 링크 입력:
   • 구글 시트의 CSV 링크를 입력한 후 확인(OK)을 클릭한다.
3. 파워쿼리 편집기:
   • 데이터를 불러온 후, 파워쿼리 편집기에서 데이터를 미리 보고 필요에 따라 변환 작업을 수행할 수 있다.
4. 데이터 로드:
   • 변환이 완료되면 닫기 및 로드(Close & Load)를 클릭하여 데이터를 엑셀 시트로 로드한다.

이제 구글 시트의 데이터가 엑셀에 연동되어 실시간으로 업데이트된다. 구글 시트에서 데이터가 변경되면 엑셀에서 데이터 새로 고침(Refresh)을 클릭해 업데이트된 데이터를 불러올 수 있다.

2. 구글 시트의 여러 시트를 엑셀로 가져오기

구글 시트에는 여러 시트(Sheet)가 포함될 수 있다. 각 시트의 데이터를 엑셀로 가져오려면 각 시트별로 고유한 CSV 링크를 생성해야 한다.

단계 1: 각 시트의 공유 링크 생성

각 구글 시트에 대해 공유 링크를 생성하고, CSV 형식 링크로 변환해야 한다.

1. 시트 선택: 구글 스프레드시트에서 각각의 시트를 선택한다.
2. 공유 링크 생성:
   • 각 시트별로 공유 링크를 생성한 후, 해당 링크에서 /edit 부분을 /gviz/tq?tqx=out:csv&sheet=SheetName으로 변경한다.
   • 여기서 SheetName은 가져올 시트의 이름이다.

단계 2: 각 시트를 파워쿼리로 엑셀에 불러오기

1. 각 시트 불러오기:
   • 각 시트의 CSV 링크를 사용해 웹에서 가져오기(From Web) 기능으로 데이터를 가져온다.
   • 각각의 시트는 독립적인 쿼리로 불러와지며, 쿼리 연결만 유지하여 연결만 만들기(Create Connection Only) 옵션으로 데이터를 엑셀에 바로 로드하지 않고 연결을 만든다.
2. 파워쿼리에서 데이터 변환:
   • 각 쿼리에 대한 변환 작업을 수행하여 필요한 데이터만 남긴다.
3. 각 시트를 독립적으로 관리:
   • 각 시트별로 독립적인 쿼리를 유지하면서도 엑셀 내에서 실시간으로 데이터를 관리할 수 있다.

---

3. 동일한 구조의 데이터를 하나의 쿼리로 병합하기

구글 시트에서 여러 시트를 엑셀로 불러온 후, 이 데이터를 하나의 시트로 병합하고자 할 때 Append Queries(쿼리 추가) 기능을 사용할 수 있다. 동일한 구조를 가진 여러 데이터를 하나로 결합할 때 매우 유용하다.

단계 1: 동일한 구조의 데이터 전처리

병합하려는 여러 쿼리(시트)에서 데이터의 구조가 동일한지 확인한다. 여기서 데이터 구조가 동일하다는 것은 각 쿼리에서 열 이름과 열의 개수가 동일하다는 것을 의미한다.

1. 열 순서 맞추기:
   • 열의 순서가 다른 경우 파워쿼리에서 열 순서 정렬을 통일시킨다.
2. 필요 없는 열 제거:
   • 필요 없는 열은 열 제거(Remove Columns)를 통해 제거하고, 병합에 필요한 데이터만 남긴다.

단계 2: 쿼리 추가(Append Queries)

이제 동일한 구조를 가진 여러 쿼리를 하나의 쿼리로 병합한다.

1. 쿼리 추가 실행:
   • 파워쿼리 편집기에서 홈(Home) 탭을 선택하고, 쿼리 추가(Append Queries) 옵션을 선택한다.
   • 두 개의 쿼리를 병합하거나, 세 개 이상의 테이블 추가(Append Three or More Tables) 옵션을 선택하여 여러 시트를 병합할 수 있다.
2. 쿼리 선택:
   • 병합할 쿼리들을 선택한 후 확인(OK)을 클릭하면 모든 쿼리가 하나의 테이블로 결합된다.
3. 데이터 로드:
   • 병합된 데이터를 확인한 후, 닫기 및 로드(Close & Load)를 통해 데이터를 엑셀 시트로 로드한다.

이렇게 하면 여러 시트에서 가져온 동일한 구조의 데이터를 하나의 엑셀 시트로 결합할 수 있다. 병합된 데이터는 실시간으로 업데이트되며, 구글 시트에서 데이터가 변경되면 엑셀에서 데이터 새로 고침(Refresh)을 통해 최신 데이터를 가져올 수 있다.

---

결론

파워쿼리(Power Query)를 활용해 구글 시트와 엑셀을 실시간으로 연동하면, 데이터 관리와 분석이 훨씬 더 간편해진다. 구글 시트의 여러 시트를 각각 엑셀로 가져오고, 동일한 구조의 데이터를 하나로 병합하는 과정을 통해 데이터 흐름을 체계적으로 관리할 수 있다. 실시간 데이터 연동과 병합 기능은 대규모 데이터 관리에서 매우 유용한 도구가 될 것이다.