googleスプレットシートの参照方法について

googleスプレットシートについて、
以下のデータがあった場合に

A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
A4 B4 C4 D4 E4
A5 B5 C5 D5 E5

以下のように参照出力したいと
考えております。

A1 B1
A1 C1
A1 D1
A1 E1
A2 B2
A2 C2
A2 D2
A2 E2
A3 B3
A3 C3
A3 D3
A3 E3
. .
. .
. .

上記は5万データ以上もあるため
セルごとに参照する形式ではなく
=ARRAYFORMULAや=QUERYといった形式で
できないものか？と考えております。

大変お手数ではございますが
何卒ご指導のほどどうぞよろしくお願い致します。

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

回答1件

ベストアンサー

5万データ以上の時に処理速度がどうなるかはわかりませんが、とりあえず ARRAYFORMULA、QUERY がこのケースでは使えないと思うので、以下を試してみてください。

別シートへ出力の場合は参照先の記述の修正を。

出力後の1列目

=TRANSPOSE(SPLIT(ARRAYFORMULA(TEXTJOIN(,TRUE,REPT(A1:A&",",4))),","))

出力後の2列目

=TRANSPOSE(SPLIT(TEXTJOIN(",",TRUE,B1:E),","))

データ量的に関数の処理だと厳しいようなので GASで出力するコード
ベタな書き方しか出来ないのでfor入れ子で。

GAS
1function dataTolist(){
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3  const fromSheet = ss.getSheetByName("商品イメージ（ T-2-7〜T-2-9）");
4  const toSheet = ss.getSheetByName("商品50%具体化（T-2-13〜T-2-15）");
5  const lastrow = fromSheet.getLastRow();
6  const data = fromSheet.getRange(1,1,lastrow,5).getValues();
7
8  let list =[];
9
10  for(i=0; i<lastrow; i++){
11    for(j=1; j<=4; j++){
12    list.push([data[i][0],data[i][j]]);
13    }
14  }
15  toSheet.getRange(1,1,list.length,2).setValues(list);
16}

以下、Arrayfolmura と Vlookup を使ったバージョン参考までに。
動きは重いかもです。

出力後の1列目

=ARRAYFORMULA(VLOOKUP(QUOTIENt(SEQUENCE(4*COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A),1,4),4),{SEQUENCE(COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A)),INDIRECT("'商品イメージ（ T-2-7〜T-2-9）'!A1:A"&COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A))},2,FALSE))

出力後の2列目

=ARRAYFORMULA(IFERROR(VLOOKUP(A1:A,'商品イメージ（ T-2-7〜T-2-9）'!A1:E,MOD(ROW(A1:A)-1,4)+2,FALSE),))

もしA列のデータに重複する値がある場合は 2列目は以下で

=ARRAYFORMULA(VLOOKUP(QUOTIENT(SEQUENCE(4*COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A),1,4),4),{SEQUENCE(COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A)),INDIRECT("'商品イメージ（ T-2-7〜T-2-9）'!B1:E"&COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A))},MOD(SEQUENCE(4*COUNTA('商品イメージ（ T-2-7〜T-2-9）'!A1:A))-1,4)+2,FALSE))

投稿2021/01/12 00:45

編集2021/01/12 09:34

sawa

総合スコア3002

tile

2021/01/12 02:56

ご指導いただき心より感謝申し上げます！テスト環境で試してみましたところご指摘の挙動になりました！ただ本番環境ですと、データが多いためか、TEXTJOINが50000字を超えたためエラーが出るようになりました。TEXTJOIN以外の方法で実現可能な案件でございますでしょうか？度々大変お手数ではございますがご指導何卒どうぞよろしくお願い申し上げます。

sawa

2021/01/12 04:13 編集

データ量が多すぎてエラーでしたか。そうすると関数じゃなくてGASで処理した方がよいと思います。それでも良いですか？その場合は現在のデータのあるシート、および出力先のシート名があるとよいです。

tile

2021/01/12 04:29

ご指導くださりありがとうございます！ GASによる処理ということで承知いたしました。素敵なご提案心より感謝申し上げます。データのあるシート名が「商品イメージ（ T-2-7〜T-2-9）」という名前で出力先のシート名が「商品50%具体化（T-2-13〜T-2-15）」という名前となっております。追加で必要な情報がございましたらご指摘いただければ幸いです。

sawa

2021/01/12 05:03

GASのコードを回答に追記しました。データはA1:Eの最終行、出力はA1:B としてます。

tile

2021/01/12 07:53

コードを制作してくださり本当にありがとうございます！実は、GAS自体は初めての経験でワクワクしております、実際に試してみたいと思います。この度はお時間いただき心より感謝申し上げます。

sawa

2021/01/12 08:03

1000行分（5000セル）くらいなら一瞬でいけましたが、5万データ以上だとどうなるか気になるので、実際試してみたら処理速度（体感値で結構です）を教えていただければ幸いです。

sawa

2021/01/12 09:04

追記した関数は元データのA列に同じ値が存在するとダメなので、その場合は式を修正します。