エクセル 文字表示 任意の期日が設定できますか？

質問

例えば
あるブックのシート上に
　　　　A　　　　　B　　　　 　C　　　 　　D
１　　４月　　　　５月　　　　６月　　　　７月
２　　１６　　　　 ５　　　　　９　　　　　２３
３　４月２８日　５月３１日　６月２９日　７月２７日

というデータが入っているとして，A2の「１６」，B2の「５」，C２の「９」，D2の「２３」の数字が，当該月末日（それぞれ４月，５月，６月，７月）に近い当該月内の最終平日を迎えたら表示されるように，条件付き書式で設定できないかどうか？と考えています。
あるいは，ある日にちを別セル（例A3に４／２８，B3に５／３１，C3に６／２９，D3に７／２７）を入力し，その日にちが来たらA2の「１６」，B2の「５」，C２の「９」，D2の「２３」の数字が表示されるようにできないかと…
条件付き書式，関数，マクロ…どのような方法でも構いませんので，ご教授頂けましたらありがたいです。どうぞよろしくお願いいたします。

回答No.4

bunjii · ベストアンサー

3行目の日付を予め手入力してあることを前提にすれば2行目に条件付き書式でフォントの色を指定すれば可能です。（フォントの色を塗りつぶしの色と同じにする）
条件式は下記の通りとします。
=A3>TODAY()
貼付画像はA2～D2を選択して条件の数式を指定した状態です。
3行目の日付が今日（TODAY()）より小さいときはフォントの色を塗りつぶしの色と同色（白）を指定してあります。
C2には9、D2には23が入力されています。
3行目の日付は検証用なので連続日付としました。
尚、3行目に当月の最終平日（最終営業日）を算出するには週休日（土日？）以外に祝祭日や振替休日を織り込む必要がありますので条件を提示してください。
数式を組むと難解になると思います。

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！意外にも簡単な設定でできることが分かり目から鱗でした。ありがとうございました。

回答No.8

kagakusuki

回答No.1、6です。

　同じ条件で、「新しい書式ルール」ダイアログボックスの「次の数式を満たす場合に値を書式設定」欄の中に入力する数式を次の様にしても同じ結果を得る事が出来ます。

=TODAY()<WORKDAY(EDATE($N$1&"/"&SUBSTITUTE(A$1,"月",),1),-1,$O$4:$O$20)

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！調整の仕方まで丁寧に，そして重ねてご回答いただき感謝いたします。ありがとうございました。

回答No.7

bunjii

回答No.4の追加です。
>当該月末日（それぞれ４月，５月，６月，７月）に近い当該月内の最終平日を迎えたら
模擬データの1行目が日付のシリアル値であり、表示形式をユーザー定義で「m"月"」してあるものとすれば3行目の当該月の末日は次の数式で算出できます。
=EOMONTH(A1,0) → "2016/4/30"
当該月の末日が土日のときは調整する必要があり、その日数は次の数式で求められます。
=MAX(WEEKDAY(EOMONTH(A1,0),2)-5,0) → MAX(1,0) → 1
更に祝祭日の調整が必要のときは当年の祝祭日一覧表に週休調整後の最終日が祝祭日に当たるか否かをチェックしなければなりません。
次の数式で調整日数を算出できます。
=COUNTIF($J$1:$J$18,EOMONTH(A1,0)-MAX(WEEKDAY(EOMONTH(A1,0),2)-5,0)) → 1
但し、$J$1:$J$18は当年の祝祭日一覧が入力されているものとします。
A3セルには上記の3つの数式を組み合わせて次のように記述します。
=EOMONTH(A1,0)-MAX(WEEKDAY(EOMONTH(A1,0),2)-5,0)-COUNTIF($G$1:$G$18,EOMONTH(A1,0)-MAX(WEEKDAY(EOMONTH(A1,0),2)-5,0))

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！調整の仕方まで丁寧に，そして重ねてご回答いただき感謝いたします。ありがとうございました。

回答No.6

kagakusuki

回答No.1です。
　同じ条件で、「新しい書式ルール」ダイアログボックスの「次の数式を満たす場合に値を書式設定」欄の中に入力する数式を次の様にしても同じ結果を得る事が出来ます。

=TODAY()<WORKDAY(EDATE($N$1&"年"&A$1,1),-1,$O$4:$O$20)

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！何度もご回答いただき感謝いたします。ありがとうございました。

回答No.5

msMike

