Excel クロス集計 やり方

「Excelでクロス集計をしたいけど、どうすればいいかわからない…」そんな風に思っていませんか？ 部署ごとの売上や、商品別の販売数など、複数の項目を組み合わせて集計したい場面は多いですよね。手作業で集計するのは時間もかかるし、ミスも起こりやすい。そこで今回は、Excelのクロス集計のやり方を、初心者の方にもわかりやすく解説します。

Excel クロス集計のやり方をマスターしてデータ分析を効率化しよう

クロス集計とは？なぜExcelでクロス集計をするのか

クロス集計とは、複数の項目を組み合わせて集計し、データの傾向や関連性を分析する手法です。例えば、ある商品の売上データを、地域別・年齢層別に集計することで、どの地域でどの年齢層に売れているのかを把握できます。Excelでクロス集計を行うメリットは、なんといってもその手軽さです。特別なソフトウェアを導入する必要はなく、普段使っているExcelで簡単に集計できます。また、ピボットテーブルを使えば、集計結果を様々な角度から分析できます。筆者の経験では、クロス集計は、売上分析だけでなく、顧客分析、アンケート分析など、様々な分野で活用されています。

• データの傾向や関連性を把握できる
• Excelで手軽に集計できる
• ピボットテーブルで様々な角度から分析できる

クロス集計に必要なデータ形式とは

クロス集計を行うためには、適切なデータ形式でデータが入力されている必要があります。具体的には、以下のような形式です。

• 1行が1つのレコードを表す
• 各列が1つの項目を表す
• 項目名は1行目に入力する

例えば、売上データの場合、1行が1つの売上記録を表し、列には、日付、商品名、顧客名、地域、売上金額などの項目が入力されます。経理部の月次締めで、部門別・勘定科目別にデータを集計する場合も、同様の形式でデータを用意する必要があります。もし、データがこの形式になっていない場合は、Excelの関数や機能を使って、データを整形する必要があります。

ピボットテーブルを使ったクロス集計の基本操作

ピボットテーブルの作成手順

Excelでクロス集計を行う最も一般的な方法は、ピボットテーブルを使うことです。ピボットテーブルを使えば、ドラッグ＆ドロップの簡単な操作で、集計結果を自由にカスタマイズできます。ここでは、ピボットテーブルの作成手順をステップバイステップで解説します。

1. Excelでデータ範囲を選択します。
2. 「挿入」タブの「ピボットテーブル」をクリックします。
3. 「ピボットテーブルの作成」ダイアログボックスが表示されるので、データ範囲とピボットテーブルの配置場所を指定します。
4. 「OK」をクリックすると、ピボットテーブルが作成されます。

ピボットテーブルを作成する際、データ範囲を正しく選択することが重要です。データ範囲に空白の行や列が含まれていると、ピボットテーブルが正しく作成されない場合があります。ある製造業の営業部では、ピボットテーブルのデータ範囲に誤って合計行を含めてしまい、集計結果が大きくずれてしまったという事例がありました。

行、列、値フィールドの設定方法

ピボットテーブルを作成したら、行、列、値フィールドを設定します。行フィールドには、集計したい項目のうち、行方向に表示する項目を設定します。列フィールドには、列方向に表示する項目を設定します。値フィールドには、集計する数値データが入力されている項目を設定します。例えば、売上データを地域別・商品別に集計する場合、行フィールドに「地域」、列フィールドに「商品名」、値フィールドに「売上金額」を設定します。

1. ピボットテーブルのフィールドリストから、行フィールドに設定したい項目をドラッグ＆ドロップします。
2. 同様に、列フィールドと値フィールドを設定します。

値フィールドには、合計、平均、最大値、最小値など、様々な集計方法を設定できます。集計方法を変更するには、値フィールドをクリックし、「値フィールドの設定」を選択します。研修で教えていると、値フィールドの設定を理解せずに、とりあえず合計を選んでしまう人が多いです。データの種類や分析目的に応じて、適切な集計方法を選択することが重要です。

