ITよろづや

ITの参考になる情報を備忘録代わりに残していきます

サブネットのレンジの中にIPアドレスが含まれるかエクセルで正誤判定する関数

サブネットのレンジの中にIPアドレスが含まれるかエクセルで正誤判定する関数についての情報になります。

 

例えば、「192.168.1.0」から「192.168.1.254」に「192.168.1.10」が含まれる場合は、OK、含まれない場合はNGという判定が可能です。

 

参考になったサイトはこちら

 

Excel Lookup IP addresses in multiple ranges※海外の技術共有のサイトです

https://stackoverflow.com/questions/42197110/excel-lookup-ip-addresses-in-multiple-ranges/42230331

 

 列AのIPアドレスをチェックし、B他の2つの列Cとの2つのアドレスの範囲(またはその間にある)に該当するかどうかを確認する列の式を見つけようとしていますDというものです。※翻訳

 

関数を以下に抜粋しました。

 

■エクセルの項目

valid address start end

address1 address2 address3 address4 address value

start1 start2 start3 start4 start value

end1 end2 end3 end4 end value

vaild

 

■address

=NUMBERVALUE(MID(B2,1,FIND(".",B2)-1))
=NUMBERVALUE(MID(B2,FIND(".",B2)+1,FIND(".",B2,FIND(".",B2)+1)-1-FIND(".",B2)))
=NUMBERVALUE(MID(B2,FIND(".",B2,FIND(".",B2)+1)+1,FIND(".",B2,FIND(".",B2,FIND(".",B2)+1)+1)-1-FIND(".",B2,FIND(".",B2)+1)))
=NUMBERVALUE(MID(B2,FIND(".",B2,FIND(".",B2,FIND(".",B2)+1)+1)+1,LEN(B2)))
=F2*256^3+G2*256^2+H2*256+I2

 

■start

=NUMBERVALUE(MID(C2,1,FIND(".",C2)-1))
=NUMBERVALUE(MID(C2,FIND(".",C2)+1,FIND(".",C2,FIND(".",C2)+1)-1-FIND(".",C2)))
=NUMBERVALUE(MID(C2,FIND(".",C2,FIND(".",C2)+1)+1,FIND(".",C2,FIND(".",C2,FIND(".",C2)+1)+1)-1-FIND(".",C2,FIND(".",C2)+1)))
=NUMBERVALUE(MID(C2,FIND(".",C2,FIND(".",C2,FIND(".",C2)+1)+1)+1,LEN(C2)))
=L2*256^3+M2*256^2+N2*256+O2

 

■end

=NUMBERVALUE(MID(D2,1,FIND(".",D2)-1))
=NUMBERVALUE(MID(D2,FIND(".",D2)+1,FIND(".",D2,FIND(".",D2)+1)-1-FIND(".",D2)))
=NUMBERVALUE(MID(D2,FIND(".",D2,FIND(".",D2)+1)+1,FIND(".",D2,FIND(".",D2,FIND(".",D2)+1)+1)-1-FIND(".",D2,FIND(".",D2)+1)))
=NUMBERVALUE(MID(D2,FIND(".",D2,FIND(".",D2,FIND(".",D2)+1)+1)+1,LEN(D2)))
=R2*256^3+S2*256^2+T2*256+U2

=SUMPRODUCT(--($P$2:$P$8<=J2)*--($V$2:$V$8>=J2))

 

入力するとこんな感じです。

f:id:merrywhite:20201006082500p:plain

 

サブネットのレンジの中にIPアドレスが含まれるかエクセルで正誤判定する関数の情報を探していた方は参考にしてみてください。

 

Anker PowerCore Essential 20000 (モバイルバッテリー 超大容量 20000mAh) 【USB-C入力ポート/PSE認証済/PowerIQ/低電流モード搭載】iPhone iPad Android 各種対応 (ブラック)