エクセル カレンダーの祝日自動表示

質問

色々ネットで検索しながらやっているのですが
なかなか上手くいきません。
どこが間違っているのかすらも全くわかりませんので
どなたか教えていただけないでしょうか？

シンプルな縦型のカレンダー（スケジュール管理）を作るつもりです。
年月を入力すれば、曜日も自動で入るところまではできました。

Ａ8の位置にその月の1日があります
Ｂ8の位置に曜日
Ｃ8の位置に祝日等を表示させたい

別シートの
Ａ列に祝日名が入っています。
Ｂ列に2015年の祝日一覧
Ｃ列に2016年の祝日一覧
Ｄ列に2017年の祝日一覧
名前を定義するというところまではできました。

この後、祝日名を表示させたいＣ8のセルに関数？
ネットをで検索しながらいろいろやっているのですが表示できません。

どなたかご教示いただけないでしょうか
よろしくお願いいたしますm(__)m

回答No.9

bunjii

>祝日の条件付き書式はどうすればできるのでしょうか？
C列の祝日には何らかの文字が代入されますので条件付き書式を設定するときは文字列の * にすれば良いのではないでしょうか？

回答No.8

bunjii

>難しいですね…やり直してみます。
やり直しの必要はありません。
次の数式で対応できます。
C8=IFERROR(INDEX(Sheet2!A:A,MATCH(A8,OFFSET(Sheet2!A$1,0,YEAR(A8)-2014,ROWS(Sheet2!A$1:A$20),1),0),1)&"","")
尚、振替休日は毎年変化しますのでSheet2の行数が増えることになりますので、必要に応じて挿入してください。
その時、Sheet1のC列の数式は自動的に範囲が変化するように配慮されていますので変更の必要はありません。

補足

昨日はPCの前に行くことができずお礼が遅くなりました。
有難いことに詳細に回答くださった方もいて
本当に感謝しつくせないほどです。
落ち着いたら作成し直すとして…

今は急いでいるので
bunjiiさんの数式を入力することで
何と！あっさりと望んでいたものが出来上がりました。
何回やってもできなかったのに…ありがとうございます。

ここでもう一つbunjiiさんにお尋ねしても良いのでしょうか？
条件付き書式で土日・祝祭日に色を付けたいのですが
土＝=WEEKDAY($A7)=7
日＝=WEEKDAY($A7)=1
土日はちゃんとできています。
ですが、やっぱり祝日に上手く色を付けることができません。
祝日の条件付き書式はどうすればできるのでしょうか？

回答No.7

kagakusuki

前回の回答では書き切れなかった回答No.6の続きです。

　次に、Sheet1のA8セルに次の関数を入力して下さい。

=IF(ISNUMBER(($A$2&"/"&$A$3&"/"&ROWS($8:8))+0),($A$2&"/"&$A$3&"/"&ROWS($8:8))+0,"")

　次に、Sheet1のB8セルの書式設定の表示形式を[ユーザー定義]の

aaa

に設定して下さい。
　次に、Sheet1のB8セルに次の関数を入力して下さい。

=IF(ISNUMBER($A8),$A8,"")

　次に、Sheet1のC8セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($A8),COUNTIF(Sheet2!$A$4:$A$28,$A8)),VLOOKUP($A8,Sheet2!$A$4:$B$28,2,FALSE)&"","")

　次に、Sheet1のA8～C8のセル範囲をコピーして、Sheet1のA9～C38のセル範囲に貼り付けて下さい。
　次に、以下の操作を行って、土日祝日の日の色を変えるための条件付き書式を設定して下さい。

