イベントに参加してくださったお客様から、「数式を利用した条件付き書式の設定過程でうまくいかないことがあった」とご質問をいただいたので、どのような流れで作業を行って何が原因でうまくいかなかったのか、対応方法は何かについて書いておきます。
なお、対応方法は前編と後編に分けて 2 つご紹介する予定です。
お客様からの質問内容は途中ででてきます。同じことが起きる可能性が高いので、条件付き書式によるカレンダーへの祝日の塗りつぶしをしたい!という方は、手順を参考にしつつ読んでみてください。
■お客様が準備しているデータ
- 左側のカレンダーの祝日に塗りつぶしの色を設定するために条件付き書式を使いたい。
- 該当の祝日を右側のテーブルにまとめて「祝日2022」というテーブル名を付けてある。
- 祝日のリストをテーブルにしてあるのは、「特別休暇」などが増えた場合に対応したいため。
- C 列には条件付き書式で利用する数式を作成してある (該当する日付に TRUE が表示) 。
数式を使った条件付き書式では、TRUE が返るときに書式が適用されます。
ダイアログ ボックスで数式を作るのはエラーのもとなので、事前に C 列に数式を作ってコピーして利用する予定です。
★石田のセミナーをよく受講してくださっている方なので、事前に数式を作成してコピーするんだよ、ということを守ってくださっていたご様子♪
数式の作り方は色々とありますが、ここでは MATCH 関数と ISERROR 関数と NOT 関数を使って、A 列の日付が「祝日2022」テーブルの [日付] 列に存在している場合に C 列に TRUE が返る数式を作っています。
たとえば、セル C2 の数式は「=NOT(ISERROR(MATCH(A2 , 祝日2022[日付] , 0)))」です。
2022/4/29 や 2022/5/3 などは、テーブルの [日付] 列に存在しているので TRUE が返っていることがわかります。
■条件付き書式の作成
-
C 列のセルに作成した数式をコピーします。
-
条件付き書式を設定するセル範囲を選択し、リボンの [ホーム] タブの [スタイル] グループの [条件付き書式] をクリックし、[新しいルール] をクリックします。
-
[新しい書式ルール] ダイアログ ボックスで [数式を使用して、書式設定するルールを決定] を選択し、下部のボックスにコピーした数式を貼り付けます。
-
A 列を固定するように数式を編集 ($ をつけて列を固定) し、[書式] をクリックします。
-
[セルの書式設定] ダイアログ ボックスの [塗りつぶし] タブを選択し、祝日に適用する色を選択して [OK] をクリックします。
-
[新しい書式ルール] ダイアログ ボックスの [OK] をクリックします。このあとの様子が本題!
これ↓がでちゃうんです! というのがご質問内容でした。
■質問に対する答えと対応
このメッセージが表示されるのは、数式で指定している 祝日2022[日付] の部分を Excel がテーブルの列であると認識できないからです。
これを解決する方法の 1 つは INDIRECT 関数を組み合わせることです。たとえば、祝日2022[日付] を「祝日2022」テーブルの [日付] 列と指定するには、INDIRECT("祝日2022[日付]") と記述します。
-
メッセージの [OK] をクリックすると [新しい書式ルール] ダイアログ ボックスに戻ります。
-
数式を編集して [OK] をクリックします。
=NOT(ISERROR(MATCH(A2 , 祝日2022[日付] , 0)))
↓
=NOT(ISERROR(MATCH(A2 , INDIRECT("祝日2022[日付]") , 0)))
-
条件付き書式が設定され、該当する日付のセルに塗りつぶしの色が適用されます。(うまくいきます。)
-
テーブルに祝日を追加した場合、条件付き書式のルールを編集しなくてもカレンダーの該当する日付に色が適用されます。
ご質問の内容は、条件付き書式の作成をするときにうまくいかなかった、ということ。
その原因は、祝日をまとめた表がテーブルになっているけれど、テーブル名と列名を認識できていないこと。
その対応策の 1 つが INDIRECT 関数を組み合わせた数式とすること。
でした。
対応策の 2 つ目である INDIRECT 関数を使用せずに名前の定義を利用する方法については後編でご紹介します。
なお、条件付き書式ではなく、データの入力規則のドロップダウン リストに表示するデータをテーブルで対応したい、という場合の例については、こちら に書いていますので参考にしてください。
石田 かのこ