スプレッドシートの条件付き書式で行全体を色付け | テクニック・カスタム書式

最終更新日: 公開日:
スプレッドシートの条件付き書式なら、条件にあったセルを自動で書式設定します。行全体を色付けする方法や、知っているとお得なテクニック、カスタム数式による設定など、条件付き書式のほぼすべてを解説します。
 スプレッドシートの条件付き書式で行全体を色付け | テクニック・カスタム書式

条件付き書式で素早く見やすい資料に

スプレッドシートの条件付き書式を使えば、自動で背景色やフォントを変更できます。人間の手を介さないので、時短とミス削減に大きく貢献します。

そんな条件付き書式のうち、行全体の色付け方法や知っていると得するテクニック、カスタム数式による条件設定などを解説します。これを機に条件付き書式をマスターしましょう。

条件付き書式とは

条件付き書式とは、条件を満たした場合のみ設定される書式です。「完了」のときだけ灰色にしたり、「2018/9/1」を過ぎていたら赤色にしたりできます。

まずはシンプルな設定方法を解説します。

1. 範囲を選択して「条件付き書式」
2. 「セルの書式設定の条件」を「完全一致するテキスト」
3. 「書式設定のスタイル」を設定し、「完了」

1. 範囲を選択して「条件付き書式」

書式設定したいセルを選択した状態で「条件付き書式」を選択します。セルの範囲は後から変更できますが、先に選択しておけば、設定が1段階減るので楽できます。

2. 「セルの書式設定の条件」を「完全一致するテキスト」

一つ目の赤枠「空白ではないセル」をクリックして、「完全一致するテキスト」に変更します。条件に適していれば他の選択肢でも問題ありません。

「空白ではないセル」をクリックして次へ。

選択肢はその時々にあったものを選ぶ。このケースは「完全一致するテキスト」

3. 「書式設定のスタイル」を設定し、「完了」

赤枠内から選べば簡単に書式設定できます。細かい設定は、「B」や「I」、ペンキから調整しましょう。

  • B:太字
  • I:斜体
  • U:下線
  • S:打ち消し線
  • A:文字色を変更
  • ペンキ:背景色を変更

設定完了です。実際に色が変化します。

行全体の色変更

上の条件付き書式では、当てはまるひとつのセルにしか書式設定できませんでした。しかし、別の方法を使えば当てはまる行をまとめて書式設定できます。

1. 範囲を選択して「条件付き書式」
2. 「セルの書式設定の条件」を「カスタム数式」
3. 「書式設定のスタイル」を設定し、「完了」

1.範囲を選択して「条件付き書式」

2.「セルの書式設定の条件」を「カスタム数式」

カスタム数式以外を選ぶと行全体を色付けできないので、注意しましょう。

先ほどとは違い「カスタム数式」しか選べません。

3.「書式設定のスタイル」を設定し、「完了」

「未着手」の他に「完了」「作業中」も設定しました。

「範囲に適用」を「A:B」に変えれば、B列のどこかに「未着手」「完了」「作業中」を入力したとき、自動で色が変化します。

条件付き書式のテクニック

書式順番の並べ替え

条件付き書式は、通常の背景色設定やフォント設定より優先されます。これを解消するには、通常の設定も条件付き書式にするのが簡単です。

しかし、条件付き書式同士でもうまく反映されないケースがあります。なぜなら、条件付き書式設定は、古い設定から優先されるためです。これを変更するには、設定の並び替えが有効です。

下の設定では、C2:C6の背景色を灰色にしたいのですが、現在は異なる色に設定されています。これらを灰色にするには、灰色に変更する書式設定を上に移動させましょう。

上に行くほど優先順位が高くなります。「背景色を灰色にする」を一番上にしたので、確実に反映されます

条件付き書式のみ貼り付け

通常のコピペでも、条件付き書式を貼り付けられます。しかし、条件付き書式だけをコピペしたいとき、どうすれば良いのでしょうか。

まず、通常どおりコピーしましょう。

注意するのは、貼り付けるときです。「特殊貼り付け」>「条件付き書式のみを貼り付け」を選びましょう。これで、条件付き書式だけを貼り付けられます。

範囲をマウスで指定

「範囲に適用」を指定する際、あらかじめ選択するのが簡単だと解説しました。しかし次の方法でも容易に指定できます。入力欄の右側にある「マス目マーク」を選択しましょう。

ポップアップが表示されます。この状態でセルを選択すると、選択した範囲が、文字で入力されます。これならスプレッドシートの操作に慣れていなくても心配不要です。

条件付き書式の種類

条件付き書式は、大きく分けて「単一色」と「カラースケール」の2種類があります。

単一色

それぞれのセルを判定して、書式を設定します。たとえば、A1のセルは赤色に塗ったり、B1は青色に塗ったり、それぞれのセルごとに色付けを検討します。

カラースケール

複数のセルを相対的にみて、書式を設定します。たとえば、A1:A20で比較して、相対的に値の大きなものには濃い緑色、小さな値は白色、その間はグラデーションといった具合です。

条件付き書式のカスタム数式

カスタム数式は「セルの書式設定の条件」で選択します。

ここから先は難しいので、詳しく解説します。

マストで「=」

