人気ブログランキング | 話題のタグを見る
ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

Excelでつくるシミュレーション  歓送迎会収支計算表(1)

Excelというアプリケーションの基本的な動作は、次の工程の連続となります。

・数値を入力する
・計算する
・結果を表示する

 人間が電卓で計算するとかなりの時間がかかる処理でも、Excelを使うと一瞬で終わってしまいます。そこで、Excelの真骨頂ともいえる使い方がシミュレーションでの利用です。シミュレーションは、前提となる数値をいろいろと変えることによって、結果がどう変化していくかを確認するために用います。
 今回は、歓送迎会の収支計算から会費をいくらに設定したらよいかをシミュレーションする表を作成してみます。

 まず、表をつくるための前提となるルールを確認します。

(歓送迎会のルール)
1.女性会費は男性マイナス千円とする。
2.退職者・転出者・着任者は会費無料。
3.記念品は退職者1人あたり5,000円とする。
4.赤字となった場合幹事が自腹を切ること。
5.欠席者の氏名を取り消し線で消すこと。

このルールに基づいて作成した表が次の図になります。

Excelでつくるシミュレーション  歓送迎会収支計算表(1)_c0136904_22293230.jpg



 ここでは、出欠者名簿と収支計算のための表の2種類に分けて作成してみました。入力する欄は薄い黄色で着色してある項目で、「氏名」「性別」「出欠」(以上「名簿」)と収支計算の前提条件となる「前回からの繰越金」「男性会費」「記念品単価」(退職者へのプレゼントの予算)「コース単価」(飲食店への1人あたり支払額)となります。
 これ以外の項目は自動計算されるようになっています。つまり、作成された名簿に基づいて、「男性会費」「記念品単価」「コース単価」の須知をそれぞれ変更すると収支計算がシミュレートされ、次回繰越金(いくら残るか)が計算されます。このルールでは、予算オーバーした場合追加徴収が認められていないので、幹事が自腹を切らなければなりません。そこで、なんとしても黒字にしなければならないことになります。

 実は、このシミュレーションには表作成をわかりやすくするために、いくつかの作業用セルが設けられていますが、非表示となっています。作業用セルを表示させると次の図のようになります。(クリックすると拡大表示されます。)

Excelでつくるシミュレーション  歓送迎会収支計算表(1)_c0136904_4575064.jpg

 


 以下、順番にどのような計算式が入力されているかをみていきます。

(セルE5の計算式)会費を表示させる
=IF(D5="○",VLOOKUP(C5,$L$5:$M$6,2,FALSE),0)

 出席者のうち会費を徴収できるのは「出欠」欄が”○”となっている人です。欠席者は”×”となっていますし、出席者でも「退職者」「転出者」「着任者」からは会費を徴収することができません。このように、1つの項目欄に入力する内容は「互いに排他的」(1つのセルには1つの内容しか入れられず、2つ以上入力することはできない。というもの)である必要があります。これは当たり前といえば当たり前ですが、結構大事なことなので覚えておくとよいでしょう。
 さて、会費を徴収できる人のうち男性と女性では金額が異なるのですから、性別に応じて会費を表示させるようにします。ここでは、VLOOKUP関数を使いました。その参照先である範囲L5:M6には、男性と女性の会費が表示されていますが、これは条件設定で決められた数値(セルF6とF7)を読み込むようになっています。
 また計算式をコピーするために、参照先範囲は $L$5:$M$6 というふうに絶対参照にしています。(こうするとコピーしても範囲が変更されなくなります。)
 さらに、検索の型ではFALSE を指定しています。というのも、検索する値が数値ではなく「男」か「女」かという文字列ですから、TRUEにしたり省略するとエラーになるからです。

 なお、VLOOKUP関数の代わりに、次のようにIF関数を入れ子にすることもできます。

=IF(D5="○",IF(C5="男",$P$6,IF(C5="女",$P$7,0)),0)


(セルF5の計算式)女性出席者で”○”かどうかの検証
F列からJ列は、出席者の男女別内訳を計算させるために設けている作業用セルです。収支計算に影響を与えるものではなく、シミュレーションとは直接関係ありませんから、男女別出席人数を知る必要がなければこの欄は不要です。
 ただし、Excelでつくるデータベースを集計する際に参考になる考え方がいくつも盛り込まれているので、興味のある方はおつきあいください。
 セルF5(出席者が女性かどうかを検証するセル)には次の計算式が入力されています。

