総務マガジン読んで役立つ記事・コラムできる総務のExcel術Excelで忘年会・新年会の出欠表を作るExcelで忘年会・新年会の出欠表を作る幹事の作業を助け、出欠や会費収集の状況を分かりやすく歓送迎会やプロジェクトの打ち上げ、年末年始には忘年会・新年会など、社内では懇親を目的としたさまざまな「飲み会」が行われます。出欠や会費を簡単に管理できるExcelワークシートで、幹事を担当する社員の仕事を助けましょう。年末年始には社内で大小さまざまな忘年会・新年会が行われます。した会の幹事のために、参加者や会費を管理するExcelワークシートの提供を検討してみましょう。会社の公式の申請書類という位置づけでなくても、便利なツールとして提供することで、幹事の負担を軽減できます。シート」を作成します。会社が福利厚生目的で補助金を支出する場合、総務としては参加者の一覧を把握しておきたいところですが、そのような場合の資料としても使えます。今回作成するワークシート参加予定者のリストを作成し、「出欠」および「会費受領」を入力していきます。「会費」は「役職」により差をつけて管理できるようにしています。会社から補助金が出る場合は「補助金」欄に入力しておける点と、役職の一覧の中に、会費が0となる「主賓」も加えているところがポイントです。社内の飲み会では、上役が多めに会費を支払う形にするケースが多いものです。このワークシートでは役職別に会費の金額を設定し、このようなケースに対応できるようにしています。金額は適宜変更でき、歓送迎会などの「主賓」は会費なしの扱いにもできます。セルH6~I12に役職ごとの会費の表を作っておき、参加者の「役職」はこの役職の一覧をリスト化して入力します。リストの作成方法は第33回(Excel申請書の部署名欄を「リスト」で省力化)でも解説しているので、詳しくはそちらを参照してください。「会費」はVLOOKUP関数を使って役職に応じた金額が自動的に表示されるようにして、管理を簡単にします。最後に出席者の人数と、会社から支給される補助金も含めた会費の合計金額を計算し、一人あたりの金額も求めて、どの程度の予算で店を予約すればいいかが一目で分かるようにします。(1)「役職」列にリストを作成する「役職」行のセルを選択して、[データ]タブ内の[データツール]にある[データの入力規則]をクリックします。[データの入力規則]ダイアログボックスが表示されたら、[設定]タブの[入力値の種類]で[リスト]を選択し、[元の値]をクリックしてから、[Shift]キーを押したままシート上のセルH6からH12をドラッグすることで「$H$6:$H$12」と入力します。最後に[OK]をクリックします。「会費」列で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などで出欠の管理や会費の集計を行っているはずです。正規の業務ではないため意外とノウハウが継承されにくく、毎回ワークシートをゼロから作る作業が繰り返されているかもしれません。今回解説したようなワークシートを用意しておけば、さまざまな社内のイベントで利用できます。実施状況の報告を求めたい場合は、店名や日時、会の名目などを入力する欄を加えて、入力してもらうようにしてもいいでしょう。いう人はぜひ大塚商会のExcel教室へ大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。次の記事Excelブックの個人情報や保護設定を削除まずはお気軽にご相談ください。製品の選定やお見積りなど、100万社ものお客様に支えられた多数の実績でお客様のお悩みにお応えします。お気軽にご相談ください。9:00~17:30)Webでのお問い合わせお問い合わせ*メールでの連絡をご希望の方も、お問い合わせボタンをご利用ください。総務マガジン」は、大塚IDでログインすると、さらに便利に。業務で使える書式や社内掲示用ポスターが、編集可能なデータでご自由にダウンロードいただけます。ほかにも大塚IDをご登録いただくと、eラーニングが無料で受講できたり、製品・サービスが無料でお試しいただけたり、お得なサービスが利用できます。役割とは大塚IDでログインしよう!メーカーズボイスOfficeあるあるあら・みるメディアの回廊ITここに歴史あり危機管理コラムIT用語辞典ビジネススキルアップや業務に役立つさまざまなサービスをご用意しています。