【Excel】条件付き書式を数式で設定する方法!ExcelのMOD関数で1行おきに色を付ける手順

Excelで大量のデータを扱う際、特定の行や列に色を付けたい場面があります。特に、1行おきに背景色を設定すると、データの視認性が格段に向上します。しかし、手作業で色を付けるのは非効率的です。条件付き書式とMOD関数を組み合わせることで、この作業を自動化できます。

この記事では、Excelの条件付き書式でMOD関数を使い、1行おきに色を付ける具体的な手順を解説します。この設定をマスターすれば、データの見やすさを向上させ、作業効率を高めることが可能です。

MOD関数とROW関数で1行おきに色を付ける仕組み

Excelで1行おきに色を付けるには、条件付き書式機能とMOD関数、そしてROW関数を組み合わせます。MOD関数は、割り算の余りを求める関数です。ROW関数は、指定したセルの行番号を返します。この二つを組み合わせることで、行番号が偶数または奇数の場合に特定の条件を満たすように設定できます。

具体的には、ROW()関数で現在の行番号を取得し、それを2で割った余りが0(偶数)または1(奇数)になるかで判定します。条件付き書式では、この条件が真(TRUE)になった場合に指定した書式(例:セルの背景色)が適用されます。これにより、データ範囲の1行おきに自動で色が設定されるのです。

ExcelでMOD関数とROW関数を使い1行おきに条件付き書式を設定する手順

ここでは、Excelのシート上でMOD関数とROW関数を使って、1行おきに背景色を設定する具体的な手順を説明します。ここでは例として、A1セルからE10セルの範囲で、1行おきに背景色を薄いグレーに設定します。

1. 色を付けたいセル範囲を選択する
   Excelシート上で、色を付けたいセル範囲をマウスでドラッグして選択します。例:A1セルからE10セルまでを選択します。
2. 条件付き書式の設定ダイアログを開く
   Excelのリボンメニューから「ホーム」タブをクリックします。「スタイル」グループにある「条件付き書式」をクリックし、ドロップダウンメニューから「新しいルール」を選択します。
3. 書式ルールを作成する
   「新しい書式ルール」ダイアログボックスが表示されます。ここで、「書式ルール」の種類として「数式を使用して、書式設定するセルを決定」を選択します。
4. MOD関数とROW関数を入力する
   「次の数式を満たす場合に値を書式設定」という入力欄に、以下のいずれかの数式を入力します。

   ・偶数行に色を付ける場合(2行目、4行目…):
   =MOD(ROW(),2)=0

   ・奇数行に色を付ける場合(1行目、3行目…):
   =MOD(ROW(),2)=1

   ここでは、1行おきに色を付けるために、偶数行に色を付ける数式 `=MOD(ROW(),2)=0` を入力します。

5. 適用する書式を設定する
   数式入力欄の下にある「書式」ボタンをクリックします。「セルの書式設定」ダイアログボックスが表示されるので、「塗りつぶし」タブを選択し、適用したい背景色(例:薄いグレー)を選び、「OK」ボタンをクリックします。
6. 設定を適用する
   「新しい書式ルール」ダイアログボックスに戻るので、「OK」ボタンをクリックして設定を完了します。

これで、選択したセル範囲の1行おき(この例では偶数行)に指定した背景色が自動で設定されます。データが追加されたり削除されたりしても、書式は自動的に更新されます。

ExcelのMOD関数で1行おきに色を付ける際の注意点と応用

MOD関数とROW関数を使った1行おきの条件付き書式設定は非常に便利ですが、いくつか注意点と応用方法があります。これらの点を理解しておくと、より柔軟なデータ管理が可能になります。

特定の開始行から色を付けたい場合

例えば、ヘッダー行を除いた2行目から1行おきに色を付けたい場合など、開始行を指定したいことがあります。その場合は、ROW関数にオフセットを加えることで実現できます。

例えば、3行目から1行おきに色を付けたい場合は、数式を以下のように変更します。

• 3行目、5行目…に色を付ける場合:
  =MOD(ROW()-3,2)=0
• 4行目、6行目…に色を付ける場合:
  =MOD(ROW()-3,2)=1

この `-3` の部分を、色を付けたい開始行の「一つ前の行番号」にすることで、任意の開始行から1行おきの書式設定が可能です。例えば、5行目から開始したい場合は `ROW()-4` となります。

列にも適用したい場合

1行おきではなく、1列おきに色を付けたい場合もあります。その場合は、ROW関数をCOLUMN関数に置き換えることで実現できます。

例えば、A列、C列、E列…のように1列おきに色を付けたい場合は、数式を以下のように変更します。

