ExcelのセルにIPアドレスが入力された時のOKとする入力規則

Question

Excelの入力規則についての質問となるのですが、
Excelのあるセルに入力規則を設定し、
IPアドレスが入力された場合のみ、入力を許可するような動きを
実現したいと考えています。

動きとしては、A1セルに入力規則を指定し、
以下のような数値の場合のみ、入力OKとしたいです。
（セルに全角英字や記号などが入力された場合もNG）

[許可]
192.168.1.1
192.168.1.2

[NG]
192.168.101.
256.256.256.256

上記のようなことは、Excelの入力規則の数式などで実現は可能でしょうか？

ご教授いただければ幸いです。
よろしくお願いします。

Comments

[ttyp03]

マクロを使うのはありですか？

[yutkts]

コメントありがとうございます。
マクロを使うのもありです。
よろしくお願いします。

Answer 1

入力規則の数式ではマクロは使えないようでしたので、ネットで調べたらちょうどいいのがありました。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11156148536
この中のwas********さんの回答が使えそうです。
以下、転載。

=PRODUCT(N(MID(SUBSTITUTE(SUBSTITUTE(B2,".","X",4),".",REPT(" ",100)),ROW(A$1:A$4)*100-99,100)*1<2^8),LEFT(B2))

B2のところをチェックしたいセルのアドレスにしてください。
詳しく解析はしてませんが、すごそうなのはわかる。

Comments

[yutkts]

入力規則の場合、転載いただいた数式を利用することでIPチェックができました！
解析は難しそうですが、こちらで調べてみます。
回答いただきありがとうございました。BAとさせていただきます。

[ikadzuchi]

「1e-2」などの指数表記も数値として扱われてしまうのが微妙ですね。

Answer 2

関数ゴリゴリ書くのもわかりにくいので、標準モジュールに判定関数を書いて、その結果を条件付き書式に設定すればよいかな、とか思いますがどうでしょう？

雑いですが、正しいIP(v4)アドレスならばtrueを返す関数はこんな感じです。

Function IsIPv4Str(TestString As String)
    'とりあえず判定結果はNGとする
    IsIPv4Str = False
    
    '「.」で区切る
    Address = Split(TestString, ".")
    
    '区切った結果の配列が4でなければ終了
    If UBound(Address) <> 3 Then Exit Function

    '各要素をチェック
    For i = 0 To 3
        '1要素取り出す
        Octet = Address(i)
        '対象の要素が、数値として判別できないか、0より小さいか、255より大きければ、判定終了
        If Not (IsNumeric(Octet)) Or Val(Octet) < 0 Or 255 < Val(Octet) Then Exit Function
    Next
    
    'ここまでの判定が通れば判定結果をOKとする
    IsIPv4Str = True
End Function

Comments

[ttyp03]

条件付き書式ではなく入力規則ですね。
ところで入力規則でユーザー関数って使えます？

[退会済みユーザー]

ご指摘ありがとうございます。入力規則でしたね。失礼しました。

入力規則だとユーザー関数は使えない(はず…。)なので、WorkSheet_Changeイベントで同様の判定を行い、Application.Undoするとかでしょうか。
こんなことするなら、難解でも関数駆使のほうがよさそうですね。

[ttyp03]

ワークのセルに判定結果を入れておいて、それを入力規則で使うって手もありますね。
いずれにしても直接ユーザー関数が使えないのは不便ですよね。

[yutkts]

入力規則で難しい場合、最悪VBAで実装も検討しておりましたので、
いただいたコードも参考にさせていただきます。
ご回答いただきありがとうございました。

Answer 3

できそうですが、面倒なので途中で挫折しました。

1. LEFTやRIGHTとFINDを駆使して、.で区切られた要素を4つみつける(これが大変だと思う)
2. 上記のそれぞれをVALUEにかけて数値化して、0以上、255以下であることをANDで判定
3. 4セグメントを全部ANDにかける

上記で組んだ関数を入力規則のユーザ設定に入れる

Comments

[yutkts]

LEFTやRIGHTやFINDについて調べて見たいと思います。
コメントいただきありがとうございました。

[papinianus]

=AND(VALUE(LEFT(B2, FIND(".",B2) - 1)) > -1, VALUE(LEFT(B2, FIND(".",B2) - 1)) < 256)
いちおう最初のセグメントはこれで取れます。
=AND(VALUE(MID(B2,FIND(".",B2)+1,(FIND(".",B2,FIND(".",B2)+1)) - 1 -(FIND(".",B2)))) > -1, VALUE(MID(B2,FIND(".",B2)+1,(FIND(".",B2,FIND(".",B2)+1)) - 1 -(FIND(".",B2)))) < 256)
次のセグメントはこんな感じ
=AND(VALUE(LEFT(MID(B2, FIND(".", B2) + 1, LEN(B2) - FIND(".", B2)), FIND(".",MID(B2, FIND(".", B2) + 1, LEN(B2) - FIND(".", B2))) - 1)) > -1, VALUE(LEFT(MID(B2, FIND(".", B2) + 1, LEN(B2) - FIND(".", B2)), FIND(".",MID(B2, FIND(".", B2) + 1, LEN(B2) - FIND(".", B2))) - 1)) < 256)
第二セグメントはこうすべきかな