Sheet1のA8セルを選択
　　↓
[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に

=WEEKDAY($A8)=1

という数式を入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に

=WEEKDAY($A8)=7

という数式を入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある青色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に

=COUNTIF(Sheet2!$A$4:$A$28,$A8)

という数式を入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた「パターンの色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの「パターンの種類」欄をクリック
　　↓
現れたパターンのサンプルの中から、適当な密度の点描の網掛けを選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中に3行ある「適用先」欄に入力されているセル範囲を、全て

=$A$8:$B$38

に設定する
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

　最後に、年が変わる度に
Sheet2のA9セルにその年の春分の日の日付を、
Sheet2のA21セルにその年の秋分の日の日付を、
それぞれ、西暦年を付けた形式（或いはH25.11.6の様な形式でも可）で入力して下さい。

　以上で準備は完了で、後はSheet1のA2セルに西暦年を指定する4桁の整数を、Sheet1のA3セルに月を指定する1～12の中の何れかの整数を入力しますと、Sheet1のA列に日付が、B列に曜日が、C列に祝日名が、それぞれ自動的に表示されると共に、土曜日は文字色が青、日曜日は文字色が赤に変わり、祝日の場合は文字色が赤で尚且つ赤い点描の網掛が行われます。

お礼

kagakusukiさま

とても詳しくご教示いただき感謝いたします。
一から作り直してみようと思います。

何をどう勉強したら
こんなことができるようになるのでしょう？？？
私には神業としか思えません。

本当にありがとうございました。

回答No.6

kagakusuki

>別シートの
>Ａ列に祝日名が入っています。
>Ｂ列に2015年の祝日一覧
>Ｃ列に2016年の祝日一覧
>Ｄ列に2017年の祝日一覧

との事ですが、年を指定すれば「春分の日」と「秋分の日」以外の祝日を自動的に計算させる方法があります。(西暦2007年以降のみ)

　尚、質問者様がお使いのExcelのバージョンが判りませんので、取り敢えずExcel2007やExcel 2010向けのやり方を御伝え致します。
　又、各シート名や

>年月を入力すれば

の年や月を入力するセルがどのシートのどのセルなのかが不明ですので、取り敢えず、仮の話としてスケジュール表が表示されるシートのシート名がSheet1、祝日の一覧表が入力されているシートがSheet2であり、Sheet1のA2セルに西暦年を、Sheet1のA3セルに月を入力するものとします。
　又、Sheet2のB4以下に祝日名を入力し、Sheet2のA4以下に「春分の日」と「秋分の日」以外の祝日を自動的に表示させるものとします。(「春分の日」と「秋分の日」は手入力)

　　まず、Sheet2のB1セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER((Sheet1!$A$2&"/1/1")+0),Sheet1!$A$2>=2007),Sheet1!$A$2,"")

　次に、Sheet2のA4セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,1,1),"")

　次に、Sheet2のB4セルに「元旦」と入力して下さい。
　次に、Sheet2のA5セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A4) =1)),A4+1,"")

　次に、Sheet2のB5セルに「振替休日」と入力して下さい。
　次に、Sheet2のA6セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,1,14-WEEKDAY(DATE($B$1,1,0),3)),"")

　次に、Sheet2のB6セルに「成人の日」と入力して下さい。
　次に、Sheet2のA7セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,2,11),"")

　次に、Sheet2のB7セルに「建国記念の日」と入力して下さい。
　次に、Sheet2のA8セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A7) =1)),A7+1,"")

　次に、Sheet2のB8セルに「振替休日」と入力して下さい。
　次に、Sheet2のB9セルに「春分の日」と入力して下さい。
　次に、Sheet2のA10セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A9) =1)),A9+1,"")

　次に、Sheet2のB10セルに「振替休日」と入力して下さい。
　次に、Sheet2のA11セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,4,29),"")

　次に、Sheet2のB11セルに「昭和の日」と入力して下さい。
　次に、Sheet2のA12セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A11) =1)),A11+1,"")

　次に、Sheet2のB12セルに「振替休日」と入力して下さい。
　次に、Sheet2のA13セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,5,3),"")

　次に、Sheet2のB13セルに「憲法記念日」と入力して下さい。
　次に、Sheet2のA14セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,5,4),"")

　次に、Sheet2のB14セルに「みどりの日」と入力して下さい。
　次に、Sheet2のA15セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,5,5),"")

　次に、Sheet2のB15セルに「こどもの日」と入力して下さい。
　次に、Sheet2のA16セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(WEEKDAY(A15)<4)),ISNUMBER(A15)),A15+1,"")

　次に、Sheet2のB16セルに「振替休日」と入力して下さい。
　次に、Sheet2のA17セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,7,21-WEEKDAY(DATE($B$1,7,0),3)),"")

　次に、Sheet2のB17セルに「海の日」と入力して下さい。
　次に、Sheet2のA18セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($B$1),$B$1>2015),DATE($B$1,8,11),"")

　次に、Sheet2のB18セルに「山の日」と入力して下さい。
　次に、Sheet2のA19セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,9,21-WEEKDAY(DATE($B$1,9,0),3)),"")

　次に、Sheet2のB19セルに「敬老の日」と入力して下さい。
　次に、Sheet2のA20セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(A21 =A19+2)),A19+1,"")

　次に、Sheet2のB20セルに「国民の休日」と入力して下さい。
　次に、Sheet2のB21セルに「秋分の日」と入力して下さい。
　次に、Sheet2のA22セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A21) =1)),A21+1,"")

　次に、Sheet2のB22セルに「振替休日」と入力して下さい。
　次に、Sheet2のA23セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,10,14-WEEKDAY(DATE($B$1,10,0),3)),"")

　次に、Sheet2のB23セルに「体育の日」と入力して下さい。
　次に、Sheet2のA24セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,11,3),"")

　次に、Sheet2のB24セルに「文化の日」と入力して下さい。
　次に、Sheet2のA25セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A24) =1)),A24+1,"")

