一歩進んだEXCEL売上集計表の作成方法 | 条件付合計計算(SUMIF)の有効活用

働く女性

業務ソフトはあくまで「実績」を管理しています。
社内業務で、顧客に対する売上予定表を作成する場合は、業務ソフトとは別に、社内でEXCELを使用して管理するケースがほとんどです。
これらの表を作成する場合、合計計算が基本となりますから、ほぼ誰でも画ご存知のSUM関数を利用しますが、実は、条件付合計計算(SUMIF)を利用すると、同じ集計表1つで多方面からの集計が簡単にできるようになります。

売上予定表の枠組例

売上予定表の枠組例

下記は顧客別月別の売上予定表です。
一般的予定表でも、確定と予定を管理したいため、ステイタスという項目をD列に用意しました。

作り上げる売上表のイメージ

WS000121

項目の配置と集計項目

・縦軸…顧客＆案件
・横軸…月別売上額

を並べ、①上半期合計部分には案件毎の合計、下段には②確定案件の合計、③予定案件の合計、④確定＋予定の合計を集計します。

誰でも知っている SUM関数で単純に合計計算

案件毎の4月～9月の上半期売上合計はSUMを使って簡単に求めることができます。

例）セルK4に=SUM(E4:J4)　を記入しそのまま5行～10行目にコピーすれば完了です。

WS000122

SUMは大変で危険

下段②③の合計も、単純にSUMを使い計算することができます。
しかし、SUMを使いステイタスごとの合計を計算する場合、表を作る作業者の目で、どれが予定でどれが確定なのかD列をいちいち見ながら、それに該当する数値の合計式をつくることになります。
本表はたまたま上段3行 AAA社からCCC社までが確定で、下段全てが予定なので、SUMを使って目で確認しながらの作業も簡単ですが、もし万が一、これが飛び飛びだったり、顧客や物件がもっと多い場合は大変な作業になるうえ、ミスも犯しやすいことになります。

条件に合致した項目だけを合計してくれるSUMIF

SUMIF関数を使ってみる

それでは、SUMIF関数を使って各月の合計を「確定」と「予定」に分けて集計してみます。
SUMIF関数は、指定された条件に合致するセルの値を合計する関数です。記述の方法は、=SUMIF(検索範囲,検索条件,合計範囲)　です。

では、4月（E列）の「確定」の合計を11行目に作成します。

WS000137

数式に登録する値は以下のとおりです。

・検索範囲…「ステイタス」のあるE列の4～10行目です。
・検索条件…「確定」が登録されたC11です。
・合計範囲…　4月の売上ですので、E列の4～10行目です。

数式は
=SUMIF($D$4:$D$10,C11,E4:E10)
となります。
検索条件は”確定”と登録しても構いませんが、やはりセルを複写する事を考え、C11を指定しC11の値を「確定」とします。

同様に予定の合計を12行に作成しますが、合計式を作成したE11をそのままE12にコピーすればOKです。

セルの数式に＄をつける意味

検索範囲に＄をつけとセル位置が固定され、計算式をコピーした際に相対的に値が変化するのを避ける事ができます。

5月以降の列にも数式をコピーして表を完成させるのですが、ここで注意しないと行けないのは、検索条件である「C11」「C12」には＄がついていませんので、そのままコピーすると値が相対的に変化し、D11,E11…とずれてしまいます。

コピーする前に検索条件にも＄をつけて　=SUMIF($D$4:$D$10,＄C＄11,E4:E10)　としてからコピーします。

セル値によって書式設定を変えられる条件付き書式

数式が完成し、すべての合計セルが作成できたら表は完成ですが、予定と実績が同じ黒色表示なので少し見づらいですね。
ここで、表を見やすくする為に予定行の文字色を青色に変更して見ましょう。
書式設定は、セルのプロパティで変更できますが、ステイタスの値は変化するので、その都度初期設定を変更するのはつらいですし、間違いも起きそうです。
そこで、セルの値を判断して自動的に書式設定を変更できる、条件付き書式設定を使用します。