엑셀에서 IP 주소를 정렬할 때 일반적인 정렬 기능을 사용하면 192.168.1.1보다 192.168.1.10이 먼저 나오는 등 잘못된 결과가 나타납니다. 이는 엑셀이 IP 주소를 텍스트로 인식하기 때문입니다. 이 문제를 해결하기 위해 수식을 사용한 방법부터 VBA 코드, 텍스트 나누기 기능까지 다양한 접근법을 통해 IP 주소를 숫자 순서대로 정확하게 정렬하는 방법을 알아보겠습니다.
수식을 활용한 IP 주소 정렬 방법
가장 널리 사용되는 방법은 TEXT 함수를 활용하여 각 옥텟에 앞자리 0을 추가하는 것입니다. IP 주소 옆의 빈 셀에 다음 수식을 입력합니다: =TEXT(LEFT(A1,FIND(“.”,A1,1)-1),”000″) & “.” & TEXT(MID(A1,FIND( “.”,A1,1)+1,FIND(“.”,A1,FIND(“.”,A1,1)+1)-FIND(“.”,A1,1)-1),”000″) & “.” & TEXT(MID(A1,FIND(“.”,A1,FIND(“.”,A1,1)+1)+1,FIND(“.”,A1, FIND(“.”,A1,FIND(“.”,A1,1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1,1)+1)-1), “000”) & “.” & TEXT(RIGHT(A1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND( “.”,A1,1)+1)+1)),”000″). 이 수식을 아래로 복사한 후 결과값을 복사하여 다음 열에 값으로 붙여넣기 합니다. 그 다음 새로운 열을 기준으로 데이터를 오름차순으로 정렬하면 IP 주소가 올바른 순서로 정렬됩니다.
VBA 스크립트를 이용한 자동 정렬
반복적인 IP 주소 정렬 작업이 필요한 경우 VBA 코드를 활용하면 효율적입니다. Alt + F11 키를 눌러 Visual Basic 편집기를 열고 새 모듈을 삽입합니다. 다음 코드를 붙여넣습니다: Sub FormatIP() 코드로 시작하는 프로시저를 작성하여 정규표현식을 사용해 IP 주소를 자동으로 감지하고 각 옥텟에 앞자리 0을 추가합니다. 코드 실행 전에 도구 > 참조에서 Microsoft VBScript Regular Expressions 5.5를 체크해야 합니다. F5 키를 눌러 실행하면 범위를 선택하는 대화상자가 나타나고, 확인을 클릭하면 선택된 범위의 IP 주소가 자동으로 포맷됩니다.
텍스트 나누기 기능 활용법
엑셀의 기본 기능만으로도 IP 주소 정렬이 가능합니다. IP 주소가 포함된 열을 선택하고 데이터 탭의 텍스트 나누기를 클릭합니다. 구분 기호를 선택하고 다음을 클릭한 후, 기타 항목을 체크하고 마침표(.)를 입력합니다. 이렇게 하면 IP 주소가 4개의 열로 분리됩니다. 원본 IP 주소와 분리된 열을 모두 선택하고 데이터 > 정렬을 클릭합니다. 정렬 조건 추가를 통해 첫 번째 옥텟부터 네 번째 옥텟까지 순차적으로 정렬 기준을 설정하면 됩니다.
- 첫 번째 옥텟(B열)을 주 정렬 기준으로 설정
- 두 번째 옥텟(C열)을 보조 정렬 기준으로 추가
- 세 번째와 네 번째 옥텟도 동일하게 추가
- 모든 정렬 조건을 오름차순으로 설정
IP 주소 정렬 방법별 비교 분석
| 방법 | 장점 | 단점 |
|---|---|---|
| 수식 사용 | 정확하고 재사용 가능 | 복잡한 수식 작성 필요 |
| VBA 코드 | 자동화 가능, 반복 작업에 유리 | 프로그래밍 지식 필요 |
| 텍스트 나누기 | 기본 기능만 사용, 이해하기 쉬움 | 여러 단계 필요, 열 추가 |
| 수동 0 추가 | 가장 간단한 방법 | 대량 데이터 처리 시 비효율적 |
Google 시트에서의 IP 주소 정렬
Google 시트에서도 동일한 수식을 사용할 수 있습니다. TEXT, LEFT, MID, RIGHT, FIND 함수들이 모두 지원되므로 엑셀과 같은 방식으로 작업이 가능합니다. 수식을 적용한 후 복사하여 값으로 붙여넣기(Ctrl+Shift+V)를 실행하고 정렬하면 됩니다. 또한 Google 시트의 경우 사용자 정의 스크립트를 작성하여 SORT_IPS 함수를 만들어 사용할 수도 있습니다.
최신 버전의 Google 시트에서는 SORT 함수와 함께 사용자 정의 함수를 만들어 더욱 편리하게 IP 주소를 정렬할 수 있습니다. Apps Script 메뉴를 통해 JavaScript 기반의 정렬 함수를 작성하면 정규표현식을 활용한 자동 인식과 정렬이 가능합니다.
고급 IP 주소 정렬 기법
대용량 데이터나 서브넷이 다양한 경우에는 더 정교한 접근이 필요합니다. IP 주소를 32비트 정수로 변환하는 방법을 사용하면 더욱 정확한 정렬이 가능합니다. 각 옥텟에 256의 거듭제곱을 곱하여 단일 숫자로 변환하는 수식을 활용합니다: =(첫번째옥텟*256^3)+(두번째옥텟*256^2)+(세번째옥텟*256)+(네번째옥텟). 이 방법은 특히 네트워크 관리자나 시스템 엔지니어가 복잡한 IP 주소 범위를 다룰 때 유용합니다.
또한 CIDR 표기법이나 서브넷 마스크가 포함된 IP 주소 목록의 경우, 추가적인 전처리 과정을 통해 순수한 IP 주소 부분만 추출한 후 정렬 작업을 수행해야 합니다. 이러한 고급 기법들을 활용하면 어떤 형태의 IP 주소 데이터라도 정확하고 효율적으로 정렬할 수 있습니다.
