ExcelでKGIとKPIを階層管理する管理表の作り方

ExcelでKGIとKPIを階層管理する管理表の作り方 IT
  1. まず整理しよう｜KGIとKPIの違い＆「階層管理」が必要な理由
    1. KGI＝ゴール、KPI＝ゴールに向かう途中経過
    2. なぜ「階層管理」が必要？（KGI→KPI→施策が繋がるから）
    3. Excel管理表でありがちな落とし穴（先に回避）
  2. 設計が9割｜KGI→KPI→施策まで落とす“管理表の型”を決める
    1. まずは「分解の式」を決める（KGIをKPIに落とす）
    2. 管理表は“3階層”を基本形にする（KGI／KPI／施策）
    3. Excelに落とす前に決める「必須カラム」（迷ったらこれ）
    4. “粒度”を揃えるルールを作る（ここが形骸化の分岐点）
    5. 設計のゴール：上から下へ“たどれる”こと
  3. Excelで作る｜入力しやすく崩れない管理表（項目・レイアウト例）
    1. まずは結論：シートは「入力」と「参照」を分ける
    2. マスタ（固定情報）シート例：IDでつなぐと崩れない
    3. 実績入力シート例：目標と実績だけに絞る
    4. 入力が楽になるExcel設定3つ（ここだけ先にやる）
  4. 見える化＆運用｜進捗・達成見込みが一瞬で分かる集計（関数／ピボット／条件付き書式）
    1. まずは関数で“進捗率”を自動計算（KPI/KGI共通）
    2. 達成見込み（着地）を出す：月の残りを“ペース”で見る
    3. ピボットで“オーナー別・階層別”の進捗一覧を作る
    4. 条件付き書式で“見るだけで分かる”にする（3色ルール）
    5. 運用の型：見る順番を固定すると、表が“生きる”
  5. つまずき対策｜よくある失敗と改善ポイント（形骸化させないコツ）
    1. 失敗①：KPIが増えすぎて「結局どれが重要？」になる
    2. 失敗②：施策が「やった気」ログになり、KPIに効いているか不明
    3. 失敗③：未達の原因が毎回「頑張ります」で終わる
    4. 失敗④：更新が面倒で止まる（入力が重い／責任が曖昧）
    5. 失敗⑤：会議が“報告会”になって改善が起きない

まず整理しよう｜KGIとKPIの違い＆「階層管理」が必要な理由

Excelで管理表を作り始める前に、ここが曖昧だとほぼ確実に詰まります。それが「KGIとKPIの違い」と「なぜ階層で管理するのか」です。20代の会社員あるあるで、上司から突然「KPI管理表つくって」と言われて、とりあえず数字を並べた結果、“頑張ってるのに成果が見えない表”になりがち。まずは定義を揃えて、ブレない土台を作りましょう。

KGI＝ゴール、KPI＝ゴールに向かう途中経過

KGI（Key Goal Indicator）は「最終的に達成したい成果」です。売上、利益、契約数、解約率、粗利率など、ビジネスのゴールに直結する指標が代表例。

一方でKPI（Key Performance Indicator）は、KGIを達成するために日々コントロールする「プロセス指標」です。例えば売上（KGI）なら、商談数、提案数、架電数、サイト流入数、問い合わせ数…などがKPIになり得ます。

  • KGI：結果（最終成果）
  • KPI：過程（成果に効く行動・状態）

ここで重要なのは、KPIは「それを改善すればKGIが上がる」関係になっていること。測りやすい数字＝KPIではありません。測りやすいだけの数字を積むと、管理表は立派でも成果に繋がらない“作業”になります。

なぜ「階層管理」が必要？（KGI→KPI→施策が繋がるから）

KGIとKPIを同じ表に並べるだけだと、次の問題が起こります。

  • 数字の優先順位がつかない（どれが最重要か不明）
  • 未達の原因が追えない（結果だけ見て終わる）
  • 打ち手がバラバラになる（KPIが下がっても何をすべきか決まらない）

そこで効くのが階層管理です。イメージは「木構造」。

  1. KGI：達成したいゴール（例：月商300万円）
  2. KPI：ゴールに効く分解指標（例：商談数、成約率、平均単価）
  3. 施策：KPIを動かす具体アクション（例：架電件数、提案書テンプレ改善、導入事例作成）

この順番でつながっていると、管理表は単なる「記録」から、意思決定の道具に変わります。たとえばKGI未達でも、KPIを見ることで「商談数は足りてるが成約率が低い→提案内容を改善」と因果を辿れます。逆にKPIも未達なら「そもそも商談母数が不足→リード獲得施策を優先」のように、次の一手が明確になります。

