サブネットマスクの表記を変換するExcel関数

当記事ではアフィリエイトプログラムを利用して商品を紹介するプロモーションを含んでいます。

サブネットマスクが255.255.255.0と表記されていて、これは / 表記すると/24なのですが、TCP/IPについて学んだ経験がない人からするとなんのことだかよくわからないことだと思います。 これが255.255.252.0とか255.255.255.192とかになると、/ 表記はどうすればいいかわからなくなってしまうかもしれません。

今回は、サブネットマスクの表記をExcel関数で変換するアイディアを紹介します。　　　　　　　　　　　　webでこれを簡単に変換してくれるサイトはありますが、Excelであれば複数データを一気に変換できるので管理表の編集やデータの加工時に活躍するものだと思います。

今回は 「192.168.11.1 255.255.255.0」 というセットになっている値をまずIPアドレスとサブネットマスクに分解し、サブネットマスクの表記を/24に変換するという内容になっています。

セルA3に 「192.168.11.1 255.255.255.0」 という値を入力します。これが元データでこれをまず分解していきます。その後分解したサブネットマスクをオクテット毎にビット表記に変更。このビット数をまたオクテット毎に合計値を算出、第一～第四オクテットの合計値と”/”を結合させてセルD3に結果を出力します。上図に説明しやすいように①～⑪の番号を記載しています。順に説明をしていきます。

① まずは元データからIPアドレスだけを抜き出す。　　　　　　　　　　　　　　　　　　　　　　　　　　サブネットマスクを独立させる・のちに変換した/表記のサブネットと結合させるためにIPとサブネットを分離していきます。　　　　　　　　　　　　
セルB3・・・ここで入力している関数は以下の通りです。
=LEFT(A3,FIND(” “,A3,1))　
※赤いアンダーラインを引いているところが環境によってユニークなもの。
実際にこの関数を投入するときにご自身で元データを入力しているセルへ変更してください。
以降の説明でも同様の考え方となります。
解説：今回使っている元データの文字列のつくり上IPとサブネットの間にはスペースがあります。
ここまでの文字数がIPアドレスということになるので、その分の文字数左から抜き出すことで
IPアドレスを取り出します。

②サブネットマスクを抜き出す。
実際に表記を変換するサブネットマスクを元データから抜き出していきます。
セルC3・・・ここで入力している関数は以下の通りです。
=RIGHT(A3,LEN(A3)-LEN(B3))
解説：抜き出したIPアドレスの文字数を元データの文字数から減じた値がサブネットマスクの文字数ということになります。今回の関数で指定しているB3がIPアドレスを抜き出しているセルですから LEN()関数でB3の文字数を算出して、元データ全体の文字数から減じます。この文字数分を元データの右から抜き出すことでサブネットマスクを抜き出します。

③サブネットマスクの第一オクテットをビット表記へ変換する。
サブネットマスクを/表記にするには、2進数化してそのビット数計がそれになります。まずはサブネットマスクの第一オクテットを2進数化していきましょう。
セルE3・・・ここで入力している関数は以下の通りです。
=IFERROR(TEXT(DEC2BIN(LEFT(C3,FIND(“.”,C3,1)-1)),”00000000″),””)
解説：C3は抜き出したサブネットマスク。まずはこのサブネットマスクから第一オクテットの部分を抜き出します。LEFT(C3,FIND(“.”,C3,1)-1)途中のこの部分が第一オクテット目を抜き出す関数。
-1は “.”を抜き出さないための文字数調整です。
続いて DEC2BIN() この関数で10進数を2進数へ変換します。255を2進数にすると11111111になります。ここまで全体像の画像通りの結果になるのですが、ビット表記にしたときに0だとただの0になってしまうので見た目をそろえる意味で TEXT()関数を使って必ず1オクテットごとに8ビット表記されるようにします。

④サブネットマスクの第二オクテットをビット表記へ変換する。
引き続き2進数化していきます。
セルF3・・・ここで入力している関数は以下の通りです。
=IFERROR(TEXT(DEC2BIN(LEFT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),1)-1)),”00000000″),””)
解説：文字数が多くなっていてやることが複雑ですが、第一オクテットをビット表記した時と同じことをしています。LEFT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),1)-1) ここでは、要すると、まずは第一オクテットを抜き出してその文字数分を減じた数右から文字を抜き出す。つまり255.255.255.0から255.255.0とするということです。そこからまた最初に来る”.”を探してその文字数分左から抜き出すということですね。そうすると第二オクテットを出力できますね。それ以外の関数は第一オクテットと同じことをおこなっています。

