どうも、あらゆる資料をExcelで作成しようとする 0 -Ray- です！
今回は特定のIPアドレスが、アドレスレンジ内(ネットワークアドレス～ブロードキャスト内)のアドレスかを確かめる関数を作成したので紹介します！
それでは行ってみましょう！
完成イメージ共有
今回作成したのは以下のようになっています！
A列：target addressがアドレスレンジに含まれているか判定する
B列：対象のアドレス
I列：ネットワークアドレス
P列：ブロードキャストアドレス
対象のアドレスがネットワークアドレスとブロードキャストアドレスの間にあるか判定できます。
良い意味でも悪い意味でもあらゆる資料がExcelで作成される昨今なので、こういったことができると局所的に役立ちます。(経験談)
解説
それでは仕組みか解説します。
大まかな仕組みは以下のサイトを参考にさせていただきました。
参考：海外の技術フォーラム
まずはアドレスを以下のようにセル毎に分けます。
※計算用の表記のため、私は非表示の列にしています。
※代表として最初の行を解説します。
B列はアドレスを直接入力します。
C列はB列の第1オクテットを取り出します。
セル内の式は以下の通りです。
=NUMBERVALUE(MID(B2,1,FIND(".",B2)-1))D列はB列の第2オクテットを取り出します。
セル内の式は以下の通りです。
=NUMBERVALUE(MID(B2,FIND(".",B2)+1,FIND(".",B2,FIND(".",B2)+1)-1-FIND(".",B2)))E列はB列の第3オクテットを取り出します。
セル内の式は以下の通りです。
=NUMBERVALUE(MID(B2,FIND(".",B2,FIND(".",B2)+1)+1,FIND(".",B2,FIND(".",B2,FIND(".",B2)+1)+1)-1-FIND(".",B2,FIND(".",B2)+1)))F列はB列の第4オクテットを取り出します。
セル内の式は以下の通りです。
=NUMBERVALUE(MID(B2,FIND(".",B2,FIND(".",B2,FIND(".",B2)+1)+1)+1,LEN(B2)))G列はB列のアドレスを数値化したのもです。
セル内の式は以下の通りです。
=C2*256^3+D2*256^2+E2*256+F2これをネットワークアドレスとブロードキャストアドレスでも同様に実施し、
最後に数値化した値を比較し、
target address > network address
target address < broadcast address
になれば、アドレスレンジ内にtarget addressがあることがわかります。
A列はその結果を〇×で表示されるようにしました。
実際は1であれば含まれる、0であれば含まれない となります。
セル内の式は以下の通りです。
=IF(SUMPRODUCT(--($N2<=G2)*--($U2>=G2))=1,"〇","×")Download
実際の資料を共有します！ご自由にどうぞ！
ちょっとわかりにくという方や実際に見て確かめてみたい方は是非！
最後までご覧いただきありがとうございます！
それでは良いエンジニアライフを！！
コメント