【Excel】IPアドレスの各オクテット・Prefixを抜き出す関数

目的

IPアドレス管理の効率化
ネットワークエンジニアの働き方改革

前提

• IPアドレスはA1セルに記載されている。
• B1セル～F1セルを計算用に使用する。
• Prefixあり・なしの両方のIPアドレスに対応する。
  • Prefixありの場合、Prefix長を抜き出す。( / なし)
  • Prefixなしの場合、空白を返す。

関数

B1セル：1オクテット目
=LEFT(A1,FIND(".",A1)-1)

C1セル：2オクテット目
=MID(LEFT(A1,FIND(".",A1,LEN(B1)+2)-1),LEN(B1)+2,3)

D1セル：3オクテット目
=MID(LEFT(A1,FIND(".",A1,LEN(B1&C1)+3)-1),LEN(B1&C1)+3,3)

E1セル：4オクテット目
=SUBSTITUTE(RIGHT(A1,LEN(A1)-LEN(B1&C1&D1)-3),"/"&IFERROR(MID(A1,FIND("/",A1)+1,3),""),"")

F1セル：Prefix
=SUBSTITUTE(RIGHT(A1,LEN(A1)-LEN(B1&C1&D1&E1)-3),"/","")

おまけ：抜き出した各数値を結合する関数
（E1をE1+1にして、対向の機器に割り当てるときなどに使う）
=B1&"."&C1&"."&D1&"."&E1&IF(F1="","","/"&F1)

イメージ

追記：Prefix表記から、サブネットマスクを出力させる関数

A1セルのPrefixのサブネットマスクを表示する。/16～/32までに対応。ついでにワイルドカードマスクも。
本当はIPアドレスのようにオクテットごとに1セルを使うのが楽だが、どうしてもひとつのセルに収める必要があり、関数をガチガチに組んで実現。

サブネットマスク：

=IF($A1="","",IF(RIGHT($A1,2)*1=16,"255.255.0.0",IF(RIGHT($A1,2)*1>24,"255.255.255."&256-2^(7-MOD(RIGHT($A1,2)-1,8)),"255.255."&256-2^(7-MOD(RIGHT($A1,2)-1,8))&".0")))

インデントをつけてみる。

=IF($A1="","",
    IF(RIGHT($A1,2)*1=16,"255.255.0.0",
        IF(RIGHT($A1,2)*1>24,
           "255.255.255."&256-2^(7-MOD(RIGHT($A1,2)-1,8)),
           "255.255."&256-2^(7-MOD(RIGHT($A1,2)-1,8))&".0"
           )
       )
   )

ワイルドカードマスク

=IF($A1="","",IF(RIGHT($A1,2)*1=16,"0.0.255.255",(IF(RIGHT($A1,2)*1>24,"0.0.0."&2^(7-MOD(RIGHT($A1,2)*1-1,8))-1,"0.0."&2^(7-MOD(RIGHT($A1,2)*1-1,8))-1&".255"))))
構造はサブネットマスクと同じ。

※はじめにA1セルが空白かを確認しているのは、この関数を複数行にずらっとコピーして使うことを想定しているため。これがないと空白行に対して#VALUEを返すので美しくないし、セルの数だけRIGHTを処理してしまうため遅くなるかも、と思っている。
※PrefixはRIGHT($A1,2)で取得している。RIGHTの結果は文字列として返されるので、そこから*1して数字の大小が比較できるようにしている。上記画像のように別セルでPrefixだけ出力されているなら、RIGHT($A1,2)*1を$F1に置き換えてもOK。

Prefixの値(F1セル)からサブネットマスクを一発出力

=IF($F1=0,"0.0.0.0",IF(INT(($F1-1)/8)>=1,"255."&IF(INT(($F1-1)/8)>=2,"255."&IF(INT(($F1-1)/8)>=3,"255.",""),""),"")&256-2^(8-(MOD($F1-1,8)+1))&IF(INT(($F1-1)/8)<3,".0"&IF(INT(($F1-1)/8)<2,".0"&IF(INT(($F1-1)/8)<1,".0",""),""),""))

=IF($F1=0,"0.0.0.0",
    IF(INT(($F1-1)/8)>=1,"255."&
        IF(INT(($F1-1)/8)>=2,"255."&
            IF(INT(($F1-1)/8)>=3,"255."
            ,"")
        ,"")
    ,"")&
    256-2^(8-(MOD($F1-1,8)+1))&
    IF(INT(($F1-1)/8)<3,
        ".0"&
        IF(INT(($F1-1)/8)<2,".0"&
            IF(INT(($F1-1)/8)<1,.0",
            "")
        ,"")
    ,"")
)

※/0～/32だと、全部で33種類。オクテット数４×各オクテット８パターンの処理だと、ひとつ足りない。そのため、/0を特殊パターンとして、最初に判別している。

追記_20201205：Prefixなしのネットワークアドレス(A1セル)から、一つ目のホストアドレスを出力

=LEFT($A$1,FIND("Γ",SUBSTITUTE($A$1,".","Γ",LEN($A$1)-LEN(SUBSTITUTE($A$1,".","")))))&MID($A$1,FIND("Γ",SUBSTITUTE($A$1,".","Γ",LEN($A$1)-LEN(SUBSTITUTE($A$1,".",""))))+1,3)+1

=LEFT($A$1,
    FIND("Γ",
        SUBSTITUTE($A$1,".","Γ",
            LEN($A$1)-
            LEN(UBSTITUTE($A$1,".",""))
        )
    )
)&
MID($A$1,
    FIND("Γ",
        SUBSTITUTE($A$1,".","Γ",
            LEN($A$1)-
            LEN(SUBSTITUTE($A$1,".",""))
        )
+1,3)
+1

参考：http://blog.excel-sys.com/Entry/165/