1. ホーム
  2. ブログ
  3. Excelピボットテーブルの作り方と使い方|月次の集計を「数式なし」で終わらせる

Excelピボットテーブルの作り方と使い方|月次の集計を「数式なし」で終わらせる

月ごと・担当ごとの集計表を、SUMIFSを何十個も書いて作っていませんか。

元の一覧が1枚あれば、ピボットテーブルで集計表はドラッグだけで作れます。 数式は1つも書きません。

左:ピボットテーブルで作った集計表(数式0個)。右:同じ表をSUMIFSで作ると数式が12個
同じ集計表。左がピボットテーブル、右がSUMIFS。担当が増えると、右は数式を足さないといけない。

元になる一覧の条件

ピボットテーブルは、1行=1件の一覧から作ります。

Excelの画面(作業実績シート)。見出し:日付、担当、取引先、作業、時間(h)、金額
1行目が見出し、2行目から1件ずつ。途中に小計や空行を入れない。
!

この形だとうまく作れない

- 見出しが2段(セルの結合)
- 途中に「小計」の行がある
- 月ごとにシートが分かれている
- 同じ列に数字と文字が混ざっている(「2.0h」など)

ピボットテーブルの作り方(3ステップ)

1. 一覧の中のセルを1つ選んで、「挿入」→「ピボットテーブル」

Excelの挿入タブ。左端のピボットテーブルボタンを赤い枠で示している
範囲を全部選ばなくてよい。一覧の中のどこか1つのセルを選んでから押す。出てきた小さな画面はそのまま「OK」。

2. 新しいシートに、空のピボットと右側の欄が出る

空のピボットテーブルと、右側の「ピボットテーブルのフィールド」の欄
右の欄に、一覧の見出し(日付・担当・取引先…)が並ぶ。ここから下の4つの箱へドラッグする。

3. 「行」に担当、「列」に日付、「値」に金額をドラッグする

担当別・月別の金額の集計表ができたピボットテーブル。右の欄では行に担当、列に月(日付)、値に合計/金額が入っている
これで完成。日付は自動で「月」にまとまる。「行」と「列」を入れ替えれば、取引先別にも一瞬で変わる。
!

「値」が「個数」になっていたら

金額の欄が「個数 / 金額」になっていたら、その▼ →「値フィールドの設定」→「合計」に変える。元の列に文字や空白が混ざっていると個数になる。

できたら、元の一覧と1か所だけ突き合わせる。佐藤さんの10月の合計を、元の表で絞り込んで確かめればよい。

ピボットテーブルの更新のしかた

元の一覧に行を足しても、ピボットは自動では変わりません。どちらかの方法で更新します。

方法1:ピボットの上で右クリック →「更新」

ピボットテーブルのセルを右クリックして出たメニュー。「更新(R)」の項目
ピボットのどのセルでもよい。右クリックして「更新」。

方法2:「データ」タブ →「すべて更新」

Excelのデータタブ。「すべて更新」ボタンを赤い枠で示している
ピボットが何枚あっても、これ1回で全部更新される。
!

更新忘れを防ぐ2つの対策

- 元の一覧をテーブル(「挿入」→「テーブル」)にしておく。行を足したときに範囲が自動で広がる
- ピボットのシートの目立つ所に「開いたら右クリック→更新」と書いておく

SUMIFSとの違い・使い分け

ピボットテーブルSUMIFS
作る手間ドラッグだけ条件の数だけ数式を書く
担当が増えたら更新するだけ数式を足す
置ける場所決まった形で1か所好きなセルに1つずつ
更新右クリックで更新自動

実務では、ピボットで数字を確かめてから、報告書の決まったマスだけSUMIFSで引く、という組み合わせが多いです。SUMIFSの書き方は SUMIFSの使い方 にあります。

ピボットが向かない場合

  • 元のデータが複数のCSVに分かれている → 先に Power Queryで結合 してからピボット
  • 1行=1件の形に直せない(紙の集計表しかない) → 先に一覧の形で入力する仕組みを作る(作業実績のデータ化)

今日やってみること

  1. いま手で集計している表の、元の一覧を探す

    1行=1件になっているか見る。

  2. 一覧の中を1つ選んで、挿入→ピボットテーブル
  3. 行に担当、列に日付、値に金額をドラッグする
  4. 元の表と1か所だけ突き合わせる
  5. 合っていたら、来月から手集計をやめる

次に読むとよい記事

#Excel#ピボットテーブル#集計#月次#SUMIFS

この記事について

SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。

無料で相談してみる

あわせて読みたい

記事一覧へ戻る