以下の画像のように、一部のセルが結合された表を、たった１つの数式（黄色のセル）で、セルの結合を解除して抽出する方法について紹介していきます。

📘 Excel小技１００選（５００円）

セル結合を解除して抽出する数式

こちらでは、以下の左側の「セルの結合を含む表」から、セルの結合を含まない状態で右側の表に抽出していきます。

数式に使う関数は、SCAN関数です。
SCAN関数は、指定した範囲のそれぞれの値に対して、１つ前に処理した値を活用し、さらに処理を行った結果を返します。

まずは、先頭の「大カテゴリー」をセルの結合を含まない状態で抽出する場合、SCAN関数を用いて、以下の法則で抽出する必要があります。

大カテゴリーの先頭のセルから順番に確認し、 ・セルが空でない場合は、そのまま値を抽出 ・セルが空の場合は、１つ前に抽出した値を抽出

これを数式で表現すると、以下のようになります。

=SCAN("",B3:B11,LAMBDA(a,b,IF(b<>"",b,a)))
// ""：初期値に、最初の値に関しては１つ前に抽出した値が存在しないため「空」を指定
// B3:B11：処理対象の範囲に、「大カテゴリーの範囲」を指定
// LAMBDA(a,b,IF(b<>"",b,a)：処理の内容に、「LAMBDA関数」を指定
// → LAMBDA(1つ前に抽出した値[1], 確認対象の値[2], [1]と[2]を用いた数式)

数式を入力することで、以下のように抽出することができます。

他の項目に関して、単純に「B3:B11（大カテゴリー）」の範囲を広げれば抽出できそうに思えますが、実際は、以下のように上手くいきません。

=SCAN("",B3:D11,LAMBDA(a,b,IF(b<>"",b,a)))

この原因は、SCAN関数が処理する順番にあります。
SCAN関数は、指定された範囲の先頭行の先頭列から隣の列へと処理をします。
そのため、SCAN関数を用いて複数列の範囲を指定する場合は、行と列の値を入れ替える必要があります。

それを実現するには、TRANSPOSE関数を活用します。
SCAN関数に指定する範囲をTRANSPOSE関数で囲って、以下のように指定します。

=SCAN("",TRANSPOSE(B3:D11),LAMBDA(a,b,IF(b<>"",b,a)))

ただ以下のように、処理した結果も行と列の値が入れ替わってしまいます。

そのため、SCAN関数自体もTRANSPOSE関数で囲って、以下のように指定します。

=TRANSPOSE(SCAN("",TRANSPOSE(B3:D11),LAMBDA(a,b,IF(b<>"",b,a))))

これで完成です。
以下のように、セルの結合を解除して抽出することができます。

リンクの共有はこちらから行えます。

  リンクコピー    X Facebook はてなブックマーク Pocket