Excelでn列おきにデータを抽出する計算式

Excelでは、大量のデータから特定のパターンに基づいてデータを抽出することがよくあります。ここでは〇列おき、n列おきにデータを抽出する方法を解説します。２列おき、３列おき、４列おき……、といった取り出しができるようになります。この方法は、COLUMNS関数、FILTER関数、SEQUENCE関数、MOD関数を組み合わせて実現します。

数式の構造

基本式は以下の通りとなります。

=FILTER(配列,MOD(SEQUENCE(1,COLUMNS(配列)),ステップ数)=1)

LET関数を用いて引数「配列」の出現を1度にする場合は以下の式となります。

=LET(_Range,配列,_Step,ステップ数,FILTER(_Range,MOD(SEQUENCE(1,COLUMNS(_Range)),_Step)=1))

具体例をみましょう。範囲・配列を「B1:Q2」、4ステップごとに行を抽出する場合は以下のいずれかの式となります。

=FILTER(B1:Q2, MOD(SEQUENCE(1, COLUMNS(B1:Q2)), 4) = 1)

=LET(_Range,B1:Q2,_Step,4,FILTER(_Range,MOD(SEQUENCE(1,COLUMNS(_Range)),_Step)=1))

Excelでn列おきにデータを抽出する計算式。範囲とステップ数を指定すれば使える。

数式の解説

=FILTER(B1:Q2, MOD(SEQUENCE(1, COLUMNS(B1:Q2)), 4) = 1)

この数式の構造を順に解説していきます。

データ範囲の指定

B1:Q2は、抽出対象のデータ範囲です。この範囲内の列からデータを抽出します。

抽出ステップ数の指定

数式内の4は、抽出する列のステップ数を指定しています。この例では4列おきにデータを抽出します。

COLUMNS関数

COLUMNS(B1:Q2)

データ範囲の列数をCOLUMNS関数で調べます。この範囲は16列です。

SEQUENCE関数

SEQUENCE(1, COLUMNS(B1:Q2))

SEQUENCE関数は、指定した範囲の列数に基づいて連続した数値の配列を生成します。例えば、範囲が16列の場合、{1, 2, 3, ..., 16}という配列を生成します。

MOD関数

MOD(SEQUENCE(1, COLUMNS(B1:Q2)), 4)

MOD関数は、SEQUENCE関数で生成された配列の各要素を4で割った余りを計算します。これにより、4列ごとの列番号を特定することができます。結果として{1, 2, 3, 0, 1, 2, 3, 0, ...}という配列が生成されます。

FILTER関数(完成)

=FILTER(B1:Q2, MOD(SEQUENCE(1, COLUMNS(B1:Q2)), 4) = 1)

FILTER関数は、指定された条件に一致するデータを抽出します。どのような条件とするかは、引数「含む」へセットします。上記の例では引数「含む」へ、MOD(SEQUENCE(1, COLUMNS(B1:Q2)), 4) = 1という条件に一致する列を抽出します。これは、余りが1の列(各ステップの先頭)を意味します。

この方法により、指定された範囲から任意のステップ数おきに列を抽出できます。簡潔で読みやすい数式を使用することで、データの抽出を効率的に行うことができます。