飲み会の精算をエクセルでする。
2次会までいくと意外と複雑
1次会だけだったらまとまって払ってくれた人のを単純に割れば良いのだが、2次会までいって払った人が別とか、あとはオクトーバーフェストみたいに各人がまばらにまとめ買いする状況だと、結構大変である。少なくとも2日酔いの頭では難しい。
ちなみに相方には「てか二次会やめなよ」と言われたけど、そういうことではない。
テクノロジーで解決する
そう、私はITベンチャーで働いている。こういう時はポカリやヘパリーゼで回復を図ることも大事だが、テクノロジーで解決するのがよい。ということでスプレッドシートを開いた。令和にも通ずるBig techである。知らない人は覚えておくと良いと思う。
ということで爆誕したシートがこちら。
作ってみたら意外と考える部分があったので、作成過程の思考をメモする。ちなみに厳密にはエクセルではなくGoogleスプレッドシートで作成しているが、私を育ててくれたエクセルに敬意を表してエクセルと記載した。
やりたいことを明確にする
やりたいことは以下。
参加メンバーを記載できる
立て替えた費目と金額、人を記載できる
よしなに按分して各人の負担額がわかる
数式の複雑性をなるべく減らして、汎用性を担保する
1ページに収まる
1/ メンバー
これはただのリスト。特に何もしていないが、気にしている点は(1) 入力項目を青字にすること、と(2)入力箇所の網掛け。
どちらも趣味の世界ではあるが、特に(1)はやって損はないと思う。コンサルや投資銀行では「カラーコード」と呼ばれ、文字に色付けをする営みである。私は
ベタ打ちが青
フィード(どこかから参照している)が緑
数式は黒*
という宗教で育った。見聞きした範囲だと、フィード or 数式を赤でやるという宗派もあるみたいだが、個人的には赤は目がチカチカするので微妙だと思っている。自身で作成するものはすべて上記に揃えるし、全人類そうしたらいいと思う(過言)。
なお青 = #0000FF(R0 G0 B255)、緑 = #005c3e(R 0 G 92 B 62)を使う。青は一般的、緑は私が新卒で入った会社のテーマカラーなので人によってゆらぎがありそう。ちなみに英ボリス・ジョンソン元首相も新卒でこの会社に入っ(て1週間で辞め)たそうだが、彼の髪はいつもゆらいでいた。
*「数式は黒」とするとかなりのものが黒になるのでは？という懸念がある。それはその通りで、数式だがなにか計算を加えず引っ張ってきているだけ(例 FILTER、VLOOKUPといった数式)は緑で良いと思っている。モデルを組む際には別の人がチェックするのが一般的なので、その際にそれが計算なのか、どこから持ってきているのか、ベタ打ちなのかを理解したいというのが背景である。
2/ 費目一覧 (前編: 費目と立て替えた人を並べる)
本命。シンプルに 費目、立て替えた人、金額の列を追加する。これらもベタ打ちが必要になるものなので、青字とする。1点だけ追加したのは、立て替えた人については1/で記載したメンバーに依拠するはずなので、そこに記載のある人のリストから選べるようにする。入力の手間を下げるのと、udonなのにudonnnとか記載してしまうと数式がワークせず取りっぱぐれたりする正しさが担保できないことを防ぐためでもある。
これはGoogleスプレッドシートであれば データ > データの入力規則 > 条件: リストを範囲で指定、から選んでできる。まぁ参加者は多くても15人くらいだと思うので、15行分列を指定している。
なお、上記で書いたように、どれくらいの幅を持たせるかは結構判断が求められるものだなと思う。もちろん100人の飲み会にも耐えられるシートは作れるが、そうするとシートが長くなって一覧性が低くなったりするし、そもそも100人の飲み会企画できるの？という問いもある(私も75人までしか経験がない)。
2/ 費目一覧 (後編: 各人の負担額の算出)
ここまではシンプルだった。ちょっと複雑になるのは、全員が全員いてワリカンなら良いが、2次会はいなかったとか、気持ちが前のめりして数人でゼロ次会をしてしまったとかそういうケースである(大いに背中を押したい)。必要なステップは4つ。
誰がどの費目に該当する(=負担すべきか)を記録する
各費目に該当する人の人数(=適用人数)を算出する
各費目の1人毎の単価を算出する
各人の負担額の合計を算出する
1.誰がどの費目に該当する(=負担すべきか)を記録する
今回はそれぞれの費目について、誰が該当するかのチェックを入れることにした。シンプルにメンバーの名前を横に並べて、そこに該当するものに1を振っていくことにした。
理想はこのチェックを入れて、次の数式一つで各人の負担まで計算されることなのだが、考えた限りだと一発でやるの難しそう + 数式が複雑になりそうだったので、諦めた。なお複雑なので数式分けるということは結構大事だと思っていて、(a+b+c) x d = e みたいな式もあえて a +b + c = α と α x d = e にわけたりする。これは後で見直しをしやすくするためであり、投資銀行やコンサルが吐き出すモデルが1,000行を超えたりするのはこうやって分けることが要因だったりする。1つのセルに複雑な数式を入れ込むことを誇りにする人もたまにいるが、あれは埃のように不毛である。
2.各費目に該当する人の人数(=適用人数)を算出する
各人の立替額の算出をする。まずは、各費目ごとに、該当する人数を合計する。シンプルにSUM関数を使う。注意点としては、上記で15名まで追加できると記載したが、5人しかいないのに15列並んでいるのは一覧性が下がるので、それをグループ化していること(矢印部分)。そしてSUMの範囲はグループ化して見えない部分までカバーしておくこと。6人とかになったらまあよしなにまたグループ化を調整したらよい。
エクセルにしろスプレッドシートにしろ、こうして列や行を隠す際に行/列を非表示にする人がいるが、これは悪手なのでやめてほしい。なぜかというと、非表示にすると(特にエクセル)、隠されていること自体がわからず、チェックの際に見落とすリスクがあるからだ。であれば、隠していることが明確に伝わり同じ効果を担保できるグループ化を必ず使うのが良い。汚いのなら少し掃除するか、ただシャイなだけならもっと前に出よう。
3.各費目の1人毎の単価を算出する
次に適用する人の中での単価を算出する。これは金額 / 適用人数なのでシンプル。ポイントはARRAYFORMULAを使っていること。常々私はエクセル>>Googleスプレッドシートと思ってきているが、その不等式に一度疑念を芽生えさせたのがこのARRAYFORMULAである(注: どうも最近はエクセルにも実装された模様)。詳細は割愛するが、一つの数式を入力するだけで縦方向に自動的に数字を吐き出してくれる。結構このARRAYFORMULA君は癖があるので仲良くなるにはコツが必要(SUM, AVERAGE, MIN, MAXといった関数が使えない等)なのだが、ミスが減るのと(あまり理解しきれてないが)スプレッドシート側の処理の負荷を下げられるようだ。
まあ面倒だったら、どうせ上述のようにどんどん人が増えるわけではないので、金額/適用人数の割り算の式を入れて下にコピーしていけばいいと思う。なおARRAYFORMULAの中にIF文を入れて空白を処理するのは美の観点から覚えて置くと良いと思う。
4.各人の負担額の合計を算出する
これで適用人数と単価が出せた。次にこれを各人にマッピングしていく。なお、この適用人数と単価は別に見える必要がないので、グループ化して隠しておく。変に入力して式が崩れることを防ぐためでもある。
次に各人ごとの負担額を算出する。先程算出し単価を、該当した人に対して適用していく。下記の赤枠部分でやりたいことは2つ。(a) 名前を引っ張ってくる、(b) 該当する部分の単価をそれぞれの人に向けて算出する。
(a) もともとメンバー入力したところから、ARRAYFORMULAとTRANSPOSE(転置)で引っ張ってくる
(b) SUMPRODUCTを使って、単価と各人の該当するかどうか(=1)に突合して合計の負担額を算出する
3/ 精算
最後、精算(=集計)である。やるべきことは
各自の名前をリスト化して表示
各自の負担額の計算
各自の立替額の計算
2 + 3 をして各自いくら支払い OR 受取るべきかを計算
1.各自の名前をリスト化して表示
ARRAYFORMULAを使って1/メンバーから引っ張ってくる。FILTERはなくても良いが、例えば1/メンバーにおいて、最初に入れたけど該当しなくなった人がいた際にも、そこの部分調整しなくとも空欄をあけずにまとめに記載されるようになる
2.各自の負担額の計算
2/にて計算した各人毎の負担額の集計をHLOOKUPを使って引っ張ってくる。VLOOKUPではなくHLOOKUPであることに注意。この辺いちいち考えなくてはいけないことが私がINDEX+MATCH >>> VLOOKUPと信じてやまない理由の一つ。息を吐くように使っているINDEX+MATCHと違いVLOOKUPは結構な頻度で関数の構成(何をどの順でいれなくてはいけないか)を調べている。本当にスマートでない。スマートでないが、INDEX+MATCHはARRAYFORMULAと併用ができないので泣く泣く使っている。
3.各自の立替額の計算
2/にて費目を積み上げたものから、各人毎に立て替えた金額を積算する。ここがちょっと悩ましいなと思った部分。ARRAYFORMULAを使いたいのだが、ARRAYFORMULAとSUM系の相性がすこぶる悪く、やり方はなくはなさそうだが数式が複雑になりそうなのでシンプルにSUMIFSを使うことにした。なおSUMIFとSUMIFSは複数形との違いだが、Sをつけても単一の条件で計算はできるので常にSUMIFSでやればよく、SUMIFは特段存在意義がない。
ここがARRAYFORMULAにできていないので下まで式をコピーする必要がある。これは人数が増えた際に数式を再度コピーする必要があるのだが、まあ15人に耐えれば良いとすればOKとすることにした。(であれば他もARRAYFORMULA使わなくていいやんという心の声は無視)
4. 2 + 3 をして各自いくら支払い OR 受取るべきかを計算
これはシンプルに足し算。ARRAYFORMULAとSUMは犬猿の仲だが、プラスを繋げるだけなら使える。2つの列/行の足し算くらいならまぁ式も魑魅魍魎にならないかなという所感。
これで完成。上にある合計のところは、チェックのために入れている。例えば支払額の合計は足し引きゼロになるはずなのでここがゼロになっていないといけない。意外とミスはするものなので、こういったチェックは財務モデルでも必ず入れるようにする。自分は意外と信用ならないもんである。実際これを作る過程では、立替額の部分でSUMIFSでなくVLOOKUPで持ってきてしまっていて、数字があわなくてムムッとなっていた(複数の立替をする場合もあるので、SUMIFSにしないと1回分の支払いしか持ってこれないことになる)。
最後に
これが最終的なアウトプットである。実際に週末のピクニックで使ってみたが、持ち込みの弁当代金も入園料も、スタバ代も支払う人別々だったけどいい感じにできた。難点はお金を集め支払う人が必要になることで、その人が面倒ってことである。まあ各費目ごとに精算するよりはよっぽどいいと思う。支払い額がプラスの人からはその分受取、マイナスの人には支払えばよい。
結構自分も2日酔いの中で精算すると段々面倒になってきてbad debtが積み上がる経験あったが、ぜひ皆さんもこういうようなものを使ってぜひ前向きに飲み会に行っていただきたい。
追伸
テクノロジーで解決する、と銘打ったものの、本当のテクノロジーはこれである。このアプリ(WEBもある)が素晴らしすぎて、上記の数式はシートはこのアプリケーションの前では無に帰す。アメリカでは5年以上前から皆使っていたが、なぜか日本ではあまり知られてないので絶賛宣伝したい。
旅行やなにかのイベントにおける支出を各自入力し、この人だけ該当、とかこれは自分多く飲んだので多く払う、みたいなのが全部いい感じに合算できる精算アプリ。アメリカではそのままvenmo(LINE payやpaypayのように簡単に送金できるアプリ。アメリカはまじで全員使ってる)につなげて支払えるので、旅行の精算も一瞬である。便利さに感銘を受けて、「日本で決済サービスと提携したらどや？」って問い合わせてみたら、「検討するぜ！」って返事きて調べてみたら創業者だったこともあった。
唯一の難点はメールアドレスが入力必須になるので、アカウントが持ってないと飲み会のためだけに登録してもらう負荷が高いことである。なので上記のスプレッドシートを生み出すことになったのだが、もうスプレッドシート無視して良いのでみんな上記のアプリのアカウント登録をしてください。
本日は以上です。
.. それでもスプレッドシートが好きだよってあなたはこういう仕事が向いてるかもしれません。
この記事が気に入ったらサポートをしてみませんか？