Excel使いこなしの基礎と応用


条件付き書式をコピーする方法と注意点

設定した「条件付き書式」をコピーする方法と注意点をまとめました。

目次


簡単なのは刷毛型ポインタでコピー

下図(上)のシフト表サンプルには文字列によって色を塗り分ける条件付き書式を設定しています。この条件付き書式を下図(下)のシフト表にコピーしましょう。

スポンサーリンク

色分けされた範囲を選択し、
条件付き書式の設定範囲を選択
ホーム」タブのリボン左端にある「書式のコピー/貼り付け」をクリック。
書式のコピー/貼り付け
選択した範囲の周囲に太い破線が回り始め、マウスポインタが刷毛の形になったら、コピー先の範囲を刷毛でなぞるようにドラッグしていきます。
刷毛型ポインタでコピー先をなぞる
書式がコピーされて、下の表も色分けされました。
条件付き書式のコピー完了

一つのセルだけ選択して「書式のコピー/貼り付け」をしてもいいのですが、それだと余分な書式(このサンプルでは罫線)もコピーされてしまいます。
罫線もコピーされてしまう
分かりにくいかと思いますが、外枠の太線が細い線に置き換わってしまっていますね。

こんな単純な表なら罫線を引き直すのは大した手間じゃありませんからいいかもですが、実務で使う膨大なデータ表で罫線を修正するのは面倒。

そこで、次は「条件付き書式」のみコピーする手順を解説します。

条件付き書式だけをコピーしたいなら「ルールの管理」で

ホーム」タブ「スタイル」グループにある「条件付き書式▼」から「ルールの管理」をクリックします。
条件付き書式のルールの管理をクリック
条件付き書式ルールの管理」ダイアログボックスが開いたら、「書式ルールの表示」を「現在の選択範囲」から「このワークシート」に替えます。
条件付き書式ルールの管理ダイアログボックスで「このワークシート」に切り替え
適用されている4種の書式が並び、「適用先」にコピー元のセル範囲番地が表示されていますね。この「適用先」にコピー先の範囲を加えていきます。

最初の適用先のボックスの中をクリックして、セル範囲の番地の終点にカーソルを置き、
適用先のボックスの中をクリック
Controlキーを押しながらコピーしたい範囲をドラッグすると、
Controlキーを押しながらコピーしたい範囲をドラッグ
半角カンマの後にコピー先のセル範囲が追加されます。
適用先にセル範囲が追加
他の3つの書式にも同様の操作を繰り返し、「OK」。
全てのルールに範囲を追加
色分けの書式ルールだけが適用されました。
条件付き書式だけがコピーされた

コピーして、???な結果が出た時は参照形式を見直そう!

条件付き書式をコピーすると、予期しない結果が返されることがあります。

特に「数式を使用して書式設定するセルを決定」で設定の時に問題が発生しやすいようですが、この多くがセルの参照形式(絶対参照・相対参照・複合参照)に起因しています。

下図の表で、英語の点数欄(C列)には、英語の平均点以上の点数に下線付き太字赤色になるよう書式設定してあります。
英語の平均点以上の点数に下線付き太字赤色になるよう書式設定
条件となる数式は「=C3>=AVERAGE(C3:C12)
条件付き書式の数式
この書式を「化学」と「日本史」の列にコピーしてみると、
条件付き書式を二つの列にコピー
一見、うまく言ったようにも見えますが、よ~く見るとヘン。
間違った値が返された
化学の平均点は65.8、それなのに平均点以下の65点に書式が反映されています。また、日本史の平均点が74.8なのに、75点に書式が反映されていません。

「ルールの管理」でコピー先の数式を確認してみると、
コピー先の数式
順当に列移動してコピーされていて、問題は無さそうです。なんで???

結論から言うと、=AVERAGE()の参照範囲を絶対行参照にする「=C3>=AVERAGE(C$3:C$12)」必要があるのです。

3行~12行をそのまま横にスライドコピーしているのに、行を固定させなければならないのは理屈に合わない気がしますが・・・
AVERAGE(E3:E12)としてE3からE12までの平均を相対参照のまま計算するということはE3セルから9つ進んだセルまでの平均を求めるということで、E7セルの判定基準になるのがE7からE16までの平均点62.94…ということになり、E7セルの65点が平均以上と判定されちゃうわけです。
平均値を相対参照で計算すると参照セルがずれる
ちょっとややこしい例題だったかもしれませんが (^^;)、条件付き書式のコピーで、??? な結果になった時はまず参照形式を疑ってみましょう。

スポンサーリンク

F4キーを叩いて絶対参照にしたり、複合参照にしたりしてる内に正解が見つかるかもしれません。

規定のA1参照形式の他にR1C1という参照形式があって、数式をR1C1参照形式で表示してみると、問題の要因が見つかりやすいよ!

Officeで作れるもの