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

Question

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といった形式で
できないものか？と考えております。

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

Answer 1

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

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

出力後の1列目

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

出力後の2列目

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

---

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

function dataTolist(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const fromSheet = ss.getSheetByName("商品イメージ（ T-2-7〜T-2-9）");
  const toSheet = ss.getSheetByName("商品50%具体化（T-2-13〜T-2-15）");
  const lastrow = fromSheet.getLastRow();
  const data = fromSheet.getRange(1,1,lastrow,5).getValues();

  let list =[];

  for(i=0; i<lastrow; i++){
    for(j=1; j<=4; j++){
    list.push([data[i][0],data[i][j]]);
    }
  }
  toSheet.getRange(1,1,list.length,2).setValues(list);
}

---

以下、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))

Comments

[tile]

ご指導いただき心より感謝申し上げます！
テスト環境で試してみましたところご指摘の挙動になりました！

ただ本番環境ですと、データが多いためか、TEXTJOINが50000字を超えたためエラーが出るようになりました。TEXTJOIN以外の方法で実現可能な案件でございますでしょうか？

度々大変お手数ではございますが
ご指導何卒どうぞよろしくお願い申し上げます。

[sawa]

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

[tile]

ご指導くださりありがとうございます！
GASによる処理ということで承知いたしました。
素敵なご提案心より感謝申し上げます。

データのあるシート名が「商品イメージ（ T-2-7〜T-2-9）」という名前で
出力先のシート名が「商品50%具体化（T-2-13〜T-2-15）」
という名前となっております。

追加で必要な情報がございましたら
ご指摘いただければ幸いです。

[sawa]

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

[tile]

コードを制作してくださり本当にありがとうございます！
実は、GAS自体は初めての経験でワクワクしております、
実際に試してみたいと思います。

この度はお時間いただき
心より感謝申し上げます。

[sawa]

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

[sawa]

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