旅行の割り勘計算を Google スプレッドシートでやってみた
Excel に飽きたので Google スプレッドシートで遊んでいるのですが、
複数人で旅行する際に、支払額の調整が出来るシートがあればいいな～と思ったので作ってみました。
本シートのセールスポイントは以下の通りです。
- 「何に」「何円かかった」「誰の分を」「誰が払った」を書くだけ
- データを縦にストックするので、何人でもOK
- 飲食代など1人当たりの支払額が異なる場合も対応
全体像はこんな感じ。さっそく見ていきましょう！
データ入力欄
項目やら金額やらを入力していきます。
全員で均等に割り勘する場合は、「項目」「金額」「支払」のみを入力します。
飲食代など、人によって支払額が異なる場合は、「誰の分」の列が追加されます。
昼食で、Aさんが700円、Bさんが650円、Cさんが600円、Dさんが800円のメニューを頼み、合計金額をAさんが一括で支払った場合は、以下のように4行にわたって入力します。
このように入力することで、人数が増えても列を追加したりテーブルを拡張したりする必要はなくなります。
集計欄
下図の青く囲んだ部分に数式を入力します。
※「総額」は個人的に入れてるだけで無くても機能します
人数を取得
「誰の分」列に登場する人数を COUNTUNIQUE で数えています。
各人が払う金額、全員で割り勘する金額をリストアップする
QUERY を用いました。
これをF4セルに書くだけでF,G列に金額のリストが出来ます。
QUERY関数の中身を軽く説明します。
select ~ ： ~ を表示する
group by ~ : ~列の値ごとに列を出力
"select C, sum(B) group by C" は、
"C列の値ごとに C, sum(B) を出力"という意味になります。
全員で割り勘する金額の1人当たり支払額を計算する
「誰の分」が空白、すなわち全員で割り勘する金額 25,470円を、
参加人数 4人で除します。
「支払済」金額の計算
SUMIF (D列の中から, F6に一致する場合, B列の値を足していく)
=IF(F6=0, "", ~) としているのはF列に登場人物がいない場合に値を表示しないためです。とりあえず I6:I21までオートフィルしておき、16人まで対応できるようにしました。
「その人が支払うべき」金額の計算
各人が支払う分＋全員割り勘分
「清算額」の計算
支払うべき金額－支払い済金額
ぱっと見「プラスの人が払う」というのが分かりにくいので、
条件付き書式で支払うべき金額を赤字にして目立たせます。
条件付き書式は「表示形式」タブにあります。
範囲とルール、スタイルを適用し、「完了」
完成
はい完成～～～～～！
