엑셀은 정말 다양한 분야에서 활용되는 강력한 도구입니다. 특히 VBA를 함께 사용하면 상상하는 거의 모든 작업을 엑셀 안에서 처리할 수 있는데요. 그래서 지도 데이터를 이용해 두 장소 간의 거리를 구하는 것도 물론 가능합니다. 이 글에서는 구글 지도(Google Maps)와 엑셀을 연동하여 거리를 계산하는 방법을 단계별 설명과 함께 자세히 소개하겠습니다.
무료 엑셀 워크북 파일을 내려받아 직접 따라 하며 연습해 보실 수도 있습니다.
사용자 정의 함수로 구글 지도 거리 계산하기
이번 예제에서는 구글 지도를 이용해 '맥아더 공원(MacArthur Park)'과 '저지 시티(Jersey City)' 사이의 거리를 구해 보겠습니다.
시작하기 전에 반드시 알아야 할 중요한 사항이 있습니다. 엑셀에서 구글 지도를 이용해 거리를 계산하려면 API 키가 필요합니다. API란 Application Programming Interface(응용 프로그래밍 인터페이스)의 약자로, 엑셀이 API 키를 통해 구글 지도와 통신하면서 필요한 데이터를 받아오는 방식입니다. 참고로 Bing Maps처럼 무료 API 키를 제공하는 지도 서비스도 있지만, 구글 지도는 무료 API를 제공하지 않습니다. 임시방편으로 무료 키를 확보하더라도 정상적으로 작동하지 않으므로, 실제 사용하려면 해당 링크에서 API 키를 구매해야 합니다.
여기서는 데모용으로 무료 API 키를 임시로 사용했습니다. 실제로는 완벽하게 작동하지 않으며, 예시 화면을 보여주기 위한 용도임을 미리 말씀드립니다.
이제 VBA로 Calculate_Distance라는 이름의 사용자 정의 함수(User-Defined Function)를 만들어 거리를 조회하겠습니다. 이 함수는 출발지(Starting Place), 목적지(Destination), API 키 세 가지 정보를 활용합니다. 그럼 절차를 하나씩 살펴보겠습니다.
진행 단계:
- ALT + F11 키를 눌러 VBA 편집기 창을 엽니다.
- 메뉴에서 삽입(Insert) > 모듈(Module)을 클릭해 새 모듈을 생성합니다.
- 모듈 창에 아래 코드를 입력합니다.
Public Function Calculate_Distance(start As String, dest As String)
Dim first_Value As String, second_Value As String, last_Value As String
first_Value = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
second_Value = "&destinations="
last_Value = "&mode=car&language=pl&sensor=false&key=YOUR_KEY"
Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = first_Value & Replace(start, " ", "+") & second_Value & Replace(dest, " ", "+") & last_Value
mitHTTP.Open "GET", Url, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
If InStr(mitHTTP.ResponseText, """distance"" : {") = 0 Then GoTo ErrorHandl
Set mit_reg = CreateObject("VBScript.RegExp"): mit_reg.Pattern = """value"".*?([0-9]+)": mit_reg.Global = False
Set mit_matches = mit_reg.Execute(mitHTTP.ResponseText)
tmp_Value = Replace(mit_matches(0).Submit_matches(0), ".", Application.International(xlListSeparator))
Calculate_Distance = CDbl(tmp_Value)
Exit Function
ErrorHandl:
Calculate_Distance = -1
End Function- 코드 입력이 끝났으면 그대로 워크시트로 돌아갑니다.
코드 해설:
- 먼저 Public Function 프로시저인 Calculate_Distance를 선언했습니다.
- 그다음 함수 인수로 사용할 변수 first_Value, second_Value, last_Value를 선언합니다.
- 각 변수에 값을 지정하고(값 자체가 의미를 잘 설명합니다), ServerXMLHTTP 객체를 mitHTTP로 설정해 GET 방식으로 요청을 보낼 준비를 합니다(이 객체 속성은 필요에 따라 POST 방식도 지원합니다).
- Url은 앞서 지정한 값들을 모두 결합한 것이며, mitHTTP 객체의 Open 속성에서 이를 사용합니다.
- 값이 할당된 후에는 라이브러리 함수가 나머지 계산을 자동으로 처리합니다.
이제 우리만의 사용자 정의 함수를 사용할 준비가 끝났습니다.
- C8 셀에 아래 수식을 입력합니다.
=Calculate_Distance(C4,C5,C6)
- 마지막으로 Enter 키를 누르면 거리가 계산됩니다. 결과는 미터(meter) 단위로 표시됩니다.
함께 읽으면 좋은 글: 엑셀에서 두 주소 간 운전 거리 계산하는 방법
구글 지도로 거리 계산 시 주의사항
- 유효한 API 키가 반드시 필요합니다.
- 위 코드의 결과물은 미터(meter) 단위로 출력됩니다.
- 사용자 정의 함수는 장소 이름을 직접 사용하므로 좌표를 입력할 필요가 없습니다.
- 정확하고 유효한 장소명을 사용했는지 꼭 확인하세요.
구글 지도 활용 거리 계산의 장단점
장점
- 채우기 핸들(Fill Handle) 도구로 수식을 복사할 수 있어 많은 수의 장소를 한 번에 처리하기에 매우 적합합니다. 구글 지도 사이트에서는 이런 방식이 불가능합니다.
- 계산 속도가 상당히 빠릅니다.
- 위도·경도 같은 좌표 없이도 장소 이름만으로 계산할 수 있습니다.
단점
- 좌표(GPS 좌표)를 직접 사용할 수는 없습니다.
- 지도 이미지나 경로는 제공되지 않고 오직 거리 값만 얻을 수 있습니다.
- 장소 이름이 부정확하게 일치하면(근사 매칭) 올바르게 작동하지 않습니다.
마무리
지금까지 소개한 방법을 활용하면 구글 지도와 엑셀을 연동해 두 장소 간 거리를 충분히 계산할 수 있습니다. 과정 중 궁금한 점이 있다면 댓글로 얼마든지 질문해 주세요. 여러분의 피드백도 환영합니다. 더 많은 엑셀 팁은 ExcelDemy에서 확인하실 수 있습니다.
관련 글
- 엑셀에서 두 GPS 좌표 간 거리 계산하는 방법
- 엑셀에서 방위각과 거리로 좌표 계산하기
- 엑셀에서 마할라노비스 거리(Mahalanobis Distance) 계산하기 (단계별 가이드)
- 엑셀에서 두 주소 간 마일(Miles) 거리 계산하기 (2가지 방법)
- 엑셀에서 맨해튼 거리(Manhattan Distance) 계산하기 (2가지 방법)
- 엑셀에서 레벤슈타인 거리(Levenshtein Distance) 계산하기 (4가지 쉬운 방법)