[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『平日と土日祝、時間帯を判断し、○以上の値をチェックつけたい』(かりん58)
教えてください。お願いします。
ExcelのC列には1ヶ月間で異なる日付が並んでいます。(YYYY/MM/DD)
その横D列には色んな時間が並んでいます。(hh:mm:ss)
P列にはとある値が並んでいます。
この時、まず、C列の日付が平日か土日祝(暦通)かを判断し、
平日であった場合
B列が7:00〜23:00内か、それ以外かを判断し、
7:00〜23:00内で、尚且つP列の値が『5』以上の行に色をつける
(色は可能であればP列のセルへつける、難しいようであれば行全体でも)
7:00〜23:00以外で、尚且つP列が『30』以上の行に別な色をつける
土日祝日であった場合
B列が8:00〜21:00内か、それ以外かを判断し、
8:00〜21:00内で、尚且つP列の値が『5』以上の行に色をつける
(平日の5以上と同じ色でいいです)
8:00〜21:00以外で、尚且つP列が『30』以上の行に別な色をつける
(平日の30以上と同じ色でいいです)
可能でしょうか。よろしくお願いいたします。
< 使用 Excel:Excel2013、使用 OS:Windows7 >
土日以外の祭日は、どこかに範囲入力し、"祭日" という名前を付けてあるものとします。
また、データは2行目からと仮定。 すると、以下の式で平日か休日かを判断できます。
=IF(WORKDAY.INTL(WORKDAY.INTL(C2,1,1,祭日),-1,1,祭日)=C2,"平日","休日")
後は、"平日" "休日" の部分に時間帯を判定するIf文を追加し、条件判定するだけです。
思った通りに判定できたならば、これを条件付き書式に書きましょう。
簡単ですが、作業するのは面倒なので、後はご自身で考えてみてください。
「面倒だから誰か代わりに作って〜」というのはお断りですよ。
(???) 2017/02/10(金) 17:45
if関数の条件で時間の指定の仕方がわかりません。
8時から21時の場合。andの8以上、21以下でもできますが、8時から21時以外の指定方法がわからなかったので投稿しています。
どなたか教えてください<(_ _)>
(かりん58) 2017/02/10(金) 18:19
すっごい遠回りなやり方だと思うのですが、今こういう感じにしてみました。
うまく動けば条件式書式に入れたいのです。
E列:休日or平日 の値
F列:8-21以外か8-21以内か 以外or以内 の値
G列:7-23以外か7-23以内か 以外or以内 の値
Sheet1!P2には値
=IF(AND(E2="休日",F2="以外",Sheet1!P2>=30),"緑","無")
=IF(AND(E2="休日",F2="以内",Sheet1!P2>=5),"青","無")
=IF(AND(E2="平日",G2="以外",Sheet1!P2>=30),"黄","無")
=IF(AND(E2="平日",G2="以内",Sheet1!P2>=5),"赤","無")
この4つを条件として式にしたい場合はどういったやり方が一番良いのでしょうか??
教えてください<(_ _)>
(かりん58) 2017/02/13(月) 11:31
条件付き書式への指定は、例えば1つ目の判定ならば、「=AND($E2="休日",$F2="以外",$P2>=30)」の場合は緑にする、というように、IF文の判定条件をそのまま条件付き書式に書いてみてください。
(???) 2017/02/13(月) 13:11
>(色は可能であればP列のセルへつける、難しいようであれば行全体でも)
P2より下のセルにだけ「条件付書式」を設定すれば済む話です。
>8:00〜21:00内で、尚且つP列の値が『5』以上の行に色をつける >(平日の5以上と同じ色でいいです) > >8:00〜21:00以外で、尚且つP列が『30』以上の行に別な色をつける >(平日の30以上と同じ色でいいです)
結局、付ける色は2つでいいんですよね?
(1) P2セルが5以上の場合に色が付く条件式 ↓ =AND(5<=P2,IF(NETWORKDAYS(C2,C2,祭日),AND("7:00"-D2<=0,D2-"23:00"<=0),AND("8:00"-D2<=0,D2-"21:00"<=0)))
(2) P2セルが30以上の場合に色が付く条件式(別の色を設定) ↓ =AND(30<=P2,IF(NETWORKDAYS(C2,C2,祭日),OR("7:00"-D2>0,D2-"23:00">0),OR("8:00"-D2>0,D2-"21:00">0)))
(半平太) 2017/02/13(月) 19:36
平日は7:00-23:00で時間を判別して、
土日祝日は8:00-21:00で時間を判別します。
なので結果としては4パターンになります。(色は5超えと30超え2種類でいんです)
ちょっとこの式を参考にして土日祝日パターンを作ってみます!!ありがとうございます。
だいぶブックが軽くなりそうです・・・
>???さん
色々ご教示ありがとうございます。
(かりん58) 2017/02/14(火) 08:32
>この式の場合、平日のみの指定となりますよね?
土日祝でも付くハズです。
(半平太) 2017/02/14(火) 08:39
例えば上の式ですが、
=AND(5<=P2,IF(NETWORKDAYS(C2,C2,祭日),AND("7:00"-D2<=0,D2-"23:00"<=0),AND("8:00"-D2<=0,D2-"21:00"<=0)))
(5<=P2,IF(NETWORKDAYS(C2,C2,祭日):平日のP2が5以上
AND("7:00"-D2<=0,D2-"23:00"<=0):7-23時以内
AND("8:00"-D2<=0,D2-"21:00"<=0):8-21時以内
という意味になりますか?
この場合土日祝日が8-21以内の場合、というカウントにはなりますか?
(かりん58) 2017/02/14(火) 08:59
=AND(5<=P2,IF(NETWORKDAYS(C2,C2,祭日),AND("7:00"-D2<=0,D2-"23:00"<=0),AND("8:00"-D2<=0,D2-"21:00"<=0)))
<数式の意味>
P2が5以上で
(1)C2が平日なら D2が7:00以上 かつ 23:00以下
(2)C2が平日じゃないなら D2が8:00以上 かつ 21:00以下
(半平太) 2017/02/14(火) 09:14
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.