현재 많은 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 단계 솔루션은 다음과 같습니다.
-
IP 주소 열을 선택하고 텍스트를 열에 적용하십시오 .
-
인접한 열에이 수식을 작성하십시오
= CONCATENATE (B3, “.”, C3, “.”, D3, “.”, E3)
-
마지막으로 오름차순으로 정렬하십시오.
스크린 샷을 확인하십시오.
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 추가 기능을 설치하십시오.)
- 표를 PowerQuery 편집기로 가져 오십시오.
- “열 복제”를 마우스 오른쪽 단추로 클릭하여 열을 복제하십시오.
- 홈 탭에서 구분 기호로 “열 분리” “구분 자마다”를 선택하십시오.
- 각 열 Asc를 정렬하십시오. 왼쪽에서 오른쪽으로.
- 이전에 분할 된 열을 선택하고 마우스 오른쪽 버튼을 클릭하고 제거하고 닫고로드하십시오.
답변
이것은 옥텟을 적절한 정렬을 허용하는 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
-
N2
012.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") -
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").