条件付合計計算(SUMIF)の有効活用

      2016/02/04

働く女性

業務ソフトはあくまで「実績」を管理しています。
社内業務で、顧客に対する売上予定表を作成する場合は、業務ソフトとは別に、社内で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) としてからコピーします。

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

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

条件付き書式設定の使い方

条件付き書式の設定開始

条件付き書式設定は、ホームメニューの中にあります。

WS000126

まず、書式を摘要したい範囲を選択します。

WS000127

書式ルールを設定する

選択できたら、条件付き書式アイコンをクリックして「新しいルール」を選択し、「数式を使用して、書式設定するセルを決定」を選択します。

WS000129

数式に =$D4=”予定”  と入力します。

D4はステイタスを登録する1行目のセルです。
&D4と記述しているのは、D列のみに$をつける事で、5行目以降はD5,D6,…と行に応じたセル位置を自動で判断させる為です。
$をつけないと、D4の値が「予定」の場合、選択した範囲すべてが反応する事になります。

書式を設定する

次に書式を設定します。
書式ボタンをクリックして好みの書式を設定し、OKをクリックして登録します。(ここでは文字色を青に設定します)

WS000130

設定した書式の変更・削除

設定した書式設定を変更したい場合は、条件付き書式設定のルールの管理を選択して、一覧から編集をクリックして変更できます。

WS000131

削除も同画面から実行できます。

まとめ

売上予定表等の管理帳票は、もちろん数字の内容が一番重要です。
ただ、表の見やすい、見にくいは数字を判断する上で重要なファクターです。
下の2つの表を見比べて見て下さい。

WS000133

内容はどちらも同じですが、どちらが分かりやすく見えますか?
見やすい帳票を使って、じっくり売上計画を練ってみてはいかがでしょう!

 - 未分類