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

前提・実現したいこと

スプレッドシートにて、
[表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関数には外部結合のような機能がないとのことで、
代替となる方法をご存じの方がいらっしゃいましたらご教示いただけますと幸いです。

sawa

2021/11/19 08:39

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

s_m

2021/11/19 13:00

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

行動規範の内容に同意します

回答1件

ベストアンサー

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

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

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

GoogleSheet
1=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で自作関数作る場合

GAS
1function QUERYJOIN(x,y){
2  const keys = [...new Set([...x.map(v => v[0]),...y.map(v => v[0])])].sort().filter(v=>v);
3
4  //空欄埋める用
5  const xNullArr = x[0].map(v => "");
6  const yNullArr = y[0].map(v => "");
7
8  const result = keys.reduce((pv,key) => {
9    const xMatchArr =  x.filter(row => row[0] === key);
10    const yMatchArr =  y.filter(row => row[0] === key);
11
12    let i =0;
13    while(xMatchArr[i] || yMatchArr[i]){
14      const xMatch = xMatchArr[i] || xNullArr;
15      const yMatch = yMatchArr[i] || yNullArr;
16      pv.push([...xMatch,...yMatch]);
17      i++
18    }
19    return pv;
20  },[])
21
22  return result;
23}