「知っていると得するかもしれないExcelの癖」シリーズ第五回、テーマは「1行おきの色分けについて」になります。
Excel 2016バージョン1902を使用して確認しています。
行で色分けするやり方は、次の2通りがありますがメリット・デメリットについてまとめておきたく存じます。
- テーブルとして書式設定する。
- 条件付き書式とROW関数を使用して設定する。
テーブルとして書式設定する
ホームタブ→「スタイル」リボングループの「テーブルとして書式設定」からカラーバリエーションを選択すると「テーブルに変換するデータ範囲を指定してください」のメッセージウィンドウが表示されるので、セル範囲を指定してOKボタンをクリックすることで設定できます。
行でもセル範囲でも設定できます。
なお、今回のテーマはあくまでも「テーブルの書式設定」に関してに限定させていただき、テーブルの持つその他の機能につきましては別のテーマで取り上げたく存じます。
設定した「テーブルとして書式設定」を取り止めにする
少し基本的な操作の画面が続きますが、後段の説明で使用したくご容赦ください。
上記操作により下図のようなテーブルが設定されます。
なお、テーブルに行を挿入・削除した場合の1行おきの色分けは、テーブル書式として自動的に反映されるので、人手によるメンテナンスは一切不要です。
このテーブルをどこかクリックするとデザインタブとそのリボンが表示されます。
書式設定を取り止めにするには、
①
まずリボンの「テーブルスタイル」で「クイックスタイル」(左図参照)もしくはリボンがカラーバリエーション表示になっている時はスクロールバー右下の「―+▼」をクリックします。
「設定する」時のカラーバリエーションの画面と異なり、左図赤枠が「クリア」になっています。
クリアをクリックするとテープに設定されていた書式がすべて取り除かれます。
ここで注意するポイントは、「テーブルとして書式設定」の書式は一般的な「セルの書式設定」とは全く別物である点です。
試しにテーブルで背景色のついているセルのセル書式を見ても、塗りつぶしの背景色は設定されていません。
つまり、ここでクリアされるのはテーブルの書式だけで、例えばテーブルに条件付き書式を設定してあったとしても、条件付き書式には一切影響を与えずにテーブルの書式を削除することができます。
もしも間違えて先に②の「範囲に変換」を行ってしまうと、①のクリアの機能は呼び出すことができません。
さらに②を実施した時点で、テーブルの書式がセルの書式に引き渡されてしまうので、書式を戻すためには個々のセルもしくはセル範囲で書式を変更して行く必要があります。
なおテーブル設定された範囲に含まれる行に、データも書式も設定されていないのであれば、テーブル全体を含む行削除をすることでテーブル書式を取り除くことはできます。
条件付き書式とROW関数を使った場合
設定する条件は、奇数行に背景色を付ける場合は左図のようにISODD関数を使用します。
偶数行にする場合は次の数式のようにISEVEN関数を使用します。
=ISEVEN(ROW())
なおこのルールの適用先ですが、シート全体であれば、行範囲として下記の設定をします。
=$1:$1048576
この条件付き書式を設定したセル範囲、行範囲で行の挿入・削除をしても色分けは自動的に反映されて、人手によるメンテナンスは一切不要です。
全行色分けした時の書式設定と動作環境面での両者の比較
行の挿入・削除には両者とも自動反映されます。
テーブルが不利なところ–その1.動作環境
ただし全行となるとテーブルは動作環境面で圧倒的に不利になります。
設定しようとした段階で、下記のメッセージが表示されます。
このまま設定するとファイルサイズは数百KBになり、開く時にもそれなりの時間がかかります。
またメモリーの消費量も大きいために警告メッセージが表示される場合があります。
テーブルが不利なところ–その2.見出し行が必須
- テーブルには最初の行は見出し行になります。
- 位置を変えることはできません。
- 無ければ自動挿入されます。
- 行全体を非表示にすることはできますが、テーブル内をクリックすると枠外の列番号に項目名が表示されます。
テーブルが不利なところ–その3.テーブル範囲は「名前の管理」される
名前の管理がされるため、テーブルを持つシートをブック内でシートコピーすると、その分名前が増えて行きます。
左図ではsheet3をコピーしてsheet4を作ると、「テーブル1」の名前が「テーブル2」にコピーされ、登録されている名前の数が増えて行きます。
条件付き書式が不利なところ
例えば仮にB2セルをホームタブ→「編集」リボングループのクリアで、「すべてクリア」した場合(左図)、シートの条件付き書式の適用先は次のようになります。
=$1:$2,$4:$1048576,$A$3,$C$3:$XFD$3
まとめ
上記の結果だけを見ると、「テーブルとして書式設定」する方が全然ぶんが悪いように思われるかもしれませんが、全行への色分け設定では、条件付き書式の不利な点が見えてこないだけなのです。
一部のセル範囲に設定している場合は、範囲外のセルの値をコピペしたり、範囲内のセルを範囲外にコピペしたりすることによって、実は目に見えていないところで影響が出ています。
- 条件付き書式が設定されたセル範囲に他から単純にコピペすると、「条件付き書式が不利なところ」と同様なことが起こります。
- 条件付き書式が設定されたセルを単純にセル範囲外にコピペすると、貼り付け先のセルにも条件付き書式が設定されてしまいます。
一方テーブルの場合はテーブル内のセルをテーブル外に単純にコピペするとテーブルの書式が張り付けられてしまいますが、あくまで目に見える範囲での話なので条件付き書式のような問題にはならない認識です。
ただしテーブルにつきましては、これ以外にもセルに数式が設定されている場合の有利な点・不利な点があります。
従いまして、それらと組合わせてどちらを選択した方が良いか?の判断をすべきであると考えます。
なお、数式の入ったテーブルの動きにつきまして、それなのボリュームがあるため、今回のテーマには含めず、別なテーマとしてお伝えしたく存じます。
最後までご一読いただきありがとうございました。
