エクセル関数を使ったシフト表についての質問です。

質問

エクセル関数を使ったシフト表についての質問です。
添付画像のような１ヶ月ごとのシフト表をエクセル２００３で作っているのですが、どうしても判らないことがあり初めてここを利用してみています。
アングルに失敗し縦行の数字が見えていませんが、一番上のセルは３行目です。

この表はセルＬ３の年、セルＮ３の月を変えると自動的に曜日が変わるように式が入っており、公休日欄に休みの曜日を予め設定しておくと年月を変えるだけで対応した曜日に「休」が出るように式を入れてます。
Ｊ１５の式を抜粋します。
=IF(OR(J5=$B15,J5=$C15,J5=$D15,J5=$E15,J15=$F15,J15=$G15),"休","")

そしてここからが問題なのですが、空いているＨの行に正社員のみ○という印をつけて、○がついている正社員の休みのみ第１公休＝Ａ、第２公休＝Ｂというようにアルファベットで表示されるしたいのです。

そこで私は色々サイトを回り複数の条件を式に入れる方法を探したのですが、どういった式がこの問題解決に適しているのかがわからずにいます。

さらに以前から気になっていた問題がもう１つあります。
この画像には見えていないのですが、４１の行のセルにユーザー定義のm"月"d"日"表記で月日が出るセルを用意してます。そしてもし３１日までない月になり４１の行に翌月の日まで表示されるようになった場合、４の行には翌月以降の日は表示されないように式を入れています。
Ｊ４の式を抜粋します。
=IF(MONTH(AM41)>$N$3,"",AM41)

そしてここからが問題なのですが、最初に質問した「休」が自動的に出る式の部分で、２月等の３１日まで無い月になり４１の行に翌月の日が入ったため、４の行に日が非表示になっているセルが出た場合、その非表示になっている縦行全てのセルが「休」表記になってしまいます。
４の行が非表示になった場合は休みも何も表示しないよう式を入れてみたのですがエラーが出てしまいうまくいきません。

どなたかお力添えをお願いできないでしょうか、どうかよろしくお願いいたします。
またこうした方が便利になるアドバイス等もあれば、ぜひいただきたいです。

回答No.7

KURUMITO · ベストアンサー

回答Ｎｏ3です。返事が遅れまして申し訳ありません。
Ｈ列に〇が付く場合には必ず第１公休日はＢ列に第2公休日はＣ列にその曜日があることにしますと次の式をＪ13セルに入力してから右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR($I13="",J$5="",COUNTIF($B13:$G13,J$5)=0),"",IF($H13="○",IF($B13=J$5,"A",IF($C13=J$5,"B","休")),"休"))

お礼

お返事が遅くなり申し訳ありません。
今回頂いた式で私の作りたかった表がほぼ完成し、さらにエクセルについても大変勉強になったので今後にも活かしていきたいと思います。
もし今後またこちらで質問をする機会があれば、またお力添えをお願いできればと思います。
本当にありがとうございます。

回答No.8

nine999

#1です。
もういらないかもしれないけど、私がJ4と書いた部分は、推測なので日付が入っているセルを選んでください。日付がない場合は表示しないということでしたね。

お礼

補足ありがとうございます。
Ｊ４には日付が入っているので、前回頂いた式で正常に機能しています。

回答No.6

tom04

No.4・5です！
何度もごめんなさい。

前回の数式に間違いがありました。

=IF($H15<>"",IF(J$5=$B15,"A",IF(J$5=$C15,"B","")),IF(COUNTIF($B15:$C15,J$5),"休",""))

に訂正してみてください。

検証せずに投稿してごめんなさいね。m(__)m

お礼

お返事が遅くなり申し訳ありません。

こちらの式にも使ったことの無い式があり勉強になります。
他の方々に頂いたものと合わせて表に当てはめてみたいと思います。
色々と検証までしていただき本当にありがとうございます。

回答No.5

tom04

No.4です！
たびたびごめんなさい。
H列の「○」の件をまったく無視していました。
H列は「○」以外は入らないという前提で・・・

もしかして、
J15セルの数式は
=IF(AND($H15<>"",J$5=$B15),"A",IF(AND($H15<>"",J$5=$C15),"B",IF(OR(J$5=$B15,$C15),"休","")))
ということですかね？

以上、お役に立てば良いのですが、
今回も外していたらごめんなさいね。m(__)m

回答No.4

tom04

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

日付と曜日の表示方法はすでにNo.3さんの方から出ていますので、
その形式で曜日が表示されているとしての方法です。
第1公休日は「A」・第2公休日は「B」と表示させたいという希望みたいなので、

J15セルの数式を
=IF(J$5=$B15,"A",IF(J$5=$C15,"B",""))

（曜日の表示方法がNo.3さんの方法と違った場合は正確に表示できないかもしれません。）

として列方向と行方向にオートフィルでコピーしてみてはどうでしょうか？

以上、参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m

回答No.3

KURUMITO

いくつかの点を改良したほうがよいと思われます。
次の参考にしてみてください。
Ｊ4への入力の式ですが次の式を入力して右横方向にオートフィルドラッグします。ＡＮ4セルまで。

=IF(MONTH(DATE($L$3,$N$3,COLUMN(A1)))=$N$3,DATE($L$3,$N$3,COLUMN(A1)),"")

これでＪ4セルから横方向には日付が5月なら５月だけの日付、2月なら2月だけの日付が表示されます。

次にＪ5セルには次の式を入力し右横方向にオートフィルドラッグします。

