不揃いのヘッダでも結合する

  1. Home
  2. Excel
  3. Excel Tips

これ、2025年6月のトークライブで、"Excelチャレンジ"として出題した内容です。当日は、時間の関係で、あまり詳細な解説ができませんでしたので、ここで詳しく解説します。お題は、次のような内容です。

状況を説明します。まず、テーブルが2つあります。セル範囲A1:C6が「Data1」で、セル範囲A8:C11が「Data2」です。Data1には「名前」「記号」「数値1」と3つの列があり、Data2は「記号」「名前」「数値2」です。「名前」と「記号」の列は、両方のテーブルに存在しますが、位置が異なります。そして、Data1の「数値1」はData2になく、逆にData2の「数値2」はData1にありません。この状況で2つのテーブルを縦に結合します。位置が異なるとはいえ、「名前」と「記号」は同じ列に結合できます。片方にしか存在しない列は、存在しない方を空欄にしてください。分かりますか？この処理を、1つの数式で実現するには？という問題(Excelチャレンジ)です。トークライブには毎回20名程度の"Excel大好き"な人が集まりますけど、正直に言って、できる人はいないだろうという"超難問"を出してみました。まぁ、たまにはいいかなってｗ

1.結合後の見出し(ヘッダ)を作る

さてさて、それでは順を追って詳しく解説していきましょう。まず、2つのテーブルから、結合後の見出し(ヘッダ)を作ります。

横方向に結合するのですからHSTACK関数を使います。しかし、両者に存在する「名前」と「記号」がダブっています。なので、UNIQUE関数で"重複しない"見出し(ヘッダ)にします。

2.見出し(ヘッダ)に該当する列を抽出する

見出し(ヘッダ)は作成できたとします。次に「名前」に該当する列全体を抽出します。今回はXLOOKUP関数を使います。列を抽出するのならCHOOSECOLS関数をイメージする方が多いでしょう。しかし、このように列の"見出し"が分かっているときはXLOOKUP関数が便利です。このへんの「列の抽出」に関しては、下記のページを参照してください。

列を抽出するいくつかの方法

あと、XLOOKUP関数を使う理由は、もうひとつあるのですが、それは後述します。

テーブルData2からの抽出も、同じように考えられます。

この2つを縦に結合するのですから、ここはVSTACK関数ですね。

「記号」列も同じです。

では、同じ発想で「数値1」列も処理してみましょう。結果は下図のとおりエラーです。

エラーの原因を探るため、2つのXLOOKUP関数を別々に実行してみましょう。

2つめのXLOOKUP関数がエラーになりました。そりゃそうです。テーブルData2には「数値1」という見出し(ヘッダ)は存在しないのですから。しかし、確かXLOOKUP関数には「エラーになったときに返す」モノを指定する引数がありました。

では、エラーになったとき、今回は何を返してほしいのでしょう。それは、3個のブランク(空欄)セルです。3個というのは、実際には「Data2の行数」なのですが、そこは後で考えるとして。指定した個数のブランクセル(からなる配列)を作るのには、どうしたらいいでしょう。ここが、この問題における最大の難関です。

3.EXPAND関数

ここで使うのがEXPAND関数です。おそらく、ほとんどの方はご存じない関数でしょう。まずは、EXPAND関数の基本から。EXPAND関数は、指定したセル範囲(配列)を拡張する関数です。

上図は、セル範囲A1:B3(3行×2列)を5行×3列に拡張したところです。拡張された4行目と5行目、3列目には値がありませんので、#N/Aエラーが表示されています。拡張した部分に、エラー値ではなく別のものを表示したいときは、EXPAND関数の4番目の引数に指定できます。

ここで指定したセル範囲A1:B3は3行×2列でした。次は、セル範囲A1:A3(3行×1列)に対して拡張してみましょう。

上図のように、EXPAND関数で引数「列」を省略すると、元のセル範囲(あるいは配列)と同じ列数とみなされます。ここまでは、ご理解いただけましたか？でも、まだEXPAND関数の素晴らしさは分かりませんよね。そう、話はここからです。では、元の範囲に1つのセルを指定してみましょう。

