Googleスプレッドシートでテーブル結合

Googleスプレッドシートには QUERY や VLOOKUP 、 ARRAYFORMULA などの高度な関数があります。これらを使えば様々なデータ操作が可能です。しかし SQL の LEFT JOIN や INNER JOIN に相当するようなテーブル結合の操作はできません。そこで、SQL のようなテーブル結合が簡単にできる名前付き関数を作ってみました。

作成した名前付き関数は次の9つです:

内部結合: INNER_JOIN, INNER_JOIN_BY_KEYS
左結合: LEFT_JOIN, LEFT_JOIN_BY_KEYS
右結合: RIGHT_JOIN, RIGHT_JOIN_BY_KEYS
完全結合: FULL_JOIN, FULL_JOIN_BY_KEYS
クロス結合: CROSS_JOIN

これらはこちらのスプレッドシートで公開しています: テーブル結合用の名前付き関数 - Google Sheets

使い方

名前付き関数は他のスプレッドシートにインポートして使うことができます。

シートを開いて [データ] > [名前付き関数] > [関数をインポート] をクリックし、読み込み元としてテーブル結合用の名前付き関数 - Google Sheets を選択してインポートを実行してください。

例: 単純な左結合

名前付き関数 LEFT_JOIN_BY_KEYS を使えば、2つの範囲と結合キーを指定して単純な左結合ができます。

例として次のような顧客データと注文データを考えます。データが入っている部分（青太枠）はそれぞれ「顧客」および「注文」という名前付き範囲になっています（列名の行は名前付き範囲に含まれません）。

顧客データに注文データを左結合するには、適当なセルに次の数式を入力します:

=LEFT_JOIN_BY_KEYS(顧客, 注文, 1, 2)

ここで

第1引数の 顧客 と第2引数の 注文 は結合したい２つの範囲を表しています。
第3引数の 1 と第4引数の 2 は、それぞれ 顧客 の1列目と 注文 の2列目を結合キーとして使うことを表しています。

結果は以下のようになります（数式が入力されているのは赤二重枠のセルです）:

この結果から次のような左結合の特徴が確認できます:

顧客 と 注文 とで顧客IDが一致する行の組み合わせが並んでいます。
顧客ID = 4 の行は 顧客 には存在しますが 注文 に存在しません。これに対して結合結果には 注文 側を空白として1行のデータが残っています。

なおここでは説明がわかりやすいように名前付き範囲を使いましたが、名前付き範囲を使わず普通に B23:D27 のような形で範囲を指定することも可能です。

また LEFT_JOIN_BY_KEYS の他にも RIGHT_JOIN_BY_KEYS、INNER_JOIN_BY_KEYS、FULL_JOIN_BY_KEYS が定義されています。それぞれ右結合、内部結合、完全結合に対応しています。使い方は同じで、２つの範囲と結合キーの列番号を指定します。

例: 複数キーによる結合

名前付き関数 LEFT_JOIN_BY_KEYS の代わりに LEFT_JOIN を使えばより複雑な結合条件を指定することもできます。

例として下のようなデータを考えます。青太枠で囲われた部分はそれぞれ「家計」と「人口」という名前付き範囲になっています（列名のセルは名前付き範囲に含まれません）。

『２つの列「地域」と「年」の両方が一致する』 という条件でこれらの表を左結合したいとします。

これは次の数式で実現できます:

=LEFT_JOIN(
    家計,
    人口,
    LAMBDA(
        x,
        y,
        AND(ARRAYFORMULA(
            CHOOSECOLS(x, 1, 2) = CHOOSECOLS(y, 1, 2)
        ))
    )
)

結果は以下のようになります（数式は赤二重枠のセルに入っています）:

上の数式で、LEFT_JOIN 関数は次の形で呼び出されています:

LEFT_JOIN(家計, 人口, 結合条件)

第1引数の 家計 と第2引数の 人口 は結合したい２つの範囲です。

