スピルの連結

複数のスピルの連結

知恵袋の焼き直し。
作った式が後で見て、わけわかめになるので、メモ。

使用例

指定した範囲（開始,終了)と、周辺の値を取ってきて、連結する。
イメージとしては、図の表から、オレンジ色の部分を中心として取ってきて、
青い部分を周辺として取ってくる。
開始,終了の値は、色々変えて観測したい。

式の説明

=LET(
 '変数定義
 B,$B$5,     '表の起点. offset関数で使用
 S,F4,       '開始行
 L,F5,       '終了行
 C,F6-1,     '参照列. offset関数で使用
 D,L-S+1,    '選択範囲の行数
 P,INT(D/2), '周辺（選択範囲手前）の行数
 N,D-P,      '周辺（選択範囲後）の行数
 '式本体
 '要素（スピルを含む）を連結して、分割する。
 'TEXTSPLITが使えるようになれば、FILTERXMLはいらない。
 'FILTERXMLはタグ(<b>)で囲まれた要素を個別に取ってきているだけ。(FILTERXMLはdesktop windows限定機能らしい)
 'TEXTJOINは各要素がタグで囲まれるように、連結している。
 FILTERXML("<a><b>" & TEXTJOIN("</b><b>",TRUE, ' <a>タグはダミー。<b>タグで囲む
   '連結したい要素を列挙
   "中心",
   OFFSET(B, S-1, C, D, 1), ' 選択範囲を取ってくる
   "　",
   "周辺",
   OFFSET(B, S-1-P, C, P, 1), ' 周辺（選択範囲手前）を取ってくる
   OFFSET(B, L, C, N, 1) ' 周辺（選択範囲後）を取ってくる
  ) & "</b></a>", "//b") ' "//b"は<b>タグの要素を取ってくるXPath
)

Excelの式の中に直接コメント書ければいいのに。。。

実例

No列を取ってくる

=LET(
 B,$B$5, 
 S,F4,
 L,F5,
 C,0,
 D,L-S+1,
 P,INT(D/2),
 N,D-P,
 FILTERXML("<a><b>" & TEXTJOIN("</b><b>",TRUE,
   "中心",
   OFFSET(B, S-1, C, D, 1),
   "　",
   "周辺",
   OFFSET(B, S-1-P, C, P, 1),
   OFFSET(B, L, C, N, 1)
  ) & "</b></a>", "//b")
)

値列を取ってくる

=LET(
 B,$B$5,
 S,F4,
 L,F5,
 C,F6-1,
 D,L-S+1,
 P,INT(D/2),
 N,D-P,
 FILTERXML("<a><b>" & TEXTJOIN("</b><b>",TRUE,
   "中心",
   OFFSET(B, S-1, C, D, 1),
   "　",
   "周辺",
   OFFSET(B, S-1-P, C, P, 1),
   OFFSET(B, L, C, N, 1)
  ) & "</b></a>", "//b")
)