まりふのひと

日付の条件付き書式/入力していないのに土曜日になる...

家計簿77の学習中に、土・日の時、日付の書式を変える「条件付き書式」で不可解な現象が見つかった。
この現象を全て把握出来ていないので、帰宅後、Windows7/Excel2010 で試行錯誤した...

ここで使う Excel のレイアウト

§1.条件付き書式をクリアする

結果論であるが、条件付き書式がおかしくなった時、まずはクリアーし、設定し直す方が早く解決するのでは‥‥ と。

  1. B列(日付の列)を選択し、
  2. [ホーム]タブ/スタイルGr.の[条件付き書式]をクリックし、
  3. [ルールのクリア]4[選択したセルからルールをクリア]をクリックする。
§2.条件付き書式を設定する

ここでは、土・日曜日の場合、セルの背景色(塗りつぶしの色)を変えてみる。

  1. B列(日付)を選択し、
  2. [ホーム]タブ/スタイルGr.の[条件付き書式]をクリックし、
  3. [ルールの管理]をクリックする。
  4. [新規ルール]をクリックする。
  5. 【新しい書式ルール】ダイアログボックスで、
    1. 「ルールの種類を選択してください」では、[数式を使用して、書式設定するセルを決定]を選択する。
    2. 「次の数式を満たす場合に値を書式設定」の下のボックスに
        =WEEKDAY(B1)=1” を入力する。
      (1)小文字で入力可。
      (2)Weekdayの引数は、選択したセルの一番上のセルを指定する。
       今回はB列を選択したので、セルで言えば B1〜B1048576 を選択したことになる。
      (3) “=1” は、日曜日だったら‥‥ の意味。
    3. [書式]をクリックし、
    4. [塗りつぶし]タブで、オレンジ系の薄い色を選択する。(色は任意)
    5. [OK]をクリックする。
  6. [OK]をクリックする。

  7. 土曜日用を設定するので、再度[新規ルール]をクリックする。
  8. 【新しい書式ルール】ダイアログボックスで、
    1. 「ルールの種類を選択してください」では、[数式を使用して、書式設定するセルを決定]を選択する。
    2. 「次の数式を満たす場合に値を書式設定」の下のボックスに
        =WEEKDAY(B1)=7” を入力する。
      (1) “=7” は、土曜日だったら‥‥ の意味。
    3. [書式]をクリックし、
    4. [塗りつぶし]タブで、ブルー系の薄い色を選択する。(色は任意)
    5. [OK]をクリックする。
  9. [OK]をクリックする。
    • ルール(“数式:‥‥”)をポイントすると、切れた部分が見られる。(たぶん)
    • 今回の数式は「等式」なので、「R 条件を満たす場合は停止」にチェックを入れた方が処理が早くなる。(が、実質的な差は感じない‥‥ はず)
  10. [OK]をクリックして、【条件付き書式ルールの管理】ダイアログボックスを閉じる。
≪問題点と原因≫


日付を入力していないセルは、「土曜日の扱い」になる。

原因は、

  1. エクセルでは、数値的には「未入力=0」となる。
  2. Weekday(0) は 7(土曜日)となった。(右図)
  3. 日付のセルに“0”を入力すると、M33/01/00 (土) と表示される。
  4. 明治33年1月1日が日曜日、その前の日は土曜日であるため‥‥

    と、推定される。



§3.条件付き書式の補完

セルが空白(blank)の場合、塗りつぶさないようにする。

  1. B列(日付)を選択し、
  2. [ホーム]タブ/スタイルGr.の[条件付き書式]をクリックし、
  3. [ルールの管理]をクリックする。
  4. [新規ルール]をクリックする。
  5. 【新しい書式ルール】ダイアログボックスで、
    1. 「ルールの種類を選択してください」では、[数式を使用して、書式設定するセルを決定]を選択する。
    2. 「次の数式を満たす場合に値を書式設定」の下のボックスに
        =ISBLANK(B1)” を入力する。
    3. [書式]をクリックし、
    4. [塗りつぶし]タブの[色なし]をクリックする。
      • フォントの色を変えている場合は、[フォント]タブの「色」を[自動]にする。
    5. [OK]をクリックする。
  6. [OK]をクリックする。
  7. 「条件を満たす場合は停止」にチェックを入れる。
    • 順序は、数式:ISBLAN... が最上部にするのがベスト。(たぶん)
       ∵空白のセルが圧倒的に多いため。

編集後記

  • 条件付き書式は、書式のコピーに含まれる。
  • Ctrl+R は左側のセルのコピペするが、この時、書式も貼り付けられる。
  • 例えば家計簿77で、カーソルがセル B11にある時、Ctrl+R を押してしまうと、条件付き書式も変わってしまう。
    • 適用先(“$”は省略)
       (1)変更前: B:B ‥‥ セルの範囲で言えば、B1:B1048576 となる
       (2)変更後: B1:B10,B12:B1048576 ‥‥ セルB11 が除かれている
  • 「条件付き書式は壊れやすい」(壊しているのは自分だが...)と意識すべきであろう。