Excel。一列置きのデータを合算したいけど、簡単に算出する方法はないかな？
＜SUMIF関数・MOD関数・COLUMN関数＞
やりたいことは簡単に思っても、実際にExcelでどうやったらいいのか？効率的なのか？考えるしまうことは結構あります。
今回は、前年と今年の売上高が並んでいる表があります。
H列やI列は、年ごとに合算値を算出しています。
さて、この合算値を算出するのに、効率的に行う方法はどうしたらいいのでしょうか？
合算といえば、SUM関数ですね。
H2に
=SUM(B2,D2,F2)
という数式を作成する。これも正解ですね。
ただ、SUM関数を使うのでもいいのですが、一列置きにデータがありますから、選択するにしても入力するとしても、面倒ですし、ミスを発生する危険性が高くなってしまいます。
今回は、このデータ量なのでいいですが、ボリュームが増えた場合は、さらに大変になることは、簡単に想像できます。
ポイントは、「一列置き」です。
2019年と2020年と判断できれば、条件付き和算のSUMIF関数を使うことができます。
そこで、注目するのは、列番号。
その列番号を2で除算すれば、余りがあれば『奇数』。
余りがなければ『偶数』と一列置きに分ける数値を算出することができます。
合算値を算出する前の状態の表をつかって、算出していきます。
B8には、次の数式を設定しました。
=MOD(COLUMN(),2)
COLUMN関数は、列番号を算出する関数です。
B列なので、2という値が算出されます。
MOD関数は、除算した余りを算出することができる関数です。
2で除算すれば、奇数偶数を確認することができます。
B列は2なので、2÷2で余りは「0（ゼロ）」ですね。
この数式をフィルハンドルをつかって、コピーします。
これで、奇数偶数を判断することができました。
つまり、2019年と2020年の列を判断するためのツールを得ることができたわけです。
あとは、SUMIF関数をつかうことで、簡単に2019年と2020年の合算値を算出できるわけです。
H2の数式は、
=SUMIF($B$8:$G$8,0,$B2:$G2)
SUMIF関数のダイアログボックスで確認してみましょう。
範囲は、$B$8:$G$8
フィルハンドルをつかって、数式をコピーする（オートフィル）ので、絶対参照を設定しておきます。
検索条件は、0（ゼロ）。2019年が0（ゼロ）2020年が1ですね。
合計範囲は、$B2:$G2
列だけ固定した複合参照にすることで、数式をコピーしたあとに数式を修正する必要がなくなります。
これで、2019年2020年の合算値を算出することができました。
これで完成といえば完成なのですが、区別をするための、8行目の数値が見えたままですね。
カッコ悪いので、0と1を表示しないようにしていきます。
間違えても、削除してはダメですね。
削除したら、SUMIF関数の検索条件が機能しませんので、元も子もありません。
また、文字の色を白色にするというのも、スマートじゃないので、表示形式のユーザー定義をつかって、非常時にしていきます。
B8:G8を範囲選択して、セルの書式設定ダイアログボックスを表示します。
表示形式の分類をユーザー定義にして、種類に、「;;;（セミコロン×3）」と入力してOKボタンをクリックします。
これで、非表示にすることができました。
「;;;（セミコロン×3）」にすると、文字を非表示にすることができますので、覚えておくと重宝します。
これで、完成しましたね。
まぁ、そもそも論として、データベースにしておけば、ピボットテーブルで簡単に算出できるのですが、帳票で管理している会社も多いので、様々な手法を知っていて損はありませんね。
