エクセルの関数について

質問

数日前にも質問をさせて頂いたのですが、追加でわからない点が発生してしまいました。

エクセルで出勤簿を作成していて、
C3に日が記載されていたらC4は２と表示、
C3に土　　　　〃　　　　　　 １と表示、
C3に月～金　　〃 　　　　　　 0と表示させたいと質問しました。

上記、3点は
=IF(C3="","",IF(C3="日",2,IF(C3="土",1,0)))
という関数で計算するようにとご回答いただき、見事解決できました。

しかし、さらにC2が祝日の場合にC4を1と表示させたいのですというのがわからないのです。
祝日を別ページに、2014/1/1～2015/12/31まで一行ずつ羅列して入力し、
そのセルを”祝日一覧”と名前の定義を設けました。
なので、C2が祝日一覧の中の日付の場合に、C4を１と表示させるようにしたいです。

どうかご教授のほど、よろしくお願いいたします。

回答No.5

tom04

続けてお邪魔します。

はぁ～～～！そういうコトだったのですね！
横1列に1ヶ月のカレンダーがあるのならば
作業列も絶対参照にしなければならないのですが、「土」の数だけ作業列が必要になってしまいますので、
別案です。

ご迷惑かもしれませんが、↓の画像のように配置を少し変えさせていただきました。
（右へ3列ずらします）
仮に「5日目」までに「土」がある月の場合、前月の週末平日も考慮する必要があると思うので・・・
（その必要がなかっても数式の関係「OFFSET関数」で
対象日から右へ5列の中に「1」があるかどうか？というのを判断していますので、
画像のようにその月の初日はF列にしてみてください。）

すでにカレンダーは作成済みのようですので、余計なお世話になるかもしれませんが、ついでにカレンダーも作ってみました。
画像通りの配置として、D1セルに西暦年の数値・F1セルに月の数値を入力します。
これで1ヶ月のカレンダーが作成できます。

まず、F2セル（セルの表示形式はユーザー定義から　d　とだけにしておきます）に
=IF(MONTH(DATE($D1,$F1,COLUMN(A1)))=$F1,DATE($D1,$F1,COLUMN(A1)),"")
F3セルに
=IF(F2="","",TEXT(F2,"aaa"))
F4セルに
=IF(F2="","",IF(F3="日",2,IF(F3="土",IF(COUNTIF(OFFSET(F3,1,-5,,5),1),0,1),IF(COUNTIF(祝日一覧,F2),1,0))))
という数式を入れ
F2～F4セルを範囲指定　→　F4セルのフィルハンドルで月末（31日まで）のAJセルまでコピーします。
これでD1・F1セルの数値を入れ替えるだけで1ヶ月のカレンダーが作成完了です。

そして、画像では黄色いセルの部分
A2セルに
=IF($F2="","",$F2+COLUMN(A1)-6)
C2セルに
=IF(COUNTIF(祝日一覧,A2),1,"")
という数式を入れそれぞれをE列までフィルハンドルでコピー！
尚、カレンダーには必要ないのでフォント色を「白」にしておきます。

※　前月の最終週の祝日を考慮しなくても良い場合は
A～E列の数式は必要なく、空白のままで構いませんが、
前述のように黄色いセル部分の5列は数式上必要となります。

※　空白が目障りであればA～C列を非表示にしてみてください。

こんなんではどうでしょうか？m(_ _)m

回答No.4

tom04

No.2・3です。

＞月火水木金の内、どの曜日か一日でも祝日があって”１”がある場合は、その週の土曜は”１”ではなく、”０”と表示させたいのです。。。

結局
日　→　「2」
土・祝日　→　「1」
平日　→　「0」
ただし、「土」の場合その5日前（月）から1日前（金）までの5日間に「1」の日が出現した場合は
「土」でも「0」と表示

以上の解釈で・・・
No.2の方が正解だというコトですので、やってみました。
↓の画像のように作業用の列を設けた方が判りやすいと思います。
画像ではE列にしていますが、実際は使っていない列にして目障りであれば非表示にします。

作業列のE2セルに
=IF(AND(C$3="土",COUNTIF(祝日一覧,C$2-(6-ROW(A1)))),C$2-(6-ROW(A1)),"")
という数式を入れ5行分（E6セルまで）フィルハンドルでコピーしておきます。
これでC3セルが「土」の場合にその週の月～金までの中で名前定義した「祝日一覧」に存在するシリアル値が表示されます。
すなわち、その週は平日に祝日があった！というコトになります。
（C3セルが「土」の場合のみです。その他の曜日の場合は何も表示されません）

そして、C4セルに少し長くなりますが
=IF(C2="","",IF(C3="日",2,IF(C3="土",IF(COUNT(E2:E6),0,1),IF(COUNTIF(祝日一覧,C2),1,0))))
という数式を入れてみてください。

