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)は、データ範囲に合わせて設定してください。
- ROW関数はデータ範囲の先頭から、それぞれの行番号を取得します。
- MOD関数は被除数として行番号を受け取り、2 で除算した余剰を返します。
- FILTER関数は指定した範囲を、 余剰= 1 の条件を満たす行にフィルタ処理します。
FILTER関数の構成要素:(範囲, 条件1, [条件2, …])
まずROW関数は、引数として指定した参照の行番号を返します。
例えば ROW(A1) なら返り値はセルの値(2)ではなく、行番号に当たる 1 です。2行目以降からデータを抽出する場合は、ROW(A:A)-1 のように 1 を減算してください。
ROW関数の構成要素:([参照])
次にMOD関数は被除数を除数で割ったときの、剰余を返します。
例えば MOD(ROW(A3),2) は行番号 3 を 2 で割り算するので、余剰は 1 です。つまり除数が 2 である限り、MOD関数は被除数(行番号)が 奇数のとき 1 を返します。
最後に FILTER関数 で返り値 1 の行にフィルタ処理すれば、1行飛ばしのデータが抽出可能です。
MOD関数の構成要素:(被除数, 除数)
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関数の除数で調整してください。
SUM関数の構成要素:(数値 1, [数値 2], …)
n行飛ばしで抽出・合計するフィルタ機能
まず抽出するセルの隣接行に、印(しるし)を入力してください。
印は最初の 2 つほどを入力しておけば、オートフィルで入力パターンをコピーできます。行数が多い場合は [Ctrl]+[Shift]+[↓] で下まで選択し、貼り付けが便利です。
印を入れる間隔によって、合計の間隔を簡単に調整できます。
次に表など一連の範囲を選択し、データタブから フィルタを作成 をクリックします。
印列の絞込みアイコン[▼]を開き、空白のチェックを外してください。
OKボタンを押すと印のないセルは非表示になるため、1行飛ばしの行を抽出できました。
SUMTOTAL関数 は1つまたは複数の範囲を、指定したコードに従って集計します。
SUBTOTAL(109,B2:B10) のように、集計コードに 109 を指定してください。集計コード 109 は、非表示の行を含まない合計を返します。
なおフィルタ結果に含まれない行は、集計方法の値にかかわらずすべて無視されます。
SUBTOTAL関数の構成要素:(集計コード,範囲 1,[範囲 2],…)