Excel 2021以降、FILTER関数、UNIQUE関数、GROUPBY関数など、数式がスピルする関数が続々追加されています。元のデータが変更されれば、スピルする範囲も自動で変わります。そこで問題になるのが、スピルした範囲の書式設定です。表示形式は空白のセルの見た目に影響しないので、あらかじめ多めの行数に設定しておいても差し支えないでしょう。しかし、塗りつぶしの色や罫線はデータが存在する行だけにピッタリ表示したいものです。ここでは条件付き書式を使用して、スピルした範囲に自動で色と罫線が表示されるように設定します。
ここでやること
図1のセルH3にはFILTER関数が入力されており、セルF3の抽出条件欄に入力されたランク（ここでは「B」）の会員データを名簿から抽出しています。
■Before
図1 ランクが「B」の会員データを抽出。抽出範囲を囲む青枠はスピルした範囲内のセルを選択したときに表示されるもので、書式設定された罫線ではない。
ここでは2種類の条件付き書式を使用して、H列の「No」欄に色を、H列～K列のデータが表示されている行に罫線を設定します。
■After
図2 抽出結果の1列目に色を付けたい。さらに、抽出結果全体に罫線を引きたい。
手順1…抽出範囲の特定の列に色を表示する
まずは抽出範囲の1列目に色を付けます。抽出元データである会員名簿の1列目に必ずデータが入力されているという前提で、「H列のセルが空白でない」という条件を指定します。実際に指定する条件式は、セルH3が空欄でないという意味の「=H3<>""」です。セルH3を単純な相対参照の「H3」と指定することで、セルH4では「=H4<>""」、セルH5では「=H5<>""」……のように、各セルの条件が相対的に変わります。
図3
(1) 条件付き書式を設定するセルを選択する。多めのセルを選択すること。
(2) ［ホーム］タブの［条件付き書式］をクリック。
(3) ［新しいルール］をクリックする。
図4
(4) ［数式を使用して、書式設定するセルを決定］をクリック。
(5) 「=H3<>""」と入力する。
(6) ［書式］をクリックする。
図5
(7) 書式設定の画面が開いたら［塗りつぶし］タブをクリック。
(8) 色を選択する。
(9) ［OK］をクリックして図4にもどり、［OK］をクリックする。
図6
抽出範囲の1列目に色を表示できた。
メモ
手順2…抽出範囲全体に罫線を表示する
次に抽出範囲全体に罫線を設定します。手順1と同様に「H列のセルが空白でない」という条件を指定しますが、条件式はセルH3の「H」の前に絶対参照の記号「$」を付けて「=$H3<>""」とします。セルH3の列を絶対参照、行を相対参照で指定することで、セルH3～K3の条件は「=$H3<>""」、セルH4～K4の条件は「=$H4<>""」、セルH5～K5の条件は「=$H5<>""」……、のように、同じ行のセルの条件が同一になります。
図7
(1) 条件付き書式を設定するセルを現在の抽出範囲より多めに選択する。
(2) ［ホーム］タブの［条件付き書式］→［新しいルール］をクリックする。
図8
(3) ［数式を使用して、書式設定するセルを決定］をクリック。
(4) 「=$H3<>""」と入力する。
(5) ［書式］をクリックする。
図9
(6) ［罫線］タブをクリックする。
(7) ［色］欄から罫線の色を選択する。
(8) 各セルに表示する罫線の位置を指定する。ここでは各セルの上と下に罫線を表示する。
(9) ［OK］をクリックして図4にもどり、［OK］をクリックする。
図10
抽出範囲全体に横罫線を表示できた。
図11
抽出条件を変更すると抽出範囲が変わり、新しい抽出範囲に合わせて色と罫線が設定し直される。
メモ