【EXCEL】IPアドレス範囲に該当するかの条件文

EXCELのB列に、調べたいIPアドレスが記載されています。
C列（最小値）からD列（最大値）にかけて、あらかじめ指定した
アドレス範囲が記載されています。E列にC列からD列にかけての
アドレス範囲を説明する文字が記載されています。

B列に記載されているIPアドレスが、B列（最小値）以上、かつ
C列（最大値）以下の範囲にある時、A列にD列の説明文字が表示され、
かつ、B列のアドレスが記載されているセルの背景色を条件付き書式で
変更させたい。

＜該当する場合の例＞
B1は（203.216.243.240）、C1は(203.216.224.0)、
D1は(203.216.255.255)、E1は(ヤフー株式会社)となっている時、
A1(空白)に、(ヤフー株式会社)と表示され、B1のセルの背景が赤色になる。

それぞれのセルに記載する条件文を教えて下さい。
なお、F列以降は空白なので計算式など自由に利用できます。

宜しくお願いします。

参考「IPv4アドレス変換ツール＠Excel」
http://qx-xp.net/ipv4addressexchange_excel.aspx

No.9 回答者： kagakusuki 回答日時： 2011/03/31 06:42

　回答番号:ANo.3、5、7です。

>B1（$B$1）とB5（$B$5）が、CからD列（$C:$D）に書かれているいずれかの行のIPアドレス範囲に該当する場合、A1（$A$1）には、IPアドレス範囲に該当した行のE列にあるセル（$E$？行目）を、A5（$A$5）には、IPアドレス範囲に該当した行のE列にあるセル（$E$？行目）を、それぞれ表示させる。

　失礼しました、ANo.5に対する補足の中の

>複数行にまたがった場合

とは、その様な意味でしたか。
　その場合には、まず、F1セルには、ANo.3で述べたものと同じ数式を入力して下さい。
　次に、G1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(SUBSTITUTE($C1,".",)*1),LEN($C1)-LEN(SUBSTITUTE($C1,".",))=3,ISNUMBER(SUBSTITUTE($D1,".",)*1),LEN($D1)-LEN(SUBSTITUTE($D1,".",))=3),LEFT(C1,FIND(".",C1)-1)*10^9+MID(C1,FIND(".",C1)+1,FIND(".",SUBSTITUTE(C1,".",,1))-FIND(".",C1))*10^6+MID(C1,FIND(".",SUBSTITUTE(C1,".",,1))+2,FIND("゛",SUBSTITUTE(C1,".","゛",3))-FIND(".",SUBSTITUTE(C1,".",,1))-2)*10^3+RIGHT(C1,LEN(C1)-FIND("゛",SUBSTITUTE(C1,".","゛",3))),"")

　次に、G1セルをコピーして、H1セルに貼り付けて下さい。
　次に、G1～H1の範囲をコピーして、同じ列の2行目以下に（C～D列においてIPアドレスが入力されている行数を上回るのに充分な行数になるまで）貼り付けて下さい。

　次に、A1セルに次の数式を入力して下さい。

=IF($F1="","",IF(SUMPRODUCT(($F1>=$G$1:INDEX($G:$G,MATCH(99^9,$G:$G)))*($F1<=$H$1:INDEX($H:$H,MATCH(99^9,$G:$G))))=1,INDEX($E:$E,SUMPRODUCT(ROW($G$1:INDEX($G:$G,MATCH(99^9,$G:$G)))*($F1>=$G$1:INDEX($G:$G,MATCH(99^9,$G:$G)))*($F1<=$H$1:INDEX($H:$H,MATCH(99^9,$G:$G)))))&"",INDEX({"","「複数あり」"},MATCH(SUMPRODUCT(($F1>=$G$1:INDEX($G:$G,MATCH(99^9,$G:$G)))*($F1<=$H$1:INDEX($H:$H,MATCH(99^9,$G:$G)))),{0,2}))))

