目的
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