名簿を見ながら「部署ごとに何人いるか」を手で数えていませんか。ピボットテーブルを使えば、その集計を数回の操作で終わらせられます。
この記事では、部署別の人数表を作ることを例に、ピボットテーブルの作成から集計結果の読み方まで、最初にやることだけを順番に説明します。フィルターや並べ替えの応用には踏み込まず、「まず人数が出るところまで」に絞ります。
ピボットテーブルは、元データを変えずに集計の切り口を変えられるのが特徴です。集計のたびにCOUNTIF関数を書き直す必要はなく、元の名簿を整えておけば繰り返し使えます。
元データを整える
ピボットテーブルは元のシートの表を読んで集計します。元データに問題があると、集計がずれたり空白の行が現れたりします。作業前にコピーを取っておくと安心です(ファイルメニューから「名前を付けて保存」で別ファイルとして残します)。
元データとして最低限確認するのは次の3点です。
- 1行目が見出し行になっている 「氏名」「部署」「入社年度」のように、各列の内容を表す文字が入っている必要があります。見出しが空白だとピボットテーブルがその列を正しく認識できません。
- 表の途中に空白行・空白列がない 空白が入ると、そこで表の範囲が切れているとExcelが判断することがあります。
- 同じ意味のデータが表記ゆれなしに入っている 「営業部」「営業部 」(末尾にスペース)「えいぎょうぶ」はそれぞれ別の値として集計されます。人数を確認する前にフィルターで値を一覧し、表記ゆれがないか確かめてください。
ピボットテーブルを挿入する
以下は一般的な操作の流れです。画面の表示はExcelの版や設定により異なりますので、詳細は公式のヘルプで確認してください。
- 元データの表内のどこか(任意のセル)をクリックします。
- 「挿入」タブを開き、「ピボットテーブル」を選びます。
- ダイアログが開き、集計する範囲と、ピボットテーブルを置く場所を確認する画面になります。範囲が正しく選ばれているかを確認し、「新しいワークシート」を選んで「OK」を押します。
これで新しいシートが追加され、右側に「ピボットテーブルのフィールド」という作業ウィンドウが開きます。この作業ウィンドウが、集計の設定を行う場所です。
フィールドを配置して人数表を作る
作業ウィンドウには、元データの見出し行の項目名(フィールド)が一覧で表示されます。「部署別の人数」を作るには、フィールドを次のように配置します。
- 「行」エリアに「部署」をドラッグする 行エリアに置いたフィールドは、集計表の左側に縦に並びます。ここに「部署」を入れると、各部署が一行ずつ並びます。
- 「値」エリアに「氏名」をドラッグする 値エリアに置いたフィールドは集計の対象になります。氏名のような文字列を値エリアに入れると、Excelは自動的に「データの個数(COUNT)」として集計します。これが人数に相当します。
配置すると、シート上にこのような表が現れます(架空の例です)。
表は横にスクロールできます
| 部署 | 個数 / 氏名 |
|---|---|
| 営業部 | 8 |
| 総務部 | 3 |
| 開発部 | 12 |
| 総計 | 23 |
値エリアに表示される見出しは「個数 / 氏名」のような自動生成の名前になります。見出しをクリックして「値フィールドの設定」を開くと、表示名を「人数」などに変更できます。
集計がずれる代表的な原因と確認手順
「合計が名簿の行数と合わない」「特定の部署が表に出ない」という場合は、以下の順で確認します。
空白セルが含まれている 値エリアに置いたフィールドのセルが空欄の場合、その行はカウントされません。部署名が空欄の行も同様に、「(空白)」として別の行に集計されます。元データをフィルターで確認し、空白を埋めます。
表記ゆれがある 前述の通り、スペースの有無や全角・半角の違いが異なる値として扱われます。ピボットテーブルの行に見慣れない値が出たら、元データを確認するサインです。
元データを修正した後にピボットテーブルが更新されていない ピボットテーブルは元データが変わっても自動では再計算されません。ピボットテーブルの中をクリックし、「分析」または「ピボットテーブル分析」タブから「更新」を実行します。
範囲に新しい行が含まれていない 名簿に行を追加しても、作成時に指定した範囲外なら集計に入りません。この問題を防ぐには、元データをあらかじめ「テーブル」(Excelのテーブル機能)として設定しておくと、行を追加しても範囲が自動で広がります。テーブルの設定は「挿入」タブの「テーブル」から行います。
迷いやすい場面:数値が入っている列を人数として集計したいとき
氏名ではなく、「社員番号」のような数値が入った列を値エリアに置くと、Excelは「合計(SUM)」として集計することがあります。この場合は「値フィールドの設定」を開き、集計方法を「データの個数」に変更してください。
逆に、氏名など文字列の列は常に「データの個数」として集計されるため、人数を数える用途には適しています。どの列を値エリアに置くかで集計の意味が変わることを覚えておくと、他の集計にも応用できます。
次の行動
まずは手元の名簿を開き、見出し行と空白行がないことを確認してから「挿入」→「ピボットテーブル」を試してください。うまく行が並ばないときは、フィルターで「部署」列の値を一覧し、表記ゆれがないかを先に直します。集計結果が出たら、元の名簿の行数と総計が一致するかを照合して、抜け漏れを確認してください。
参考資料
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
