Excelで配列をアイテム単位で繰り返し表示させる計算式

Excelで特定の配列内のアイテムを指定した回数だけ繰り返し表示する方法について解説します。以下の計算式を使うことで、配列の要素を効率的に繰り返し表示できます。

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

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

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

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

式の構造と効果

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

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

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

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

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

INT関数は数値を整数化します。

INT(... / 回数)は、数列を回数で割り、その商の整数部分を取得します。これにより、各要素が回数分繰り返されます。

INT(… / 回数)+1ならば、上記の式に1を足したものになります。

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

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

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

=INDEX($A$1:$A$4, INT(SEQUENCE(COUNTA($A$1:$A$4) * 3, 1, 0) / 3) + 1)

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

=INDEX($A$1:$A$4, INT(SEQUENCE(COUNTA($A$1:$A$4) * C1, 1, 0) / C1) + 1)

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

東京
東京
東京
千葉
千葉
千葉
神奈川
神奈川
神奈川
埼玉
埼玉
埼玉

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

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

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

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

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

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