カスタム数式は最初に「=」を入力してください。カスタム数式はスプレッドシートの関数とよく似ていますが、「=」の入力も似ている点のひとつです。忘れずに入力しましょう。

下のように最初に「=」をいれてください。

=(F4="完了")

この式は「F4(相対参照)に完了と入力されたら、書式を適用します」という意味です。相対参照は難しい概念なので、絶対参照とあわせて確認します。

絶対参照の「$」

「$」は、右にあるアルファベットや数字を絶対参照にします。わかりにくいと思うので、例を用いて解説します。D8とE8のセルには以下のように関数を入力しています。

D8「=SUM(B48:C48)」
E8「=SUM(B$48:C$48)」

どちらもB48とC48をたしていますし、計算結果は同じです。しかし、オートフィルを使うと結果が異なります。

$を使ったE列は、3しか並んでいません。これが$による絶対参照です。絶対参照で何がなされているのか詳しく見ていきましょう。D48とE48には先ほど紹介した関数が入力されています。

これをオートフィルすると以下のようになります。$はオートフィルした際に右側にある値を固定させます。E48は$の右にそれぞれ「48」があるので、オートフィルしても48は固定されたままになったというわけです。

$の特性をさらに掘り下げます。$のつき方で4つのパターンに分類し、ひとつずつ解説します。

  • A1型
  • $A$1型
  • $A1型
  • A$1型

A1型

$がひとつもついていない、相対参照のパターンです。セルがひとつズレるごとに参照元もひとつずつズレます。

(下の表は「範囲に適応」をA1:D4にした場合です。)

A B C D
1 A1参照 B1参照 C1参照 D1参照
2 A2参照 B2参照 C2参照 D2参照
3 A3参照 B3参照 C3参照 D3参照
4 A4参照 B4参照 C4参照 D4参照

使い方例

「=( A2=”完了”)」をカスタム数式としています。

$A$1型

アルファベットにも数字にも$がついた絶対参照です。この場合、すべてのセルにおいて「A1($A$1)」の結果が反映されます。

A B C D
1 A1参照 A1参照 A1参照 A1参照
2 A1参照 A1参照 A1参照 A1参照
3 A1参照 A1参照 A1参照 A1参照
4 A1参照 A1参照 A1参照 A1参照

使い方例

E3が「完了」なので、すべてのセルが緑色になっています。

「=( $E$3=”完了”)」をカスタム数式としています。

$A1型

アルファベットを絶対参照にしています。冒頭で紹介した、行全体の背景色変更もこれを応用しています。「使い方例」のように、特定のアルファベット(例ではE列)を入力すれば、その列でTRUEかFALSEかを判断します。

A B C D
1 A1参照 A1参照 A1参照 A1参照
2 A2参照 A2参照 A2参照 A2参照
3 A3参照 A3参照 A3参照 A3参照
4 A4参照 A4参照 A4参照 A4参照

使い方例

E列が「完了」のとき横の行も緑色になります。A列やB列も、E列の結果を参照しているためです。

「=( $E2=”完了”)」をカスタム数式としています。

A$1型

行全体ではなく列全体で書式設定をします。使う機会はなさそうですが方法のひとつとして紹介します。

A B C D
1 A1参照 B1参照 C1参照 D1参照
2 A1参照 B1参照 C1参照 D1参照
3 A1参照 B1参照 C1参照 D1参照
4 A1参照 B1参照 C1参照 D1参照

使い方例

「=( A$12=”完了”)」をカスタム数式としています。

AND, OR

ANDは「xxxxxかつyyyyy」の複合検索、ORは「xxxxxまたはyyyyy」の複合検索ができます。ANDとORの使い方は一緒なので、ANDで解説します。

=AND((条件1),(条件2))

条件1と条件2に適当な条件を入力します(「$E2="完了"」など)。ANDは両方の条件を満たしたもののみ、ORは少なくとも片方の条件を満たしたものに書式を適用します。

使い方例

「=OR(($E2=”完了”),($E2=”作業中”))」とカスタム数式に入力 完了と作業中に反応します

豊富な関数

カスタム数式は関数も使えます。ただし、使える関数は答えがTRUEかFALSEになるものだけです。答えがTRUEとFALSE以外になる場合は、うまく調整しましょう。以降で解説するWEEKDAY関数も、答えが数字なので調整の参考になるでしょう。

WEEKDAY()

WEEKDAY関数は()内の日付が何曜日か判断して数字を返します。

曜日 数字
日曜日 1
月曜日 2
火曜日 3
水曜日 4
木曜日 5
金曜日 6
土曜日 7

ただし、WEEKDAY関数をこのまま使ってもカスタム数式は反応しません。カスタム数式はTRUEかFALSEにしか反応しません。そのため下の例のように、「=数字」を使いましょう。

使い方例

「=(WEEKDAY(A20)=7)」をカスタム数式に入力しています。

「=7」は土曜日なので、青文字にしました。書式設定のスタイルを変更すれば、文字色を青にしたり、背景色を青にしたりできます。

同様に日曜日の場合も入力します。

日曜日も同様です。ポイントは「=数字」の形にする点です。

条件付き書式が大活躍

条件付き書式を網羅する勢いで解説しました。

条件付き書式を活かせれば、自動で背景色や書体を変更できます。

他のシステムと連携すれば、入力の手間を大幅に省けるので、設定に挑戦してみましょう。