【Excel関数】IPv6アドレスの省略表記にゼロを補完する【No VBA】

16871 ワード
IPアドレス Excel関数 ネットワーク IPv6 IPv6 テキストリンク

概要

IPv6の省略形から、ゼロを補完した完全体に変換したい。
VBAを使わずExcel関数だけで、なおかつ途中計算用のセルをなるべく作らずに実現することを目指す。

下準備

IPv6アドレスを入力するセルに、【addr】と名前を定義する。
セルに名前をつけることで、セル番地に関わらず常に一定のセルを指定することができる。つまり、セルの名前さえ正しく定義すれば、下記のコードをどのファイルに貼り付けてもそのまま使える。

※Excelの「名前の定義」については下記を参照のこと。

コード

Ver.1

コロンコロンを展開し、16bit fieldごとに列記、最後にtextjoinするパターン。

addr2

連続したゼロを省略する"::"を、":0"に変換する。"::"がなければ、なにもしない。
省略のないIPv6アドレスだと、":"の数は7つ。
"::"で省略されるfieldは、8から":"の数を引いたもの。
ex1) 2001::1　→コロン2個、省略field6個
ex2) 2001:2:3:4:5::1　→コロン6個、省略field2個
ex3) 2001:2:3:4:5:6::1　→コロン7個、省略field1個　※推奨される書き方かは不明。
ex4) 2001:2:3:4:5:6:7:1　→コロン7個、省略field0個　※"::"がないので、省略なしと判断。
LEN(addr)-LEN(SUBSTITUTE(addr,":",""))で":"を数え、省略されたfieldの数だけREPTで":0"を繰り返し、最後に":"をつける。
ただし、元のアドレスの末尾が"::"や"::/xx"だった場合は、ゼロを補って":0"にする必要がある。

=SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&IF(OR(COUNTIF(addr,"*::"),COUNTIF(addr,"*::/*")),":0",":"))
field1

fieldの頭のゼロを補完する。最初の":"より手前の文字数を調べ、文字数が4になるようにREPTでゼロを追加する。

=REPT(0,4-LEN(LEFT(addr2,FIND(":",addr2)-1)))&LEFT(addr2,FIND(":",addr2)-1)
field2

1個目の":"を開始位置にして、2個目の":"の手前までをMIDで取得する。
n個目の":"の位置を把握するには、FINDとSUBSTITUTEを組み合わせて使う。元の文字列(addr2)のn回目に出てきた":"をSUBSTITUTEでダミー文字(Θ)に置換してから、ダミー文字をFINDで検索することで、n回目に出てきた":"の位置を割り出すことができる。
2個目の":"の位置から1個目の":"の位置を引いてfield2の文字数を調べ、field1と同様にREPTでゼロを追加する。
(以下、field7までは同様)

=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)
field3
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)
field4
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)
field5
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)
field6
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)
field7
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)
field8

元の入力値にプレフィックス(/64とか)がついていたりいなかったりすることを想定し、IFで分岐。
プレフィックスありなら、"/"の位置を調べてその手前までの文字数に対してREPTでゼロ付加してから、RIGHTで取得した"/"以降を末尾に付加する。
プレフィックスなしなら、MIDの文字数をfieldの最大値4にする。

=IF(COUNTIF(addr2,"*/*"),REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)))&MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)&RIGHT(addr2,LEN(addr2)-FIND("/",addr2)+1),
REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4)))&MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4))

Ver.2

コロンコロンを展開するために、1セルだけ処理用セルを作成したパターン。

addr2

Ver.1と同じ。

=SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&IF(OR(COUNTIF(addr,"*::"),COUNTIF(addr,"*::/*")),":0",":"))
結果を出力するセル

Ver.1で各セルに分けたfieldを、":"でつないですべて単一のセルに入れた形。
(2186文字)

=REPT(0,4-LEN(LEFT(addr2,FIND(":",addr2)-1)))&LEFT(addr2,FIND(":",addr2)-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)&":"
&IF(COUNTIF(addr2,"*/*"),REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)&RIGHT(addr2,LEN(addr2)-FIND("/",addr2)+1),
REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4)))&MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4))

Ver.3

コロンコロンの処理(addr2)も無理矢理ひとつのセルにねじ込んでみたパターン。
7218文字。該当セルでF2押すだけでExcelが数秒間操作不能(場合によっては落ちる)になったので、実用的ではない。
※field8が"::"のときの挙動が正確でないことが判明。修正しようと思ったがExcel操作不能になるので未対応。

=REPT(0,4-LEN(LEFT(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND(":",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-1)))&LEFT(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND(":",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-1)&":"
&IF(COUNTIF(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),"*/*"),REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,FIND("/",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-1)))&MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,FIND("/",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-1)&RIGHT(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),LEN(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-FIND("/",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))+1),
REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,4)))&MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,4))

まとめ

  • ゼロ補完の逆、ゼロ省略も関数でやってみたくなった。
  • 関数の処理を解説するのは大変(今回はほぼ放棄してる)。
  • 関数込みで7000文字超をひとつのセルに入れると、処理が滞る。
  • なんでもひとつのセルに入れればいいってものでもない。途中計算の結果を適宜別セルに記載することで、可読性の向上やバグフィックスに役立つ。