엑셀은 단순한 로컬 스프레드시트를 넘어, 웹과 연결하여 실시간 데이터를 가져오고 분석할 수 있는 강력한 도구로 진화했습니다. 주식 시세, 환율 정보, 공공 데이터, 또는 특정 웹사이트의 정보 등을 엑셀로 직접 가져와 분석하고 싶다면 바로 이 웹 데이터를 엑셀로 가져와 파싱(Parsing)하는 기본적인 방법을 알아야 합니다.
왜 엑셀 웹 함수를 알아야 할까요?
빠르게 변화하는 현대 비즈니스 환경에서는 최신 정보에 기반한 의사결정이 중요합니다.
웹은 방대한 실시간 데이터의 보고이지만, 이 데이터를 수동으로 복사하고 붙여넣는 것은 매우 비효율적이고 오류 발생 가능성이 높습니다. 엑셀의 웹 관련 함수를 사용하면:
- 자동화된 데이터 수집: 웹에서 데이터를 자동으로 가져와 수동 작업을 줄입니다.
- 실시간 정보 업데이트: 최신 웹 데이터를 엑셀에 반영하여 항상 최신 정보로 분석할 수 있습니다.
- 다양한 웹 데이터 활용: 웹 서비스(API), XML 형식 데이터 등 다양한 웹 소스를 활용합니다.
- 보고서의 동적 구성: 웹에서 가져온 데이터를 기반으로 동적인 보고서와 대시보드를 구축합니다.
이 함수들은 엑셀을 단순한 계산기를 넘어, 웹 데이터를 실시간으로 모니터링하고 분석하는 강력한 '데이터 커넥터'로 만들어줍니다.
주요 내용들
다음 웹 함수 및 관련 기능들을 실무 예제와 함께 자세히 살펴보겠습니다.
- WEBSERVICE: 웹 서비스(API)에서 데이터 가져오기
- FILTERXML: XML 형식의 웹 데이터에서 특정 요소 추출 (웹 크롤링 응용)
- (응용) Power Query (데이터 가져오기 및 변환): 웹에서 데이터 가져오기 및 변환 (함수는 아니지만 웹 데이터 가져오기의 필수 기능)
실무 예제로 배우는 엑셀 웹 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 웹 데이터 가져오기 및 파싱의 기본기를 보여드리겠습니다.
1. WEBSERVICE: 웹 서비스(API)에서 데이터 가져오기
WEBSERVICE 함수는 웹 서비스의 URL을 인수로 받아, 해당 웹 서비스의 응답(주로 XML, JSON 또는 텍스트 형식)을 문자열로 반환합니다. 이는 API(Application Programming Interface)를 통해 데이터를 가져올 때 사용됩니다.
- 기본 형식: =WEBSERVICE(url)
- url: 데이터를 가져올 웹 서비스의 URL.
실무 예제: 특정 웹 서비스(예: 가상의 환율 API)를 통해 실시간 USD/KRW 환율 정보를 가져오고 싶습니다. (이 예시에서는 공개된 무료 웹 서비스 URL을 사용합니다.)
- 사용할 API URL: http://www.floatrates.com/daily/usd.xml (미국 달러 기준 환율 정보를 XML 형식으로 제공하는 공개 API)
예제 목표: 위 URL에서 미국 달러에 대한 환율 정보를 XML 문자열로 가져오세요.
해결 방법: B2 셀에 다음 수식을 입력합니다. =WEBSERVICE("http://www.floatrates.com/daily/usd.xml")
결과: B2 셀에 XML 형식의 긴 텍스트 문자열이 반환됩니다. (예: <fxrate><baseCurrency>USD</baseCurrency><targetCurrency>KRW</targetCurrency>...)
최상급 실무 활용: WEBSERVICE 함수는 웹 기반 API를 통해 동적인 데이터를 가져올 때 첫 번째 단계입니다.
주식 시세, 환율, 날씨 정보, 공공 데이터 포털의 API 등 다양한 웹 서비스에서 원시 데이터를 엑셀로 직접 가져올 수 있습니다. 하지만 이 함수만으로는 XML/JSON 문자열을 원하는 값으로 파싱하기 어렵습니다. 이때 다음 함수인 FILTERXML이 필요합니다.
2. FILTERXML: XML 형식의 웹 데이터에서 특정 요소 추출
FILTERXML 함수는 WEBSERVICE 등으로 가져온 XML 형식의 문자열에서 XPath(XML Path Language)를 사용하여 특정 요소를 추출합니다. 이는 XML 데이터를 원하는 값으로 파싱할 때 매우 강력합니다.
- 기본 형식: =FILTERXML(xml, xpath)
- xml: XML 형식의 텍스트 문자열 (예: WEBSERVICE 함수의 결과).
- xpath: 추출하려는 XML 요소의 경로를 지정하는 XPath 문자열.
XPath 기본 문법 (간략 예시):
- //element: 모든 element 노드
- //element/subelement: element 노드 아래의 subelement 노드
- //element[@attribute='value']: 특정 속성을 가진 element 노드
- //element[position()=N]: N번째 element 노드
실무 예제: 1번 예제에서 WEBSERVICE로 가져온 XML 문자열(B2 셀)에서 '대한민국 원(KRW)'의 환율(rate)과 '대상 통화(targetCurrency)'를 추출하고 싶습니다.
- XML 구조 (간략화):
-
XML
<fxrates> <item> <targetCurrency>KRW</targetCurrency> <exchangeRate>1350.50</exchangeRate> <rate>1350.50</rate> ... </item> <item>...</item> </fxrates>
예제 목표: B2 셀의 XML에서 KRW의 rate 값과 **targetCurrency (KRW)**를 추출하세요.
해결 방법:
- KRW 환율 추출: C2 셀에 다음 수식을 입력합니다. =FILTERXML(B2, "//item[targetCurrency='KRW']/rate")
- //item: XML 문서 내의 모든 <item> 태그
- [targetCurrency='KRW']: 그 중에서 <targetCurrency>가 'KRW'인 <item> 태그
- /rate: 해당 <item> 태그 아래의 <rate> 태그 값
- KRW 대상 통화 추출: D2 셀에 다음 수식을 입력합니다. =FILTERXML(B2, "//item[targetCurrency='KRW']/targetCurrency")
결과:
- C2 셀: 1350.50 (또는 해당 시점의 실제 환율)
- D2 셀: KRW
최상급 실무 활용: WEBSERVICE와 FILTERXML을 조합하면 웹 API를 통해 특정 데이터를 실시간으로 가져와 엑셀에 파싱할 수 있습니다. 이는 주식 실시간 시세, 환율 대시보드, 특정 웹사이트의 공개 데이터 요약 등 웹 기반의 동적 보고서를 구축할 때 핵심적인 기술입니다. 복잡한 XPath 문법을 익히면 거의 모든 XML 데이터를 자유자재로 추출할 수 있습니다.
3. (응용) Power Query (데이터 가져오기 및 변환): 웹 데이터 가져오기의 강력한 대안
WEBSERVICE와 FILTERXML은 함수 기반의 웹 데이터 가져오기입니다. 하지만 엑셀 2016 이후 버전 및 Microsoft 365에는 훨씬 더 강력하고 사용자 친화적인 웹 데이터 가져오기 도구인 Power Query (데이터 가져오기 및 변환 기능)가 내장되어 있습니다. 이는 함수는 아니지만, 웹 데이터 처리에 있어 필수적인 기능이므로 함께 소개합니다.
Power Query 사용 방법 (간략 예시):
- 데이터 탭으로 이동합니다.
- 데이터 가져오기 그룹에서 웹에서를 선택합니다.
- 웹 페이지 URL을 입력하고 확인을 클릭합니다.
- Power Query 탐색기 창이 열리고, 웹 페이지의 표 데이터를 자동으로 인식하여 보여줍니다.
- 원하는 표를 선택하고 로드를 클릭하면 해당 데이터가 엑셀 시트로 가져와집니다. 데이터 변환을 클릭하면 Power Query 편집기에서 데이터 전처리(열 제거, 형식 변경, 병합 등)를 수행할 수 있습니다.
실무 예제: 어떤 웹사이트에 있는 공개된 테이블 형태의 데이터를 엑셀로 가져와야 합니다. (예: Wikipedia의 국가별 인구 목록)
- 예시 URL: https://ko.wikipedia.org/wiki/%EA%B5%AD%EA%B0%80%EB%B3%84_%EC%9D%B8%EA%B5%AC_%EB%AA%A9%EB%A1%9D
예제 목표: 위 Wikipedia 페이지에서 '국가별 인구 목록' 테이블을 엑셀로 가져오세요.
해결 방법:
- 데이터 탭 → 데이터 가져오기 → 웹에서 클릭.
- URL에서 대화 상자에 위 URL을 입력하고 확인.
- 탐색기 창이 열리면, 웹 페이지에서 인식된 테이블 목록이 나타납니다. '표 1(국가별 인구 목록)'과 같은 원하는 테이블을 선택하고 로드를 클릭합니다.
결과: 해당 웹 테이블의 데이터가 엑셀 시트의 새 테이블로 가져와집니다. 이 데이터는 나중에 데이터 탭에서 새로 고침 버튼을 클릭하면 웹에서 최신 정보로 업데이트됩니다.
최상급 실무 활용: Power Query는 WEBSERVICE나 FILTERXML보다 훨씬 강력하고 유연하게 다양한 웹 페이지의 테이블 데이터를 가져오고 전처리할 수 있습니다.
웹 스크래핑에 가깝게 작동하며, 복잡한 웹 데이터 통합 및 변환 파이프라인을 구축하는 데 필수적인 도구입니다. 정기적으로 업데이트되는 웹 기반 보고서, 경쟁사 정보 모니터링, 공공 데이터 분석 등에 광범위하게 활용됩니다.
WEBSERVICE로 웹 서비스(API)에서 원시 데이터를 가져오고, FILTERXML로 XML 형식의 데이터를 파싱하는 방법을 배웠습니다. 또한, 웹 데이터 가져오기의 강력한 대안인 Power Query의 기본 활용법도 함께 살펴보았습니다.
이 기능들은 엑셀을 웹과 연결하여 실시간 데이터를 가져오고 분석하는 첫걸음이자, 동적인 보고서를 만드는 데 필수적인 기반입니다.