IP 주소가 포함 된 Excel에서 셀을 올바르게 정렬하려면 어떻게해야합니까? 따르기 쉬운 방식으로 정렬되지 않습니다. 예를 들어 다음과

현재 많은 IP 주소 목록 (수천 개의 IP 주소)을 사용하고 있습니다.

그러나 IP 주소가 포함 된 열을 정렬하면 직관적이거나 따르기 쉬운 방식으로 정렬되지 않습니다.

예를 들어 다음과 같이 IP 주소를 입력하면

여기에 이미지 설명을 입력하십시오

그리고 오름차순으로 정렬하면 다음과 같이 나타납니다.

예를 들어 IP 주소 17.255.253.65가 1.128.96.254 이후 에 오름차순으로 정렬 될 때 103.236.162.56 이전에 나타나도록 셀을 포맷하는 방법이 있습니까?

그렇지 않다면이 궁극적 인 목표를 달성 할 수있는 다른 방법이 있습니까?

답변

알다시피 IP 주소는 숫자가 아닌 텍스트로 취급됩니다. 텍스트로 정렬되므로 “162”로 시작하는 주소는 “20”으로 시작하는 주소 앞에옵니다. 문자 “1”이 문자 “2”앞에 오기 때문입니다.

https://stackoverflow.com/a/31615838/4424957 에서 제공하는 공식을 사용하여 IP 주소를 부분으로 분할 할 수 있습니다.

IP 주소가 열 A에 있으면 아래에 표시된대로 열 BE를 추가하십시오.

공식을 입력하십시오

=VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),(B$1)*999-998,999)))

셀 B2에서 모든 행의 BE 열에 복사하여 각 IP 주소의 네 부분을 가져옵니다. 이제 아래 표시된 것처럼 열 B에서 E까지 (순서대로) 전체 범위를 정렬하십시오.

도우미 열 (BE)을 보지 않으려면 숨길 수 있습니다.

답변

내가 제안 할 수있는 가장 쉬운 3 단계 솔루션은 다음과 같습니다.

  1. IP 주소 열을 선택하고 텍스트를 열에 적용하십시오 .

  2. 인접한 열에이 수식을 작성하십시오

    = CONCATENATE (B3, “.”, C3, “.”, D3, “.”, E3)

  3. 마지막으로 오름차순으로 정렬하십시오.

스크린 샷을 확인하십시오.

NB :

빨간색 은 원래 IP 주소입니다 (A 열).

텍스트를 열에 적용한 후 녹색 (B 열에서 E 열로).

검은 색 이 적용된 후 연결 및 정렬 (열 F)입니다.

그 이유는 원래 IP 주소가 텍스트 데이터이기 때문에 매우 간단하기 때문에 Excel에서는 셀 형식을 허용하지 않으므로 숫자로 변환 할 수 없습니다.

이것이 당신을 돕기를 바랍니다.

답변

다음은 동일한 문제를 해결하기 위해 얼마 전에 작성한 VBA 함수입니다. 올바르게 정렬 된 IPv4 주소의 패딩 된 버전을 생성합니다.

Function SortAddress(Address As String)                     '   format address as XXX.XXX.XXX.XXX to permit sorting

Dim FirstByte As Integer, LastByte As Integer, I As Integer

SortAddress = ""
FirstByte = 1

For I = 0 To 2                                          '   process the first three bytes

    LastByte = InStr(FirstByte, Address, ".")           '   find the dot
                                                        '   append the byte as 3 digits followed by dot
    SortAddress = SortAddress & Format(Mid(Address, FirstByte, LastByte - FirstByte), "000\.")

    FirstByte = LastByte + 1                            '   shift the start pointer

Next I

SortAddress = SortAddress & Format(Mid(Address, FirstByte), "000") ' process the last byte

End Function

간단한 예 :

결과

방식

‘정렬 가능’열을 기준으로 정렬하여 숨길 수 있습니다.

답변

다음은 테이블의 한 열만 가져오고 IPv4 주소를 기본 10 번호 매기기로 변환하는 답변입니다.

데이터를 “M”열에 넣었으므로 M2 셀에서 시작합니다 (M1은 레이블 임). 코드로 캡슐화하면 끔찍한 혼란이 생겨 블록 인용 부호를 사용했습니다.

