同じ値が続く行を1つのグループとして、グループが変わるたびに背景色を交互にします。
前の行との比較で補助番号を作ると、同じ値が後から再登場する場合も区別できます。
ZIPを展開してXLSXをGoogle Sheetsへ読み込み、対象の記事IDの数式TXTを使って練習します。
実務のシートへ入れる前にシートを複製し、既存の式やルールを控えてください。
入力と補助番号を用意する
| セル | 入力 |
|---|---|
| A1／B1／C1 | グループ／補助番号／メモ |
| A2:A5 | りんご、りんご、バナナ、りんご |
| A6 | 空欄 |
| A7:A9 | りんご、A*、A? |
A2からデータが始まる例です。
B2へ最初の番号を入れます。
最初の行が空欄なら0にします。
=IF(A2="",0,1)B3へ次の式を入れ、B9までコピーします。
空白行では前の番号を維持し、空白の後の値は新しいグループにします。
=IF(A3="",B2,B2+IF(A3=A2,0,1))2つの条件付き書式を設定する
A2:C9を選び、表示形式から条件付き書式を開きます。
書式ルールをカスタム数式にして、奇数グループの式へ淡い緑を設定します。
=AND($A2<>"",MOD($B2,2)=1)同じ適用範囲にもう1つルールを追加し、偶数グループの式へ淡い青を設定します。
空白行を除く条件は両方に入れます。
=AND($A2<>"",MOD($B2,2)=0)$Aと$Bは判定する列を固定し、行番号は変わるようにします。
適用範囲を3行目から始める場合は、ルールの基準行も3にそろえます。
確認結果
| 入力 | Google Sheetsの結果 |
|---|---|
| 2・3行目：りんご | 番号1／緑 |
| 4行目：バナナ | 番号2／青 |
| 5行目：再登場のりんご | 番号3／緑 |
| 6行目：空白 | 番号3を維持／色なし |
| 7行目：空白後のりんご | 番号4／青 |
| 8行目：A* | 番号5／緑 |
| 9行目：A? | 番号6／青 |
A3を新グループへ変えると、その後の番号も変わります。
A3をりんごへ戻すと最初の番号と色へ戻ることを確認しました。
A*とA?は、前行との比較では記号を含む文字列として扱います。
補助列を使わない短い式の適用範囲
同じ値をまとめて並べ替え、空白やワイルドカード記号がないデータでは、従来の短い式も使えます。
練習表のD2:D7がこの並びの例です。
次の式をF2へ入れてF7までコピーすると、奇数グループの判定を確認できます。
色を付ける場合はD2:E7を適用範囲にしてカスタム数式へ入れます。
=AND(D2<>"",MOD(ROUND(SUMPRODUCT(1/COUNTIF(D$2:D2,D$2:D2)),0),2)<>0)この式は、先頭から現れる値の種類を数える方法です。
りんご、バナナ、りんごのような再登場を新しいグループとして数える用途では、本文の補助番号方式を使います。
練習表のD～F列では、空白のない並びで奇数グループの判定がTRUE、TRUE、FALSE、FALSE、TRUE、TRUEとなることを確認しました。
色が合わないときと戻し方
まず補助番号が期待どおりかを確認し、次に適用範囲と基準行、ほかのルールとの重なりを調べます。
値をまとめたい場合は表全体を並べ替え、1列だけを並べ替えて対応するデータを崩さないようにします。
元へ戻すときは今回追加した2つのルールだけを外し、補助番号を変更前のコピーへ戻します。
入力のA列や既存のルールを残してください。
条件付き書式の共通の切り分けは条件付き書式が反映されない場合を参照できます。
公式仕様・参考資料
練習ファイルの使い方
Google用の式は各記事IDのTXTにあります。
XLSXの互換用の式とGoogle用の式の違いはZIPのREADMEで確認できます。
本文の入力を使い、出力先と戻し方を確認してから実務のシートへ適用します。
