大きさの異なる1列に並んだ結合セルから連続で値を抽出

大きさの異なる1列に並んだ結合セルから連続で値を抽出した際のノート．

結果

• 結合セル（抽出対象）: B3:B29
• インデックス（直接入力）: D3:D9
• 抽出結果（配列数式）: E3:E9

数式

=OFFSET(
  $B$3, 
  SMALL(
    IF(
      ISBLANK($B$3:$B$29), 
      9E+9, 
      ROW($B$3:$B$29)-ROW($B$3)
    ), 
    D3
  ), 
)

以上をE3セルへ配列数式として入力（Ctrl+Shift+Enter）．
E4セル以下はE3セルのコピー＆ペーストで可能．

補足:

• 9E+9は入力されると9000000000に自動で変換されます．
• セル行数よりも十分に大きな値を簡単に入力するため9E+9としています．十分に大きな値であれば他の値でも問題ありません．

解説

前置き

• セルアドレスを文字列に置き換えて表現

=OFFSET(
      結合セル全体の先頭セル, 
      SMALL(
        IF(
          ISBLANK(結合セル全体), 
          9E+9, 
          ROW(結合セル全体)-ROW(結合セル全体の先頭セル)
        ), 
        インデックス
      ), 
)

• 結合セルについて
  結合セルはそれぞれの結合セルの先頭のセルしか値を保持しておらず，残りのセルは空白になっています．これはセルの結合を解除すると解ります．
  

以下，数式処理の流れに沿って内側の関数から順を追って数式を説明します（IF > SMALL > OFFSETの順）．

(1) オフセット行数配列の取得

IF関数を使用し，空白ではないそれぞれの結合セルの先頭セルについて，その行番号を元に結合セル全体の先頭セルからのオフセット行数配列を取得します．空白セルについてはオフセット行数の代わりに9E+9を取得します．

IF(
      ISBLANK($$結合セル全体),                       <- セルが空白か判別
      9E+9, 　　　　　　　　　　　　　　　　　　　　　  <- セルが空白の場合，9E+9とする
      ROW($$結合セル全体)-ROW($$結合セルの先頭のセル)  <- セルが空白ではない場合，オフセット行数配列を取得
)

IF関数を使用した場合と使用しない場合の例を以下に示します．
※どちらも配列数式です．

• IF関数を使用した場合

  IF(
    ISBLANK($B$3:$B$29), 
    9E+9, 
    ROW($B$3:$B$29)-ROW($B$3)
  )
  > {0; 9E+9; 9E+9; 9E+9; 9E+9; 9E+9; 9E+9; 7; 9E+9; 9E+9; 9E+9; 11; 9E+9; ...}

• IF関数を使用しない場合

  ROW($B$3:$B$29)-ROW($B$3)
  

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; ...}

(2) 取得されたオフセット行数配列から指定番目に小さなオフセット行数を取得

指定番目に小さなオフセット行数を取得するためSMALL関数を使用します．
SMALL関数の書式は次のとおりです．
書式: SMALL(範囲,順位)
SMALL 関数 - Office サポート

SMALL関数の内部処理は不明ですが，実際に配列を昇順に並び替えて考えるとわかりやすいと思います．
並び替えた際に先程振り分けた"9E+9"は全てのオフセット行数よりも十分に大きいため，配列の後方に配置されます．

• 並び替え前
  {0; 9E+9; 9E+9; 9E+9; 9E+9; 9E+9; 9E+9; 7; 9E+9; 9E+9; 9E+9; 11; 9E+9; ...}

• 並び替え後
  {0; 7; 11; 12; 18; 19; 21; 9E+9; 9E+9; 9E+9; 9E+9; 9E+9; 9E+9; ...}

次に，1~7のインデックス値が入力されたセル（D3:D9）を利用して，指定番目のオフセット行数を取得します．

1. SMALL((1)で取得されたオフセット行数配列, D3) > 0
2. SMALL((1)で取得されたオフセット行数配列, D4) > 7
3. SMALL((1)で取得されたオフセット行数配列, D5) > 11
   ︙

(3) 取得されたオフセット行数を元に結合セルの値を取得

最後にOFFSET関数を使用して，先程取得された結合セルの先頭セルからのオフセット行数を元に結合セルの値を取得します．
書式: OFFSET(基準, 行数, 列数, [高さ], [幅])
OFFSET 関数 - Office サポート

OFFSET($B$3, (2)で取得されたオフセット行数, ) > 結合セルの値

1. OFFSET($B$3, 0, ) > A
2. OFFSET($B$3, 7, ) > B
3. OFFSET($B$3, 11, ) > C
   ︙