• 締切済み

マクロや関数に詳しい方

私は個人で店を経営しているものです。 私の店では、その滞在時間に応じて料金を加算していく（漫画喫茶のような）料金設定をとっています。 この清算をエクセルで計算したいと思っているのですが、 どのようにすればよいか教えて頂ければ幸いです。 詳細です↓ (1)30分以内で300円として、その後30分ごとに300円ずつ加算されていきます。 (2)3時間以上から5時間までは定額で1800円です。 (3)5時間からは、1800円＋30分経過ごとに300円の追加料金という形です。 (4)ちなみに、入室時間と退室時間を入力すれば料金が表示できるようにしたいです。 (5)滞在時間が50分や1時間45分などの中途半端な時間ならば、それぞれ60分・2時間扱いで計算します。 滞在時間は退室時間から入室時間を引いて出すことが出来るのですが、 その以降の計算が関数やマクロを使用しても、どうしても自分ではできません。 エクセルに詳しい方のお力を頂ければと思い質問させていただきました。 わかりにくい説明で大変申し訳ありませんが、どうかよろしくお願いいたします。

みんなの回答

  • Flak45
  • ベストアンサー率29% (91/306)
回答No.4

＃３です。書き間違いがありました ＞Ａ５のセルに次の式を書きます Ａ６のセルに でした。

  • Flak45
  • ベストアンサー率29% (91/306)
回答No.3

ひとつの式に放り込んでみました（＾＾； 滞在時間＝Ｎ Ｎ＋29分したものを30で割った商がＮ１とし Ｎ１＜６であればＮ１＊３００ Ｎ１＞５かつＮ１＜１１であれば１８００ Ｎ１＞１０であれば（Ｎ１－１０）＊３００＋１８００ という式をひとつのセルに埋め込めば良いかと思います Ａ１のセルに入店時間　 Ａ２のセルに出店時間 Ａ３のセルにその差　セル書式で[mm]としますと60分を超えた場合も分単位になります。 Ａ４にワンクッションおいて　＝Ａ３＊２４＊６０とします　セル書式は標準にしてください。 (ワンクッションおくのは時間が1日で1.00の数値だからです。24時間＊60分をかけて整数にします。) Ａ５に　=INT((A4+29)/30)で、30分単位の数字が出ます。 Ａ３以外セル書式が[mm]にならないよう注意です。 Ａ５のセルに次の式を書きます =IF(A5<6,A5*300,IF(AND(A5>5,A5<11),1800,(A5-10)*300+1800)) 3時間未満ですと前部分で30分ごとに300円 3時間を超え5時間未満ですと中段の1800円定額 それ以外ですと(5時間超えですので)5時間までの1800円に5時間を超過した30分ごとに300円を追加した額になります。 尚、30分きざみで端数時間は1分でもオーバーすると繰り上がります。その辺調整するにはＡ４のセル計算式の29を任意にすればＯＫです。 まあ力ずくってことで、もっとスマートなやり方あると思いますので、参考程度で（＾＾；

ayuka777
質問者

お礼

わかりやすく式を入れて教えて頂きありがとうございます！！ 試してみたところ表示されました！ 非常に助かりました（*＾0＾）

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.2

ＶＬＯＯＫＵＰ関数のＴＲＵＥ型をやってみましたか。 こういうタイプの問題は、まずＶＬＯＯＫＵＰ関数でできないか考えるものです。 これでできないほど複雑なルールかどうか、ご自分でやってみて、できなければ質問してください。 概略は本日の他の質問（点数の段階わけ）のに載せましたが。 ＞マクロを使用しても、 自由度はあがりますが、難しくなるだけでしょう。

ayuka777
質問者

お礼

ＶＬＯＯＫＵＰ関数がありましたね！ まだ、試していませんでした。 教えてくださりありがとうございました。

回答No.1

●場合分け 　こういう場合は、まず滞在時間により場合分けをしましょう。 IF関数を使うことになりますが、すべての条件を一つの式に 放り込んでしまうと、式が複雑になりすぎて、見た目に わかりにくくないります。 　そこで、A列に滞在時間が入っている場合、 　　B列　3時間以下の式 　　C列　3時間～5時間の式 　　D列　5時間以上の式 のようにそれぞれ式を入れ、E列にIFを用いて 滞在時間に応じてどの式かを選ばせます。 ●具体的な式 　A1に滞在時間が入っているとします。 　B1　=(FLOOR(A1-"0:01","0:30")*24*2+1)*300 　C1　=1800 　D1　=1800+(FLOOR(A2-"0:01","0:30")*24*2-9)*300 　E1　=IF(A1<VALUE("3:00"),B1,IF(A1<VALUE("5:00"),C1,D1)) なお、B～Eは表示形式がおかしくなる可能性があります。 時刻や日付表示になってしまったら、セルの書式設定で 数値や通貨などに変えてください。

ayuka777
質問者

お礼

