枯れ木に花を咲かせましょう

Excel の条件付き書式

昨年、孫の誕生日を忘れていて誕生祝が後付けになってしまいました。
近くに孫が二人いますが今年は忘れずにと、Excelを使うことを考えてみました。

まず、Excelで家族の誕生日テーブルを作成します。
生年月日を書き込んであるセルで、誕生日の30日前から10日前まで間に背景色を黄色に塗りつぶす。
さらに、10日前から誕生日までの間は背景色を赤色に塗りつぶす。
ということにしてみます。


cap20170222-1.png

ホームメニューのスタイルグループにある「条件付き書式」をクリックし、
「セルの強調表示ルール(H)」 → 「その他のルール(M)」とたどります。
上のサンプル画像ではすでに黄色や赤色の背景色になっているセルがありますが、
条件付き書式を設定してしまっているので背景色が変わっているわけです。
最初はテーブルの縞模様だけです。


cap20170222-2.png

「新しい書式ルール」という小窓が開きますので、一番下の「数式を使用して、書式設定するセルを決定」を選択します。
「次の数式を満たす場合に値を書式設定(O):」の欄に数式を記述します。

黄色:誕生日の30日前から11日前までは、
=AND(DATEDIF(TODAY(),DATE(YEAR(C2)+120,MONTH(C2),DAY(C2)),"yd")>10,DATEDIF(TODAY(),DATE(YEAR(C2)+120,MONTH(C2),DAY(C2)),"yd")<=30)

赤色:誕生日の10日前から誕生日当日までは、
=DATEDIF(TODAY(),DATE(YEAR(C2)+120,MONTH(C2),DAY(C2)),"yd")<=10

