[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルのみで年間リース料支払表を作成したいのですが・・・』（yomogi）
はじめまして。以下の条件で年間のリース料支払リストを作成したいのですが、作成の方法がよくわかりません。当方、マクロやVBAの知識がなく関数のみで作成できる方法を押してください。
＜条件＞
B1：対象期間の開始月（2016/04 等）
D1：対象期間の終了月（2017/03 等） ※A2〜D2は12ヶ月（1年間）
A3：物件名?@ 以下下へ向かって A6,A7,A8は順に 物件?A,物件?B,物件?C
B3：リース料支払開始月（2011/09 等）
C3：リース料支払終了月（2016/08 等）
D3：リース料支払回数（60(回)=5年 等）
E3：当初期間（5年）の月額リース料 （ \50,000 等 ※円単位・毎月均等）
F3：当初期間後、継続となった場合（延長リース）の延長リース料の支払開始月（2016/9）※ズレはなし
G3：延長リースの支払回数 ※必ず12回以上
H3：延長リース料の月額リース料 （ \30,000 等 ※円単位・毎月均等）
I2〜T2：該当月 （上記の例の場合は 2016/04,2016/05,2016/06 ･･･ 2017/03 等）
I3〜T3：月額リース料 （上記例の物件?@の場合は 2016/04〜2016/08⇒50,000 + 2016/9〜2017/03⇒\30,000 と表示される）
上記の条件の場合、 I2〜T2 が自動的に表示され I3〜T3のリース料も自動的に表示されるような表を作成したいのです。
先に述べました通り、マクロやVBAの知識がなく関数だけで作成できればと考えております。
ベテランの方には非常に易しいとは思います。お恥ずかしい限りですが宜しくお願い致します。
追記）・対象期間にリース満了が到来しない場合は同じ金額が12個並びます
・全ての物件が延長するとは限りません（打ち切りになる場合があります）。その場合は「0」を表示したいと思います
よろしくお願い致します（yomogi）
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
（γ） 2016/02/27(土) 20:53
A B C D E F G H
1 自： 2016/4/1 至： 2017/03
2 支払開始月 最終支払月 回数 金額 延長開始月 延長期間 延長時金額
3 物件１ 2011/09 2016/08 60 ￥50,000 2016/09 12 ￥30,000
=============================================================================================
I J K L M N O P ･･･ T
2 2016/04 2016/05 2016/06 2016/07 2016/08 2016/09 2016/10 2016/11 ･･･ 2017/03
3 \50,000 \50,000 \50,000 \50,000 \50,000 \30,000 \30,000 \30,000 ･･･ \30,000
A1〜H3までがすべて埋まっているとして
?@ I2〜T1 を自動的に表示
?A I3〜T3 を自動的に表示
をさせたいと思います。
ご教示の程よろしくお願い致します。
（yomogi） 2016/02/27(土) 22:02
1列追加して、G3に延長の場合の終了月を表示させてもいいのなら
G3: 延長の場合の終了月 H3: 延長の支払い回数 I3: 延長の月額リース料
J列から月別表示
G3 =IF(COUNT(F3,H3)<2,"",EDATE(F3,H3-1))
J2 =IF(OR(B3>$D$1,MAX(C3,G3)<$B$1),"",MAX($B$1,B3))
K2 =IF(J2="","",IF(EDATE(J2,1)<=MIN($D$1,MAX($C3,$G3)),EDATE(J2,1),""))
K2をU2までコピー
J3 =IF(J2="","",IF(J2<=$C3,$E3,$I3))
J3をU3までコピー
年月はすべて月初のシリアル値だとする（表示形式〜ユーザー定義 yyyy/mm）
すみませんが、検証はお任せします。
> 全ての物件が延長するとは限りません（打ち切りになる場合があります）。 > その場合は「0」を表示したいと思います
↑ 意味がわからないので無視してます。どういうことですか？ （笑） 2016/02/27(土) 23:04
延長がある場合は、H3（支払い回数）だけ手入力の方がいいですね。
F3 =IF(H3="","",EDATE(C3,1))
G3 =IF(H3="","",EDATE(F3,H3-1))
表示形式〜ユーザー定義 yyyy/mm
こんな感じで。 （笑） 2016/02/27(土) 23:16
上の回答、勘違いかも･･･
こっちの例で言うと、J2以降の年月はすべての物件に共通ですか？ J2〜U2には、単にB1とD1の期間の12か月、を表示させればいいだけ？
> G3: 延長の場合の終了月 > H3: 延長の支払い回数 > I3: 延長の月額リース料
F3 =IF(H3="","",EDATE(C3,1)) G3 =IF(H3="","",EDATE(F3,H3-1))
H3は手入力
J2 =B1 K2 =EDATE(J2,1) U2までコピー
I3 =IF(OR($B3>J$2,MAX($C3,$G3)<J$2),0,IF(J$2<=$C3,$E3,$I3))
U3までコピー
こういうこと？
これも違うんだったら、支払い回数の下限を無視して 支払い回数を1桁にした簡単な例を出してください。 （笑） 2016/02/28(日) 02:47
これでいけると思いますよ。 ★入力必須箇所（B1,B3,D3,E3,G3,H3） ************************************************** A1=自： B1=2016/4/1 C1=至： D1=TEXT(EDATE($B$1,12)-1,"yyyy/mm/dd") ************************************************** B2=支払開始月 C2=最終支払月 D2=支払回数 E2=料金（月額） F2=延長開始月 G2=延長回数 H=延長料金（月額） ************************************************** A3=物件1 ※B3の表示形式を文字列にする。 B3=2011/9 C3=TEXT(EDATE(TEXT($B$3,"yyyy/mm/dd"),$D$3)-1,"yyyy/mm") D3=60 E3=50000 F3=TEXT(EDATE(TEXT($B$3,"yyyy/mm/dd"),$D$3+1)-1,"yyyy/mm") G3=12 H3=30000 ************************************************** I2=TEXT(EDATE($B$1,COLUMN()-9),"yyyy/mm") I3=IF(TEXT(I$2,"yyyy/mm/dd")<=TEXT($C$3,"yyyy/mm/dd"),$E3,IF($G3>=12,$H3,"")) (I2:I3)のセル範囲を選択後、T列まで数式をフィルコピー ************************************************** 金額箇所の表示形式：ユーザー定義型（\#,##0）、または通貨（項目より選択） （マリオ） 2016/02/28(日) 08:37
2が抜けてました(^^♪（訂正前）H=延長料金（月額）（訂正後）H2=延長料金（月額） 延長しない場合は、G3の箇所を空欄または「0」にしておけばよろしいかと。 金額箇所：E3,H3,I3〜T3ですね （マリオ） 2016/02/28(日) 08:50
訂正します。物件2以降の事を考慮すると、次のようにしなければなりませんでした(^^♪ C3=TEXT(EDATE(TEXT($B3,"yyyy/mm/dd"),$D3)-1,"yyyy/mm") F3=TEXT(EDATE(TEXT($B3,"yyyy/mm/dd"),$D3+1)-1,"yyyy/mm") I3=IF(TEXT(I$2,"yyyy/mm/dd")<=TEXT($C4,"yyyy/mm/dd"),$E4,IF($G4>=12,$H4,"")) （マリオ） 2016/02/28(日) 09:04
たぶん最初の回答は間違ってると思うので、2つめの回答を試してみてください。
> G3: 延長の場合の終了月 > H3: 延長の支払い回数 > I3: 延長の月額リース料
だとして
> 支払い回数を1桁にした簡単な例
B C D E F G H I 3 2016/07 2016/11 5 500 2016/12 2017/01 2 300
↑ の場合、J2〜U2、J3〜U3 はどうなればいいのか、ということです。 なお、 D1 =EDATE(B1,11)
D1に ↑ の式を入れておけば、B1に入力するだけで自動的に表示されます。
B1とD1の表示形式〜ユーザー定義 yyyy/mm
B1には 2016/4 のように入力します。
C3にもG3と同じような式を入れておけば、いちいち入力する必要はありません。 セルの配置は変えた方がいいかもしれませんが。 （笑） 2016/02/28(日) 09:48
上記の記載は全部、無視してください。 上記のIF($G3>=12,$H3,"")では対応しきれてないことに気づきました。 下記の記載のみ見て試してみてください。
★入力必須箇所（B1,B3,D3,E3,G3,H3） ************************************************** A1=自： B1=2016/4/1 C1=至： D1=TEXT(EDATE($B$1,12)-1,"yyyy/mm/dd") ************************************************** B2=支払開始月 C2=最終支払月 D2=支払回数 E2=料金（月額） F2=延長開始月 G2=延長回数 H2=延長料金（月額） ************************************************** A3=物件1 ★B3の表示形式を文字列にする。 B3=2011/9 C3=TEXT(EDATE(TEXT($B3,"yyyy/mm/dd"),$D3)-1,"yyyy/mm") D3=60 E3=50000 F3=TEXT(EDATE(TEXT($B3,"yyyy/mm/dd"),$D3+1)-1,"yyyy/mm") G3=12 H3=30000 ************************************************** ★作業列を作る U3=TEXT($B3,"yyyy/mm/dd") V3=TEXT($C3,"yyyy/mm/dd") W3=TEXT($F3,"yyyy/mm/dd") X3=TEXT(EDATE(TEXT($F3,"yyyy/mm/dd"),$G3-1),"yyyy/mm/dd") ************************************************** I2=TEXT(EDATE($B$1,COLUMN()-9),"yyyy/mm") I3=IF(AND(TEXT(I$2,"yyyy/mm/dd")>=$U3,TEXT(I$2,"yyyy/mm/dd")<=$V3),$E3,IF(AND(TEXT(I$2,"yyyy/mm/dd")>=$W3,TEXT(I$2,"yyyy/mm/dd")<=$X3),$H3,""))
(I2:I3)のセル範囲を選択後、T列まで数式をフィルコピー ************************************************** 金額箇所の表示形式：ユーザー定義型（\#,##0）、または通貨（項目より選択）
+++++++++++++++++++++++++++++++++++++++++++++++++++ 以下のデータで、C列フィルコピー,F列フィルコピー,I〜X列フィルコピーしてみてください。 A4=物件2 B4=2016/5 D4=6 E4=60000 G4=12 H4=40000 （マリオ） 2016/02/28(日) 10:30
＞上記の記載は全部、無視してください。 すいません。言葉足らずでm(_ _)m 僕が記載した記事で、かつ、（2016/02/28(日) 10:30）より前の記事には間違いがあるので、 参考にしないでくださいの意味です。 （笑）さんも記事アップしてるに、この書き方は大変失礼だと気づきました。 （笑）さん、すいません。 （マリオ） 2016/02/28(日) 10:46
二通りの方法を教えていただいた通りに入力させていただきました。
どちらも満足でした。
こちらの説明方法が悪くいろいろご思案いただきまして有難うございました。
単純にJ2に手入力で開始日付を入力して右へオートフィルでも良かったのですが、
毎回、オートフィルをする手間を省きたかっただけです。大変失礼致しました。
あわせて数点ご質問とご相談ですが、
１．2016/02/28(日) 02:47 に教えていただいた計算式で
＞I3 =IF(OR($B3>J$2,MAX($C3,$G3)<J$2),0,IF(J$2<=$C3,$E3,$I3)) の部分ですが、
「I3」は延長の月額リース料のセルですがそこにこの計算式を入力するのでしょうか？
一番初めに教えていただいた結果の方が正しいように思うのですが･･･
２．例えば、物件１（=A3）の支払開始月（=B3）が 2016/6 の場合 2016/4と2016/5のリース料欄
（=J3,K3）にも数値が入ってしまいます。開始前なので「0」となって欲しいのです。
３． > 全ての物件が延長するとは限りません（打ち切りになる場合があります）。
> その場合は「0」を表示したいと思います ↑ 意味がわからないので無視してます。どういうことですか？
の部分ですが、こちらも説明が下手で申し訳ありませんでした。
具体的に申し上げますと、
2011/6〜2016/5の支払期間（5年）で支払いが終了（契約が終了）し、延長がない場合は
2016/6以降の L3〜U3 のセルは 「0」と表示させたかったのです。
⇒こちらは、「延長金額」欄（=I3）に0 を入力すれば結果は得られました。
延長しない場合は「0」と入力すれば良かったのですね。
４．金額が変更になった月のセルに（今回の場合は O3 ）黄色の色付けをすることは可能でしょうか？
ご指導いただければ幸甚です。
よろしくお願い致します。
（マリオ）様
アドバイス有難うございます。
こちらも試してみたいと思いますので、またご報告させていただきます。
ご指導よろしくお願い致します。
何分素人の私にとっては非常に貴重なご指導でございます。
今回このサイトを初めて利用させていただきましたが、このように素晴らしい方々にご指導いただけとても感謝しております。引き続き宜しくお願い申し上げます。
（yomogi） 2016/02/28(日) 10:59
↑の質問１で「I3」に入力すべき計算式を「J3」に入力すれば思った通りのデータ出来ました！
その後、物件２以降も下に向けてオートフィルするだけで思い通りの資料となりました！
ご質問いただきました簡単な短期の例について希望する表示方法は
B3：2016/07
C3：2016/11
D3：5
E3：500
F3：2016/12
G3：2017/01
H3：2
I3：300 の場合の 希望する表示については
J2〜U2 には 2016/04 〜 2017/03 を表示
金額欄には
2016/04の時 O
2016/05の時 0
2016/06の時 0
2016/07の時 500
2016/08の時 500
2016/09の時 500
2016/10の時 500
2016/11の時 500
2016/12の時 300
2017/01の時 300
2017/02の時 300
2017/03の時 300
となる様に表示させたいと思います。
対象期間内（2016/4〜2017/3）に該当するすべてのセルに数値を表示させたいと思います。
※対象期間内に支払いが発生しなければ「0」と表示です。
なかなか上手く説明出来ませんがよろしくお願い致します。
（yomogi） 2016/02/28(日) 11:56
> 「I3」は延長の月額リース料のセルですがそこにこの計算式を入力するのでしょうか？
すみません、「J3」の間違いでした。申し訳ない。
最初の回答は、物件ごとに該当する支払い年月を表示させるものなので 2つめの回答の方で検証してください。 > 2017/02の時 300 > 2017/03の時 300
その例だと、延長の終了は「2017/01」なので、2月と3月は「0」じゃないんですか？
それとB3が「2016/07」なら、こちらでは 2016/04〜2016/06 は「0」になります。 ならないんでしょうか？ もう一度よく見直してみてください。
B3はシリアル値になっていますか？ 表示形式〜ユーザー定義を yyyy/mm にして「2016/7」と入力しましたか？ > 延長しない場合は「0」と入力すれば良かったのですね。
延長しない場合は、H3（延長の支払い回数）に何も入力しないで下さい。 > ４．金額が変更になった月のセルに（今回の場合は O3 ）黄色の色付けをすることは可能でしょうか？
条件付き書式を使ったことはありますか？
> 以下下へ向かって A6,A7,A8は順に
物件名が飛び飛びになっているんですか？ 違いますよね？ 物件名が3行目から10行目までだとすると J3からU10を範囲選択して、条件付き書式の数式
=J$2=$F3 ← この式を入れて、書式から色を選択
くり返しますが、延長しない場合はH列（支払い回数）に何も入力しない、 つまりF列（延長開始）には何も表示されていない、という前提です。
> F3 =IF(H3="","",EDATE(C3,1)) > G3 =IF(H3="","",EDATE(F3,H3-1))
条件付き書式で数式を利用する方法 http://www4.synapse.ne.jp/yone/excel2010/excel2010_jyosyo_susiki.html
参考まで。 （笑） 2016/02/28(日) 12:18
また間違ってるかも･･･
> 金額が変更になった月のセル
延長の開始が「2016/04」以前だったら、最初のJ列から延長の料金になりますよね？ その場合はどうするんですか？ J3に色をつけるんですか？ （笑） 2016/02/28(日) 12:38
＞それとB3が「2016/07」なら、こちらでは 2016/04〜2016/06 は「0」になります。
↑これも完璧に表示されました！
金額が変更した場合はやはり「条件付き書式」を利用するのですね。
うすうすは感じていましたが、どのような式を入れてよいのかわからず･･･ でした。
今回は延長かどうかにはこだわらず、年間を通して金額が変更となる以降の部分だけをセルに色づけしたいだけですので、最初の回答で十分です。
また、リース契約は基本12ヶ月以上ですので、1年間に2回以上変更となる場合（延長が2回発生する場合）は滅多にありませんので、万一、そのような状況が多数発生する際はまたご相談させていただきます。
ご指導、本当に有難うございました。
（マリオ）様
未熟な私の質問にご指導くださり、誠に有難うございました。
これからもこのサイトを利用させていただきますので、また閲覧された時にはご指導の程、宜しくお願い申し上げます。
＝＝＝ここからは余談ですが＝＝＝＝
今回、皆様のようなプロの方々にご指導いただきながら作業していますと、だんだんエクセルが楽しくなって参りました。
普段、何時間も何日も時間をかけて作業していたことが非常に効率化出来てしまうのだなぁと反省し痛感致しました。
これからもこれまでに手を出してこなかった「マクロ」や「VBA」にもチャレンジしたいと思います。
また、（笑）さんはいつも速やかにご指導をくださいますが、いったいいつ寝ておられるんですか？？
とても不思議に思いましたので･･･
有難うございました。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
（yomogi） 2016/02/28(日) 15:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.