　次に、Sheet2のB25セルに「振替休日」と入力して下さい。
　次に、Sheet2のA26セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,11,23),"")

　次に、Sheet2のB26セルに「勤労感謝の日」と入力して下さい。
　次に、Sheet2のA27セルに次の関数を入力して下さい。

=IF(ISNUMBER($B$1),DATE($B$1,12,23),"")

　次に、Sheet2のB27セルに「天皇誕生日」と入力して下さい。
　次に、Sheet2のA28セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(A27) =1)),A27+1,"")

　次に、Sheet2のB28セルに「振替休日」と入力して下さい。

　　次に、以下の様な操作を行って下さい。

[データ]タブをクリック
　　↓
Sheet1のA2セルを選択
　　↓
「データツール」グループの中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
現れた「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[整数]をクリック
　　↓
現れた「データ」欄をクリック
　　↓
現れた選択肢の中にある[次の値の間]をクリック
　　↓
現れた「最小値」欄に

2007

という数値を入力
　　↓
現れた「最大値」欄に

9999

という数値を入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
Sheet1のA3セルを選択
　　↓
「データツール」グループの中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
現れた「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に

1,2,3,4,5,6,7,8,9,10,11,12

と入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

※まだ途中なのですが、サイトの回答欄に入力可能な文字数制限を超えてしまいますので、残りは次の回答で行わせて頂きます。

回答No.5

bunjii

>この後、祝日名を表示させたいＣ8のセルに関数？
貼付画像のような状態でExcel 2007以降のとき次の数式で良いと思います。
B8=A8
書式の表示形式でユーザー定義で"aaa"とすれば曜日が表示されます。
C8=IFERROR(VLOOKUP(TEXT(A8,"mm月dd日"),Sheet2!A:D,MATCH(TEXT(A8,"yyyy")&"年",Sheet2!A$1:D$1,0),0)&"","")
但し、別シートの名前はSheet2とします。

補足

ありがとうございます。
画像を貼りつければ早いのでしょうが
貼り付け方がわからなくて…ご迷惑おかけいたします。

Sheet2
A列に元旦、成人の日・・・入力しています。
B列に2015/1/1、2015/1/12・・・
C列に2016/1/1、2016/1/11・・・
といった具合に入力していたので
bunjiiさんのように入力しなおせば良いですね。

難しいですね…やり直してみます。

回答No.4

msMike

[No.3補足]へのコメント、
》 A8 その月の1日です、下方向へ2日、3日…
「その月」が本年の「3月」の場合、
A8、A9、A10、… には、数値の 1、2、3、… を入力しますか？この場合、年、月は何処に入力しますか？
それとも 2015/3/1、2015/3/2、2015/3/3、… を入力して、各セルを単に 1、2、3、… と表示させますか？

補足

何度もありがとうございます。

C3に年が入っています。
E3に月が入っています。

要らない1行削除したので
現在、その月の1日はA7にあり=IF(COUNT(C3,E3)<2,"",DATE(C3,E3,1))
B7に曜日があり=TEXT(A7,"aaa")となっています。

回答No.3

msMike

》 Ｂ8の位置に曜日
A8 (その月の1日)の曜日ですね？

》 Ｃ8の位置に祝日等を表示させたい
「等」とは何々ですか？
どの日の祝日をですか？まさか A8 の、ではないですよね？
「その月」に複数の祝日がある場合はどうする、どうする？

補足

すみません、おかしいですよね…
A8 その月の1日です、下方向へ2日、3日…です。
B列に曜日なので、3月はB8に日、下方向へ月、火…
C8ではなくC列に祝日名を表示したいです。

よろしくお願いいたします。

回答No.2

mshr1962

A8 = "2015/02/01"
B8 = TEXT($A8,"aaa")
C8 = IF(COUNTIF(INDIRECT(YEAR($A8)&"年の祝日一覧"),$A8),INDEX(祝日名,MATCH($A8,INDIRECT(YEAR($A8)&"年の祝日一覧"),0),"")

回答No.1

Q_A_beginner

名前定義などは忘れて以下で事足りませんか？

【例】
- Sheet1

A8：2015/03/01 <- 日付を入力
B8：=TEXT($A8,"aaa") <- 曜日を表示するための関数
C8：=IFERROR(VLOOKUP(Sheet1!A8,Sheet2!A:B,2,FALSE),"") <- Sheet2から祝日名を取得するための関数

上記を記入し下にドラッグ

- Sheet2
A1：祝日の日付(Sheet1のA8列と同じように)
B1：祝日名

上記の要領でA2、B2・A3、B3に祝日日と祝日名を記入していく

上記で可能なはず。
他にもやり方は相当数ありますが、今回はこれで。