Excel管理表でありがちな落とし穴（先に回避）

階層を作らずにいきなりExcelで作ると、次のような表になりがちです。

  • 項目が増え続けて、どれがKGI/KPIか分からない
  • 部署・チーム・個人の数字が混ざり、責任範囲が曖昧
  • 月次報告のたびに手直しが発生し、更新が面倒で形骸化

だから1章の結論はシンプルで、「Excelの前に、構造を決める」です。次章では、KGI→KPI→施策まで落とし込むための“管理表の型”をどう設計するかを整理していきます。

設計が9割｜KGI→KPI→施策まで落とす“管理表の型”を決める

Excelを開く前にやるべきことは、「どんな表にするか」を先に決め切ることです。ここが曖昧だと、あとから列が増殖して更新が地獄になります。逆に言うと、型さえ決まれば入力して集計するだけ。この章では、KGI→KPI→施策までが一本の線でつながる“管理表の型”を作ります。

まずは「分解の式」を決める（KGIをKPIに落とす）

KPI設計で一番強いのは、KGIを計算式に分解することです。たとえば売上KGIなら、ありがちな分解は以下。

  • 売上＝商談数 × 成約率 × 平均単価
  • リード数＝流入数 × CVR（問い合わせ率）

この式が決まると、KPIの優先順位が自然に決まります。「商談数が足りないのか」「成約率が落ちているのか」が切り分けられるからです。ポイントは、Gitのように差分（どこがズレたか）を追える構造にすること。数字をただ並べるのではなく、因果で追える形にします。

管理表は“3階層”を基本形にする（KGI／KPI／施策）

おすすめの型は、実務で破綻しにくい3階層です。

  1. KGI：最終目標（例：月商300万円）
  2. KPI：KGIを構成する分解指標（例：商談数、成約率、平均単価）
  3. 施策：KPIを動かす行動（例：架電、資料改善、事例追加）

ここで大事なのは、KPIと施策を1対1で結ばないことです。現実は「1つのKPIに複数施策」「1施策が複数KPIに効く」が普通。だから管理表の型としては、施策側に“主に効かせたいKPI”を紐づける運用にするとシンプルに回ります。

Excelに落とす前に決める「必須カラム」（迷ったらこれ）

入力が続く管理表は、項目を盛るほど死にます。先に必須カラムを固定しましょう。おすすめは次のセットです。

  • 階層（KGI / KPI / 施策）
  • 指標名・施策名
  • オーナー（責任者。ここが無いと全てが“みんなの仕事”になる）
  • 目標（月次など期間を明確に）
  • 実績
  • 進捗率（目標に対して何％か）
  • 期限（施策は必須）
  • ステータス（未着手/進行中/完了/保留）
  • メモ（未達理由・次アクションを書く欄。ここが“意思決定”になる）

さらに、後で3章のExcel設計がラクになるコツとして、KPIには「単位」も入れておくのがおすすめです（件、％、円など）。単位が混ざると、条件付き書式や集計で事故りやすいからです。

“粒度”を揃えるルールを作る（ここが形骸化の分岐点）

同じ表の中で、KPIが「月次」、施策が「日次」、一部が「四半期」…のように粒度がバラバラだと、更新頻度も会議の見方も崩れます。おすすめルールはシンプル。

  • KGI/KPIは月次（経営・チームのレビューがしやすい）
  • 施策は週次（行動は短いサイクルで回す）
  • 会議では「先にKPIの差分→次に施策のテコ入れ」で見る

この運用前提まで決めておくと、Excelの表は「報告のため」ではなく、毎週の修正が入る前提の設計になります。結果、更新され続ける管理表になります。

設計のゴール：上から下へ“たどれる”こと

2章の結論は、管理表の型は上（KGI）から下（施策）へ原因と打ち手を追跡できること。KGIが未達になりそう→どのKPIがズレてる？→効かせる施策は何？まで、一本でつながる状態を作ります。

次章では、この型をそのままExcelに落とし込み、入力しやすく崩れないレイアウト（項目配置・表の作り方）を具体例付きで作っていきます。

Excelで作る｜入力しやすく崩れない管理表（項目・レイアウト例）

設計（型）が決まったら、次はExcelに落とし込みます。ここで意識したいのは「見た目がそれっぽい表」ではなく、増えても壊れない／誰でも迷わず入力できること。おすすめは、シートを分けて役割を固定するやり方です。

まずは結論：シートは「入力」と「参照」を分ける

  • ①マスタ（固定情報）：KGI/KPI/施策名、階層、オーナー、単位、紐づくKPIなど
  • ②実績入力（月次/週次）：期間ごとの目標・実績だけを入れる
  • ③ダッシュボード（一覧/可視化）：集計結果を見る専用（4章で作る）

