納品日から得意先ごとの請求日を表示したい。

質問

VLOOKUPを使って請求書を作っています。
得意先ごとに請求日が異なっており、納品日から起算して指定の請求日を設定したいのですが。
例えば参照先の得意先マスターにEOMONTHやDAY関数を登録して引用とかできるのでしょうか？

回答No.2

zap35 · ベストアンサー

#01です
補足ありがとうございました。まだ疑問はありますが、いきがかり上、その点は想像で補います。
まず、請求書のシートに得意先の情報を引っぱってくる得意先情報が必要です。そこで次のようなサンプルを想定します

A1:得意先コード　　B1：得意先名　式は=VLOOKUP(D2,得意先シート!範囲,2)
B2：納品日　

の状態で、D2:に納品日より未来で直近の請求日（＝得意先シートの締め日から算出）をセットするのが目的ということで良いですか？　（それとも本日以降の直近の締め日が適切でしょうか）

もう一つ前提を決めなければなりません。それは得意先シートの締め日（C列）の記述方法です。日数が２０日のように明確に決まっていれば問題がありませんが、「月末」の場合はどのように記述されるのでしょうか？　それが分からないので「C列には数字もしくは”月末”という文字列が入っている。」と仮定します。

関数で可能ですが、だいぶ長くなりますよ。

=IF(ISNUMBER(VLOOKUP(A1,得意先シート!範囲,3)),IF(VLOOKUP(A1,得意先シート!範囲,3)>DAY(B2),DATE(YEAR(B2),MONTH(B2),VLOOKUP(A1,得意先シート!範囲,3)),DATE(YEAR(B2),MONTH(B2)+1,VLOOKUP(A1,得意先シート!範囲,3))),IF(B2=DATE(YEAR(B2),MONTH(B2)+1,1)-1,DATE(YEAR(B2),MONTH(B2)+2,1)-1,DATE(YEAR(B2),MONTH(B2)+1,1)-1))

ただし制限事項があります。29,30,31という締め日は正しく処理できません。月によってはその日がないからです。ですからこれらの日付は「月末」となっているものとします

意味が分かりにくければ補足で質問して下さい

補足

ありがとうございます。思っていたことが完璧にできました。
あと質問ついでで申し訳ありませんが、納品日が締日（請求日）になる得意先もあるのですがどのように処理をすればよろしいでしょうか？
ただ式を真似ただけなので、数値や文字をどのように区別しているのか今の私ではまだ理解できませんのでよろしくお願いします。

回答No.4

zap35

＃０３さんのご指摘はごもっともですね。実業務ではもう少し条件が複雑だとは思います。次回質問されるときは今回の補足質問のようなときはどうするか予め書かれるとよいでしょう。

それはともかく補足質問に回答します。この式は４つの場合分けをしています
１）締め日が数字、締め日＞納品日
　DATE(YEAR(B2),MONTH(B2),VLOOKUP(A1,得意先シート!範囲,3)　
　（年月は納品月と同じ、日は締めの日）をDATE関数で生成
２）締め日が数字、締め日＜＝納品日
　DATE(YEAR(B2),MONTH(B2)+1,VLOOKUP(A1,得意先シート!範囲,3))
　（年月は納品月＋１、日は締めの日）をDATE関数で生成

３）締め日が数字でない（＝月末）、納品日＝月末日
　DATE(YEAR(B2),MONTH(B2)+2,1)-1
　翌月の月末日を請求日として生成（翌々月のついたちの一日前で計算）
４）締め日が数字でない、納品日≠月末でない
　DATE(YEAR(B2),MONTH(B2)+1,1)-1)
　今月の末日を請求日として生成（翌月ついたちの１日前）