= INT (LEFT (M2, FIND ( “.”, M2)-1)) * 256 ^ 3 + INT (MID (M2, FIND ( “.”, M2) + 1, FIND ( “.”, M2, FIND ( “.”, M2) + 1)-FIND ( “.”, M2) -1)) * 256 ^ 2 + INT (MID (M2, FIND ( “.”, M2, FIND ( “.”, M2) + 1) + 1, FIND ( “.”, M2, FIND ( “.”, M2, FIND ( “.”, M2) + 1) + 1)-FIND ( “.”, M2, FIND ( “.” , M2) + 1)-1)) * 256 + INT (RIGHT (M2, LEN (M2)-FIND ( “.”, M2, FIND ( “.”, M2, FIND ( “.”, M2) + 1) ) + 1)))

가장 쉽게 읽을 수있는 공식은 아니지만 셀에 복사하여 붙여 넣을 수 있습니다 (예 : N2 또는 첫 번째 IP 주소와 같은 행에있는 다른 항목). 수식의 오류 수정으로 인해 사람의 구문 분석이 더 나빠질 수 있으므로 IP 주소의 올바른 형식을 가정합니다.

답변

수식이나 VBA를 사용하지 않으려면 파워 쿼리를 사용하십시오. (Excel 2016, Get & Transform의 Excel 2010 또는 2013에서 PowerQuery 추가 기능을 설치하십시오.)

  1. 표를 PowerQuery 편집기로 가져 오십시오.
  2. “열 복제”를 마우스 오른쪽 단추로 클릭하여 열을 복제하십시오.
  3. 홈 탭에서 구분 기호로 “열 분리” “구분 자마다”를 선택하십시오.
  4. 각 열 Asc를 정렬하십시오. 왼쪽에서 오른쪽으로.
  5. 이전에 분할 된 열을 선택하고 마우스 오른쪽 버튼을 클릭하고 제거하고 닫고로드하십시오.

답변

이것은 옥텟을 적절한 정렬을 허용하는 3 자리 필드로 변환하는 유사한 하나의 라이너입니다.

10.1.0.15됩니다 10001000015.

=LEFT(B85, FIND(".", B85) - 1) * 1000000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 1)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - FIND(".", B85) - 1) * 1000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 3)) - FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - 1) * 1000
+ RIGHT(B85, LEN(B85) - FIND("x", SUBSTITUTE(B85, ".", "x", 3)))

답변

문제에서 알 수 있듯이 M 열 은 M2부터 IP 주소 (IPv4)입니다.

모든 사람의 대답에서 좋은 점을 얻음으로써 여기 내 해결책이 있습니다. 1 개의 도우미 열만 필요합니다. 우리는 시도 에 IPv4 주소 포맷 012.198.043.009형식을 분류 그들을 다음과 :

  • 12.198.43.9~에 12 198 43 9, ~에012.198.043.009

  1. N2012.198.043.009 를 입력하여 IPv4 주소 를 형식 으로 형식화하고 아래로 채 웁니다.

    = TEXT( LEFT(SUBSTITUTE(M2, ".", "      "), 3    ), "000") & "."
& TEXT(  MID(SUBSTITUTE(M2, ".", "      "), 8, 5 ), "000") & "."
& TEXT(  MID(SUBSTITUTE(M2, ".", "      "), 15, 7), "000") & "."
& TEXT(RIGHT(SUBSTITUTE(M2, ".", "      "), 3    ), "000")

  2. N 열로 정렬

설명

6 개의 공백으로SUBSTITUTE 점 을 찍으면 다음을 얻을 수 있으므로 올바르게 추출 할 수 있습니다.. 

                   |123456789|123546789|123456789|
1.1.1.1         ->  1      1      1      1
11.11.11.11     ->  11      11      11      11
111.111.111.111 ->  111      111      111      111
                    =1=    ==2==  ===3===
  • 문자 1-3은 첫 번째 부분 만 포함하고 포함합니다.
  • 문자 8-12는 두 번째 부분 만 포함하고 있습니다.
  • 문자 15-21에는 세 번째 부분 만 포함됩니다.
  • 가장 오른쪽 3 자에는 네 번째 부분 만 포함됩니다.

그런 다음로 각 부분을 추출하고 형식을 지정하십시오 TEXT(..., "000").