［Excel］１つ飛ばしでセルを合計する小ネタ

Excel を使っていると、セルを飛ばし飛ばしで合計を出したいときがよくあります。その方法は様々ですが、今回は私がよく使っている簡単な手法を紹介します。

本ページはこんな方におすすめ

表計算で飛ばし飛ばしのセルの合計を出したい
各階ごとに備品の合計を素早く出したい

使用する関数は

SUMPRODUCT
MOD
ROW

の３つです。
ROW 関数に関しては、ズルく使うためのテクニックみたいなものですので、特に使いこなせなくても実務に支障は出ません。SUMIFS 関数を使えば、SUMPRODUCT 関数のような博物館入りのものを使わなくて良いように思うかもしれませんが、時には枯れた関数の方が、数式を煩雑化させない、簡単に数式を分解できて応用がきくなどの理由で使いやすいことがあります。すでに、SUMIFS 関数になれている方は、頭の体操として読んでみてください。

Excel でセルを一つ飛ばしで合計するには？

Excel の SUMPRODUCT 関数の復習

Excel の SUMPRODUCT 関数は掛け算と足し算をまとめて行う関数です。単価＊個数で合計を出すときに SUMPRODUCT 関数で一気に行うよく使いました。よく見る例は以下のようなものです。

例では単価の列（G列）と個数の列（H列）を選択し、掛け合わせて合計を G20 のセルに出しています。
このような使い方だけだとホントのところ、使っている本人だけが気持ちいいだけで、イマイチな独りよがり感があります。

Excel で SUMPRODUCT と MOD 関数を組み合わせて一つ飛ばしに合計を出す

１階と２階に割り当てた備品の総合計を算出するという単純な例を考えてみようと思います。例では Apple 製品を１階と２階に割り当てた表になってますが、ホテルや病院のベッドなどの割り当てる例でも、同様に計算できます。

この例で関数を入力しているのは C23 と C24（と C25）だけです。C23 には１階の備品（機種）の割り当て合計金額、C24 には２階の合計金額を出しています。C25 はその２つを足しただけです（C23＋C24）。

Excel の ROW 関数の復習

ROW 関数は引数に与えたセルの行番号を返す関数です。例えば =ROW(C15) とやれば、C15 は C列の15行目ですので、15 という数字が返ります。図例のようにセル単体を引数にせず、連続した複数のセルを対象とする配列指定して =ROW(A3:A22) とやると３～22までの数字の配列が返ります。もう少しかみ砕くと、セル C23 で合計を計算するときに、A3 の行を合計するときには =ROW(A3:A22) は =ROW(A3) と解釈され、数値としては３になり、A4 の行を合計に加えるときは =ROW(A3:A22) は =ROW(A4) として解釈され数値の４になり、A22 の行を合計に加えるときは =ROW(A3:A22) は =ROW(A22) として解釈され、数値の 22 になるという意味です。このまとめた処理を、C23 の一つのセルで行うときは ROW(A3:A22) と範囲指定で行います。

Excel の MOD 関数の簡単な使用例

本例での MOD 関数は正の数を正の数で割ったときの余りを求める使い方をしています（負の数を扱うケースは、後から補足説明します、本例では使用しません）。例えば =MOD(5,2) とやれば５÷２＝２余り１ということから、余りの１が返ります。
本例の「配置（C列）」は３、５、７、９といった奇数行に１階が必ずきていることから、目的の行を２で割って余りが１になる行が１階のデータになります。余りが０になる行が２階です。
つまり、MOD(目的の行,2)=1 となる行の価格欄と個数欄を掛けた数字をすべて合計すれば１階の合計になります。MOD(目的の行,2)=0 となる行の合計が２階の合計になります。

"(MOD(ROW(A3:A22),2)=1)*C3:C22" の部分は、以上のことから "(MOD(ROW(目的の列範囲),2)=1)*価格" という意味です。かみ砕くと、ROW(目的の列範囲) の部分は単に目的とする行番号が返りますので、それを２で割って余りが１であれば、１階のデータだとして価格欄（C列の値）をつかうという意味になります。併せて、

"=SUMPRODUCT((MOD(ROW(A3:A22),2)=1)*C3:C22,D3:D22)" として、SUMPRODUCT(価格,個数) のとして１階の合計金額を出しています。

Excel でセルを飛ばし飛ばしで合計するには？

上の例を踏まえて、今度は５階までの各階別備品（機種）価格合計を算出してみます。
例のように、１階、２階、３階、４階、５階の各階をみても、今回の例は規則性なくデータが入力されています。

先ほどの例では、偶数行に２階、奇数行に１階と規則正しくデータが並んでいましたが、今度はデータに規則性がなく、各階の備品データが存在するかしないかもマチマチで、規則正しく並べて整理していない表を扱います。
セルの奇数偶数行という階数の判断手法をやめて、今回は配置欄（C欄：１階～５階）のデータを使うようにします。
このC欄は階数を数値として入力してあり、１～５の数字が入っているとします。５で割りきれれば５階、１余れば１階、２余れば２階、３余れば３階、４余れば４階のデータになります。

図例の３階の合計の式を見ていきます。
まず、MOD(調べたい列の範囲,5) は目的とする行番号が返ります。その行番号を５で割った余りによって、何階のデータなのかを判別します。
３階のデータがほしいときは、MOD(調べたい列の範囲,5)=3)*単価とすれば、３階のデータであればその単価を使います。３階のデータでなければ使いません。

次に SUMPRODUCT(単価, 個数) として、求めたい３階の合計を出します。
まとめて書くと３階の計算式は、
=SUMPRODUCT((MOD(B3:B27,5)=3)*C3:C27,D3:D27)
となります。

MOD 関数の応用知識

Modulus の頭をとった MOD 関数はこのこれだけで何かが完成するということはまずありません。他の関数と組み合わせて自分に都合の良くセルを選びだしたりするのに使います。先ほどの例では、正の数だけを扱いましたが、今度は負の数を扱う例を整理してみます。
MOD 関数は「数値を除数で割ったときの余りを算出する」します。書式は「＝MOD（数値, 除数）」で、正の数字であれば、分子と分母を「,」で区切るだけです。