Excel 行と日付自動入力する方法

質問

Excelで金融機関の支払表を作っています。
目標イメージ(空白が認識されずずれるので勘弁してください）
A　　B　　C　　D　　E　　F　　G　
1　　年　 月　日　曜 支払　　他略
2　　2008 3　 1 土　　水道　　 -
3　　2008 3 　2 日　　電気　　 -
4　　2008 3 3　 月　　 - -
5　　2008 3 4 火　　 ガス　　 -
・・・
32 オートフィルタ合計の管理(年の水道料金別などを出すための行）

といった感じですなのですが、エクセル初心者なので現在次の日がほしいときは6行目挿入、オートフィルコピー、月が替わると手動で4 1（4月1日)と入力して同じようなことをしています。挿入と手動入力をするのに手間がかかりますので、2008 3 1を入力すると3月の日付が自動挿入(行が挿入)、自動入力(2～31まで)などされて効率よくしたいので方法、マクロ、コードなんでもいいので教えてください。オートフィルタ合計行を作り年、月、日を分けていますので手間取っています。

できれば今後ずっと使っていく予定ですので、4月は30日までとか閏年とかも認識させたり、土日は自動色変更されるようにしたいです。
説明下手ですがお願いします。

回答No.1

hallo-2007 · ベストアンサー

日付は、Windowsのカレンダーを利用して、最初から日付形式で入力すると、閏年とか曜日とか全て反映してくれます。
A　　 B　　C　　D　　E　　F　　G　
1　　年月日　曜 支払　　他略
2　　2008/3/1 土　　水道　　 -
3　　2008/3/2 日　　電気　　 -
になります。
B2に2008/3/1 と入力してEnter押すだけです。
日付の表示の仕方を変更したければ、「書式」「セル」「表示の形式」
日付の一覧から変更してください。
曜日　C列には（C2に)、=IF(B2="","",B2) と入れて下フィル、「表示の形式」「ユーザー設定」でaaaと入れて見てください。
日付の表示が曜日の表示になります。
A列には、A2に　=IF(B2="","",ROW()) で行番号が表示されます。

お礼

ご返事ありがとうございます。
その方法なら簡単にできそうですが、質問にかいていますよう
オートフィルタをつけて、「4月」を選択すると4月分がぱっとみれるようにしたり、「ガス」を選択すると今までのガス代の推移がみれたりしたいのです。
後行番号が表示したいわけではなく、行が挿入したいのですがこれは無理なのでしょうか。

回答No.4

hallo-2007

No1です。
>後行番号が表示したいわけではなく、行が挿入したいのですがこれは無理なのでしょうか。
この部分が抜けていましたね。
ご希望が月が替わると、自動的に、その月のそれぞれの合計を表示する
行が挿入されていく
というのであればVBAの機能でも使わないと無理です。
更に、データシートの途中、途中に合計値など入ると別の集計時に厄介になって行きます。
データシートの上に、3行ほど挿入しておいて、希望の月、項目いれて、合計値が表示されるように数式入れる
或いは、別シートに
縦に　年月　いれて下フィル
横に　項目入れているシート準備して、
自動的に、合計値が入る式でうめておく。
そういった集計の表示のほうが良いと思います。

お礼

ありがとうございます。
ＶＢＡというものを使えばできるのですか。
おっしゃるとおり上にオートフィルタ行をつくりました。
とりあえず2009年3月までオートフィルコピーしましたが、オートフィルタの月で4月に設定しているので管理しやすいです。

最後に祝日の色を自動で変更させる式などあれば教えてください。
毎月自分で色を変更していくのは面倒です。

回答No.3

hallo-2007

No1です。
一般的には、作業列使用します。
多分、月だけではなく年と月が必要と思いますが、
H列にでも
=TEXT((B2,"yy/mm")　入れて下フィルしておく
或いは
D列に表示形式で、ユーザー設定で　dd に設定して
2008/3/1 と入力しても1と年日しか表示しない設定にして
B2　　=YAER(D2)
C2　　=MONTH(D2)　で下フィルしておく

土日の色設定ですが、条件付書式で設定します。
仮にB列にきちんとしたシリアル値で日付があいっていて、A~G列で日曜日の場合、色をつけるには。A~G列選択して、条件付書式
右側を　数式が　　に変えて
=WEEKDAY(B2)=0 入れて、書式でパターンにて好きな色を選択です。

お礼

たびたびありがとうございます。
わかりやすい説明で理解できました。
・祝日の色を認識させてつけることはできますか？
とりあえず土日はオレンジ色に変更できました。（１年分）

回答No.2

maac777

差し支えなければ作業列を作って（ここでは仮にH列で表記します）
H1に前期末の日付（期首が1月1日なら　2007/12/31）を入力
B2=YEAR(H2)
C2=MONTH(H2)
D2=DAY(H2)
E2==CHOOSE(WEEKDAY(H2),"日","月","火","水","木","金","土")
H2=H1+1
E2セル条件付書式設定（土曜・日曜の場合はそれぞれ色を変える）
B2からH2まで選択状態にしておいてオートフィルで366日分コピー
で1年分先に作った方が簡単かと思われます。
うるう年以外は最後の行を削除してください。またH列以外のセルの書式設定は標準で。

お礼

ありがとうございます。
366日分コピーしたらその方法で１年ぶんだせますね！
ですがオートフィルタ行は一番上にもってきました。