[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『開いた日のリースの残回数と残高を自動計算される関数を教えて下さい』（miffy）
A開始月 B終了月 C総支払回数 Dリース料 E残回数 F残高
上記のような表で、開始月、終了月、リース料を手入力すれば、 その他の項目が自動計算される関数を教えてください。
PCはExcel2003、WindowsXPです。
サンプルデータをアップしてください。
(3年間リースで、4ヶ月以上経過した或る日、とでも想定して作ってみてください)
(半平太) 2010/12/07 17:59
A開始月2007/8、B終了月2013/7、C回数72回、Dリース料￥45,000、E残回数？、F残高？
の場合ではどうなるでしょうか？
＞・・場合ではどうなるでしょうか？
それはこちらがお訊きしたいのですけど？
クエスチョンマークのところをそちらで埋めてください。 (それを見て、こちらの対策が正しいことを確認しますので)
行 ___A___ ___B___ _____C_____ ____D____ ____E____ ___F___ _______G_______ 1 開始月 終了月 総支払回数 リース料 残回数？ 残高？ (想定オープン日) 2 2007/8 2013/7 72 45000 2007/12/4
あと、A列とB列は、年月のみで、仕切りとしてスラッシュ(/) が入っている文字列データですね？
(半平太) 2010/12/07 20:45
厳密にいうと、A列とB列はリースの引落し日がリース会社によって違う（例：8/3や8/27等）ので、より正確な残高を知るためには2007/8/3としたいのですが・・・
miffy
> 厳密にいうと
あのー、私としては、厳密な算出式を作って差し上げたいと考えております。
その為には、厳密な条件をご提示いただく必要があります。
>より正確な残高を知るためには2007/8/3としたいのですが・・・ それは、開いた日以前に引落日が到来するものは残高を減らし、 引落日未到来なら同じ月でも残高は減らさない様にしたい、と云う意味ですよね？
そうすると、A列とB列は「年月」だけでなく「日」のデータが必要になると思いませんか？
・・と云うより、引落日はいつなのかがハッキリ分かるデータが必要ですよ。
10日や25日なんてのは簡単でしょうが、月末なんてものは日にちが決まっていません。 それを今どう管理しているのですか？
普通は「１、５、１０、１５、２０、２５、月末(これは３０とか９９とかで管理) ただし、26～月末前日は存在しない。」なんてことでやっているハズなんですけど。
あと土日祝日は、翌営業日(ただし、月末の場合は前営業日)になる、なんてことも契約書に 書いてあると思いますが、そんな事は考えなくていいのですか？
もっと考えれば、延滞が生じている先はどうします？ 自動計算じゃまずいんじゃなーい？ なんてこともありますよ。 別ブックで管理しているかも知れませんけど、老婆心からお聞きしております。
(半平太) 2010/12/08 14:44
おっしゃる通り、開いた日以前に引落日が到来するものは残高を減らし、
引落日未到来なら同じ月でも残高は減らさない様にしたいです。
引落し日については、私どもで扱っているリースでは『毎月3日』というのが一番多いので、それで統一したいと思います。
その他の引落し日や土日祝日による支払日のズレ、延滞等までは考慮する必要がありませんので、多少の誤差は大丈夫です。
データ表の残高を基に見積りを作成・提案⇒契約⇒納品⇒残高処理
･･･という流れで、提案～納品までにかかる日数は物件により様々ですので、いずれにしても多少の誤差を考慮しながらになります。
1ヵ月位の誤差は許容範囲内ですので、宜しくお願いします。
miffy
い・・・いいのか・・・？
>いずれにしても多少の誤差を考慮しながらになります。 >1ヵ月位の誤差は許容範囲内ですので、
かなりアバウトでいい訳ですかぁ、楽ですねぇ ＾＾
※ でも本当にA、B列は文字列なんですよね。これが違うと計算合わなくなります。。。念の為。
(1) C2セル =IF(COUNTA(A2:B2)<2,"",DATEDIF(A2,B2,"M")+1) (2) E2セル =IF(C2="","",IF(B2-DAY(B2)+3<TODAY(),"終了",DATEDIF(TODAY(),B2-DAY(B2)+3,"M")+1)) (3) F2セル =IF(C2="","",D2*N(E2))
<Sheet1> 結果図 行 ___A___ ___B___ _____C_____ ____D____ ___E___ ____F____ 1 開始月 終了月 総支払回数 リース料 残回数 残高 (想定オープン日) 2 2007/8 2013/7 72 45,000 32 1,440,000 (2010/12/3) 3 2007/8 2013/7 72 45,000 31 1,395,000 (2010/12/4) 4 2007/8 2013/7 72 45,000 31 1,395,000 (2010/12/5) 5 2007/8 2013/7 72 45,000 1 45,000 (2013/7/1) 6 2007/8 2013/7 72 45,000 1 45,000 (2013/7/3) 7 2007/8 2013/7 72 45,000 終了 0 (2013/7/4) 8 2007/8 (2010/12/3) 9 (2010/12/4)
(半平太) 2010/12/08 17:37
想定オープン日が (2013/7/3) で残高0になるべきでしたら、、
ここ2に変えてください。(二か所) ↓ ↓ E2セル =IF(C2="","",IF(B2-DAY(B2)+3<TODAY(),"終了",DATEDIF(TODAY(),B2-DAY(B2)+3,"M")+1))
(半平太) 2010/12/08 17:46
ほんとにほんとにありがとうございました。
miffy
=IF(C2="","",IF(B2-DAY(B2)+2<TODAY(),"終了",DATEDIF(TODAY(),B2-DAY(B2)+2,"M")+1)) で設定させて頂きましたが、月末の場合はどうなるのでしょうか？ 2月は28日だし、うるう年は29日、月末は30日もあるし31日もあります。 月末日で計算は「2」の部分の変更だけでいいのでしょうか？ どのようにしたらいいですか？
（アン） 2015/09/06(日) 12:10
＞ =IF(C2="","",IF(B2-DAY(B2)+2<TODAY(),"終了",DATEDIF(TODAY(),B2-DAY(B2)+2,"M")+1)) ＞で設定させて頂きましたが、月末の場合はどうなるのでしょうか？
その数式は、「アバウトでいい」と云う妙に気楽な条件で作ったもので、 1箇月くらいの誤差がでます。(と思う）
うるう年のことを考えると云うことは「そんなに」アバウトではない気がします。
「月末の場合」とは、全契約について一律に月末請求と云うことですか？ それとも、月末もあれば、このトピックの様に3日もあれば、10日の場合もあると云うことなのですか？
後者であれば、分岐処理が必要になります。つまり請求日の区分データがどこにどんな形で 存在しているかと云うご説明も必要になります。
（半平太） 2015/09/06(日) 16:47
リース開始日とリース満了日は手入力で文字列で作成してあるので
総支払い回数
=IF(COUNTA(A2:B2)<2,"",DATEDIF(A2,B2,"M")+1)
毎月3日引き落としはオープン日が3日であり「0」にしたいので
残回数
=IF(C2="","",IF(B2-DAY(B2)+2<TODAY(),"終了",DATEDIF(TODAY(),B2-DAY(B2)+2,"M")+1))
総支払い回数-残回数＝払った回数
...3日引き落とし分はセルに式を入れました。
月末は引き落としでなくリース会社へ振込の形になります。
なので実際は
２月であるならば28日で振込
うるう年ならば29日で振込
30日月は30日で振込
31日月は31日で振込
...という処理がしたいのです。
このパターンを月末として考えたいと思います。
3日の物も月末の物も銀行口座を通すものなので「土」「日」「祝日」が
あるのですが「土」「日」「祝日」による日付の誤差は想定内です。
宜しくお願いします。
（アン） 2015/09/06(日) 18:01
再度、申し上げますが、miffy さんへの回答案は、アバウトな数式です。 真面目に考えるのであれば、情報もチャンとしたものがないと作式出来ません。
＞リース開始日とリース満了日は手入力で文字列で作成してある １．これは具体的にどんな文字列ですか？ ※miffy さんのは「2007/8」とかで、日にち部分は無い文字列でした。
２．引き落としは毎月3日か、振込日は毎月末の2通りですね？
すると、どこかに「3」とか「振込」とか書いてある列が存在するのですか？
それとも、上でお聞きした「2007/8」が、アンさんのシートでは 「2007/2/3」とか「2007/2/28」とかなっていて、 その文字列を見れば3日か月末か分かる寸法になっているんですか？
あと、エクセルのバージョンを教えてください。
（半平太） 2015/09/06(日) 19:53
エクセル2007使用
1 -A----------B-------C-----------D---------E--------F-------G-------H
2 ﾘｰｽ会社 開始日 終了日 金額（税抜） 支払日 契約回数 支払済回数 残回数
3 ○○会社 2012/7/10 2017/7/9 10,000 月末
4 ○○会社 2012/6/1 2017/5/31 10,000 毎月3日
＊F列とG列はmiffyさんとのやりとりを参考にしましたが正当な式が他にありますでしょうか？
F列 G列 H列 が求めたいです。
宜しくお願い致します。
（アン） 2015/09/06(日) 20:49
こんな表になっていますね。
行 ____A____ ____B____ ____C____ ______D______ ___E___ ____F____ _____G_____ ___H___ 1 ﾘｰｽ会社 開始日 終了日 金額（税抜） 支払日 契約回数 支払済回数 残回数 2 ○○会社 2012/7/10 2017/7/9 10,000 月末 3 △△会社 2012/6/1 2017/5/31 10,000 毎月3日
１．○○会社の最終支払日はいつですか？ 2017/6/30 か 2017/7/31 のどちらかだろうと推測はしますが。
２．△△会社の最終支払日はいつですか？ 2017/5/3 か 2017//3 のどちらかだろうと推測はしますが。
それが分からないと、Today(=オープン日)との関係で、支払済み回数が変わってくるのですけど。
３．B列とC列の日付は、本当に文字列ですか？（日付シリアル値かも知れないと思えてくるのですけど・・・）
＞＊F列とG列はmiffyさんとのやりとりを参考にしましたが正当な式が他にありますでしょうか？ miffyさんのはいい加減ですからねぇ・・・彼(女）がそれでいいと云ったので、私はあまり頓着しないで作りました。
（半平太） 2015/09/06(日) 21:28
2行目の○○会社 月末支払日 最終支払日（2017/7/31）
3行目の△△会社 毎月3日 最終支払日（2017/6/3）
...となります。
B列とC列の日付は、本当に文字列ですか？（日付シリアル値かも知れないと思えてくるのですけど・・・）
↑
...テンキ-で日付の入力しています。
セルの書式設定 表示形式 分類→日付 種類→ ＊2001/3/14 カレンダーの種類→西暦
となっています。
（アン） 2015/09/06(日) 21:51
結構ややこしいですね。(^_^;)
(1) F2セル =IF(COUNT(B2:C2)<2,"",DATEDIF(B2,C2+1,"M")) (2) G2セル =IF(F2="","",MIN(F2,DATEDIF(EOMONTH(B2,-(RIGHT(E2)<>"末"))+1+2*(RIGHT(E2)<>"末"),TODAY()+(RIGHT(E2)="末"),"M"))) (3) H2セル =IF(F2="","",F2-G2)
<結果図> 行 ____A____ ____B____ ____C____ ______D______ ___E___ ____F____ _____G_____ ___H___ 1 ﾘｰｽ会社 開始日 終了日 金額（税抜） 支払日 契約回数 支払済回数 残回数 2 ○○会社 2012/7/10 2017/7/9 10,000 月末 60 37 23 3 △△会社 2012/6/1 2017/5/31 10,000 毎月3日 60 39 21
（半平太） 2015/09/06(日) 23:22
私の勘違いがありまして
3 △△会社 2012/6/1 2017/5/31 10,000 毎月3日 （最終支払日が2017/5/3）でした。 Today(=オープン日)との関係で、支払済み回数がやはり合いませんでした。
G2セル =IF(F2="","",MIN(F2,DATEDIF(EOMONTH(B2,-(RIGHT(E2)<>"末"))+1+2*(RIGHT(E2)<>"末"),TODAY
()+(RIGHT(E2)="末"),"M")))
どの部分を修正すればいいでしょうか？
お手数をおかけしますが宜しくお願い致します。
（アン） 2015/09/07(月) 16:51
＞（最終支払日が2017/5/3）でした。
どう言う理屈(ルール)で、最終支払日が決まるのですか？
以下の例だと、最終支払日が4/3から5/3に変わるのは、契約終了日がいつの時ですか？
契約終了日 最終支払日 4/30 4/3 5/1 ? 5/2 ? 5/3 ? : ： 5/30 ? 5/31 5/3
（半平太） 2015/09/07(月) 17:27
行 ____A____ ____B____ ____C____ ______D______ ___E___ ____F____ _____G_____ ___H___ 1 ﾘｰｽ会社 開始日 終了日 金額（税抜） 支払日 契約回数 支払済回数 残回数 2 △△会社 2012/6/1 2017/5/31 10,000 毎月3日 ア イ ウ ↑ リース開始 6/1 第1回目支払い 6/3 第2回目 7/3 ： ： ： ： 第60回目 2017/5/3
＊当月引き落としなので
契約最終日月です。ただし3日引き落としなので5/3が契約最終日ならば4/3支払いが最後です。
もうひとつお願いがあります。
アは以前回答して頂いた式でOKなんですが 支払日を手入力でさまざまな日付を入れて
イ と ウを求める式はありますか？
リースの数が多く今現在いくつものシートで管理しています。
（アン） 2015/09/08(火) 12:39
まことに申し訳ありません。 m(__)m
支払うべき年月日の決定ルールが理解できないので、ドロップアウト致します。
（半平太） 2015/09/08(火) 20:26
もう一度考えてみました。
ロジックが複雑なので、自分で言うのもなんですが、半信半疑です。
(1) G2セル =IFERROR(IF(F2="","",MIN(F2,MAX(0,IF(E2="月末",DATEDIF(EOMONTH(B2,-((DAY(C2+1))=1))+1,TODAY()+1,"M"),SUMPRODUCT(MATCH(IF({1,0},EDATE(DATE(YEAR(B2),1,E2),(MONTH(B2)-1)+(DATE(YEAR(C2),MONTH(C2),E2)<C2)*(DAY(C2)<DAY(B2))),TODAY()),INDEX(EDATE(DATE(YEAR(B2),1,E2),ROW(INDIRECT("A1:A"&F2+12))),0))*{-1,1})+1)))),0)
(2) H2セル =IF(F2="","",F2-G2)
<結果図> 行 ____A____ ____B____ ____C____ ______D______ ___E___ ____F____ _____G_____ ___H___ 1 ﾘｰｽ会社 開始日 終了日 金額（税抜） 支払日 契約回数 支払済回数 残回数 2 ○○会社 2012/7/10 2017/7/9 10,000 月末 60 37 23 3 △△会社 2012/6/1 2017/5/31 10,000 3 60 40 20 4 ○○会社 2012/7/10 2017/7/9 10,000 3 60 38 22 5 △△会社 2012/6/1 2017/5/31 10,000 3 60 40 20 6 ○○会社 2015/4/30 2016/4/29 10,000 3 12 5 7 7 △△会社 2015/5/1 2016/4/30 10,000 3 12 5 7 8 ○○会社 2015/5/4 2016/5/3 10,000 3 12 5 7 9 △△会社 2015/5/5 2016/5/4 10,000 3 12 4 8 10 ○○会社 2015/6/1 2016/5/31 10,000 3 12 4 8 11 △△会社 2015/2/28 2016/2/27 10,000 30 12 7 5 12 ○○会社 2015/3/1 2016/2/29 10,000 30 12 6 6 13 △△会社 2015/3/31 2016/3/30 10,000 30 12 6 6 14 ○○会社 2015/4/1 2016/3/31 10,000 30 12 5 7
※ 実務でも、本当にこんな複雑な条件で作業しているんでしょうか・・・・
（半平太） 2015/09/09(水) 13:35
かなり大変だった事でしょう...
べた打ち入力+電卓とかなりアナログでしたので毎月数字、金額、回数残に手をやいていました。
あきらめていたのですがほんとうにありがとうございました。
こんなになが～い式は初めて拝見しますがチャレンジしてみます！
（アン） 2015/09/12(土) 17:06
＞こんなになが～い式は初めて拝見しますがチャレンジしてみます！
式はコピペで入力してください。
最終支払日(または第一回支払日)を正しく決定するのが難しく、 それ以外はそんなに難度は高くないです。
なので、もし、その日にちが、どこか(たとえば回数列の左隣り）に 手入力してあるなら(または、今後手入力するルールにすれば）、 ズーと簡単に出来ます。
（半平太） 2015/09/12(土) 19:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.