[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日と時間の計算について』（ぽんた）
[20161228070810]『有給休暇時間の算出』関連の質問です。
再度、きちんと整理しての質問です。よろしくお願いします。
以下のような表があります。
GとHは、A〜Fの計算結果です。
例えば、
24行目 1月21日に0.5日(8:30〜13:15)の有給を取得しています。
25行目 1月23日に1日の有給を取得しています。
26行目 1月24日から1月25日の2日の有給を取得しています。
27行目 1月27日に2時間(9:30〜11:30)の有給を取得しています。
という具合に、GとHに計算結果を出したいのです。
その場合、日の単位と4時間は0.5として、G列に結果を出したいのです。
それ以外の時間は、H列に結果を出したいのです。
どうぞよろしくお願いします。
A B C D E F G H 日付 日付 時間 時間 日 時間 24 1/21 〜 8:30 〜 13:15 0.5 25 1/23 〜 1/23 〜 1 26 1/24 〜 1/25 〜 2 27 1/27 〜 9:30 〜 11:30 2
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
始期と終期の中間に休み(土日祝)があったらどうするんですか？
どこかに祝日リストが作られているんですか？ それとも、小刻みに入力して土日祝は跨らない様にするんですか？
（半平太） 2017/01/20(金) 22:53
(1) G24セル =IF(COUNT(A24:C24)=2,C24-A24+1,IF(AND(COUNT(D24:F24)=2,TEXT(MAX(0,MIN("12:15",F24*1)-D24)+MAX(0,F24-MAX("13:00",D24)),"h:mm")="4:00"),0.5,""))
(2) H24セル =IF(OR(ISNUMBER(G24),COUNT(D24:F24)<2),"",TEXT((MAX(0,MIN("12:15",F24*1)-D24)+MAX(0,F24-MAX("13:00",D24)))*24,"0.0000")*1)
下にコピー
<結果図> 行 ___A___ _B_ ___C___ ____D____ _E_ ____F____ __G__ __H__ 23 開始日 終了日 開始時刻 終了時刻 日数 時間 24 1月21日 〜 8:30 〜 13:15 0.5 25 1月23日 〜 1月23日 〜 1 26 1月24日 〜 1月25日 〜 2 27 1月27日 〜 9:30 〜 11:30 2 28 1月20日 〜 8:30 〜 14:15 5 29 1月20日 〜 8:30 〜 11:30 3 30
（半平太） 2017/01/20(金) 23:20
レイアウトの説明に一貫性がないような気が・・・以下、推測で、
<想定レイアウト> 行 ___K___ L ___M___ _N_ ____O____ _P_ _S_ ____T____ _U_ _Z_ _AA_ _AB_ 23 開始日 終了日 開始時刻 終了時刻 日数 時間 24 1/21 〜 8:30 16:15 7 25 1/23 〜 1/23 1 26 1/24 〜 1/25 2 27 1/27 〜 9:30 11:30 2
１．終了日(M列）の入力規則
開始時刻または終了時刻が入力されていたら入力不可
M24:M100を選択して、入力規則を設定する。 【入力の種類】→ユーザー設定、 □空白を無視する のチェックを外す 【数式】ボックスに → =COUNT(O24,T24)=0
２．開始時刻(O列）または終了時刻(T列）の入力規則
(a)終了日が入力されていたら、入力不可 (b)時間(AB列）は空白か、1〜7の整数でなかったら、入力不可
O24:O100、T24:T100を選択して、入力規則を設定する。
入力の種類→ユーザー設定、
□空白を無視する のチェックを外す
数式ボックスに → =AND($M24="",OR($AB24="",ISNUMBER(MATCH((0&$AB24)*1,ROW(INDIRECT("A1:A7")),0))))
（半平太） 2017/01/21(土) 16:52
半平太さん、もう一つお尋ねしたいことがあります。
これは有給休暇簿なのですが、これまでの様式はK列からAB列まで全て手入力のもので、人によっては合計の入力ミスなど多々あって、結局は最初から管理職が確認するという二度手間なものとなっていました。
今回、皆さんのおかげで、使い勝手の良い素晴らしいものができました。
ただ、お昼休みを挟んだ場合の終了時刻をよく理解していない人がいまして、、、もう一つ別の入力パターンとして、AA列とAB列を直接入力することで、T列とM列を自動化するというものを作成してみたいのですが、今一度ご教示頂ければ有難いです。それに伴って、他の入力規則等の修正も必要になる場合もあるかと思います。
どうぞよろしくお願いいたします。
（ぽんた） 2017/01/21(土) 19:22
(1) M24セル =IF(OR(AA24=0.5,AA24=""),"",K24+AA24-1)
(2) T24セル =IF(OR(O24="",COUNTBLANK(AA24:AB24)=2),"",IF(O24<"12:15"*1,O24+IF(AA24=0.5,"4:00",IF(ISNUMBER(AA24),"",AB24/24))+(O24+IF(AA24=0.5,"4:00",IF(ISNUMBER(AA24),"",AB24/24))>"12:15:01"*1)*"0:45",MAX("13:00",O24)+IF(AA24=0.5,"4:00"*1,IF(ISNUMBER(AA24),"",AB24/24))))
下にコピー
<結果図> 行 ___K___ L ___M___ _N_ ____O____ _P _S_ ____T____ _U_ _____AA_____ _____AB_____ 23 開始日 終了日 開始時刻 終了時刻 日数(手入力) 時間(手入力) 24 1/21 〜 8:30 13:15 0.5 25 1/23 〜 8:30 16:15 7 26 1/24 〜 1/24 1 27 1/27 〜 1/28 2 28 1/20 〜 9:30 11:30 2 29 1/20 〜 8:30 14:15 5 30 1/23 8:30 11:30 3 31 1/21 8:30
入力規則の設定 (※以前の入力規則はクリアする）
１．AA24:AA100 =AND(K24<>"",AB24="",OR(AND(AA24=0.5,O24<>""),MOD(AA24,1)=0)) □空白は無視する のチェックは外す
２．AB24:AB100
=AND(K24<>"",AA24="",AB24<>4,O24<>"",ISNUMBER(MATCH(AB24,ROW(INDIRECT("A1:A7")),0)))
□空白は無視する のチェックは外す
（半平太） 2017/01/22(日) 00:04
＞何か原因があるでしょうか？
多分、エクセルのバグだと思います。 今まで、聞いたことがないトラブルですね。
同じ規則を再度設定すると普通にワークするので、対症療法として、 下のマクロを「ThisworkBookのモジュール」に貼り付けてはどうでしょうか？
Private Sub Workbook_Open()
Range("ZZ1000").Validation.Delete
End Sub
一旦保存して閉じ、再オープンすれば正常化します。
※マクロに意味はありません。エクセルに活を入れることだけが目的です。
※マクロが含まれることになるので、ファイルの拡張子は「.xlsm」の形式にする必要があります。
（半平太） 2017/01/23(月) 19:02
8:30〜16:15
9:00〜16:45
（ぽんた） 2017/01/24(火) 00:50
=AND($M24="",OR($AB24="",ISNUMBER(MATCH((0&$AB24)*1,ROW(INDIRECT("A1:A7")),0))))
どうぞよろしくお願いします。
（ぽんた） 2017/01/24(火) 00:53
＞以下（結果が7時間）が入力不可となってしまいます。 ＞7時間までは入力可能なはずだと思いますが、エラーとなってしまいます。 ＞8:30〜16:15 ＞9:00〜16:45
こちらでは、普通に入力できます。 現象が再現しないので、エラーの原因は分かりません。
＞それから、以下の数式の ROW(INDIRECT("A1:A7")) 部分はどのような意味になりますか？
単に、1〜7までの配列を作っているだけです。
配列定数が使えれば {1;2;3;4;5;6;7} でいいんですが、入力規則には使えない為。
わざわざINDIRECT()を使っているのは、表のレイアウトが変更になって、 その配列が勝手に(自動的に）変化するのを避けるためです。
＞=AND($M24="",OR($AB24="",ISNUMBER(MATCH((0&$AB24)*1,ROW(INDIRECT("A1:A7")),0))))
~~~↑~~~~~~
ここは単に$AB24だけでよかったです。(当初の仕様の名残です）
全体の意味は、 (1)M24が必ず空欄であること かつ (2)AB24が空欄であるか または 数値が1,2,・・,7のどれかであること。(注：AB24が4になることはない→0.5日に移るので)
こんなのでもよかったです。 ↓ =AND($M24="",IF($AB24="",TRUE,AND($AB24=INT($AB24),1<=$AB24,$AB24<=7))) ↑ そこで、実際にこれに変え、くだんのマクロを消してテストしてみたところ、マクロ無しでもいけました。 そちらでもテストしてみてください。(□空白は無視する のチェックを外すのをお忘れなく）
（半平太） 2017/01/24(火) 08:02
半平太さん、別のところで質問している [20161227180631] についてですが、
こちらにもお力をいただけませんか？
これがうまく動作できれば、有給休暇簿が完成するんです。
厚かましいお願いなのですが、よろしくお願いします。
（ぽんた） 2017/01/24(火) 20:34
日単位取得の場合は、M列に入力。時間単位取得の場合は、O列及びT列に入力します。
これをさらに発展させて、日単位取得の場合は、AA列の合計から限度の日数を超えると入力不可させ、時間単位取得の場合は、AB列の合計から限度の時間を超えると入力不可とさせたいのです。そのため、以前ご教授いただいた以下の数式を変更する必要があるのですが、どのような形に変更すればよいでしょうか？
前提条件として、AU16セルにその年に取得できる日数が入力されているとします。
AU16=40日
時間単位取得限度数=40時間（これは誰でも共通）
日単位取得限度数=35日（AU16-上記時間を除して算出）
M24:M100を選択して、入力規則を設定する。
【入力の種類】→ユーザー設定、 □空白を無視する のチェックを外す 【数式】ボックスに → =COUNT(O24,T24)=0
O24:O100、T24:T100を選択して、入力規則を設定する。
入力の種類→ユーザー設定、
□空白を無視する のチェックを外す
数式ボックスに → =AND($M24="",OR($AB24="",ISNUMBER(MATCH((0&$AB24)*1,ROW(INDIRECT("A1:A7")),0))))
（ぽんた） 2017/01/29(日) 09:51
【M24:M100】 =AND(COUNT(O24,T24)=0,SUMIF(AA$24:AA24,">=1")<=$AU$16-5)
【O24:O100、T24:T100】 =AND($M24="",IF($AB24="",TRUE,AND($AB24=INT($AB24),1<=$AB24,$AB24<=7)),COUNTIF(AA$24:AA24,0.5)*4+SUM(AB$24:AB24)<=40)
（半平太） 2017/01/29(日) 10:44
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.