クロス集計結果の表示形式を変更する方法

ピボットテーブルの表示形式は、自由にカスタマイズできます。例えば、数値の表示形式を変更したり、行や列の並び順を変更したり、フィルターを追加したりできます。

• 数値の表示形式を変更するには、値フィールドをクリックし、「値フィールドの設定」を選択し、「表示形式」をクリックします。
• 行や列の並び順を変更するには、行または列フィールドをクリックし、「並べ替え」を選択します。
• フィルターを追加するには、行または列フィールドをクリックし、「フィルター」を選択します。

クロス集計結果の表示形式を工夫することで、データの傾向をよりわかりやすく把握できます。例えば、売上金額を降順に並べ替えたり、特定の地域のみを表示したりすることで、売上の上位地域や特定の地域の売上状況を簡単に把握できます。

Excelクロス集計の実用例：３つの業務シナリオ

売上分析：地域別・商品別の売上状況を把握する

売上分析は、クロス集計の最も一般的な活用例の一つです。地域別・商品別の売上状況を把握することで、売上の傾向や課題を明確にできます。例えば、ある地域で特定の商品の売上が伸び悩んでいる場合、その原因を調査し、対策を講じることができます。また、売上の上位地域や商品を特定し、その成功要因を分析することで、他の地域や商品にも応用できる可能性があります。あるアパレル企業では、クロス集計を使って、店舗別・商品別の売上状況を分析し、売れ筋商品を各店舗に重点的に配置することで、売上を大幅に向上させました。

• 売上の傾向や課題を明確にできる
• 売上の上位地域や商品を特定できる
• 売上改善のための対策を講じることができる

顧客分析：顧客属性別の購買傾向を分析する

顧客分析も、クロス集計の重要な活用例です。顧客属性（年齢、性別、居住地など）別の購買傾向を分析することで、ターゲット顧客を明確にし、効果的なマーケティング戦略を立案できます。例えば、ある年齢層の顧客が特定の商品の購入頻度が高い場合、その年齢層向けのキャンペーンを実施することで、売上を向上させることができます。また、顧客属性と購買履歴を組み合わせることで、顧客のニーズをより深く理解し、パーソナライズされたサービスを提供できます。あるECサイトでは、クロス集計を使って、顧客属性別の購買傾向を分析し、顧客一人ひとりに最適化された商品レコメンデーションを提供することで、顧客満足度と売上を向上させました。

• ターゲット顧客を明確にできる
• 効果的なマーケティング戦略を立案できる
• 顧客のニーズをより深く理解できる

アンケート分析：回答者の属性と回答内容の関係性を分析する

アンケート分析も、クロス集計の有効な活用例です。回答者の属性（年齢、性別、職業など）と回答内容の関係性を分析することで、回答者の意見やニーズをより深く理解できます。例えば、ある年齢層の回答者が特定の意見に賛成している場合、その背景にある理由を調査し、政策やサービスの改善に役立てることができます。また、回答者の属性と回答内容を組み合わせることで、回答者の満足度や不満点を明確にし、改善策を講じることができます。ある自治体では、クロス集計を使って、住民アンケートの回答を分析し、住民のニーズに合った行政サービスを提供することで、住民満足度を向上させました。

• 回答者の意見やニーズをより深く理解できる
• 政策やサービスの改善に役立てることができる
• 回答者の満足度や不満点を明確にできる

クロス集計の応用パターン：関数との組み合わせでさらに高度な分析を

COUNTIF関数と組み合わせる：条件に合致するデータの個数を集計する

COUNTIF関数は、指定した条件に合致するセルの個数を数える関数です。クロス集計と組み合わせることで、特定の条件に合致するデータの個数を、さらに詳細に分析できます。例えば、地域別・商品別に、売上金額が10万円以上のデータの個数を集計することができます。これにより、売上の上位地域や商品を特定するだけでなく、売上の安定性や成長性を評価できます。

数式例：`=COUNTIF(範囲,条件)`

