エクセルＳＵＭの計算を効率的に行う方法

質問

• エクセルのSUM関数を使用して、一番右の列の数値を自動的に計算する方法について教えてください。
• 一括計算するために、SUM関数ではなく、動的な式を使用する方法があるか知りたいです。
• エクセルの表で、合計すべきセルの数が変化する場合に効率的な計算方法を教えてください。

回答No.4

kagakusuki

回答番号:ANo.3の続きです。

　SUMPRODUCT関数は本来は配列の積の和を計算するための関数なのですが、これを応用しますと、

SUMPRODUCT((判定式1)*(判定式2)*セル範囲)

という様な形式とする事で、複数条件に該当するセルの値のみの合計値を求める事も出来ます。
　例えば、

=SUMPRODUCT((A2:A5=3)*(B3:B6>0)*C2:C5)

という数式は、パソコンの内部で

=(A2=3)*(B2>0)*C2
+(A3=3)*(B3>0)*C3
+(A4=3)*(B4>0)*C4
+(A5=3)*(B5>0)*C5

という計算が行われています。
　尚、

(A2=3)

という部分は、
A2セルの値が3である場合には、数値の1と同様に扱われ、
A2セルの値が3ではない場合には、数値の0と同様に扱われます。

SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))

という数式は、それを更に応用したものです。
　例えば、D1セルをコピーして、D4セルに貼り付けると、その部分は

SUMPRODUCT(ROW($A$1:$A4)*($A$1:$A4>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A4)),"><")=COUNTIF($A$1:$A4,"><")))

という様に変換されますが、これは

ROW($A1)*($A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A1)),"><")=COUNTIF($A$1:$A1,"><"))
+
ROW($A2)*($A2<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A2)),"><")=COUNTIF($A$1:$A2,"><"))
+
ROW($A3)*($A3<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A3)),"><")=COUNTIF($A$1:$A3,"><"))
+
ROW($A4)*($A4<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")=COUNTIF($A$1:$A4,"><"))

と同じ意味になりますから、

=1*("A社"<>"")*(1=2)
+
2*(""<>"")*(1=2)
+
3*("B社"<>"")*(2=2)
+
4*(""<>"")*(2=2)

=1*1*0+2*0*0+3*1*1+4*0*1

=3

という計算が行われます。
　文章で説明しますと、
A1～A4のセル範囲で、
A列のセルが空欄ではなく、
且つ
A1セルからそのセルまでの範囲にある、空欄ではないセルの個数が、A1～A4の範囲にある空欄ではないセルの個数と等しい
という条件を満たすセルが存在する行の行番号を合計する
という数式です。
　この条件を満たすのは、A1からそのセルまでの範囲にある、空欄ではないセルの個数が、2となる最初の行である3行目のみであるため、計算結果は3行目の行番号である3になります。

ROW()

の値は、その数式が入力されている行の行番号になりますから、

INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A4)*($A$1:$A4<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A4)),"><")=COUNTIF($A$1:$A4,"><")))&":C"&ROW())

=INDIRECT("C"&3&":C"&4)

=INDIRECT("C3:C4")

=$C3:$C4

という事になります。

補足

どうもありがとうございます。
申し訳ありません。ここでつまづいてしまいました。
=1*("A社"<>"")*(1=2)
+
2*(""<>"")*(1=2)
+.................

のところです。
(1=2)とあるのですが、（このあとも1＝2）（2＝2）
と続きますが、この前者の数字（＝より前の数字）がどうやって出てきたのかがわかりません。申し訳ありません。
もう少し考えてみますが、もしこちらの質問に気づかれましたら教えてください。