関数だけで結合セルを解除する
Excelは「入力」→「計算」→「出力」という流れが基本です。この中で「入力」はいわゆる"元データ"です。元データは、人間ではなく、Excelのために必要な情報ですから、データベースの形式になっていなければなりません。したがって「入力」での"セル結合"は御法度です。絶対に結合してはいけません。とはいえ、取引先や他部署からもらったデータで、セル結合されていたら…。これはもう、まずセル結合を解除しなければなりませんね。
これ、いろんな方法があります。結合を解除してオートフィルタとか、[選択オプション]で空白セルを選択するとか、Power Queryでやるとか、IF関数で埋めて値貼り付けするなど。それこそ、ググれば多くの方法が見つかります。ただ、いずれにしても何らかの"手作業"が必要です。う～ん「これ、関数だけで、できねーかなぁ」って、少し前から考えていました。もちろん、結合されているセルの個数がバラバラでも対応出来るように。関数だけでできたら、その結果をVSTACK関数やHSTACK関数で別のリストに合体できるのになぁ～みたいな。たとえば上図だったら、左図のA列から右図のA列を関数だけで作成できれば、左図の"結合されたまま"のリストで、「田中のB」や「小原のD」みたいにして、どこか別のリストから数値を持ってこられるのになぁ～みたいな。具体的な実用例はありません。何かで、いつか使えるかもしれませんけど、それよりも単純に"関数パズル"みたいで興味を持ちました。さて、以前、別のコンテンツで「関数職人の皆さんだったら、どうしますか？何なら、下記の解説を読む前にチャレンジしてみてください」って書いたら、服部さんが楽しんで考えてみました～ｗって言っていたので、今回も興味のある方はここで止めて、自分でチャレンジしてみてくださいな。私の考えた方法よりもエレガントなやり方があるかもしれませんね。サンプルは上図を参考にしてください。私の解説はCMの後で。
最初は、IF関数だけでできそうな気がしました。でも、上図の"小原"や"瀬戸"のように、3つ以上のセルが結合されているとうまくいきません。FILTER関数やUNIQUE関数なども検討してみましたけど、どうにも簡単にはできませんでした。そんなとき、ある事情で1週間ほど入院することになり[*1(下記参照)]、やることもなく、ベッドの上でボォ～っと脳内Excelを起動して遊んでいたところ、不意に思いつきました。
意外な関数を使います
さて、そろそろいいかな。使うのはSCAN関数です。SCAN関数は、LAMBDA関数と一緒に使う関数で、数値の累計を計算するときに使う、と言われています。もちろん、その使い方は知っていました。たとえば、こんな感じです。
ああ、ちなみにSCAN関数は、上図のようにLAMBDA関数が必須ではありません。LAMBDA関数のパラメータに渡すことも可能ってことです。さて、上図の計算って、いったい何をやっているのでしょう？つまりは、こういうことです。
便宜的に、上から順番に「1つめの数式」「2つめの数式」みたいに表します。さて「1つめの数式」では、初期値に設定した「0」とセル範囲に指定したA2:A5のうち、先頭のセルA2に入力されている「10」が足されて、結果は「10」になります。そして「2つめの数式」では、ひとつ前の計算結果である「10」が初期値の代わりに与えられます。この「10」とふたつめのセルA3である「20」が足されて、結果が「30」になります。「3つめの数式」では、この計算結果である「30」とセルA4の「30」が足されて「60」となり、その「60」とセルA5の「40」を足して「100」です。やってることは、上図を見ながらイメージしてください。さて、ここで重要ことは、もし計算式にLAMBDA関数を使った場合、LAMBDA関数のパラメータは次のようになるわけです。
- 1つめの数式→パラメータ「0」「10」
- 2つめの数式→パラメータ「10」「20」
- 3つめの数式→パラメータ「30」「30」
- 4つめの数式→パラメータ「60」「40」
LAMBDA関数のパラメータに注目すると、パラメータaは「直前の計算結果」であり、パラメータbは「セルに入力されている値」であることが分かります。ってことはですよ、次のような計算を行ってやれば…
もちろん、このA列というのは、セルが結合されている状態と同じなので、結合セルを次のように分解できるということです。
ベッドの上で「あっ！これできる(はず)！」と閃いたので、脳内のExcelを終了して、こっそり持ち込んだパソコンを開き、本物のExcelを起動して確認してみました。結果は大成功。じゃぁ、ってことで、列方向に結合されているバージョンや、結合セルと結合されていないセルが複数列(複数行)になっているケースなど、あれこれ夢中で確認して楽しんでいたら、見回りに来た看護師さんに「田中さん！消灯ですよ！」と怒られてしまいました。いずれにしても、悩んでいた"関数パズル"が解けたので満足です。めったにしない"入院"という特殊状況下で思いついた方法なので、このやり方を「入院メソッド」と呼ぶことに決めましたｗ
補足
(＊1)入院した病院は、21:00に消灯で6:00起床でした。ふだん、だいたい4:00頃就寝して10:00か11:00に起きるという、完全に昼夜逆転というか、約4時間ズレた生活を過ごしている私が、夜21:00に寝ろって、そりゃ無理ですよｗ 夜21:00って、私にしたら午後17:00くらいですもん。ちなみに、病院食は予想どおりの超薄味。健康を考えての食事ということは重々理解できるのですが、いかんせん、あれでは食欲も出ません。そこで、入院前にコンビニで購入して、バッグに隠して持ち込んだ桃屋の「ごはんですよ」が大活躍しました。管理栄養士のみなさま、ごめんなさい。