- プレフィックスとサブネットマスクの変換は表引きするのが確実ですが関数式による変換も可能です。
手順
VLOOKUP関数による変換
変換のパターンが限られているので、表を作っておき参照するのが確実です。
式が単純になりますし、不適切な値を判定することもできます。
画像ではB3:C35の範囲に33通りのパターンを記録しておき（下方が途切れていてスミマセン）、E3に入力されたプレフィックスに対しF3セルの式によりサブネットマスクを算出しています。また、E6セルに入力されたサブネットマスクに対しF6セルの式によりプレフィックスを算出しています。
F3セル（プレフィックス→サブネットマスク）
=VLOOKUP(E3,B3:C35,2,FALSE)
F6セル（サブネットマスク→プレフィックス）
=VLOOKUP(E6,{C3:C35,B3:B35},2,FALSE) いずれも単純な表引きですが、後者は「VLOOKUPで左側の列を取得する」の例です。
次の記事でも例を紹介しています。
BIN2DEC/DEC2BIN関数による変換
関数式だけで変換を行う例です。
式が複雑になるほか、特にサブネットマスク→プレフィックスの場合に不適切なパターンの判定が容易でないのが弱点で、この例でも判定は行っていません。
まずプレフィックス→サブネットマスクの例です。
C3セルに式を入力してB3セルの値を変換しています。以降のセルも同様です。
C3セル
=JOIN(".",ARRAYFORMULA(BIN2DEC(MID(REPT("1",B3)&REPT("0",32),{1,9,17,25},8)))) REPT関数を使って「数値の数だけ『1』を並べてその後に『0』を32文字並べた文字列」を生成します。
MID関数を使い「1~8文字目」「9~16文字目」「17~24文字目」「25~32文字目」に分割して抽出します。
BIN2DECでそれぞれ10進数に変換し、最後にJOIN関数でピリオドを付けて連結しています。
続いてサブネットマスク→プレフィックスの例です。
C3セルに式を入力してB3セルの値を変換しています。以降のセルも同様です。
C3セル
=LEN(REGEXREPLACE(CONCATENATE(ARRAYFORMULA(DEC2BIN(SPLIT(B3,".")))),"0",""))
SPLIT関数で文字列を4つに分割し、それらをDEC2BIN関数で2進数に変換し、CONCATENATE関数で連結します。
そしてREGEXREPLACEにより「0」を空文字列に置き換え、LEN関数で残った「1」の長さを数えています。はじめからREGEXEXTRACTで「1」を数える方法も考えられますがオール0のときにマッチしなくてエラーになります。
