【条件付き書式】アイコンセット応用編！各アイコンの境目となる数値を固定にする方法 #Excel

先日の記事で条件付き書式のアイコンセットの設定方法について解説しましたが、今回は応用編について解説していきます。

各アイコンの境目の数値は変動してしまう

先日の記事でも解説しましたが、条件付き書式のアイコンセットは書式設定しているセル範囲内の最大値・最小値に応じて、自動的に各アイコンの境目は変動します。

先日の記事の該当部分を引用すると、以下の部分ですね。

各アイコンの境目となる数値を計算する場合の数式は、「(最大値-最小値）×n%+最小値」となり、n%の部分に「値」に設定された数値を代入して計算すればOKですよ。

イメージしにくい方のために、実際にどういうことか、以下をご覧ください。

アイコンセット固定化①

上図は1つのセルの値を変更したことで、アイコンセットを設定している範囲内の最小値が変動したため、アイコンの境目が変わり、値を変更していないセルのアイコンも変わってしまいましたね。

Excelの親切設計だとは思うものの、クライアントや上司へ報告する資料で活用している場合、アイコンの基準が変わると説明が大変ですね(;^_^A

私もクライアント向けの報告書で活用していたので、このアイコンを何とかして固定化したいと思っていろいろ頑張ってみました。

そのときに工夫したのが、以下の方法です。

解決方法

次の3STEPで対応できます。では、順番に見て行きましょうね(*^-^*)

STEP1：各アイコンの基準値を表にまとめる

アイコンセット固定化②

まず、上図のようにアイコンセットを設定したい表とは別に、各アイコンの基準値を表にまとめましょう。

以下の3ポイントを満たす表を準備すればOKです。

  1. ユーザーへアイコンの基準を説明する文章
  2. 各アイコンの境目になる数値（VLOOKUP関数の「検索値」を検索する範囲）
  3. 各アイコンの基準値（VLOOKUP関数で返したい値）

1は、各アイコンが、どの数値の場合に割り振られるか、わかるようにしておくと説明しやすいです。

2は、VLOOKUP関数の「検索値」を探す範囲ですね。
VLOOKUP関数は近似値検索を行うため、値の並びは昇順にしましょう。
レコード数はアイコンの数と一緒にしてください。

3は、VLOOKUP関数で返す値です。この値を元にアイコンの種類が変わりますのでいわば「基準値」ですね。
これはレコードの上から通し番号を振れば良いです。

STEP2：アイコンセットを設定する各セルでアイコンの基準値を取得

アイコンセット固定化③

続いて、アイコンセットを設定する各セルに対し、それぞれSTEP1で用意した表の基準値を取得しましょう。

この場合、VLOOKUP関数を使うと楽ちんですよ(*^-^*)

今回は、上図のように「前月比」を元にアイコンを設定したいので、「前月比」の左横に1列設け、そこにVLOOKUP関数を追加しました。

VLOOKUP関数の引数「検索値」は各行の「前月比」を指定、引数「範囲」はSTEP1で用意した表の2・3の部分を選択しましょう。今回なら「J3:K7」ですね。

引数「列番号」は選択した「範囲」の2列目が返したいので"2"を入力、引数「検索方法」は近似値検索を行うために"1"を入力すればOKです。

STEP3：アイコンセットを書式設定

あとは、アイコンセットの条件付き書式を設定すれば完了です。

範囲を選択

アイコンセット固定化④

まず、条件付き書式を設定するセル範囲を選択しましょう。

STEP1の3の部分と、STEP2でVLOOKUP関数を追加した範囲を両方選択します。

なお、離れたセル範囲を選択する場合は、「Ctrl」キーを押しながらマウスでもう一方のセル範囲を選択すればOKですよ(*^-^*)

条件付き書式-アイコンセットを設定

条件付き書式のアイコンセットを設定します。

設定方法の詳細は先日の記事をご参照ください。

【条件付き書式】アイコンセットを設定する方法 #Excel | Excelを制する者は人生を制す　～No Excel No Life～

今回追加で設定してほしい部分だけ補足しますので、下図をご覧ください。

アイコンセット固定化⑤

「書式ルールの設定」ダイアログ画面の「アイコンのみ表示」にチェックを入れてください。

あとはワークシート上に戻りましょう。

設定内容を確認

アイコンセット固定化⑥

ワークシートに戻ると、1～5のアイコンの基準値の部分がすべてアイコンのみ表示に変更されました！

これで見栄えが良くなりましたね。

問題なければこれで作業完了です(*^-^*)

範囲内の最大値・最小値が変わっても、アイコンの基準は固定される

これで本当にアイコンセットを設定したセル範囲内の最大値・最小値が変わっても、アイコンの基準が固定されるか試してみましょう。

アイコンセット固定化⑦

上図のように、1セルのみに最小値部分を変更しましたが、その部分以外のアイコンは変わってませんね。

このように、アイコンの基準を固定したい場合は有効なテクニックだと確認できましたね(*^-^*)

当サイト内関連記事

【条件付き書式】アイコンセットを設定する方法 #Excel | Excelを制する者は人生を制す　～No Excel No Life～

VLOOKUP関数の引数「検索方法」のTRUEの使いどころ #Excel #関数 | Excelを制する者は人生を制す　～No Excel No Life～

まとめ

アイコンセットはかなり表の見栄えを良くできますし、数値の意味合いも感覚的に分かりやすくなるので、有効活用すると良いですね。

ただし、第三者へ伝えるための資料には、相手への余計な混乱や誤解を避けることができるためにも、アイコンに限らず今回のように基準を明確にしておくことがおすすめです。

ご参考になれば幸いですm(__)m