ＥＸＣＥＬで勤務表作りたいのですが・・・ど素人です。

質問

セル1マスを1時間で0-24までの勤務表を作りたいのです。
勤務表のファイル見つけて改ざんして24時間まで作成しました。
以下の関数がセル1マス1マス入ってます。
=IF(AND(AX$9>=HOUR($C10),AX$9<HOUR($D10)),1,"")
A　 B 　　　C 　　　　D 　　　E 　F 　G　 H　 I.....　　AC
1　 名前　開始時間　終了時間　0時 1時 2時 3時 4時 5時....24時
2　　あ　　18：00　　23：00
3　　い　　17：00　　24：00

という形で時間を入れると開始-終了時間のセルに1の値が表示され
同時にセルに色が付くのですがどうしても24：00を選択するとすべての
セルで1の値が出なくなり色も付きません。
AC1の式は=IF(AND(AC$6>=HOUR($C7),AC$6<HOUR($D7)),1,"")です。
解決策とこの式の意味合いを詳しく教えては頂けないでしょうか？

回答No.4

tom04

No.2です！
たびたびごめんなさい。
前回の回答では確かに余計なセルまで「1」が入ってしまいましたね。

数式を訂正してください。
尚、希望としては「1」の変わりにその行の名前が表示できれば良いと言うことですかね？

一応そういうことだとして・・・

まず、E2セルの数式を
=IF(COUNTBLANK($C2:$D2)>0,"",IF(AND($C2<=E$1,$D2>E$1),$B2,""))
に訂正してください。

そして、メニュー　→　書式　→　条件付書式　から
「数式が」を選択　→　数式欄に
=E2<>""　として、好みの色を選択してください。

E2セルを列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

以上、お役に立てれば幸いです。m(__)m

回答No.3

nattocurry

24:00 と入力したセルを選択して、数式バーを見ると解ると思いますが、表示は「24:00」でも、内部では「翌日の0:00」と認識しています。
そして、HOUR関数は、「時」だけを取り出す関数なので、当日の0時でも翌日の0時でも、同じ結果になります。
つまり、この数式では、24:00と入力すると、0:00と入力したのと同じ結果になってしまいます。

質問文の例を見る限りでは、分単位での入力は無い（すべて時単位）ので、そうなのであれば、入力の形式を時間形式（8:00とか）ではなく、数値形式（8）で入力するようにしてはいかがでしょうか？
分単位で入力するにしても、15分単位での入力であれば、8:15、8:30、8:45、はそれぞれ、8.25、8.5、8.75、と入力することで対応できます。
10分単位、5分単位、1分単位であれば、正確な対応は無理ですが。

そうすれば、HOUR関数とかを使う必要も無くなり、質問者様も自分が解る範囲での計算式を書けると思います。

回答No.2

tom04

こんばんは！
質問の回答とまではいかないと思いますが・・・

質問の中の数式を拝見すると、AX$9　のように質問だけではそのセルに何が入っているのか理解できないセルがありますので、
余計なお世話だと思いますが、勝手にこちらで↓の画像のように最初から表を作成してみました。

当方使用のExcel2003の場合です。
E1～AC1セルは0～24の数値だけ入力し、セルの表示形式で書式設定から
ユーザー定義で　[h]　としています。

そして、C・D列の時刻を入力するセルの書式設定はユーザー定義から
[h]:mm　としています。

E2セルの数式は
=IF(COUNTBLANK($C2:$D2)>0,"",IF(AND($C2<=E$1,$D2>=E$1),1,""))

とし、条件付書式で、セル値が　→　次の値に等しい　とし　1　だけ入力して塗りつぶしの色を「赤」に設定し、OK

これで列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

以上、長々と書いた上に、余計なお世話だと思いますが、
参考になれば幸いです。
尚、的外れなら読み流してくださいね。m(__)m

お礼

早々ご回答ありがとうございます。
朝会社で早々やってみました。出来ました！！有難うございます。
つきましてはもう一つお尋ねしたいのですが、色で無理つぶされたセルには背景に1という数字の文字が隠れますが、Ｂ列の名前あ、い、う（佐藤、伊藤、安藤）と塗りつぶされたセルに自動に表示させる方法は有るのでしょうか？

補足

ん！？問題発生です。24時迄色塗出来ましたが、上記画面にもあるのですが３列のＶ～ＡＣ17：00～24：00なのに１時間多く成ってしまいます。（実際７時間が24～25時の１時間多く）

回答No.1

anmochi

まず、Excelではセルを「列のアルファベット」＋「行の番号」という形式で指定し、列のアルファベットあるいは行の番号の前に「$」がつくとそれは絶対位置（つかないと相対位置）という意味になります。なので$C10はC列は絶対位置、10行は相対位置という意味です。絶対位置と相対位置は今回の話にはあまり関係ないのでそんなもんだと思っていてください。
　次にHOURというのは、$C10というセルに時間（年月日時分秒）が入っていると仮定してその「時」を取得する関数です(正確にはちょっと違いますがこんなもんだと思ってください)。つまり、HOUR("24:00:00")は「0」になります。次の日の0時という意味ですね。なので、3行目の場合
=IF(AND(AC$1>=HOUR($C3),AX$9<HOUR($D3)),1,"")
は「AC$1(恐らく「24」が入っている)が17(時)より大きく0(時)よりも小さい場合に1を、そうでなければ""をこのセルの値とする」という意味になり、24は0(時)よりも小さくないのでFALSEとなり、1にはならなくなります。
　解決するには時に加えて日も見なくてはいけないため、もう少し複雑な式にしなくてはいけません。残念ながら今手元にExcelが無いので正確な式を作る事はできませんが、C列(開始時間)とD列(終了時間)をそれぞれ開始時、開始分、終了時、終了分の2列ずつに分け、HOURではなく開始時の数値と終了時の数値で単純に比較するという形にした方が式が簡単になるため良いのではないでしょうか。質問に答えてなくて申し訳ありません。もしくはYEAR関数、MONTH関数、DAY関数の使い方を調べてみてください。

お礼

早々のご回答有難う御座いました。
今回上記nattocurryさんのご回答で解決しましたが、anmochiさんのおっしゃる年、月、日の関数もっと勉強すると2010/1/1/金22：00-2010/1/2土03：00迄の表も自動に塗ってくれるシフト表作れそうですね～！！
ガンバってみます。
nattocurryにも同じ質問したのですが、塗りつぶされたセルにＢ列2,3で選んだ従業員の名前を自動的に表示する又は色分けする良いアイディアあればご指導願います。