導入
問い合わせ管理表に毎週新規が増えていく中、上長から「田中は今週何件抱えてる？」「鈴木に負荷が偏ってないか？」と聞かれて答えられない。明細をフィルタすれば1人ずつは出せるが、全員の負荷を一目で比較する仕組みがない――こんな場面はありませんか。
これは管理者の集計能力の問題ではなく、担当者別に集計するビューが管理表にないことが原因です。本記事では、担当者別の件数・未対応件数・期限超過件数を一画面で見られるビューを作る手順をまとめます。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 担当ごとの件数や負荷が分からない |
| 主な原因 | 担当者別に集計する仕組みがない |
| 解決方法 | 担当者別の件数・未対応・期限超過を集計する |
| 対象業務 | 案件管理・営業管理・問い合わせ管理 |
| 対象人数 | 3〜30人 |
| 難易度 | ★☆☆☆☆ |
| 作業時間 | 30分 |
| 用意するもの | 対象のExcelファイル／編集権限 |
| 効果 | 担当別の状況を把握しやすい |
| 向かないケース | 担当者が1人だけの表 |
担当別ビューは1枚あれば、「誰に何件溜まっているか」「誰の期限が危ないか」が即わかります。リソース調整の判断が速くなります。
なぜその管理表はうまくいかないのか
- 担当者列に表記ゆれがあり「田中」「田中さん」が別人扱いになる
- 担当別の負荷を見たい時に毎回フィルタで数える
- 同じ担当者が複数の状態に分散していて全体像が見えない
- 期限超過件数を担当別に出す仕組みがない
- 担当者の入れ替わり時に過去データの集計が破綻する
担当者の意識ではなく、担当別の負荷を可視化する仕組みがないことが原因です。見直しは、担当者列をマスタ参照に整えてから、担当別ビューを切り出すところから始めます。
完成イメージ
直す前 — 明細表だけ:
| 案件ID | 担当者 | 状態 | 期限日 |
|---|---|---|---|
| Q-001 | 田中 | 対応中 | 2024-04-15 |
| Q-002 | 鈴木 | 完了 | 2024-04-10 |
| Q-003 | 田中さん | 未対応 | 2024-04-20 |
田中の総件数を出すのに「田中」と「田中さん」をそれぞれ集計する必要がある。
直した後 — 担当別ビュー:
| 担当者ID | 担当者名 | 全体件数 | 未対応 | 対応中 | 期限超過 |
|---|---|---|---|---|---|
| U-001 | 田中 一郎 | 18 | 4 | 8 | 2 |
| U-002 | 鈴木 二郎 | 12 | 2 | 5 | 1 |
| U-003 | 佐藤 三郎 | 8 | 1 | 3 | 0 |
| 合計 | — | 38 | 7 | 16 | 3 |
担当者別の負荷と期限超過が同時に見えます。
改善手順
ステップ1. 担当者列を整える
担当者の表記揺れがある状態では集計が成り立ちません。
操作: 別シート「担当者マスタ」を新規作成。A列に担当者ID、B列に氏名、C列に部署、D列に有効/無効を記入。明細表の担当者列をマスタ参照のプルダウンに変える。
記入例:
| 担当者ID | 氏名 | 部署 | 有効 |
|---|---|---|---|
| U-001 | 田中 一郎 | 営業 | 有効 |
| U-002 | 鈴木 二郎 | 営業 | 有効 |
| U-003 | 佐藤 三郎 | サポート | 有効 |
明細表の担当者ID列に入力規則を =担当者マスタ!$A$2:$A$50 で設定し、自由入力を禁止する。
ステップ2. 担当別ビュー用のシートを作る
担当者別の集計を表示する専用シートを作ります。
操作: シート「案件_担当別ビュー」を新規作成。タブ色を黄色にする。シート先頭に「対象期間」「最終更新」セルを置き、その下に担当者別集計表を配置する。
記入例:
| ヘッダー | 値 |
|---|---|
| 対象 | 全期間（または当月） |
| 最終更新 | =NOW() |
| 期限超過の定義 | 期限日 < TODAY() AND 状態 ≠ 完了 |
ステップ3. 担当者別の件数・未対応を集計する
担当者×状態のクロス集計を作ります。
操作: A列に担当者ID、B列にVLOOKUPで氏名を表示、C列以降にCOUNTIFSで状態別件数を入れる。
記入例:
| 担当者ID | 氏名（VLOOKUP） | 全体件数 | 未対応 | 対応中 |
|---|---|---|---|---|
| U-001 | =VLOOKUP(A2,担当者マスタ!A:B,2,FALSE) | =COUNTIF(案件!C:C,A2) | =COUNTIFS(案件!C:C,A2,案件!D:D,”未対応”) | =COUNTIFS(案件!C:C,A2,案件!D:D,”対応中”) |
✗悪い例: 担当者列が「田中」「田中さん」のまま集計 ◎良い例: 担当者IDに統一し、表示は VLOOKUP で引く
ステップ4. 担当者を選んで明細を抽出する
件数だけでなく、担当者を選んで担当案件の明細も見られるようにします。
操作: 担当別ビューシートに「担当者選択」セル（プルダウン）を置き、その下にFILTER関数で該当担当者の案件を抽出する。=FILTER(案件!A2:F1000,案件!C2:C1000=$B$5) のように。
記入例:
| セル | 内容 |
|---|---|
| B5 | プルダウン（担当者ID選択） |
| A8〜F100 | =FILTER(案件!A2:F1000,案件!C2:C1000=$B$5) |
B5を切り替えると、その担当者の案件一覧が下に展開される。
ステップ5. 偏りに気づける目印を入れる
担当別件数を見るだけでなく、偏りに気づけるように条件付き書式で色を付けます。
操作: 「全体件数」列に対して、「条件付き書式」→「カラースケール」を設定。件数が多い人ほど赤、少ない人ほど緑にする。期限超過列は1以上で赤背景にする。
記入例:
| 列 | 書式 |
|---|---|
| 全体件数 | カラースケール（多い赤・少ない緑） |
| 期限超過 | 1以上で赤背景 |
色で偏りが分かり、リソース再配分の判断が速くなる。
実務での注意点
- 担当者が1人だけの表（個人タスク管理など）には担当別ビューを作る価値が薄いです。
- 担当者マスタの「有効/無効」列を活用し、退職者は無効化（行削除はしない）で過去データとの突合を残します。
- FILTER関数はExcel 365/2021以降の機能です。古いExcelの場合はオートフィルタ＋並び替えで代用します。
- 「担当者がいない案件」を見落とさないよう、件数表の合計と明細表全体の合計を比較する確認ルーチンも入れます。
- 担当別件数の合計が明細件数と一致しない場合は、空欄の担当者列または不正なIDが混入しています。
まとめ
担当ごとの件数や負荷が分からない原因は、担当別に集計する仕組みが管理表にないことです。担当者マスタを整え、担当別ビューシートでCOUNTIFS集計＋FILTER抽出を組み合わせれば、担当別の負荷が一画面で把握できます。
次にやることは、対象ファイルの担当者列を「担当者マスタ参照のID列」に置き換えることです。マスタ整備が済んだら担当別ビューは30分で組めます。あわせて、状態別件数のビューが必要なら状態別・担当別の件数ビューを作る手順、期限管理を強化するなら期限超過ビューを作る手順も参考になります。
