IPアドレスから場所、セグメントを自動補完したい

Question

前提・実現したいこと

あるIPアドレスを入力すると、IPアドレスの一覧表(Prefix表記)から、該当する表記を出力するテーブルorプログラムを作成したいです。

例)
一覧表
10.222.2.32/28 本社
10.222.22.0/24 支社
・
・
・

入力
10.222.2.34
10.222.255.254
10.222.22.254

出力
本社
(N/A)
支社

全て/24だとExcelのvlookup等で完結しそうですが、
何か良いアイデアが有れば仰って頂けると幸いです。

触ったことが有る程度なのが上記のタグの言語です。

Answer 1

改めて、今回の目的はA,B列のIP部署一覧から　H列に一斉入力したIPがK列にどの部署か出力したいというものでした。
A列IPアドレス　B部署名データ　　　　　E下限10進数　　F上限10進数　　　　　　　H入力値→貼り付け　J列10進数探索　K列　該当部署名

Sub testip1()

For r = 1 To 7

a = Cells(r, 1).Value '値取得
b = Split(a, "/") 'b(1):/以降
c = Split(b(0), ".")


k = ((c(0) * 256 + c(1)) * 256 + c(2)) * 256 + c(3)

e = 2 ^ (32 - b(1))

j = k + e - 1 '上限

Cells(r, 5).Value = k
Cells(r, 6).Value = j

Next

End Sub

Sub testip2()

For m = 1 To 7
p = Cells(m, 8).Value '入力値取得

q = Split(p, ".")

n = ((q(0) * 256 + q(1)) * 256 + q(2)) * 256 + q(3) '実際の値

Cells(m, 10).Value = n
Next

For u = 1 To 7

v = Cells(u, 10).Value
 For t = 1 To 7
 
 x2 = Cells(t, 5).Value
 x3 = Cells(t, 6).Value
 x4 = Cells(t, 2).Value
 
    If v >= x2 And v <= x3 Then
     Cells(u, 11).Value = x4

   End If
 Next t

Next u

End Sub![イメージ説明](bd95da9e61b58b7da2ddd019d60149da.png)

Answer 2

愚直に自前変換かな

10.222.22.0/24 支社A
10.222.22.128/25 支社B

が、あった場合

10.222.22.129

は、ネットマスクの設定で、支社AかBどちらになるか、変わります。
結局、

10.222.22.129/24
10.222.22.129/25

って、なるので、otnさんの回答にあるvbaを使うか、見栄え気にしないなら、

セルごとに、DEC2BINをして、４オクテット結合したあとに、マスク分をLEFTして、vlookupとか。

Comments

[momon-ga]

がんばればセル分けないでできるけど。そこまで、がんばるもんでもないでしょう。
それなら、vba使った方が楽だと思います。

Answer 3

teratailyh さんが一番分かるツールで作られるのが「良い」と思います.
入力がどこからどういう形式で来て、出力はどういう形式でどこに行くのか等まで投げられては, なにが「良い」のか分からないと思います.

一覧がデータベースにあって入力はCSVファイルで出力は標準出力なコマンドと, JavaのGUIを設計して一覧を一覧テキスト欄に入力しておき入力テキスト欄にIPアドレスを入力すると出力テキスト欄に表示されるのと, 一覧が入ったマスターEXCELファイルが別にあり今開いているEXCELシートを入力として新しいEXCELシートを生成して結果を書き込むのと...等で, どれが「良いアイデア」なのか判断は出来ないのではないでしょうか.

Comments

[退会済みユーザー]

どの言語で何ができて何ができないか、初心者なので判別がつきませんでした。
せめてもう少し具体的に書くように心がけます。

[jimbe]

otnさんへの回答のコメントの最初のほう, 「改めて」から「ここまで」の部分が質問に入っていたら...と思いました.

[退会済みユーザー]

仰る通り、初めの質問が分かりづらかったです、すみません。

Answer 4

VBAで書くしかないんじゃないでしょうか。

各ネットワークアドレスに対して、IPアドレス32ビット整数値の下限と上限をあらかじめ算出しておき、各IPアドレスを32ビット整数値に変換して、さっきの上限下限の間にあるかどうかを順番に調べる。

a = "10.222.2.32/28"
b = Split(a, "/")
c = Split(b(0), ".")
d = ((c(0) * 256 + c(1)) * 256 + c(2)) * 256 + c(3)
If d > 2 ^ 31 Then d = d - 2 ^ 31
e = 2 ^ (32 - b(1))
f = 2 ^ 32 - e
If f > 2 ^ 31 Then f = f - 2 ^ 31
下限 = d And f
上限 = 下限 + e - 1

