コールセンターのAHT（Average Handle Time／平均処理時間）を「エージェント×月次（MoM）」で集計したいのに、元データが横持ちで時刻表示のまま…その状態だとピボットが作りにくく、変換すると「0:00」「819」「13.65」などの謎の値に見えることがあります。本記事では、データ整形からピボット作成、分換算の落とし穴、特定エージェントの一括除外までを実務目線でまとめます。
なぜAHT（月次）がピボットで崩れるのか：よくある3つのつまずき
AHTを「エージェント別・月別」で見たいだけなのに、Excelで詰まりやすいポイントは主に次の3つです。
- 元データが横持ち（各月が列）で、ピボットの「列フィールド」にうまく落とし込めない
- AHTが時刻表示（例：13:39）で、分の数値にしたいのに表示が崩れる（0:00になる／819になる等）
- 大量データで、特定エージェント（例：Agent A）を行ごと一括で削除・除外したい
結論から言うと、最短ルートは「横持ち→縦持ち（3列構造）に整形」→「分換算列を追加」→「ピボットで平均」です。ここをテンプレ化すると、毎月のレポート更新が一気に楽になります。
まずは完成形を決める：欲しいのは「エージェント×月」のAHT表
ゴールを先に固定します。最終的に欲しいピボットのイメージは、次のような「行＝エージェント」「列＝月」「値＝平均AHT（分）」の表です。
| Agent | Jan | Feb | Mar | … |
|---|---|---|---|---|
| Agent A | 13.65 | 15.33 | 14.80 | … |
| Agent B | 12.17 | 13.90 | 12.50 | … |
ポイントは、AHTを「時刻」のまま見せるよりも、分（数値）で揃えた方が比較・並び替え・条件付き書式が圧倒的にやりやすいことです。
ピボットが強くなる「縦持ち3列構造」：Agent／Month／AHT
元データが次のような形だとします（各月が列に並ぶ横長テーブル）。
| Agent | Jan AHT | Feb AHT | Mar AHT | … |
|---|---|---|---|---|
| Agent A | 13:39 | 15:20 | 14:48 | … |
| Agent B | 12:10 | 13:54 | 12:30 | … |
この形は「入力としては見やすい」反面、Excelの集計（ピボット・Power Query・Power Pivot）では扱いづらい代表例です。ピボットに最適なのは、次のような縦持ち（正規化）です。
| Agent | Month | AHT（時刻） |
|---|---|---|
| Agent A | Jan | 0:13:39 |
| Agent A | Feb | 0:15:20 |
| Agent B | Jan | 0:12:10 |
| … | … | … |
この3列構造にすると、Monthがピボットの「列フィールド」にそのまま入るので、集計が一気に素直になります。
横持ち→縦持ちに変換する方法：手作業よりPower Queryが安定
方法A：行数が少ないなら手作業・関数で整形
月数・エージェント数が少ないなら、別シートに「Agent／Month／AHT」を作って貼り付ける方法でも対応できます。ですが、月が増える・人が増える・毎月更新する、という運用では破綻しがちです。
- 貼り付け漏れが発生する
- 月が増えるたびに列を足す必要がある
- レポート更新が「作業」になり、属人化しやすい
方法B：Power Queryで「列のピボット解除（Unpivot）」が鉄板
データ量が多い／毎月更新するなら、Power Queryが最も安定します。やることはシンプルで、月の列をまとめて選んで「ピボット解除」するだけです。
- 元データ範囲を選択し、「挿入」→「テーブル」でテーブル化（推奨）
- 「データ」→「テーブル/範囲から」でPower Queryエディターを開く
- Agent列は残し、月ごとのAHT列（Jan/Feb/Mar…）をまとめて選択
- 選択した列を右クリック → 「列のピボット解除」
- 「属性」列をMonthに、「値」列をAHTにリネーム
- 必要ならAHTの型（データ型）を「時刻」に整える
- 最後に「閉じて読み込む」でシートへ出力（テーブルとして読み込むのがおすすめ）
Power Queryを使う最大のメリットは、翌月以降も「更新」ボタンだけで同じ整形が再実行できる点です。月列が追加されても、列選択の手間を最小化できます（運用が固まったら、列名ルールを揃えるとさらに強いです）。
Power Queryで「分」を作る場合の考え方
Power Query上でAHTを分に変換しておくと、Excel側の数式を減らせます。AHTが「時刻（Time）」として入っている場合は、時間・分・秒を取り出して分に合算するのが分かりやすいです。
// 例：AHT（time）を分（number）に変換するカスタム列
// [AHT] が time 型である前提
= Time.Hour([AHT]) * 60
+ Time.Minute([AHT])
+ Time.Second([AHT]) / 60
この列を「AHT_分」などの名前にしておけば、ピボットの値フィールドは常に「AHT_分」を使う運用に統一できます。
Excelの時間は「1日＝1.0」の小数：だから分換算は×1440
「13:39」を分に変えたいのにうまくいかない理由は、Excelの時間の内部表現を知らないと起きがちです。
- Excelの時間は1日＝1.0（シリアル値）として保存される
- 1時間＝1/24、1分＝1/1440（＝24×60）
- したがって、時刻→分の数値にするには×1440
たとえばA2に 0:13:39 が入っている場合：
=A2*1440
結果は 13.65 前後になります。これは「13分39秒＝13 + 39/60＝13.65分」なので、小数になるのが正常です。
小数を見やすくする（四捨五入・秒の扱い）
秒まで含むAHTは小数が出ます。レポートでは「小数第2位」などに揃えると見やすくなります。
=ROUND(A2*1440,2)
「秒は不要、分だけで良い」なら切り捨ても選択肢です。
=INT(A2*1440)
ただし、切り捨ては実態より短く出るので、KPI用途では四捨五入の方が無難です。
「0:00になる」「819になる」原因はほぼ表示形式：症状別の診断表
分に変換したつもりなのに、0:00になったり、819などの謎値になったりする場合、値が間違っているのではなく「表示が間違っている」ことが多いです。まずは症状から切り分けます。
| 症状 | よくある原因 | 確認ポイント | 対処法 |
|---|---|---|---|
| 分換算したのに「0:00」と表示される | 結果セルの表示形式が「時刻」のまま | セルの書式設定が h:mm や mm:ss になっていないか | 表示形式を「数値」または「標準」に変更 |
| 「819」など大きい数字になって不安 | 分としては正しいが、見慣れないだけ／または日付として誤表示 | 819分＝13時間39分。AHTの実態と合うか | 表示形式を数値に。AHTが長時間の場合は元値も確認 |
| 「13.65」など小数になって困る | 秒が入っているため | 元のAHTに秒が含まれるか（0:13:39など） | ROUNDで桁数を揃える（例：小数2桁） |
| 計算できずエラー／0になる | 元のAHTが「文字列」 | 左寄せ表示／VALUEで変換できない | テキスト→時刻に変換してから×1440 |
最重要：分換算したセルは「数値」表示にする
分換算の式（=A2*1440）は合っていても、結果セルの表示形式が「時刻」だと、Excelはその数値を「日付/時刻のシリアル」として解釈してしまい、0:00や意味不明な時刻表示になります。
対処は簡単です。
- 分換算した列（結果列）を選択
- 右クリック → 「セルの書式設定」
- 「数値」または「標準」を選ぶ
「13:39」が13分39秒なのか、13時間39分なのかを見抜く
AHTの表示が「13:39」だと、次の2パターンが紛れます。
- 13分39秒（mm:ssとして見せている）
- 13時間39分（h:mmとして見せている）
見分け方は、元のセルの表示形式を一時的にh:mm:ss（または[h]:mm:ss）にして「本当の値」を確認することです。
- 「0:13:39」と出る → 13分39秒（分換算は13.65）
- 「13:39:00」と出る → 13時間39分（分換算は819）
つまり819や13.65は「変換ミス」ではなく、元の時間の意味が違うか、表示形式が違うだけ、というケースが非常に多いです。
「12時間表記の罠」：12:13:39 AM が 13:39 に見えることがある
少しややこしいですが、次の現象も頻出です。
- セルの実値：12:13:39 AM
- これを「mm:ss」表示にしていると：13:39に見える
12時間表記では、12:xx AM＝0:xx（24時間表記）です。つまり実態は0時間13分39秒で、分換算すると13.65分になります。表示だけ見て「12時間分が入っているのでは？」と誤解しやすいので、必ずh:mm:ssで確認するのが安全です。
AHTが「文字列」だった場合の直し方：計算できないときの定番処理
見た目は13:39でも、実際は「文字列」になっていると、×1440しても期待通りになりません。次のいずれかで「時刻」に直します。
方法1：TIMEVALUEで変換（内容が時刻として解釈できる場合）
=TIMEVALUE(A2)
ただし「13:39」が本当に「分:秒」なのか「時:分」なのかで解釈が変わるため、AHTが短い（1時間未満）前提なら、先頭に「0:」を足すのが安全です。
=TIMEVALUE("0:"&A2) // A2が "13:39"（分:秒）の文字列想定
方法2：「区切り位置」で強制変換
- AHT列を選択
- 「データ」→「区切り位置」
- 区切り文字は適当（次へ次へ）→「列のデータ形式：標準」→完了
文字列→数値の強制変換として定番です。状況によってはこれだけで直ります。
方法3：Power Queryでデータ型を揃える（大量データ向け）
大量データ・定期更新なら、Power Query側で「AHTは時刻」と決め打ちして型を揃えた方が再現性が高いです。型が揃うと、分換算もピボットも安定します。
ピボットテーブル作成手順：Agent×Month×平均AHT（分）
縦持ち（Agent／Month／AHT）までできたら、ピボットは素直に組めます。おすすめは「分換算列（AHT_分）」を作ってからピボットです。
準備：分換算列（AHT_分）を追加
Excelで変換するなら、縦持ちテーブルに次の列を追加します。
= [@AHT] * 1440
テーブル参照（[@AHT]）を使うと行追加に強くなります。結果列の表示形式は数値にしてください。
ピボットの組み方
- 縦持ちテーブル（Agent／Month／AHT_分）内の任意セルを選択
- 「挿入」→「ピボットテーブル」
- フィールド配置を次のように設定
- 行：Agent
- 列：Month
- 値：AHT_分
- 値フィールド（AHT_分）を右クリック → 「値フィールドの設定」→ 「平均」を選択
- 「数値の表示形式」で小数点桁数（例：小数2桁）を整える
月の並び順を崩さないコツ
Monthが「Jan, Feb…」の文字列だと、環境によっては並びが崩れることがあります。安定させるなら次のどちらかがおすすめです。
- Monthを日付（例：2025/01/01）で持つ：表示だけ「yyyy-mm」や「mmm」にする
- MonthKey（例：202501）の数値列を追加し、並び替えの基準にする
特に「月が増える運用」では、月を文字列だけで管理しない方が後で楽です。
実務で差が出る注意点：「平均の平均」問題（必要なら重み付け平均）
ここは現場でよく起きる誤差ポイントなので、念のため触れておきます。元データが「各月のAHT（すでに平均）」になっている場合、そのまま月単位で比較するだけなら問題になりにくいですが、日次・週次など複数粒度を混ぜて集計する場合は平均の平均に注意が必要です。
例として、同じエージェントの2日分のAHTが次のようだったとします。
| 日 | コール件数 | AHT（分） |
|---|---|---|
| Day1 | 2件 | 30 |
| Day2 | 100件 | 10 |
単純平均（(30+10)/2=20分）にすると、実態より大きくズレます。正しくは「処理時間の合計÷件数の合計」の重み付け平均です。
- 合計処理分＝30×2 + 10×100＝60 + 1000＝1060分
- 合計件数＝102件
- 正しい平均AHT＝1060/102＝約10.39分
もし元データに「処理時間合計（秒）」や「件数」がある場合は、ピボットでは「平均AHT」ではなく合計処理時間÷合計件数の形を作る方が正確です。月次KPIで厳密性が必要な場合は、この設計を検討してください。
特定エージェント（例：Agent A）を一括で削除／除外する方法
「重複する名前を削除」という相談の多くは、実務的には特定エージェントを集計対象から外したい（退職者・検証用アカウント・研修用など）という意味で出てきます。やり方は複数ありますが、目的別に選ぶのが安全です。
| 方法 | 向いているケース | メリット | 注意点 |
|---|---|---|---|
| ピボット側で除外（フィルター） | 集計から外したいだけ | 元データを傷つけず安全。戻しやすい | フィルター設定を共有・管理する必要あり |
| Power Queryで行フィルター | 更新のたびに常に除外したい | 更新ボタンで自動的に除外が維持 | 除外条件の変更はクエリ編集が必要 |
| 元データでフィルター→行削除 | 完全に消して問題ないデータ | データが軽くなる | 戻せない。誤削除リスクが高い |
| 除外フラグ列を作る（例：対象/除外） | 除外対象が増減する運用 | 柔軟。運用ルールを作りやすい | フラグ管理（誰が更新するか）が必要 |
方法1：ピボットテーブル上で除外（最も安全）
- ピボットの行ラベル（Agent）の▼をクリック
- Agent Aのチェックを外す（またはラベルフィルターで除外）
集計から外すだけなら、まずはこれが最も安全です。元データを編集しないので、後から「やっぱり戻して比較したい」もできます。
方法2：Power QueryでAgent Aを除外（更新運用に強い）
毎月更新する運用で「常にAgent Aは除外したい」なら、Power Queryでフィルターしてしまうのが安定です。
- Power QueryエディターでAgent列のフィルターを開く
- Agent Aのチェックを外す（または「等しくない」条件）
- 「閉じて読み込む」→以後は更新で自動反映
方法3：元データから削除する場合は「行ごと削除」が必須
どうしても元データから消す場合、名前セルだけ消してAHTを残すのはNGです。誰のAHTか判別できなくなり、ピボットが崩れます。
- 元データのAgent列にフィルター
- Agent Aだけ表示
- 行全体を選択して削除
よくある運用トラブルと防止策：月次レポートをテンプレ化する
毎月同じ作業をするほど、ヒューマンエラーが増えます。次の構成にすると、更新が「作業」ではなく「ボタン」になります。
| シート | 役割 | ポイント |
|---|---|---|
| Raw（元データ） | 貼り付けるだけ | 列名ルールを固定（Jan/Feb…など） |
| PQ（整形結果） | 縦持ち＋AHT_分 | Power QueryでUnpivot＆分換算 |
| Pivot（集計） | ピボット表 | 行=Agent、列=Month、値=平均AHT_分 |
| Dashboard（任意） | 可視化 | スライサー・条件付き書式で運用しやすく |
チェックリスト：更新前にここだけ見れば崩れない
- 元のAHTセルをh:mm:ss（または[h]:mm:ss）で表示して「本当の時間」を確認したか
- AHTが文字列になっていないか（左寄せ／計算できないなど）
- 分換算列の表示形式が数値になっているか
- ピボットの値フィールドが「合計」ではなく平均になっているか
- Agent名に余計な空白がないか（例：Agent A と Agent A␠）
まとめ：AHTの月次ピボットは「縦持ち」と「表示形式」が9割
AHT（月次）をエージェント別に比較する作業は、データの形とExcelの時間仕様を押さえれば、毎月の更新が驚くほど楽になります。
- 横持ち（各月が列）を、Agent／Month／AHTの縦持ちに変換する（Power Queryの列のピボット解除が最短）
- Excelの時間は1日＝1.0の小数。分換算は×1440
- 「0:00」「819」問題の大半は表示形式が原因。分換算列は数値で表示する
- 特定エージェントの除外は、まずはピボットのフィルター、更新運用ならPower Queryで行フィルター
この流れをテンプレ化しておけば、「貼り付け→更新→ピボット更新」だけで月次レポートが回るようになります。AHTの推移が安定して見えるようになると、改善の議論（どの月・どのエージェントで跳ねたか）に時間を使えるようになります。
コメント