[[20081128104301]]　『何回目？』　ページの最後に飛ぶ

『何回目？』

 はじめまして。これからエクセルで請求書を作る上で相談させてください。
 下記は全て予定です。
 私の業務がパソコンのリース業と仮定して
 2008/4/10からお客さんがパソコンを借り始めてくれたとします。

 この場合リース料の請求は
 2008/4/10 - 2008/5/10　のように 一ヶ月単位で計算し
 毎月請求書を発行します。 料金は前払いなので上記の期間の場合3月末に
 請求書を出します。
 同じ要領で、次は4月末に2008/5/10 - 2008/6/10 の期間に対する
 請求書を出します。
 請求書は1st(ファースト)や2nd(セカンド)や3rdなど記載しており、
 リース開始から何回請求書を出したか分かるようになっています。

 この例でいくと3月末発行の請求書は1st、4月末の請求書が2ndとなります。

 1stやら2ndやら、もっと長くなると13thという回数をセルA4にいれますが
 これを自動的に変わるように出来ませんでしょうか？

      A  　  B          C  　   　 D    　   E
 1　                       　　   請求月:   3月        
 2      
 3
 4　 1st　 請求書
 5  
 6         From:    2008/4/10   
 7         To  :    2008/5/10

 なかなかうまくいきません。
 どなたかお助けいただけますか？出来ればC6とC7の日付も
 自動的に変わればありがたいです。

 (Oki)

        A  　  B          C  　   　 D    　   E
 1　                       　　   請求月:     6月 ← 2009/6/1       
 2      
 3
 4　 16th  請求書
 5         基準日   2008/4/10  
 6         From:    2009/7/10   
 7         To  :    2009/8/10

 期間や回数を出すためには何らかの基準が必要です。
 例として、 C5 セルに基準となる日付を入力した場合です。
 E1 には　2009/6/1 などと請求日を入れておき、表示形式で 0"月" などと設定します。

 C6=DATE(YEAR(E1),MONTH(E1)+1,DAY(C5))
 C7=DATE(YEAR(C6),MONTH(C6)+1,DAY(C6))
 A4=DATEDIF(C5,C7,"M")&LOOKUP(DATEDIF(C5,C7,"M"),{1;2;3;4},{"st";"nd";"rd";"th"})

 とするとこのようになります。
 4回目以降は全部 th がつきます。
 ただし、基準日が　30日とか31日の場合、2月は正常に表示されません。

 そこまで考慮すると大変そう・・・なのでリタイヤします。
 (sato)

 どうもありがとうございます。
 おっしゃる通り基準日が31日のものがあるので
 そうなると30日までの月がうまく表示されませんでした。
 何かもう少し考えてみます。

 しかし何回目というのはきちんと表示され、とても嬉しく思います。
 (Oki)

  >おっしゃる通り基準日が31日のものがあるので
  >そうなると30日までの月がうまく表示されませんでした。

  どうなれば正しいのかご説明いただく必要があると思います。
  以下は、基準日が月末の時は「月末ー翌月末」にする、と云う前提です。

 (1) C6セル =IF(MONTH(C5+1)<>MONTH(C5),DATE(YEAR(E1),MONTH(E1)+2,0),MIN(DATE(YEAR(E1),MONTH(E1)+1+{0,1},DAY(C5)*{1,0})))

 (2) C7セル =IF(MONTH(C5+1)<>MONTH(C5),DATE(YEAR(E1),MONTH(E1)+3,0),MIN(DATE(YEAR(E1),MONTH(E1)+2+{0,1},DAY(C5)*{1,0})))

 (3) A4セル =IF(OR(C5="",C6<C5),"時期尚早",DATEDIF(C5,C7+4,"M")&IF(LEFT(RIGHT(0&DATEDIF(C5,C7+4,"M"),2))="1","th",
 　　　　　　　LOOKUP(MOD(DATEDIF(C5,C7+4,"M"),10),{0,1,2,3,4},{"th";"st";"nd";"rd";"th"})))

 <サンプル結果図>
  行   _A__  __B___  ____C____  ___D___  __ E ___  
   1                            請求月:  2009/6/1  
   2                                               
   3                                               
   4   18th  請求書                                
   5         基準日  2008/2/29                     
   6         From:   2009/7/31                     
   7         To      2009/8/31

  (半平太)

 基準が29日であれば下記のようにしたいです。

  行   _A__  __B___  ____C____  ___D___  __ E ___  
   1                            請求月:  2009/1/20  
   2                                               
   3                                               
   4   18th  請求書                                
   5         基準日  2008/2/29                     
   6         From:   2009/2/28                    
   7         To      2009/3/29

 基準日(リース開始日)が15日とか22日とかどの月にもある日であれば問題ないのですが、
 例えば基準日が2008/11/30(セルC5)の場合、satoさんの教えてくださった式だと
 1月に請求書を作る時には C6は2009/1/30　C7が2009/3/1となってしまいます。
 これがC6が2009/1/30　C7が2009/2/28になれば私の言う正解となります。
 （翌月はC6が2009/2/28でC7が2009/3/30が正解）
 基準日が2008/7/31だとすると30日までしかない月の時にC7が翌月の1日になってしまいます。
 これがC6が2008/8/31、C7が2008/9/30　となれば正しいです。
 (翌月の請求はC6が2008/9/30でC7が2008/10/31)

 分かりにくい説明ですみませんが、何か良いアイデアがあればご教示ください。
 (Oki)

 そう云うことですと、こんな数式になります。
 　　　　　　　　　　　↓
 (1) C6セル =MIN(DATE(YEAR(E1),MONTH(E1)+1+{0,1},DAY(C5)*{1,0}))
 (2) C7セル =MIN(DATE(YEAR(E1),MONTH(E1)+2+{0,1},DAY(C5)*{1,0}))

  ※ A4セルの数式は、前回と同じです。

  (半平太)

 わっ凄いですねバッチリうまくいきました。ありがとうございました。
 (Oki)

 半平太さん
 しかしこれはどういう仕組みなのでしょうか？
 まだ見ていらっしゃれば教えてもらえませんか？

 >基準日(リース開始日)が15日とか22日とかどの月にもある日であれば問題ないのですが、
 つまり、satoさんの