ある家電量販店では、COUNTIF関数とクロス集計を組み合わせて、店舗別・商品別に、返品率が5%以上のデータの個数を集計しました。その結果、特定の店舗で特定の商品の返品率が高いことが判明し、その原因を調査したところ、商品の品質に問題があることがわかりました。その後、商品の品質を改善したことで、返品率を大幅に低下させることができました。

SUMIF関数と組み合わせる：条件に合致するデータの合計を計算する

SUMIF関数は、指定した条件に合致するセルの合計を計算する関数です。クロス集計と組み合わせることで、特定の条件に合致するデータの合計を、さらに詳細に分析できます。例えば、地域別・商品別に、売上金額が10万円以上のデータの合計を計算することができます。これにより、売上の上位地域や商品を特定するだけでなく、売上の貢献度を評価できます。

数式例：`=SUMIF(範囲,条件,合計範囲)`

ある食品メーカーでは、SUMIF関数とクロス集計を組み合わせて、顧客属性別・商品別に、購入金額が5000円以上のデータの合計を計算しました。その結果、特定の顧客属性の顧客が特定の商品の購入金額が高いことが判明し、その顧客属性向けのキャンペーンを実施したところ、売上を大幅に向上させることができました。

AVERAGEIF関数と組み合わせる：条件に合致するデータの平均を計算する

AVERAGEIF関数は、指定した条件に合致するセルの平均を計算する関数です。クロス集計と組み合わせることで、特定の条件に合致するデータの平均を、さらに詳細に分析できます。例えば、地域別・商品別に、顧客満足度が4点以上のデータの平均を計算することができます。これにより、顧客満足度の高い地域や商品を特定するだけでなく、顧客満足度の向上に向けた施策を検討できます。

数式例：`=AVERAGEIF(範囲,条件,平均範囲)`

あるホテルでは、AVERAGEIF関数とクロス集計を組み合わせて、宿泊者の属性別・客室タイプ別に、顧客満足度が4点以上のデータの平均を計算しました。その結果、特定の宿泊者の属性が特定の客室タイプの顧客満足度が高いことが判明し、その客室タイプを重点的にプロモーションすることで、客室稼働率を向上させることができました。

クロス集計でありがちなエラーとトラブルシューティング

ピボットテーブルが正しく作成されない場合の原因と対策

ピボットテーブルが正しく作成されない場合、いくつかの原因が考えられます。最も一般的な原因は、データ範囲の指定ミスです。データ範囲に空白の行や列が含まれていると、ピボットテーブルが正しく作成されない場合があります。また、データ形式が整っていない場合も、ピボットテーブルが正しく作成されない場合があります。例えば、項目名が1行目に入力されていない場合や、数値データが文字列として入力されている場合などです。

• データ範囲を正しく指定する
• データ形式を整える
• ピボットテーブルのキャッシュをクリアする

ピボットテーブルのキャッシュをクリアすることで、問題が解決する場合があります。ピボットテーブルを右クリックし、「ピボットテーブルオプション」を選択し、「データ」タブの「ファイルを開くときにデータを更新する」のチェックを外して、再度チェックを入れ直します。実務でよく見かけるのは、日付データが文字列として認識されてしまい、グループ化がうまくいかないケースです。この場合は、日付データを日付形式に変換する必要があります。

集計結果が意図しない値になる場合の確認ポイント

集計結果が意図しない値になる場合、いくつかの確認ポイントがあります。まず、値フィールドの集計方法が正しいか確認します。合計、平均、最大値、最小値など、適切な集計方法を選択する必要があります。また、フィルターが設定されている場合、フィルター条件が正しいか確認します。意図しないフィルター条件が設定されていると、集計結果が誤って表示される場合があります。さらに、データに誤りがないか確認します。誤ったデータが含まれていると、集計結果が誤って表示される場合があります。

• 値フィールドの集計方法を確認する
• フィルター条件を確認する
• データに誤りがないか確認する

