【Excel】ピボットテーブルで空白行が大量に表示される!Excelのソースデータの欠損を修正する方法

ピボットテーブルで意図しない空白行が大量に表示され、レポートが見づらくなった経験はないでしょうか。これは、Excelのソースデータに欠損や予期せぬ空白セルが存在することが原因で発生します。レポート作成の効率を著しく低下させるこの問題を、この記事で根本から解決しましょう。

本記事では、ピボットテーブルに空白行が表示される原因を特定し、ソースデータの欠損を修正するための具体的な手順を解説します。これにより、見やすく正確なピボットテーブルを作成できるようになります。

ピボットテーブルに空白行が表示される原因

ピボットテーブルで空白行が大量に表示される主な原因は、元となるExcelのソースデータに、空白のセルや欠損値が含まれていることです。ピボットテーブルは、ソースデータの各行を個別のレコードとして扱います。そのため、データが存在しない行は、ピボットテーブル上で空白行として認識されてしまうのです。

具体的には、以下のようなケースが考えられます。データ入力時のミス、外部から取り込んだデータの不整合、あるいは意図的に空欄になっている行などが該当します。これらの空白データがピボットテーブルの集計範囲に含まれることで、予期せぬ空白行が発生します。

ピボットテーブルの空白行を解消するソースデータ修正手順

ピボットテーブルの空白行を解消するには、まずソースデータを確認し、欠損箇所を特定・修正する必要があります。ここでは、そのための具体的な手順を解説します。

1. Excelシートのデータ範囲を確認する
   ピボットテーブルを作成する前に、ソースデータとして使用するExcelシートのデータ範囲を正しく指定しているか確認します。シート全体を選択するか、データが存在する範囲を正確に指定することが重要です。
2. 空白セルを検索・置換する
   ソースデータに空白セルが存在する場合、これらを特定して修正します。以下の手順で検索・置換機能を使用します。まず、ソースデータ範囲を選択します。次に、「ホーム」タブの「検索と選択」から「条件付き書式」→「セルの値」を選択し、「空白」と入力して「OK」をクリックします。これにより、空白セルがすべて選択されます。選択された状態で、「検索と選択」→「置換」を選び、「置換後の文字列」に「0」や「N/A」など、データとして意味のある値(あるいは空欄にする場合は何も入力しない)を入力して「すべて置換」をクリックします。
3. 重複データを削除する
   データ内に不要な重複行がある場合も、ピボットテーブルに影響を与えることがあります。データ範囲を選択し、「データ」タブの「データツール」グループにある「重複の削除」をクリックして、重複している行を削除します。
4. ピボットテーブルを更新する
   ソースデータの修正が完了したら、ピボットテーブルを更新して変更を反映させます。ピボットテーブル内の任意のセルを選択し、「ピボットテーブル分析」タブ(または「オプション」タブ)の「更新」をクリックします。

Power Queryを使用したデータクレンジング

大量のデータや複雑なデータソースの場合、Excelの標準機能だけでは欠損の修正が難しいことがあります。そのような場合は、Power Query(パワークエリ)を活用することで、より効率的かつ柔軟にデータクレンジングを行えます。

Power Queryの基本操作

Power Queryは、Excel 2016以降およびMicrosoft 365で利用できる強力なデータ変換ツールです。外部データソースへの接続、データの整形、不要なデータの削除などを自動化できます。ピボットテーブルのソースデータとしてPower Queryで整形されたデータを使用すると、データ更新時の手間を大幅に削減できます。

Power Queryで空白行を削除する手順

Power Queryエディター内で空白行を削除する手順は以下の通りです。

1. Power Queryエディターを開く
   Excelの「データ」タブから「データの取得と変換」グループにある「テーブルまたは範囲から」を選択し、ソースデータ範囲を指定して「OK」をクリックします。これにより、Power Queryエディターが開きます。