=IF(J4="","",TEXT(J4,"aaa"))

その月々の日にちに対応した曜日が表示されます。

各人のデータが13行目から下方に入力されるとして、また、各人の休みの取れる曜日がＢれつからＧ列までの行に、また、〇はＨ列に入力され、氏名はＩ列に入力されるとします。

Ｊ13セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR($I13="",J$5=""),"",IF(COUNTIF($B13:$G13,J$5)=0,"",IF($H13="○",CHAR(65+COUNTIF($I13:I13,"?*")-1),"休")))

これでお望み通りの結果が表示されると思います。一度お試しください。

お礼

補足についての補足をどこに書くべきかわからなかったので、お礼欄で失礼します。
すみません、先程の補足で間違いがありました。

第１公休日と曜日が合致している５の行には「Ａ」、第２公休日と曜日が合致してる５の行には「Ｂ」と表示される２パターンのみの式にする場合はどうすればよろしいでしょうか？

この部分は
第１公休日と５の行の曜日が合致した場合「休」でなく「Ａ」、第２公休日と５の行の曜日が合致した場合「休」でなく「Ｂ」と表示される・・・
の誤りです、失礼しました。

補足

お返事ありがとうございます。
=IF(MONTH(DATE($L$3,$N$3,COLUMN(A1)))=$N$3,DATE($L$3,$N$3,COLUMN(A1)),"")
=IF(J4="","",TEXT(J4,"aaa"))
上記の２つの式はまさに私がしたかったことが凝縮されていました、ありがとうございます。
使ったことのない関数もあり、見てるだけで勉強になります。

=IF(OR($I13="",J$5=""),"",IF(COUNTIF($B13:$G13,J$5)=0,"",IF($H13="○",CHAR(65+COUNTIF($I13:I13,"?*")-1),"休")))
上記の式についてですが、ほぼ私の思い描いていた通りの式です。
ただ私の説明が不足していた点がありまして、社員には第１と第２の２日間しか公休日が無いのです。
なので、もし社員ということでもしＨの行に○があった場合、第１公休日と曜日が合致している５の行には「Ａ」、第２公休日と曜日が合致してる５の行には「Ｂ」と表示される２パターンのみの式にする場合はどうすればよろしいでしょうか？

大変お手数かとは存じますが、どうぞよろしくお願いいたします。

回答No.2

keithin

第一段階：J5がB15:G15のどれかなら休みにする
=IF(COUNTIF($B$15:$G$15,J5)=1,"休","")

第二段階：休みを，さらに正社員なら公，そうでないなら休にする
=IF(COUNTIF($B$15:$G$15,J5)=1,IF(H5="○","公","休"),"")
Ｈ列のどこに○を入れてあるのを見ればよいのか，情報提供不足です。

第三段階：公（すなわち○）の１個目２個目３個目をＡＢＣにする？
=IF(COUNTIF($B$15:$G$15,J5)=1,IF(H5="○",MID("ＡＢＣＤＥＦ",COUNTIF($H$5:H5,"○"),1),"休"),"")
しかしそもそも第１とか第２とは一体何のことで，エクセルのどこを見たら良いのか全く情報不足です。

第四段階：その日の日付表示が無い時は，まったくの最初から計算しない
=IF(J4="","",IF(COUNTIF($B$15:$G$15,J5)=1,IF(H5="○",MID("ＡＢＣＤＥＦ",COUNTIF($H$5:H5,"○"),1),"休"),""))

補足

お返事ありがとうございます。
説明が不足しており大変失礼いたしました、申し訳ございません。

Ｂ～Ｇ列の欄で、氏名欄の列に対応した人の第１～第６の公休日の曜日を設定しています。
Ｈの列についてですが、氏名欄の左側のＨ列のセルに、社員でなければ未記入、社員であるなら○と書くようにしようと思っています。
そして例えばＡさんが社員でなく、左側のセル(H14)が未記入な場合は先程お答えいただいた=IF(COUNTIF($B$15:$G$15,J5)=1,"休","")を適用しようと思います。
そしてもし社員であり上記のセルに○があった場合は、社員の場合は公休日が２日のみなので、質問欄に書きました第１＝第１公休日を「休」でなく「Ａ」、第２＝第２公休日を「休」でなく「Ｂ」と表示する式を入力したいのです。

私がぶつかっている点が、この○表示があるかないかの条件付けと、もしないなら「休」表示の式を適用、もし○があるなら第１公休はＡ、第２公休はＢという表示を適用するという条件を１つの式にまとめる方法です。

もしまだ説明が不足している個所がございましたら、大変お手数かと存じますがお返事をいただければと思います。どうぞよろしくお願いいたします。

回答No.1

nine999

とりあえず、31日未満の月に休を表示させない方法について。

=IF(OR(J5=$B15,J5=$C15,J5=$D15,J5=$E15,J15=$F15,J15=$G15),"休","")
これが休を表示している式でしたよね。ここを直せば良いということです。
=IF(AND(OR(J5=$B15,J5=$C15,J5=$D15,J5=$E15,J15=$F15,J15=$G15),J4<>""),"休","")
J4が空白でない場合は、休を表示する条件を最初のORで結んだ条件とANDでまとめてあります。

お礼

早速のお返事ありがとうございます。
早速入力してみたところ「循環参照」という初めて見るメッセージが出てしまったので、これについては調べつつ入力していこうと思います。
もし他に何かお気づきの点がございましたら、またよろしくお願いいたします。