　次に、F1セルをコピーして、F2以下に（B列においてIPアドレスが入力されている行数を上回るのに充分な行数になるまで）貼り付けて下さい。
　次に、A1セルをコピーして、A2以下に（F列と同じ行数だけ）貼り付けて下さい。


　そして、B1セルの条件付き書式設定では、「数式が」とした上で、次の数式を入力して下さい。

=SUMPRODUCT(($F1>=$G$1:INDEX($G:$G,MATCH(99^9,$G:$G)))*($F1<=$H$1:INDEX($H:$H,MATCH(99^9,$G:$G))))>0

　次に、B1セルをコピーして、B2以下に、[形式を選択して貼り付け]機能を使用して、書式のみを貼り付けて下さい。

　これで、B列の任意のセルに入力されているIPアドレスを数値に変換した数よりも、「C列に入力されているIPアドレスを数値に変換した数の方が小さく、且つ、D列に入力されているIPアドレスを数値に変換した数の方が大きい」という条件を満たす行が、別の行を含む範囲のどこかに存在している場合には、B列のセルの色を赤色とした上で、条件を満たす行のE列の内容を、A列のセルに表示させる事が出来ます。
　又、条件を満たす行が複数存在し、どの行の説明を表示するべきか不明な場合には、

「複数あり」

と表示されます。

No.8 回答者： KURUMITO 回答日時： 2011/03/30 14:54

回答２，６です。

やっとご質問の意味が理解できたようです。
内容が複雑ですので作業列をふんだんに作って対応するのがよいでしょう。
回答６で示したように初めにB列のデータを区切り位置の操作でF,G,H,L列に表示させます。また、C列のデータをJ,K,L,M列に表示させます。同様にD列のデータをN,O,P,Q列に表示させます。
次にR１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(F1="","",VALUE(F1&G1&H1))

S１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(I1="","",I1)

T１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(J1="","",VALUE(J1&K1&L1))

U１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(M1="","",M1)

V１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(N1="","",VALUE(N1&O1&P1))

W１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(Q1="","",Q1)

これで作業列は完成しましたので最後にA１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B1="","",IF(COUNTIF(T:T,R1)>0,IF(S1>=INDEX(U:U,MATCH(R1,T:T,0)),INDEX(E:E,MATCH(R1,T:T,0)),""),IF(COUNTIF(V:V,R1)>0,IF(S1<=INDEX(W:W,MATCH(R1,V:V,0)),INDEX(E:E,MATCH(R1,V:V,0)),""),"")))

最後には作業列が目障りでしたらF列からV列までの列を選択してから右クリックして「非表示」を選択すればよいでしょう。

なおA列に表示のあるB列のセルを赤色にするのでしたら、B列を選択してから「条件付き書式」で「数式が」などを選んで、数式の窓には=A1<>"" と入力して「書式」からは「塗りつぶし」で赤色を選択すればよいでしょう。

No.7 回答者： kagakusuki 回答日時： 2011/03/29 17:48

　回答番号:ANo.3、5です。

>複数行にまたがった場合の方法をご教示ください。

　A1～H1の範囲をコピーして、2行目以下に貼り付けた後、B列～D列のIPアドレスを、適時書き換えるだけでOKです。
　1行ずつ書き換えるのが面倒な場合には、一旦A2～H2の範囲に貼り付けてから、B2～D2の範囲にあるセルを空欄にして、
その上で、A2～H2の範囲をコピーして、3行目以下に貼り付けた後、B列～D列に新たにIPアドレスを入力されると良いと思います。

この回答への補足

なんとご説明したらいいのか難しいのですが、
単純に申し上げようとするとこうなります。

B1（$B$1）とB5（$B$5）が、CからD列（$C:$D）に書かれている
いずれかの行のIPアドレス範囲に該当する場合、
A1（$A$1）には、IPアドレス範囲に該当した行のE列にあるセル（$E$？行目）を、
A5（$A$5）には、IPアドレス範囲に該当した行のE列にあるセル（$E$？行目）
を、
それぞれ表示させる。

