이 글에서는 4가지 유용한 방법을 활용해 Excel에서 자동으로 업데이트되는 데이터베이스를 만드는 방법을 소개합니다. 동적 데이터를 다룰 때 반드시 필요한 기능으로, 특히 데이터베이스가 외부 데이터 소스에 의존하는 경우 원본 데이터 변경 사항을 자동으로 반영하는 것이 매우 중요합니다. 지금부터 실제 예제를 통해 각 방법을 하나씩 살펴보겠습니다.
Excel에서 자동 업데이트되는 데이터베이스를 만드는 4가지 방법
1. 웹에서 데이터 추출하여 자동 업데이트되는 데이터베이스 만들기
과제: 미국 뉴욕의 14일간 일기예보를 웹에서 추출하여 자동으로 업데이트되는 Excel 데이터베이스를 만듭니다.
문제 분석: 온라인에는 일기예보를 제공하는 여러 웹사이트가 있습니다. 이번 예제에서는 timeanddate.com의 뉴욕 일기예보 페이지(https://www.timeanddate.com/weather/usa/new-york/ext)를 사용하겠습니다. 아래 스크린샷의 표를 복사해서 Excel 워크시트에 붙여넣으면 데이터베이스를 만들 수 있지만, 원본 링크와 연결하지 않으면 웹사이트의 최신 데이터가 반영되지 않습니다. 반면 원본 링크와 연결하면 Excel에서 데이터베이스를 수동 또는 자동으로 업데이트할 수 있는 옵션을 제공합니다.
해결 방법: 아래 단계를 따라 일기예보 데이터베이스를 자동으로 업데이트되도록 설정합니다.
1단계: 웹에 연결하기
- Excel 리본 메뉴에서 데이터 탭으로 이동합니다.
- 데이터 가져오기 및 변환 그룹에서 웹에서 옵션을 클릭합니다.
- 대화 상자에 데이터를 추출할 URL을 붙여넣고 확인을 누릅니다.
- 탐색기(Navigator) 창에서 해당 웹사이트에서 발견된 표들을 확인할 수 있습니다. 테이블 보기를 통해 추출된 데이터를 표 형태로 미리 볼 수 있습니다.
- Excel 데이터베이스로 불러오기 전에 데이터를 편집하려면 데이터 변환 옵션을 클릭합니다.
2단계: 데이터 변환하기
- 파워 쿼리 편집기에서 불필요한 열을 제거할 수 있습니다. 열 머리글을 마우스 오른쪽 버튼으로 클릭한 후 제거 옵션을 선택하면 됩니다.
- 여러 열을 제거하고 데이터베이스로 가져올 6개 열만 남겼습니다.
- 이제 홈 탭에서 닫기 및 로드 버튼을 클릭합니다.
- 닫기 및 로드 옵션을 선택합니다.
3단계: Excel 데이터베이스로 데이터 가져오기
- 데이터 가져오기 창에서 기존 워크시트 옵션을 선택하고, 데이터를 가져올 시작 셀을 지정한 후 확인을 클릭합니다.
- 웹사이트 원본과 연결된 Excel 데이터베이스가 성공적으로 생성되었습니다.
4단계: 자동 업데이트 기능 설정하기
- 데이터베이스 표를 클릭합니다.
- Excel 리본 메뉴의 데이터 탭으로 이동합니다.
- 모두 새로 고침 버튼을 클릭합니다.
- 연결 속성 옵션을 선택합니다.
- 새로 고침 간격 입력란에 데이터베이스를 자동으로 새로 고칠 시간을 설정하고 확인을 누릅니다.
함께 읽으면 좋은 글: Excel 데이터베이스 함수 사용법 (예제 포함)
2. 피벗 테이블로 자동 업데이트되는 데이터베이스 만들기
이번에는 원본 데이터셋을 기반으로 피벗 테이블을 만드는 방법을 알아봅니다. 피벗 테이블은 Excel에서 데이터베이스 역할을 하므로, 피벗 테이블의 자동 새로 고침 기능을 활성화하면 Excel 데이터베이스도 자동으로 업데이트됩니다. 다음 단계를 따라 해 보세요.
1단계: 피벗 테이블 만들기
매장의 판매 내역을 담고 있는 데이터셋이 있다고 가정해 보겠습니다.
피벗 테이블을 만들려면,
- 전체 데이터셋을 선택합니다.
- 삽입 탭으로 이동합니다.
- 피벗 테이블 버튼을 클릭합니다.
- 테이블/범위에서 옵션을 선택합니다.
- 테이블 또는 범위에서 피벗 테이블 창에서 새 워크시트 옵션을 선택하고 확인을 누릅니다.
- 새 워크시트에 피벗 테이블 데이터베이스가 생성됩니다.
2단계: 피벗 테이블 자동 새로 고침 기능 활성화하기
이 방법은 데이터셋이 변경될 때마다가 아니라 통합 문서를 열 때마다 피벗 테이블을 업데이트합니다. 즉, 피벗 테이블의 부분적인 자동화라고 할 수 있습니다. 자동 새로 고침 기능을 활성화하는 방법은 다음과 같습니다.
- 피벗 테이블의 아무 셀이나 마우스 오른쪽 버튼으로 클릭하여 바로 가기 메뉴를 엽니다.
- 바로 가기 메뉴에서 피벗 테이블 옵션을 선택합니다.
- 피벗 테이블 옵션 창에서 데이터 탭으로 이동한 후 파일을 열 때 데이터 새로 고침 옵션에 체크합니다.
- 마지막으로 확인을 눌러 창을 닫습니다.
함께 읽으면 좋은 글: Excel VBA로 간단한 데이터베이스 만드는 방법
3. VBA 코드로 피벗 테이블 자동 새로 고침하기
과제: 간단한 VBA 코드를 사용하면 원본 데이터를 변경할 때 피벗 테이블이 자동으로 업데이트됩니다. 무엇보다 이전 방법과 달리 파일을 닫았다가 다시 열 필요 없이 즉시 반영된다는 점이 큰 장점입니다.
해결 방법: 아래 가이드를 따라 해 보세요!
- 워크시트 이름을 마우스 오른쪽 버튼으로 클릭하고 코드 보기 옵션을 선택합니다.
코드: 비주얼 베이직 편집기에 다음 VBA 코드를 입력합니다.
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
실행 결과: 이 VBA 코드는 원본 파일의 셀 데이터가 변경될 때마다 실행됩니다. 해당 원본과 연결된 모든 피벗 테이블이 즉시 자동으로 업데이트됩니다.
위 과정을 확인하기 위해 VBA 시트의 원본 데이터를 기반으로 pivot_table이라는 시트에 피벗 테이블을 만들었습니다. Apple의 수량을 50에서 30으로 변경하자, pivot_table_VBA 시트의 데이터베이스도 자동으로 함께 업데이트되었습니다.
참고: 통합 문서의 모든 피벗 테이블이 아니라 특정 피벗 테이블만 자동으로 새로 고침하고 싶다면 아래 코드를 사용하세요. 이 코드는 데이터 원본이 변경될 때 pivot_table_VBA 시트의 피벗 테이블만 업데이트합니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("pivot_table_VBA").PivotTables("PivotTable2").PivotCache.Refresh
End Sub이 코드에서 pivot_table_VBA는 PivotTable2가 포함된 시트 이름입니다. 워크시트와 피벗 테이블의 이름은 손쉽게 확인할 수 있습니다.
함께 읽으면 좋은 글: Excel 양식으로 데이터베이스 만드는 방법
4. 다른 시트의 데이터를 참조하는 자동 업데이트 데이터베이스 만들기
과제: 다른 워크시트의 표에서 데이터를 가져오는 데이터베이스 표를 만듭니다. 원본 데이터가 변경되면 데이터베이스도 자동으로 업데이트되어야 합니다.
해결 방법: source_table 시트에 제품명과 개별 단가를 담은 표가 있습니다.
이제 판매 내역을 나타내는 데이터베이스(database 시트)를 만들었습니다. 이 데이터셋에는 "UnitPrice"라는 열이 포함되어 있는데, 이 UnitPrice 열의 각 데이터를 source_table 시트의 표에서 참조해야 합니다. 데이터베이스 표의 UnitPrice 열에 데이터를 삽입하려면,
- 셀 F3(Apple의 단가)에 등호(=)를 입력합니다.
- source_table 시트로 이동합니다.
- 셀 C3(Apple의 단가)를 클릭합니다.
- Enter 키를 누릅니다.
- 이제 채우기 핸들을 사용하여 source_table 시트의 원본 표에서 참조한 데이터로 UnitPrice 열을 채웁니다.
실행 결과: source_table 시트의 원본 표에서 데이터를 변경하면 데이터베이스가 자동으로 업데이트됩니다.
함께 읽으면 좋은 글: Excel로 고객 데이터베이스 관리하는 방법
주의 사항
방법 3의 VBA 코드를 사용하면 피벗 테이블이 자동화되지만 실행 취소 기록이 사라진다는 점에 유의해야 합니다. 변경을 한 후에는 이전 상태로 되돌릴 수 없습니다. 이것이 VBA 코드로 피벗 테이블을 자동 업데이트할 때의 단점입니다.
결론
지금까지 4가지 방법으로 Excel에서 자동으로 업데이트되는 데이터베이스를 만드는 방법을 알아보았습니다. 이 글이 실무에서 자동 업데이트 기능을 더욱 자신 있게 활용하는 데 도움이 되기를 바랍니다. 궁금한 점이나 제안 사항이 있다면 아래 댓글로 남겨주세요.
관련 글
- Excel에서 재고 데이터베이스 만드는 방법 (3가지 쉬운 방법)
- Excel에서 관계형 데이터베이스 만들기 (쉬운 단계별 가이드)
- Excel에서 검색 가능한 데이터베이스 만드는 방법 (2가지 빠른 팁)