シート内のある範囲の文字列を繰り返して自動入力できるようにしたいです。

Question

Googleスプレッドシートで以下の事を実現させる方法を教えていただきたいです。

・シート名「対象」のA1～A7のセルにそれぞれ文字列が入っています。
シート名「表」のC5に「対象」のA1～A7のセルに入っている文字列のどれかを入力したら、
「表」のE5→C6→E6→C7→…C35→E35の順に、「対象」のA1～A7の文字列が順番に繰り返して自動で入力されるようにしたいです。
その時に入れた文字列の次の文字列から入るようにしたいです。
(例えば「表」C5に「対象」A3の文字列を入れたら、「表」E5に「対象」A4の文字列が自動で入る。)
・なるべくGASを使わずに関数で実現させたいです。無理ならGASでの方法を知りたいです。

以上です。
宜しくお願い致します。

5月22日追記：
自分で考えてみましたものを以下に記述致します。

①「表」E5に以下の式を入れる。
=SWITCH(C5,'対象'!$A$1,'対象'!$A$2,'対象'!$A$2,'対象'!$A$3,'対象'!$A$3,'対象'!$A$4,'対象'!$A$4,'対象'!$A$5,'対象'!$A$5,'対象'!$A$6,'対象'!$A$6,'対象'!$A$7,'対象'!$A$7,'対象'!$A$1,"")

②C列とE列に式をドラッグしてコピー。C列にコピーしたら式の数値を変更してからC35までドラッグしてコピー。

以上です。一応できたのですが、よりスマートにできる方法がありましたら教えていただけると幸いです。
なさそうでしたら自己解決とさせていただきます。

Comments

[sawa]

>関数で実現
色々と検索して調べた上での質問かと思いますが、どういった関数を使ったらと実現できそうだと考えましたか？やりたいことを分割した上で、
「スプレッドシート　関数　〇〇」
みたいに検索すると、それっぽいサイトが幾つか見つかります。
そうするとなんとなく、この関数を使えば出来そうというアタリをつけられるかと。

手っ取り早く答えが欲しいってことでしたら、関数で実現する方法は回答できますが、まずは綺麗で簡潔な方法でなくてもいいので、ご自身なりに調べて試したものを記載いただくと良いと思います。

[退会済みユーザー]

ご意見ありがとうございます。自分で試してみたものを追記致しますので、
よろしければご確認いただきましてアドバイスをいただけますと幸いです。
宜しくお願い致します。

Answer 1

関数 SWITCHを使う方法を試されたのですね。パターン毎に回答を返す関数なので、こういった7つくらいのケースへの対応としては良いと思います。よりスマートかはわかりませんが、幾つか他のアプローチをご提示します。

1.MTACH、OFFSET、MODを使ってループさせる
上をE5に貼ってE6以下にコピペかオートフィル
下をC6に貼ってC7以下にコピペかオートフィル

=OFFSET('対象'!$A$1,MOD(MATCH($C6,'対象'!$A$1:$A$7,0),7),0,1,1)

=OFFSET('対象'!$A$1,MOD(MATCH($E5,'対象'!$A$1:$A$7,0),7),0,1,1)

1つ前のキーワードがA1~A7内の何番目かをMATCHで見つけ、OFFSETで'対象'!$A$1(あいうえお)を起点として、MATCHで返してきた数値分下のセルを取得。（つまりは1個下を取得っていう動きになります）

ただ「まみむめも」の次を最初に戻って「あいうえお」とさせる為に、MODという割り算の余りを出す関数を使って、7番目の時を7÷7=0と表示させることで、「あいうえお」に戻しています。（A1の0個下なのでA1そのもの）

2.先に対象を繰り返した配列を用意した上で、数列のルールからFILTERで抽出
上をE5に貼る。
下をC6に貼る。

=FILTER(TRANSPOSE(SPLIT(REPT(JOIN(",",'対象'!A1:A7)&",",10),",")),SEQUENCE(70) > MATCH(C5,'対象'!A1:A7,0),SEQUENCE(70) <MATCH(C5,'対象'!A1:A7,0)+62,MOD(SEQUENCE(70)+MATCH(C5,'対象'!A1:A7,0),2)=1)

=FILTER(TRANSPOSE(SPLIT(REPT(JOIN(",",'対象'!A1:A7)&",",10),",")),SEQUENCE(70) > MATCH(C5,'対象'!A1:A7,0),SEQUENCE(70) <MATCH(C5,'対象'!A1:A7,0)+62,MOD(SEQUENCE(70)+MATCH(C5,'対象'!A1:A7,0),2)=0)

式をまとめようとしたら複雑な式になってしまったのですが、OFFSETはArrayFormula関数と相性が悪いので、こんな方法になりました。

考え方としては先にA1A7をJOINで連結させて、REPTで繰り返して、SPLITで分割して、TRANSPOSEで縦に戻すことで、A1A7を10回繰り返し。これにSEQUENCEで1~70まで番号を振ったものを左にくっつけたイメージの仮想の作業列を作成してます。(素直に作業列を作った方が理解できると思います)

これをFILTERで開始と最後を条件で切って、E列側、C列側 それぞれのルール（C5の値が'対象'!A1:A7 の何番目かによって、偶数行か奇数行のみの数列になる）をMODの余りで絞りこんだ式です。

2番目のFILTERの方は可読性も低いしスマートでもないので蛇足ですが、とりあえずこの手の繰り返しや1個飛び（偶数のみ、奇数のみ）みたいなルールがある場合は MODが有効だったりします。

FILTERを使った式があまり良くないので、ARRAYFORMULAの式を追記します。これが一番シンプルかも。

E5に貼る

=ARRAYFORMULA(LOOKUP(MOD(SEQUENCE(31,1,MATCH($C$5,'対象'!$A$1:$A$7,0),2),7)+1,SEQUENCE(7),'対象'!$A$1:$A$7))

C6に貼る

=ARRAYFORMULA(LOOKUP(MOD(SEQUENCE(30,1,MATCH($C$5,'対象'!$A$1:$A$7,0),2),7)+2,SEQUENCE(7),'対象'!$A$1:$A$7))

Comments

[退会済みユーザー]

sawa様

返信が遅れまして大変申し訳ございません。
ご回答ありがとうございます。
色々な関数を教えていただきまして、大変勉強になりました。
MODは色々な所に使えて便利なのですね。
SEQUENCEの使い方やFILTERの引数の書き方は
まだ慣れない部分がありますが、これからまた勉強していきたいと思います。
ありがとうございました。