1枚のシートに全部詰め込むと、列追加や並び替えで破綻します。「入力する場所はここ」を固定すると運用が回ります。

マスタ（固定情報）シート例：IDでつなぐと崩れない

マスタは「指標や施策の定義」を置く場所です。ここがブレないと、実績入力や集計が安定します。列は次のテンプレでOK。

項目 例 狙い
ID KGI001 / KPI010 / ACT120 参照キー。名前変更しても集計が壊れない
階層 KGI / KPI / 施策 フィルタ・集計の軸
名称 月商 / 商談数 / 架電 表示用（変更してもOK）
親ID KPI010の親＝KGI001 階層のつながり（上から辿れる）
主に効かせたいKPI（施策のみ） ACT120 → KPI010 施策とKPIの紐づけを簡略化
オーナー 自分/チーム名 責任範囲を固定
単位 円 / 件 / % 表示・条件付き書式の事故防止
更新頻度 月次（KGI/KPI）/ 週次（施策） 運用ルールを表に埋め込む

ポイントは「ID」と「親ID」です。階層をインデント（スペース）で表現すると、行の挿入・並べ替えで崩壊します。IDで繋げておけば、後でピボットや関数で参照しても壊れません。

実績入力シート例：目標と実績だけに絞る

実績入力は「数字を入れるだけ」にします。コメントや会議メモまでここに混ぜると入力ハードルが上がるので、必要最小限に。

期間 ID 階層 目標 実績 ステータス 期限（施策のみ） メモ
2025/12 KPI010 KPI 40 32 進行中 – 商談化率が下振れ。リスト見直し
2025/W50 ACT120 施策 200 180 進行中 2025/12/20 架電時間を午前に寄せる
  • IDはプルダウンにする（手入力ミスをゼロに）
  • 階層はIDから自動表示（XLOOKUPでマスタ参照）にすると入力が速い
  • 期間は「月次」と「週次」で列を分けず、同じ列で管理（後の集計が楽）

入力が楽になるExcel設定3つ（ここだけ先にやる）

  1. テーブル化（Ctrl+T）

    行を追加しても書式・数式が自動で伸びます。「列が増殖して崩れる」問題をかなり防げます。テーブル名は T_実績 などにしておくと、関数が読みやすくなります。

  2. 入力規則（データの入力規則）でプルダウン化

    ID、ステータス（未着手/進行中/完了/保留）はプルダウンに。人が増えるほど表記ゆれ（完了/済/Done）が致命傷になるので、ここは最優先で固定します。

  3. 参照列は“表示用”にしてロック

    名称、オーナー、単位はマスタから参照して表示する列として追加しておくと便利です（例：=XLOOKUP([@ID],T_マスタ[ID],T_マスタ[名称])）。この列は編集不要なので、シート保護で入力セル以外をロックすると事故が減ります。

ここまで作れれば、3章のゴールである「入力しやすく、増えても崩れない管理表」は完成です。次章では、この実績データを使って、進捗や達成見込みが一瞬で分かるように関数／ピボット／条件付き書式で見える化していきます。

見える化＆運用｜進捗・達成見込みが一瞬で分かる集計（関数／ピボット／条件付き書式）

3章までで「入力は回る」状態になりました。次は、入れた数字を意思決定に変えるパートです。やることはシンプルで、ダッシュボード側で①進捗（今どれくらい？）②着地見込み（このままだと間に合う？）③どこが詰まってる？を一瞬で見えるようにします。

まずは関数で“進捗率”を自動計算（KPI/KGI共通）

ダッシュボードや実績入力シートに、進捗率列を用意します。テーブル化（T_実績）している前提なら、式はこれでOK。

=IFERROR([@実績]/[@目標],0)

％表示にしておけば、会議で見る数字が揃います。さらに「階層」や「単位」はマスタから参照済みの想定なので、“目標と実績だけ入力→進捗が勝手に出る”状態になります。

達成見込み（着地）を出す：月の残りを“ペース”で見る

月次KPIで効くのが、現時点の実績から「このペースだと月末いくつ？」を出す見込みです。例として、当月の経過日数ベースで単純予測するなら以下。

=IFERROR( [@実績] / DAY(TODAY()) * DAY(EOMONTH(TODAY(),0)) ,0)

これで「今月の着地見込み」を作れます。精度は完璧じゃないですが、20代の現場運用では“赤信号を早めに点ける”のが目的なので十分役に立ちます。週次施策は着地よりも、後述のステータス集計のほうが効きます。

