エクセル２０００で出勤簿の表示形式について

質問

長文ですがよろしくお願いします。20日〆のカレンダー形式の出勤簿を作成しています。例えば8月分だとＡ１セルに７／２１（木）、Ｂ１に２２（金）・・Ｇ１に２７（水）、Ａ３に２８（木）・・と７日間を同一行の７列に入力します。この中で２１日と月初めの１日の表示は月／日（曜日）としたいのです。今はユーザー定義で２１日のセルにｍ”／”ｄ”（”ａａａ”）”の表示形式を、２２日にはｄ”（”ａａａ”）”を入れ更新する度に２３日以降にドラッグして後で１日のセルだけ２１日の書式のみをコピーしています。１人分だけならこれでもいいのですが、ページ１として元データを作って約１００名分のページを＄Ａ＄１などの数式で自動反映していて、３０日の月や３１日の月があるため１日の位置がずれ、自動反映のページには７／３１（日）、１(月）と表示されてしまい、今は結局１つ１つコピーし直していて自動反映の意味がありません。何か良い方法があれば教えていただけたら幸いです。よろしくお願いします。Ｗｉｎｄｏｗｓ９８使用です。

回答No.6

nobuendo · ベストアンサー

ＡＮｏ．４まで完成したものを、変更するかたちで説明します。

Ｂ３は、=DATE($A$1,$C$1,1)　に変更。
Ｃ５からＧ５は、Ｃ３からＧ３をコピーする。
Ｃ５は、=B5+1　Ｄ５は、=C5+1　Ｅ５は、=D5+1　Ｆ５は、=E5+1 Ｇ５は、=F5+1 と式が入ります。
Ｂ１１を、=IF(DAY(H9+1)=29,H9+1,"")　に変更。
Ｃ１１を、=IF(B11="","",IF(DAY(B11+1)=30,B11+1,""))　に変更。
Ｄ１１を、=IF(C11="","",IF(DAY(C11+1)=31,C11+1,""))　に変更。

これでどうでしょう。

お礼

凄いです、バッチリです。関数を使いこなせたらこんなに便利なんですね～。自分で作れるようになったらいいのですが・・とりあえずこれを機会に色々試してみます。何度もお時間割いてくださって有り難うございました！またよろしくお願いいたします。

回答No.5

DoragonFang

ANo.1です。
回答が遅くなって済みません。
すでに既出の回答で解決しているようなので、蛇足ですが、
ANo.1の回答で、＃ｖａｌｕｅ！エラーになるとのこと。
A1セルの入力内容はどうなっていますでしょう？
文字列で入力していませんか？
例えば、７月２１日とか全角になっているとか。
一度A1セルの内容をクリアして、書式を「標準」か、「日付」にして、「2005/7/21」と入力していただければ、エラーにならないと思いますが。
私の説明不足で、すみません。

お礼

ご丁寧にお返事を有り難うございます。実際はＡ１入力ではないので式を変えないといけなかったのですが、それを間違っていたようです。ただ、出勤簿の形が７日毎に下段に作らなければならないので、この方法だと週ごとに式をやりかえる手間から考えてＮＯ.３の内容でやってみようと思います。Ｎ０．１の内容も活かされてとても助かりました。有り難うございます。またよろしくお願いします。

回答No.4

nobuendo

レイアウト的に変かも知れませんが、解りやすく説明する為、ANo.3をベースに説明します。

Ａ１に、=A2+1988　と式を入力します。
Ａ２を、和暦の年の入力欄とします。（今年なら　17）
Ａ３には、="平成"&A2&"年"&C1&"月分"　と式を入力します。
Ｂ３は、貴方の言われるように　=DATE($A$1,$C$1-1,21)に変更。
そして、Ａ３からＨ１１（日付のすぐ下が入力セルならＨ１２まで）に印刷範囲指定をしておきます。
Ａ１、Ａ３には、セル保護をかけます。
Ａ１は、非表示でもかまいません。

尚、年の入力を西暦にするなら、Ａ１が入力セルとなり、
Ａ２には、=A1-1988　の式を入力することになります。

こんなことでよろしいでしょうか。

余計なお世話かも知れませんが、この表を不特定多数の方が使用（入力から印刷まで）するのか、管理者に限定されるのかによって、間違いが起こらないように仕組むレベルが変わってきます。
不特定多数の方が使用するなら、入力できるセルを色分けし、変更不可のセルには保護を掛け、全シートに印刷範囲を正しく設定し、且つ正しく印刷されるように用紙設定等の印刷設定をしておくなど、細かな配慮が必要となってきます。

お礼

何度も有り難うございます。今必死に作成中です。取扱うのは原則私だけなので大丈夫かと思いますが、何かの時に混乱しないように明確にしておくようにします。
もう一つだけ我侭言わせていただけるなら、2名分だけ末締めの出勤簿がありまして、最後の29日～31日を他の20〆のように2、4、6、9、11月毎に自動で入力されるようにしたいのですが、数式はどうなるのでしょうか？20〆のに手を加えて見たりしていますが、翌月の1日までが表示されてしまったりで悪戦苦闘しています。すみません、よろしくお願いします。

回答No.3

nobuendo

ＡＮｏ．２の変更案をお知らせします。（関数式の入力が少し簡単になります。）
ただし、変更しなくとも支障はありませんが。

ＡＮｏ．１の方のTEXT関数を、Ｃ５からＦ５に応用します。
Ｃ５を =IF(DAY(B5+1)=29,B5+1,TEXT(B5+1,"m/d(aaa)")) に変更します。
Ｄ５を =IF(OR(DAY(B5+2)=30,DAY(B5+2)=2),B5+2,TEXT(B5+2,"m/d(aaa)")) に変更します。
Ｅ５を =IF(OR(DAY(B5+3)=31,OR(DAY(B5+3)=2,DAY(B5+3)=3)),B5+3,TEXT(B5+3,"m/d(aaa)")) に変更します。
Ｆ５を =IF(DAY(B5+4)<>1,B5+4,TEXT(B5+4,"m/d(aaa)")) に変更します。

上記以外は、ＡＮｏ．２の通りです。
エクセルを立ち上げ、このページから、それぞれの関数式を該当セルにコピーすれば、簡単且つ間違えなく作成できますので試してみて下さい。

お礼

凄いです、感動です。こういうのを求めてました。ただもう一つ甘えさせていただけるなら、うちの年月表示が、例で言うとＨ２のセル内に「平成17年8月分」となってまして、7/21～8/20であれば「8月分」なんです。これは＄Ｃ＄－１とすれば解決しそうですが、せっかくならＡ１の２００５を平成１７年表示にして、この関数を生かす方法はありますか？ＤＡＴＥＶＡＬＵＥなど試したけどよく解らなくてすみません。もし教えていただけたら幸いです。なければＡ１の行を非表示で印刷して使おうと思います。

回答No.2

nobuendo

具体例を記述します。
Ａ1に年（今年なら2005）、Ｃ１に月を入力するセルとします。
Ｂ３を２１日として横に一週間、次の週は、一行あけてＢ５が２８日、次の週も一行ずつあけて表記する例とします。
Ｂ３の表示形式は、当然　ｍ”／”ｄ”（”ａａａ”）”です。しかし、それ以外の日付の入るセル（ここではＤ11まで）は、すべて　ｄ”（”ａａａ”）”に設定します。

次に、各セルに入力する関数式を表記します。
Ｂ３には、=DATE($A$1,$C$1,21)
Ｃ３には、=B3+1　Ｄ３には、=C3+1　と言うように、この行は規則正しく入力します。
次のＢ５からは、やや複雑ですが、一字一句間違えてはいけません。
Ｂ５は、=H3+1　
Ｃ５は、=IF(DAY(B5+1)=29,B5+1,CONCATENATE($C$1+1,"/",1,CHOOSE(WEEKDAY(B5+1),"(日)","(月)","(火)","(水)","(木)","(金)","(土)")))
Ｄ５は、=IF(OR(DAY(B5+2)=30,DAY(B5+2)=2),B5+2,CONCATENATE($C$1+1,"/",1,CHOOSE(WEEKDAY(B5+2),"(日)","(月)","(火)","(水)","(木)","(金)","(土)")))
Ｅ５は、=IF(OR(DAY(B5+3)=31,OR(DAY(B5+3)=2,DAY(B5+3)=3)),B5+3,CONCATENATE($C$1+1,"/",1,CHOOSE(WEEKDAY(B5+3),"(日)","(月)","(火)","(水)","(木)","(金)","(土)")))
Ｆ５は、=IF(DAY(B5+4)=1,CONCATENATE(IF($C$1=12,1,$C$1+1),"/",1,CHOOSE(WEEKDAY(B5+4),"(日)","(月)","(火)","(水)","(木)","(金)","(土)")),B5+4)
Ｇ５は、=B5+5
Ｈ５は、=G5+1
次のＢ７、Ｂ９の列は規則正しく入力します。
Ｂ７は、=H5+1　Ｃ７は、=B7+1　Ｄ７は、=C7+1　・・・という具合です。
Ｂ９列は、Ｂ７列をそっくりコピーしてください。
最後にＢ１１列目です。
Ｂ１１は、=IF(DAY(H9)<20,H9+1,"")
Ｃ１１は、=IF(B11="","",IF(DAY(B11)>=20,"",B11+1))
Ｄ１１は、=IF(C11="","",IF(DAY(C11)>=20,"",C11+1))
関数式入力はここまでです。

Ｂ３からＤ１１までのデータは、そっくりそのまま、どこへコピー、移動しても相対関係は崩れませんので、大丈夫ですが、Ａ１とＣ１の年、月の入力セルを違うセルに変更する場合は、リンクする関数式のあるセル（Ｂ３，Ｃ５，Ｄ５，Ｅ５，Ｆ５）の該当部分を変更してください。

年、月を入力する、Ａ１、Ｃ１以外、まったく変更することなく自動反映されます。もちろん、うるう年も大丈夫。
Ｂ３～Ｄ１１の各セルに保護を掛けておけば、安心です。

回答No.1

DoragonFang

題意通りではないかもしれませんが、
Ａ１セルにその週の最初の日付を入力します。

で、Ｂ１セルに次の式を入力し、Ｇ２セルまで
ヨコにコピーします。

=IF((DAY($A$1+COLUMN(A1)-1)=21)+(DAY($A$1+COLUMN(A1)-1)=1),TEXT($A$1+COLUMN(A1)-1,"m/d(aaa)"),TEXT($A$1+COLUMN(A1)-1,"dd(aaa)"))

式が複雑で済みません。
表示にはTEXT関数を使っています。該当する日が１か２１の場合にのみ"m/d(aaa)"を使い、それ以外の日は
"d(aaa)"の書式にしてあります。

COLUMN(A1)-1の部分はA1セルに入れた日付に対して、ヨコに＋１ずつするために使っています。
必ず　A1セルに週初めの日付を入れなければなりませんが、題意の自動書式は可能だと思いますので、
参考になりましたら。

お礼

早々の回答ありがとうございます。関数とかよく解らないのですが、試しに早速やってみました。でも＃ｖａｌｕｅ！の表示が出ます。となりにコピーしてみても同じでした。もしや最後のはｄｄ（ａａａ）ではなくｄ（ａａａ）では？と変えてみたけど、やはり駄目でした。＝のボタンを開いてみると、論理式～偽の場合、全て＃ｖａｌｕｅ！とありました。何度見直しても入力漏れは無いようなのですが、すみません・・何が違うのか解りません。