excel-mod-sumproduct

Excel Windows ソフトウェア

[Excel]1つ飛ばしでセルを合計する小ネタ

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

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

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

使用する関数は

  • SUMPRODUCT
  • MOD
  • ROW

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

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

Excel の SUMPRODUCT 関数の復習

Excel の SUMPRODUCT 関数 は掛け算と足し算をまとめて行う関数です。単価*個数で合計を出すときに SUMPRODUCT 関数で一気に行うよく使いました。よく見る例は以下のようなものです。
Excel SUMPRODUCT関数
例では単価の列(G列)と個数の列(H列)を選択し、掛け合わせて合計を G20 のセルに出しています。
このような使い方だけだとホントのところ、使っている本人だけが気持ちいいだけで、イマイチな独りよがり感があります。

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

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

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

Excel の ROW 関数の復習

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

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

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

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

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

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

Excel 各階別合計

上の例を踏まえて、今度は5階までの各階別備品(機種)価格合計を算出してみます。
例のように、1階、2階、3階、4階、5階の各階をみても、今回の例は規則性なくデータが入力されています。

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

Excel SUMPRODUCTsとMOD関数

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

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

MOD 関数の応用知識

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

〔オマケ〕MOD 関数のちょっとした注意点

MOD 関数 そのものは、正の値を扱うときは大した問題はないのですが、負の値を扱うときは少し注意が必要です。
MOD 関数は INT 関数で書き換えることができます。

MOD 関数の書き換え


MOD(数値, 除数) = 数値-除数*INT(数値, 除数)

と書き換えることができます。
というより、「数値-除数*INT(数値, 除数)」をまとめたものが MOD 関数です。この式からマイナスの値の時にどうなるかは見えてきます。

  1. 正の数を扱うときの例:MOD(19,13)=6
  2. 負の数を扱うときの例:MOD(-19,13)=7
  3. 負の数を扱うときの例:MOD(19,-13)=-7

負の数を処理するときのMOD 関数ポイント


負の数で除したときだけ、マイナスの値が返る

ちょっと、INT 関数を復習しておきましょう。INT 関数は「指定した数値を超えない最大の整数を返す」ものですが、正の数の場合は「0に近い数字を返す」、負の数の場合は「0から遠ざかる数字を返す」関数です。
19÷13
正の値だけを扱うときは、誰もが予想する値が返るのですが、負の値を扱うときは直感とは異なる値が返ります。

  • INT(19/13)≓INT(1.46157)=1
  • INT(-19/13)=INT(19/-13)≓INT(-1.46157)=-2

図の数直線で考えると、小さい値の方に丸められるということがわかりやすいと思います。
単に間に0を挟むので、言葉にすると間違いやすいだけですね。
以下で、計算式でどう処理しているかをみておきましょう。
19÷13

正の数を扱うときの例:MOD(19,13)=6

これは、19=13*1+6 (1が商、6が余りです)。だから6が返ります。

負の数を扱うときの例:MOD(-19,13)=7

これは、-19=13*(-2)+7 (-2が商、7が余りです)。だから7が返ります。

負の数を扱うときの例:MOD(19,-13)=-7

これは、19=-13*(-2)-7 (-2が商、-7が余りです)。だから-7が返ります。


関連記事
bye vlookup
[Excel]サヨナラ VLOOKUP、これからステップアップ

Excel の初級心者脱出ネタです。Excel を簡易データベースとして使うときに、すぐに思いつくのが VLOOKUP ...

続きを見る


関連記事
エクセル ランダム
[VBA]Excel でランダム抽出するマクロ

かつて予想外に速攻作成に悩んだのが、Excel VBA で一覧からの無作為(ランダム)抽選です。ランダムは追求すると難し ...

続きを見る

-Excel, Windows, ソフトウェア
-, , , ,

© 2020 ネーテルス