一行におけるCからE列（$C:$E）の組み合わせは同じであるが、
１行目と2行目には別の組み合わせが、２行目と３行目にはまた別の
組み合わせが記載されている。

CからE列（$C:$E）の組み合わせは最初に数行分記載した後は、
ほとんど書き換えることはありません。例：$C$1:$E$100

しかしBの列は、調べたいIPを都度書き換えていきます。
B1（$B$1）にはさっきとは別のIPに書き換えたり、B2（$B$2）に
あらたに調べたいIPを書き加えます。

すると、A1（$B$1）やA2（$A$2）には、B1（$B$1）やB2（$B$2）が、
あらかじめ記載されているCからD列（$C$1:$D$100）にあるいずれかの
IP範囲に該当する場合、該当した●行のE列のセル（$E$該当した●行番号）を
表示させる。。。

少し話か冗長しましたが、以上です。

（vlookup関数を使う必要がある？）

よろしくお願いします。

補足日時：2011/03/29 20:27

No.6 回答者： KURUMITO 回答日時： 2011/03/25 09:04

回答No2です。

式が複雑になるので簡単には次のようにすることでしょう。
なお、次の方法ではIPアドレスが何ケタの数値の組み合わせになっていても対応することができます。
初めにB列を選択します。その後に「データ」タブの「区切り位置」で「次へ」、「区切り文字」のその他に「.」を入力して「次へ」、「表示先」の窓には$F$1と入力して「完了」します。この操作でB列にある３つの.で区切られた４つの数値がF,G,H,I列にそれぞれ表示されます。同様にしてC列を選択して区切り文字の操作で、表示先を$J$1として、それらの数値をJ,K,L,M列に表示します。D列についても表示先を$N$1として、４つの数値をN,O,P,Q列に表示させます。
その後にA１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(E1="","",IF(AND(MIN(F1:I1)>=MIN(J1:M1),MAX(F1:I1)<=MAX(N1:Q1)),E1,""))

A列に表示のあるB列のセルの背景を赤にするためには次のようにします。
初めにB列を選択します。
次に「条件付き書式」で「数式が」などにして数式の窓には次の式を入力します。

=A1<>""

同じ画面の「書式」をクリックして「塗りつぶし」のタブから赤色を選択してOKします。

この回答へのお礼

ご回答ありがとうございます。
一行に限定したやり方としては間違いではないのですが、
複数行では実現できません。

C列からE列にかけてのアドレス範囲は排他的領域です。
実現例としては添付図のように、B列のセルのIPが、C列からE列に
かけてのどの範囲に属しているかを知りたいのです。

Bのセルが検索したい数字であり、C列からE列のアドレス範囲が
DBという扱いです。

よって、C列からE列のアドレスは、各行によって数字の昇順・降順に
関わらずランダムに表記されていますが、アドレス範囲は排他的領域なので
C列からE列をソートして昇順にすることは想定内です。

お礼日時：2011/03/29 16:15

No.5 回答者： kagakusuki 回答日時： 2011/03/25 02:06

>

=IF(AND(ISNUMBER(SUBSTITUTE(B1,".",)*1),LEN(B1)-LEN(SUBSTITUTE(B1,".",))=3),LEFT(B1,FIND(".",B1)-1)*10^9+MID(B1,FIND(".",B1)+1,FIND(".",SUBSTITUTE(B1,".",,1))-FIND(".",B1))*10^6+MID(B1,FIND(".",SUBSTITUTE(B1,".",,1))+2,FIND("゛",SUBSTITUTE(B1,".","゛",3))-FIND(".",SUBSTITUTE(B1,".",,1))-2)*10^3+RIGHT(B1,LEN(B1)-FIND("゛",SUBSTITUTE(B1,".","゛",3))),"")

>上記の関数では何を求めているのか簡単なご説明をいただければ幸いです。

　まず、

