コールセンターやサポート業務の生産性を見るうえで「AHT（平均処理時間）」は最重要KPIのひとつです。本記事では“8月のAHT”を、Excelだけで全体・上位60%（速いグループ）・下位40%（遅いグループ）に分けて自動計算する実務手順を、最新関数と旧バージョン両対応で徹底解説します。データ構造から関数例、エラー対策、可視化までひとつの記事で完結します。
目的（ゴール）
- 8月の全体 AHT（全エージェント平均）を1セルで算出
- 8月の上位60%（AHTが低い＝速い）グループのAHTを自動算出
- 8月の下位40%（AHTが高い＝遅い）グループのAHTを自動算出
- Microsoft 365 の新関数（
LET/FILTER/SORTBY/TAKE/DROP/HSTACK/CHOOSECOLSなど）で並べ替え不要・スピル一発の集計 - 旧Excel（新関数なし）でも実現可能な代替手順を提示
前提データ（8月分の集計表）
まずは8月分のエージェント別集計が下表のように用意されていることを想定します。単位は「秒」でも「分」でも構いません（後述の表示形式で調整できます）。
| 列 | 内容例 | 説明 |
|---|---|---|
| A | Agent | 担当者名 |
| B | Contacts | 取扱件数 |
| C | HandleTime | 総処理時間（秒 または 分） |
表の先頭行（A1:C1）はヘッダー、データはA2:Cn行までとします。Microsoft 365 環境では表に変換（Ctrl + T）しておくと、列の増減に自動追従できて便利です（以降の例では表名 tblAug、列名 [Agent] / [Contacts] / [HandleTime] を使用）。
最短ルート：1セルで全自動（Microsoft 365）
次のスピル数式を1セル（例：E2）に入力するだけで、全体・上位60%・下位40%の人数／合計／AHTがまとまった結果表を瞬時に返します。元データの並べ替えは不要です。
スピル数式（表 tblAug を前提）
=LET(
a0, tblAug[Agent],
c0, tblAug[Contacts],
h0, tblAug[HandleTime],
mask, (c0>0)*(h0>=0),
a, FILTER(a0, mask),
c, FILTER(c0, mask),
h, FILTER(h0, mask),
arr, HSTACK(a, c, h, h/c),
s, SORTBY(arr, INDEX(arr,,4), 1),
k, ROUNDUP(ROWS(s)*0.6, 0),
top, TAKE(s, k),
bot, DROP(s, k),
whole, SUM(h)/SUM(c),
topAHT, SUM(CHOOSECOLS(top, 3)) / SUM(CHOOSECOLS(top, 2)),
botAHT, SUM(CHOOSECOLS(bot, 3)) / SUM(CHOOSECOLS(bot, 2)),
VSTACK(
{"区分", "人数", "Contacts合計", "HandleTime合計", "AHT"},
HSTACK("全体", ROWS(s), SUM(c), SUM(h), whole),
HSTACK("上位60%", ROWS(top), SUM(CHOOSECOLS(top, 2)), SUM(CHOOSECOLS(top, 3)), topAHT),
HSTACK("下位40%", ROWS(bot), SUM(CHOOSECOLS(bot, 2)), SUM(CHOOSECOLS(bot, 3)), botAHT)
)
)ポイント：
maskで「件数 > 0 かつ 総処理時間 ≥ 0」の行だけを採用。ゼロ件や負値（入力ミス）を自動除外します。arrは{Agent, Contacts, HandleTime, AHT}の横結合。SORTBYで AHT（4列目）を昇順にし、ROUNDUP(ROWS(s)*0.6)で人数ベースの上位60%を切り出し（TAKE）、残りを下位40%（DROP）。- グループAHTは「合計時間 ÷ 合計件数」で算出（単純平均ではありません）。AHTの定義に忠実な加重平均です。
出力イメージ（例）
| 区分 | 人数 | Contacts合計 | HandleTime合計 | AHT |
|---|---|---|---|---|
| 全体 | 10 | 4,642 | 2,989,741 | 644.0 |
| 上位60% | 6 | 3,369 | 1,790,406 | 531.2 |
| 下位40% | 4 | 1,273 | 1,199,335 | 942.4 |
※上はダミー値の例です。AHTの単位は元データ（秒／分）に依存します。セルの表示形式で「[m]:ss」等に整えると読みやすくなります。
ステップバイステップで理解する
行ごとのAHTを作る
最も基本的な式は次です。表にしていない場合、D列にAHT列を新設してコピーします。
=C2/B2Microsoft 365 の場合は以下のように行単位で安全に計算できます（ゼロ割り対策付き）。
=MAP(C2:C1000, B2:B1000, LAMBDA(ht, ct, IF(ct>0, ht/ct, NA())))表（tblAug）を使うなら、構造化参照が読みやすいです。
=MAP(tblAug[HandleTime], tblAug[Contacts], LAMBDA(ht, ct, IF(ct>0, ht/ct, NA())))ポイント：IF(ct>0, ...) がゼロ件の担当者を弾く安全装置になります。ゼロ件の行まで平均に入れると全体AHTが歪みます。
全体AHT（8月）
=SUM(tblAug[HandleTime]) / SUM(tblAug[Contacts])この式は AHT の定義 「総処理時間 ÷ 総件数」 に忠実なので、行平均の平均を取るよりも正確です（件数の多い担当者の重みをきちんと反映）。
AHTで担当者を昇順ソート（小さいほど上位）
元データを並べ替えたくない場合は関数で「並べ替え済みビュー」を作ります。
=LET(
a, tblAug[Agent],
c, tblAug[Contacts],
h, tblAug[HandleTime],
s, SORTBY(HSTACK(a, c, h, h/c), h/c, 1),
s
)SORTBY を使うと元表はそのまま、別セルに並べ換え結果がスピル表示されます。
上位60%・下位40%の抽出（人数ベース）
上位60%の人数 k は、ROUNDUP(n*0.6, 0)（端数切り上げ）で求めます。
=LET(
s, SORTBY(HSTACK(tblAug[Agent], tblAug[Contacts], tblAug[HandleTime], tblAug[HandleTime]/tblAug[Contacts]), 4, 1),
k, ROUNDUP(ROWS(s)*0.6, 0),
top, TAKE(s, k), /* 上位60% */
bot, DROP(s, k), /* 下位40% */
top
)下位40%が必要なら bot を返せばOKです。
各グループのAHT
上位60%のAHT：
=SUM(CHOOSECOLS(top, 3)) / SUM(CHOOSECOLS(top, 2))下位40%のAHT：
=SUM(CHOOSECOLS(bot, 3)) / SUM(CHOOSECOLS(bot, 2))8月データの作り方（ログから集計する場合）
「1行＝1コンタクト」の明細ログから8月の集計表（Agent / Contacts / HandleTime）を作る方法です。ログシート名を Log、列を Agent（A列）、Date（B列：日付時刻）、HandleTime（C列：秒または分）と仮定します。対象月の先頭日をセル F1 に入力（例：2025/8/1）。
8月だけに絞ったエージェント一覧
=LET(
ag, Log!A2:A100000,
dt, Log!B2:B100000,
m0, $F$1,
m1, EOMONTH(m0, 0) + 1,
agents, UNIQUE(FILTER(ag, (dt>=m0)*(dt<m1))),
agents
)担当者ごとの8月 Contacts（件数）
=COUNTIFS(Log!A2:A100000, agents, Log!B2:B100000, ">="&$F$1, Log!B2:B100000, "<"&(EOMONTH($F$1,0)+1))担当者ごとの8月 HandleTime（総時間）
=SUMIFS(Log!C2:C100000, Log!A2:A100000, agents, Log!B2:B100000, ">="&$F$1, Log!B2:B100000, "<"&(EOMONTH($F$1,0)+1))上の3式を組み合わせて HSTACK(agents, contacts, handle) すれば、先述の「最短ルート式」にそのまま渡せる中間表が完成します。Power Query で月次集計を作るのも定番ですが、関数だけでも十分に自動化可能です。
表示単位と体裁の整え方
- 秒 → mm:ss 表示：「セルの書式設定」→「ユーザー定義」→
[m]:ss。 - 秒 → 分：
=秒/60を別列で表示するか、書式で除算（カスタム表示）でもOK。 - 桁区切り：合計値は「桁区切り（,）」で視認性アップ。
- 平均の丸め：
ROUND(式, 0)/ROUNDUP/ROUNDDOWNを使い分け。
旧Excel（新関数なし）の確実なやり方
並べ替えを行ったうえで、上位60%行だけの合計を可変範囲で集計する方法です。
手順
- D列にAHT（
=C2/B2）を作成して最終行までコピー。 - データ全体を選択 → データ > 並べ替え → AHT列を「昇順」に。
- 上位人数
kをセルG1に計算：=ROUNDUP(COUNTA(A2:A1000)*0.6, 0) - 上位60%の Contacts合計：
=SUM(INDEX(B:B, 2):INDEX(B:B, 1+$G$1)) - 上位60%の HandleTime合計：
=SUM(INDEX(C:C, 2):INDEX(C:C, 1+$G$1)) - 上位60%の AHT：
=<上記 HandleTime 合計> / <上記 Contacts 合計> - 下位40%は、総合計から上位60%分を差し引くか、同様に残りの行範囲を
INDEXで指定して求めます。
この方法は可変範囲に INDEX を使うため、OFFSET より軽く、再計算も安定します。
品質を守るための実務Tips
- ゼロ件の扱い：
Contacts=0の行はAHTが定義できないため、集計から除外するのが原則（前掲のmaskで自動除外）。 - 外れ値対策：極端に長い処理が混じる場合は、参考値として
TRIMMEAN（裾切り平均）を併記すると、日々の運用改善の議論がスムーズ。 - 休日や夜間帯の偏り：担当者シフトに偏りがあると AHT に影響が出ます。月次レビュー時に稼働時間帯・業務種別を分けた AHT も確認すると因果が見えます。
- 単位の混在：秒と分が混在していると事故の元。取り込み時に必ず統一する（Power Query で「列のデータ型」を固定）。
- 定義の揺れをなくす：保留・後追い処理・エスカレーション時間を AHT に含めるかは組織で定義を固定し、データ抽出から表示まで一貫させる。
チェックリスト（配布前の最終確認）
- 人数（行数）と上位・下位の分割が意図した比率になっているか（
ROUNDUPで切り上げのため、合計が100%を越えないことを確認）。 - グループAHTは「合計時間÷合計件数」になっているか（単純平均ではない）。
- 表示形式（mm:ss 等）は意思決定者が一目で読めるか。
- ゼロ件や負値が混入していないか（
maskの条件を必要に応じて厳格化）。
「8月」切替をセル1つで行う（応用）
月を切り替えながら同じ仕組みを使いたい場合、対象月の先頭日を F1 に持つ「コントロールセル」を用意し、以下のように LET 内に組み込みます。明細ログ Log からの直集計版です。
=LET(
m0, $F$1,
m1, EOMONTH(m0, 0)+1,
ag, Log!A2:A100000,
dt, Log!B2:B100000,
ht, Log!C2:C100000,
mask, (dt>=m0)*(dt<m1),
agents, UNIQUE(FILTER(ag, mask)),
contacts, COUNTIFS(ag, agents, dt, ">="&m0, dt, "<"&m1),
handle, SUMIFS(ht, ag, agents, dt, ">="&m0, dt, "<"&m1),
arr, HSTACK(agents, contacts, handle, handle/contacts),
s, SORTBY(arr, 4, 1),
k, ROUNDUP(ROWS(s)*0.6, 0),
top, TAKE(s, k),
bot, DROP(s, k),
whole, SUM(handle)/SUM(contacts),
topAHT, SUM(CHOOSECOLS(top,3))/SUM(CHOOSECOLS(top,2)),
botAHT, SUM(CHOOSECOLS(bot,3))/SUM(CHOOSECOLS(bot,2)),
VSTACK({"区分","人数","Contacts合計","HandleTime合計","AHT"},
HSTACK("全体", ROWS(s), SUM(contacts), SUM(handle), whole),
HSTACK("上位60%", ROWS(top), SUM(CHOOSECOLS(top,2)), SUM(CHOOSECOLS(top,3)), topAHT),
HSTACK("下位40%", ROWS(bot), SUM(CHOOSECOLS(bot,2)), SUM(CHOOSECOLS(bot,3)), botAHT))
)この式を1セルに置けば、F1の日付を8/1 → 9/1…と変更するだけで、対象月（8月/9月…）のAHTサマリーが自動的に更新されます。
可視化のすすめ（差がひと目でわかる）
意思決定者に刺さる見せ方は「比較」と「目標ライン」の二点です。
- 棒グラフ（縦）：
横軸＝区分（全体／上位60%／下位40%）、縦軸＝AHT。系列を3本にするだけで差が直観的に伝わります。 - 目標AHTの横線：
別セルに目標（例：480秒）を置いて、定数線（または追加系列として横線）を重ねると、乖離が一目瞭然。 - 条件付き書式：
上位60%行を薄い青、下位40%行を薄い赤で塗り分け。AHTが目標超過のセルを赤太字に。
サンプル数値で動き方を確認（10人の例）
| 区分 | 人数 | Contacts合計 | HandleTime合計 | AHT |
|---|---|---|---|---|
| 上位60% | 6 | 3,369 | 1,790,406 | ≈ 531 秒 |
| 下位40% | 4 | 1,273 | 1,199,335 | ≈ 942 秒 |
上位の「速い」グループは531秒、下位の「遅い」グループは942秒。およそ1.8倍の差です。次のアクションとしては、下位グループの共通ボトルネック（特定チャネル、問い合わせカテゴリ、ナレッジの不足、後処理）を分解して対策するのが王道です。
よくある質問（FAQ）
Q. パーセンタイル（PERCENTILE）で「AHTの60%点」を閾値にしてグループ分けするのはダメ？
A. 悪くはありませんが、担当者人数ベースで厳密に60%/40%に分けることが要件なら、本記事のように並べ替え→件数で切る方法が確実です。パーセンタイルだと閾値付近の同値が多い場合に人数が60%からズレることがあります。
Q. グループAHTを「担当者AHTの単純平均」で出してはいけない？
A. おすすめしません。AHTは必ず「合計時間÷合計件数」で算出してください。担当者ごとの件数が異なるため、単純平均では真の平均処理時間を表しません。
Q. 0件の担当者は？
A. AHTが定義できないため、集計から除外するのが原則です（前掲の mask で自動除外）。
まとめ（配布テンプレートに落とし込む）
- 「8月のAHT」を全体・上位60%・下位40%に自動分解。
- Microsoft 365 なら 1セルのスピル式で一発集計（並べ替え不要）。
- 旧Excelでも
INDEX範囲指定の合計で確実に再現。 - ゼロ割り・外れ値・単位の混在に注意し、表示形式を整える。
- 棒グラフ＋目標ラインで差と改善余地を明確に見せる。
ここまで作っておけば、8月だけでなく9月以降もセル1つの切り替えで同じ手順を再利用できます。業務レビューや毎月の報告資料に、正しいAHTと説得力ある比較指標を安定供給しましょう。
付録：関数早見表（コピペ用）
| 用途 | 推奨数式（Modern） | 代替（Legacy） |
|---|---|---|
| 行AHT | =MAP(tblAug[HandleTime], tblAug[Contacts], LAMBDA(ht,ct, IF(ct>0, ht/ct, NA()))) | =C2/B2 をコピー |
| 全体AHT | =SUM(tblAug[HandleTime]) / SUM(tblAug[Contacts]) | 同左 |
| 並べ替えビュー | =SORTBY(HSTACK(tblAug[Agent], tblAug[Contacts], tblAug[HandleTime], tblAug[HandleTime]/tblAug[Contacts]), 4, 1) | データタブで「昇順」 |
| 上位60%抽出 | =TAKE(s, ROUNDUP(ROWS(s)*0.6, 0)) | =SUM(INDEX(B:B,2):INDEX(B:B,1+k)) 等の可変合計 |
| 下位40%抽出 | =DROP(s, ROUNDUP(ROWS(s)*0.6, 0)) | 総合計−上位合計 |
| グループAHT | =SUM(CHOOSECOLS(group,3)) / SUM(CHOOSECOLS(group,2)) | 同趣旨の範囲合計 ÷ 範囲合計 |
付録：ミスを防ぐデータバリデーション
- Contactsは整数（0以上）に制限。
- HandleTimeは0以上の数値に制限。
- Agentは既存名のプルダウンから選択（重複名・揺れを撲滅）。
付録：チームで使うときの運用リズム
- 月初1営業日：前月（例：8月）ログ確定→取り込み→F1（月初日）を更新→サマリー更新。
- 定例レビュー：上位/下位比較と差分要因を特定。案件タイプ別のAHT分解も検討。
- 改善スプリント：ナレッジ改訂、ワークフロー短縮、後処理テンプレート整備。
- 翌月追跡：可視化と同じ指標で効果検証。目標AHTに対する乖離を定量的に管理。
最後に：この記事の使い方
ここで紹介したスピル式／代替式をそのまま貼り付ければ、どの現場でも即日で「8月のAHT（全体・上位60%・下位40%）」を自動計算できます。まずは小さく回し、正しく動くことを確認したら、表をテーブル化し、表示形式とグラフを整えてレポートに組み込んでください。継続的な可視化が、現場の改善力を底上げします。
コメント