Excel。複数列置きのデータを手早く合算するには、どうしたらいいの
＜SUMPRODUCT+(MOD+COLUMN関数＞
連続しているデータならば、SUM関数で簡単に合計値を算出できますが、2列おきにあるデータの合計値を算出したい場合、どのようにしたら手早く算出することができるのでしょうか。
次の帳票の場合で説明していきます。
B列とE列のそれぞれの販売金額の合計をH列に算出したいわけです。
このケースのように2か所ならば、Ctrlキーをつかうことで、容易に範囲選択でるので算出すること自体面倒というわけでもありません。
ただ、さらに多くのデータだった場合は、数式を作るのも面倒になっていきます。
このような場合、何かしらの「法則」がないのかが見つかれば、数式を作成するヒントになります。
今回は、合計したい列が、2列置きにあります。
2列置きの数値だけを合算する方法を考えいけばいいということになります。
そこで、SUMPRODUCT関数をつかうことで、解決することができます。
H3には、SUMPRODUCT関数をつかった数式を設定しました。
=SUMPRODUCT((MOD(COLUMN(B3:G3),3)=2)*B3:G3)
オートフィルで数式をコピーしています。
この数式で2列置きの数値を合算することができたわけですが、どのような仕組みなのかを説明していきます。
SUMPRODUCT関数は、PRODUCT＝「掛け算」とSUM＝「総和」が組み合わさった関数です。
MOD関数は、除算した余りを算出する関数です。何の余りを算出するのかというと、COLUMN関数。つまり列番号を除算するわけですね。
「MOD(COLUMN(B3:G3),3)」
今回は、3で列番号を除算した余りを算出させるわけです。
その結果が、
「MOD(COLUMN(B3:G3),3)=2」
2と等しいのかとします。
B列は、余り2ということで合致しますから、「TRUE」となるわけです。合致しなければ「FALSE」となるわけです。
Excelでは、「TRUE」は「1」で「FALSE」が「0」となっていますから、その値を、セルに入力されている値と乗算「*B3:G3」します。
すると、販売金額の列以外は、「0」に置換されるので、販売金額だけを合計することができるというわけです。
ただ、ちょっと動きがわかりにくいので、このような数式を確認するには、数式タブにある「数式の検証」をつかってみましょう。
途中計算が視覚として理解することができます。