AND(ISNUMBER(SUBSTITUTE(B1,".",)*1),LEN(B1)-LEN(SUBSTITUTE(B1,".",))=3)

の部分はIF関数の判定式で、

ISNUMBER(SUBSTITUTE(B1,".",)*1)

の部分で、B1の値から、SUBSTITUTE関数を使用して「.」を全て取り除くと、数字のみになっている事を判定し、

LEN(B1)-LEN(SUBSTITUTE(B1,".",))=3

の部分で、「.」を全て取り除いた前後における、文字数の差が3文字である事を判定する事で、「.」が3個含まれている事、即ち「.」によって4つに区切られている事を判定しています。
　それを、ANDで囲む事で、「.」によって4つに区切られている数字である事を判定しています。

　次に、

LEFT(B1,FIND(".",B1)-1)

の部分は、FIND関数を使って最初の「.」が、左から何番目にあるのかを求めて、それよりも前にある部分、即ちB1セルの値である

203.216.243.240

の中の

203

を取り出しています。

　次に、

MID(B1,FIND(".",B1)+1,FIND(".",SUBSTITUTE(B1,".",,1))-FIND(".",B1))

の部分は、最初の「.」よりも右側で、2個目の「.」よりも左にある

216

を取り出しています。
　この時、2個目の「.」の位置を求めるために、SUBSTITUTE関数を使用して、最初の「.」を取り除いた文字列中で、最初に「.」が現れる位置、即ち、元の文字列の2個目の「.」の位置（正確には、その１つ前の位置）を求めています。

　次に、

MID(B1,FIND(".",SUBSTITUTE(B1,".",,1))+2,FIND("゛",SUBSTITUTE(B1,".","゛",3))-FIND(".",SUBSTITUTE(B1,".",,1))-2)

の部分は、2個目の「.」よりも右側で、3個目の「.」よりも左にある

243

を取り出しています。
　この時、3個目の「.」の位置を求めるために、SUBSTITUTE関数を使用して、3個目の「.」を、IPアドレスに現れる筈がない文字である「゛」（濁点）に置換し、置換後の文字列中における「゛」の位置、即ち、元の文字列の3個目の「.」の位置を求めています。

　次に、

RIGHT(B1,LEN(B1)-FIND("゛",SUBSTITUTE(B1,".","゛",3)))


の部分は、3個目の「.」よりも右側にある

240

を取り出しています。

　そして、4個の数字の桁が重ならない様にするために、左側の区切りの数字の桁が、右側の区切りの数字の桁よりも、3桁大きな値となる様に、最初の区切りには10億、2番目の区切りには100万、3番目の区切りには1000を乗じてから、4個の数字を合計し、

203216243240

という数値を得ています。

　C1とD1に対しても、同様の処理を行い、数値に変換してから、各IPアドレスの大小を比較しています。
　尚、単純に全ての「.」を削除しただけでは、

203.216.224.0

の様に、3桁未満の数字が含まれている場合には、桁がずれてしまい、IPアドレスの大小を比較する事が出来なくなります。

この回答へのお礼

IPの特質を理解して頂いている最も理想的に近い回答です。
複数行にまたがった場合の方法をご教示ください。

お礼日時：2011/03/29 16:17

No.4 回答者： mike_g 回答日時： 2011/03/25 00:58

[No.1補足]へのコメント、

IPアドレスの形式を、私は誤解しておりました。
203.216.243.240 のように３桁ずつと思い込んでいたのです。
つまり、203.216.224.0 でなく、203.216.224.000 という具合に。
でも貴方は「C1は(203.216.224.0)」と仰っていたのを、いま気づきました。

私が提示した式は「３桁ずつ」の形式の場合にしか対応していないので、私の回答は無視してください。

