엑셀로 IP주소가 특정 IP주소 범위에 속하는지 구하기

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제

 

01. 문제

Q. 숫자 범위에 해당하는 값 삭제 엑셀로 이거 어떻게 가능할까요? 대한민국 국가 IP 주소의 시작주소/끝 주소가 있는 상황에서  샘플 IP들이 해당되는 범위에 있으면 삭제하고 범위에 없으면 남기고 싶어요. 즉, 대한 민국 IP가 아닌 IP들을 찾고 싶어요.

 

IP 범위 및 샘플 IP의 값은 다음과 같다.

 

문제를 정리하면 다음과 같다.

 

1. 여러 개의 IP주소 대역 (시작주소와 끝주소 조합)이 있다.

2. 여러 개의 샘플 IP주소가 있다.

3. 샘플 IP중에서 1에서 제시된 대역에 포함되지 않는 IP만 남기자.

 

비교해야 될 것이 너무 많아서 VBA를 사용하지 않고 순수하게 엑셀만으로는 처리하기 힘들 수 도 있을 것 같다.

 

02. 문제 해결 방법

문제를 해결하는데 가장 중요한 능력은  IP주소가 특정 IP대역에 포함되는지를 알아내는 방법이다.

 

(1) IP주소가 특정 IP주소 대역에 포함되는 지를 확인하는 방법

IP 주소가 대역 시작 주소보다 크거나 같고, 대역의 종료 주소보다 작거나 같으면, IP주소가 해당 대역에 포함 된다고 볼 수 있다. 

 

다음과 같은 의문이 든다면 정상이다.

 

지금 주소가 작거나 같다고 했는 데, 그럼 2개의 IP주소의 대소관계를 확인할 수 있어야 되는거 아닌가?

 

(2) 두 IP 주소를 비교하는 방법

사실 IP주소는 32비트 숫자라고 볼 수 있다. 바이트가 4개 모인 값으로 0~255.0~255.0~255.0~255 가 나열되어 있다고 볼 수 있다. 즉 IP주소는 하나의 숫자와 일대일 매칭된다.

 

IP주소를 숫자로 변경한 후, 숫자끼리 비교하면 대소관계를 확인할 수 있다.

 

역시 다음과 같은 의문이 든다면 정상이다.

 

그럼 저 텍스트에서 숫자를 추출할 수 있어야 겠군?

 

(3) IP주소에서 비교할 수 있는 숫자값 추출하기

1.11.0.0을 바로 숫자로 변경한다면 그것보다 이 문제를 빨리 해결할 수 있는 방법은 없을 것이다. 그러나 지금 당장은 그 방법이 생각 나지 않는다.

1.11.0.0 에서 첫번째 1, 두번째 11, 세번째 0, 네번째 0을 분해하는 방법이 필요하다. 

 

이 작업을 위해서는 FIND 함수와 LEFT, MID, RIGHT 함수를 적절할게 사용하면 될 것 같다.

힌트는 "." 의 위치를 기준으로 숫자를 추출하는 것이다. 

 

A2셀에 분석할 IP주소(1.11.0.0)가 있다고 가정해보자.

 

- 첫번째 점의 위치는 다음과 같이 구할 수 있다.

=FIND(".",A2)

- 두번째 점의 위치는 다음과 같이 구할 수 있다.

=FIND(".",A2,FIND(".",A2)+1)

- 세번째 점의 위치는 다음과 같이 구할 수 있다.

=FIND(".",A2, FIND(".",A2,FIND(".",A2)+1)+1)

 

- 첫번째 묶음인 "1"은 다음과 같은 수식으로 구할 수 있다.

=LEFT(A2,FIND(".",A2)-1)

주소의 처음부터 첫번째 점의 위치 바로 전까지 잘라내기

LEFT 함수의 두번째 인자는 잘라낼 글자의 길이다. 따라서 첫번째 점의 위치에서 1을 빼주면 우리가 원하는 길이가 나온다.

 