》 当該月末日に近い当該月内の最終平日を迎え
》 たら表示されるように，条件付き書式で設定
「…を迎えたら表示される」を「…を未だ迎えないものは表示しない」として条件付き書式を設定します。
「表示しない」だと見えないので、此処では添付図のように、「迎えないものは太字斜体灰色で表示する」ことにしました。
A4: =WORKDAY(DATE($A1,1,1),NETWORKDAYS(DATE($A1,1,1),EOMONTH(DATE($A1,1,1),A2-1),nhldy),nhldy)
セル A3 における条件付き書式は次のとおり。
数式が　　　　=TODAY()<A4
書式:　フォント→太字斜体、色→灰色
ちなみに、nhldy は 2016年の祝日リスト(別表、此処では省略)に付けた名前です。

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！数式をもう少し勉強しなければと思います。ありがとうございました。

回答No.3

msMike

「７月２７日」を月末稼働日とするのは西暦何年のことか教えてください。
それとも「…というデータ」に示された月末日は単なる例であって、実はデタラメだと？

お礼

以後、質問の仕方に気をつけたいと思います。すみませんでした。

回答No.2

dogs_cats

２行目の数値を入力し、セルの書式設定でフォント色を白色をしてすると数値は見えなくなります。（セルの背景色を付ける場合は、フォント色を背景色と同じにする）

A2~数値の入っているセルを選択する。
条件付き書式→新しいルール→数式を使用して書式設定するセルを決定する
数式
=AND(A3<>"",TODAY()>=A3)
書式設定でフォント色を黒を選択

条件付き書式　数式を使用
http://love-guava.com/excel-conditional-format/

３行目は日付である事が前提です、月日だけの表示は書式設定で変更下さい。文字列では対応しません。

添付画像のA2~D2は白色フォントで数値を入力しています。
２月データのみ２行目の数値表示。

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！意外にも簡単な設定でできることが分かり目から鱗でした。ありがとうございました。

回答No.1

kagakusuki

まず問題なのは単に
４月　　　　５月　　　　６月　　　　７月
としたのでは2016年の4月～7月の事なのか、1900年の4月～7月の事なのか、はたまた9999年の4月～7月の事なのかが判らないため、月末が平日なのか休日なのか判断のしようが無いという点です。
　ですから、単に
４月　　　　５月　　　　６月　　　　７月
としただけでは、

>当該月末日（それぞれ４月，５月，６月，７月）に近い当該月内の最終平日

を求める事など出来ませんので、何らかの手段で西暦何年の日付なのかを指定してやらなければなりません。
　又、「平日ではない日」には祝日等もありますから、その年の祝日や振替休日等の「平日ではない日」の日付を網羅した一覧表も必要になります。

　そこで今仮に、1行目に入力されている月は全て西暦2016年の月であるものとし、尚且つN列とO列に次の様に入力されているものとします。

　　　　　N列　　　　　　　O列
1行目 　　2016 　　　　　　年
2行目　　
3行目 　　名称　　　　　　　日付
4行目 　　元日　　　　　　　2016/1/1
5行目 　　成人の日 　　　　　2016/1/11
6行目 　　建国記念の日　　　2016/2/11
7行目 　　春分の日　　　　　2016/3/20
8行目 　　昭和の日　　　　　2016/4/29
9行目 　　憲法記念日　　　　2016/5/3
10行目　　みどりの日　　　　2016/5/4
11行目　　こどもの日　　　　2016/5/5
12行目　　海の日　　　　　　2016/7/18
13行目　　山の日　　　　　　2016/8/11
14行目　　敬老の日　　　　　2016/9/19
15行目　　秋分の日　　　　　2016/9/22
16行目　　体育の日　　　　　2016/10/10
17行目　　文化の日　　　　　2016/11/3
18行目　　勤労感謝の日 　　　2016/11/23
19行目　　天皇誕生日　　　　2016/12/23
20行目　　振替休日　　　　　2016/3/21

　又、

>A2の「１６」，B2の「５」，C２の「９」，D2の「２３」の数字

が

>当該月末日（それぞれ４月，５月，６月，７月）に近い当該月内の最終平日

にならなければ見えないのでは、何と入力したのかが解り難くなりますので、

>当該月末日（それぞれ４月，５月，６月，７月）に近い当該月内の最終平日

になる前には、薄い灰色で表示される様にするものとします。
　その場合における条件付き書式の設定方法は次の様なものとなります。

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

=TODAY()<WORKDAY(DATE($N$1,SUBSTITUTE(A$1,"月",)+1,1),-1,$O$4:$O$20)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある薄い灰色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄を

=$A$2:$D$2

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　以上です。

お礼

お礼が遅くなり大変申し訳ありません。試してみたところ、見事にできました！！ありがとうございました。