異なる締め日に対応して支払日を自動表示させるには

質問

それぞれ決済条件の違う（締め日、支払日、現金か手形か）仕入先の支払予定金額を集計したいのですが
例えば・・・
あ社：納品日3/05 \10,000 （決済：毎月末締め、翌月末現金支払）
い社：納品日3/07 \15,000 （決済：毎月末締め、翌々5日現金支払）
う社：納品日3/10 \20,000 （決済：毎月20日め、当月末現金支払）
え社：納品日3/12 \25,000 （決済：毎月20日締め、翌月末現金支払）
お社：納品日3/15 \30,000 （決済：毎月20日締め、翌々5日現金支払）
か社：納品日3/17 \35,000 （決済：毎月末締め、翌末手形90日支払）
き社：納品日3/20 \40,000 （決済：毎月末締め、翌々5日手形90日支払）

※手形条件の先は手形発効日ではなく手形が引落しされる日を把握したいと思っています※
例えば、”か社”（毎月末締め、翌末手形90日支払）でいえば
3/17に納品してもらっているので3/末締め→4/末手形発行→7/31\35,000引落し という事です

1件づつ手入力していけば可能なのはわかっているのですが、
件数が毎月数千件ありまして。。。

下のように(TESTファイルをアップさせて頂いています)のように”社名”、”決済条件”、”納品日”、”金額”は手入力します
決済条件は左のリストからプルダウンリストで選べるようにしてあります
この情報を入力すれば”現金支払日”、”手形引落日”に自動的に日付を表示できる関数はどのように書けばよろしいでしょうか

https://box.yahoo.co.jp/guest/viewer?sid=box-l-vadgkyocquf44rpb2bpq6rkhxy-1001&uniqid=068dc4aa-5992-414f-9919-a1d8bac92341&viewtype=detail

おわかりの方ご伝授願えませんでしょうか
どうぞ宜しくお願い致します

回答No.6

tom04 · ベストアンサー

No.3です！
補足の件について・・・

H2セルに
=IF(ISNUMBER(FIND("手形",B2)),IF(ISNUMBER(FIND("翌末",B2)),EOMONTH(C2,1),EOMONTH(C2,1)+5),"")
としてオートフィルで下へコピーではどうでしょうか？

尚、エラー処理していませんのでC列（納品日）が空白の場合は
へんてこな数値（1900年代）の表示になると思います。
IF関数で適宜対応してみてください。

お役に立ちますかね？m(_ _)m

回答No.5

mt2008

ANo.1です。

分析ツールが正常に組み込まれていないのかもしれませんね。

=EDATE(NOW(),1)
↑この式で一か月後の日付（または日付のシリアル値）が表示されますか？
＃「41014」の様な日付のシリアル値が表示されるときは表示書式を日付に変更してください。

補足

ありがとうございます！
エクセルの設定を変えたところできました！本当に感謝です！
しかし、更にG列の隣のH列にもう1列加えたい項目がございまして・・・
それは手形の”発効日”です

例えば
”か社－3/17納品－毎月末締め、翌末手形90日支払”であればH列に”4/30”と自動表示したいのです

手形の引き落とし日は既に表示されているので、このH列に手形発行日を自動表示もお願いできませんでしょうか
お手数おかけいたしますがご検討宜しくお願い致します

回答No.4

mt2008

ANo.1です。

> それで見よう見まねで打ち込んでみたのですが、上手く反映されない・・・
> との通りに関数などはコピペなどしてみたのですが・・・
今、手元の環境にExcelが無いのでOOoのCALCで開いて見ました。
OOoですので私のところで正しく表示されていないだけかもしれませんが、
式中のFALSEをFALSE()にしていませんか？

補足

E2には・・・　=EDATE(C2-DAY(C2)+1,1*((DAY(C2)-VLOOKUP(B2,支払,2,FALSE))>0))
F2には・・・　=IF(VLOOKUP(B2,支払,7,FALSE)="現金",EDATE(E2,VLOOKUP(B2,支払,3,FALSE))-1+VLOOKUP(B2,支払,4,FALSE),"")
G2には・・・　=IF(VLOOKUP(B2,支払,7,FALSE)="現金","",EDATE(E2,VLOOKUP(B2,支払,5,FALSE))-1+VLOOKUP(B2,支払,6,FALSE))

上記数式が作成してUPさせて頂いたものに入力されているものになります
お手間おかけいたします。。。
ご見識お伺いできると幸いです
よろしくお願い致します

回答No.3

tom04

こんばんは！
横からお邪魔します。

手形は「月末」か「5日」のいずれかに受け取る訳ですよね？
受取日から90日後が引落日だとしています。
手形の90日というのは手形を受け取って3か月としてみました。
EOMONTH関数を使用していますので、
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておいてください。

両セルともセルの表示形式は「日付」にして・・・
無理やりIF関数のネストでやってみました。