　 C6セル=DATE(YEAR(E1),MONTH(E1)+1,DAY(C5))
 でいい訳です。

 しかし、応答日がない場合、その式は翌月に突き出してしまうのが難点だった訳ですよね？
 従って、その歯止めを月末にすれば良いことになります。

 月末を求める式は、翌月1日より1日前ですから、翌月の「0」日を求めれば良いので、
   =DATE(YEAR(E1),MONTH(E1)+2,0)
 になります。

 結局、求める日は上の式のどちらか小さい方を採用することになるので、MIN関数を使って、
　 C6セル=MIN(DATE(YEAR(E1),MONTH(E1)+1,DAY(C5)),DATE(YEAR(E1),MONTH(E1)+2,0)）
 とすれば求められます。

 それをDATE関数の使用を１回で済ませようとした式が下式です。
　 C6セル=MIN(DATE(YEAR(E1),MONTH(E1)+1+{0,1},DAY(C5)*{1,0}))

 この種の日付は、専用の関数であるEDATE()を使っても求められます。(詳細はヘルプをご覧ください)
　※　ただし、アドインを使いますので、XL2007より古いエクセルのバージョンでは、
　　　メニュー[ツール]→[アドイン]→[分析ツール]にチェックを入れる必要があります。

 C6セル=EDATE(C5,(YEAR(E1)-YEAR(C5))*12+MONTH(E1)-MONTH(C5)+1)

 ところで、なぜ英語の番目(st,nd,rd,th)を使用するのでしょうか？
 その為にすごく複雑な式を使わなければならなくなっています。
 日本語でよければ、
　 A4セル=IF(OR(C5="",C6<C5),"時期尚早",TEXT(DATEDIF(C5,C7+4,"M"),"第0回目"))
 で済んじゃうのですが。。。

 <追記>
 EDATE関数を使う場合は、A4を先に求めた方が楽です。

 (1) A4セル =(YEAR(E1)-YEAR(C5))*12+MONTH(E1)-MONTH(C5)+2
      ※ A4セルの書式(表示形式)を ユーザー定義で　"第"0"回目"　と設定

 (2) C6セル =EDATE(C5,A4-1)

 (3) C7セル =EDATE(C5,A4)

  （半平太）

 半平太さん
 ありがとうございます！なるほど、詳しい説明で初心者でも分かりやすかったです。
 説明を聞いて式を見ればフムフムと納得出来るのですが、これを自分で考えて作るのは大変ですね。

 EDATE用と日本語用の式まで教えて頂いてありがとうございます。
 明日早速練習で入力してみます。

 実はこの請求書は海外に出すものなんですよ。
 本当はコンピューターではなく船のリースでして（＾＾）

コメント返信：

[ 一覧(最新更新順) ]

[[20081128104301]] 『何回目？』 ページの最後に飛ぶ

[[20081128104301]]　『何回目？』　ページの最後に飛ぶ