※　C3セルに数式はNo.2の回答通り
=IF(C2="","",TEXT(C2,"aaa"))
という数式にしておきます。

※　作業列の表示形式は変えても構いません。

こんなんではどうでしょうか？m(_ _)m

お礼

tom04さん＞
ご丁寧にありがとうございます。
ですが、できませんでした。

３１日まである月だとC2が１日でAG2が３１日になります。
tom04さんがおっしゃているE2～E6までの数式は、AN2～AN6に作成しました。
そしてC4セルに入力する数式を
=IF(C2="","",IF(C3="日",2,IF(C3="土",IF(COUNT(AN2:AN6),0,1),IF(COUNTIF(祝日一覧,C2),1,0))))
としましたが、11/29(土)が"1"になってしまいます。

何が間違っているわからない状態です。
何度も申し訳ございませんがご回答の程よろしくお願いいたします。

回答No.3

tom04

No.2です。

投稿後気になったので、再び顔を出しました。
前回の数式の場合、日曜でその日が祝日一覧の範囲にあっても「2」が表示されます。

仮にC2セルのシリアル値が日曜日でなおかつ祝日一覧のデータ内にある時に
表示を「1」としたい場合は、IF関数の優先順位を入れ替えてください。
前回の数式
＞=IF(C2="","",IF(C3="日",2,IF(OR(C3="土",COUNTIF(祝日一覧,C2)),1,0)))
の最初の部分
＞=IF(C2="","",
は単にエラー処理でC2セルが空白の場合は何も表示させないためで
表示させる条件の優先順位はIF関数の並び順となります。
すなわち前回の数式の順番を入れ替えて
＞=IF(C2="","",IF(OR(C3="土",COUNTIF(祝日一覧,C2)),1,IF(C3="日",2,0)))
とすれば
何曜日であろうと、C2セル値が祝日一覧の範囲にあれば「1」が表示されます。
その他の曜日は「0」～「2」が表示されます。

何度もお邪魔しました。m(_ _)m

お礼

tom04さん＞
ご回答ありがとうございます(^^)
日曜が祝日でも”１”と表示したいので、No.2の回答で大丈夫です！
沢山考えて下さって、本当に感謝です！

後、実はもう一つこの出勤簿には問題がありまして。。。
月火水木金の内、どの曜日か一日でも祝日があって”１”がある場合は、その週の土曜は”１”ではなく、”０”と表示させたいのです。。。
これは可能でしょうか？！
ご面倒をおかけいたして恐縮ではございますが、よろしくお願い申し上げます。

回答No.2

tom04

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

前の質問を読んでいないので、間違っていたらごめんなさい。
↓の画像でC2セルにはシリアル値が入るとします。

＞祝日を別ページに、2014/1/1～2015/12/31まで一行ずつ羅列して入力し、
＞そのセルを”祝日一覧”と名前の定義を設けました
「祝日一覧」と名前定義されている範囲のデータもシリアル値で入力しているとします。

C3セルに
=IF(C2="","",TEXT(C2,"aaa"))
という数式を入れておきます。

C4セルに
=IF(C2="","",IF(C3="日",2,IF(OR(C3="土",COUNTIF(祝日一覧,C2)),1,0)))
という数式を入れるとお望み通りになると思います。m(_ _)m

お礼

tom04さん＞
お礼が遅くなりまして申し訳ございません！

＞C4セルに
＞=IF(C2="","",IF(C3="日",2,IF(OR(C3="土",COUNTIF(祝日一覧,C2)),1,0)))
これが私の出勤簿にピタリとあてはまったようで、見事祝日を"1"と出力することができました！これで祝日を忘れる事無く、ひろうことができそうです(^^)／
お忙しい中、ありがとうございました！

回答No.1

ryo_ Deathscythe（@Deathscythe）

ほいほ～～い♪
またまたお邪魔しますよw

まずツッコミで「そのセルを”祝日一覧”と名前の定義を設けました。」
は「そのSheetを”祝日一覧”と名前の定義を設けました。」
ですなｗ

その一覧(の日付)がまぁ・・・Sheet"祝日一覧"のD1~D20にあると仮定しましょうか

でとりあえず祝日に該当するか判別します、空いている場所・・とりあえずC5を使用します
C5=IFERROR(VLOOKUP(C2,祝日一覧!$D$1:$D$20,1,FALSE),"")
で、C4をちょっと改造

C4=IF(C2=C5,1,IF(C3="","",IF(C3="日",2,IF(C3="土",1,0))))

でいけるはず。

お礼

Deathscytheさん＞
お礼が遅くなりまして申し訳ございません！
ご参考にさせて頂きましたが、空いている場所でC5を使用すると他の数式に不具合が生じてしまい、教えて頂いた数式を活用することができませんでした(><)
私の知識不足でしたが、大変勉強になりました。
お忙しい中、ありがとうございました！