エニプラ Advent Calendar 2025

Excel関数のみでデータの内部結合を実現する方法

Last updated at 2025-12-11Posted at 2025-12-11

はじめに

本記事では、Excel関数のみでデータの内部結合を実現する方法をご紹介します。

テーブルの構造がきれいな場合は各レコードに対して1対1でデータを結合させるため、XLOOKUP関数のみで対応可能です。
しかし1対多の関係のような、1つのレコードの結合先に複数の一致レコードが存在する場合、上記の方法では対応が難しくなります。

今回はこのような複雑なケースでも、内部結合を実現する方法を紹介します。

行複製処理

事前準備として、内部結合を行う上で必要となる「行複製処理」について説明します。

概要

行複製処理は、以下の画像のような処理を行うものです。

入力テーブルの各列のデータを、出力テーブル上に"表示行数"の数ずつ表示します。

データ"aaa"は表示行数が3であるため、"aaa"は3行表示されています
データ"ccc"は表示行数が1であるため、"ccc"は1行表示されています
データ"ddd"は表示行数が0であるため、この場合は出力データに"ddd"は表示されません

作成方法

下記のようなシートを作成します。

データの処理の動きが分かりやすいよう、
今回は入力テーブルに仮データを入れたうえで作成していきます。

今回出力したいデータは次のようなものでした。(赤字の値は参考用です)

入力データと出力データの対応が分かりやすいよう、項番を付与します。
F列に1からインクリメントして入力します。

ここで対応を見てみると、

データ"aaa"は表示行数が3なので、項番1,2,3 に表示
データ"bbb"は表示行数が4なので、項番4,5,6,7 に表示
データ"ccc"は表示行数が1なので、項番8 に表示
データ"ddd"は表示行数が0なので、表示はなし
データ"eee"は表示行数が2なので、項番9,10 に表示

となっています。
各データが表示されている項番の最大数は、

データ"aaa"は3
データ"bbb"は7
データ"ccc"は8
データ"eee"は10

となります。
この最大数を累計行数列に表示します。

D4セルに以下の値を入力します

D4セル

=SUM(C$4:C4)

入力した数式をD4セルの下側のD列にオートフィル、または数式貼り付けします。

最後に、出力テーブルにデータを出力します

G4セルに以下の値を入力します

G4セル

=XLOOKUP(F4,D$4:D$16,B$4:B$16,"",1,1)

上記で16と入れている箇所については、値を自由に調節していただいて構いません。
ただし、D列の累計行数の数式を入力した範囲を超えないようにしてください。

入力した数式をG4セルの下側のG列にオートフィル、または数式貼り付けします。

出力テーブルに欲しかったデータが出力されています。
処理の作成は以上で完成です。

ここで最後の数式について説明をしたいと思います。
例としてG9セルの場合を考えます

G9セル

=XLOOKUP(F9,D$4:D$16,B$4:B$16,"",1,1)

項番が6なので、項番4, 5, 6, 7 のデータ"bbb"を表示するため、B5セルを参照したいです。
そこで、第1引数の F9=6 を第2引数の D$4:D$16 で探すのですが、範囲内に6はありません。
しかし、第5引数を 1 にしているため、6がない場合は次に大きい7を探しにいきます。
これはD5セルにあるため、第3引数の B$4:B$16 内で対応するB5セルが参照できます。

B5セルのデータ"bbb"を表示したいのは、項番が4, 5, 6, 7 である G7, G8, G9, G10セルです。
XLOOKUPで検索をかけた際、第2引数の中でここに紐づくのはD5セルの7ですが、これは項番4, 5, 6, 7の最大値として与えられていました。
そのため、第2引数の範囲に4, 5, 6, 7で検索をかけ、見つからない場合は次に大きい数を探すという動きでD5セルの7を対応付けられるわけです。

なお項番11以降が空欄であるのは第4引数が "" であるため、
項番8のG11セルに "ddd" ではなく "ccc" が表示されるのは第6引数が 1 であるためです。
これらの詳細な説明は割愛しますが、気になる方はぜひ考えてみてください。

内部結合

先ほど紹介した行複製処理を用いて、データの内部結合を行う方法を紹介します。

概要

次のような状況を考えます。

2つのテーブル table_a , table_b に対して、
column_a0 と column_b0 で内部結合した上で、列名を column_k0 に置き換えています。

SQLではこのようになります。

INNER JOIN

SELECT table_a.column_a0 AS column_k0, table_a.column_a1, table_b.column_b1
FROM table_a
INNER JOIN table_b
ON table_a.column_a0 = table_b.column_b0;

実行結果は以下の通りです。

上記をExcel関数のみで実現します。

作成方法

まず、下記の表を作成します。

・入力部分

・処理部分

・出力部分

次に、B,C列の値を I,J列に、E,F列の値を N,O列に、
S,T,U列の値を X,Y,Z列に参照させます。

具体的には、I4セルに =B4 と入力してからそれをJ4セルにオートフィルし、
その後 I4,J4セルの数式を I,J列の下の方まで再度オートフィルします。
これでB,C列の値が I,J列に参照されます。

N,O列、X,Y,Z列で行う作業も E,F列と同様です。

また、K4, L4, P4, S4, T4, U4セルにそれぞれ以下の値を入力します:

K4セル

=COUNTIF($E$4:$E$[end_row],I4)

L4セル

=SUM(K$4:K4)

P4セル

=COUNTIF(N$4:N4,N4)

S4セル

=XLOOKUP(R4,L$4:L$[end_row],I$4:I$[end_row],"",1,1)

T4セル

=XLOOKUP(R4,L$4:L$[end_row],J$4:J$[end_row],"",1,1)

U4セル

=LET(key,XLOOKUP(R4,L$4:L$[end_row],I$4:I$[end_row],"",1,1),
  num,R4-XLOOKUP(R4-1,L$4:L$[end_row],L$4:L$[end_row],0,-1,1),
  XLOOKUP(key&num,N$4:N$[end_row]&P$4:P$[end_row],O$4:O$[end_row],""))

※数式内の [end_row] の部分は作成した表の最下行の行数を入力してください。

入力したら、K4, L4, P4, S4, T4, U4セルの数式をそれぞれ表の最下行までオートフィルします。

最後に、R列に1から順に値をインクリメントして入力します。
(R4セルに1を入力し、R4セルから最下行までをCtrlを押しながらオートフィルします。)

このようになっていれば完成です。

・入力部分