=IF($C5="女",1,0)*IF($D5=F$4,1,0)

 IF関数とIF関数を掛け合わせるというのはあまり見慣れない式かもしれません。左側のIF関数では「性別が女性であれば1を、そうでなければ0を返す」ことになっています。また右側のIF関数では「出欠欄がセルF4(ここに入力されているのは”○”です)と同じであれば1を、そうでなければ0を返す」というものです。
 この2つのIF関数を組み合わせると、両方のIF関数の結果がともに1であった場合(女性で”○”)のみ結果は1となります。どちらか一方でも0であれば計算結果は0になります。
 なぜこんなことをするのかというと、女性の出席者は「○」「退職」「転出」「着任」の4種類のどれか1つになるからです。それぞれの条件に該当する人数を数えるには、その条件(たとえば、女性で○、という条件)に合致する場合、1という値を設定し、全体でその1を合計すると、その条件に該当する人数がわかることになります。(セルF31がその合計値です。)

 なお、計算式の中でセルの番地指定として「$C5」や「F$4」という書き方をしています。絶対参照がセル番地の列名と行番号の両方に「$」がつけられるのに対し、ここではどちらか片方にだけ「$」がつけられています。これを混合参照といい、計算式をコピーした祭に、変わってほしいものと変わると困るものを分けるときに用います。ちなみに、この式を右方向にコピーするとどうなるかというと、

(コピー前)
=IF($C5="女",1,0)*IF($D5=F$4,1,0)

(コピー後)
=IF($C5="女",1,0)*IF($D5=G$4,1,0)

 また、縦方向にコピーすると、

(コピー前)
=IF($C5="女",1,0)*IF($D5=F$4,1,0)

(コピー後)
=IF($C6="女",1,0)*IF($D6=F$4,1,0)

 このようになり、単純にコピー&ペーストするだけよいことがおわかりいただけるかと思います。

 また、このような考え方(1×1=1,1×0=0)を応用して作成したのが次の計算式です。

=IF(($C5="女")*($D5=F$4),1,0)

 この式の場合、条件がTRUEとなるのは、($C5="女")がTRUEであり、しかも($D5=F$4)がTRUEの場合のみです。なぜなら、TRUE×TRUE=TRUEであり、TRUE×FALSE=FALSEとなり、FALSE×FALSE=FALSEとなるからです。(というのは、TRUE=1,FALSE=0だから)

 ゆえに、この式は次の計算式と同じ意味を持ちます。
=IF(AND($C5="女",$D5=F$4),1,0)

 したがって、この3種類のうちどれを選んでも同じ結果となります。

(セルG5の計算式)女性出席者で退職者かどうかの検証
=IF($C5="女",1,0)*IF($D5=G$4,1,0)

(セルH5の計算式)女性出席者で転出者かどうかの検証
=IF($C5="女",1,0)*IF($D5=H$4,1,0)

(セルI5の計算式)女性出席者で着任者かどうかの検証
=IF($C5="女",1,0)*IF($D5=I$4,1,0)

(セルJ5の計算式)男性の場合と欠席者の場合は0となります
=SUM(F5:I5)

(セルC31の計算式)営業部の総人数
=COUNTA(C5:C30)

(セルD31の計算式)出欠欄記入済みの件数-記入漏れをチェックするため
=COUNTA(D5:D30)



 以下は、右側の集計表の計算式です。

Excelでつくるシミュレーション  歓送迎会収支計算表(1)_c0136904_2231479.jpg



(セルP7の計算式)女性会費は男性マイナス千円だから
=P6-1000

(セルP12の計算式)営業部の総人数
=COUNTA(D5:D30)

(セルP13の計算式)
=COUNTIF(D5:D30,"×")欠席者の下図

(セルP14の計算式)男性出席者:出席者合計から女性出席者を引いたもの
=P16-P15

(セルP15の計算式)女性出席者の人数
=SUM(J5:J30)

(セルP16の計算式)出席者合計(総人数-欠席者)
=P12-P13

(セルP17の計算式)退職者の人数
=COUNTIF($D$5:$D$30,P17)

(セルP20の計算式)退職者・転出者・着任者の合計
=SUM(Q17:Q19)

(セルP23の計算式)前回からの繰越金
=Q5

(セルP24の計算式)会費合計
=E31

(セルP25の計算式)収入合計:前回から繰越金+会費合計
=SUM(Q23:Q24)

(セルP26の計算式)飲食費合計:コース単価×出席者人数
=Q9*Q16

(セルP27の計算式)記念品購入額:記念品単価×退職者人数
=Q8*Q17

(セルP28の計算式)支出合計:飲食費合計+記念品購入額
=SUM(Q26:Q27)

(セルP29の計算式)次回繰越金:収入-支出
=Q25-Q28


 このようにして作成した表は、条件設定を変えると収支結果(次回繰越金)が変わっていくことになります。
なお、Excelには、「次回繰越金を1万円確保したい場合、会費をいくらに設定したらいいか?」を簡単に求める機能があります。次回は、この「ゴールシーク」という機能について書いてみたいと思います。
by T_am | 2011-06-18 22:38 | Excel のあの手この手

by T_am