ここから本文です

エクセル関数に関する質問です。エクセル2007で支出管理表なるものを作っています...

exc********さん

2010/6/421:49:56

エクセル関数に関する質問です。エクセル2007で支出管理表なるものを作っています。次回の銀行(郵便局)引落日を自動入力したいんです。どういう風な関数にすればいいのでしょうか?

こんにちは。初めて知恵袋を利用させていただきます。エクセル2007で支出管理表なるものを作成して愛用してから1年以上になるのですが、先日不具合を見つけました。A列は空白です。B列からE列に適用・金額・支払い方法・支払い日(引落日までの日数)などが入っています。画像を添付しました。画像はJ~L列です。簡単に説明しますと 別途記載=別表にて今期の支払などが表にあり自動で表示されます。26日=毎月26日です。"-"=未記入です。月末=毎月月末です。今回の質問ですがK列の"#VALUE!"ってなっているところです。ここは①偶数月の引落がある②銀行が土日祝祭日にあたり引落ができない日は翌営業日の日付を自動表示したい③例)偶数月26日引き落としの場合、今日が2010年6月1~25日にあたる場合は26日が土曜日にあたるので6月28日を自動表示させたい。今日が2010年6月26日以降(26日も含める)は(2010年6月26日~2010年8月25日まで)8月26日を自動表示させたいのです。一応今まで独学ですべて勉強してきました(今も勉強ちゅうです)。んで一応こんな式を作ってみました。
=IF(DAY(TODAY())>1,WORKDAY(IF(MOD(MONTH(EOMONTH(TODAY(),0)),2)=1,EDATE(DATE(YEAR(EOMONTHTODAY(),0)),MONTH(EOMONTH(TODAY(),0)),2),1),EDATE(DATE(YEAR(EOMONTH(TODAY(),0)),MONTH(EOMONTH(TODAY(),0)),2),2)),0,祝日),WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>1),1)-1,祝日))
もうね、自分でも何が何だかわからなくなりました。直観だけで作りました。祝日は別シートに表を作成して"祝日"と定義して参照させています。L列はワークセルで今月か否かを判定しています。どこをどう直してどこからどこまではどんな意味なのか詳しく教えていただけると今後、同じような場面に遭遇した時応用が利いてたすかります。他にもっと簡単な方法があれば教えて頂きたいです。関数が長くなって来るといつも途中で訳わかんなくなちゃうんですよね。

補足maron5ooさん、早速のご回答ありがとうございます。一応J列が偶数月○日になっているセルのみ関数を使わせていただきました。というのも月末となっているセルと空白("-")セルと別途記載でエラーがでてしまうんです。補足としてここには出てないのですがE列に 『銀行引落』、『コンビニ』、『郵便引落』、『振込み』、空白(各店舗)となっていて問題は銀行(郵便局)引落の場合だけなんです。13日頃は13日で構いません。

EOMONTH,WORKDAY,引落日,EOMONTHTODAY,エクセル,偶数月,J3

閲覧数:
1,084
回答数:
1
お礼:
25枚

違反報告

ベストアンサーに選ばれた回答

mar********さん

編集あり2010/6/505:54:09

【補足による追記】
>偶数月○日になっているセルのみ関数を使わせていただきました
◆実際に「偶数月○日」と入力されているセルにだけ使用するのであれば、
K4=IF(RIGHT(E4,2)="引落",WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>MID(J4,4,(LEN(J4)>5)+1)*1)+(MOD(MONTH(TODAY())+(DAY(TODAY())>MID(J4,4,(LEN(J4)>5)+1)*1),2)=1),MID(J4,4,(LEN(J4)>5)+1))-1,1,祝日),"")

★前回回答の式は、すべてに対応していますが、「偶数月○日」だけの対応ならば、すこし、式が短くなります

◆「偶数月2日」限定なら
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>2)+(MOD(MONTH(TODAY())+(DAY(TODAY())>2),2)=1),2)-1,1,祝日)
または、
=IF(RIGHT(E4,2)="引落",WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>2)+(MOD(MONTH(TODAY())+(DAY(TODAY())>2),2)=1),2)-1,1,祝日),"")

◆「偶数月26日」限定なら
=IF(RIGHT(E9,2)="引落",WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>26)+(MOD(MONTH(TODAY())+(DAY(TODAY())>26),2)=1),26)-1,1,祝日),"")

*******************************************************

◆「引落日支払日」のJ列は、「26日」は「26」、「偶数月2日」は「偶数月2」、「13頃日」は「13頃」と入力します
◆表示形式は、「Ctrl+1(ぬ)」または、マウスを右クリックして「ショートカットメニュー」の「セルの書式設定」を選択して
◆「表示形式」→「ユーザー定義」にして「種類」に、 0"日";@"日" と入力します
◆「13日頃」が「13頃日」になりますが、ごめんなさい
◆なお、「13頃日」は土日祝日のかかわらず、次の「13日」としました

K3=IF(COUNTIF(J3,"*頃*"),DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>SUBSTITUTE(J3,"頃",)*1),SUBSTITUTE(J3,"頃",)),IF(J3="月末",WORKDAY(EOMONTH(TODAY(),0)-1,1,祝日),WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>SUBSTITUTE(J3,"偶数月",)*1)+(MOD(MONTH(TODAY())+(LEFT(J3)="偶")*(DAY(TODAY())>SUBSTITUTE(J3,"偶数月",)*1),2)=1),SUBSTITUTE(J3,"偶数月",))-1,1,祝日)))
★下にコピー

◆ただし、「13日頃」が「7月13日」になりません

【補足による追記】
>偶数月○日になっているセルのみ関数を使わせていただきました...

質問した人からのコメント

2010/6/9 16:17:50

成功 しばらく他の方の回答を待っていましたがこれ以上の回答が得られない為、一応これで回答の受付を終了することにしました。maron5ooさん、補足では、月末とその他でエラーがでると間違えて投稿してしまいましたが、月末は大丈夫でした。今回は一つの式で全てに対応するのではなくセルによって式を変えていくことで解決といたしました。今回は大変勉強になりました。

この質問につけられたタグ

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる