【エクセル】毎月土日・祝のみを表示したいです！

毎月の土曜日と日曜・祝日の日付を自動入力したいのですが、
皆様の知恵を貸して下さい。

■やりたい事
・年度、月をドロップダウンリストで選択すると、土日祝枠にその日付が
表示される
（ボタンは写っていませんが、リストは設置してあります）

＊追記
月毎に土曜、日曜祝日の回数が違うので大目に枠を用意し、
余った枠（8/25の下）はその都度削除します。

画像では ８／３を、A列に「月」、 B列に「／」、 C列に「日」
と列を分けてありますが、A3セル１つのセルに反映されても
かまいません。

宜しくお願いします。

No.4 ベストアンサー 回答者： tsubuyuki 回答日時： 2013/07/06 19:39

皆さんの回答をご覧になって、結構面倒であることに驚かれているでしょう。

実際、そう簡単には出来ない処理ではあります。


私からも例に漏れず面倒なやり方です。

とりあえず、図の通りです。
他の皆さんの回答も同様ですが、おそらく、図（回答）の通りに作って、
出来上がってから切り取り・貼り付けしてやる方が楽です。
もちろん、他の場所に作業列を作って、式を適切に書き換えられるなら
最初から他の場所に作って方が早いです。
そこはご自身とご相談くださいませ。


さて、Ａ１セル・Ｃ１セルに入力規則によるドロップダウンがあるものとして進めます。

図の右からですが、Ｊ：Ｍに「祝日一覧」を作っています。
最初から面倒ですが、これが無いと「祝日の判断」が出来ませんから必須です。
残念ながら、エクセルには「祝日」の概念が無いのです。

Ｆ：Ｈ列は作業列です。
　　Ｆ１：=IF(MONTH(DATE($A$1,$C$1,ROW()))<>$C$1,"",DATE($A$1,$C$1,ROW()))
　　Ｇ１：=IF(IF(F1<>"",WEEKDAY(F1)=7),ROW(),"")
　　Ｈ１：=IF(IF(F1<>"",OR(WEEKDAY(F1)=1,COUNTIF($J$1:$O$18,F1))),ROW(),"")
という式を入れ、３１行目までスマートフィルしてあります。
（１か月の最大日数は３１日ですから、３１行目まででＯＫですね。）

「３年分じゃ足りん」「会社の創立記念日も欲しい」「盆暮れ正月も必要」
と言うときは、祝日一覧の範囲を拡げて、
Ｈ１：Ｈ３１のＣＯＵＮＴＩＦ関数の第１引数を拡げてやってください。

問題の土曜・日曜一覧です。
　　Ａ４：=IFERROR(INDEX(F:F,SMALL(G:G,ROW(A1))),"")
　　Ｃ４：=IFERROR(INDEX(F:F,SMALL(H:H,ROW(A1))),"")
　　条件付き書式：式が「=OR(C4<>"",A4<>"")」
　　　　　　　　　　書式「外枠」
として、１５行目辺りまでフィルしておきます。
（２０１５年まででここに抽出される最大日数は８日分です。ちなみに２０１５年５月。）
必要に応じて、行数は調整しましょう。

この条件付き書式がミソです。
> 余った枠（8/25の下）はその都度削除します。
面倒でしょ？式まで消えてしまいますし。

ここまでで完成です。


あとは、作業列を非表示にするなり、祝日一覧を他のシートに移すなり、
適当にお好みのフォーマットに整えればＯＫです。



以上、参考まで。

この回答へのお礼

知恵を寄せて頂いきました皆様、ありがとうございました。
お陰様で作業を進めて行けそうです。

今回はtsubuyuki様をベストアンサーとして選ばさせていただきましたが、
皆様にも同様に感謝致しております。

有難うございました。

お礼日時：2013/07/10 23:25

No.5 回答者： MarcoRossiItaly 回答日時： 2013/07/07 19:09

＞……A3セル１つのセルに反映されてもかまいません。