• 偶数列に色を付ける場合(B列、D列…):
  =MOD(COLUMN(),2)=0
• 奇数列に色を付ける場合(A列、C列…):
  =MOD(COLUMN(),2)=1

さらに、行と列の両方で1行おきかつ1列おきの条件を組み合わせることも可能です。例えば、偶数行かつ奇数列に色を付けたい場合は、AND関数を使い `=AND(MOD(ROW(),2)=0, MOD(COLUMN(),2)=1)` のような数式を設定します。

条件付き書式が適用されない場合の確認事項

設定した条件付き書式が意図通りに適用されない場合、いくつかの原因が考えられます。

• 選択範囲の間違い: 条件付き書式を適用したいセル範囲が正しく選択されているか再確認してください。
• 数式の誤り: MOD関数やROW関数、またはそれらを組み合わせた数式にタイプミスがないか確認してください。特に、括弧の閉じ忘れや、 `=0` や `=1` の部分の間違いに注意が必要です。
• ルールの優先順位: 複数の条件付き書式が設定されている場合、ルールの適用順序によって意図しない結果になることがあります。「条件付き書式」メニューの「ルールの管理」から、ルールの優先順位を確認・変更できます。
• 絶対参照・相対参照: 使用する数式で、行番号や列番号を固定したい場合は絶対参照(例:`$A$1`)、相対的に変化させたい場合は相対参照(例:`A1`)を正しく使用しているか確認してください。ROW()やCOLUMN()関数は、範囲選択に応じて自動的に相対参照で機能しますが、他のセル参照を含む場合は注意が必要です。

MOD関数とROW関数以外で1行おきに色を付ける方法

MOD関数とROW関数を使う方法が一般的ですが、Excelのバージョンによっては他の機能や、やや異なるアプローチで1行おきに色を付けることも可能です。ここでは、代替手段をいくつか紹介します。

Excelテーブル機能を利用する

Excelの「テーブル」機能を使うと、データの追加や削除に追従する形で、自動的に1行おきにストライプ(縞模様)の背景色を設定できます。これは、条件付き書式よりも手軽に設定できる方法です。

1. データ範囲をテーブルに変換する
   色を付けたいデータ範囲を選択し、「挿入」タブの「テーブル」をクリックします。ダイアログで範囲を確認し、「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して「OK」をクリックします。
2. 縞模様のスタイルを適用する
   テーブルが作成されると、「テーブルデザイン」タブが表示されます。このタブの「テーブルスタイル」グループから、好みの縞模様のスタイルを選択します。多くの場合、デフォルトで1行おきの配色が適用されています。

この方法は、データが頻繁に追加・削除される場合に特に有効です。ただし、テーブル機能特有の挙動(数式が自動コピーされるなど)があるため、その点を理解して使用する必要があります。

Power Query を利用する

より高度なデータ整形や自動化を行いたい場合は、Power Queryを利用する方法もあります。Power Queryでは、インデックス列を追加し、そのインデックス番号に基づいて1行おきの条件を設定できます。

1. Power Queryエディターを開く
   色を付けたいデータ範囲を選択し、「データ」タブの「データの取得と変換」グループから「テーブルまたは範囲から」を選択してPower Queryエディターを開きます。
2. インデックス列を追加する
   「列の追加」タブを選択し、「インデックス列」から「0から」または「1から」を選択してインデックス列を追加します。
3. 条件列を追加する
   追加したインデックス列を選択し、「列の追加」タブの「標準」グループから「カスタム列」を選択します。新しい列名(例:「色付け条件」)を入力し、数式欄に `=Number.Mod([インデックス列名],2)` のように入力して「OK」をクリックします。
4. 条件に基づいて色を付ける
   追加した条件列のヘッダーにあるフィルターボタンをクリックし、条件(例:`=0`)で絞り込みます。絞り込んだ状態で、Power Queryエディターの「ホーム」タブにある「行の選択」から「選択した行」を選び、その行に色を付けるための列を追加するか、Power BIなどのBIツールに連携して色付け処理を行います。

Power Queryは、Excelの標準機能だけでは難しい複雑なデータ処理を可能にしますが、学習コストはやや高めです。大量のデータを定期的に処理する必要がある場合に検討すると良いでしょう。

まとめ

ExcelでMOD関数とROW関数を組み合わせた条件付き書式設定により、1行おきにセルに色を付ける方法を解説しました。この設定を行うことで、データの可読性が向上し、作業効率を高めることが可能です。

開始行の指定や列への適用、Excelテーブル機能の利用など、応用範囲も広いです。ぜひ、ご自身の業務に合わせて活用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】