【Excel】アイコンセットの設定方法|数値50・80の境界とパーセントの違い

Excelの条件付き書式「アイコンセット」は、数値を境界で分け、矢印や信号機などを添える機能です。点数表で「80点以上は達成、50点以上80点未満は確認、50点未満は要対応」と表示したい場合は、ルールの種類を「数値」にして境界を指定します。

選ぶだけで適用される既定の割合ルールと、固定の合格点は同じではありません。ここではWindows版Excelを中心に、実際の数値で設定し、境界に当たる50点・80点を確かめる手順を説明します。

アイコンは基準となる値で切り替わる

3つのアイコンを使うルールでは、主に2つの境界を指定します。大きな値をよい状態として扱うか、小さな値をよい状態として扱うかはデータの意味で決めます。点数なら高い値を達成として扱えても、未処理件数なら高い値は要対応です。アイコンを選ぶ前に、何を伝える欄なのかを決めてください。

まず「点数」という見出しの下に、0~100の数値だけを並べます。「80点」と単位を直接打つと文字列になるので、数値の80を入力し、単位は見出しへ書く方法が分かりやすい準備です。対象はB2:B7などデータ部分に限定し、表の見出しや合計は外します。

アイコンはセルの見た目を変えますが、元の数値を採点し直したり、データの意味を読み取って基準を決めたりするものではありません。全員が高得点なら全員に達成のアイコンが付く設定でよいのか、それとも集団内での位置を示したいのかで、次に選ぶ種類が変わります。

既定の「パーセント」と「パーセンタイル」を区別する

アイコンセットの「パーセント」は、人数を均等に分ける設定ではありません。境界は最小値+(最大値-最小値)×割合で求めます。3区分の既定例で33と67が設定されている場合、最小値20、最大値100なら、境界は46.4と73.6です。単に33点・67点になるわけでもありません。

たとえば20、21、22、23、24、100という6件では、46.4未満に5件、73.6以上に1件が入ります。各アイコンが2件ずつになるわけではなく、「上位33%の人に上向き矢印」という説明は当てはまりません。極端に大きな値を1件追加しただけで、既存のセルの分類が変わることもあります。

パーセンタイルも、同じ値が複数あれば各区分の件数が等しくなる保証はありません。毎月同じ合格基準を使う点数表なら、データの追加で境界が動かない「数値」が適しています。既定値は選んだセットや既存の設定で異なるため、「ルールの編集」で実際の種類と値を読みます。

50点・80点を境界にする設定手順

1. B2:B7に49、50、79、80、81、100を入力し、その範囲を選びます。
2. 「ホーム」→「条件付き書式」→「アイコンセット」から3方向の矢印を選びます。
3. 「条件付き書式」→「ルールの管理」を開き、該当するアイコンセットを選んで「ルールの編集」を押します。
4. 2つの境界の「種類」を両方とも「数値」に変更します。
5. 上の境界を80、下の境界を50にし、両方の比較記号を「>=」にします。
6. 適用先が=$B$2:$B$7になっているか確認して確定します。

この設定なら、上向き矢印は80以上、横向き矢印は50以上80未満、下向き矢印は50未満です。49は下、50と79は横、80・81・100は上になります。「>」を選ぶと境界の値を含まないので、80点ちょうどを達成にしたい場合は記号も見てください。

緑だけ数値、黄色はパーセントという混在設定もできるため、片方を変えただけで済ませないことが大切です。また、セルに表示された79.5が小数点以下の表示設定で80に丸められていても、判定は元の値に基づきます。見た目と判定が食い違うときは、小数点以下を表示して確かめます。

達成率や「少ないほどよい値」に使う場合

セルに80%と入力した達成率は、内部の数値では0.8です。達成率80%以上と50%以上で分けるなら、種類は「数値」、値は0.8と0.5にします。ここへ80と50を入れると、通常の達成率は境界に届きません。点数80と達成率80%を同じ列で混ぜないようにしてください。

残件数など少ないほうがよい列では、アイコンの順序を逆にする設定が使えます。ただし、逆にしても境界の比較方法は確認が必要です。たとえば境界が20以上、5以上なら、区分は「20以上」「5以上20未満」「5未満」のままです。「5以下を良好にしたい」という条件なら、5ちょうどの扱いも調整します。

実務の表へ広げる前に、別の小さな範囲で境界の直前・境界と同じ値・直後を並べて確認すると、解釈のずれを見つけられます。達成率なら0.49、0.5、0.79、0.8を試すと、0.5と0.8を含む側がはっきりします。確認用の行を本番の集計へ混ぜないよう、練習用シートを使うと管理しやすくなります。

アイコンが出ない・判定が違うときの調べ方

表示がないセルは、まず適用先の範囲内か確認します。行を足したときや別の場所からコピーしたときは、ルールが途中までになったり、複数に分かれたりすることがあります。「ルールの管理」の表示対象をこのワークシートに切り替え、対象範囲と重複するルールを調べてください。

次にセルが数値かを見ます。CSVなどから取り込んだ「80」は文字列として保存されていることがあります。必要なら空きセルの=ISNUMBER(B2)で確認し、FALSEなら元データの形式を調べます。元の列を一括変換する前に、コピーした数セルで数字が正しく扱われるか試しましょう。空白や数式エラーのセルにアイコンがない場合も、最低評価と読み替えてはいけません。

列幅が狭く、アイコンと数値が収まらない場合は列を広げます。「アイコンのみ表示」をオンにしていると数字は見えませんが、値はセルに残り、計算にも使われます。確認中はこの設定をオフにして、数値とアイコンを横に並べると誤りを見つけやすくなります。

元の数式エラーを0へ置き換えてアイコンを表示させる処理は、未計算を0点と誤読させる原因になります。まずエラーの原因を修正するか、「未入力」「計算不可」などの状態を別欄に示し、点数の判定と区別してください。

凡例を添えて配布し、不要なルールだけを戻す

矢印の意味は表の近くに「↑80点以上/→50点以上80点未満/↓50点未満」と書きます。上向き矢印は前月からの増加を意味する、と読む人もいるため、点数の区分であることが分かる見出しが必要です。色だけが違う信号機より形も違うセットを使うと、白黒でも区別しやすくなりますが、配布するPDFや印刷物でも見分けられるかを確認します。

設定を取り消す場合は「ルールの管理」で該当のアイコンセットだけを選び、「ルールの削除」で消します。セルの値は残ります。「ルールのクリア」でシート全体を指定すると、期限切れの色付けなど他の条件付き書式まで消えるため、対象を確認してから操作してください。

最後に、適用先・種類・境界の値・比較記号・凡例を照合します。50と80の扱いまで一致していれば、アイコンを見た人が同じ基準で判断できる表になります。

📊

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

🏆

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