Excel で IP アドレスを扱う。

Excel で IP アドレスを扱う機会は、意外に多い。
いつものとおり、備忘をかねて記載していく。

なお、ここでは明記しない限り IPv4 を前提とする。

分割する

IPアドレスを分割するのに、いくつか方法はある。
「データ」→「区切り位置」で、ドットを指定し、セルを分割してしまう方法。
関数でひとつひとつ抜き出してあげる方法。


関数で抜き出すにも、いくつかやり方はあるものの、僕がよく使う方法を記載する。
(もっと効率の良いやり方もあったけど…これが馴染むw)
使う関数は、以下の通り。

  • SEARCH (対象文字列から、指定した文字の位置(数値)を返す)
  • LEFT (対象文字列の左側から指定文字数を抜き出す)
  • RIGHT (対象文字列の右側から指定文字数を抜き出す)
  • MID (対象文字列の指定文字数から、指定文字数までを抜き出す)
  • VALUE (文字列を数字にする)


A1 に 192.168.1.0 を入力した場合の分解。


Ctrl + Shift + ` キーで、数式を表示。
こんな数式を入力。


こんなんで、ドットの場所を特定する。
=SEARCH(".",A1,1)
=SEARCH(".",A1,SEARCH(".",A1,1)+1)
=SEARCH(".",A1,SEARCH(".",A1,SEARCH(".",A1,1)+1)+1)


でもって、以下の計算式でそれぞれの値を抜き出す。
=LEFT(A1,A3-1))
=MID(A1,A3+1,A4-A3-1))
=MID(A1,A4+1,A5-A4-1))
=RIGHT(A1,LEN(A1)-A5))


これだけだと、数値じゃなくて文字列として認識されるので、変換。
=VALUE(LEFT(A1,A3-1)))
=VALUE(MID(A1,A3+1,A4-A3-1)))
=VALUE(MID(A1,A4+1,A5-A4-1)))
=VALUE(RIGHT(A1,LEN(A1)-A5)))

ソート用に10進数へ変換する

10進数に変換するというのは、要はロングIPアドレスにするということ。



まとめると、こんなに長くなる…
=VALUE(LEFT(A1,SEARCH(".",A1,1)-1))*16777216+VALUE(MID(A1,SEARCH(".",A1,1)+1,SEARCH(".",A1,SEARCH(".",A1,1)+1)-SEARCH(".",A1,1)-1))*65536+VALUE(MID(A1,SEARCH(".",A1,SEARCH(".",A1,1)+1)+1,SEARCH(".",A1,SEARCH(".",A1,SEARCH(".",A1,1)+1)+1)-SEARCH(".",A1,SEARCH(".",A1,1)+1)-1))*256