Jak uzyskać komórki w programie Excel zawierające adresy IP, aby prawidłowo sortować?

Obecnie pracuję z dużą listą adresów IP (tysiące z nich).

Jednak gdy sortuję kolumnę zawierającą adresy IP, nie sortują one w sposób intuicyjny lub łatwy do naśladowania.

Na przykład, jeśli wprowadzę adresy IP w następujący sposób:

A potem, jeśli sortuję rosnąco, otrzymuję:

Czy istnieje sposób na sformatowanie komórek, aby na przykład adres IP 17.255.253.65 pojawił się po 1.128.96.254 i przed 103.236.162.56 po posortowaniu w porządku rosnącym?

Jeśli nie, czy jest inny sposób na osiągnięcie tego ostatecznego celu?

Jak być może zauważyłeś, twoje adresy IP są traktowane jak tekst, a nie liczby. Są one sortowane jako tekst, co oznacza, że ​​adresy zaczynające się od „162” pojawią się przed adresami rozpoczynającymi się od „20”. (ponieważ znak „1” występuje przed znakiem „2”.

Możesz użyć wzoru podanego w tej odpowiedzi: https://stackoverflow.com/a/31615838/4424957, aby podzielić adres IP na jego części.

Jeśli twoje adresy IP znajdują się w kolumnach A, dodaj kolumny BE, jak pokazano poniżej.

Wprowadź formułę

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

w komórce B2 i skopiuj go do kolumn BE we wszystkich wierszach, aby uzyskać cztery części każdego adresu IP. Teraz posortuj cały zakres według kolumn od B do E (w tej kolejności), jak pokazano poniżej:

Jeśli nie chcesz widzieć kolumn pomocniczych (BE), możesz je ukryć.

Najłatwiejsze, 3 kroki Rozwiązanie, które mogę zasugerować, ,,,

1. Wybierz kolumnę adresu IP, zastosuj polecenie Tekst do kolumny .

2. W sąsiedniej kolumnie napisz tę formułę

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

3. Wreszcie sortuj w porządku rosnącym.

Sprawdź zrzut ekranu.

NB:

Czerwony to oryginalny adres IP (w kolumnie A).

Zielony po zastosowaniu tekstu w kolumnie (kolumna B do E).

Czarny jest po zastosowaniu konkatenatu i sortowania (kolumna F).

Powód jest bardzo prosty, pierwotnie adres IP to Dane tekstowe, a Excel nie akceptuje żadnego formatu komórki, aby zmienić go na Liczba.

Mam nadzieję, że ci to pomoże.

Oto funkcja VBA, którą napisałem jakiś czas temu, aby rozwiązać ten sam problem. Generuje wyściełaną wersję adresu IPv4, która sortuje się poprawnie.

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

Prosty przykład:

Wynik

Formuły

Możesz sortować według kolumny „Sortable” i ukryć ją.

Oto odpowiedź, która zajmie tylko 1 kolumnę tabeli i konwertuje adres IPv4 na numerację podstawową 10.

Ponieważ umieszczasz swoje dane w kolumnie „M”, zaczyna się to w komórce M2 (M1 jest etykietą). Hermetyzowanie go jako kodu powoduje jeden straszny bałagan, więc użyłem cytatu:

= INT (LEWY (M2, ZNAJDŹ ("."; M2) - 1)) * 256 ^ 3 + INT (MID (M2, ZNAJDŹ (".", M2) + 1, ZNAJDŹ (".", M2, ZNAJDŹ) (".", M2) + 1) - ZNAJDŹ (".", 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)))

Nie jest to najłatwiejsza do odczytania formuła, ale możesz po prostu skopiować i wkleić do komórki (najlepiej N2 lub coś innego w tym samym rzędzie, co pierwszy adres IP). Zakłada prawidłowe formatowanie adresu IP, ponieważ korekta błędu w formule pogorszyłaby to nawet w przypadku analizowania przez człowieka.

Jeśli nie chcesz używać formuł lub VBA, użyj Power Query. (W programie Excel 2016, Get & Transform, w programie Excel 2010 lub 2013 zainstaluj dodatek PowerQuery, aby śledzić dalej).

1. Przenieś tabelę do edytora PowerQuery.
2. Zduplikuj kolumnę, klikając prawym przyciskiem myszy „Duplikuj kolumnę”
3. „Podziel kolumnę” według separatora na karcie Narzędzia główne. Wybierz „Każde wystąpienie ogranicznika”
4. Posortuj każdą kolumnę Asc. od lewej do prawej.
5. Wybierz poprzednio podzielone kolumny, kliknij prawym przyciskiem myszy i usuń, zamknij i załaduj.

Jest to podobny liniowiec, który przekształca oktety w 3-cyfrowe pola, które umożliwiają prawidłowe sortowanie.

10.1.0.15staje się 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)))

Jak pokazano w pytaniu, kolumna M to adresy IP (IPv4), zaczynające się od M2.

Otrzymując dobre punkty od odpowiedzi wszystkich, oto moje rozwiązanie. Potrzebna jest tylko 1 kolumna pomocnicza. Próbujemy sformatować adresy IPv4 do 012.198.043.009formatu, a następnie posortować je:

• 12.198.43.9do 12 198 43 9, a następnie do012.198.043.009

1. Sformatuj adresy IPv4 w 012.198.043.009formacie, wprowadzając N2 i wypełniając w dół:

   = 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. Sortuj według kolumny N

Wyjaśnienie

Wprowadzając SUBSTITUTEkropkę . z 6 spacjami , otrzymujemy następujące informacje, aby można je było poprawnie wyodrębnić:

                   |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===

• Postać 1-3 zawiera i zawiera tylko pierwszą część.
• Postać 8-12 zawiera i zawiera tylko drugą część.
• Postać 15-21 zawiera i zawiera tylko trzecią część.
• Prawe 3 znaki zawierają i zawierają tylko czwartą część.

Następnie wyodrębnij i sformatuj każdą część według TEXT(..., "000").

Jeśli używasz najnowszej wersji programu Excel w systemie Windows, możesz użyć następującej formuły do ​​obliczenia 32-bitowej wartości dziesiętnej adresu IP.

=SUMPRODUCT(FILTERXML("<I><o v="""&SUBSTITUTE([@ipbase],".","""/><o v=""")&"""/></I>","//o/@v"),{16777216;65536;256;1})

Następnie można sortować według pochodnej wartości dziesiętnej. Zastąp [@ipbase]lokalizację komórki prawidłowego adresu IP4.

Funkcja „FILTERXML” działa tylko w systemie Windows, więc nie masz szczęścia, jeśli używasz innego systemu operacyjnego.

Mam również jednowierszowy, który da ci wyściełany format IP (000 000 000 000) do sortowania alfa.

= TEXT(MID(A1,1,FIND(".",A1)),"000") & "." &
  TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-1-FIND(".",A1)),"000") & "." &
  TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1-FIND(".",A1,FIND(".",A1)+1)),"000") & "." &
  TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)),"000")