日割り料金をGoogleスプレッドシートで自動計算する

Image

いよいよ11月1日から「コワーキングスペース八王子 8Beat」がオープンします。そのため、日々あれやこれや準備に追われています。

今日も、最初の月の日割り料金はどうしよう?的なメッセージが流れてきて、チームのみんなで使っているGoogleスプレッドシートで計算できたら楽だなぁと思ってちょっと作ってみました。

スポンサーリンク

といっても、Googleスプレッドシートではほとんど関数とか使ったことがなかった

EXCELなら割と使ってきたのですが、Googleスプレッドシートでは row() とか sum() とかごくごく簡単な関数しか使ったことがありませんでした。

そこで…    ググります!

割とスグにこちらが見つかりました。

Googleスプレッドシートで月末を求める

見たこともないような関数がいくつかならんでいます。しかし欲しかった計算が全部あったので参考にさせていただくことにしました(^^)/

日数に関する関数をコピペして動作を確認する

ちょっとコピーして実行してみます。

例4)今月末の日だけを求める

=DAY(EOMONTH(NOW(),0))

31と表示されます。

=DAY(EOMONTH(NOW(),0)) というところをコピーして、

Image

新しいGoogleスプレッドシートを作って

Image

貼り付けて、

Image

リターンを押すと、31と表示されました。今月は10月なので、あってます♪

日割り計算をしたいので、

例5)今月の残り日数を求める。
=DAY(EOMONTH(NOW(),0))-DAY(NOW())

もやってみます。

Image

貼り付けて、

Image

リターンすると、15と表示されました。今が16日なのであってます♪

このままだとどっちがどっちの数字か分からなくなっちゃうので

Image

見出しを入れてみました。

月額料金に対する当月の日割り料金を計算する

月額料金に対する当月の日割り料金の計算ですが

当月の日割り料金 = 月額料金 × ( 今月の残り日数 ÷ 今月の日数 )

ですね。計算に使う 月額料金 をスプレッドシートに入力しておきます

Image

あと、計算結果を表示する欄も設けて分かりやすいように Bold にしたりフォントを大きくしておきます。

Image

いよいよ計算式です。さっき書いた計算式を関数で書いてみます。

=$B$7 * (B5 / $B$4)

Image

入力して、

Image

リターンを押すと計算されました。小数点は要らないのとカンマ区切りを付けたいので、

Image

月額料金と、今月の日割り料金の数値を選択しておき、

Image

¥マークをクリックします。

Image

カンマがつきました。この状態で、

Image

小数点以下の桁数を減らす ボタンを2回クリックします。

Image

小数点以下の数字が見えなくなりました。

今日の日付を表示します

Image

日付を入れたいセルの上に「本日」と入力しておきます。日付を入れたいセルで、

=now()

として、

Image

日付が表示されました。

Image

表示形式>数字>日付 を選択して、

Image

年月日だけの表示になりました。これで本日から自動計算するという部分が完成しました。

今日(2014/10/16)からの入会だった場合の月額日割り料金は 4,297円 です!

おまけ:○日に入会したらいくらになる?

本日ではない日に入会したらいくらになるのか、という計算をします。

先ほど作った「本日」というセルの右に「日付指定」と入れます。

Image

本日の下のセルに今日の日付が自動表示されていますが、日付指定するには日付指定と入力してあるセルの下のセルに計算したい日付を入力します。

今月の残り日数と日付指定セルの交差するセルに、

=if(C3<>””,DAY(EOMONTH(NOW(),0))-DAY(C3),””)

と入力します。

Image

これは「もし日付指定の日付(c3)が空でなければ、今月の日数から 入力された日付の日 を引いた数、それ以外なら空白を入れる」という式になります。

料金計算部分は、本日の料金計算をしているセルをクリックして右下に出てくる、

Image

■をクリックして右のセルにドラッグします。

Image

このようになります。中身は

Image

となっていて、式が

=$B$7 * (C5 / $B$4)

と自動的に残り日数部分左側のセルだとB5だったものが自動的にc5になっています。$を付けないとこのように式を引っ張ったときに自動的にセルの値が変わるので便利です。

日付を指定してみます。

Image

Image

こんな感じで指定した日付からの月額日割り料金も計算できるようになりました(^^)/

閲覧モードで公開しました

兎本美佳
ブログを見た人がそのままできたらいいなと思って、できるだけ丁寧に書いています。blogに書いたようなネタの有償対応のご相談は「ゆうそうと」へいただければと思います(^^)/ 無償での対応をご希望の場合は、コメントをいただけましたら可能な場合はコメントを返させていただきます。

シェアする

フォローする

スポンサー広告
スポンサーリンク
スポンサーリンク
トップへ戻る