ピボットテーブルの更新がうまくいかない場合の対処法

ピボットテーブルの更新がうまくいかない場合、いくつかの対処法があります。まず、データソースが変更されていないか確認します。データソースが変更されている場合、ピボットテーブルを更新する必要があります。また、ピボットテーブルのキャッシュが破損している場合、ピボットテーブルを再作成する必要があります。さらに、Excelのバージョンが古い場合、最新バージョンにアップデートすることで、問題が解決する場合があります。

• データソースを確認する
• ピボットテーブルを再作成する
• Excelを最新バージョンにアップデートする

ピボットテーブルの更新がうまくいかない場合、エラーメッセージが表示されることがあります。エラーメッセージの内容を確認し、適切な対処法を講じることで、問題を解決できます。

クロス集計と他の関数・機能との使い分け

VLOOKUP関数との比較：どのような場合にクロス集計を使うべきか

VLOOKUP関数は、指定した検索値に基づいて、別の範囲から対応する値を検索する関数です。クロス集計とVLOOKUP関数は、どちらもデータを分析するためのツールですが、それぞれ得意な用途が異なります。VLOOKUP関数は、特定の値を検索するのに適していますが、クロス集計は、複数の項目を組み合わせて集計し、データの傾向や関連性を分析するのに適しています。例えば、顧客IDに基づいて顧客情報を検索する場合はVLOOKUP関数を使い、地域別・商品別の売上状況を分析する場合はクロス集計を使うのが適切です。 Microsoft公式: VLOOKUP関数

• VLOOKUP関数は特定の値を検索するのに適している
• クロス集計はデータの傾向や関連性を分析するのに適している

FILTER関数との比較：データの抽出と集計を使い分ける

FILTER関数は、指定した条件に合致するデータを抽出する関数です。クロス集計とFILTER関数は、どちらもデータを分析するためのツールですが、それぞれ得意な用途が異なります。FILTER関数は、特定の条件に合致するデータを抽出するのに適していますが、クロス集計は、複数の項目を組み合わせて集計し、データの傾向や関連性を分析するのに適しています。例えば、売上金額が10万円以上のデータを抽出する場合はFILTER関数を使い、地域別・商品別の売上状況を分析する場合はクロス集計を使うのが適切です。FILTER関数はExcel 365以降で利用可能です。

• FILTER関数は特定の条件に合致するデータを抽出するのに適している
• クロス集計はデータの傾向や関連性を分析するのに適している

ピボットグラフとの連携：集計結果を視覚的に表現する

ピボットグラフは、ピボットテーブルの集計結果を視覚的に表現するグラフです。ピボットテーブルとピボットグラフを連携させることで、データの傾向をよりわかりやすく把握できます。例えば、地域別・商品別の売上状況をピボットテーブルで集計し、その結果をピボットグラフで表示することで、売上の上位地域や商品を一目で把握できます。ピボットグラフの種類も豊富で、棒グラフ、折れ線グラフ、円グラフなど、様々なグラフを選択できます。データの種類や分析目的に応じて、適切なグラフを選択することが重要です。

• データの傾向をよりわかりやすく把握できる
• 様々なグラフを選択できる
• インタラクティブな操作で分析できる

業務効率化のヒント：クロス集計をさらに便利にするテクニック

ショートカットキーを活用して作業時間を短縮する

Excelには、様々なショートカットキーが用意されています。ショートカットキーを活用することで、マウス操作を減らし、作業時間を大幅に短縮できます。例えば、ピボットテーブルを作成するには、「Alt + N + V」というショートカットキーを使います。また、ピボットテーブルを更新するには、「Alt + A + R」というショートカットキーを使います。これらのショートカットキーを覚えることで、クロス集計の作業時間を大幅に短縮できます。

• Alt + N + V：ピボットテーブルを作成する
• Alt + A + R：ピボットテーブルを更新する
• Ctrl + Shift + *：データ範囲を選択する

マクロを使ってクロス集計を自動化する

