Question Comment puis-je trier correctement les cellules contenant des adresses IP dans Excel?


Je travaille actuellement avec une grande liste d'adresses IP (des milliers).

Cependant, lorsque je trie la colonne contenant les adresses IP, elles ne sont pas triées de manière intuitive ou facile à suivre.

Par exemple, si je saisis les adresses IP comme suit:

enter image description here

Et puis si je trie par ordre croissant je reçois ceci:

enter image description here

Existe-t-il un moyen de formater les cellules afin que, par exemple, une adresse IP de 17.255.253.65 s'affiche après 1.128.96.254 et avant 103.236.162.56 lors du tri dans l'ordre croissant?

Sinon, existe-t-il un autre moyen pour moi d'atteindre ce but ultime?


34
2017-12-24 00:00


origine


Le tri n'est pas affecté par le formatage des cellules. - Blackwood
C'est intéressant - j'étais à peu près sûr que le format d'une cellule affecterait le tri dans certains cas et pensait que c'était peut-être l'un d'entre eux. Merci pour la clarification! - Monomeeth
Et Bruce vous aide si vous avez besoin de gérer les adresses IPv6! - Criggie


Réponses:


Comme vous l'avez peut-être compris, vos adresses IP sont traitées comme du texte et non des nombres. Ils sont triés en tant que texte, ce qui signifie que les adresses commençant par "162" viendront avant les adresses commençant par "20". (parce que le caractère "1" vient avant le caractère "2".

Vous pouvez utiliser la formule fournie dans cette réponse: https://stackoverflow.com/a/31615838/4424957 diviser l'adresse IP en ses parties.

Si vos adresses IP figurent dans les colonnes A, ajoutez les colonnes B-E comme indiqué ci-dessous.

enter image description here

Entrez la formule

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

dans la cellule B2 et le copier dans les colonnes B-E dans toutes les lignes pour obtenir les quatre parties de chaque adresse IP. Maintenant, triez l’ensemble des colonnes B à E (dans cet ordre) comme indiqué ci-dessous:

enter image description here

Si vous ne voulez pas voir les colonnes auxiliaires (B-E), vous pouvez les masquer.


42
2017-12-24 00:35



Au lieu de diviser en quatre colonnes, il serait peut-être préférable de "concaténer" les octets comme first*256^3+second*256^2+third*256+fourth dans une colonne? - Ruslan
Surtout que les adresses IPv4 ne sont en réalité que des nombres de 32 bits. Le mode d'affichage des adresses IP à quatre nombres de 4 bits est uniquement destiné à la lisibilité humaine. - Kat
@Kat je suis d'accord que cela fonctionnerait, et il ne nécessite qu'une colonne au lieu de quatre. Mais la formule dans cette colonne serait plutôt longue, alors j'ai choisi d'utiliser quatre colonnes (je les cacherais probablement dans tous les cas). - Blackwood


Le plus simple, 3 étapes Solution Je peux vous suggérer que vous êtes ,,,

  1. Sélectionnez la colonne d'adresse IP, appliquez Texte en colonne commander.

  2. Dans la colonne adjacente, écrivez cette formule

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

  3. Enfin, triez par ordre croissant.

Vérifiez la capture d'écran.

enter image description here

NB: 

rouge est l'adresse IP d'origine (dans la colonne A).

vert après application du texte à la colonne (colonne B à E).

Noir est après application de la concaténation et du tri (colonne F).

La raison en est très simple à l'origine l'adresse IP est le texte et Excel n'accepte aucun format de cellule pour le transformer en nombre. 

J'espère que cela vous aidera.


10
2017-12-24 10:50



C'est terrible. Vous ne voulez jamais mélanger des données provenant de différentes lignes. - MaxW
@MaxW ,, Je ne comprends pas exactement ce que vous voulez dire !! - Rajesh S
Il signifie que vous devriez avoir inclus la colonne originale A dans le tri, de sorte que les adresses IP seraient les mêmes sur chaque ligne. - Bandersnatch
Non, rien à redire, la demande OP consistait à trier l'adresse IP en ordre naturel, (Number) et la colonne A en RED n'est pas triée. La colonne F a une adresse IP triée en NOIR. - Rajesh S
+1 C'est la solution la plus simple sur la page. @RajeshS peut-être changer le mot "ajusté" à "adjacent" dans votre réponse - je pensais que vous vouliez dire l'un des Text To Column colonnes là-bas. @MaxW, la mise en page est juste pour la démonstration. Si vous voulez que l'IP d'origine trié ignore simplement l'étape 2. Cette énorme "VALUE (TRIM (MID (formule de substitution" de la formule acceptée dans la réponse acceptée doit être remplacée par "Text To Columns" et le reste de la réponse est essentiellement le même). - mcalex


