役立つ! 総務マガジン

Excelで忘年会・新年会の出欠表を作る

幹事の作業を助け、出欠や会費収集の状況を分かりやすく

歓送迎会やプロジェクトの打ち上げ、年末年始には忘年会・新年会など、社内では懇親を目的としたさまざまな「飲み会」が行われます。出欠や会費を簡単に管理できるExcelワークシートで、幹事を担当する社員の仕事を助けましょう。

[2019年12月 9日公開]

飲み会が集中する季節に便利な管理シートを利用

年末年始には社内で大小さまざまな忘年会・新年会が行われます。こうした会の幹事のために、参加者や会費を管理するExcelワークシートの提供を検討してみましょう。会社の公式の申請書類という位置づけでなくても、便利なツールとして提供することで、幹事の負担を軽減できます。

今回は以下のような、さまざまな飲み会で使える「懇親会等出欠管理シート」を作成します。会社が福利厚生目的で補助金を支出する場合、総務としては参加者の一覧を把握しておきたいところですが、そのような場合の資料としても使えます。

今回作成するワークシート

参加予定者のリストを作成し、「出欠」および「会費受領」を入力していきます。「会費」は「役職」により差をつけて管理できるようにしています。会社から補助金が出る場合は「補助金」欄に入力しておける点と、役職の一覧の中に、会費が0となる「主賓」も加えているところがポイントです。

役職により参加費に差を付け、一人あたりの金額も算出

社内の飲み会では、上役が多めに会費を支払う形にするケースが多いものです。このワークシートでは役職別に会費の金額を設定し、このようなケースに対応できるようにしています。金額は適宜変更でき、歓送迎会などの「主賓」は会費なしの扱いにもできます。

セルH6~I12に役職ごとの会費の表を作っておき、参加者の「役職」はこの役職の一覧をリスト化して入力します。リストの作成方法は第33回(Excel申請書の部署名欄を「リスト」で省力化)でも解説しているので、詳しくはそちらを参照してください。

第33回:Excel申請書の部署名欄を「リスト」で省力化

「会費」はVLOOKUP関数を使って役職に応じた金額が自動的に表示されるようにして、管理を簡単にします。最後に出席者の人数と、会社から支給される補助金も含めた会費の合計金額を計算し、一人あたりの金額も求めて、どの程度の予算で店を予約すればいいかが一目で分かるようにします。

(1)「役職」列にリストを作成する

「役職」行のセルを選択して、[データ]タブ内の[データツール]にある[データの入力規則]をクリックします。[データの入力規則]ダイアログボックスが表示されたら、[設定]タブの[入力値の種類]で[リスト]を選択し、[元の値]をクリックしてから、[Shift]キーを押したままシート上のセルH6からH12をドラッグすることで「$H$6:$H$12」と入力します。最後に[OK]をクリックします。

VLOOKUP関数で役職に応じた会費を表示

「会費」列でVLOOKUP関数を使い、役職に応じた金額を表示するようにします。ただし、VLOOKUP関数だけでは「役職」列が空白だったときに「#N/A」というエラーが表示されてしまうため、IFERROR関数を組み合わせて、エラーの場合は何も表示しないようにします。

VLOOKUP関数の構文
VLOOKUP(検索値,範囲,列番号,検索の型)
IFERROR関数の構文
IFERROR(値,エラーの場合の値)

(1)VLOOKUP関数を入力する

「会費」列の先頭であるセルD4を選択し、「=VLOOKUP(C4,$H$6,$I$12,2,FALSE)」と入力します。これは「セルC4の内容を、セルH6~I12の表から検索し、該当する行の2列目の値、つまり役職に対応する会費を表示する」という意味になります。

(2)役職に対応した会費が表示された

VLOOKUP関数を入力すると、「役員」に対応した会費「10,000」が表示されました。

(3)VLOOKUP関数にIFERROR関数を組み合わせる

