エクセルごはん第４回（結合したセルを関数を使って分解してみよう）

こんにちは。
エクセルごはんです。
 
第４回目になります。
今回もExcel問題を通して、楽しく脳トレしていきましょう。
 
このnoteは、もしも会社に入社するのにExcelの試験が必要だったら、もしも学校に入学するのにExcelの試験科目があったら、そんな状況を想定して、Excel問題を出題し、読者の皆様のExcel力をゴリゴリ鍛えていこうと思っています。

問題

 
今回の問題は、結合されたセルを、関数を使って分解するという問題になります。

セルB３からセルB２０に結合されたセルがあるので、それを１つのセルずつの形に分解します。
セルC３からセルC２０が今回の解答欄になります。
 
完成形のイメージは次になります。
 

結合されたセルは、左上のセルにのみ値が入っており、残りのセルは空のセルになっているのですが、これをどう処理するかが、この問題のカギです。
 
関数と配列数式を使って解いてみてください。
どうですか？出来ましたか？
 
問題を今一度おさらいしておきましょう。

結合されたセルの分解です。
セルC３からセルC２０が今回の解答欄になります。
例えば、会社Aは、４つのセル分結合されているので、分解すると、会社A、会社A、会社A、会社Aとなります。
会社Bなら、２つのセル分結合されているので、分解すると、会社B、会社Bとなります。
会社Cなら、３つのセル分結合されているので、分解すると、会社C、会社C、会社Cとなります。
会社Dなら、３つのセル分結合されているので、分解すると、会社D、会社D、会社Dとなります。
会社Eなら、４つのセル分結合されているので、分解すると、会社E、会社E、会社E、会社Eとなります。
会社Fなら、２つのセル分結合されているので、分解すると、会社F、会社Fとなります。
どうですか？配列数式を使って解けましたでしょうか？ 

解説

ここからは解説に入ります。
セルC３に、
=INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),COUNTA($B$3:B3))-2,1)
と入力して、CtrlとShiftとEnterキーを押し、配列数式にします。
セルC４からセルC２０へはオートフィル機能を使ってコピーします。
これで、結合されていた会社名が分解されて表示されました。
 
どういう仕組みになっているのか詳しく見ていきましょう。
まず、
=INDEX($B$3:$B$20,SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),COUNTA($B$3:B3))-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２０までで、空のセルじゃなければ行番号を返しています。空のセルだった場合はFALSEが返ります。
結合されたセルは、それぞれ、左上のセルにのみ値が入っており、残りのセルは空のセルになっているので、値が入っているセルの行番号３，７，９，１２，１５，１９を得ることができます。
 
この行番号３，７，９，１２，１５，１９をどう使うかですが、SMALL関数に、この３，７，９，１２，１５，１９の配列を入れてあげて、次のように順位に1を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),1)
１番目に小さい数である３が返されます。
SMALL関数は配列と順位を指定してあげることで、その配列の中で何番目に小さい値が返ります。
 
次のように順位に２を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),2)
３，７，９，１２，１５，１９の配列の中で２番目に小さい値である７が返ります。
 
次のように順位に3を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),3)
３，７，９，１２，１５，１９の配列の中で3番目に小さい値である9が返ります。
 
次のように順位に4を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),4)
３，７，９，１２，１５，１９の配列の中で4番目に小さい値である12が返ります。
 
次のように順位に5を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),5)
３，７，９，１２，１５，１９の配列の中で5番目に小さい値である15が返ります。
 
次のように順位に6を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),6)
３，７，９，１２，１５，１９の配列の中で6番目に小さい値である19が返ります。
 
つまり、
会社A（セルB３、セルB４、セルB５、セルB６）の時は、順位を１にし、
会社B（セルB７、セルB８）の時は順位を２にし、
会社C（セルB９、セルB１０、セルB１１）の時は順位を３にし、
会社D（セルB１２、セルB１３、セルB１４）の時は順位を４にし、
会社E（セルB１５、セルB１６、セルB１７、セル１８）の時は順位を５にし、会社F（セルB１９、セルB２０）の時は順位を６にしてあげれば良いわけです。
 
それを実現しているのが、SMALL関数の順位に指定した
COUNTA($B$3:B3)の箇所です。
COUNTA関数は、空白でないセルの数を数えます。結合されたセルは。左上のセルにのみに値が入っているので、残りのセルはカウントされません。このことを利用して、
会社Aなら１、会社Bなら２、会社Cなら３、会社Dなら４、会社Eなら５、会社Fなら６とすることができます。
 
あとは、
SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),COUNTA($B$3:B3))
として、SMALL関数の配列に、
IF($B$3:$B$20<>"",ROW($B$3:$B$20))
とし、順位に、
COUNTA($B$3:B3)
とすることで、会社Aなら３を、会社Bなら７を、会社Cなら９を、会社Dなら１２を、会社Eなら１５を、会社Fなら１９を得ます。
 
そして、仕上げに、INDEX関数に渡しています。行番号はINDEX関数とROW関数とでずれが生じていますので、-2しています。列番号は１ですね。
INDEX関数で$B$3:$B$20の中で指定された行番号と列番号にある値を返しています。行番号にSMALL関数が返す値に-2した、１，５，７，１０，１３，１７を得て、それぞれの会社名を表示しているわけです。
 
おつかれさまでした。
配列数式に苦手意識を持っている方は、エクセルごはんの記事の解説を読んで、配列数式に慣れていきましょう。ぐんぐん力がついていきますよ。
配列数式が得意な方は、エクセルごはんの記事の問題に挑戦してみましょう。解説とは違うやり方が思いついたり、思わぬ発見があったりするかもしれませんよ。
次回も配列数式の問題を扱いますので、配列数式使いを目指して頑張りましょう。
また、エクセルごはんおまけ回（結合したセルの扱い方）で、今回の問題の類題を取り上げていますので、良かったら覗いてみてください。
それでは。