Introduction
Ver. all 2013 2016 2019 365
ここでの説明は、飛び飛びの行があった場合、離れた一個所に計算結果を算出したい場合の説明です。
下のように飛び飛びで数式をコピーしたい時は、
下のリンク先を参考にしてください。
使用する関数は、
- SUM
- IF
- ROW
- MOD
の４つです。
それぞれの関数に馴染みのない方は難しく感じるかもしれません。
少しややこしくなるにゃ
１つ飛ばしなど飛び飛びの行を計算する方法
では、今回は１行飛ばしのセルを合計する例で説明していきます。
式を理解すると、2行飛ばしでも3行飛ばしもできます。
今回「C13」に入れる式は下のようになります。
=SUM(IF(MOD(ROW(C3:C12)-2,2)=0,C3:C12,0))
それぞれの役割を説明すると下のようになります。
ここで問題となるのは、「この条件を満たしていたら」のオレンジ下線の場所だと思います。
この場所
MOD(ROW(C3:C12)-2,2)=0
を重点的に説明していきます。
この場所を言葉にすると、それぞれの行が「=0」だったら（「0」と判定されたら）ということになります。
その「0」と判定されたセルだけ、最終的にSUM関数で合計されます。
今回の場合は、「1つ飛び」のセルだけ「0」になるようにしなければいけません。
その部分が
MOD(ROW(C3:C12)-2,2)
です。
1つ飛びを設定するのがMOD関数の役割です。
MOD関数は、割り算をした余りを返します。
MOD関数・・・数値を除数で割った余りを返す
MOD（数値,除数）
- 数値・・・割り算の分子となる数値を指定
- 除数・・・割り算の分母となる数値を指定
例えば
=MOD(5,2)
とした場合、
5÷2=2 余り1
の計算が行われ、結果は余りの「1」が表示されます。
下の表をご覧ください。黄色のセルを、それぞれ「2」「3」「4」で割り算した場合のMOD関数の動きを見てみましょう。
結果は下のようになります。
ここで注目してもらいたいのが、下の赤丸で囲んだ場所です。
対象の数値で割り切れた場所は、余りが無いので、当たり前ですが結果が「0」になります。
そう考えると、1つ飛びの場合は、「2」で割り、2つ飛びの場合は「3」、3つ飛びの場合は「4」で割れば「0」の結果が出ることが分かります。
飛ばす行の数+1
にすればOKですね。
MOD（〇,〇）
の後ろの除数の部分がこの数値になります。
今回の場合は、1つ飛びにしたいので、
MOD（〇,2）
ということになりますね。
ということで、
MOD(ROW(C3:C12)-2,2)
の最後の「2」の部分は解決しました。
後は、分子となる、手前の部分ですね。
MOD(ROW(C3:C12)-2,2)
今説明した、MOD関数で「〇番目」を指定するには、今回合計する範囲が「1」からの連番になるように設定する必要があります。
つまり、今回の表の下部分「C3」からを「1」から始まる連番にしたいのです。
その式が、
MOD(ROW(C3:C12)-2,2)
の赤字部分です。
ROW関数は、指定したセルの行番号を取得する関数です。
ROW関数・・・指定したセルの行番号を返す。
ROW（範囲［省略可］）
- 範囲・・・行番号を調べるセルや範囲を指定［省略可］
ROW関数は、下のリンク先で説明しています。
例えば、下の表の「E5」に =ROW(C3:C12) と入れると、
「C3」は３行目なので、「3」「4」「5」と数字が並びます。
※最近のバージョン（Office365）では、動的配列数式が使えるようになったため下のように表示されますが、旧バージョンでは表示されません。
これを「1」から始まるようにしたいのですが、難しく考えず「-2」してあげましょう。
これで「1」から始まる数値の出来上がりです。
ということで、このROW関数の場所をまとめると、
合計する範囲の最初の行番号-1
を指定してあげれば良いことになります。
例えば下の場所に表があった場合は、
ROW（合計したい範囲）-5
となります。
説明が長くなったので、まとめましょう。
まず、全体の式のイメージは下の通り。
さらに、オレンジ下線の場所だけを言葉で説明すると、
MOD(ROW(C3:C12)-2,2)=0
「合計したい範囲の行数を「2」で割り算した余りが「0」だったら」
という意味になります。
緑文字の「-2」の場所は、「合計する範囲の最初の行番号-1」の数値にして、
青文字の「2」は、「飛ばす行の数」+1にします。
そして、大切な事。
Office2021、Office365よりも前のバージョンを使用している方は、式を確定する際に、「Ctrl」+「Shift」+「Enter」で確定する必要があります。
これにより、式の最初と最後に { } が挿入され、配列数式として扱われます。
今回の式では、途中でROW(C3:C12)のように、「配列数式」を使用しています。
最近のバージョンのエクセルでは、自動でこの配列数式が適用されるようになっています。
しかし、前のバージョンでは、この配列を式で使用するためには、「Ctrl」+「Shift」+「Enter」で式を確定し、大括弧で囲う必要があるのです。
※配列数式として扱われないと、エラーは表示されませんが、結果が正しく表示されません。
ちなみに、SUMPRODUCT関数を使用することもできます。
その場合は、下の式になりますが、やはり赤文字にした部分がポイントになります。
=SUMPRODUCT((C3:C12)*(MOD(ROW(C3:C12),2)=0))
SUMPRODUCT関数で試したい方は、下のリンク先が参考になると思います。
まとめ
今回のようにROW関数やMOD関数を使用することで、様々な動きをエクセルに要求できます。
慣れるまでは難しく感じると思いますが、様々な場所で試すことで、自然と使えるようになりますよ。
最後に、「C4:C15」までの範囲を２つ飛ばしで合計する際の式です。
=SUM(IF(MOD(ROW(C4:C15)-3,3)=0,C4:C15,0))
表と見比べていただいて、式の内容を確認してみてください。
それぞれの場所をSUM関数で指定した方が楽だにゃ・・・