一つ飛ばし・１行おきなどの飛び飛びを関数で合計する方法

ここでは、セクセルで「一つ飛ばし・１行おきなどの飛び飛びを関数で合計する方法」を紹介します。

他の列に、条件として使える値がある場合とない場合の２通りの説明です。

始めに、やりたいことを例を含めて確認します。

１－１．飛び飛びのセルを合計したい

やりたいことは、「飛び飛びのセルを合計・集計する」です。

例えば一つのデータで複数行使っている場合、合計したいセルが飛び飛びになります。

このような場合、他の列に条件として使える値が「ある場合」と「ない場合」で方法が変わりますので、それぞれ説明します。

１－２．例で確認

例をふたつ見てみましょう。

一つ目の表は、A店とB店の4～6月の売り上げを入力したものです。

この表からセルG4にあるように、「A店の3ヶ月の売り上げの合計」を求めることができます。ちなみに、「合計したい行のB列に『A店』とある（＝他の列の条件で使える値がある）」のがポイントです。

二つ目の表は、A店の4～6月の売り上げとロス率を入力したものです。

この表からセルE4にあるように、「4～6月の売り上げの合計」を求めることもできます。ちなみに、「合計したい行の他の列に条件で使える値がない」のがポイントです。

まずは簡単な方からで、「他の列に条件で使える値がある場合」からです。

２－１．まずはポイントを確認

この方法を使う時のポイントは、次の通りです。

先程の例でいうと、「合計したい行の他の列に必ず『A店』という値」があります。

つまり、これを条件値とすればSUMIF関数で簡単に「A店の売り上げ」」が合計できるのです。

２－２．SUMIF関数の使い方

SUMIF関数の数式の形は、次の通りです。

例えば、先ほどの例でセルG4に入れた数式は、次の通りです。

=SUMIF(B4:B9,F4,C3:C9)

「範囲」には、「条件値のある範囲」を指定します。例だと、店舗名が入っている「B4:B9」となります。

「検索条件」には、「条件値」を指定します。例だと、A店と入っている「F4」となります。

「合計範囲」には、「合計する範囲」を指定します。例だと、売上額が入っている「B4:B9」となります。

２－３．実際に数式を作ってみる

では、先ほどの表から「A店の売り上げを合計する数式」を作ってみます。

以下手順です。

（１）以下の数式をコピーし、セルに貼り付ける

【コピーする数式】

（２）「範囲」を消し、「条件値のある範囲」を指定する

（３）「検索条件」を消し、「条件値」を指定する

（４）「合計範囲」を消し、「合計したい範囲」を指定する

（５）Enterキーを押せば完了

２－４．SUMIF関数の注意点

SUMIF関数で一つ注意しなければならないのは、「範囲と合計範囲の『行範囲』を合わる」ことです。

例では、「範囲」も「合計範囲」も行の範囲は「4～9行目」となっていますが、仮に合計範囲を3～9行目としてしまうと、誤った値が返ってきます。

このようにならないために、範囲と合計範囲の「行範囲」は必ず合わせるようにしましょう。

次に、「条件に使える値がなく『〇行おき』に合計したい場合」です。

合計したい行がひとつ飛ばし（1行おき）や3行おきなど、規則的に並んでいるケースで使えます。

３－１．例を確認

一つ例を見てみます。

上の表は、先ほどの例とは違って「合計したい行の他の列に条件値になる値がない」状態です（本当はなくはないですが…）。

つまり、SUMIF関数で合計できない形です。

しかし一方で、「合計したい行は『1行おき』」という規則性もあります。

ここではこの規則性を生かして、「〇行おきの合計」を求めます。

３－２．数式の形

例えば、先ほどの例でセルG4に入れた数式は、次の通りです。

=SUMPRODUCT((MOD(ROW(B4:B9)-ROWS(B1:B3),1+1)=1)*(B4:B9))

「合計範囲」には、「合計する範囲」を指定します。例だと、売上額が入っている「B4:B9」となります。

「範囲」には、「1行目から合計範囲の一つ前まで（列番号はなんでも可）」を指定します。例だと、「B1:B3」となります。

「空き行数」には、「〇行おきの〇の数」を指定します。例だと、1行おきなので「1」となります。

３－３．実際に数式を作ってみる

以下手順です。

（１）以下の数式をコピーし、セルに貼り付ける

【コピーする数式】

（２）「合計範囲」を消し、「合計したい範囲」を指定する

（３）「範囲」を消し、「1行目から合計範囲の手前までの範囲」を指定する

（４）「空き行数」を消し、「『〇行おき』の〇の数」を指定する

（５）Enterキーを押せば完了

３－４．数式はどのように計算されるのか？

最後に、数式が同様に計算されるのかを確認します。

（１）MOD( )の部分

MOD関数は、「数値を割った余りを返す」関数です。

まず、大前提として一番外側がSUMPRODUCT関数のため、ROW関数は「配列数式」になります。つまり、「範囲内の行番号をそれぞれ持つ」形になり、ROW関数のところだけ見ると、「4,5,6,7,8,9」となります。

次に、ROWS関数で「1行目から合計範囲の一つ上までの行数（例では「3」）」を引くので、値は「1,2,3,4,5,6」に変わります。

また、「空き行数+1」はMOD関数の「除数（割る数）」ですが、例では1行おきに足したいので「1+1=2」となります。

そして、「1,2,3,…」を「2」で割った余りなので、結果「1,0,1,0,1,0」となります。

（２）SUMPRODUCT( )の部分

SUMPRODUCT関数は、「カッコ同士を掛け合わせた後合計する」関数です。

左側のカッコ「MOD( )=1」は、「余りが1の場合に真となり、エクセルでは真は1なので、結果「1,0,1,0,1,0」となります。

そして右側のカッコ「B4:B9」は「合計したい値」が入っているので、「100,15%,112,11.0%,98,12.0%」となります。

これらを掛け合わせると、「100,0,112,0,98,0」となり、合計すると「310」となります。

計算はちょっと複雑ですが、慣れると便利です。

前項で紹介した方法は結果のみが表示されるので、表示上はすっきりとしますが途中が合っているか確認しにくいデメリットがあります。

そこで計算用のセルを用意して、行ごとの値の抜き出しが正しいことを確認できるようにする方法も紹介します。

４－１．例を確認

つまりは、上のような形です。

計算用のセルがあれば間違いを見つけることができ、正しければ後はこの範囲をSUM関数で合計すればいいのです。

４－２．数式の形

数式の形は、次の通りです。

先程の数式との違いは、「SUMPRODUCT関数をIF関数に変えた」ことです。そして、真の場合には「合計したいセル」の値を、偽の場合には「空白」を表示するようにしています。

例えば、先ほどの例のセルCに入った数式は次の通りです。

=IF(MOD(ROW(B4:B9)-ROWS(B1:B3),1+1)=1,B4,"")

４－３．数式を作ってみる

上の例のセルC4に数式を作ってみます。

以下手順です。

（１）以下の数式をコピーし、セルに貼り付ける

【コピーする数式】

（２）各引数を入れてEnterキーを押す

（３）他のセルにコピーすれば完了

以上、参考になれば幸いです。

関数の問題解決記事一覧へはこちら