エクセルごはんおまけ回（結合したセルの扱い方）

こんにちは。
エクセルごはんです。
 
今回はおまけ回です。
問題を出題するのではなく、説明を行います。
エクセルごはん第４回で取り上げた結合したセルの扱い方について、知識を整理しておきましょう。エクセルごはん第４回では結合したセルを１つずつのセルに分解する方法を取り上げましたが、今回は、その類題を取り上げながら、結合したセルの扱い方を学んでいきます。
 
まず、結合したセルは、左上のセルにのみ値が入っています。残りのセルは空のセルになります。
なので、普通に値のコピーでコピーをすると、下図のように空白のセルが生まれてしまいます。

これを、下図のように空白のない形でコピーしたい時があると思います。

そんな時、どうするかというのが今回のテーマです。
コピーした値を並び替えても良いのですが、ここでは、関数を使って空白のセルを詰めてみましょう。
 
エクセルごはん第４回を読んでいる方は方法が分かるのではないでしょうか？
今回も配列数式の出番となります。
 
セルC３に、
=IFERROR(INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),ROW(A1))-2,1),"")
と入力し、CtrlとShiftとEnterキーを押して、セルC２０まで、オートフィル機能でコピーします。
これで、空白のないセルの出来上がりです。
 
どういう仕組みになっているのか詳しく見ていきましょう。
まず、
=IFERROR(INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),ROW(A1))-2,1),"")
太字の箇所を見ていきましょう。
$B$3:$B$20<>""
ですが、この配列数式の返す値は、
TRUE　FALSE　FALSE　FALSE　
TRUE　FALSE　
TRUE　FALSE　FALSE　
TRUE　FALSE　FALSE　
TRUE　FALSE　FALSE　FALSE　
TRUE　FALSE
になります。
結合されたセルは、それぞれ左上のセルにのみ値が入っており、残りのセルは空のセルになっているので、このような結果になるわけです。
 
次に、
=INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),COUNTA($B$3:B3))-2,1)
太字の箇所を見ていきます。
ROW($B$3:$B$20)
ですが、IF関数と組み合わせていない、これ単体では、この配列数式の返す値は、
3　4　5　6　7　8　9　10　11　12　13　14　15　16　17　18　19　20
になります。
ROW関数は行番号を返す関数になります。
 
次に、
=INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),COUNTA($B$3:B3))-2,1)
太字の箇所を見ていきます。
IF($B$3:$B$20<>"",ROW($B$3:$B$20))
ですが、この配列数式の返す値は、
3　FALSE　FALSE　FALSE　
7　FALSE　
9　FALSE　FALSE　
12　FALSE　FALSE　
15　FALSE　FALSE　FALSE　
19　FALSE
になります。
セルB３からセルB２０までで、空のセルじゃなければ行番号を返しています。
 
ここまでの解説はエクセルごはん第４回と同じですね。
ここからの処理が少し違ってきます。
SMALL関数に渡す順位が違います。
 
=IFERROR(INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),ROW(A1))-2,1),"")
太字の箇所を見ます。
SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),ROW(A1))
ですが、順位にROW(A1)を指定しています。ここは相対参照になっているため、セルが下に動く度にROW関数の中のA1もA2、A3…と動いていきます。つまり、行番号１，２，３…を得られるわけです。
 
そして、SMALL関数の配列に指定した、
IF($B$3:$B$20<>"",ROW($B$3:$B$20))
によって、結合したセルの左上の値の入っているセルの行番号、３，７，９，１２，１５，１９と組み合わせることで、３，７，９，１２，１５，１９を順番に取得しているわけです。
 
そして、仕上げに、INDEX関数に渡しています。行番号はINDEX関数とROW関数とでずれが生じていますので、-2しています。列番号は１ですね。
INDEX関数で$B$3:$B$20の中で指定された行番号と列番号にある値を返しています。行番号にSMALL関数が返す値に-2した、１，５，７，１０，１３，１７を得て、それぞれの会社名を表示しているわけです。
 
そして、最後に、IFERROR関数でエラー対策を行っています。
このままでは、SMALL関数に渡した順位ROW（A1）が会社の数６を超えて表示しようとしてエラー値が返ってくるため、エラー値が返された時は、空白にするという処理をしています。
 
おつかれさまでした。
これで、エクセルごはん第４回と合わせて、結合したセルの扱い方が理解できたのではないでしょうか。
それでは。