それはソレとして、貴方の説明が理解できません。
添付図の１行目は
B1: 203.216.243.240
C1: 203.216.224.000
D1: 203.216.255.255
としていたので、セル B1 を赤く表示し、セル A1 にセル E1 の内容を表示させたのです。つまり、既にセル A1 に「ヤフー株式会社」と表示されているので、「B1のIPが、別の行であるC2からD2列にかけてのアドレス範囲（ユホー株式会社）である場合にも、A1に（ユホー株式会社）と表示させる方法」はありません。
ひょっとして、その場合は、「ヤフー株式会社」と「ユホー株式会社」の二つを表示させたいということでしょうか？それは無理難題のような気がしています。
何れにしても、文章でゴチャゴチャ述べるより、貴方が期待する結果例を添付図で示した方が一目瞭然です。

この回答への補足

捕捉になりますが、例にあげて頂いた「ヤフー株式会社」と「ユホー株式会社」の場合、同じアドレス範囲ではありません。排他的領域となります。

補足日時：2011/03/29 16:03

この回答へのお礼

「３桁ずつ」の形式の場合にしか対応していない件、ご連絡ありがとうございます。

私が期待する例を質問の欄に添付しました。

お礼日時：2011/03/25 01:55

No.3 回答者： kagakusuki 回答日時： 2011/03/24 09:19

　まず、F1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(SUBSTITUTE(B1,".",)*1),LEN(B1)-LEN(SUBSTITUTE(B1,".",))=3),LEFT(B1,FIND(".",B1)-1)*10^9+MID(B1,FIND(".",B1)+1,FIND(".",SUBSTITUTE(B1,".",,1))-FIND(".",B1))*10^6+MID(B1,FIND(".",SUBSTITUTE(B1,".",,1))+2,FIND("゛",SUBSTITUTE(B1,".","゛",3))-FIND(".",SUBSTITUTE(B1,".",,1))-2)*10^3+RIGHT(B1,LEN(B1)-FIND("゛",SUBSTITUTE(B1,".","゛",3))),"")

　次に、F1セルをコピーして、G1セルとH1セルに貼り付けて下さい。
　次に、A1セルに次の数式を入力して下さい。

=IF(AND($E1<>"",COUNT($F1:$H1)=3),IF(OR($F1<$G1,$F1>$H1),"",$E1),"")


　そして、B1セルの条件付き書式設定では、「数式が」とした上で、次の数式を入力して下さい。

=AND(COUNT($F1:$H1)=3,$F1>=$G1,$F1<=$H1)

この回答へのお礼

=IF(AND(ISNUMBER(SUBSTITUTE(B1,".",)*1),LEN(B1)-LEN(SUBSTITUTE(B1,".",))=3),LEFT(B1,FIND(".",B1)-1)*10^9+MID(B1,FIND(".",B1)+1,FIND(".",SUBSTITUTE(B1,".",,1))-FIND(".",B1))*10^6+MID(B1,FIND(".",SUBSTITUTE(B1,".",,1))+2,FIND("゛",SUBSTITUTE(B1,".","゛",3))-FIND(".",SUBSTITUTE(B1,".",,1))-2)*10^3+RIGHT(B1,LEN(B1)-FIND("゛",SUBSTITUTE(B1,".","゛",3))),"")

上記の関数では何を求めているのか簡単なご説明をいただければ幸いです。
扱っているのがただの数値ではなく、IPアドレスというコンマ区切りのもの
ですので関数がオクテット毎に計算しているのでしょうか。

お礼日時：2011/03/24 21:05

No.2 回答者： KURUMITO 回答日時： 2011/03/24 08:58