ピボットで“オーナー別・階層別”の進捗一覧を作る

関数は個別行の計算、ピボットは全体の俯瞰が得意です。ダッシュボードにピボットを1つ置くだけで、運用が一気にラクになります。

  • 行：オーナー
  • 列：階層（KGI / KPI / 施策）
  • 値：目標の合計、実績の合計

そしてピボットの外で進捗率を作ります（=実績合計/目標合計）。ピボット内で比率を出す方法もありますが、まずは読みやすさ優先でOK。これで「誰のどの階層が遅れてるか」が会議前に分かります。

条件付き書式で“見るだけで分かる”にする（3色ルール）

最後に、目で判断できる状態にします。おすすめは進捗率に対して3色。

  • 赤：進捗率 < 0.8（未達濃厚）
  • 黄：0.8 以上 1.0 未満（要テコ入れ）
  • 緑：1.0 以上（達成）

Excelの「条件付き書式 → 新しいルール → 数式を使用」で、進捗率セルを基準に設定します。加えて施策は、期限があるので「期限超過」も赤にすると効果が大きいです（例：=AND([@階層]="施策",[@期限]<TODAY(),[@ステータス]<>"完了")）。

運用の型：見る順番を固定すると、表が“生きる”

ダッシュボードができたら、会議の見方も固定します。

  1. KGIの進捗（結論）
  2. KPIの差分（原因）
  3. 施策の期限・停滞（打ち手）

この順番で見ると「数字を眺めて終わり」になりません。未達が見えた瞬間に、どのKPIがズレていて、どの施策を前倒すべきかまで、同じExcel内で辿れるようになります。

つまずき対策｜よくある失敗と改善ポイント（形骸化させないコツ）

管理表は「作った瞬間」がピークになりがちです。特にExcelは自由度が高いぶん、運用ルールが弱いと一気に形骸化します。ここでは、20代の現場で起きやすい“あるある失敗”と、その場で直せる改善ポイントをまとめます。

失敗①：KPIが増えすぎて「結局どれが重要？」になる

測れる数字を片っ端から入れると、会議で見るべき指標が埋もれます。対策はシンプルで、KPIに上限を設けること。

  • 1つのKGIに対してKPIは最大3〜5個（分解式に直結するものだけ）
  • それ以外は「参考指標」枠に隔離（ダッシュボードに出さない）

ポイントは「入力の頑張り」より意思決定の速さ。KPIは少ないほど、テコ入れが速くなります。

失敗②：施策が「やった気」ログになり、KPIに効いているか不明

施策欄が「資料作成」「打ち合わせ」などの作業名だけになると、何も改善されません。改善は、施策の書き方を成果に寄せることです。

  • NG：架電する／資料を直す
  • OK：1日30件×5日架電（商談数を+5狙い）／提案書1枚目を刷新（成約率+2pt狙い）

さらにマスタに入れた「主に効かせたいKPI」の紐づけが、ここで効きます。施策の横に狙うKPIが見えているだけで、無駄打ちが減ります。

失敗③：未達の原因が毎回「頑張ります」で終わる

管理表の価値は「次の一手」が具体化されること。メモ欄を“感想”ではなく、差分→原因→手当ての型で書くとブレません。

  1. 差分：目標40に対し実績32（-8）
  2. 原因：商談化率が先月比-3pt
  3. 手当て：リスト更新＋トーク冒頭をABテスト（今週中）

この型をテンプレ化しておくと、上司への説明も一発で通ります。

失敗④：更新が面倒で止まる（入力が重い／責任が曖昧）

止まる原因はほぼ2つで、「入力が多い」か「誰の仕事か不明」です。

  • 入力は目標・実績・ステータス・ひとことメモだけに絞る（3章の設計に戻す）
  • オーナー列は必須。オーナーが空欄の行は作らない運用にする

「忙しくて更新できない」は自然現象なので、表側を頑張るより更新コストを下げる設計が正解です。

失敗⑤：会議が“報告会”になって改善が起きない

ダッシュボードを作っても、見る順番が自由だと雑談で終わります。運用のコツは、会議のゴールを「報告」から修正に変えること。

  • 毎週やることは固定：赤（未達濃厚）だけ見る
  • 判断も固定：①KPIを1つ選んで ②施策を前倒し/追加/やめる

この“赤だけ／修正だけ”ルールにすると、管理表は一気に生き返ります。Excelは作るより、更新され続ける仕組みを作った人が勝ちです。

コメント

NewsTowerをもっと見る

今すぐ購読し、続きを読んで、すべてのアーカイブにアクセスしましょう。

続きを読む