- 두번째 묶음인 "11"은 다음과 같은 수식으로 구할 수 있다.

=MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)

 첫번째 점의 위치 다음부터, 두번째 점의 위치 바로전까지 잘라내기

 MID함수의 세번째 인자는 글자의 길이이다 따라서 두 점의 위치를 뺀 후 1을 더 빼주면 우리가 원하는 길이가 나온다.

 

- 세번째 묶음인 "0"은 다음과 같은 수식으로 구할 수 있다.

MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-FIND(".",A2,FIND(".",A2)+1)-1)

두번째 점의 위치 다음부터, 세번째 점의 위치 바로전까지 잘라내기

 

- 네번째 묶음인 "0"은 다음과 같은 수식으로 구할 수 있다.

RIGHT(A2,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))

오른쪽에서부터 세번째 점이 등장하기까지의 길이만큼 잘라내기

RIGHT의 두번째 인자는 잘라낸 글자의 길이이다. LEN(A2)를 이용하여  전체 글자 수에서 세번째 점의 위치를 뺀 후 1을 더하면 잘라낼 길이가 나온다.

 

(4) 모든 범위 값에 대해서 포함 여부 확인하기

IP주소에서 숫자만 추출했으면 IP주소 하나를 대표할 수 있는 숫자로 변경해주자.

 

첫번째 숫자 * (256)^3 + 두번째 숫자 * (256)^2 + 세번째 숫자 * 256 + 네번째 숫자로 연산하면 원하는 숫자가 나올 것이다. 그러나 여기서는 넉넉하게 1000의 배수를 곱해주자. 계산하기 쉽게...

 

첫번째 숫자 * 1000,000,000 + 두번째 숫자 * 1,000,000 + 세번째 숫자 * 1,000 + 네번째 숫자

 

위와 같은 수식으로 IP 주소에 대응하는 숫자를 만들 수 있다.

 

입력된 IP 주소의 숫자값이 특정 구간의 시작값보다 크거나 같고, 특정 구간의 종료값 보다 작거나 같으면 구간에 포함된다고 볼 수 있다.  

 

 

(5) 참고하면 좋은 포스트

 

엑셀 문자열 처리 (LEFT함수, RIGHT함수, LEN함수, FIND함수)

 

엑셀에서 배열 수식 사용하기 (1)

 

엑셀에서 배열 수식 사용하기 (2)

 

03. 예제

(1) 시트 구성

 

시트 구성은 위와 같다. B열 D열에 IP주소에 대응하는 숫자값을 위에서 구한 수식으로 채울 것이다.

또한 F열에도 샘플 IP주소에 대응하는 숫자 값을 채울 것이다.

 

(2)  IP주소에 대응하는 숫자값 채우기

 

다음 수식을 B2에 채운다.

 

=RIGHT(A2,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))+1000*MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-FIND(".",A2,FIND(".",A2)+1)-1)+1000000*MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)+1000000000*LEFT(A2,FIND(".",A2)-1)

 

 

숫자가 필요한 셀에 위의 수식을 모두 복사한 후 셀 서식의 표기 형식을 알아보기 쉽게 변경한다.

 

완료된 시트는 다음과 같다.

(3) 포함여부 확인하기

G2에 다음과 같은 수식을 입력해보자.

=SUM(IF(($B$2:$B$43<=F2)*($D$2:$D$43>=F2),1,0))

 

위의 수식은 배열 수식으로 입력할 때 Ctrl + Shift + Enter를 입력해야 한다.

 

위 수식은 F열의 값이 B열보다 크고 D열보다 작으면 1을 아니면 0으로 구성된 배열을 반환하고, 그 반환 된 배열을 다시 SUM함수를 통해 계산하는 것이다. 1이 하나라도 있으면 SUM값이 0이 아닌 값이 되게 된다. 

 

1이 있다는 이야기는 주소 범위에 들어간다는 것을 의미하므로 적어도 하나의 주소구간에 샘플 IP가 들어간다는 것을 의미한다.