マクロとは、Excelの操作を自動化するための機能です。マクロを使うことで、クロス集計の作業を自動化し、作業時間を大幅に短縮できます。例えば、毎月同じ形式のデータを使ってクロス集計を行う場合、マクロを作成することで、データを取り込み、ピボットテーブルを作成し、集計結果を表示するまでの一連の作業を自動化できます。マクロを作成するには、VBA（Visual Basic for Applications）というプログラミング言語を使う必要がありますが、Excelには、マクロの記録機能が搭載されており、簡単な操作であれば、プログラミングの知識がなくてもマクロを作成できます。

• データ取り込みを自動化できる
• ピボットテーブル作成を自動化できる
• 集計結果表示を自動化できる

Power Queryを使って外部データを取り込む

Power Queryは、Excelに搭載されているデータ変換ツールです。Power Queryを使うことで、様々な外部データソース（CSVファイル、テキストファイル、データベースなど）からデータを簡単に取り込み、整形できます。例えば、複数のCSVファイルに分割された売上データを、Power Queryを使って1つのテーブルに結合し、そのデータをピボットテーブルで集計することができます。Power Queryを使うことで、データ収集から集計までの作業を効率化できます。 Microsoft公式: Power Query

• 様々な外部データソースからデータを取り込める
• データ整形を簡単に行える
• データ収集から集計までの作業を効率化できる

クロス集計における競合製品との違い：Googleスプレッドシートとの比較

Googleスプレッドシートのピボットテーブルとの違い

ExcelとGoogleスプレッドシートは、どちらも表計算ソフトとして広く利用されていますが、ピボットテーブルの機能にはいくつかの違いがあります。Excelのピボットテーブルは、Googleスプレッドシートのピボットテーブルよりも、高度な機能が豊富です。例えば、Excelのピボットテーブルには、Power Pivotという機能があり、大量のデータを高速に処理できます。また、Excelのピボットテーブルには、VBAを使って、ピボットテーブルの操作を自動化できます。一方、Googleスプレッドシートのピボットテーブルは、Excelのピボットテーブルよりも、操作が簡単で、初心者にも使いやすいというメリットがあります。また、Googleスプレッドシートは、クラウド上で利用できるため、複数人で共同作業を行うのに適しています。ある中小企業の営業部では、ExcelとGoogleスプレッドシートを併用しており、個人での分析にはExcelを使い、チームでの共有や共同作業にはGoogleスプレッドシートを使っています。

• Excelのピボットテーブルは高度な機能が豊富
• Googleスプレッドシートのピボットテーブルは操作が簡単
• Googleスプレッドシートはクラウド上で利用できる

LibreOffice Calcとの比較：互換性と機能の違い

LibreOffice Calcは、無料のオフィススイートであるLibreOfficeに含まれる表計算ソフトです。LibreOffice Calcのピボットテーブルは、Excelのピボットテーブルと互換性がありますが、いくつかの機能に違いがあります。例えば、LibreOffice Calcのピボットテーブルには、ExcelのピボットテーブルにあるPower Pivotのような高度な機能はありません。また、LibreOffice Calcのピボットテーブルは、Excelのピボットテーブルよりも、操作が若干異なります。しかし、LibreOffice Calcは、無料で利用できるため、コストを抑えたい場合に適しています。総務部では、Excelのライセンス費用を削減するために、一部の部署でLibreOffice Calcを導入することを検討しています。

• LibreOffice Calcは無料で利用できる
• Excelのピボットテーブルと互換性がある
• 高度な機能はExcelの方が豊富

Excelバージョン別の対応：2016/2019/365での違い

Excel 2016/2019/365のピボットテーブル機能の比較

