Excel。行と列がクロスしたセルを塗りつぶす管理表を作ってみよう。
＜条件付き書式&COUNTIFS関数＞
別表のデータをもとに、クロス表の行列見出しと一致するセルを塗りつぶした表を作りたい場合どうしたらいいのでしょうか？次のようなデータがあります。
A列には日付。B列には予約している会場名のリストがあります。
このリストだけだと視覚的に管理しにくいので、D3:H7にある行が日付で列が会場名のクロス表があります。
例えば、2月1日は、会議室Cを予約しているので、クロス表の2月1日の会議室Cがクロスしている、G4を塗りつぶしたいわけです。
考え方としては、塗りつぶすので、条件付き書式を使うことはイメージできますが、あとはどうやったら、クロスしたセルを塗りつぶすことができるのかが、ポイントですね。
あとは、どのような条件式を作ってあげるかということになるのですが、意外かもしれませんが、COUNTIFS関数を使うことで、対応することができるのです。
ただ、動きがわかりにくいので、説明をしてから、条件付き書式を作ることにしましょう。
【COUNTIFS関数で条件を満たすかどうかを判断させる】
E4をクリックして、COUNTIFS関数ダイアログボックスを表示しましょう。
検索条件範囲1には、$A$4:$A$11 条件付き書式にすることを考慮して、絶対参照を設定しておきましょう。
検索条件1には、$D4 こちらは、列を固定しておく必要がありますので、複合参照にしておきます。
検索条件範囲2には、$B$4:$B$11
こちらは、B列を絶対参照を使って、固定させています。
検索条件2には、E$3 こちらは、見出しの列に対応させるために、複合参照にしておきます。
E4の数式は、
=COUNTIFS($A$4:$A$11,$D4,$B$4:$B$11,E$3)
それでは、クロス表の各セルにオートフィルで数式をコピーしてみましょう。
該当するデータがある場合は、『1』と算出されましたね。この1のところを塗りつぶすように条件付き書式で設定してあげればいいわけです。
なお、条件付き書式で数式を作るのが苦手な場合は、先程の作成した数式をコピーすることで対応することができます。
それでは、改めて、作成した数式を削除しておいて、E4:H7を範囲選択して、ホームタブの条件付き書式から、「新しいルール」をクリックして、新しいルールダイアログボックスを表示しましょう。
ルールの種類は、「数式を使用して、書式設定するセルを決定」を選択して、数式のボックスには、
=COUNTIFS($A$4:$A$11,$D4,$B$4:$B$11,E$3)
と入力して、書式ボタンをクリックして、塗りつぶす色を選択してOKボタンをクリックしてみましょう。
このように、クロス表を塗りつぶすことができました。
ところで、条件の数式。
=COUNTIFS($A$4:$A$11,$D4,$B$4:$B$11,E$3)
で設定していますが、
=COUNTIFS($A$4:$A$11,$D4,$B$4:$B$11,E$3)=1としていませんよね。
なぜ、『=1』が不要なのかというと、Excelでは1がTRUE。0がFALSEと設定されているので、関数の算出された結果が1の場合は、TRUEということで、条件が満たされるので、塗りつぶしされるわけです。
なお、基本となる表ですが、重複をさけるように設定しておく必要があります。
