wake-up-neo.com

Wie erhalte ich Zellen in Excel, die IP-Adressen enthalten, um richtig zu sortieren?

Ich arbeite derzeit mit einer großen Liste von IP-Adressen (Tausende von ihnen).

Wenn ich die Spalte mit den IP-Adressen sortiere, werden sie jedoch nicht auf intuitive oder leicht verständliche Weise sortiert.

Wenn ich zum Beispiel IP-Adressen wie folgt eingebe:

 enter image description here

Und wenn ich dann in aufsteigender Reihenfolge sortiere, erhalte ich Folgendes:

 enter image description here

Kann ich die Zellen so formatieren, dass beispielsweise eine IP-Adresse von 17.255.253.65 after 1.128.96.254 und before 103.236.162.56 in aufsteigender Reihenfolge angezeigt wird? ?

Wenn nicht, gibt es einen anderen Weg für mich, um dieses ultimative Ziel zu erreichen?

34
Monomeeth

Wie Sie vielleicht bemerkt haben, werden Ihre IP-Adressen als Text und nicht als Zahlen behandelt. Sie werden als Text sortiert, dh Adressen, die mit "162" beginnen, stehen vor Adressen, die mit "20" beginnen. (weil das Zeichen "1" vor dem Zeichen "2" steht.

Sie können die in dieser Antwort angegebene Formel verwenden: https://stackoverflow.com/a/31615838/4424957 , um die IP-Adresse in ihre Teile aufzuteilen.

Wenn sich Ihre IP-Adressen in den Spalten A befinden, fügen Sie die Spalten B-E wie unten gezeigt hinzu.

 enter image description here

Geben Sie die Formel ein

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

in Zelle B2 und kopieren Sie es in die Spalten B-E in allen Zeilen, um die vier Teile jeder IP-Adresse abzurufen. Sortieren Sie nun den gesamten Bereich nach den Spalten B bis E (in dieser Reihenfolge), wie unten gezeigt:

 enter image description here

Wenn Sie die Hilfsspalten (B-E) nicht sehen möchten, können Sie sie ausblenden.

42
Blackwood

Die einfachste Lösung in 3 Schritten, die ich vorschlagen kann, dass Sie es sind ,

  1. Wählen Sie die IP-Adressenspalte aus und wenden Sie den Befehl Text in Spalte an.

  2. In der nebenstehenden Spalte schreiben Sie diese Formel

    = KONKATENAT (B3, ".", C3, ".", D3, ".", E3)

  3. Zuletzt in aufsteigender Reihenfolge sortieren.

Überprüfen Sie den Screenshot.

 enter image description here

NB:

Rot ist die ursprüngliche IP-Adresse (in Spalte A).

Grün nach Anwenden von Text auf Spalte (Spalte B bis E).

Schwarz wird nach dem Anwenden von Verketten und Sortieren (Spalte F) angezeigt.

Der Grund dafür ist sehr einfach, dass die ursprüngliche IP-Adresse Textdaten ist und Excel kein Zellenformat akzeptiert, um sie in Zahl umzuwandeln.

Hoffe das hilft dir.

10
Rajesh S

Hier ist eine VBA-Funktion, die ich vor einiger Zeit geschrieben habe, um das gleiche Problem zu lösen. Es wird eine gepolsterte Version einer IPv4-Adresse generiert, die korrekt sortiert ist.

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

Einfaches Beispiel:

Ergebnis

 Result

Formeln

 Formulas

Sie können nach der Spalte "Sortierbar" sortieren und diese ausblenden.

9
grahamj42

Hier ist eine Antwort, die nur eine Spalte Ihrer Tabelle benötigt und die IPv4-Adresse in eine 10er-Basis-Nummerierung umwandelt.

Da Sie Ihre Daten in Spalte "M" einfügen, beginnt dies in Zelle M2 (M1 ist die Bezeichnung). Das Einkapseln als Code führt zu einem furchtbaren Durcheinander. Daher habe ich blockquote verwendet:

= INT (LINKS (M2, SUCHEN (".", M2) - 1)) * 256 ^ 3 + INT (MITTEL (M2, SUCHEN (".", M2) + 1, SUCHEN (".", M2, SUCHEN (".", M2) + 1) - FIND (".", M2) -1)) * 256 ^ 2 + INT (MID (M2, FIND (".", M2, FIND (".", M2) + 1) + 1, SUCHEN (".", M2, SUCHEN (".", M2, SUCHEN (".", M2) + 1) + 1) - SUCHEN (".", M2, SUCHEN ("." , M2) + 1) - 1)) * 256 + INT (RECHTS (M2, LEN (M2) - FIND (".", M2, FIND (".", M2, FIND (".", M2) + 1 ) + 1)))

Nicht gerade die am leichtesten lesbare Formel, aber Sie können sie einfach kopieren und in Ihre Zelle einfügen (vorzugsweise N2 oder etwas anderes in derselben Zeile wie Ihre erste IP-Adresse). Die korrekte Formatierung der IP-Adresse wird vorausgesetzt, da eine Fehlerkorrektur in der Formel das Parsen durch den Menschen noch verschlimmern würde.

6
RudyB

Dies ist ein ähnlicher Einzeiler, der die Oktette in dreistellige Felder umwandelt, die eine ordnungsgemäße Sortierung ermöglichen.

10.1.0.15 wird zu 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)))
3
user856853

Wenn Sie keine Formeln oder VBA verwenden möchten, verwenden Sie Power Query. (In Excel 2016, Get & Transform, in Excel 2010 oder 2013 installieren Sie das PowerQuery-Add-In, um mitzumachen.).

  1. Bringen Sie die Tabelle in den PowerQuery-Editor.
  2. Duplizieren Sie die Spalte, indem Sie mit der rechten Maustaste auf "Spalte duplizieren" klicken.
  3. "Spalte teilen" nach Trennzeichen auf der Registerkarte "Startseite". Wählen Sie "Jedes Vorkommen des Begrenzers"
  4. Sortieren Sie jede Spalte aufsteigend. von links nach rechts.
  5. Wählen Sie die zuvor geteilten Spalten aus, klicken Sie mit der rechten Maustaste und entfernen Sie sie, schließen Sie sie und laden Sie sie.
3
click here

Wie in Frage gezeigt, sind Spalte M die IP-Adressen (IPv4), beginnend mit M2.

Hier ist meine Lösung, indem ich die guten Punkte aus der Antwort aller heraushole. Es wird nur 1 Hilfssäule benötigt. Wir versuchen, die IPv4-Adressen in das Format 012.198.043.009 zu formatieren und sie dann zu sortieren:

  • 12.198.43.9 ZU 12 198 43 9, DANN ZU 012.198.043.009

  1. Formatieren Sie IPv4-Adressen in das 012.198.043.009 -Format, indem Sie N2 eingeben und abwärts ausfüllen:

    = 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. Sortieren nach Spalte N


Erklärung

Durch SUBSTITUTE des Punktes . mit 6 Leerzeichen erhalten wir Folgendes, damit sie korrekt extrahiert werden können:

                   |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===
  • Zeichen 1-3 enthält und enthält nur den ersten Teil.
  • Zeichen 8-12 enthält und enthält nur den zweiten Teil.
  • Zeichen 15-21 enthält und enthält nur den dritten Teil.
  • Ganz rechts 3 Zeichen enthält und enthält nur den vierten Teil.

Extrahieren und formatieren Sie dann jeden Teil mit TEXT(..., "000").

2
wilson