엑셀에서 국가 IP 조회 방법 알려드릴게요

인터넷이 되지 않는 단절된 망, 인트라넷 같은 망에서 서버, 시스템 등 여러 장비에서 접근한 IP를 조회할 때 사용하기 좋은 방법이니 확인해보세요 🙂

국가 IP 대역 수집

국가 IP 대역을 수집하기 위해 구글에 “국가 IP 대역” 작성 후 검색 또는 아래의 링크를 누릅니다. 한국인터넷정보센터에서 국가 IP 리스트를 확인할 수 있습니다. 

https://xn--3e0bx5euxnjje69i70af08bea817g.xn--3e0b707e/jsp/statboard/IPAS/ovrse/natal/IPaddrBandCurrent.jsp

한국인터넷정보센터에서 제공하고 있는 국가별 IP 주소 대역별 현황을 볼 수 있습니다. 전체 국가 CSV 다운로드하고 실행합니다.

국가 IP 대역별 현황 리스트를 볼 수 있습니다. 

IP 대역별 10진수 변환

시작 IP를 기준으로 10진수로 변환해야 합니다. E열 옆에 하나의 열을 삽입합니다.

시작 IP에 대한 10진수를 만들었습니다. 10진수로 만드는 이유는 엑셀에서 IP는 문자에 불과합니다. 10진수로 변경하여 값으로 조회가 가능하게 만드는 작업입니다. 
코드는 아래와 같습니다.

=LEFT(D2, SEARCH(".", D2) - 1) * POWER(2, 24) + INT(SUBSTITUTE(MID(D2, SEARCH(".", D2) + 1, 11), ".", "", 2)) * POWER(2, 16) + INT(MID(D2, SEARCH("-", SUBSTITUTE(D2, ".", "-", 2)) + 1, 7)) * POWER(2, 8) + MID(D2, SEARCH("-", SUBSTITUTE(D2, ".", "-", 3)) + 1, 3)

10진수 시작 IP를 2행에서 232675행까지 드래그 또는 마우스가 +로 보이게 한 후 더블클릭합니다. 상단 이미지처럼 완성되면 아래의 이미지를 보고 따라 하시면 됩니다.

10진수로 변환한 셀에서 마우스 우클릭합니다. 정렬에서 숫자 오름차순 정렬을 선택합니다.

상단처럼 정렬되면 완성입니다. 

IP 조회 Sheet 만들기

다음은 국가 IP를 조회할 수 있는 시트를 만드는 작업을 해야 합니다.

+를 눌러서 Sheet1을 만든 후 “IP 조회”, “국가명”, “10진수”로 작성합니다. 
10진수에 아래 코드를 작성해주세요. 상단과 동일합니다.

=LEFT(A2, SEARCH(".", A2) - 1) * POWER(2, 24) + INT(SUBSTITUTE(MID(A2, SEARCH(".", A2) + 1, 11), ".", "", 2)) * POWER(2, 16) + INT(MID(A2, SEARCH("-", SUBSTITUTE(A2, ".", "-", 2)) + 1, 7)) * POWER(2, 8) + MID(A2, SEARCH("-", SUBSTITUTE(A2, ".", "-", 3)) + 1, 3)

INDEX, MATCH 함수를 이용하여 국가명을 불러올 수 있습니다. 아래의 코드를 복사하여 붙여 넣으세요.

=INDEX('ipv4'!$A$2:$G$232675,MATCH(Sheet1!C2,'ipv4'!$F$2:$F$232675,1),2)

제 블러그는 팁을 알려드리기 위한 포스팅이니 함수에 대해 자세한 사항은 각설하고 간단하게 말씀드리겠습니다. INDEX(조회할 범위, MATCH(확인할 값, 확인할 값을 조회할 범위, 1(보다작은 값), 보여줄 값의 위치)가 됩니다.
A2:G232675 사이에 A$2:$G$232675 “$”가 들어가는 이유는 드래그했을 때 행열 값을 고정하기 위함이니 참고하시면 됩니다.

국가 IP 조회에서 국가코드를 좀 더 보기 쉽게 국가명까지 넣었습니다. 국가명에 대해 검색하시면 국가코드와 국가명을 확인할 수 있는 사이트가 있으니 참고하셔서 만들어 보시면 될 것 같습니다. 

마치며

잘 만들어 보셨나요? ipvoid.com, 키사 whois, 바이러스토탈 등 IP 조회할 수 있는 사이트가 많지만 조회가 불가능할 때가 간혹 있습니다. 이럴 때 엑셀을 이용한 국가 IP 조회를 이용하여 사용해보시길 바랍니다.