Excel上でIPアドレスをオクテットごとに分解する関数

Excel

当記事ではアフィリエイトプログラムを利用して商品を紹介するプロモーションを含んでいます

IPアドレスは、”192.168.11.1″ や “172.17.1.1” などとドットで数字を区切っているわけですが、

これらを管理している人間からすると

・大量のIPアドレス群から第三オクテットまでを抜き出したい

・第一から第四オクテットまでを分割してフレキシブルに管理したい

こういった要望が出てくることがあります。

IPアドレスを打ち込めばオクテットごとに分割できるExcelの関数を紹介します。

大量のデータ編集を容易にするヒントになればと思います。

上図はExcelで元のIPアドレスと、それをオクテットごとに分割する関数を埋め込んだ実際のファイルをキャプチャしています。

各列1行目はヘッダーとしていて、実データは2行目からになります。各セルの内容を詳しく見ていきましょう。

セルA2 ・・・ ここは分割対象となるIPアドレスを入力する箇所です。ただ単に192.168.11.1と入力しているだけですね。

セルB2 ・・・ 第一オクテット分割の箇所です。ここで入力している関数は以下の通りです。              =IFERROR(LEFT(A2,FIND(“.”,A2)),””) → この関数でいうところのA2は分割対象となるIPアドレスが入力されているセルを指定しています。A2のセル内容から ” . ” が最初にくる文字数分を対象セルの左から抜き出すという内容ですね。

セルB3 ・・・ 第二オクテット分割の箇所です。ここで入力している関数は以下の通りです。           =IFERROR(LEFT(RIGHT(A2,LEN(A2)-LEN(B2)),FIND(“.”,RIGHT(A2,LEN(A2)-LEN(B2)))),””)                                    →A2は先ほどと同様対象セル。ここではB2セルも指定していますが第一オクテット目を抜き出したその文字数分を全体から差し引いたのち、第一オクテットと同様の手法で ” . ” が最初に来る文字数分を左から抜き出すという手法ですね。                                   192.168.11.1というIPから第一オクテットを差し引いた168.11.1を対象として ” . ” を探して第二オクテットを抜き出しています。 

セルB4 ・・・ 第三オクテット分割の箇所です。ここで入力している関数は以下の通りです。       =IFERROR(LEFT(RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)),FIND(“.”,RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)))),””) →考え方は第二オクテットと同じで、第一オクテット第二オクテット分差し引いた文字列からまた   ” . ” を探し出して第三オクテットを抜き出します。                          

セルB5 ・・・ 第四オクテット分割の箇所です。ここで入力している関数は以下の通りです。          =IFERROR(RIGHT(A2,LEN(LEFT(A2,LEN(A2)-LEN(B2)-LEN(C2)-LEN(D2)))),””)    →最後の関数です。原始的な抜き出し方ですが、元IPアドレスの右から第四オクテットの文字数分抜き出すのですが、第一~第三オクテットの文字数を元IPアドレスの文字数からそれぞれ差し引いた文字数が第四オクテットの文字数となるので、その値分右から抜き出すという手法ですね。          192.168.11.1の文字数から192.と168.と11.の文字数分差し引いて右から取り出すことで1が残るという考え方です。  

IPの分割はここまでで完了ですが、これらのIPを組み合わせる場合、

このように =concatenate(B2,C2,D2)   とすることでB,C,D列の内容を合体させることで第三オクテットまでの内容となりました。

B,C,D,E,F列に入力した関数をそのまま下のほうまでフィルハンドルで引っ張ればA列に入力したIPアドレスをひたすら分割して第三オクテットまで結合してくれます。

コメント

タイトルとURLをコピーしました