Excel で、セル結合を解除して一覧を作る。

この記事は自分用の覚書です。

■MacBook Pro macOS Ventura
バージョン 13.6.4

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.83 (24031120)

H.K さん。

なんと呼んでよいかわかりませんが、次のような作業のことですよね。

■考え方

A列のセルは、3行分のセル（次図A2からA4、A5からA7、A8からA10）ごとにセル結合しているので、「"セル3つ分の面積" のなかに文字列が入っている」ように見えます。

ですが、それはあくまで「そう見えるという "見かけ"」の話です。
エクセルとしては、「セル結合した3つのセルの一番上のセルに文字列が入っていて、あとの2つのセルは空欄」になっています。（次図D列）

そのことは、上図のG列のようにA列のセルを参照してみればわかります。

A2、A5、A8を参照している数式は文字列が返ってきています。
そのほかのすべての数式は 0 が返っています。A2、A5、A8以外は「空欄」だからです。

ということは、「セル結合した3つのセルの一番上のセルに文字列が入っている（一番上のセルが空欄ではない）」が成り立ったら、そのセルの値をそのまま表示してやればよいです。

さらに、空欄のセルについては、「セルが空欄である」が成り立ったら、1つ上の行のセルの値を表示してやればよいです。「一番上のセルが空欄ではない」が成り立ってその値が表示されているはずなので、その値が使えます。

■使っている数式について（相対参照についての理解が重要）

上記のことを数式で表すと、こうなります。
=IF(A2<>"",A2,D1)
(下図D列のセルに入っている数式です。)

ちょっと話が逸れますが、IF関数を使っている理由は、打たなければいけない引数を少なくしたかったからです。
IFS関数なら、こうなりますね。
=IFS(A2<>"",A2,A2="",D1)

このとき、重要なのは「相対参照とはどういう参照なのか」ということです。
なぜこの数式で上図D列の結果になるのかよくわからない場合、「仕事でエクセルを活用する」という観点からみて「相対参照」についての理解度が足らないといえます。

「相対参照」についての理解で大事なのは「位置関係」だといえます。
数式が入力されているセルから見て、数式内で使われているセルのアドレスがどういう位置関係にあたるかを意識できることが重要です。

たとえば、上図D2のセルに入れた数式、

=IF(A2<>"",A2,D1)

でいえば、数式が入っているセルD2と、引数に使っているA2の「位置関係」は「（D2から見て）"3つ左隣で同じ行のセル"」ということになります。

IF関数の第1引数なので、「（D2から見て）"3つ左隣で同じ行のセル" が空欄ではない」という論理式です。論理式が「真」ならば、「"3つ左隣で同じ行のセル"」 の値が返ってきます。

また、セルD1は、「（D2から見て）"同じ列で1つ上の行のセル"」ということになります。先の論理式が「偽」（空欄である）ならば、「"同じ列で1つ上の行のセル"」の値が返ります。

この数式を下に向かってコピーすれば、「（数式を入れているセルから見て、数式内で参照しているセルが）"3つ左隣で同じ行のセル" と "同じ列で1つ上の行のセル"」という「位置関係」はそのままに、A2とD1というセルのアドレスだけが変わっていきます。

その結果、「セル結合を解除して、そこに表示されていた文字列が一覧になる」状態を作ることができるわけです。

ここで「相対参照」についてよくわからなければ、私の説明の仕方が上手くないのが原因です。すみません。

とにかく、セルの参照の仕方「相対参照（絶対参照）」についての理解を深めておくことは、仕事でエクセルを使うためには必須です。
お互い励んでいきましょうね。