条件付き書式が設定されているExcelシートを利用されたことがあるでしょうか?
条件付き書式を使うことで、マクロを使用していない普通のエクセルファイル(以下xlsx形式)でも一見マクロを使用しているかのようなユーザインタフェースを作ることができます。
ただし、条件付き書式が設定されているExcelシートには、少し注意をしなければならない点がありますので、サンプルを使いながらご説明しておきたく存じます。
条件付き書式でできることは?
xlsx形式の普通のエクセルファイルでも数式や関数(複雑な条件に基づき計算する機能)を設定することで、動的な画面の動きを提供することができます。
ただし、数式や関数が値を反映できるのは数式や関数が設定されている1つのセルに限られます。
つまり数式や関数は「自分以外のセルの値を変更すること」は許されていません。
一方マクロを使用すると、そのような縛りがなく、どこのセルに対してもプログラミングすることで値を変更することはできます。
ただしマクロを含む形式のエクセルファイルを動かすためには、セキュリティ面や端末環境面での対応が必要になる部分があります。
方やxlsx形式のエクセルファイルについては、そのようなわずらわしさはありません。
条件付き書式でできることは、指定したセル範囲で、対象となる個々のセルが、設定した条件に合致するか?を判断し(この判断は数式や関数を使って複雑な条件分岐や、複数のセルの値にもとづき計算をしてうえで導き出すことができます)、そのセルの書式を動的に変更できるという機能です。
マクロを使用しているかのようなインタラクティブな動きは、この動的に変更できる機能を利用することで実現することができます。
動的な動きのサンプル
下記ExcelシートはMicrosoftサポート「OneDrive から Web ページやブログに Excel ブックを埋め込む」を元に設定をしています。
また下記のサイトでも使用方法が詳しく解説されています。
なおExcelブックを埋め込み方式で表示させるにあたり、下記の点にご注意ください。
- このシートはExcel OnlineでOneDrive「https://onedrive.live.com」にアクセスをして表示します。
- iframeを使用していますので、iframeがご利用になれない環境の場合は、別途ファイルをダウンロードしてご確認ください。
- 埋め込まれたExcelシートでは枠番1～9と書かれた下のグレー色の枠線で囲まれた9つのセルに直接入力することができます。
枠番1～9と書かれた下のグレー色の枠線で囲まれた9つのセルに「1から9の数字」をダブらせながら入力していただくと、設定されている式と条件付き書式により、ダブっている数字にそれぞれ違う色が付き、どこにダブっている数字があるか?を簡単に見つけることができます。
なお、ご存じであるように条件付き書式には既存で「一意の値または重複する値だけを書式設定」するルールが提供されています。
この既存のルールと上記サンプルとの違いは、重複する値が複数存在した場合に、既存のルールではダブりが複数組あってもすべて同じ色になってしまいますが、上記サンプルでは重複する値ごとに別の色で付けることができる点です。
なおセルに設定している数式の内容につきましては、横に長くなっていますが下記Excelシートをご参照ください。
※●の部分は本来の数式では「=」になります。
また設定している条件付き書式につきましては、画像になりますが下記をご参照ください。
各色(オレンジ・ゴールド・緑・青)に対するルールはB3セルを選択した状態で編集をします。その後、それぞれ色に対して適用先をセル範囲で設定します。今回はすべて同じセル範囲になります。(正確にはセルごとに指定するべきですが、適用先が管理し難くなるのでセル範囲にしています。)
なお一番下の黄色のルールにつきましては、後段でご説明いたします。
IF文の設定で
オレンジはB7
ゴールドはB8
緑はB9
青はB10
の各セルに1が入っている場合、条件がTRUEとなり書式が設定されます。
条件付き書式設定で気を付けなればならない点－その1.順序
先ほどのサンプルに設定されている条件付き書式では、黄色の色のルールは一番下位に設定されていました。
この設定を変更してみたいと思います。
条件付き書式ルールの管理で、黄色のルールを上位から3つ目に移動させたExcelシートを下記に埋め込み方式で掲載しています。
なお、黄色の書式ルールの設定は、つぎのようになります。
黄色のルールを選択してから、左図の赤枠にある上下移動ボタンで上位から3つの目の位置に移動し、適用ボタンをクリックします。
黄色の書式ルールはB3セルを選択した状態でホームタブの条件付き書式→新しいルールで表示される左図ウィンドウで、「一意の値または重複する値だけを書式設定」を選び、左図下段の「すべての値を書式設定」のプルダウンメニューで「重複」を選択し、書式から背景色を黄色に設定しOKボタンをクリックしています。
その後(上図参照)適用先を「=$B$3:$R$3」に設定します。
お試しいただくと分かるように、ダブり2組目までは黄色の背景色となり、3組目以降は、緑、青で表示されます。
このことから、次が最初の「ご注意いただきたい点」になります。
条件付き書式の設定ではルールの並び順を気にする必要がありますので、多くの条件付き書式を設定する必要がある場合にはご注意ください。
条件付き書式設定で気を付けなればならない点－その2.貼付け
その1の並び順よりも、むしろ今回ご説明するセル書式の貼付けの方が問題になり易いポイントです。
その1で使用したExcelシートで、B3からR3をセル範囲でコピーして、11行目のB11セルから貼り付けをしてみた結果が、次の埋め込み方式のExcelシートになります。
コピー貼り付けをする目的は、「枠線が囲われた書式をもう一つ 別に作るため」であると仮定します。
試しに貼り付けしたB11からR11の枠線のなかにダフリを含む1から9の数字を入力してみると、ダブり数字の背景がすべて黄色になります。
このワークシートの条件付き書式ルールの管理を見ると下記のようになっています。
- 条件付き書式がコピーされて数が最初と比べて2倍になっています。
- B11～R11は増幅されたルール
- B3～R3が最初のルール
- なお順番ですが、後から張り付けたB11～R11のルールが、最初のルールよりも上位になることにも注意が必要です。
なお背景が黄色くなったのは、コピーされた条件付き書式で、条件として成立するのが黄色のルール以外になかったためです。
その他の条件が成立するためには、このシートで使われている一連の数式がすべてコピーされている必要があります。
書式貼り付けで、「条件付き書式を除く書式貼り付け」の機能ができれば、この問題は解消されるのですが、現時点(2019/3/14)ではエクセルにそのような機能はありません。
なお、条件付き書式だけをコピーする機能はエクセルのなかにあるのですが…
条件付き書式設定で気を付けなればならない点－その3.分断
ここで更にB11セルをコピーしてJ3セルに張り付けるとどうなるか?を試してみます。
張り付けた結果が次の埋め込み方式のExcelシートになりますが、B3～R3のセルにはサンプルで数字入力がしてあります。
※下段の赤枠のセルをコピーして、上段の赤枠のセルに張り付けをした後に、数字を入力しています。
見た目として、上段赤枠の2の背景色が、その他の2の背景色と異なることが分かります。
このような状態になると、一般論として、利用者の皆様からは「Excelが壊れた」とか「このシートの動きがおかしくなった」と言われてしまう事になります。
条件付き書式ルールの管理を見てみると次のようになっています。
J3セルに張り付けられた書式分が増幅されて最初の3倍に膨らんでいます。
注意が必要な点は、張り付けられたことにより、いままで適用先がB3～R3にまとまっていたのが、B3～I3とK3～R3の2つに分断されている事です。
なおこの分断はエクセルが自動で行います。
上段赤枠の2の背景色が、その他の2の背景色と異なることになった原因は、背景黄色のルールは適用先の範囲の中から重複する数字を見つけ出し設定しているため、適用先がJ3単独では背景黄色のルールは適応とはならず、その他の色の条件はJ3単独でも成立するため、ゴールドのルールが適応されて周りと異なる色になっています。
そもそもはひとつの条件付き書式が設定されたセルをコピーしているだけなのですが、現時点のExcelの機能では、周りと同じ条件であることを認識することはできず分断されてしまうことで、見た目として残念な結果を引き起こしています。
将来AI化が進んで行けば、この問題は解決されるものと考えます。
注意点のまとめ
いくつかの注意点をご紹介して来ましたが、まとめとしては下記になります。
以上、条件付き書式が設定されている場合の注意点を述べてきましたが、条件付き書式を活用することで日常業務の一部ではありますが、省力化したり効率化したりできる部分があることと存じます。
是非、リスクを恐れずに条件付き書式をご活用いただければ幸いです。
最後までご一読いただきありがとうございました。
