スプレッドシートで2つの表をキーを揃えて表示させたい

Question

前提・実現したいこと

スプレッドシートにて、
[表1][表2]から、キー番号を揃えて左右に表示させたいと考えています。

[表１]

キー番号	氏名	電話番号
001	Aさん	090-1234-1111
002	Bさん	090-1234-3333
002	Bさん	090-1234-4444

[表２]

キー番号	氏名	電話番号
001	Aさん	090-1234-1111
001	Aさん	090-1234-2222
002	Bさん	090-1234-3333

【完成形】
[表１] 　　　　　　 [表２]

キー番号	氏名	電話番号	キー番号	氏名	電話番号
001	Aさん	090-1234-1111	001	Aさん	090-1234-1111
		001	Aさん	090-1234-2222
002	Bさん	090-1234-3333	002	Bさん	090-1234-3333
002	Bさん	090-1234-4444

試したこと

A2セルとD2セルに、query関数を使用して、全量取得した結果を左右に並べると、下記のように詰まったように表示されてしまいます。

[表１] 　　　　　　 [表２]

A列	B列	C列	D列	E列	F列
キー番号	氏名	電話番号	キー番号	氏名	電話番号
001	Aさん	090-1234-1111	001	Aさん	090-1234-1111
002	Bさん	090-1234-3333	001	Aさん	090-1234-2222
002	Bさん	090-1234-4444	002	Bさん	090-1234-3333

調べたところ、スプレッドシートのquery関数には外部結合のような機能がないとのことで、
代替となる方法をご存じの方がいらっしゃいましたらご教示いただけますと幸いです。

Comments

[sawa]

お手軽な代替はないので、GASで自作関数作るか、通常のシート関数を組み合わせて長い式作って出力させるかですかね。

[s_m]

やはりそうですよね…
ご回答いただき、ありがとうございました！

Answer 1

コメントに書いた通りなんですが、とりあえず作ってみました。
シート関数で出来るとは言いましたが、複雑すぎて汎用性ないしやるもんじゃないですね。

タイトル行を除いて 表1のデータが A2:C20、表2のデータが E2:G20 にあるとして、

---

Googleスプレッドシート関数組み合わせの場合

=ARRAYFORMULA({IFERROR(VLOOKUP(QUERY(UNIQUE({A2:A20&"_"&COUNTIFS(A2:A20,A2:A20,ROW(A2:A20),"<="&ROW(A2:A20));E2:E20&"_"&COUNTIFS(E2:E20,E2:E20,ROW(E2:E20),"<="&ROW(E2:E20))}),"where Col1 <>'_0' order by Col1 asc"),{A2:A20&"_"&COUNTIFS(A2:A20,A2:A20,ROW(A2:A20),"<="&ROW(A2:A20)),A2:C20},{2,3,4},FALSE)),(IFERROR(VLOOKUP(QUERY(UNIQUE({A2:A20&"_"&COUNTIFS(A2:A20,A2:A20,ROW(A2:A20),"<="&ROW(A2:A20));E2:E20&"_"&COUNTIFS(E2:E20,E2:E20,ROW(E2:E20),"<="&ROW(E2:E20))}),"where Col1 <>'_0' order by Col1 asc"),{E2:E20&"_"&COUNTIFS(E2:E20,E2:E20,ROW(E2:E20),"<="&ROW(E2:E20)),E2:G20},{2,3,4},FALSE)))})

---

GASで自作関数作る場合

function QUERYJOIN(x,y){
  const keys = [...new Set([...x.map(v => v[0]),...y.map(v => v[0])])].sort().filter(v=>v);

  //空欄埋める用
  const xNullArr = x[0].map(v => "");
  const yNullArr = y[0].map(v => "");

  const result = keys.reduce((pv,key) => {
    const xMatchArr =  x.filter(row => row[0] === key);
    const yMatchArr =  y.filter(row => row[0] === key);

    let i =0;
    while(xMatchArr[i] || yMatchArr[i]){
      const xMatch = xMatchArr[i] || xNullArr;
      const yMatch = yMatchArr[i] || yNullArr;
      pv.push([...xMatch,...yMatch]);
      i++
    }
    return pv;
  },[])

  return result;
}

シート側に↓ と入れて使う

=QUERYJOIN(A2:C20,E2:G20)

Comments

[s_m]

ご丁寧にありがとうございます！！

できれば関数でやりたかったのですが、やはり簡単に代用できそうなものはなさそうなのですね…。
ゆくゆくはスプレッドシート関数に実装されることを願うこととします。

非常に助かりました。
ありがとうございます。