Excelで配列をセット単位で繰り返し表示させる計算式

Excelで特定の配列をセット単位で繰り返し表示する計算式を紹介します。

この記事では、INDEX関数とSEQUENCE関数を組み合わせて、配列の要素をセット単位で指定した回数だけ繰り返して表示する方法を詳しく解説します。以下の計算式を使うことで、配列の要素を効率的に繰り返し表示できます。

=INDEX(配列, MOD(SEQUENCE(COUNTA(配列) * 回数, 1, 0), COUNTA(配列)) + 1)

「配列」の指定を一度だけで済ます場合は以下の式を使用してください。コピペで使う場合にはこちらがおすすめです。

=LET(_Array,配列,_Number,回数,INDEX(_Array, MOD(SEQUENCE(COUNTA(_Array) * _Number, 1, 0), COUNTA(_Array)) + 1))

※横に展開させるならばTRANSPOSE関数で式全体を囲んで縦横回転させてください。

式の構造と効果

上記の計算式は、配列の要素を指定した回数だけ繰り返し表示するために設計しました。それぞれの関数の役割を理解することで、式の動作を把握することができます。

これは、繰り返し表示したいデータの範囲を指します。たとえば、セル範囲$A$1:$A$4のように指定します。

COUNTA(配列)は、配列の要素数をカウントします。配列が4つの要素を持つ場合、結果は4になります。

SEQUENCE関数は連続した数列を生成する関数です。

SEQUENCE(COUNTA(配列) * 回数, 1, 0)は、0から(COUNTA(配列) * 回数 – 1)までの連続した数列を生成します。例えば、COUNTA(配列)が4で回数が3の場合、0から11までの数列を生成します。

MOD関数は除算・割り算の余りを計算する関数です。

MOD(SEQUENCE(...), COUNTA(配列))は、数列の各要素を配列の要素数で割った余りを計算します。これにより、数列は0から(COUNTA(配列) – 1)の範囲に変換されます。

MOD(SEQUENCE(...), COUNTA(配列))+1ならば以下のような配列が生成されます。

INDEX関数では配列と位置を指定して値を取り出すことができます。

INDEX(配列, ...)は、変換された数列を使って、配列の要素を順番に参照します。これにより、指定された回数だけ配列の要素が繰り返し表示されます。

これらの関数を組み合わせて、配列の要素をセット単位で指定した回数だけ繰り返し表示する計算式が導き出されます。

=INDEX(配列, MOD(SEQUENCE(COUNTA(配列) * 回数, 1, 0), COUNTA(配列)) + 1)

具体的な例を使って、式の動作を確認してみましょう。例えば、セル範囲$A$1:$A$4に「東京」「千葉」「神奈川」「埼玉」が入力されているとします。このセットを「配列」として登録し、3回繰り返して表示する場合、次のようになります。

=INDEX($A$1:$A$4,MOD(SEQUENCE(COUNTA(A1:A4) * 3, 1, 0), 3) + 1)

「回数」をセルC1へセットした場合の式は次のようになります。

=INDEX($A$1:$A$4,MOD(SEQUENCE(COUNTA(A1:A4) * C1, 1, 0), C1) + 1)

セルC1へ入力した値が「3」のとき、結果は以下のようになります。

東京
千葉
神奈川
東京
千葉
神奈川
東京
千葉
神奈川
東京
千葉
神奈川

「配列」の指定を一度だけで済ませるならば、前述の式をLET関数で変形させた以下の式を使用するのがおすすめです。出力先のセルへ貼り付けてから「配列」と「回数」を登録します。

=LET(_Array,配列,_Number,回数,INDEX(_Array, MOD(SEQUENCE(COUNTA(_Array) * _Number, 1, 0), COUNTA(_Array)) + 1))

出力先へコピペをしたら、「関数の挿入」(fx)で引数の指定へ進みます。

「配列」と「回数」を指定します。

指定後はOKして完成です。

※横に展開させるならばTRANSPOSE関数で式全体を囲んで縦横回転させてください。