＞納品日が締日（請求日）になる得意先もあるのです
は現在の式では翌月の締め日を請求日としています。
もし納品日が締め日のとき、当日請求するなら
　IF(VLOOKUP(A1,得意先シート!範囲,3)>DAY(B2),
の条件判定を
IF(VLOOKUP(A1,得意先シート!範囲,3)>=DAY(B2),
に修正します。（３５文字目くらいにあるIF文です）

補足

zap35　様
度々丁寧な説明でありがとうございます。
取り敢えずご指摘のように「＝」（下記）を入れてみましたが、「月末」とおなじ表記になってしまいます。
（～●得意先,4）の列に締日の数値以外に「月末」と「納品日」という文字を使っているので、区別されないのではないかと思いますが…？

=IF(ISNUMBER(VLOOKUP(A6,★商品顧客マスタ.xls!●得意先,4)),IF(VLOOKUP(A6,★商品顧客マスタ.xls!●得意先,4)>=DAY(I5),DATE(YEAR(I5),MONTH(I5),VLOOKUP(A6,★商品顧客マスタ.xls!●得意先,4)),DATE(YEAR(I5),MONTH(I5)+1,VLOOKUP(A6,★商品顧客マスタ.xls!●得意先,4))),IF(I5=DATE(YEAR(I5),MONTH(I5)+1,1)-1,DATE(YEAR(I5),MONTH(I5)+2,1)-1,DATE(YEAR(I5),MONTH(I5)+1,1)-1))

回答No.3

imogasi

＞完璧にできました
いまさらどうでも良いが、ほんとかな。ご回答がどうこうはなくて、質問が大雑把で、こんなことで仕事に使うの大丈夫ですか。締め日が休日ならどうするとか色々ややこしいルールがあるのではないですか。
＞納品日から起算して指定の請求日を設定したいのですが
納品日の日付からＸＸ日後でよいのですか。請求は月１回とかまとめるのでは。
仕事に使うときは、文章に正確に表現した仕様書を残し、関係各部署のエクスパートに慣習も聞き、得意先にも理解を求めてやるのが普通ではないですか。その過程でもう少し考えが整理され、質問の表現も
適切になるのが普通では。
それにしごとで使うとなると、間数式が長くなったりしすぎるので
ＶＢＡを取り入れるなどは必須だというのが小生の持論です。

お礼

文面で質問するには確かに説明不足だったと思います。
今回は知り合いに頼まれ、運用も個人レベルなのでなんとか関数だけで作ろうとしています。しかし使用している内に多角的な処理も必要になってくると、ＶＢＡなどで作るしかないと思いますが。私自身が初心者なので今後の課題ですが…。

回答No.1

zap35

すみません。質問文だけでは何をどうしたいのかサッパリ分かりません。

具体的に各シートにどのような情報があって、どの項目で何を突合したいのか書いていただかないと回答のしようがありません。

シート１（請求書データ）
Ａ列　　　Ｂ列　　　Ｃ列　　　Ｄ列
請求日　　納品日　　得意先　　品名

シート２（得意先マスタ）
Ａ列　　　Ｂ列　　　Ｃ列　　　Ｄ列
コード　　得意先　　締め日　　支払日　　

また
＞納品日から起算して指定の請求日を設定
も他人が分かるように解説していただけると助かります。おそらく用語の意味は会社によって独特でしょうから…

補足

早速の解答ありがとうございます。
請求書の書式に「納品日」と「請求日」各々のセルがあります。
納品日は手入力し、請求日のセルに得意先ごとの締日に対応した日付にしたいのです。
－－－－－－－－－－－－－－－－－－－－－－－－－－－
　Ａ　　　　　　　Ｂ　　　　　　　　　Ｃ　　　　　　　　　Ｄ
納品日　2006年７月14日　　　請求日　　2006年７月20日
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
（得意先Ａの締日が毎月20日の場合）

得意先ごとの締日はマスタに設定。
－－－－－－－－－－－－－－－－－－－－－－
Ａ　　　　　　　Ｂ　　　　　　Ｃ　　　　　　
コード　得意先名　　締日（20日とか月末とか）
－－－－－－－－－－－－－－－－－－－－－－
こんな感じですが、どうでしょうか？