結合条件 は次の形で与えられています:

LAMBDA(x, y, 条件式)

LEFT_JOIN 関数では結合条件を表すために LAMBDA関数を使います。 LAMBDAの第1引数と第2引数に与えられている x と y は、次の 条件式 で使われる変数名を定義しているだけです。

条件式 は次のようになっています:

AND(ARRAYFORMULA(
    CHOOSECOLS(x, 1, 2) = CHOOSECOLS(y, 1, 2)
))

x には 家計 の各行が、y には 人口 の各行がそれぞれ入り、すべての行の組み合わせに対して式が評価されます。

CHOOSECOLS関数は配列から列を取り出す関数です。例えば CHOOSECOLS(x, 1, 2) は家計の行 x から1列目と2列目を取り出すことを意味しています。
ARRAYFORMULA関数は複数のセルを同時に比較するために使用しています。
最後に AND関数を適用することで1列目と2列のセルが両方等しいという条件を表しています。

条件式 は ARRAYFORMULA を使わず次のように書いても同じ結果になります:

AND(
    CHOOSECOLS(x, 1) = CHOOSECOLS(y, 1),
    CHOOSECOLS(x, 2) = CHOOSECOLS(y, 2)
)

LEFT_JOIN の他にも RIGHT_JOIN、INNER_JOIN、FULL_JOIN が定義されています。それぞれ右結合、内部結合、完全結合に対応しています。２つの範囲と結合条件をLAMBDA関数で指定する使い方は同じです。

例: 不等式の結合条件

次のような２つのデータ「カレンダー」と「予定」があるとします。青太枠の部分は名前付き範囲になっています（列名の行は名前付き範囲に含まれません）。

カレンダーに予定を左結合することを考えます。このとき 『カレンダーの日付が予定の開始日から終了日までの範囲内にある』 という条件でマッチするようにしたいとします。これを表す数式は次のようになります:

=LEFT_JOIN(
    カレンダー,
    予定,
    LAMBDA(
        x,
        y,
        ISBETWEEN(
            CHOOSECOLS(x, 1),
            CHOOSECOLS(y, 3),
            CHOOSECOLS(y, 4)
        )
    )
)

結果は以下のようになります（数式は赤二重枠のセルに入っています）:

上の数式中で、結合条件はLAMBDA関数で次のように表されています

LAMBDA(x, y, 条件式)

x と y は次の 条件式 中で使われる変数名を定義しています。 条件式 は次のようになっています:

ISBETWEEN(
    CHOOSECOLS(x, 1),
    CHOOSECOLS(y, 3),
    CHOOSECOLS(y, 4)
)

変数 x と y にはそれぞれ カレンダー と 予定 の各行が入り、すべての行の組み合わせに対して式が評価されます。

CHOOSECOLS関数は配列から列を取り出す関数です。
ISBETWEEN はある値がある範囲内にあるかどうかを判定する関数です。
これらを組み合わせて「カレンダーの1列目が予定の3列目から4列目の間の範囲内にある」という条件を表しています。

ISBETWEEN を使う代わりに次のように条件式を書いても同じ結果になります:

AND(
    CHOOSECOLS(x, 1) >= CHOOSECOLS(y, 3),
    CHOOSECOLS(x, 1) <= CHOOSECOLS(y, 4)
)

まとめ

GoogleスプレッドシートでSQLのような結合操作ができる名前付き関数を作りました。こちらのシートから好きなスプレッドシートへインポートして利用できます。
名前付き関数 LEFT_JOIN_BY_KEYS でシンプルな左結合ができます。
名前付き関数 LEFT_JOIN で複雑な条件の左結合ができます。結合条件はLAMBDA関数を使って指定します。
右結合、内部結合、完全結合に対応した名前付き関数も同様に定義されています。

使い方

名前付き関数のインポート

例: 単純な左結合

例: 複数キーによる結合

例: 不等式の結合条件

まとめ