#追記
回答の方にも追記しておきますが、下限を求めるときにビット演算は不要で、そのまま d が下限になるのでした。ということで、Longの範囲に収めるための if ～ の行2つと、変数 f は不要です。

Comments

[退会済みユーザー]

改めて、今回の目的はA,B列のIP部署一覧から　H列に一斉入力したIPがK列にどの部署か出力したいというものです。
A列IPアドレス　B部署名　　　　　E下限10進数　　F上限10進数　　　　　　　H入力値→貼り付け　J列10進数　K列　該当部署名

113.32.0.0/13 q 1897922560 1898446847 113.52.65.133
113.34.38.128/25 w 1898063488 1898063615 114.153.2.222
113.40.162.0/24 e 1898488320 1898488575 113.34.134.155
113.52.64.0/18 r 1899249664 1899266047 114.122.206.212
113.64.0.0/11 t 1900019712 1902116863 115.122.99.88
114.120.0.0/13 y 1920466944 1920991231 222.122.99.89
114.153.0.0/17 u 1922629632 1922662399 113.40.162.77

ここからがVBA（２つに分けています）コメントなのでベタ書きで失礼します。

Sub testip1() 'EF列を算出するプログラム

Dim k As Long
Dim j As Long
Dim n As Long

For r = 1 To 7

a = Cells(r, 1).Value '値取得
b = Split(a, "/") 'b(1):/以降
c = Split(b(0), ".")

k = ((c(0) * CLng(256) + c(1)) * CLng(256) + c(2)) * CLng(256) + c(3) '下限

e = 2 ^ (32 - b(1))
j = k + e - 1 '上限

Cells(r, 5).Value = k
Cells(r, 6).Value = j

Next

End Sub

Sub testip2() 'J列を出力、EF列と比較し、K列を出力

Dim n As Long

For m = 1 To 7
p = Cells(m, 8).Value '入力値取得
q = Split(p, ".")

n = ((q(0) * CLng(256) + q(1)) * CLng(256) + q(2)) * CLng(256) + q(3) '実際の値

Cells(m, 10).Value = n
Next

For u = 1 To 7

v = Cells(u, 10).Value
For t = 1 To 7

x2 = Cells(t, 5).Value
x3 = Cells(t, 6).Value
x4 = Cells(t, 2).Value

If v >= x2 And v <= x3 Then
Cells(t, 11).Value = x4

'If Cells(u, 10) >= Cells(t, 5) And Cells(u, 10) <= Cells(t, 6) Then
' Cells(t, 11).Value = Cells(t, 2).Value
End If
Next t

Next u

End Sub

このコードの問題点として、
①254.254.254.254のように大きな数字だとk, nの部分で"オーバーフローが発生(6)"となる
②testip2()の2重For文が正しく出力されない（初心者なので些細なミスなのでしょうが・・・）

が挙げられます。

よろしければ、他の回答者様含め、手助け宜しくお願い致します。

[otn]

私の書いた、
If d > 2 ^ 31 Then d = d - 2 ^ 31
とかが、オーバーフローを防ぐ操作ですが、何故削ったのでしょうか？

補足：
ｄへの代入時には、Long範囲外なら自動的に浮動小数点数になるので、OK。（Long型で宣言しては駄目）
Long範囲外かどうか調べて、範囲外なら、負の整数にして、範囲内に収める。

[otn]

あ、正しくは if d>= 2^31 だ。

[退会済みユーザー]

すみません。
その辺りを削ったのが悪いと薄々思っていたのですが、
一旦値が扱える範囲を超えたdがなぜ後から-2^31で良いのか、
プログラム内部でどのようになっているか教えて頂けると助かります。

[otn]

Longは数値符号付き32ビット数なので、扱える範囲は、-2^31 ～ 2^31-1 です。
IPアドレスを単純に数値に直すと、0 ～ 2^32-1 になるので、半分で折り返します。

理解しやすいように小さい数字で考えます。
1バイト符号付きの場合、-128 ～ 127 が表現できます。
0～255の数字を、上記に収めるように、128～255の部分を、-128～-1に移動させます。
127から128にまたぐあたりで大小関係がおかしくなりますが、IPアドレスの場合、2^31をまたぐサブネットはないので大丈夫です。

[退会済みユーザー]

理解できました。ありがとうございます。
変数宣言をしないとLongの範囲を超えると浮動小数点型になり、if d>= 2^31無しでもカバーできることも確認できました。

[otn]

そうか、ネットワークアドレスの方は、ビット演算(And)無しでできましたね。
むりやりLong範囲に収める必要はないのか。