と二つの条件付き書式を設定します。
DATEDIF関数で、今日から誕生日までの日数を計算しています。
DATEDIF関数は、「=DATEDIF([開始日],[終了日],"戻り値の種類"」という書式ですが、
戻り値は年未満の日にちで良いので引数を"YD"にします。
開始日は今日、終了日は誕生日ですが、終了日が開始日より過去ではエラーとなります。
そこで、年未満の日数だけがほしいわけですから、年数は関係ないので、誕生年に何年か足してやることにします。
ここでは、大還暦を迎える人はめったにないので120年を足しておきます。
これで先ず誕生日が今日より過去になることはないですね。

DATEDIF関数はExcelができる前、表計算ソフトのLotus 1-2-3 の関数で互換性のためにExcelが採用しています。
Excelの関数一覧にもヘルプにもない関数ですけど、年齢計算など最も使われる関数ですね。
Excelではバグがあるということが知られています、特に引数の"YD", "MD"を使う場合ですが、
閏年の問題に関係しているようです。
グレゴリー歴(西暦)が4で割り切れる年が閏年ですが、4で割り切れても100で割り切れる場合は平年。
さらに400で割り切れる場合はやっぱり閏年。
2000年が400で割り切れる400年に一度の珍しい年でした。
コンピューターは西暦二桁計算と400年閏年の2000年問題とかで大変な騒ぎの年でしたね。
DATEDIF関数のバグを考えると、誕生年が2100年を跨がないほうが良いのでしょうけど。
まっ、1日2日間違っても関係のない話ですから、いっそ400年くらい足したほうが良いかも知れませんね。


cap20170222-3.png

条件式を記入したら「書式」ボタンをクリックして、セルの書式設定でセルの塗りつぶし色を選びます。
そしてこの書式の適用範囲を「C2:C12」などのように書き入れてOKです。
この書式の記入欄は小さくて全体が見えないし、矢印でカーソルを移動しようとすると範囲が出てきたり使いにくいです。
長い条件式を書く場合は、WORDやテキストエディターなどに書いてコピー・ペーストしたほうが間違いないですね。

条件付書式設定が終わったらファイルを保存して、そのファイル名を右クリックしてショートカットを作ります。
そのショートカットをWindowsのスタートアップに入れておきます。
以前はWindowsのメニューにスタートアップという項目があったのですが、Windows10ではありません。
ユーザーディレクトリーのかなり深い層にあり、隠しフォルダーですので次の方法が良いです。
ウィンドウズボタンを右クリックして「ファイル名を指定して実行(R)」を左クリックします。
「名前(O)」欄に、shell:startup と記入して「OK」ボタンでスタートアップフォルダーが出ます。
そこへExcelファイルのショートカットを張り付けてスタートアップの完成です。


cap20170222.png

このように、Windowsを起動すると最初に必ずこのExcelファイルが立ち上がってきます。
孫の誕生日の30日前には黄色、10日前からは赤色で注意喚起してくれますから、
これで孫の誕生日をパスしてしまうことはなさそうです。


「Excelの条件付き書式」の続きがあります
クリックしてください→
追記

条件式のDATEDIF関数で、期間の終了日を[DATE(YEAR(C2)+120,MONTH(C2),DAY(C2))] すなわち誕生年+120としていますが、
誕生年によっては1日の違いが生じることがわかりました。
そこで、期間の終了日を[DATE(YEAR(TODAY())+400,MONTH(C2),DAY(C2))] 、
あるいは、[DATE(YEAR("2099年12月31日"),MONTH(C2),DAY(C2))] とかにすると、
いまのところ1日の違いはなく計算されているようです。

OFFICE のバージョンは2013ですが、やはり EXCEL における DATEDIF関数のバグでしょうか。
孫の誕生日なら一日の違いくらいなんでもないですが、ビジネスの重要な日がターゲットならちょっと使えないかもしれません。









スポンサーサイト
  1. 2017/02/22(水) 11:50:01|
  2. パソコン
  3. | トラックバック:0
  4. | コメント:4
<<宇品海岸プロムナード | ホーム | 春告げ草>>

コメント

エクセルの最難関レベルのテクニックですね。
途中まで読んでギブアップ!
日を改めて、実際にチャレンジしてみようと
思うのですが・・・
わたしの能力でできるか、疑問です。
それにしてもエクセルは使えば
いろいろなことが、できることが
分かりました。
勉強になります!
  1. 2017/02/24(金) 09:48:41 |
  2. URL |
  3. 風の旅人 #GxAO5jdM
  4. [ 編集 ]

風旅さんへ

説明が下手なのと、ちょっと端折って書いているので解かり難くてすみません。
条件式が長いので面倒ですが、コピペで貼ってもらうとよいと思います。
エクセルは面白いですね。
若い頃のMS-DOS時代は Lotus 1-2-3 でしたけど、ずいぶん仕事で役に立ちました。
  1. 2017/02/24(金) 17:14:49 |
  2. URL |
  3. 花咲か爺 #MDo56pwE
  4. [ 編集 ]

MS-DOSや Lotus の言葉は
パソコンの出始めの頃というか、
会社にオフィスコンピューターの導入を
決めたころのS40年代初めの頃、
技術セクションや総務などの管理セクションの
仲間から聞いたことがありましたが、
その頃から余り関心が無くてチンプンカンプン!
コンピューターは数字0か1の計算式?で
動くことだけは記憶に残っています(笑)

  1. 2017/02/25(土) 10:01:52 |
  2. URL |
  3. 風の旅人 #GxAO5jdM
  4. [ 編集 ]

1980年代から

コンピュータを初めて使ったのは1980年頃、
シャープのポケットコンピューターPC12xxシリーズでした。
プログラムを1行づつ順番に実行するBASICインタープリターでした。
自分でBASICプログラムを作って仕事に使って、
現場での省力化は目から鱗でしたね。

MS-DOSを使いだしたのはそれからすぐでした。
最初に自分で買ったPCはやはりNECでしたが、
90年代になってDOS/V機(PC/AT互換機)が普及しだして自作を始めました。
日本のPC/AT互換機の普及を遅らせたNECは二度と使う気にはなりません。

今は道楽で作ったデスクトップが4台、ノートが1台、
加えてタブレットやファブレットが2台で使うのに忙しいです(笑)。
しかしスマホが普及して、もうPCはビジネス用か、オタクの個人使用用途ですね。
スマホも旅行などに便利ですが、私はやはりデスクトップ機が一番です。
  1. 2017/02/25(土) 11:49:32 |
  2. URL |
  3. 花咲か爺 #MDo56pwE
  4. [ 編集 ]

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://deadwoods.blog.fc2.com/tb.php/256-b5a9284d
この記事にトラックバックする(FC2ブログユーザー)

プロフィール

花咲か爺

Author:花咲か爺
日記帳はいつも最後まで書いたことがなかったし、ウェブログも二度開設したが続かなかった。
三度目のブログにトライで、時折々の雑事を書いて続けられたらよし。
枯れ木も山のにぎわい、花咲か爺が「枯れ木に花を咲かせましょう」。

カレンダー

08 | 2017/09 | 10
- - - - - 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

カテゴリ

未分類 (1)
山 (25)
海 (18)
植物 (23)
動物 (3)
街 (12)
旅行 (26)
神社仏閣 (11)
道具 (52)
パソコン (32)
モバイル (7)
飲食 (30)
買い物 (9)
My Favorite (17)
その他 (10)
写真 (6)
季節 (29)
風景 (6)
観光 (1)

最新記事

最新コメント

月別アーカイブ

リンク

このブログをリンクに追加する

最新トラックバック

検索フォーム

RSSリンクの表示

QRコード

QR