関数や数式を使った書式のコピペの方法［条件付き書式のコピペのコツ］

今回の記事では

Excelで関数や数式を使った計算はコピペ出来るけど書式設定はコピペ出来ないの？

もし、出来ないなら書式設定だけ自動化する方法やコピペする方法を知りたい

条件付き書式を使った書式設定の自動化は出来たけど設定したい範囲が広がった時はどうしたら良いの？

普通の書式設定のように簡単にコピペする方法が知りたい

こういった疑問に答えます

書式設定を効率的にコピペする方法のテーマ

①関数や数式を使った書式のコピペは出来ないけど書式のコピペで効率化できる

②条件付き書式のコピペのコツを知っておけば書式の自動設定は簡単になります

この記事を書いているのはOfficeを約20年間活用しながら

ITを活用したエステの経営マネジメントしている私の経験を基に

「書式設定を効率的にコピペする方法のテーマ」に関して実体験に基づいて解説しています

今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい

関数や数式を使った書式のコピペは出来ないけど書式のコピペで効率化できる

関数や数式を使った書式のコピペは出来ませんが書式のコピペの効率化は出来ます

なぜなら、関数や数式はあくまで計算式を設定するもので書式の設定は別の設定だからです

関数や数式を設定して計算の自動化はできたけど一緒に書式の設定も自動化したい

でも、関数だけじゃ書式設定の自動化は出来ないから諦めた方がいいの？

そういう相談をされることがたまにあります

今回はそんな悩みを解決する方法について解説します

こんな風に設定すると、社名を変更する度にグループと請求額・請求額の色が自動で変更されます

ためしに社名を「A社からC社」に変更してみましょう

その結果、グループ名が「2」に変更されて請求書の色が「オレンジ色」に変更されました

この仕組は、主に「VLOOKUP関数」「リストの設定」「条件付き書式」の3つを設定することで実現できます

要は、関数を使って表示内容を自動変更させ、同時に書式の設定の変更も行うにはこの方法が一番簡単という訳です

では、ざっくりですが今回の仕組みを解説します

社名を入力規則で設定します

「データの入力規則」ボタンをクリック

入力値の種類から「リスト」を選択、元の値に「=$A$2:$A$6」と入力して「OK」ボタンをクリック

これで社名のリストが出来ました

「A社」を選択しておきます

次はグループを自動で表示する設定です

「E2のセル」に「=VLOOKUP(F2,$A$2:$B$6,2,0)」と入力

コレでグループ番号を自動で表示する設定が出来ました

次に「G2のセル」に「=VLOOKUP(F2,A2:$C$6,3,0)」と入力

これで請求額を自動で表示する設定が出来ました

ここから条件付き書式を使って書式設定の自動化をしていきます

「条件付き書式」をクリック「新しいルール」をクリック

「次の数式を満たす場合に値を書式設定」項目のテキストボックスに「=E2=1」と入力

これは「E2のセルが1だったら」という意味です

「書式」ボタンをクリック

「フォントタブ」の「色」のリストから「青」をクリック

「OK」ボタンをクリック

設定が終わったら「OK」ボタンをクリック

グループが「1」なので請求額が「青色」になりました

あとは同じ要領で「グループ番号が2の場合の書式設定」を追加して「OK」ボタンをクリック

同じ要領で「グループ番号が3の場合の書式設定」を追加して「OK」ボタンをクリック

これで3パターンの条件付き書式が設定出来ました

内容に問題がなければ「OK」ボタンをクリック

こんな風にグループが3のときは「請求額が緑色」に自動で切り替われば設定完了です

ちょっと難しく感じるかもしれませんが、Excelをある程度実務で使っている人であればそんなに難しく無い方法です

条件付き書式を上手く使えば書式を自由に設定することが出来ます

関数や数式と一緒に書式設定をすることは出来ませんが、それぞれの機能を使うことでシステムっぽいことは出来たりします

この方法を使えばある程度自由な書式設定を自動化できますが自動化出来ない場合もあるので書式のコピペの基本も押さえておきましょう

今回は左の書式を右の何も書式設定していない方にコピペしてみましょう

コピーの範囲をコピー

「I1のセル」をクリック

「ホーム」タブの「貼り付け」ボタンの▼をクリック

「基の書式を保持」をクリック

コレだけで「書式のみ貼り付け」出来ます

作業はめちゃくちゃ簡単ですよね

これが書式のコピペの基本です

今回、請求額の書式がエラー表示されていますが、これは条件付き書式の設定が原因です

条件付き書式のコピペについては後半で解説します

オートフィルで書式のコピペ

最後にオートフィル機能を使った書式設定の方法を見ていきましょう

最初のセルの書式設定をします

オートフィル機能を使ってセルごとコピーします

フィルのオプション機能をクリック

「書式のみコピー(フィル)」をクリック

これだけで書式設定を簡単にコピー出来るので覚えておくと便利ですよ

こんな風に書式設定のコピーの方法は色々あるので目的や機能に合わせて使い分けてみましょう

繰返しになりますが、関数や数式を使った書式のコピペは出来ませんが書式のコピペの効率化は出来ます

条件付き書式のコピペのコツを知っておけば書式の自動設定は簡単になります

条件付き書式の設定をコピペするにはある程度コツが必要ですが、コツさえ掴んでしまえば作業はすごく簡単です

なぜなら、条件付き書式のコピペのコツはセルの参照方法にあるからです

条件付き書式をコピーする時は参照方法に気をつければ上手くコピペできます

まずは、条件付き書式をコピペして上手くいった結果から見てみましょう

こんな風に条件付き書式をコピーすると30万円以上の項目を自動で塗り分けるように設定出来ます

では、この設定方法について解説していきます

「B5のセル」を選択した状態で「条件付き書式」の「新しいルール」をクリック

「数式を使用して、書式設定するセルを決定」をクリック

数式の「↑」ボタンをクリック

「B5」のセルをクリック

「↓」をクリックすると元のダイアログ画面に戻ります

数式に「=$B%$>=300000」と入力

「書式」ボタンをクリック

オレンジ色を選択したら「OK」ボタンをクリック

「OK」ボタンをクリック

B5のセルは30万以下なので書式は変わりませんが、オートフィルで「B15のセルまでフィルコピー」します

「書式のみコピー(フィル)」をクリック

本当はこれで書式設定が反映されるので色が変わるはずですが、なぜか変わりません

原因は「セルの参照方法」にあります

参照方法を修正するために「条件付き書式」から「ルールの管理」をクリック

「ルールの編集」ボタンをクリック

数式の「B5」を相対参照に修正して「OK」ボタンをクリック

縦横に書式をコピーしたいので「相対参照」に設定するのがポイントです

式を修正すると自動的に書式設定も修正されます

あとは、同じ要領で表全体にフィルコピーします

フィルコピーしたら「書式のみコピー(フィル)」を選択

その結果、ちゃんと条件付き書式をコピーして自動で塗り分けしてくれるようになります

こんな風に条件付き書式を設定する時は、セルの参照方法に注意して下さい

これが、条件付き書式のコピーのコツです

繰返しになりますが、条件付き書式の設定をコピペするにはある程度コツが必要ですが、コツさえ掴んでしまえば作業はすごく簡単です