Chuck에는 한 열에 일련의 IP 주소가 포함 된 워크 시트가 있습니다. 이것들은 192.168.2.1의 익숙한 형식입니다. 주소를 정렬하면 숫자로 정렬되지 않습니다. 예를 들어 Excel은 192.168.1.100과 192.168.1.2 사이에 192.168.1.100을 배치합니다. Chuck은 IP 주소 열을 적절한 순서로 표시하도록 정렬하는 방법이 있는지 궁금합니다.

이는 Excel이 IP 주소를 숫자 나 일련의 숫자가 아닌 텍스트로보기 때문에 발생합니다. 문제를 해결할 수있는 몇 가지 방법이 있으며 그 중 몇 가지를이 팁에서 설명하겠습니다. 데이터 및 해당 데이터 사용 방법에 정의 된대로 요구 사항에 적합한 접근 방식을 선택해야합니다.

한 가지 방법은 IP 주소의 각 옥텟이 3 자리 숫자로 구성되도록하는 것입니다. (옥텟은 마침표로 구분 된 IP 주소의 각 부분입니다.) 예를 들어 192.168.1.1과 같은 주소 대신 192.168.001.001을 사용합니다. 이 “전면 패드”는 각 옥텟을 0으로 채우고 모든 IP 주소가이 형식이면 올바르게 정렬됩니다.

수식을 사용하여 각 8 진수의 앞쪽 패딩을 확인하려면 다음을 사용할 수 있습니다.

=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 주소 옆의 열에 배치 한 다음 (해당 주소가 A1 셀에 있다고 가정) 필요한만큼 행 아래로 복사합니다. 정렬을 할 때 B 열을 기준으로 정렬하면 주소가 적절한 순서로 정렬됩니다.

많은 IP 주소로 작업하는 경우 IP 주소의 각 옥텟을 0으로 앞쪽에 채운 다음 완전히 형식화 된 IP를 반환하는 사용자 정의 함수를 만들 수 있습니다. 다음은 작업을 수행합니다.

Function FormatIP(IPAddr As String) As String     Dim Dot1 As Integer     Dim Dot2 As Integer     Dim Dot3 As Integer     Dim Octet1 As String     Dim Octet2 As String     Dim Octet3 As String     Dim Octet4 As String

Dot1 = InStr(1, IPAddr, ".", vbTextCompare)

Dot2 = InStr(Dot1 + 1, IPAddr, ".", vbTextCompare)

Dot3 = InStr(Dot2 + 1, IPAddr, ".", vbTextCompare)



Octet1 = Left(IPAddr, Dot1-1)

Octet2 = Mid(IPAddr, Dot1+1, Dot2-Dot1-1)

Octet3 = Mid(IPAddr, Dot2+1, Dot3-Dot2-1)

Octet4 = Mid(IPAddr, Dot3+1, Len(IPAddr))



FormatIP = Right("000" & Octet1, 3) & "."

FormatIP = FormatIP & Right("000" & Octet2, 3) & "."

FormatIP = FormatIP & Right("000" & Octet3, 3) & "."

FormatIP = FormatIP & Right("000" & Octet4, 3)

End Function

Excel에서는 A1 셀에 원래 IP 주소가 있다고 가정하여 이러한 방식으로 UDF를 사용할 수 있습니다.

=FormatIP(A1)

또 다른 접근 방식은 IP 주소를 별도의 열로 나누고 각 옥텟을 자체 열에 넣는 것입니다. 다음과 같은 방식으로 Text to Columns 도구를 사용하면 쉽게 수행 할 수 있습니다.

  1. IP 주소 오른쪽에 3 개의 빈 열이 있는지 확인합니다.

  2. IP 주소를 포함하는 셀 범위를 선택하십시오.

  3. 리본의 데이터 탭을 표시합니다.

  4. 데이터 도구 그룹에서 텍스트를 열 도구로 클릭합니다. Excel에서 텍스트를 열로 변환 마법사를 시작합니다. (그림 1 참조)

  5. 구분 라디오 버튼을 선택합니다.

  6. 다음을 클릭하십시오.

  7. 기타 옆의 확인란을 선택하고 확인란 오른쪽에 마침표를 입력합니다.

  8. 마침을 클릭하십시오.

완료되면 일반적으로 숫자를 정렬하는 것처럼 4 개의 열을 정렬 할 수 있습니다. 그런 다음 IP 주소를 다시 합치고 자 할 때 다음과 같은 공식을 사용할 수 있습니다.

=A1 & "." & B1 & "." & C1 & "." & D1

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13481)은 Microsoft Excel 2007, 2010, 2013 및 2016에 적용됩니다.