2. 不要な列を削除する
   ピボットテーブルに不要な列がある場合は、列を選択し右クリックメニューから「列の削除」を選びます。
3. 空白行をフィルタリングする
   各列のヘッダーにあるフィルタボタンをクリックします。表示されるリストから「(空白)」のチェックを外し、「OK」をクリックします。これにより、その列に空白が含まれる行が除外されます。すべての列に対してこの操作を行うことで、実質的に空白行を削除できます。
4. データをExcelに読み込む
   「ホーム」タブの「閉じて読み込む」をクリックします。これにより、整形されたデータが新しいシートにテーブルとして読み込まれます。このテーブルをピボットテーブルのソースデータとして使用します。

ピボットテーブルでの空白表示を制御する

ソースデータの修正と並行して、ピボットテーブル自体の表示設定で空白行の表示を制御することも可能です。これにより、データ修正が難しい場合でも、見やすいレポートを作成できます。

ピボットテーブルオプションの設定

ピボットテーブルの表示設定を変更するには、以下の手順を行います。

1. ピボットテーブルオプションを開く
   ピボットテーブル内の任意のセルを選択し、リボンメニューの「ピボットテーブル分析」(または「オプション」)タブにある「オプション」をクリックします。
2. 「空白セルに表示する」設定
   「ピボットテーブルのオプション」ダイアログボックスが表示されたら、「レイアウトと書式」タブを選択します。「空白セルに表示する」のチェックボックスをオンにし、表示したい文字(例:「-」や「0」)を入力します。これにより、ソースデータに空白があっても、ピボットテーブル上では指定した文字が表示され、空白行として認識されなくなります。
3. 「エラーセルに表示する」設定
   同様に、「エラーセルに表示する」にもチェックを入れ、表示したい文字を入力することで、エラー値も制御できます。

ピボットテーブルのフィールド設定

行ラベルや列ラベルに設定されているフィールドでも、空白の項目を除外することができます。

1. フィールドのフィルタ設定
   ピボットテーブルの行ラベルや列ラベルのフィールドボタンをクリックします。表示されるリストから「(空白)」のチェックを外します。これにより、そのフィールドにおいて空白の項目を持つ行が表示されなくなります。

よくある誤解と注意点

ピボットテーブルで空白行が表示される問題に関して、よくある誤解や注意点を以下にまとめました。

「空白」と「0」の違い

ソースデータにおいて、セルが完全に空欄である「空白」と、数値の「0」は明確に区別されます。ピボットテーブルでは、これらは異なるものとして扱われます。空白セルは集計対象外となるか、空白行として表示される可能性がありますが、数値の「0」は有効なデータとして集計に含まれます。意図せず空白になっていないか、あるいは「0」で代用すべきでないかを確認することが重要です。

データ更新時の再発防止策

一度修正しても、ソースデータの更新時に再び空白行が表示されることがあります。これを防ぐためには、データ入力規則を設定したり、Power Queryでデータ変換プロセスを自動化したりすることが有効です。定期的なデータチェックの習慣も、問題の早期発見につながります。

ピボットグラフへの影響

ピボットテーブルで空白行が表示されている場合、それに基づいて作成されたピボットグラフにも影響が出ます。グラフの軸に予期せぬ空白が表示されたり、データの分布が不正確になったりする可能性があります。ソースデータのクレンジングは、ピボットグラフの正確性を保つためにも不可欠です。

まとめ

この記事では、ピボットテーブルに大量の空白行が表示される原因と、その解決策について解説しました。ソースデータの欠損を特定し、Excelの検索・置換機能やPower Queryを用いて修正することで、見やすく正確なピボットテーブルを作成できます。また、ピボットテーブルのオプション設定やフィールド設定で空白表示を制御することも可能です。これらの方法を実践し、レポート作成の効率と質を向上させましょう。次回は、ピボットテーブルの集計方法をより詳細に解説する予定です。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】