Excelは、2016、2019、365と、様々なバージョンがリリースされていますが、ピボットテーブルの機能には、バージョンによっていくつかの違いがあります。Excel 365は、常に最新の機能が利用できるため、最も機能が豊富です。例えば、Excel 365には、動的配列数式という機能があり、ピボットテーブルの集計結果を自動的に更新できます。一方、Excel 2016や2019には、動的配列数式のような最新の機能はありません。しかし、Excel 2016や2019でも、ピボットテーブルの基本的な機能は利用できます。経理部の月次締めでは、Excel 2016を使っている社員もいれば、Excel 365を使っている社員もいますが、ピボットテーブルの基本的な操作は共通であるため、特に問題は発生していません。 Microsoft公式: Excel の仕様と制限

• Excel 365は常に最新の機能が利用できる
• Excel 2016/2019でも基本的な機能は利用できる
• バージョンによって利用できる機能が異なる

バージョンによる操作性の違いと注意点

Excelのバージョンによって、ピボットテーブルの操作性も若干異なります。例えば、Excel 2016では、ピボットテーブルのフィールドリストが画面の右側に表示されますが、Excel 365では、ピボットテーブルのフィールドリストが画面の左側に表示されます。また、Excel 2016では、ピボットテーブルのスタイルが限られていますが、Excel 365では、ピボットテーブルのスタイルが豊富に用意されています。バージョンによる操作性の違いを理解することで、よりスムーズにクロス集計を行うことができます。

• フィールドリストの表示位置が異なる
• ピボットテーブルのスタイルが異なる
• バージョンによって操作性が異なる

クロス集計に関する補足FAQ

Q: ピボットテーブルの集計結果を別のシートにコピーできますか？

A: はい、できます。ピボットテーブルの集計結果を選択し、コピーして、別のシートに貼り付けることができます。ただし、貼り付ける際には、「値」として貼り付けることをお勧めします。数式として貼り付けると、元のピボットテーブルのデータソースに依存してしまうため、別のシートでピボットテーブルを更新すると、貼り付けた集計結果も更新されてしまいます。

Q: ピボットテーブルの集計結果をグラフにできますか？

A: はい、できます。ピボットテーブルを選択し、「挿入」タブの「グラフ」をクリックすると、ピボットテーブルの集計結果をグラフにすることができます。グラフの種類も豊富で、棒グラフ、折れ線グラフ、円グラフなど、様々なグラフを選択できます。データの種類や分析目的に応じて、適切なグラフを選択することが重要です。

Q: ピボットテーブルのデータソースを変更できますか？

A: はい、できます。ピボットテーブルを選択し、「ピボットテーブル分析」タブの「データソースの変更」をクリックすると、ピボットテーブルのデータソースを変更できます。データソースを変更する際には、新しいデータソースが、元のデータソースと同じ形式であることを確認してください。データ形式が異なると、ピボットテーブルが正しく表示されない場合があります。

Q: クロス集計の結果を印刷する際の注意点は？

A: クロス集計の結果を印刷する際には、印刷範囲を適切に設定することが重要です。印刷範囲が広すぎると、余計な空白が印刷されてしまいますし、印刷範囲が狭すぎると、集計結果の一部が印刷されない場合があります。また、印刷プレビューで、印刷結果を確認し、必要に応じて、印刷設定を調整することをお勧めします。

Q: ピボットテーブルのデザインを変更するには？

A: ピボットテーブルのデザインは、「デザイン」タブで変更できます。ピボットテーブルのスタイルを変更したり、行や列の見出しの表示形式を変更したり、縞模様の表示/非表示を切り替えたりできます。ピボットテーブルのデザインを工夫することで、集計結果をより見やすくすることができます。

明日からの実務に取り入れるための３つのステップ

ここまで、Excelのクロス集計のやり方について詳しく解説してきました。最後に、明日からの実務にクロス集計を取り入れるための３つのステップを紹介します。

1. まず、手元にあるデータで、クロス集計を試してみましょう。
2. 次に、クロス集計の結果を分析し、業務改善に役立ててみましょう。
3. 最後に、クロス集計のスキルを向上させ、より高度な分析に挑戦してみましょう。

クロス集計は、データ分析の強力な武器になります。基本を押さえれば、応用は自然と広がるでしょう。