非常に長い式になりますがA1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTA(B1:E1)<>4,"",IF(AND(IF(LEN(SUBSTITUTE(B1,".0",""))=15,MIN(MID(B1,1,3),MID(B1,5,3),MID(B1,9,3),MID(B1,13,3)),IF(LEN(SUBSTITUTE(B1,".0",""))=11,MIN(MID(SUBSTITUTE(B1,".0",""),1,3),MID(SUBSTITUTE(B1,".0",""),5,3),MID(SUBSTITUTE(B1,".0",""),9,3)),IF(LEN(SUBSTITUTE(B1,".0",""))=7,MIN(MID(SUBSTITUTE(B1,".0",""),1,3),MID(SUBSTITUTE(B1,".0",""),5,3)),IF(LEN(SUBSTITUTE(B1,".0",""))=3,SUBSTITUTE(B1,".0","")))))>=IF(LEN(SUBSTITUTE(C1,".0",""))=15,MIN(MID(C1,1,3),MID(C1,5,3),MID(C1,9,3),MID(C1,13,3)),IF(LEN(SUBSTITUTE(C1,".0",""))=11,MIN(MID(SUBSTITUTE(C1,".0",""),1,3),MID(SUBSTITUTE(C1,".0",""),5,3),MID(SUBSTITUTE(C1,".0",""),9,3)),IF(LEN(SUBSTITUTE(C1,".0",""))=7,MIN(MID(SUBSTITUTE(C1,".0",""),1,3),MID(SUBSTITUTE(C1,".0",""),5,3)),IF(LEN(SUBSTITUTE(C1,".0",""))=3,SUBSTITUTE(C1,".0",""))))),IF(LEN(SUBSTITUTE(B1,".0",""))=15,MAX(MID(B1,1,3),MID(B1,5,3),MID(B1,9,3),MID(B1,13,3)),IF(LEN(SUBSTITUTE(B1,".0",""))=11,MAX(MID(SUBSTITUTE(B1,".0",""),1,3),MID(SUBSTITUTE(B1,".0",""),5,3),MID(SUBSTITUTE(B1,".0",""),9,3)),IF(LEN(SUBSTITUTE(B1,".0",""))=7,MAX(MID(SUBSTITUTE(B1,".0",""),1,3),MID(SUBSTITUTE(B1,".0",""),5,3)),IF(LEN(SUBSTITUTE(B1,".0",""))=3,SUBSTITUTE(B1,".0","")))))<=IF(LEN(SUBSTITUTE(D1,".0",""))=15,MAX(MID(D1,1,3),MID(D1,5,3),MID(D1,9,3),MID(D1,13,3)),IF(LEN(SUBSTITUTE(D1,".0",""))=11,MAX(MID(SUBSTITUTE(D1,".0",""),1,3),MID(SUBSTITUTE(D1,".0",""),5,3),MID(SUBSTITUTE(D1,".0",""),9,3)),IF(LEN(SUBSTITUTE(D1,".0",""))=7,MAX(MID(SUBSTITUTE(D1,".0",""),1,3),MID(SUBSTITUTE(D1,".0",""),5,3)),IF(LEN(SUBSTITUTE(D1,".0",""))=3,SUBSTITUTE(D1,".0","")))))),E1,""))

なお、B列でA列に記載のあるセルの背景色を付けるのでしたら、B列を選択したのちに「条件付き書式」で「数式が」などを選んで数式には次の式を入力し、同じ画面の「書式」から「塗りつぶし」のタブで好みの色を指定すればよいでしょう。

=A1<>""

この回答への補足

残念ながら、式が長いので入力制限に引っ掛かってしまいました。

補足日時：2011/03/24 20:49

No.1 回答者： mike_g 回答日時： 2011/03/24 06:57

添付図参照

A1: =IF(AND(B1>=C1,B1<=D1),E1,"")

セル B1 の条件は次のとおり
数式が　　　　AND(B1>=C1,B1<=D1)
フォント色　　白
パターン色　　赤

この回答への補足

添付図を確認しました。

添付図のB1のIPが、別の行であるC2からD2列にかけての
アドレス範囲（ユホー株式会社）である場合にも、A1に
（ユホー株式会社）と表示させる方法はありますか。

C列からE列にかけていくつかのアドレス範囲とその説明を
記載していき、B1のセルのIPにはそのいずれかのアドレス範囲に
該当した場合にその説明をA1に表示させるのが最終目的です。

よろしくお願いします。

補足日時：2011/03/24 20:57