続けてセルD4を編集し、IFERROR関数を書き加えます「=」の後に「IFERROR(」と入力し、VLOOKUP関数の最後に「,"")」と入力しましょう。セルD3の数式全体では「=IFERROR(VLOOKUP(C4,$H$6,$I$12,2,FALSE),"")」となり、「VLOOKUP関数がエラーだった場合は何も表示しない(『""』は空白の文字列を表す)、エラーでない場合はVLOOKUP関数の結果を表示する」という意味になります。

(4)数式をコピーする

セルD4に入力した数式を「会費」列全体にコピーしましょう。セル右下のフィルハンドル(小さな四角形)を下にドラッグします。

(5)数式がコピーされ、全員の会費が表示された

数式がコピーされ、それぞれの役職に応じた会費の金額が表示されました。「役職」列が空白の行は、IFERROR関数により何も表示されません。

「出欠」と「会費受領」もリストから入力できるようにする

「出欠」列と「会費受領」列もリストから入力できるようにします。これらの項目の選択肢は簡単なので、[データの入力規則]ダイアログボックスで選択肢となる[元の値]を直接入力します。

「出欠」列は「出席」または「欠席」、「会費受領」列は「未」「済」または「不要」から選べるようにします。

(1)「出欠」列にリストを作成する

「出欠」列のセルを選択して、[データ]タブ内の[データツール]にある[データの入力規則]をクリックします。[データの入力規則]ダイアログボックスが表示されたら、[設定]タブの[入力値の種類]で[リスト]を選択し、[元の値]に「出席,欠席」と入力ます。最後に[OK]をクリックします。

(2)「会費受領」列にリストを作成する

続けて、同様に「会費受領」列内のセルを選択して[データの入力規則]ダイアログボックスを表示します。[設定]タブの[入力値の種類]で[リスト]を選択し、[元の値]に「不要,未,済」と入力して[OK]をクリックします。

参加者の人数と参加者の会費を集計する

以上で出欠管理の部分は完成しました。次に、参加人数や会費総額を計算します。参加人数は検索条件に合致した数を数えるCOUNTIF関数を使い、「出欠」列で「出席」の数を数えます。

会費総額は、「出欠」列で「出席」と答えた人の会費を合計し、さらに会社からの補助金分を加えます。こちらは、検索条件に合致した数値の合計を求めるSUMIF関数を使います。

COUNTIF関数の構文
COUNTIF(範囲,検索条件)
SUMIF関数の構文
SUMIF(範囲,検索条件,合計範囲)

(1)参加人数の合計を求めるCOUNTIF関数を入力する

参加人数の合計が入るセルI14を選択し、「=COUNTIF(E4:E17,"出席")」と入力します。これは「出欠」列の「出席」と入力されたセルを数える、という意味になります。

(2)参加人数が表示された

セルI14に参加人数が表示されました。

(3)会費総額を求めるSUMIF関数を設定する

会費総額が入るセルI15を選択し、「=I3+SUMIF(D3:D17,"出席",C3:C17)」と入力します。セルI3は、補助金の額が入力されているセルです。SUMIF関数は「『出欠』列の値が『出席』なら、同じ行の『会費』列の値を合計に加える」という意味になります。

(4)会費総額表示された

会費総額が表示されました。

(5)一人あたりの金額を計算する

最後に、会費総額を参加人数で割り、一人あたりの金額を計算します。セルI16を選択し、「=I15/I14」と入力します。

(6)一人あたりの金額が計算された

一人あたりに充てられる金額が計算できました。

見えにくいムダな作業を省いて飲み会の準備も時短

何らかの飲み会があるたびに、幹事はExcelなどで出欠の管理や会費の集計を行っているはずです。しかし、正規の業務ではないため意外とノウハウが継承されにくく、毎回ワークシートをゼロから作る作業が繰り返されているかもしれません。

今回解説したようなワークシートを用意しておけば、さまざまな社内のイベントで利用できます。実施状況の報告を求めたい場合は、店名や日時、会の名目などを入力する欄を加えて、入力してもらうようにしてもいいでしょう。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る