おっしゃっているように、日付／曜日を表示するセルの値としては、なるべく「年月日の形で 1 つのセルに持たせる」ことをお勧めします。そのほうが日付データを他のいろいろな計算に使うことができます。年月日データのうち月だけとか月日だけ、曜日だけを表示するといったことは、セルの書式でごく簡単にできます。


一例を。

他の方もおっしゃっているように、祝日は一覧を用意して検索します。そのため、ある月の祝日を表示する表は、日曜とは別表に分けないと厳しいです。添付図では、次式を入力しました。

A1　2013
C1　11
A3　=date(a1,c1,1)+mod(7-weekday(date(a1,c1,1)),7)
A4　=if(month(a3)=month(a3+7),a3+7,"")
C3　=a3+1
E1　=small(index(I$2:I$18+9^9*((month(I$2:I$18)<>C$1)+(weekday(I$2:I$18)=1)+(weekday(I$2:I$18)=7)),),1+row(e3)-row(E$3))

「date(a1,c1,1)」というのは、2013 年 8 月 1 日のシリアル値です。WEEKDAY 関数は、（日）、（月）、…、（土）に対応する曜日の番号 1、2、…、7 を返す関数。「MOD(割られる数、割る数)」は、余りを求めています。INDEX は、配列を数式中に導入できる状態のものにしています。「1+row(e3)-row(E$3)」は、行の位置に応じた 1 から始まる順位です。

E 列の各セルの日付は、大きすぎる値（祝日の一覧で月が異なるか土日であるために 9^9 が足された値）の場合に、非表示になるようにしています。2100 年 1 月 1 日のシリアル値が 73,051 で、9^9 = 387,420,489 なので、「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に、「[>80000]"";m/d」と入力しました。


ここまでの操作で添付図どおりの外観になりますが、空白のようになっているセルの枠線を消したい場合は、いったん枠線を全部消し、条件を満たしたセルだけ線が引かれるようにします。A3:A7、C3:C7、E3:E7 のセル範囲の線を消した後、同じ範囲に対して、ホームタブ「条件付き書式」で「指定の値を含むセルだけ…＞セルの値＞次の値より小さい」と選び、「80000」などと入力し、「書式」ボタンから線を指定します。「""」や「大きすぎる値」のセルでは、線が消えたままとなります。

No.3 回答者： KURUMITO 回答日時： 2013/07/06 17:23

祝日も含めて表示させるとなりますと、しかも同じ月の中で日付を祝日を含めて昇順で表示させるとなりますと操作がさらに複雑となります。

こちらの指示通りで一度挑戦してみてください。
シート１お望みの表を表示させるとして例えばA1セルには２０１３年でしたら２０１３と入力します。また、８月でしたらC１セルに８と入力します。
土曜日の日付については祝日であっても無関係に表示させるとしてA３セルには次の式を入力してC３セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(MONTH(DATE($A$1,$C$1,8+(ROW(A1)-1)*7-WEEKDAY(DATE($A$1,$C$1,1))))<>$C$1,"",IF(COLUMN(A1)=1,$C$1,IF(COLUMN(A1)=2,"/",IF(COLUMN(A1)=3,DAY(DATE($A$1,$C$1,8+(ROW(A1)-1)*7-WEEKDAY(DATE($A$1,$C$1,1)))),""))))

問題は日曜日と祝日の日付についてです。
シート２に祝日に関するデータベースとして例えば次のように表を作成します。
A１セルには２０１１年なら２０１１と入力してB１セルには祝日の文字を入力します。A２セルから下方には２０１１年の祝日を例えば2011/1/1のように入力します。
B列には該当する祝日の名前などを入力します。C列は作業列として用意します。
２０１１年について、すなわち１年当たりに３列を用意します。２０１３年ならば例えばG１セルに２０１３と入力し、G２以下には祝日を2013/1/1のように入力します。H２以降には祝日の名前を、I列は作業列とします。
C,F,I列の作業列にはシート１からのデータを受けて表示させることにしますが、シート２にはシート１での指定年や、指定月を表示させるセルを用意します。
K１セルには次の式を入力します。

=Sheet1!A1

L１セルには次の式を入力します。

