いまさら聞けないExcelの使い方講座

【Excel】グループ旅行費の割り勘が面倒!エクセルで立替金の精算などの複雑な処理を簡単に済ませる方法

せっかくの旅行なのに旅費の精算でイライラしたくない

 友人や仕事仲間とのグループ旅行は楽しいものですが、最後に旅費を精算する段階になって、計算がうまくいかずにもめてしまったことはありませんか。複数の人がホテル代やレンタカー代などの異なる費用を立て替えていたり、旅行の間も各々がさまざまに支払ったりしていると、最後にまとめて精算するのは結構面倒ですよね。楽しい旅行のはずなのに、お金のことで気分が台無しになってしまうことは避けたいですよね。

 そこで今回は、Microsoftが無料で公開しているExcelのテンプレートの中から、面倒な旅費の精算を自動で行ってくれる「旅のしおり(割り勘 計算機付き)」というテンプレートを取り上げて使い方を解説します。飲み会やランチ会などで割り勘するような場合は、スマホの計算機などで十分ですが、立替金などを考慮しなくてはならない場合、このテンプレートは大変便利です。また、このテンプレートには精算に必要な計算機能はすでに組み込まれているので、使用者は旅行に参加するメンバーや支払った金額などの情報を入力するだけでよく、あとはExcelが計算をしてくれます。

 ではさっそく使い方を見てみましょう。

テンプレートを探してダウンロードする

 Microsoftは、ExcelなどのOffice製品で使えるさまざまなテンプレートを無料で公開しています(①)。ここから、便利なテンプレートをダウンロードすることができます。

 テンプレートを探すには、①の画面を下方向にスクロールすると表示される検索ボックスに直接キーワードを指定して探したり、「季節のイベント」「仕事」「家庭/趣味」などのテーマから探したりできます。

 今回は、「旅のしおり(割り勘計算機付き)」というテンプレートを使用します。検索ボックスに「旅のしおり」「割り勘」「計算機」(②)などと入力して検索するとヒットします(③)。テンプレート名(④)をクリックして次に進みます。

 以降は、画面に表示される指示どおりに操作してテンプレートをダウンロードします。ダウンロードが完了したらテンプレートを開いてみましょう。

「旅のしおり」テンプレートの構成

 ダウンロードした「旅のしおり」テンプレートを開いてみます。「23127_shiori_travel.xltm」(①)という名前でダウンロードされるので、ダブルクリックして開きます。

 インターネットから入手したファイルを保護ビューのままにしておくことを推奨するメッセージバーや、セキュリティの警告についてのメッセージバーが表示されることがありますが、承認してボタンをクリックしていくと、ファイルを開くことができます。

 「旅のしおり」テンプレートには、複数のシート(②)が保存されています。それぞれのシートについて少し見ていきましょう。先頭にあるのは[ガイド]シート(③)で、このテンプレートの使い方が記載されています。

 [スケジュール][行き先詳細][チェックリスト]のそれぞれのシートには、旅行の詳細や持ち物、ToDoリストなどを入力することができますが、ここでは詳細の解説はしません。

 今回解説するのは、[【入力用】割り勘計算][【集計用】割り勘計算][メンバー連絡先]シートの3つのシートです。まず[メンバー連絡先]シート(④)に、旅行に参加するメンバーの名前を入力します。既定の状態で、例としてデータが入力されています(⑤)。今回はこれらのデータを流用したうえで、新たなメンバーも登録していきます。

 [【入力用】割り勘計算]シート(⑥)には、旅行中(または旅行前)にメンバーが支払ったものと金額を記録します。既定の状態で仮のデータが入力されているので、削除しておきましょう。データが入力されているセル範囲を選択して[Delete]キーを押します(⑦)。

 [【集計用】割り勘計算]シート(⑧)は、メンバー1人1人が支払う金額を計算するシートです。[集計]ボタン(⑨)をクリックすると、自動的に計算されます。ここでも既定の状態で仮のデータが入力されているので削除しておきます。C列に入力されているデータ(⑩)を選択し、[Delete]キーを押して削除します(F列やG列には数式が設定されているので削除しません)。C列のデータを削除したあとで、一度[集計]ボタンをクリックして初期化しておきます。

 これで準備ができました。次の項では、実際にデータを入力していきます。

参加メンバーを登録する

 旅行に参加するメンバーを登録します。今回は、10人のメンバーによるグループ旅行を想定してみましょう。[メンバー連絡先]タブ(①)をクリックして、[メンバー連絡先]シートを表示します。既定の状態で、数人分の情報が登録されているので(②)、今回はこれらのデータを流用し、さらに3人分のデータを追加して10人分のデータを用意します。

 C列に追加するメンバーの名前を3人分、入力します(③)。今回の例では、電話番号やAddressなどは仮のデータのままにしておきます(④)が、必要に応じて入力してください。旅費の精算に必要なのは、C列に入力されたメンバーの情報だけです。

立替金を入力する

 [【入力用】割り勘計算]シート(①)に移動しましょう。このシートに、メンバーが立て替えた金額の情報を入力していきます。例として、田中さんが全員分のホテル代30万円を立て替えたとして、先頭の行にデータを入力してみます。

 セルC13(②)に田中さんの名前を入力します。先ほど[メンバーの連絡先]シートに入力した内容がドロップダウンリストとして表示される(③)ので、クリックして選択します。

 セルD13には買ったものとして「ホテル代(30,000×10)」(④)と入力し、セルE13には金額(⑤)を入力します。

 同じようにして、すべてのメンバーの立替金を入力します(⑥)。

 入力を終えたら、次は集計です。

集計・精算する

 [【集計用】割り勘計算]シート(①)に移動しましょう。C列(②)に、参加メンバー全員の名前を入力していきます(セルC25に「支払ったメンバー」とありますが、何も支払っていないメンバーの人数も集計処理の際に必要なので、全員分の入力が必要です)。

 ここでも、ドロップダウンリストからメンバーの名前を選択できます(③)。

 全員分の名前を入力できましたね(④)。⑤の欄には、一人当たりの支払金額(41,210円)が計算され、表示されています。

 立替金を差し引いた支払金額を確認するには、[集計]ボタン(⑥)をクリックします。するとF列とG列に自動的に値が入力されます。

 精算の結果、それぞれのメンバーはG列(⑦)に表示された金額を支払えばいいことが示されました。マイナスの値が表示されているメンバー(田中さんと山田さん)はその金額を受け取ります。⑦の範囲に金額が表示されていないメンバーは、立替金がないため、⑧の欄に表示されている「41,210円」を支払います。

 最後に、このブックを保存する際は、[名前を付けて保存]画面(または[名前を付けて保存]ダイアログボックス)で[ファイルの種類]に[マクロ有効ブック](⑨)を指定して保存しましょう。そうでないと、マクロによる集計機能が失われてしまいます。

テンプレートをうまく使って品質の高いドキュメントを作ろう

 今回は、Excelのテンプレートの中から、面倒な旅費の精算を自動で行ってくれる「旅のしおり(割り勘 計算機付き)」というテンプレートの使い方を解説しました。このテンプレートのほかにも、機能的で使い勝手のいいテンプレートがたくさん公開されています。ぜひ、一度試してみてくださいね。