売上データやアンケート結果を項目別に見比べたいとき、Excelのクロス分析は情報を整理するために役立ちます。
担当者別、商品別、地域別などの複数条件を表の縦横に配置すると、単純な一覧表では気付きにくい傾向も確認しやすくなります。
特にピボットテーブルを使えば、数式を大量に入力しなくても、ドラッグ操作を中心に集計表を作成できます。
クロス分析では、行に比較したい分類、列にもう一つの分類、値に集計対象の数値を配置することが基本です。
元データは1行目を見出しにし、途中の空白行や結合セルを作らない状態で準備しましょう。
この記事では、ピボットテーブルを利用して項目別のクロス集計を作る手順、集計方法の変更、見やすい表に整える方法を順番に解説します。
ピボットテーブルによるクロス分析の作成手順
| 担当者 | 商品分類 | 地域 | 売上金額 |
|---|---|---|---|
| 田中 | 飲料 | 東京 | 12500 |
| 佐藤 | 食品 | 大阪 | 9800 |
| 田中 | 食品 | 東京 | 14200 |
それではまず、元データからクロス分析表を作成する手順について解説していきます。
売上金額のように数値を合計する項目を値へ、比較する分類を行と列へ入れると、担当者別の商品分類別売上を短時間で確認できます。
クロス分析に使える元データの整え方
ピボットテーブルを作成する前に、一覧表の形を整えることが重要です。
サンプルデータでは、1行目に担当者、商品分類、地域、売上金額という見出しを設定します。
1つの列には1種類の情報だけを入れることが、正確なクロス集計につながります。
たとえば担当者名と所属部署を同じセルへ混在させると、担当者別と部署別の両方で分析したい場合に扱いにくくなります。
途中に空白行があると、Excelがデータ範囲を正しく認識できないことがあります。
数値として集計する売上金額列には、文字列の「12,500円」ではなく数値の12500を入力し、表示形式で通貨や桁区切りを設定すると安心です。
元データをテーブル形式に変換しておくと、行を追加した際もピボットテーブルの対象範囲を管理しやすくなります。
【操作のポイント】元データの見出しは重複させず、空欄の見出しを作らないことが大切です。
挿入タブからピボットテーブルを作る操作
集計したい一覧表の中にある任意のセルを1つ選択します。
続いて、Excel上部の挿入タブを開き、ピボットテーブルを選択しましょう。
表示された作成画面では、テーブルまたは範囲に対象データの範囲が入っているかを確認します。
新規ワークシートを選べば、元データとは別のシートに集計表を作成できます。
既存のワークシートを選ぶ場合は、集計結果を置く先頭セルを指定します。
OKをクリックすると、左側に空のピボットテーブル、右側にピボットテーブルのフィールド一覧が表示されます。
フィールド一覧は、元データの1行目に設定した見出し名をもとに表示されます。
【操作のポイント】元データ内の1セルを選んでから開始すると、範囲選択の手間を抑えられます。
行列値へのフィールド配置
フィールド一覧の下部には、フィルター、列、行、値という4つの配置エリアがあります。
担当者を行へ、商品分類を列へ、売上金額を値へドラッグすると、担当者別かつ商品分類別の売上合計が表示されます。
この配置では縦方向に担当者、横方向に商品分類が並ぶため、担当者ごとの得意分野を比較しやすくなります。
値に売上金額を入れた時点で、通常は合計として集計されます。
クロス分析の見方は、行と列が交わるセルを読むことです。
たとえば田中行と食品列が交わるセルの数値は、田中さんが食品で上げた売上金額の合計を表します。
【操作のポイント】比較したい対象を行と列に分け、集計したい数値だけを値へ置きます。
行列フィールドによる項目別集計の考え方
| 担当者 | 飲料 | 食品 | 総計 |
|---|---|---|---|
| 田中 | 12500 | 14200 | 26700 |
| 佐藤 | 0 | 9800 | 9800 |
続いては、行と列に置く項目の選び方を確認していきます。
行と列は、単に表の向きを決めるだけではありません。
読み手が比較したい軸をどちらに置くかで、クロス集計表の理解しやすさが変わります。
行フィールドに配置する項目
行フィールドには、縦に並べて確認したい分類を設定します。
担当者、部署、顧客名、都道府県、年度など、項目数が比較的多い分類は行に配置すると見やすい傾向があります。
横幅が限られたワークシートでも、行方向なら多くの項目を確認しやすいためです。
担当者を行に置くと、誰がどの程度の売上を作っているかを一覧で把握できます。
行フィールドは、分析の主役となる分類を置く場所と考えると選びやすくなります。
同じエリアに地域、担当者の順で複数項目を置けば、地域ごとに担当者を掘り下げる階層構造も作れます。
【操作のポイント】項目数が多い分類や、詳細までたどりたい分類は行に配置すると扱いやすくなります。
列フィールドに配置する項目
列フィールドには、横に並べて差を比べたい分類を設定します。
月、四半期、商品分類、評価区分など、項目数が多すぎない分類が列に向いています。
担当者を行、月を列にすると、個人ごとの月別売上推移を横方向で追えます。
商品分類を列に置けば、各担当者がどの商品に強いかを比較できます。
列数が増えすぎると横スクロールが必要になるため、必要に応じてフィルターも併用しましょう。
月別分析で日付が細かく並びすぎる場合は、日付を右クリックしてグループ化し、月や四半期単位にまとめる方法も便利です。
【操作のポイント】列には、横に並べても見渡せる程度の分類数を持つ項目を選びます。
フィルターフィールドによる対象範囲の絞り込み
フィルターフィールドは、表全体の対象を限定したい場合に使います。
たとえば地域をフィルターに置けば、東京だけ、大阪だけというように選択地域のクロス分析へ切り替えられます。
担当者と商品分類の配置を変えずに分析対象だけを変更できるため、報告書作成にも便利です。
フィルターは元データを削除せず、表示する集計結果だけを絞り込む機能です。
複数の地域や部門を比較したい場合は、フィルターの複数項目の選択を有効にします。
部門、年度、地域をフィルターとして用意すると、1つのピボットテーブルをさまざまな切り口で再利用できます。
【操作のポイント】毎回見たい条件が変わる項目は、行や列ではなくフィルターへ置くと表を保ちやすくなります。
値フィールド設定による集計方法の変更
| 商品分類 | 売上金額の合計 | 件数 | 平均単価 |
|---|---|---|---|
| 飲料 | 12500 | 1 | 12500 |
| 食品 | 24000 | 2 | 12000 |
続いては、値フィールドで合計や件数などを切り替える方法について解説していきます。
同じ元データでも、合計、件数、平均、最大値、構成比のどれを見るかで分析結果の意味は変わります。
合計と件数を使い分ける場面
売上金額、数量、工数のような数値項目は、値へ置くと通常は合計になります。
売上規模を調べるなら合計、注文数や回答数を調べるなら件数が適しています。
値エリアにある売上金額の下向き矢印を選び、値フィールドの設定を開くと集計方法を変更できます。
集計の方法でデータの個数を選択すると、売上金額が入力されたレコード数を数えられます。
数値が文字列として入力されている場合、Excelが合計ではなく個数を初期設定することがあります。
意図しない個数表示になった場合は、元データの数値形式と空白セルを確認しましょう。
【操作のポイント】金額の規模は合計、発生回数や回答数は個数として比較します。
平均最大値最小値による傾向の把握
平均を選ぶと、担当者別や商品別の平均売上金額を確認できます。
高額な1件が全体の合計を大きくしている場合でも、平均を併せて見ると実態を判断しやすくなります。
最大値は最高売上、最小値は最低売上を確認したい場合に便利です。
たとえば店舗別の最大値を比較すれば、各店舗で最も高額だった注文の水準が分かります。
合計だけでは偏りを見落とすことがあるため、平均や件数を並べて分析する視点が重要です。
同じ売上金額フィールドを値エリアへ複数回ドラッグすれば、合計と平均を1つのクロス集計表に同時表示できます。
【操作のポイント】同じ数値項目を複数の集計方法で追加し、量と質の両面を確認します。
構成比表示による比較
ピボットテーブルでは、値の表示方法を変更して構成比を表示できます。
値フィールドの設定ではなく、値の表示方法から総計に対する比率、行集計に対する比率、列集計に対する比率を選択します。
たとえば担当者別の商品構成を見たい場合は、行集計に対する比率を選ぶと便利です。
各担当者の売上合計を100パーセントとして、飲料と食品が何パーセントずつを占めるかが分かります。
構成比の基本的な考え方は、対象セルの値を基準となる合計値で割る計算です。
構成比 ＝ 個別の売上金額 ÷ 行または列または総計の売上金額
通常のセルで計算するなら、売上金額がD2、全体合計がD10の場合は、=D2/$D$10 の数式で割合を求められます。
【操作のポイント】金額差が大きいデータほど、合計と構成比を切り替えて読み解くことが有効です。
レイアウト設定による見やすいクロス集計表
| 地域 | 担当者 | 飲料 | 食品 |
|---|---|---|---|
| 東京 | 田中 | 12500 | 14200 |
| 大阪 | 佐藤 | 0 | 9800 |
続いては、作成したピボットテーブルを読みやすく整える設定を確認していきます。
見やすいクロス分析表では、数値の表示形式、総計の扱い、並べ替え、フィルターを目的に合わせて調整します。
表形式レイアウトへの変更
複数の項目を行エリアに入れると、初期状態ではコンパクト形式で表示されることがあります。
コンパクト形式は省スペースですが、地域と担当者が同じ列に表示されるため、データを他の表へ利用したい場合には不便なことがあります。
ピボットテーブルを選択し、デザインタブのレポートレイアウトから表形式で表示を選びましょう。
表形式にすると、行フィールドごとに列が分かれ、通常の一覧表に近い見た目になります。
必要なら、すべてのアイテムラベルを繰り返す設定も利用できます。
この設定により、地域名などが各行に繰り返し表示され、フィルターやコピー後のデータ処理がしやすくなります。
【操作のポイント】他の関数や資料へ転記する予定がある場合は、表形式で表示すると扱いやすくなります。
並べ替えと数値フィルターの活用
売上合計を大きい順に並べ替えると、成果の高い担当者や商品分類をすぐに確認できます。
行ラベルの下向き矢印からその他の並べ替えオプションを開き、降順と売上金額の合計を選択します。
数値フィルターでは、指定した金額以上の担当者だけを残すことも可能です。
たとえば売上金額の合計が10000以上という条件を指定すれば、重点確認が必要な対象を絞り込めます。
並べ替えやフィルターは、元の一覧データを並べ替えるのではなく、集計結果の表示順を変える操作です。
月別の列がある場合は、特定の月だけを表示して比較することもできます。
【操作のポイント】報告目的に合わせ、上位を確認するなら降順、基準未満を探すなら数値フィルターを使います。
Excel画面での表示形式設定
ここでは、売上金額の値フィールドを通貨表示へ変更する操作画面のイメージを確認していきます。
値フィールドの設定画面で数値の表示形式を選ぶと、通貨、数値、パーセンテージなどを設定できます。
金額なら桁区切りを入れ、必要に応じて通貨記号を設定すると、読者が数値の単位を誤解しにくくなります。
【操作のポイント】数値の書式設定はセル単位ではなく、値フィールドの設定から行うと更新後も維持されます。
データ更新とエラー対策の基本
| 更新前の売上合計 | 追加データ | 更新後の売上合計 |
|---|---|---|
| 36500 | 食品 7500 | 44000 |
続いては、データを追加した後の更新方法と、集計が期待通りにならない場合の確認点を解説していきます。
ピボットテーブルは元データを直接監視しているわけではないため、データ変更後には更新操作が必要です。
更新コマンドによる最新データの反映
元データに新しい売上行を追加しても、作成済みのピボットテーブルは自動で数値を書き換えない場合があります。
ピボットテーブル内の任意セルを右クリックし、更新を選択すると最新の内容が反映されます。
リボンのピボットテーブル分析タブにある更新ボタンから実行することも可能です。
集計結果が古いと感じたら、まず更新操作を行うことが基本です。
元データをExcelテーブルにしていれば、追加行も対象範囲へ含まれやすくなります。
通常範囲で作った場合は、新しい行がピボットテーブルの元データ範囲に入っているかも確認しましょう。
【操作のポイント】データを追加、修正、削除した後は、報告前に必ず更新を実行します。
集計結果が個数になる原因
売上金額を値へ配置したのに合計ではなく個数になる場合、数値列に文字列が含まれている可能性があります。
セル先頭のアポストロフィ、全角数字、不要な空白、円という文字を含む入力などが主な原因です。
元データの該当列を選び、エラー表示やセルの配置を確認しましょう。
文字列になった数値は、数値へ変換した後にピボットテーブルを更新すると、合計として扱えるようになります。
見た目が数字でも、Excel内部では文字列として保存されているケースがあります。
データの型を統一することは、クロス分析の精度を保つための基本作業です。
【操作のポイント】合計ではなく個数が出たときは、値フィールド設定と元データの数値形式を確認します。
空白表示と総計設定の調整
組み合わせによって該当データがないセルは、空白や0として表示されることがあります。
空白セルに0を表示したい場合は、ピボットテーブルのオプションから空のセルに表示する値を設定できます。
ただし、0件と未入力を区別したい分析では、むやみに0表示へ変えない方がよい場合もあります。
総計が不要な報告書では、デザインタブの総計から行と列の総計をオフにできます。
反対に、全体規模を比較するためには総計が重要な判断材料になります。
空白、0、総計は表の見た目だけでなく、データの意味を伝える要素です。
【操作のポイント】0表示と総計表示は、閲覧者が何を判断する表なのかを考えて設定します。
まとめ ピボットテーブルで行うエクセルのクロス分析方法
Excelでクロス分析を行う際は、1行目に見出しを持つ整った一覧データを用意し、挿入タブからピボットテーブルを作成します。
行には主な比較対象、列には横方向で比較しやすい分類、値には売上金額や数量などの集計対象を配置することが基本です。
担当者別、商品別、地域別、月別といった切り口を入れ替えるだけで、同じデータから多様な分析結果を得られます。
値フィールドの設定では、合計だけでなく個数、平均、最大値、構成比も活用できます。
数値の表示形式、並べ替え、フィルター、表形式レイアウトを整えると、分析結果を報告資料として使いやすくなります。
元データを変更した後は更新を行い、合計が個数になってしまう場合は数値が文字列になっていないか確認しましょう。
ピボットテーブルの配置と集計方法を理解すれば、複雑に見えるクロス集計も、Excelで効率よく作成できるようになります。
