![見出し画像](https://assets.st-note.com/production/uploads/images/101098144/rectangle_large_type_2_7419374b5dfc8af52fa274b9355668ce.png?width=1200)
エクセルごはん第4回(結合したセルを関数を使って分解してみよう)
こんにちは。
エクセルごはんです。
第4回目になります。
今回もExcel問題を通して、楽しく脳トレしていきましょう。
このnoteは、もしも会社に入社するのにExcelの試験が必要だったら、もしも学校に入学するのにExcelの試験科目があったら、そんな状況を想定して、Excel問題を出題し、読者の皆様のExcel力をゴリゴリ鍛えていこうと思っています。
問題
今回の問題は、結合されたセルを、関数を使って分解するという問題になります。
![](https://assets.st-note.com/img/1679698777605-EuXdCAWbIz.png)
セルB3からセルB20に結合されたセルがあるので、それを1つのセルずつの形に分解します。
セルC3からセルC20が今回の解答欄になります。
完成形のイメージは次になります。
![](https://assets.st-note.com/img/1679698795426-Y7cUYorJhI.png)
結合されたセルは、左上のセルにのみ値が入っており、残りのセルは空のセルになっているのですが、これをどう処理するかが、この問題のカギです。
関数と配列数式を使って解いてみてください。
どうですか?出来ましたか?
問題を今一度おさらいしておきましょう。
![](https://assets.st-note.com/img/1679718973036-RMjCaLcnac.png)
結合されたセルの分解です。
セルC3からセルC20が今回の解答欄になります。
例えば、会社Aは、4つのセル分結合されているので、分解すると、会社A、会社A、会社A、会社Aとなります。
会社Bなら、2つのセル分結合されているので、分解すると、会社B、会社Bとなります。
会社Cなら、3つのセル分結合されているので、分解すると、会社C、会社C、会社Cとなります。
会社Dなら、3つのセル分結合されているので、分解すると、会社D、会社D、会社Dとなります。
会社Eなら、4つのセル分結合されているので、分解すると、会社E、会社E、会社E、会社Eとなります。
会社Fなら、2つのセル分結合されているので、分解すると、会社F、会社Fとなります。
どうですか?配列数式を使って解けましたでしょうか?
解説
ここからは解説に入ります。
セルC3に、
=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キーを押し、配列数式にします。
セルC4からセルC20へはオートフィル機能を使ってコピーします。
これで、結合されていた会社名が分解されて表示されました。
どういう仕組みになっているのか詳しく見ていきましょう。
まず、
=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
になります。
セルB3からセルB20までで、空のセルじゃなければ行番号を返しています。空のセルだった場合はFALSEが返ります。
結合されたセルは、それぞれ、左上のセルにのみ値が入っており、残りのセルは空のセルになっているので、値が入っているセルの行番号3,7,9,12,15,19を得ることができます。
この行番号3,7,9,12,15,19をどう使うかですが、SMALL関数に、この3,7,9,12,15,19の配列を入れてあげて、次のように順位に1を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),1)
1番目に小さい数である3が返されます。
SMALL関数は配列と順位を指定してあげることで、その配列の中で何番目に小さい値が返ります。
次のように順位に2を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),2)
3,7,9,12,15,19の配列の中で2番目に小さい値である7が返ります。
次のように順位に3を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),3)
3,7,9,12,15,19の配列の中で3番目に小さい値である9が返ります。
次のように順位に4を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),4)
3,7,9,12,15,19の配列の中で4番目に小さい値である12が返ります。
次のように順位に5を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),5)
3,7,9,12,15,19の配列の中で5番目に小さい値である15が返ります。
次のように順位に6を指定すれば、
=SMALL(IF($B$3:$B$20<>"",ROW($B$3:$B$20)),6)
3,7,9,12,15,19の配列の中で6番目に小さい値である19が返ります。
つまり、
会社A(セルB3、セルB4、セルB5、セルB6)の時は、順位を1にし、
会社B(セルB7、セルB8)の時は順位を2にし、
会社C(セルB9、セルB10、セルB11)の時は順位を3にし、
会社D(セルB12、セルB13、セルB14)の時は順位を4にし、
会社E(セルB15、セルB16、セルB17、セル18)の時は順位を5にし、会社F(セルB19、セルB20)の時は順位を6にしてあげれば良いわけです。
それを実現しているのが、SMALL関数の順位に指定した
COUNTA($B$3:B3)の箇所です。
COUNTA関数は、空白でないセルの数を数えます。結合されたセルは。左上のセルにのみに値が入っているので、残りのセルはカウントされません。このことを利用して、
会社Aなら1、会社Bなら2、会社Cなら3、会社Dなら4、会社Eなら5、会社Fなら6とすることができます。
あとは、
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なら3を、会社Bなら7を、会社Cなら9を、会社Dなら12を、会社Eなら15を、会社Fなら19を得ます。
そして、仕上げに、INDEX関数に渡しています。行番号はINDEX関数とROW関数とでずれが生じていますので、-2しています。列番号は1ですね。
INDEX関数で$B$3:$B$20の中で指定された行番号と列番号にある値を返しています。行番号にSMALL関数が返す値に-2した、1,5,7,10,13,17を得て、それぞれの会社名を表示しているわけです。
おつかれさまでした。
配列数式に苦手意識を持っている方は、エクセルごはんの記事の解説を読んで、配列数式に慣れていきましょう。ぐんぐん力がついていきますよ。
配列数式が得意な方は、エクセルごはんの記事の問題に挑戦してみましょう。解説とは違うやり方が思いついたり、思わぬ発見があったりするかもしれませんよ。
次回も配列数式の問題を扱いますので、配列数式使いを目指して頑張りましょう。
また、エクセルごはんおまけ回(結合したセルの扱い方)で、今回の問題の類題を取り上げていますので、良かったら覗いてみてください。
それでは。
この記事が気に入ったらサポートをしてみませんか?