Excelの数式

質問

Excelの数式で

=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=1)*$E$1:$E$10)

という数式があったのですが、数式の中身の意味がわかりません。
5つの分類があってそれぞれの合計を出す数式です。
どなかたこの数式の解説をお願い致します！

回答No.2

matsu_jun · ベストアンサー

aspiring1111 さん、こんにちは

まずは結果を先に言葉で説明します。
「E1セルからE10セルに入っている10個の値のうち、行番号を5で割ったときの余りが
1である行(すなわち1行目と6行目)の値を合計する。すなわちE1セルとE6セルの和である。」

さて、内容を詳しくひも解いてみます。
中々難しい数式ですが、まずはExcelの下の2つの機能について理解しましょう。
　1. Excelの数式は、配列を取り扱うことができるものがある。
　2. 数式の真偽は、1と0で置き換えることができる。

まずは1から
　例えばA1セルに2、B1セルに3が入力されているとき、C1セルに =A1*B1と入力すれば
　2×3 すなわち 6 が表示されるはずです。
　では、A1セルに2、A2セルには3、B1セルに3、A2セルに4 が入力されているとき
　C1セルに =A1:A2*B1:B2 と入力した場合はどうなるでしょうか。
　普通に試せば、C1セルに A1×B1の計算結果である6が表示されます。
　しかしこの状態で一度C1とC2セルをマウスで囲み、さらにF2キーを押してC1セルを
　編集可能とし、その状態でCtrl と Shift キーを押しながら、Enterキーを押すと
　C1セルには6、C2セルには、A2×B2 すなわち 12 が表示されます。
　実は A1:A2*B1:B2 という式は、A1×B1 と、A2×B2 の2つの結果を持っているのです。
　1つの変数や式で複数の値や結果を持つことを、配列と呼びます。
　上の例では、6 と 12 という2つの値を持つことが分かりましたが、これを表記する際には、
　{6,12} と表記します。

次に2を
　A3セルに0を入れてください。次に、A4セルに =NOT(A3) と入力し、Enterキーを押せば
　「TRUE」と表示されます。さらに、A5セルに =A4*1 と入力し、Enterキーを押してください。
　A5は1になるはずです。
　ここまで試したら、A3セルを1と書き換えます。すると、A4セルは FALSE に、A5セルは0になります。
　TRUE は、本当のこと 数学の用語では「真」、FALSE は、間違っていること 数学の用語では「偽」です。
　NOT(値) は、値の逆を返す数式ですので、TRUEが1、FALSEが0と考えると、上の式の辻褄が合います。
　セルA3が0 ⇒ セルA3はFALSE ⇒ NOT(A3)は、NOT(FALSE)と読み替えが可能
　⇒ FALSE(偽) の逆は、TRUE(真) ⇒ だからセルA4はTRUEとなる。
　セルA4がTRUE ⇒ セルA4は1 ⇒ A4*1 は、1*1 と読み替えが可能
　⇒ 1×1＝1 ⇒ だからセルA5は1となる。

この2つが分かれば、あとは件の数式を、カッコの内側から読み解いていくだけです。

=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=1)*$E$1:$E$10)

まずは ROW($E$1:$E$10) について、
　ROW(セル) は、そのセルの行番号を返す数式ですので、ROW($E$1)なら結果は1ですが、
　ROW は、配列を取り扱うことが可能ですので、ROW($E$1:$E$10) はそれぞれの行番号、すなわち
　{1,2,3,4,5,6,7,8,9,10} という10個の値を持つこととなります。 ・・・(1)

次にMOD(値1,値2)ですね
　これは、値1を値2で割ったときの余りを返す数式です。例えば、MOD(5,3) なら、
　5を3で割ったときの余り すなわち 2 が返ってくることになります。
　では、件の数式中のMODを解釈してみましょう。
　MOD(ROW($E$1:$E$10),5) これは、ROW($E$1:$E$10) を 5で割ったときの余りを返します。
　ROW($E$1:$E$10) は、(1)から、{1,2,3,4,5,6,7,8,9,10} ですので、それぞれを5で割ったときの余り、
　すなわち {1,2,3,4,0,1,2,3,4,0} という10個の値を持つことが分かります。 ・・・(2)

では、MOD(ROW($E$1:$E$10),5,)=1 はどうでしょうか？
　この式の左辺は、(2)から、{1,2,3,4,0,1,2,3,4,0} の10通り存在することが分かっています。
　それに対して右辺は1で固定です。
　左辺が1のときは、MOD・・・ = 1 が 1=1 となり、数式として正しいことが分かります。
　すなわち、この場合の結果は、真(TRUE) となります。
　左辺が2のときは、MOD・・・ = 1 が 2=1 となり、数式として誤っていることが分かります。
　すなわち、この場合の結果は、偽(FALSE) となります。
　つまり、{1,2,3,4,0,1,2,3,4,0} 10個の値に対する計算結果は、
　{TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE} という10個の結果となります。
　最初に説明した 2 の機能から考えると、
　{1,0,0,0,0,1,0,0,0,0}
　という10個の値を持つことが分かりました。 ・・・(3)

最後に SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=1)*$E$1:$E$10) を考えます。
　SUMPRODUCT(配列1 * 配列2) は、2つの配列の同じ要素を掛け合わせ、さらにその結果を足し算するという
　数式となります。少し分かりにくいでしょうから、下に例を示します。
　例えばセルA1に1、A2に2、A3に3、B1に4、B2に5、B3に6が入っていたとしましょう。
　この時、SUMPRODUCT(A1:A3*B1:B3) の結果は、
　A1*B1 + A2*B2 + A3*B3　すなわち、1×4＋2×5＋3×6 ＝ 4＋10＋18 ＝ 32 となります。
　では、この式に、(3)の結果を反映させてみましょう。
　SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}*$E$1:$E$10)
　となりました。この結果は
　= 1×$E$1 ＋ 0×$E$2 ＋ 0×$E$3 ＋ 0×$E$4 ＋ 0×$E$5
　＋1×$E$6 ＋ 0×$E$7 ＋ 0×$E$8 ＋ 0×$E$9 ＋ 0×$E$10
　すなわち $E$1 ＋ $E$6 となります。

長文のため分かりにくいところも多いと思いますが、ご理解いただけましたでしょうか。
更なる疑問点があれば、お礼欄や補足欄にて記載いただければフォローいたします。

お礼

詳しい解説ありがとうございます！
複雑で理解が難しいですが頑張ってみます・・・。

回答No.1

chie65536（@chie65535）

行数を５で割った余りが１の行のみ、合計する。

つまり、Ｅ列の１行目と６行目の合計を計算する。

=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=1)*$E$1:$E$10)
の他に
=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=2)*$E$1:$E$10)
=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=3)*$E$1:$E$10)
=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=4)*$E$1:$E$10)
=SUMPRODUCT((MOD(ROW($E$1:$E$10),5)=0)*$E$1:$E$10)
と言う、似ている数式もあった筈。

お礼

解答いただきありがとうございます！