いよいよ11月1日から「コワーキングスペース八王子 8Beat」がオープンします。そのため、日々あれやこれや準備に追われています。
今日も、最初の月の日割り料金はどうしよう？的なメッセージが流れてきて、チームのみんなで使っているGoogleスプレッドシートで計算できたら楽だなぁと思ってちょっと作ってみました。
といっても、Googleスプレッドシートではほとんど関数とか使ったことがなかった
EXCELなら割と使ってきたのですが、Googleスプレッドシートでは row() とか sum() とかごくごく簡単な関数しか使ったことがありませんでした。
そこで… ググります！
割とスグにこちらが見つかりました。
見たこともないような関数がいくつかならんでいます。しかし欲しかった計算が全部あったので参考にさせていただくことにしました(^^)/
日数に関する関数をコピペして動作を確認する
ちょっとコピーして実行してみます。
例４）今月末の日だけを求める
=DAY(EOMONTH(NOW(),0))
31と表示されます。
=DAY(EOMONTH(NOW(),0)) というところをコピーして、
新しいGoogleスプレッドシートを作って
貼り付けて、
リターンを押すと、31と表示されました。今月は10月なので、あってます♪
日割り計算をしたいので、
例５）今月の残り日数を求める。
=DAY(EOMONTH(NOW(),0))-DAY(NOW())
もやってみます。
貼り付けて、
リターンすると、15と表示されました。今が16日なのであってます♪
このままだとどっちがどっちの数字か分からなくなっちゃうので
見出しを入れてみました。
月額料金に対する当月の日割り料金を計算する
月額料金に対する当月の日割り料金の計算ですが
当月の日割り料金 = 月額料金 × ( 今月の残り日数 ÷ 今月の日数 ）
ですね。計算に使う 月額料金 をスプレッドシートに入力しておきます
あと、計算結果を表示する欄も設けて分かりやすいように Bold にしたりフォントを大きくしておきます。
いよいよ計算式です。さっき書いた計算式を関数で書いてみます。
=$B$7 * (B5 / $B$4)
入力して、
リターンを押すと計算されました。小数点は要らないのとカンマ区切りを付けたいので、
月額料金と、今月の日割り料金の数値を選択しておき、
￥マークをクリックします。
カンマがつきました。この状態で、
小数点以下の桁数を減らす ボタンを2回クリックします。
小数点以下の数字が見えなくなりました。
今日の日付を表示します
日付を入れたいセルの上に「本日」と入力しておきます。日付を入れたいセルで、
=now()
として、
日付が表示されました。
表示形式＞数字＞日付 を選択して、
年月日だけの表示になりました。これで本日から自動計算するという部分が完成しました。
今日（2014/10/16）からの入会だった場合の月額日割り料金は 4,297円 です！
おまけ：○日に入会したらいくらになる？
本日ではない日に入会したらいくらになるのか、という計算をします。
先ほど作った「本日」というセルの右に「日付指定」と入れます。
本日の下のセルに今日の日付が自動表示されていますが、日付指定するには日付指定と入力してあるセルの下のセルに計算したい日付を入力します。
今月の残り日数と日付指定セルの交差するセルに、
=if(C3<>””,DAY(EOMONTH(NOW(),0))-DAY(C3),””)
と入力します。
これは「もし日付指定の日付（c3)が空でなければ、今月の日数から 入力された日付の日 を引いた数、それ以外なら空白を入れる」という式になります。
料金計算部分は、本日の料金計算をしているセルをクリックして右下に出てくる、
■をクリックして右のセルにドラッグします。
このようになります。中身は
となっていて、式が
=$B$7 * (C5 / $B$4)
と自動的に残り日数部分左側のセルだとB5だったものが自動的にc5になっています。$を付けないとこのように式を引っ張ったときに自動的にセルの値が変わるので便利です。
日付を指定してみます。
こんな感じで指定した日付からの月額日割り料金も計算できるようになりました(^^)/
閲覧モードで公開しました
コメント