1つのセルだって、1行×1列の大きさを持っています。それを5行×1列に拡張しました。もともと値が入っていたのは1つだけですから、それ以外の4セルには"xxx"が表示されます。では、この"xxx"部分にセルA1を指定してみます。

さらに、EXPAND関数の最初の引数「元の範囲」というのは、実は文字列も指定できます。

最後に、この"田中"を空欄("")にしたら

5つの空欄セル(ブランクセル)からなるセル範囲(配列)を作ることができました。

したがって、「3個のブランクセル」はEXPAND関数で作成できます。上記の「？？？」には「EXPAND("",3,,"")」を指定してやればいいことになります。

もちろん、Data1に対するXLOOKUP関数でも同様です。冒頭で、列を抽出するのに今回は「XLOOKUP関数を使うのがいい」と書いた理由はこれです。CHOOSECOLS関数やTAKE関数と違ってXLOOKUP関数は「エラーになったときの処理」を引数で指定できるからです。

この考え方を、すべての列で行えば、存在しない列は空欄("")セルとして扱えます。

4.まとめ

=LET(Header,UNIQUE(HSTACK(Data1[#見出し],Data2[#見出し]),TRUE),
     Data,DROP(REDUCE("",Header,LAMBDA(a,b,HSTACK(a,VSTACK(
         XLOOKUP(b,Data1[#見出し],Data1,EXPAND("",ROWS(Data1),,"")),
         XLOOKUP(b,Data2[#見出し],Data2,EXPAND("",ROWS(Data2),,"")))))),,1),
     VSTACK(Header,Data))

解説が長くなったので、最終的な数式にしました。決して、解説が面倒くさくなったからではありません。ザックリと解説します。冒頭の「UNIQUE(HSTACK(Data1[#見出し],Data2[#見出し]),TRUE)」で、重複しない見出し(ヘッダ)を作成しています。LET関数を使って、その結果を変数「Hrader」に入れました。次の

     Data,DROP(REDUCE("",Header,LAMBDA(a,b,HSTACK(a,VSTACK(
         XLOOKUP(b,Data1[#見出し],Data1,EXPAND("",ROWS(Data1),,"")),
         XLOOKUP(b,Data2[#見出し],Data2,EXPAND("",ROWS(Data2),,"")))))),,1),

は、実際には1行です。やっていることは、

DROP(REDUCE("",Header,LAMBDA(a,b,HSTACK(a,VSTACK(Data1へのXLOOKUP,Data2へのXLOOKUP)))))),,1),

です。この「DROP(REDUCE("",値,LAMBDA(a,b,処理)),,1)」は鉄板です。動作の詳しい解説は、下記のページをご覧ください。

1つの数式で縦横にスピらせる方法

解説は以上です。本問題は、実務で役立つテクニックというより、"関数パズル"みたいなものです。特にEXPAND関数で「同じ値(今回は空欄)の複数セル(配列)」を作るやり方は、かなりマニアックですが、EXPAND関数が唯一活躍できる場面かと。頭の片隅にでも、置いておいてくださいな。

(余談)

最近のExcelには「この数式を説明してください」という機能が追加されています。

AI(Copilot)が、数式の意味を詳しく解説してくれる便利機能なのですが、今回作成した数式を説明してもらいました。そしたらなんと！いくつかダメ出しされましたｗ

・[#見出し] という構文の誤り
Excelテーブルのヘッダー参照は [#Headers]（英語環境）または [#見出し]（日本語環境）ですが、英語関数名と日本語構文が混在しています。
例： Data1[#見出し] → 英語関数なら Data1[#Headers]、日本語関数なら Data1[#見出し]。

はぁ？何言ってんのかと。日本語版Excelの構造化参照では、[#Headers]みたいな英語表記は使えません。LET関数で入れている変数名がHeaderなので、混在してるって判断したんですかね？ぜんぜん、まったく見当違いです。

・XLOOKUPの使い方の誤り
XLOOKUP(b,Data1[#見出し],Data1,EXPAND("",row1,,""))→ XLOOKUPの第3引数（返す範囲）にテーブル全体 Data1 を指定していますが、これは列範囲でなければなりません。
また、bは列名（ヘッダー）ですが、Data1[#見出し]は列名のリスト、Data1はテーブル全体なので、列名で列全体を返すには Data1[列名] のようにする必要があります。

できるし！正しく動作するし！間違ってないし！なに言っちゃってくれてんの？Excelの基礎から勉強し直した方がいいんじゃない？何なら私のセミナー受けますか？ちなみに「XLOOKUPの使い方の誤り」のように同じ助詞が続くのは日本語としてのクオリティが低いです。可読性が劣り、意味が理解しにくくなります。百歩譲って「XLOOKUPの使い方が誤り」とか「XLOOKUPの使い方に誤り」とか何なら「XLOOKUPの使い方が誤っている」みたいに書くことをお勧めします。日本語なめんなよ！

・REDUCEの初期値が空文字列
REDUCE("",Header,...) で初期値が空文字列 "" ですが、配列を積み重ねる場合は {}（空配列）を使うのが一般的です。

冗談は適度な休憩を挟んでから言っていただけますか。REDUCE関数の初期値に「REDUCE({},…」みたいに空配列を指定するとエラーになります。空配列は指定できないんですよ。それって、あなたの感想ですよね？ヘルプ読んでます？

・EXPAND関数の使い方
EXPAND("",ROWS(Data1),,"") のように使っていますが、空文字列を拡張しても意味がありません。空白で埋めたい場合は {""} などを使うべきです。

寝言は就寝してからおっしゃっていただきたいです。意味がないどころか、今回の数式において、そこが最大のポイントです。EXPAND関数以外の方法で「空欄の配列」を作るとなったら、メチャクチャ大変なことになります。実際に数式を作った経験ないんですかね？あ、AIだからないのかｗ

ということで「修正版の例」として提案してきたのが次の数式です。

=LET(
  Headers, UNIQUE(HSTACK(Data1[#見出し], Data2[#見出し])),
  Data1Cols, CHOOSECOLS(Data1, XMATCH(Headers, Data1[#見出し], 0, 0)),
  Data2Cols, CHOOSECOLS(Data2, XMATCH(Headers, Data2[#見出し], 0, 0)),
  Data, VSTACK(Data1Cols, Data2Cols),
  VSTACK(Headers, Data)
)

はい、ダウト！XMATCH関数だと、ヘッダが見つからないとエラーになります。見つからなかったときに空欄のセル範囲を返す処理がありません。一応、この数式をセルに入れてみましたけど、もちろんエラーでした。さらに、別の修正例も提示してくれました。

=LET(
  Headers, UNIQUE(HSTACK(Data1[#見出し], Data2[#見出し])),
  Data1Cols, MAP(Headers, LAMBDA(h, IFERROR(Data1[[#すべて],[h]], SEQUENCE(ROWS(Data1), 1, "")))),
  Data2Cols, MAP(Headers, LAMBDA(h, IFERROR(Data2[[#すべて],[h]], SEQUENCE(ROWS(Data2), 1, "")))),
  Data, VSTACK(Data1Cols, Data2Cols),
  VSTACK(Headers, Data)
)

SEQUENCE(ROWS(Data1), 1, "")って何だよ？SEQUENCE関数の第3引数って、作成する配列の初期値つまり数値を指定するとこじゃん。初期値に空欄("")を指定したら空欄("")だけの配列を返してくれるんですか？何ですか？裏技ですか？思わず試しちゃいましたよ。もちろんエラーでしたけど。

いやぁ～相手がAIなので、思わず乱暴な口調になってしまいました。失礼いたしました。まぁ、現在のAIって、この程度のレベルなんだなと。あらためて実感しました。もっとがんばりましょうね＞AI