Convertir une IP en nombre entier avec Excel : Différence entre versions
De Vulgum Techus
(Page créée avec « Catégorie:Sites Internet Catégorie:Microsoft = Présentation = Sous '''Excel''' il peut être utile de convertir une IP sous la forme "WWW.XXX.YYY.ZZZ" en ... ») |
|||
| (4 révisions intermédiaires par un utilisateur sont masquées) | |||
| Ligne 1 : | Ligne 1 : | ||
| − | [[Catégorie: | + | [[Catégorie:Excel]] |
[[Catégorie:Microsoft]] | [[Catégorie:Microsoft]] | ||
| − | + | [[Catégorie:Sites Internet]] | |
| + | '''[[Lien court]]''' vers cette page : https://vt.cx/CIPE | ||
| + | '''[[QR code]]''' vers cette page : https://vt.cx/CIPE.qr | ||
| + | <html> | ||
| + | <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script> | ||
| + | <!-- VT2 --> | ||
| + | <ins class="adsbygoogle" | ||
| + | style="display:inline-block;width:468px;height:60px" | ||
| + | data-ad-client="ca-pub-3341840374417340" | ||
| + | data-ad-slot="6349432125"></ins> | ||
| + | <script> | ||
| + | (adsbygoogle = window.adsbygoogle || []).push({}); | ||
| + | </script> | ||
| + | </html> | ||
= Présentation = | = Présentation = | ||
| − | Sous '''[[Excel]]''' il peut être utile de convertir une IP sous la forme | + | Sous '''[[Excel]]''' il peut être utile de convertir une IP sous la forme '''WWW.XXX.YYY.ZZZ''' en un nombre entier et le présent article explique comment y parvenir avec une seule formule. |
= Procédure = | = Procédure = | ||
Ici il s'agit d'isoler chaque membre de l'IP et de le multiplier par une puissance de 256 à savoir '''<nowiki>WWW * 256³ + XXX * 256² + YYY * 256 + ZZZ</nowiki>'''<br/> | Ici il s'agit d'isoler chaque membre de l'IP et de le multiplier par une puissance de 256 à savoir '''<nowiki>WWW * 256³ + XXX * 256² + YYY * 256 + ZZZ</nowiki>'''<br/> | ||
| − | Ceci s'obtient avec la formule suivante qui convertit | + | Ceci s'obtient avec la formule suivante qui convertit une IP placée dans la cellule A1. |
| + | Exemple : IP 132.213.15.23 → 2228555543 (<nowiki>132 * 16777216 + 213 * 65536 + 15 * 256 + 23</nowiki>) | ||
| + | La formule se décompose en 4 parties qui, dans un premier temps, donnent les 4 membres de l'IP et où chaque point est remplacé par une lettre (respectivement A, B et C) par une imbrication de la commande SUBSTITUE : | ||
| + | SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1) qui transforme l'IP 132.213.15.23 en 132A213B15C23 | ||
| + | Les formules pour chacun des 4 membres se décomposent comme suit : | ||
| + | GAUCHE(A1;TROUVE(".";A1)-1) | ||
| + | |||
| + | STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1) | ||
| + | |||
| + | STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1) | ||
| + | |||
| + | DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))) | ||
| + | Ensuite il suffit de multiplier chacun des membres par la puissance de 256 correspondant à la position du nombre (1, 2, 3 ou 4) : | ||
(16777216*GAUCHE(A1;TROUVE(".";A1)-1)) | (16777216*GAUCHE(A1;TROUVE(".";A1)-1)) | ||
| Ligne 16 : | Ligne 41 : | ||
(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C")))) | (DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C")))) | ||
| − | + | Puis de réunir les quatre en une seule opération pour obtenir le total : | |
| − | = | + | =(16777216*GAUCHE(A1;TROUVE(".";A1)-1)) + |
(65536*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) + | (65536*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) + | ||
(256*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) + | (256*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) + | ||
(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C")))) | (DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C")))) | ||
| + | |||
| + | |||
| + | = Commentaires = | ||
| + | {{#widget:DISQUS | ||
| + | |id=vulgumtechus | ||
| + | }} | ||
Version actuelle en date du 5 juillet 2020 à 19:31
Lien court vers cette page : https://vt.cx/CIPE QR code vers cette page : https://vt.cx/CIPE.qr
Présentation
Sous Excel il peut être utile de convertir une IP sous la forme WWW.XXX.YYY.ZZZ en un nombre entier et le présent article explique comment y parvenir avec une seule formule.
Procédure
Ici il s'agit d'isoler chaque membre de l'IP et de le multiplier par une puissance de 256 à savoir WWW * 256³ + XXX * 256² + YYY * 256 + ZZZ
Ceci s'obtient avec la formule suivante qui convertit une IP placée dans la cellule A1.
Exemple : IP 132.213.15.23 → 2228555543 (132 * 16777216 + 213 * 65536 + 15 * 256 + 23)
La formule se décompose en 4 parties qui, dans un premier temps, donnent les 4 membres de l'IP et où chaque point est remplacé par une lettre (respectivement A, B et C) par une imbrication de la commande SUBSTITUE :
SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1) qui transforme l'IP 132.213.15.23 en 132A213B15C23
Les formules pour chacun des 4 membres se décomposent comme suit :
GAUCHE(A1;TROUVE(".";A1)-1)
STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)
STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)
DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C")))
Ensuite il suffit de multiplier chacun des membres par la puissance de 256 correspondant à la position du nombre (1, 2, 3 ou 4) :
(16777216*GAUCHE(A1;TROUVE(".";A1)-1))
(65536*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1))
(256*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1))
(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))))
Puis de réunir les quatre en une seule opération pour obtenir le total :
=(16777216*GAUCHE(A1;TROUVE(".";A1)-1)) +
(65536*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) +
(256*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) +
(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))))
