連番でIPアドレスをアサインするとき用のエクセル関数
自分以外に役立つのか全く不明だけど、備忘録的に。
特定のネットワークアドレス（たとえば192.168.10.0/24）から各ホストにアドレスを割り当てていく。こんな感じ。
ホストA 192.168.10.1/24 ホストB 192.168.10.2/24 ホストC 192.168.10.3/24 以下、延々に続く
エクセルのシートに各ホストの名前とかが書いてあって、一緒にアドレス管理もしたいときに第４オクテットだけインクリメントして割当てていくってことが単にドラッグしてインクリメントさせるエクセルの機能だけだとうまくいかない。
ということで、こんな関数を書けば、あほでもできた。
=LEFT($A$1,FIND("..",SUBSTITUTE($A$1,".","..",3)))&MID($A$1,FIND("..",SUBSTITUTE($A$1,".","..",3))+1,FIND("/",$A$1)-FIND("..",SUBSTITUTE($A$1,".","..",3))-1)+ROW(A2)-1&RIGHT($A$1,LEN($A$1)-FIND("/",$A$1)+1)
+-----------------------------------------------+ +------------------------------------------------------------------------------------------------------------------+ +------------------------------+
第1～3オクテット 第4オクテット +--------+ サブネットマスク
インクリメント用
長くてごめんね。セルA1にネットワークアドレスを入れてあげて、セルA2以降に連番に出力させるようなケースで使える感じ。A2に上記の関数突っ込んで、あとはA3以降にドラッグしていけば連番で出力される。
セルA2以外に関数を入れる場合、適宜「 ROW(A2)-1 」の部分を変更しないとダメ。
| A | B | |
|---|---|---|
| 1 | 192.168.10.0/24 | 適当なネットワークアドレスを入れる |
| 2 | 192.168.10.1/24 | 上記の関数を入れるとこうなる |
| 3 | 192.168.10.2/24 | あとはA2をドラッグ |
| 4 | 192.168.10.3/24 | - |
| 5 | 192.168.10.4/24 | - |
ざっくりと中身解説。
第1～3オクテット
LEFT($A$1,FIND("..",SUBSTITUTE($A$1,".","..",3)))&
まずはSUBSTITUTE関数でA1(=ネットワークアドレス)の3番目の「.（ドット）」だけを「..（ドット x2）」に置換します。
FIND関数で「..（ドット x2）」の位置（何文字目に登場するか）を識別して、LEFT関数でその位置までの文字列を出力します。
これでとりあえずどんなネットワークアドレスでも第３オクテットまで綺麗に出力されます。
第4オクテット
MID($A$1,FIND("..",SUBSTITUTE($A$1,".","..",3))+1,FIND("/",$A$1)-FIND("..",SUBSTITUTE($A$1,".","..",3))-1)+ROW(A2)-1
前半：
MID($A$1,FIND("..",SUBSTITUTE($A$1,".","..",3))+1,
後半：
FIND("/",$A$1)-FIND("..",SUBSTITUTE($A$1,".","..",3))-1)+ROW(A2)-1
インクリメントしなければいけない第４オクテットを抽出するためにこのクソ面倒な関数を書いてます。もっと簡単な方法あるんじゃないのかな。。。
SUBSTITUTE関数は上記ですでに説明しているので割愛。
前半部分はMID関数で出力する文字位置（=第４オクテットの最初の文字が何文字目に登場するか）を指定しています。
後半部分のFIND関数以降がMID関数で出力する文字数を指定しています。「/（スラッシュ）」が登場する＝第４オクテットの最後の文字位置から第３オクテット以前の文字数を引くことで第４オクテットの文字数が必然的にわかります。
最後にインクリメントするようにROW(A2)-1でセルの行位置から第４オクテットがうまい具合にインクリメントするように調整。
サブネットマスク
&RIGHT($A$1,LEN(A1)-FIND("/",$A$1)+1)
最後にサブネットマスクを出力。
LEN関数でA1(=ネットワークアドレス)の文字数を識別して、そこから「/（スラッシュ）」の位置（何文字目に登場するか）を差し引けば、サブネットマスクの文字数がわかるので、「/（スラッシュ）」分に+1してあげて、RIGHT関数で出力させれば、完了。
うーん、書いてみて改めて思うけど、エクセルでこんなにがんばらなくていいじゃんと思う。
