Googleスプレッドシートで1行飛ばしで抽出・合計する方法

Googleスプレッドシートで1行飛ばし、2行飛ばし、3行飛ばしのデータを抽出・合計する方法をご紹介します。フィルタ機能を使えば複雑な数式なしで n 行飛ばしのデータを抽出または合計できるので、ぜひ参考にしてみてください。

n行飛ばしで抽出・合計する関数

抽出データの開始位置が2行目からならROW関数に 1 を、3行目からならROW関数に 2 を減算してください。また、FILTER関数とROW関数の範囲は一致させる必要があります。

1行飛ばしで抽出する方法

1行飛ばしで抽出する数式は FILTER(A:A,MOD(ROW(A:A),2)=1) です。参照部分(A:A)は、データ範囲に合わせて設定してください。

1. ROW関数はデータ範囲の先頭から、それぞれの行番号を取得します。
2. MOD関数は被除数として行番号を受け取り、2 で除算した余剰を返します。
3. FILTER関数は指定した範囲を、 余剰= 1 の条件を満たす行にフィルタ処理します。

まずROW関数は、引数として指定した参照の行番号を返します。

例えば ROW(A1) なら返り値はセルの値(2)ではなく、行番号に当たる 1 です。2行目以降からデータを抽出する場合は、ROW(A:A)-1 のように 1 を減算してください。

次にMOD関数は被除数を除数で割ったときの、剰余を返します。

例えば MOD(ROW(A3),2) は行番号 3 を 2 で割り算するので、余剰は 1 です。つまり除数が 2 である限り、MOD関数は被除数(行番号)が 奇数のとき 1 を返します。

最後に FILTER関数 で返り値 1 の行にフィルタ処理すれば、1行飛ばしのデータが抽出可能です。

2行飛ばし・3行飛ばしで抽出する方法

2行飛ばしで抽出する場合は、MOD関数の除数を 3 にしてください。

例えば FILTER(A:A,MOD(ROW(A:A),3)=1) なら、3 で割った余剰が 1 の行のみ抽出します。なお除数を 4 にすれば、3行飛ばしの抽出も可能です。

n行飛ばしで合計する方法

SUM関数は個々の数値、セル参照、セル範囲、またはこれらすべての組み合わせを合計します。

n行飛ばしの合計を取得する場合 FILTER関数 の抽出結果を使えばいいので、1行飛ばしの合計なら SUM(FILTER(A:A,MOD(ROW(A:A),2)=1)) です。

合計間隔を変える場合は、MOD関数の除数で調整してください。

n行飛ばしで抽出・合計するフィルタ機能

まず抽出するセルの隣接行に、印(しるし)を入力してください。

印は最初の 2 つほどを入力しておけば、オートフィルで入力パターンをコピーできます。行数が多い場合は [Ctrl]+[Shift]+[↓] で下まで選択し、貼り付けが便利です。

次に表など一連の範囲を選択し、データタブから フィルタを作成 をクリックします。

印列の絞込みアイコン[▼]を開き、空白のチェックを外してください。

OKボタンを押すと印のないセルは非表示になるため、1行飛ばしの行を抽出できました。

SUMTOTAL関数 は1つまたは複数の範囲を、指定したコードに従って集計します。

SUBTOTAL(109,B2:B10) のように、集計コードに 109 を指定してください。集計コード 109 は、非表示の行を含まない合計を返します。

なおフィルタ結果に含まれない行は、集計方法の値にかかわらずすべて無視されます。