Voici une fonction VBA que j'ai écrite il y a quelque temps pour résoudre le même problème. Il génère une version complétée d'une adresse IPv4 qui se trie correctement.

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

Exemple simple:

Résultat

Result

Formules

Formulas

Vous pouvez trier par la colonne 'Sortable' et la masquer.


9
2017-12-24 12:34



Je veux juste noter que l'adresse IP triable sera analysée comme une adresse différente de celle d'origine (car certains imbéciles ont choisi d'interpréter ceux-ci en octal). Donc, ne les utilisez que pour le tri et ne les traitez pas comme des adresses IP correctes. - CodesInChaos
Si vous allez sortir vba, pourquoi ne pas faire vba faire le tri - Richard Tingle
@ RichardTingle 1. C'est (un peu) plus de travail 2. Je pourrais vouloir trier la liste de plusieurs manières différentes à différents moments - grahamj42


Voici une réponse qui ne prend qu'une colonne de votre table et convertit l'adresse IPv4 en base 10.

Comme vous mettez vos données dans la colonne "M", cela commence dans la cellule M2 (M1 étant l'étiquette). L'encapsuler comme du code donne un désordre terrible, alors j'ai utilisé la citation suivante:

= INT (GAUCHE (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 (DROITE (M2, LEN (M2) - FIND (".", M2, FIND (".", M2, FIND (".", M2) + 1 ) + 1)))

Pas exactement la formule la plus lisible, mais vous pouvez simplement copier et coller dans votre cellule (de préférence N2 ou autre chose dans la même ligne que votre première adresse IP). Cela suppose un formatage correct de l'adresse IP, car la correction des erreurs dans la formule le rendrait encore pire pour l'analyse humaine.


6
2017-12-25 03:02





Si vous ne souhaitez pas utiliser de formules ou de VBA, utilisez Power Query. (Dans Excel 2016, Get & Transform, dans Excel 2010 ou 2013, installez le complément PowerQuery à suivre).

  1. Amenez la table dans l'éditeur PowerQuery.
  2. Dupliquez la colonne en cliquant avec le bouton droit sur "Duplicate Column"
  3. "Split Column" par délimiteur, sous l'onglet Accueil. Sélectionnez "Chaque occurrence du délimiteur"
  4. Trier chaque colonne Asc. de gauche à droite.
  5. Sélectionnez les colonnes précédemment divisées, cliquez avec le bouton droit et supprimez, fermez et chargez.

3
2017-12-27 21:06





La une est une doublure similaire qui transforme les octets en champs de 3 chiffres qui permettent un tri correct.

10.1.0.15 devient 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
2017-12-28 00:42



Pour que le tri fonctionne, vous devez également insérer le premier octet avec des zéros non significatifs. - Blackwood


Comme indiqué en question, colonne M sont les adresses IP (IPv4), à partir de M2.

En obtenant les bons points de la réponse de chacun, voici ma solution. Une seule colonne d'assistance est nécessaire. Nous essayons de formater les adresses IPv4 en 012.198.043.009 format puis triez-les:

  • 12.198.43.9 à 12 198 43 9, puis à 012.198.043.009

  1. Formater les adresses IPv4 dans 012.198.043.009 format en saisissant N2et remplissez vers le bas:

    = 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. Trier par colonne N


Explication

Par SUBSTITUTEle point .  avec 6 espaces, nous obtenons ce qui suit pour pouvoir les extraire correctement:

                   |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===
  • Le caractère 1-3 contient et ne contient que la première partie.
  • Le caractère 8-12 contient et ne contient que la deuxième partie.
  • Le personnage 15-21 contient et ne contient que la troisième partie.
  • Les 3 caractères les plus à droite contiennent et ne contiennent que la quatrième partie.

Et puis, extrayez et formatez chaque partie par TEXT(..., "000").


2
2018-01-15 08:53