=Sheet1!C1

その上で各作業列には次の式を入力します。
C２セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(A2="",A$1<>$K$1),"",IF(AND(YEAR(A2)=$K$1,MONTH(A2)=$L$1),MAX(C$1:C1)+1,""))

F２セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(D2="",D$1<>$K$1),"",IF(AND(YEAR(D2)=$K$1,MONTH(D2)=$L$1),MAX(F$1:F1)+1,""))

I２セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(G2="",G$1<>$K$1),"",IF(AND(YEAR(G2)=$K$1,MONTH(G2)=$L$1),MAX(I$1:I1)+1,""))

その上でその月の日曜日になる日付と祝日をL２セル以降に表示させるためにL２セルには次の式を入力してL６セルまで下方にドラッグコピーします。

=IF(MONTH(DATE($K$1,$L$1,8+(ROW(A1)-1)*7-WEEKDAY(DATE($K$1,$L$1,7))))<>$L$1,"",DATE($K$1,$L$1,8+(ROW(A1)-1)*7-WEEKDAY(DATE($K$1,$L$1,7))))

また、L７セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(G1)>MAX(INDEX(A:I,1,MATCH($K$1,A$1:I$1,0)+2):INDEX(A:I,100,MATCH($K$1,A$1:I$1,0)+2)),"", INDEX(INDEX(A:I,1,MATCH($K$1,A$1:I$1,0)):INDEX(A:I,100,MATCH($K$1,A$1:I$1,0)),MATCH(ROW(A1),INDEX(A:I,1,MATCH($K$1,A$1:I$1,0)+2):INDEX(A:I,100,MATCH($K$1,A$1:I$1,0)+2),0)))

これでシート２での作業は終わります。
シート１に戻ってE３セルには次の式を入力してG３セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT(Sheet2!$L:$L)-1,"",IF(COLUMN(A1)=1,$C$1,IF(COLUMN(A1)=2,"/",DAY(INDEX(Sheet2!$L:$L,MATCH(SMALL(Sheet2!$L:$L,ROW(A1)+1),Sheet2!$L:$L,0))))))

No.2 回答者： tom04 回答日時： 2013/07/06 11:37

こんばんは！

一案です。
ドロップリストではないのですが・・・
↓の画像（小さいと思いますので、拡大して確認してください）で説明します。
左側がSheet1で右側のSheet2に祝日データをシリアル値で作成しておきます。

Sheet1のA1セルに西暦年・C1セルに月の数値を入力すると
日付・曜日・祝日名を表示するようにしてみました。
作業用の列を2列使います。
Sheet1の作業列E2セルに
=IFERROR(IF(MONTH(DATE(A$1,C$1,ROW(A1)))=C$1,DATE(A$1,C$1,ROW(A1)),""),"")
F2セルに
=IFERROR(IF(OR(WEEKDAY(E2,2)>5,COUNTIF(Sheet2!$B$1:$E$21,E2)),ROW(),""),"")
という数式を入れ31日分オートフィルで下へコピーしておきます。

A4セル（セルの表示形式は「日付」）に
=IFERROR(INDEX(E:E,SMALL(F:F,ROW(A1))),"")

B4セルに
=TEXT(A4,"aaa")

C4セルに
=IFERROR(IF(COUNTIF(Sheet2!$B$1:$E$21,A4),INDEX(Sheet2!$A$1:$A$21,SUMPRODUCT((Sheet2!$B$1:$E$21=A4)*ROW($A$1:$A$21))),""),"")

という数式を入れ、A4～C4セルを範囲指定　→　C4セルのフィルハンドルで下へコピー！
これで画像のような感じになります。m（_ _）m

No.1 回答者： aokii 回答日時： 2013/07/06 10:37

データが多くなるので、まずは、開始年月日と終了年月日を決め、祝日のリストは以下のURLの作成要領から祝日のデータをコピーし、

https://sites.google.com/site/ekuserudekarenda/

土日は７日毎にドラッグコピーで作成して、土日祝日の全リストを作りフィルター機能で表示を選択してはいかがでしょう。