F2セルに
=IF(ISNUMBER(FIND(20,B2)),IF(ISNUMBER(FIND(5,B2)),EOMONTH(C2,1+IF(DAY(C2)<=20,0,1))+5,IF(ISNUMBER(FIND("当月",B2)),EOMONTH(C2,0+IF(DAY(C2)<=20,0,1)),EOMONTH(C2,1+IF(DAY(C2)<=20,0,1)))),IF(ISNUMBER(FIND("末現金",B2)),EOMONTH(C2,1),IF(ISNUMBER(FIND("現金",B2)),EOMONTH(C2,1)+5,"")))

G2セルに
=IF(ISNUMBER(FIND("手形",B2)),IF(ISNUMBER(FIND("翌末",B2)),EOMONTH(C2,4),EOMONTH(C2,4)+5),"")

としてオートフィルで下へコピーではどうでしょうか？

※　手形受け取りから実質90日の場合はG2セルの数式を
=IF(ISNUMBER(FIND("手形",B2)),IF(ISNUMBER(FIND("翌末",B2)),EOMONTH(C2,1)+90,EOMONTH(C2,1)+5+90),"")
にしてみてください。

何とか希望に近い形にならないでしょうか？m(_ _)m

補足

ありがとうございます！
エクセルの設定を変えたところできました！本当に感謝です！
しかし、更にG列の隣のH列にもう1列加えたい項目がございまして・・・
それは手形の”発効日”です

例えば
”か社－3/17納品－毎月末締め、翌末手形90日支払”であればH列に”4/30”と自動表示したいのです

手形の引き落とし日は既に表示されているので、このH列に手形発行日を自動表示もお願いできませんでしょうか
お手数おかけいたしますがご検討宜しくお願い致します

回答No.2

mt2008

ちょっとゴチャゴチャとしちゃっています。
EDATE関数を使用しますので、アドインで分析ツールを組み込んでください。

別シートでいいの決済条件毎に締め日や支払日を一覧にした表を作ります。
添付の図の下の方がその表です。
項目は以下の7つ
　1.決済条件
　2.締日（月末の場合は31固定）
　3.現金払いの場合の支払月（何か月後）
　4.現金払いの場合の支払日
　5.手形払いの時の支払月（何か月後）
　6.手形払いの時の支払日
　7.現金なのか手形なのか

この時、支払日が月末の時は支払月に+1した数、支払日は0と登録します。
例えば、「毎月末締め、翌末手形90日支払」の場合、実際の支払は4か月後の月末なので、支払月：5、支払日：0です。
添付の図の場合、A4:G10が一覧表です。この範囲に名前定義します。
今回、「支払」と名付けました。

次に実際に表示するシートに移ります。
今回、E列を作業列として使用します。この列に表示されるのは締めた月です。
E2に↓
=EDATE(C2-DAY(C2)+1,1*((DAY(C2)-VLOOKUP(B2,支払,2,FALSE))>0))

F2に↓
=IF(VLOOKUP(B2,支払,7,FALSE)="現金",EDATE(E2,VLOOKUP(B2,支払,3,FALSE))-1+VLOOKUP(B2,支払,4,FALSE),"")

G2に↓
=IF(VLOOKUP(B2,支払,7,FALSE)="現金","",EDATE(E2,VLOOKUP(B2,支払,5,FALSE))-1+VLOOKUP(B2,支払,6,FALSE))

と、入れたらE2:G2を下にコピーしてください。
作業列は非表示にでもしておいてください。

もう少しシンプルにしたかったのですが、このくらいが限度でした。図もちゃんと見えるかなぁ……

補足

mt2008さん、ありがとうございます！本当に何かお礼がしたい。

それで見よう見まねで打ち込んでみたのですが、上手く反映されない・・・
との通りに関数などはコピペなどしてみたのですが・・・
一応下記にUPしてみました。
ご精査して頂けるとありがたいのですが宜しくお願い致します

https://box.yahoo.co.jp/guest/viewer?sid=box-l-vadgkyocquf44rpb2bpq6rkhxy-1001&uniqid=060b8bc5-6c71-4fd7-b96c-84460b1f6b72&viewtype=detail

回答No.1

mt2008

補足願います。
・アプリ名がありませんが、Excel2003でよろしいですね？
・「翌末手形90日支払」は、翌月末に手形が発行されてその90日後…ではなく、3か月後支払と言う意味ですか？

補足

失礼しました。EXCEL2000です（2003機種もあるので2003でもOKです）
あと手形ですが、その通りです。
2月度の請求金額に対して3/末日(3月で言えば・・・3/31)に手形発行でお支払します
そして発行された手形は3ヵ月後の6月末日(6月で言えば6/30)に銀行から
引き落とされるという事と考えて頂いて宜しいです

何卒、ご伝授お待ちいたしております
宜しくお願い致します