「データはあるのに分析の仕方がわからない」という声をよく耳にします。Excelにはデータ分析のための強力なツールが標準で備わっており、特別な知識がなくてもすぐに使い始められます。本記事では、実務で最も使用頻度の高い「ピボットテーブル」「ソート・フィルター」「SUMIFS関数」を中心に、スライサーや集計フィールド、UNIQUE/FILTER関数まで踏み込んで解説します。
ピボットテーブルでクロス集計する
ピボットテーブルは、大量のデータを自由な切り口で集計できるExcel最強の分析ツールです。「地域別×商品別の売上合計」「月別×担当者別の件数」など、ドラッグ&ドロップだけでクロス集計表を作成できます。一度作れば集計軸を自在に切り替えられるため、「どんな角度からデータを見ればいいかわからない」という探索的な分析にも向いています。
ピボットテーブルの作成手順
- データ範囲内の任意のセルをクリック
- 「挿入」タブ → 「ピボットテーブル」をクリック
- テーブル範囲を確認し「OK」（新しいシートに作成するのがおすすめ）
- 右側のフィールドリストで集計したい項目をドラッグ
フィールドの配置
ピボットテーブルには4つのエリアがあり、フィールド（列名）をどこに配置するかで集計内容が変わります。
- 行 … 縦方向の見出し（例：地域、商品名）
- 列 … 横方向の見出し（例：月、年度）
- 値 … 集計する数値（例：売上の合計、件数）
- フィルター … 全体の絞り込み条件
日付のグループ化で月別・四半期別に集計する
売上データに日付列がある場合、ピボットテーブルの「グループ化」機能を使うと、日単位のデータを月別・四半期別・年別に自動でまとめられます。これは特に便利な機能で、元データを加工せずに集計単位を切り替えられます。
- 日付フィールドを「行」エリアに配置する
- ピボットテーブル内の日付セルを右クリック → 「グループ化」
- 「月」「四半期」「年」などにチェックを入れる（複数選択可）
- 「OK」を押すと日付が指定した単位でまとまる
「月」と「年」を同時に選択すると、年→月の階層で展開できます。2年分のデータがある場合でも、年ごとに折りたたんで表示できるため見やすくなります。
集計フィールドの追加（利益率など計算列を作る）
ピボットテーブルには元データにない計算列を追加できます。たとえば「売上」と「利益」の列があれば、ピボットテーブル内で「利益率 = 利益 ÷ 売上」という計算列を作成できます。これを「集計フィールド」と呼びます。
- ピボットテーブルの任意のセルをクリック（ピボットテーブルを選択状態にする）
- 「ピボットテーブル分析」タブ → 「フィールド、アイテム、セット」→「集計フィールド」
- 「名前」に「利益率」などを入力
- 「数式」に =利益/売上 と入力（フィールド名を選択して挿入できる）
- 「追加」→「OK」でフィールドリストに追加される
集計フィールドは元データには存在しないため、列を追加・加工しなくても良い点が便利です。ただし、集計方法（合計/平均等）によっては計算結果がずれる場合があるため、特に平均を使う場合は注意が必要です。
スライサーで直感的に絞り込む
フィルターエリアへの配置よりも視覚的でわかりやすいのが「スライサー」です。ボタンをクリックするだけで絞り込み条件を切り替えられるため、複数人で共有するレポートシートやプレゼン用の資料に向いています。
- ピボットテーブルを選択した状態で、「ピボットテーブル分析」タブをクリック
- 「スライサーの挿入」をクリック
- 絞り込みに使いたいフィールド（例：地域、商品カテゴリ）にチェック
- 「OK」でシート上にボタン形式のフィルターパネルが表示される
スライサーは複数のピボットテーブルに同時接続できます。「スライサーを右クリック → レポートの接続」から設定できます。1つのスライサーで複数のピボットテーブルを同時に絞り込めるため、ダッシュボード作成時に重宝します。
ピボットグラフの作成
ピボットテーブルのデータをそのままグラフ化できます。ピボットグラフはスライサーと連動するため、グラフ上で絞り込みを変えるたびにグラフも自動更新されます。
- ピボットテーブルを選択した状態で「ピボットテーブル分析」タブ → 「ピボットグラフ」
- グラフの種類を選択して「OK」
- 通常のグラフと同様にデザインやレイアウトを変更できる
データ更新時の「更新」操作
ピボットテーブルは元データが変わっても自動では更新されません。元データを修正したり新しい行を追加したりした後は、必ず手動で更新する必要があります。
- ピボットテーブル上で右クリック → 「更新」
- または「ピボットテーブル分析」タブ → 「更新」ボタン
- 複数のピボットテーブルをまとめて更新したい場合は「すべて更新」を使う
ソースデータの空白セル問題と対策
ピボットテーブルで集計が正しく行われないトラブルの多くは、元データに空白行・空白列・空白セルが混在していることが原因です。
- 値列に空白がある → 集計が「（空白）」として別扱いされる。対処：IFERRORや「0」で埋める
- ヘッダー行に空白列がある → その列以降がピボットテーブルに取り込まれない場合がある。対処：ヘッダーは必ず全列に名前をつける
- データの途中に空白行がある → ピボットテーブルの範囲がその行で切れる。対処：空白行を削除する
- 新しい行が元データに追加された → ピボットテーブルの参照範囲が古いままになる。対処：元データをテーブル形式（Ctrl+T）にしておくと自動で範囲が拡張される
元データを「テーブル形式」（Ctrl+Tで変換）にしておくのが長期的な管理の鍵です。テーブル形式にすると、行を追加した際にピボットテーブルの参照範囲が自動拡張されるため、「更新」ボタンを押すだけで最新の集計が得られます。新しいデータを追加するたびに範囲を手動で修正する手間がなくなります。
ソートとフィルターでデータを絞り込む
ソート（並べ替え）とフィルター（抽出）は、最も基本的なデータ分析操作です。関数やピボットテーブルを使わなくても、データの傾向をつかむ第一歩になります。まずこの2つを使いこなすことが、Excel分析の基礎体力になります。
複数条件でのソート
単純な1列ソートだけでなく、「部署でソートした後、同じ部署内はさらに売上の高い順で並べたい」という複数条件のソートも簡単に設定できます。
- データ範囲内のセルを選択
- 「データ」タブ → 「並べ替え」をクリック（ダイアログが開く）
- 第1キーとなる列と順序を設定
- 「レベルの追加」で第2キーを設定（さらに追加も可能）
- 「OK」で並べ替えが実行される
並べ替えダイアログでは「セルの色」や「フォントの色」を基準にソートすることも可能です。条件付き書式で色分けしたデータを、色順に並べ替えたいときに使えます。
色フィルターで特定の色だけ表示する
条件付き書式や手動の塗りつぶしで色分けされたデータから、特定の色のセルだけを抽出できます。「赤くなっているセル（警告状態のデータ）だけ一覧で見たい」といった場面で役立ちます。
- フィルターを設定した状態で、ヘッダーの▼をクリック
- 「色フィルター」にカーソルを合わせる
- 表示されたカラーパレットから絞り込みたい色を選択
フィルター中の集計にはSUBTOTAL関数を使う
フィルターで絞り込んだ状態で合計や件数を確認したい場合、通常のSUM関数やCOUNT関数では「非表示になっている行も含めた集計」になってしまいます。フィルター結果だけを集計したい場合はSUBTOTAL関数を使います。
=SUBTOTAL(集計方法の番号, 範囲)
- 9 → SUM（合計）
- 2 → COUNT（数値セルの件数）
- 3 → COUNTA（空白以外の件数）
- 1 → AVERAGE（平均）
例： =SUBTOTAL(9, C2:C1000) と書いておくと、フィルターをかけるたびに「表示されている行だけの合計」が自動で変わります。
集計行をシートの上部に固定しておき、フィルターを変えながら結果を確認する、という使い方が実務では定番です。
ソートはデータ全体の順序を変えたいとき（売上が高い順に並べたい等）、フィルターは特定条件のデータだけを表示したいとき（営業部だけ見たい等）に使います。両方を組み合わせることも可能です。また、フィルター中にSUBTOTALで集計すれば、「この条件のデータは何件で合計いくら」という分析が素早くできます。
Excelの分析ツール一覧と選び方
Excelには多くの分析機能が搭載されています。目的に応じて適切なツールを選ぶことで、効率的にデータから知見を引き出せます。
| ツール | 主な用途 | 難易度 |
|---|---|---|
| ピボットテーブル | クロス集計・要約 | 中級 |
| ソート・フィルター | 並べ替え・絞り込み | 初級 |
| SUMIFS / COUNTIFS | 条件付き集計 | 中級 |
| UNIQUE / FILTER | 動的な一覧・抽出（365） | 中級 |
| ゴールシーク | 目標値からの逆算 | 中級 |
| 統計関数（AVERAGE, MEDIAN等） | 平均・中央値・分散 | 初級 |
| 分析ツールアドイン | 回帰分析・ヒストグラム | 上級 |
SUMIFS関数で条件付き集計する
SUMIFS関数は、複数の条件を指定して合計を求める関数です。ピボットテーブルを使わなくても、特定の条件に合致するデータの合計を手軽に算出できます。レポートの集計セルに数式として埋め込んでおくと、データが更新されるたびに自動で計算し直してくれます。
=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], …)
- 合計範囲 … 合計を求めたい数値の列
- 条件範囲1 … 条件を判定する列
- 条件1 … 条件の値（テキストや数値）
基本的な活用例
- =SUMIFS(C2:C9, A2:A9, “東京”) → 地域が「東京」の売上合計
- =SUMIFS(C2:C9, B2:B9, “ノートPC”) → 商品が「ノートPC」の売上合計
- =SUMIFS(C2:C9, A2:A9, “東京”, B2:B9, “ノートPC”) → 東京のノートPCの売上
COUNTIFS・AVERAGEIFSも一緒に覚える
SUMIFS（合計）だけでなく、件数を数えるCOUNTIFSと平均を求めるAVERAGEIFSも構文がまったく同じで、用途に合わせて使い分けられます。
| 関数 | 計算内容 | 使用例 |
|---|---|---|
| SUMIFS | 条件に合う数値の合計 | 東京支店の今月の売上合計 |
| COUNTIFS | 条件に合うセルの件数 | 東京支店の今月の受注件数 |
| AVERAGEIFS | 条件に合う数値の平均 | 東京支店の今月の平均受注単価 |
| MAXIFS / MINIFS | 条件に合う最大値・最小値 | 東京支店で最も高額だった案件 |
日付範囲での集計（開始日〜終了日）
「3月1日から3月31日の売上合計を出したい」のように、日付範囲を条件にする場合は比較演算子を文字列として組み合わせます。
A列に日付、C列に売上が入っている場合：
- =SUMIFS(C:C, A:A, “>=”&DATE(2026,3,1), A:A, “<=”&DATE(2026,3,31))
- または開始日・終了日をセル（例：F1、F2）に入力して =SUMIFS(C:C, A:A, “>=”&F1, A:A, “<=”&F2)
セル参照にしておくと、F1・F2の日付を変えるだけで任意の期間の集計ができます。月次レポートの集計セルに使うと非常に便利です。
ワイルドカード（*）で部分一致検索
「東京」で始まるものだけ、「株式会社」が含まれるものだけ、という部分一致の条件も指定できます。その場合はワイルドカード（*）を使います。
- “東京*” → 「東京」で始まる値（東京支社、東京本社など）
- “*株式会社” → 「株式会社」で終わる値
- “*営業*” → 「営業」を含む値（東京営業部、営業1課など）
例： =SUMIFS(C:C, B:B, “*営業*”) → 会社名に「営業」が含まれる取引先の売上合計
ピボットテーブル vs SUMIFS — どちらを使うべきか
この2つは似たことができますが、状況によって使い分けるのがベストです。
| 観点 | ピボットテーブル | SUMIFS |
|---|---|---|
| 使いやすさ | ドラッグ&ドロップで直感的 | 関数の記述が必要 |
| 自動更新 | 手動で「更新」が必要 | 元データが変わると自動で再計算 |
| レイアウトの自由度 | ピボット形式に固定される | 任意の場所に集計値を配置できる |
| 探索的な分析 | 向いている（軸を素早く切り替えられる） | あまり向かない（都度数式を書く必要がある） |
| 固定レポートへの埋め込み | やや不便（デザインの制約がある） | 向いている（好きなセルに配置できる） |
「まずデータを探索する」ならピボットテーブル、「固定フォーマットのレポートに集計値を埋め込む」ならSUMIFS、という使い分けが基本です。
- COUNTIFS … 条件に合致するデータの件数を数える
- AVERAGEIFS … 条件に合致するデータの平均を求める
- MAXIFS / MINIFS … 条件に合致する最大値・最小値を求める
UNIQUE・FILTER関数で動的な分析をする（Excel 365）
Excel 365（Microsoft 365のExcel）では、スピル（Spill）と呼ばれる機能により、1つのセルに数式を書くだけで複数の結果が自動的に周囲のセルに広がる「動的配列関数」が使えるようになりました。その代表格がUNIQUE関数とFILTER関数です。ピボットテーブルを作らなくても、数式だけで動的な一覧・絞り込みができます。
UNIQUE・FILTER関数はExcel 365（Microsoft 365）およびExcel 2021以降で利用可能です。Excel 2019以前やGoogle スプレッドシートの場合は使えないことがあります。会社の環境に応じてご確認ください。
UNIQUE関数 — 重複を除いた一覧を自動生成する
UNIQUE関数は、指定した範囲から重複を除いた値の一覧を自動で作成します。「この列にどんな値が入っているか」を確認したいとき、手動でリストを作る必要がありません。
=UNIQUE(配列, [列方向], [重複なしのみ])
- 例： =UNIQUE(A2:A100) → A2:A100の重複を除いた一覧が表示される
- 結果は数式を入力したセルから下方向に自動で展開される（スピル）
- 元データに新しい値が追加されると、一覧も自動で更新される
ドロップダウンリストの選択肢を動的に管理したい場合にも活用できます。UNIQUE関数の結果範囲を、データの入力規則のリストソースに指定しておくと、元データに新しい値が追加されると選択肢も自動で増えます。
FILTER関数 — 条件に合うデータだけ抽出する
FILTER関数は、元データを変えずに条件に合う行だけを別の場所に抽出します。フィルター機能と似ていますが、FILTER関数は数式なので元データが更新されると自動で結果も更新されます。複数シートへのレポート出力にも使えます。
=FILTER(配列, 条件, [条件が偽の場合の値])
- 例： =FILTER(A2:D100, B2:B100=”東京”) → B列が「東京」の行だけを全列抽出
- 例： =FILTER(A2:D100, C2:C100>=100000) → C列が10万以上の行だけ抽出
- 条件が複数ある場合はAND条件に * を使う: =FILTER(A2:D100, (B2:B100=”東京”)*(C2:C100>=100000))
- OR条件には + を使う: =FILTER(A2:D100, (B2:B100=”東京”)+(B2:B100=”大阪”))
SORT関数と組み合わせて使う
FILTER関数とSORT関数を組み合わせると、「東京の案件を売上が高い順に並べた一覧」を数式1行で作れます。
=SORT(FILTER(A2:D100, B2:B100=”東京”), 3, -1)
FILTER関数の結果をSORT関数でラップし、3列目（売上）を降順（-1）に並べています。ネストするだけで高度な抽出・並べ替えが実現します。
- 「担当者ごとの案件リスト」を担当者シートに自動で表示したいとき
- 「今月締切のタスクだけ」をダッシュボードに常に表示しておきたいとき
- ドロップダウンの選択肢を元データから自動生成したいとき
- ピボットテーブルほど複雑ではないが、手動フィルターより自動化したいとき
分析用データの整理ルール
どんなに優秀な分析ツールを使っても、元データが整理されていなければ正しい結果は得られません。分析しやすいデータには以下の特徴があります。
- 1行目にヘッダー（列名）がある
- 1行が1レコード（1件のデータ）に対応している
- 空白行・空白列がデータの途中にない
- セルの結合がない
- 同じ列には同じ種類のデータが入っている
- セルを結合してレイアウトを整えている（ピボットテーブルが正しく動作しない）
- 同じ項目が複数列にまたがっている（例：1月、2月、3月が横に並ぶ形）
- 集計行がデータの途中に挿入されている
データ分析の次のステップ
本記事で紹介したツールを使いこなせるようになったら、以下のステップに進むことで分析力をさらに高められます。
- Power Queryでデータを自動整形 … 複数ファイルの統合や定期的なデータ取り込みを自動化
- Power Pivotで大規模データを分析 … 100万行を超えるデータの集計や、複数テーブルのリレーション設定
- ダッシュボードの作成 … ピボットテーブル + グラフ + スライサーを組み合わせたインタラクティブなレポート
Excelのデータ分析は、「ソート・フィルター」で概要をつかみ、「SUMIFS」で条件別の集計を行い、「ピボットテーブル」で多角的に分析する、という流れが基本です。Excel 365環境であればFILTER・UNIQUE関数も加えることで、さらに動的で自動化されたレポートが作れるようになります。まずはこの3〜4つのツールを使いこなすことから始めましょう。
はてなベース株式会社では、Excelを活用したデータ分析やレポート自動化のコンサルティングを提供しています。「データはあるが活用できていない」「分析レポートの作成に時間がかかりすぎる」など、お気軽にご相談ください。
