KPIカードと条件付き強調表示

KPIカードとは、読者が最も知りたい1つの情報を示す、見出しとなる単一の数値です。たとえば、総売上高、今日の注文数、平均注文額などがあります。ダッシュボードは通常、このようなカードを横一列に並べた状態から始まります。

優れたカードは、明確なラベル、数式で求めた大きな数値、そして前の期間との比較などの小さな比較情報という3つの要素で構成されます。

このレッスンでは、集計数式で KPI の数値を作成し、期間ごとの比較を追加して、注意が必要な値を条件分岐で目立たせます。

まず中心となる指標から始めます。Sales シートの総売上高は、金額列に対して SUM を使うだけで求められます。

これをカードの値セルに配置します。

注文数を求めるには、Order ID のように必ず値が入っている列に対して COUNTA を使います。カードのラベルは、その数値が何を意味するのかを示す、上部の通常のテキストセルです。各カードを1つの数値に絞ると、内容をすぐに読み取れます。

多くの KPI は比率で表されます。平均注文額は、総売上高を注文数で割って求めます。2つの集計値から作成しましょう。

簡潔な方法として、AVERAGE を直接使えます。

別の方法として、合計カードの値を件数カードの値で割って求めることもできます。どちらの方法でも、このカードには平均的な購入額が表示され、データが変わるたびに更新されます。

KPI は目標と並べると、より意味のある情報になります。たとえば、月間の売上目標がセル B1 に入っているとします。目標をどれだけ上回ったか、または下回ったかを計算します。

差異は、現在の値から目標を引いて求めます。

=SUM(Sales!C2:C500)/B1 を使って、目標に対する割合で表すこともできます。1.12 と表示されれば、目標の112パーセントを達成したことを示します。比較することで、単なる数値が意味のある情報になります。

読者は、数値の勢いを確認できると便利だと感じます。前の期間からの変化率を計算しましょう。今期の合計が D2、前期の合計が D3 に入っているとします。

成長率の数式は次のとおりです。

今期が120で前期が100の場合、結果は0.2、つまり20パーセントの成長となります。セルをパーセント表示に設定してください。このような小さな上昇・下降の表示があれば、単調なカードでも一目で傾向を把握できます。

IF を使うと、文字や記号で方向を示せます。変化率のセルを読み取り、ラベルを選択します。

これにより、割合とともに上昇または下降のマーカーが返されます。

E1 の変化率が正の場合は上昇を示す表示が、それ以外の場合は下降を示す表示が現れます。テキストと書式設定済みの数値を結合すると、グラフを使わなくてもカードを簡潔で読みやすくできます。

条件付き書式は、ルールに基づいてセルの色を変更するため、重要な値を目立たせられます。KPI のセルを選択し、「条件付き書式」を開いて、数式で判定するルールを追加します。

目標を下回ったカードを赤で示すには、次のような数式ルールを使います。

ルールの結果が TRUE になるセルには、選択した書式が適用されます。これにより、売上が目標に届かなければダッシュボードが赤に、目標を上回れば緑になり、数値を詳しく読まなくても状況を把握できます。

色だけでなく、セル自体にフラグをテキストで表示したい場合もあります。IF と比較演算子を使うと、状態を表す語を作成できます。

目標に対する達成状況を表示するには、次のようにします。

目標を達成していれば「順調」、達成していなければ「遅れ」と表示されます。このような状態列は、文字の色を変える条件付き書式と相性がよく、視覚的な情報と文字による情報の両方を示せます。

実際の KPI には、良好、警告、重大のように、2つを超える状態があることがよくあります。IFS 関数は条件を順番に確認し、最初に一致した結果を返すため、IF を入れ子にするよりも読みやすくなります。

達成状況を3段階に分類するには、次のようにします。

IFS は上から下へ条件を確認するため、最も厳しい条件を最初に配置します。それぞれの段階に色のルールを組み合わせると、カードだけで状態を一目で伝えられます。

単一のルールだけでなく、カラースケールを使うと、数値の範囲をグラデーションで色付けできます。数式を使わなくても最大値と最小値が目立つため、地域別の合計や日別の数値の列に適しています。

範囲を選択し、「条件付き書式」を開いて、緑から赤などのカラースケールを選びます。

• 最大値は緑、最小値は赤で表示され、その中間の値は両方の色が混ざった色になります。
• データが変わると、スケールも自動的に再計算されます。

カラースケールは KPI カードとよく合います。カードが見出しとなる数値を示し、色付けされた列が各値の相対的な位置を示します。

ダッシュボードの上部に、カードをきれいな横一列に配置します。各カードは、ラベルセル、値を求める数式、その下の比較または状態表示で構成される小さなブロックです。

• カード1：総売上高、=SUM(Sales!C2:C500)、目標との差異付き。
• カード2：注文数、=COUNTA(Sales!A2:A500)。
• カード3：平均注文額、=AVERAGE(Sales!C2:C500)、成長率付き。

条件付き書式で比較セルに色を付けます。この行を見るだけで、読者は詳細を確認する前に概要を把握できます。

複数段階の状態フラグを作成する方法を理解しているか確認しましょう。

ダッシュボードの見出し部分を作成しました。

• SUM、COUNTA、AVERAGE のような集計数式で、大きく表示する KPI の数値を作成しました。
• 差異と成長率の数式で、目標や前の期間と比較するための情報を追加しました。
• IF と IFS で数値を「順調」「注意」「重大」のような状態表示に変換しました。
• 数式ルールを使った条件付き書式でセルに自動的に色を付け、重要な項目にフラグを付けました。

これまでのレッスンで作成した集計テーブル、数式によるピボット、インタラクティブなドロップダウンと組み合わせれば、数式で動く完全なライブダッシュボードを作成できます。