⑤サブネットマスクの第三オクテットをビット表記へ変換する。
サブネットマスクのビット変換はここで折り返し地点です。引き続き2進数化していきます。
セルG3・・・ここで入力している関数は以下の通りです。
=IFERROR(TEXT(DEC2BIN(LEFT(RIGHT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),LEN(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)))-FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3)),1)),FIND(“.”,RIGHT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),LEN(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)))-FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),1)),1)-1)),”00000000″),””)
解説：かなり文字数が多くなってきましたが、これまたやることは一緒です。1オクテットずつ順番にはがしていくだけですね。さっき行ったように第一オクテットの文字数分全体から減じて第二オクテット以降だけにする。そして255.255.0から第二オクテットを抜き出します。残りの255.0からまた最初の”.”を探して第三オクテットを抜き出していきます。

⑥サブネットマスクの第四オクテットをビット表記へ変換する。
サブネットマスクのビット変換ラストです。
セルH3・・・ここで入力している関数は以下の通りです。
=IFERROR(TEXT(DEC2BIN(RIGHT(RIGHT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3)),1)+1),LEN(RIGHT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),1)+1))-FIND(“.”,RIGHT(RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),FIND(“.”,RIGHT(C3,LEN(C3)-FIND(“.”,C3,1)),1)+1),1))),”00000000″),””)
解説：書いてあることは長くてとにかく複雑ですが、やっていることは第三オクテット目までの文字数分を全体から減じた文字数分右から抜き出して第四オクテットの値を出力しています。
一文一文を解析していこうと思うと、関数を分解しながら少しずつ足していくと動きがわかってきます。今回はC３のセルにサブネットマスクが255.255.255.0といった表記で書かれていれば、この関数を入れた箇所に第四オクテットのビット表記が出現します。

⑦~⑩ 各セルのビット数を足しこむ
255であれば2進数表記上11111111となります。/表記はこのビット数の合計値です。各オクテットごとのビット数をそれぞれ足しこんでいくだけの処理です。
セルI~L3・・・ここで入力している関数は以下の通りです。
=IFERROR(MID(E3,1,1)+MID(E3,2,1)+MID(E3,3,1)+MID(E3,4,1)+MID(E3,5,1)+MID(E3,6,1)+MID(E3,7,1)+MID(E3,8,1),””)
※今回の例はE列が第一オクテット、F,G,H列で第二第三第四と続きます。やっていることは全く一緒なのでまとめて記載しています。
上記赤マーカーの位置にサブネットをビット表記した各オクテットのセルを指定してください。
解説：赤マーカーの位置に計算したいセルの場所を記載しています。今回でいうとまずはE３で第一オクテットのビット数を算出します。かなり原始的なやり方になりますがMID()関数を使って対象セルの何番目から何個の値を算出するかという指定で1から8文字目までを一つずつ足しこんでいきます。11111111であれば8となります。これを赤マーカーの位置を各セグメントのアルファベットに変更して横のセルに関数を入れいていくことで第一オクテット～第四オクテットまでのビット数を算出しました。

⑪ビット数の合計を出して/○○の表記とする。
最後D3に入っている関数の説明です。これは極めてシンプルで、/○○表記とするために算出したビット数の合計値を算出します。そして/表記に書き換えが完了する流れです。
セルD3・・・ここで入力している関数は以下の通りです。
=IFERROR(CONCATENATE(“/”,I3+J3+K3+L3),””)
解説：ビット数算出の合計値と/をconcatenate関数で合体させるという手法です。

255.255.255.0といった表記のサブネットマスクを/24と変換する手法を説明しました。
あとは、B3で分解しておいたIPとD３の/24をconcatenate()関数で合体させて　　　　　　　　　　　　　192.168.11.1 /24としたり、フィルハンドルで関数が入ってるセルを下におろせばA列に値を入れれば入れるだけ変換が可能になっていきます。