アドバイスありがとうございます！！ 具体的に式を入れて下さっているので、 早速試させて頂きます。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • エクセルと関数について

    エクセルと関数について 以下のような表を作成しました。 漫画喫茶の料金を計算したいのですが、 喫茶Aは1時間まで400円、それ以降は30分毎に200円加算。 喫茶Bは20分毎に300円 喫茶Cは10時間まで1時間ごとに100円、10時間以降は1000円加算。 下記表のA列3行目以降に時間の表示を入力しています。 1時間も1：00ではなく、数値の1.0を入力して時間としています。 下記の表の空欄セルにどんな数式を入力すればいいでしょうか。 よろしくお願いします。

  • 関数の計算式を教えてください。

    今晩は。お世話になります。 pure-cocoaと申します。 カラオケ部屋使用料の計算方法についてお尋ねいたしたくよろしくお願いを申し上げます。 下記カラオケ部屋の使用料があるとして使用開始時間と退出時間を入れる事により 使用料が算出できる計算式（関数）を教えてください。 08:30-17:15迄（平常料金） \1,000/最初の１時間（以降30分単位で500円ずつ加算） 17:15-22:15迄 (夜間料金） \1,500/最初の１時間（以降30分単位で750円ずつ加算） 22:15-04:45迄 (深夜料金） \2,000/最初の１時間（以降30分単位で1000円ずつ加算) 04:45-08:30迄 (早朝料金）　 \1,500/最初の１時間（以降30分単位で750円ずつ加算） 使用料金は30分ごとに区分して異なる料金体系に時間がまたがった場合は高い方の料金を適用します。 例 a) 07:30-09:00の場合：07:30-08:30の１時間分早朝料金と08：30-09:00の30分平常料金の合算 b）08:20-09:20の場合：08:20-08:50の30分を早朝料金として計算し08:50-09:20の30分を平常料金として合算する c) 16:30-17:45の場合：16:30-17:00の30分を平常料金として計算し17:00-17:45の45分を一時間分の夜間料金として合算する d) 21:45-23:00の場合：21:45-22:15の30分を夜間料金として計算し22:15-23:00の45分の一時間分を深夜料金として合算する 以上ですがご教示いただければ幸甚です。 よろしくお願いを申し上げます。

  • アルバイトでお金を盗みました

    漫画喫茶でアルバイトをしています。 漫画喫茶に行く方にはわかると思いますが、時間料金制で私のアルバイト先は、 ３時間パック８００円 ６時間パック１２００円 延長３０分毎に２００円になってます。 ３時間パックで入られたお客さんが、パック料金の切り替えをせず６時間で清算したので、 ３時間料金８００円＋延長料金３時間分で１２００円＝２０００円を支払って帰られました。 お恥ずかしながら、それをバレないように清算後６時間パック（１２００円）にして、差額８００円を盗んだのですが、それがバレてしまったのです。 これってどの様な罪になるのでしょうか？ 一ヶ月の給料無し又は、減給と言われても反論できないでしょうか？ 教えてください、よろしくお願いします。

  • ネットカフェの料金

    ネットカフェの料金についてですが、例えばナイトパック（5時間いくらとか）で入店してその後居続けたら料金はどうなりますか？もしそのお店が15分100円だとしたら、ひたすらその料金が加算されてゆくのでしょうか。それとも時間に応じて勝手にお得な料金(３時間１０００円とか）で加算されるのでしょうか。それとも時間を過ぎるとホテルのように電話がかかって来るのでしょうか。お店によって違うでしょうが一般的にどんな感じでしょうか。ご存知の方教えてください。

  • エクセルでの数式を教えてください

    駐車場の料金計算で２時間まで４００円でそれ以降３０分につき２００円加算される数式を教えてください。 入庫及び出庫の入力及び駐車時間の計算はできています。

  • エクセルVBAの時間計算

    Ｅｘｃｅｌ２０００を使用しています． ＶＢＡで時間計算をしていまうが、日をまたぐ時間計算がうまくいきません． 例 dim a as date　　’入室時間 dim b sa date 　 ’退室時間 dim x as date　　’滞在時間 x= b - a で aが23:00　bが25:30　だと　xが2:30　になりません． 日付データはありませんので、時間だけで計算したいのです．

  • 10分単位の関数　計算式

    会社のデーターがとんでしまい、いちから作り直すんですが、あまり計算式がわからなくて困っています。 エクセルで10分単位の計算式を教えてください。 たとえば1時間1200円、そうすると10分あたりが200円。 6時間20分を6：20と表示して、6時間20分あたりの金額は7600円になるのですが、これをエクセルの関数で自動計算したいのですが、どうすれば良いですか？ ちなみに同じ形で5分単位の計算にも応用できれば助かります。 助けて下さい。 宜しくお願い致します。

  • 〈至急〉エクセルの関数

    画像が添付できないので、文面で失礼します。 2003/2/10のレンタル料金計算表 貸出日が2003/2/6 経過日数が4日です。 料金は経過日数が2日以内なら300円、3日を超えると、1日につき200円加算されます。 2003/2/6の料金の関数を使っての求め方がわかりません。 よろしくお願いします。

  • 中学入試問題（算数）が解けなくて困っています

    私立中学の過去問題です。 問題文 Ａ社の携帯電話通話料金は1分あたり60円で、ひと月の通話料金の合計が60分以上では定額3600円です。Ｂ社の携帯電話通話料金は１分40円で、ひと月の通話料金の合計が120分以上では定額4800円です。 （1）Ａ社とＢ社のひと月あたりの金額が同じになるのは、通話時間の　　合計が何分のときですか。 （2）さらに、Ａ社では通話時間が120分を越えた場合、定額の3600円に　　１分あたり60円の通話料が加算されるとします。このとき、Ｂ社の　　ひと月あたりの金額を上回るのは、通話時間の合計が何分より長　　　く、何分より短い場合ですか。 回答 （1）90分　（2）90分より長く140分より短い とき方が全くわかりません。詳しく教えていただけたらと思います。

  • エクセルと時間の計算

    エクセルと時間の計算 エクセル2003で以下のような表を作りました。 C列の金額には、「20分毎に200円加算される」金額を入力したいです。 できれば、A列の値と200円で計算したいのですが、どんな数式をいれれば いいでしょうか。20分単位で切り上げたいので、CEILING関数を使うのでしょうか。 教えてください。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する