【条件付き書式】Googleスプレッドシートで 交互の背景色を 自作するのは 間違っているのだろうか
Googleスプレッドシートの 条件付き書式 と 交互の背景色 という機能について書いてみたいと思います。
後半はカスタム数式を使って、↑このような グループ毎に交互の背景色を設定する。という応用テクニックにも触れます。
「交互の背景色」くらい知ってるよ！って人にも 学びがある内容かと思いますので、最後まで読んでみてください。（で、ついでにスキボタンも押しちゃってください）
ちなみにタイトルの「Googleスプレッドシートで 交互の背景色を 自作するのは 間違っているのだろうか」の元ネタは コチラ
先週は「GASで作成したWebアプリ（Webサイト）でGoogleドライブ内の画像が表示されなくなった問題を解決する」 というネタで noteを書きました。（どっちもタイトル長いな）
Googleスプレッドシート 交互の背景色とは？
まずは、この交互の背景色という機能について書いていきましょう。
Googleスプレッドシートには自動で交互に背景色を設定する機能がある
スプレッドシートにおける表の作成で、見栄えをよくしたい、もしくは選択している行を誤認しないように可読性をあげたい、といった要望は結構あります。
初心者がやりがちですが、このように表にフルに格子線を入れてしまうと、みっちみちにデータが入った表がさらに見づらくなりますし、なんか野暮ったい感じになってしまいます。
また、目視で作業をしている場合など、今作業している行を見失って前後の行のデータと誤認したりといったミスも発生しやすくなります。
これを回避策として、以前noteで GASを使って選択セルをハイライト表示する方法を紹介しました。
しかし、もう少し簡単な方法があります。
それが交互の背景色という機能です。
交互の背景色を設定したい範囲を選択してからメニューから
表示形式 ＞ 交互の背景色
を選択すると、
サイドバーに 交互の背景色の設定が表示されます。
ここで 適用範囲の変更や ヘッダー、フッターの設定、カラースタイルの選択が出来ます。
色のスタイルはデフォルト（薄い色合いと無色の交互）から選んでもよいですし、
＋ボタンから 自分の好きな色の組み合わせでカスタムタイルを作ることもできます。
選択が終わったら最後に 「完了」ボタンを押すと セルに交互の背景色が適用されます。
この自作のカスタムスタイルは、どこかで使用していれば保持されますが、作成しても使われていない（一度適用したけど削除した）場合は保持されません。
使ってない 交互の背景色のカスタムスタイルは、ブラウザを更新（スプレッドシートを開きなおし）すると リセット（削除）されます。
ちなみに フッターは こんな感じで 一番下に集計行がある時に使えます。
このように交互の背景色機能で、一つおき（交互に）行が色づけされることで、表のデザイン性が向上し、目視作業の際に 前後の行のデータを誤認することが防げます。
もし周りに 手動で １行１行選択して 交互に背景色を付けてる人がいたら・・・、とても無駄なのでこの機能を教えてあげましょう。
早々にタイトルフラグ回収ですが、
「Googleスプレッドシートで 交互の背景色を 自作するのは 間違っているのだろうか」
→ 解： 然り。手作業で自作は 間違ってます！ 交互の背景色という機能があるので、そちらを使うべきです。
となりますね。 しかし本当にそうでしょうか？
Googleスプレッドシートの 交互の背景色は Excelのテーブル機能とは別モノである
このGoogleスプレッドシートの 「交互の背景色」機能ですが、なにも知らないExcelユーザーがこれを見て
「Googleスプレッドシートにもテーブル機能があるの！？」
と勘違いすることがあります。
Excelのテーブル機能と Googleスプレッドシートの 交互の背景色は、まったく別モノです。
Excelのテーブル機能については、いくらでも詳しく解説しているサイトやブログがあるのでここでの説明は割愛しますが、
Excelのテーブルは単に見栄えを変えるものではありません。
構造化参照 や 数式も含めた自動拡張、集計行の挿入など、非常に多機能で近代エクセルには欠かせない機能と言えます。
パワークエリを利用する際もテーブル化は欠かせません。
一方 Googleスプレッドシートの 交互の背景色は、ぶっちゃけ 完全に見栄えだけの為の機能と言えますｗ
交互の背景色の特徴と注意点
交互の背景色は仕組みわかってなくても 簡単に使える便利な機能ですが、その特徴と注意点をしっかり理解することで、無駄に悩んだり余計な作業で時間をとられることなく より快適に活用できます。
特徴と注意点を見ていきましょう。
1.背景色しか設定できない
この機能は、その名の通り 交互の「背景色」しか設定できません。
例えば上の画像のように タイトル行 に濃いめの背景色を設定した場合、文字色が黒だと少しみづらいのですが、これを白文字にしたり 太字にしたりといった、文字の装飾は 交互の背景色の機能では設定することは出来ません。
交互の背景色設定後に 手動で文字の装飾を変えるしかないです。
2.部分的な書式のクリアはできない
この「交互の背景色」という書式設定は少し特殊で、設定した範囲の一部分（たとえば この行だけ）を 書式クリア といったことが出来ません。
上のgif動画でも 部分選択した際の「書式をクリア」は、文字の装飾はリセットされるものの 背景色に変化が無いのがわかります。
唯一、表全体（交互の背景色の設定範囲全て）を選択した場合のみ 「書式をクリア」で 交互の背景色が解除されます。
交互の背景色の 設定画面から 解除する方法もあります。
交互の背景色が設定されているセル（のうちどれか）を選択して、
表示形式 ＞ 交互の背景色
と進んで、適用されている交互の背景色の設定画面を開き、
その一番下にある 「交互の背景色を削除」をクリックします。
ちょっと手数が多いのが難点ですね。
当然ですが、手動で色付けしたセルや 条件付き書式であれば、「書式をクリア」で 部分的な書式のリセットが可能です。
背景に色がついているセルがあって、どうしてもセルの色を消すことができない。条件付き書式も見たが設定されていない。
こんな時は 交互の背景色が設定されているのかもしれません。確認してみましょう。
3.背景色の優先順位は 交互の背景色が 一番低い
そう簡単に解除できない「交互の背景色」ですが、背景色としての優先度は一番低かったりします。
優先度は
(優先度 高) 条件付き書式 ＞手動による 背景色設定 ＞交互の背景色 (低)
このようになっています。
だから、先ほど交互の背景色を部分的にリセットは出来ないと書きましたが、手動で「背景色 白」で塗りつぶすという方法で、見た目上は 背景色を削除したようにすることは可能です。
4.交互の背景色の範囲は自動拡張する
交互の背景色を適用したセル範囲は、行を挿入したり、入れ替えたり、行削除をしても 背景色が 交互であることは 保持されます。
変更に連動して 自動で背景色がつく行も変わる仕組みとなっています。
さらにこれだけではなくて、
このように交互の背景色の適用範囲に隣接している行 に入力をした場合、自動で交互の背景色の範囲が拡張されます。
ココだけ見ると Excelのテーブル機能と似ているのですが、もちろん先述の通り テーブル機能ではないので 追加入力された 7行目のD列（合計）に 自動で 個数×単価 の式は補完されません。
あくまでも背景色だけの拡張です。
当然、列方向も入力に応じて自動拡張されるんですが、これが便利に感じる時もあれば、不要で邪魔くさいなーって時もあります。（横に備考かきたいけど色付けはしたくない）
交互の背景色の範囲を後で修正するのは面倒なので、そんな時は
このように隣接する列ではなく、表（交互の背景色の範囲）から1列空けた次の列に記入してから、列削除する方法が有効です。
この方法で隣接させた場合は 交互の背景色の拡張は止まり、このE列の他のセルに入力をしても 交互の背景色が適用されることはありません。
この方法は 行方向の拡張ストップにも使えるので、覚えておくと便利だったりします。
以上が、交互の背景色の 特徴と注意点となります。
交互の背景色を条件付き書式で自作する
Googleスプレッドシートの 交互の背景色という機能ですが、確か以前はなくて機能として追加されたのは 2016年頃だったと記憶しています。
公式のリリース記事は見つかりませんでしたが
↑ コチラのサイトで 2016年10月5日 の記事に、最近追加された新機能として 交互の背景色が紹介されています。
では「交互の背景色」という機能が実装される以前は、行を交互に色付けしたい場合はどのように対応していたか？
その 古（いにしへ）の手法が、条件付き書式を使った方法です。
今さら わざわざ
交互の背景色の自作方法を学ぶ意味があるの？
と思うかもしれませんが、意味があるかないか？（間違っているのだろうか？）は後で明らかになります。
とりあえず、一緒にお題にチャレンジしてみましょう。
Q1. 条件付き書式で 1行飛ばしで行に色を付けたい
表の範囲が A1:C22 だった場合、上のように 2行目から 1行おきに薄いグレーで色付けして 交互の背景色のようにしたい。
交互の背景色機能を使わない場合、条件付き書式をどのように設定したらよいでしょうか？
これは割と簡単ですね。チャレンジしてみましょう！
すぐに回答いきます。
↓↓↓
A1. 条件付き書式で 1行飛ばしで行に色を付ける
正解はこちら。
=ISEVEN(ROW())
条件付き書式を設定する際、少し複雑な条件設定をする場合はカスタム数式を使います。
そして カスタム数式を使う際は
■条件付き書式で カスタム数式を作成するポイント
・その範囲の開始セル（左上）で動く式を作る （範囲内で自動でスピる）
・式は TRUE,FALSEを返す形にする （TRUEの時に書式適用）
・条件セルではなく 書式設定するセル（色付けセル）の視点で式を作る
・条件セルと色付けセルがイコールでない場合は 絶対参照を利用
・一部の関数は 動かないので注意
・エラーが見つけにくので複雑な式はセルで作成・確認してからコピペ
これらがポイントとなります。（過去noteにも登場しました）
お題の式で 色付け対象となる 行は 2,4,6,8… 偶数行ですね。
つまり 行番号 ROW() で取得した数値が、偶数である時に 条件付き書式を適用すれば良いわけです。
対象の数値が偶数かどうかを判別する（偶数の時に TRUEを返す）関数は ISEVEN関数です。
別解として MOD関数を使う方法もあります。
この後の問題でも登場しますが、MODを使った方が交互（1行毎）以外のパターンにも応用がききます。
逆に、色を付ける行を 1,3,5,7… と奇数行にしたい場合は、奇数の時にTRUEを返す ISODD関数を使いましょう。
ちなみに MOD関数だと 奇数の場合は 2で割って必ず1余る、つまり
MOD(奇数,2) ・・・ 必ず1となる
となり、さらに 0以外の数値は TRUEとして扱われるので
これだけで良いです。
行番号を奇数・偶数 判定する方法で、交互の背景色が設定できました。
Q2.表をどこに動かしても タイトル行（1行目）を濃いめのグレー、データ行を交互に薄いグレーで色付けしたい
それでは少し応用です。
上の表をどこに動かしても 書式を維持するような 条件付き書式のカスタム数式は どのような式を組めばよいでしょうか？
タイトル行の条件付き書式、データ行用の交互の条件付き書式、それぞれ設定が必要です。
ちなみに
タイトル行 =ROW()=1
データ行 =ISODD(ROW())
このように設定してしまうと、表をドラック＆ドロップで動かした時に タイトル行の色付けは失われてしまい、データ行の交互の色付けが逆になってしまう（ことがある）ので、これは✖です。
少し難しくなってきましたかね？ チャレンジしてみましょう！
↓ここから回答です。
A2.表をどこに動かしても タイトル行（1行目）を濃いめのグレー、データ行を交互に薄いグレーで色付けする
タイトル行 =ROW()-ROW($A$1)=0
データ行 =ISEVEN(ROW()-ROW($A$1))
これが回答です。
このように設定することで、表を動かした場合でも タイトル行の色付け、データ行の交互の色付けが保持され、条件付き書式で「交互の書式設定」を再現することが出来ます。
ここで注意すべき点が2つあります。
1つは絶対参照の利用、もう1つは 条件の順番です。
ROW($A$1) はタイトル行の行番号ですが、ここを ROW(A1)としてしまうと 条件付き書式の適用セルに合わせて 条件の式が相対参照されてしまい、正しく機能しません。
条件付き書式のカスタム数式で 参照を動かしたくないセルは $を付けて 絶対参照としましょう。
通常、セル内の計算式であれば F4キーで 絶対参照の切り替えが出来るのですが、残念ながら カスタム数式内では F4キーが効きません。
ここは 手動で $をつけると割り切りしょう。
Googleスプレッドシートの 絶対参照、相対参照は 基本的にExcelと一緒で、Excelの解説サイトの方が丁寧なものが多いので、そちらを参考にしましょう。
そしてもう1点が 条件付き書式の順番です。
Excelと違って Googleスプレッドシートの 条件付き書式は 1つしか適用されず 上に設定したものが優先度が高い（上の条件が適用されたら それで終わり）という仕様になっています。
もし タイトル行の条件がデータ行の条件より下だと、データ行条件が優先されタイトル行にも適用されてしまいます。
Googleスプレッドシートの 条件付き書式のルール は、順番に注意しましょう。
交互の背景色が自作できれば 列方向にも適用できる
条件付き書式で 交互の背景色を自作できることはわかりましたが、
交互の背景色機能を使えばいいんだから 今さら必要なくね？
と思いますよね。
確かにそうなんですが、たとえば 上のように列方向を交互に色付けしたい といったケースでは活用できます。
「交互の背景色」の機能は、あくまでも 行に対する交互のみで、列へ適用ができません。
条件付き書式を使う方法を知っていれば、先ほどの式の ROW関数を COLUMN関数に置き換えて
タイトル列 =COLUMN()-COLUMN($A$1)=0
データ列 =ISEVEN(COLUMN()-COLUMN($A$1))
このようにカスタム数式を設定することで、列方向の交互の書式が実現できます。
応用編：特殊な交互の背景色に挑戦する
最後に 列方向の交互の背景色と同様に 通常の「交互の背景色」では対応できない 特殊な 交互の背景色 を作成するお題にチャレンジしてみましょう。
Q3. 〇行毎に交互に色付けをしたい(可変）
1つ目のお題は、さきほどの交互の背景色の応用例です。F3セルの数字に連動して、〇行毎に交互の背景色を B3:D24 の表に適用したい。
こんなお題です。もちろん 条件付き書式のカスタム数式を利用します。どのような式を組めばよいでしょうか？
前提として 条件付き書式の 適用範囲は B3:D24とし、タイトル行用の条件付き書式
こちらが既に設定されているものとします。
ハードルがあがってきましたね。考えてみましょう！
↓ここから回答です。
A3. 〇行毎に交互に色付けをする(可変）
回答です。
=LET(n,$F$3,MOD(ROW()-ROW($B$3)-1,n*2)<n)
なるべく汎用的な式をということで LET関数で F3 の絶対参照 $F$3を nと置いて処理をしています。
実際の計算箇所は
MOD(ROW()-ROW($B$3)-1,n*2)<n
ですね。少し解説をしておきましょう。
まず ROW()-ROW($B$3) の式で、タイトル行を 0として データ行を上から1,2,3と連番を振っています。
これを例えば 2行ずつ 背景色を交互に設定したい場合は、2行色付けして2行無色の繰り返しとなるので、合わせて 4行のセットを繰り返すと捉えます。
4を1セットとするので、
=MOD(ROW()-ROW($B$3),4)
としたいところですが、これだと
1,2,3,0 の繰り返しのうち 1,2の時だけ 色をつける形になり、条件を綺麗に式にするのが難しいです。
というわけで式を少し改良して
=MOD(ROW()-ROW($B$3)-1,4)
このように 割られる方の数を -1して 連番を 0スタートにすることで
MODの結果（余り）、 0,1,2,3 の繰り返しのうち 0,1だけ色をつける。つまり、
=MOD(ROW()-ROW($B$3)-1,4)<2
を満たす（TRUE）だけ色をつける という シンプルな条件式に出来ます。
ここに登場する 2は 〇行ずつの 〇 、つまり LETで用意した nですし、4は n*2と表すことができます。
よって
=LET(n,$F$3,MOD(ROW()-ROW($B$3)-1,n*2)<n)
という式になります。
表を 〇行毎に交互に色付けする汎用的な式、理解できましたでしょうか？
Q4. A列のグループ毎に交互に色付けをしたい
最後のお題は、冒頭に紹介した グループ毎に交互に背景色を設定、これをやってみましょう。
上のgif動画のように 間をあけて 同じグループの行が登場しても、とにかくグループ毎に 交互に 色あり・色なしをくりかえしたい、という要件です。
これは 条件付き書式の設定範囲を タイトル行を除いたデータ範囲 A2:C30としましょう。
だいぶ 難しくなってきましたが、出来そうでしょうか？ 自信のある人はチャレンジしてみましょう。
↓ここから回答です。
A4. A列のグループ毎に交互に色付けする
今回は順を追って 解説しながら回答に近づいていきましょう。
まず、思いつく方法が A列の一つ上の行と比較して 同じなら そのまま、違うなら TRUE,FALSEを切り替える という
=IF(A1=A2,D1,NOT(D1))
こんな式を使った方法です。
TRUE ⇔ FALSE の切り替えは、シート関数だと NOT関数を使います。
これは JavaScript の ビット否定みたいな感じですね。
ちなみに NOT関数を使って 0,1切り替えもできます。
しかし、この方法は 式が入っているD列の一つ上の結果を利用している為、通常の配列処理や 条件付き書式内での利用が出来ません。
では、D列を参照せずにどのように式を組めばよいか？
とりあえず Ａ列の一つ上と比較する方法は使えそうなので、それだけＤ列に出力してみましょう。
=A1<>A2
これを下にオートフィルすると、切り替わりのタイミングは TRUEになるものの、 同じ グループの2番目以降は FALSEとなってしまうので、このまま色分けには使えませんね。
では、TRUE、FALSEを数値にしてみましょう。
=(A1<>A2)*1
さらに E列に D列の その行までの累計を出してみましょう。
=SUM(D$2:D2)
ちょうど累計の結果がグループ毎になっており、累計が偶数の行が 背景色の対象行であることがわかりますね。
ちなみに 累計だからといって、SCANやARRAYFORMULAとSUMIFを組み合わせた複雑な一つの式で処理する必要はありません。
条件付き書式で使う カスタム数式は、自動で 先頭の式をオートフィル（下にコピペ）したような動きとなる為です。
ただし、ここで 同じ列の計算結果を使う式
=E1+D2
ではダメです。あくまでも D列の結果のみを使います。
その累計方法が =SUM(D$2:D2) です。
行の先頭（開始位置）だけ D$2 として絶対参照で2行目で固定、一方 終了位置の方は D2として 下のセルにフィルした際に D3,D4,D5と 相対して変動させることで、 その行までの累計 を計算することができます。
累計の式は SCAN関数登場時の note でも少し触れています。
とりあえず 切り替わり箇所を1とカウントした 配列を 累計した結果を利用すると グループ毎の交互の背景色が出来そう、ってことはわかりました。
しかし、まだ式が2つに分かれています。これを1つの式にしたいのですが、どうすればよいでしょうか？
ここで 使えるのが 式内で配列計算が出来る SUMPRODUCT関数です。
=SUMPRODUCT(A$1:A1<>A$2:A2)
中の動きを理解する為に、F15セルの
=SUMPRODUCT(A$1:A14<>A$2:A15)
を見てみましょう。
これは SUMPRODUCTの式内で
A1<>A2
A2<>A3
A3<>A4
・
・
・
A14<>A15
という比較処理をして TRUEを1、FALSEを 0として 合計を算出しています。
つまり
=ARRAYFORMULA((A$1:A15<>A$2:A16)*1)
の結果を SUMしているのと一緒です。
あとは 偶数の時だけ TRUEを返すように 冒頭で登場した ISEVEN関数を使って
=ISEVEN(SUMPRODUCT(A$1:A1<>A$2:A2))
このようにすれば良いですね。条件付き書式で 3列に対して使うんで 列方向も絶対参照で固定しておきましょう。
あと、データが入っていない行に色が付かないように A列が空白ではないという条件も追加しておきます。
こちらが グループ毎に交互の背景色を設定する カスタム数式の回答です。
=AND($A2<>"",ISEVEN(SUMPRODUCT($A$1:$A1<>$A$2:$A2)))
他の関数を使った式もあるかと思いますが、これが一番シンプルかなと。
実際にこちらの式を 条件付き書式のカスタム数式に入れて試してみましょう。
グループ毎に 交互の色分けが設定できました～！
改めて問う「 Googleスプレッドシートで 交互の背景色を 自作するのは 間違っているのだろうか？」
列の交互の背景色、そして特殊なケースでの交互の背景色を2つ紹介しました。
というわけで、真のタイトルフラグ回収としては
問：Googleスプレッドシートで 交互の背景色を 自作するのは 間違っているのだろうか
→ 解： 否。手作業ではなく 条件付き書式で自作することは間違いではありません。条件付き書式の理解が深まりますし、例外的な列方向への交互の背景色 や 単純な交互以外の変則パターンの背景色にも対応可能となります。
となります。
絶対参照やMODによる繰り返し対応、 累計処理なんかも登場して、条件付き書式のカスタム数式のトレーニングとしては、なかなか良い題材だったんじゃないでしょうか？
次回は、今回の流れで Googleスプレッドシートの他の 色関連ネタをやろうかなと思います。
この記事が気に入ったらサポートをしてみませんか？