【Excel】Excelでピボットの集計対象から空白行を除外したい時の原因と修正手順

Excelのピボットテーブルで集計を行うと、元データに空白行が含まれているために「(空白)」という行が表示されることがあります。この空白行は見た目を悪くするだけでなく、集計結果を誤解させる原因にもなります。本記事では、ピボットテーブルの集計対象から空白行を除外するための具体的な確認手順と、根本的な対処方法を解説します。会社の共有データや定型報告書で使う場合の注意点も含めて説明します。

1. 空白行が表示される原因を特定する

ピボットテーブルに「(空白)」という行が現れるのは、集計に使用しているフィールド(行ラベルや列ラベル、または値フィールド)の元データに空のセルが含まれているからです。ただし、空白行の発生パターンはいくつかあります。原因を正確に把握しないと、誤った対処をしてしまう恐れがあります。

1-1. 行ラベルや列ラベルに空白があるケース

最も多いのが、ピボットテーブルの行ラベルや列ラベルに指定したフィールド内に空白セルがある場合です。例えば「商品名」を行ラベルにしたとき、元データの商品名列に空白のセルが1つでも存在すると、ピボットテーブルに「(空白)」という行が自動で作成されます。この空白行は、そのフィールドが空白であるすべてのレコードをまとめたものです。

1-2. 値フィールドに空白があるケース

値フィールド(例えば「売上金額」)に空白セルがある場合、集計方法によっては「(空白)」行が表示されることがあります。特に、値フィールドを「行ラベル」や「列ラベル」にドラッグした場合に発生します。ただし、通常は値フィールドの空白は0として扱われるため、空白行が直接表示されることは稀です。

1-3. フィルター条件による影響

ピボットテーブルにレポートフィルターを設定している場合、フィルターで除外した結果、集計対象レコードがなくなり空白行が表示されることがあります。この場合はフィルターの設定を見直す必要があります。

2. 元データを修正して空白行を根本的になくす方法(推奨)

最も確実な方法は、ピボットテーブルの元データを修正し、空白セルに適切な値を入力することです。これにより、ピボットテーブルを更新しても「(空白)」行が再発しなくなります。ただし、元データの内容を勝手に変更すると、他の集計や報告に影響を与える可能性があるため、事前に関係者と調整してください。

1. ピボットテーブルの元になっているデータ範囲(通常は別シートや同じシートのテーブル)を開きます。
2. 「(空白)」と表示されているフィールド名を確認します。例えば「担当者」フィールドに空白行がある場合、その列の空白セルを探します。
3. 空白セルに、適切な値を入力します。例えば「未割当」や「その他」といったカテゴリ名を入力すると、空白ではなくなります。
   ※注意:空白に意味がある場合(例えば未入力のまま管理したい場合)は、この方法は使えません。
4. 元データを修正したら、ピボットテーブルを右クリックし、「更新」を選択します。または、[データ]タブの「すべて更新」をクリックします。
5. ピボットテーブルに「(空白)」行が消えていることを確認します。

この方法は、空白行の発生原因を根本的に取り除くため、最も推奨される対処法です。会社のデータベースから抽出したデータなど、元データを編集できない場合は、次のフィルター方法を検討してください。

3. ピボットテーブルのフィルター機能で空白行を隠す方法

元データを変更できない、または変更したくない場合は、ピボットテーブル自体のフィルター機能を使って空白行を表示しないように設定します。ただし、この方法は見かけ上空白行を非表示にするだけであり、元データが更新されると再度表示される可能性があります。

1. ピボットテーブルの行ラベルまたは列ラベルの横にあるドロップダウン矢印(▼)をクリックします。
2. フィルターの一覧で、「すべて選択」のチェックを外します。
3. 一覧から「(空白)」のチェックだけを外します。その他の項目はすべてチェックしたままにします。
4. 「OK」をクリックすると、空白行が非表示になります。
5. ピボットテーブルを更新(右クリック→更新)しても、このフィルター設定は維持されますが、新しい空白データが追加された場合は再度フィルターを適用する必要があります。

この方法は簡単ですが、フィルターをかけていることを他のユーザーが気づかず、すべてのデータが表示されていないまま報告に使ってしまうリスクがあります。チームで共有するピボットテーブルでは、フィルター設定を明示的に伝えるか、元データ修正のほうが安全です。

4. 高度なテクニック:空白行を除外した状態でデータを集計する方法

4-1. 数式を使って空白以外のデータだけを抽出する

ExcelのFILTER関数(Office 365以降)を使用して、空白セルを除いたデータ範囲を動的に作成し、それをピボットテーブルの元データに指定する方法です。元データが変更されるたびに自動的に空白行を除外できます。

1. 新しいシートを作成し、セルA1に次の数式を入力します。
   =FILTER(元データ範囲, 元データ範囲の該当列<>"")
   例えば元データがSheet1のA1:D100で、空白を除外する列がB列の場合、=FILTER(Sheet1!A1:D100, Sheet1!B1:B100<>"")となります。
2. 数式の結果がスピル範囲として表示されます。この範囲を選択し、[数式]タブの「名前の定義」を使って名前を付けておくと便利です(例:「フィルターデータ」)。
3. ピボットテーブルを作成する際、元データとして名前付き範囲または数式の範囲(例:Sheet2!$A$1#)を指定します。
4. ピボットテーブルを更新すると、空白行が除外された状態のデータだけが集計されます。

この方法は、元データを変更せずに動的に空白行を除外できるため、定期的に更新されるデータに最適です。ただし、FILTER関数はOffice 365またはExcel 2021以降でしか使えません。古いバージョンのExcelでは別の方法(配列数式など)を検討してください。

4-2. テーブル機能を活用する

元データをExcelの「テーブル」に変換すると、空白行の扱いが変わることがあります。テーブル内で空白セルがある行は、テーブルとして認識されます。ただし、ピボットテーブルの行ラベルに空白が含まれると、やはり「(空白)」行が表示されます。テーブルにしただけでは空白行は自動で除外されないため、フィルター機能や前述の数式と組み合わせて使うことになります。

5. 状況別の比較表:空白行を除外する方法のメリット・デメリット

方法	メリット	デメリット	おすすめの場面
元データを修正する	根本解決になる。ピボット更新後も空白行が再発しない。	元データの内容を変えてしまうため、他の集計に影響する可能性がある。空白を許可したい場合には使えない。	データの入力ルールを変更できる場合。自分だけで使うデータ。
ピボットフィルターで空白行を非表示	元データを変更しない。簡単ですぐに適用できる。	一時的な対処に過ぎない。新しい空白データが追加されると再表示される。フィルターがかかっていることが伝わらずミスを招く。	自分だけが見る一時的な集計。元データ変更不可かつ頻繁に更新しない場合。
FILTER関数で動的範囲を作成	元データを変更せず、自動的に空白行を除外したデータで更新可能。	Office 365以降でしか使えない。数式が複雑になり、共有した場合にエラーになる可能性がある。	定期的に更新されるデータで、元データを触れない場合。チーム内で環境が統一されている場合。

6. 失敗パターンと注意点:こんなときはどうする?

6-1. [フィルター]で空白行を消したつもりが、データ更新後に再表示された

フィルター設定は、ピボットテーブルに保存されますが、元データに新しい空白行が追加された場合、その空白行はフィルターでは自動的に除外されません。新しいデータを含めて再フィルターする必要があります。対策としては、フィルター設定後にピボットテーブルを更新したら、必ずフィルターが適用された状態か確認する習慣をつけてください。

6-2. 元データを修正したのにピボットに空白行が残っている

元データの空白を埋めても、ピボットテーブルが更新されていないと「(空白)」行が消えません。必ず右クリック→「更新」を実行してください。また、複数のピボットテーブルが同じデータソースを参照している場合、すべてのピボットテーブルを更新する必要があります。

6-3. 値フィールドが空白の場合の扱い

値フィールド(数値データ)に空白がある場合、ピボットテーブルでは空白を0として扱うことが多いですが、集計方法が「データの個数」などの場合は空白行としてカウントされることがあります。この場合も、元データの空白を0または適切な値に置き換えるか、フィルターで除外する必要があります。

7. よくある質問(FAQ)

Q1. 空白行を完全に削除する方法はありますか?
ピボットテーブル自体には空白行を完全に削除する機能はありません。元データを修正するか、フィルターで非表示にするしかありません。

Q2. ピボットテーブルを更新するたびに空白行フィルターをやり直すのが面倒です。自動的に除外する方法は?
元データをテーブルに変換し、フィルターをかけてからピボットテーブルを作成する方法や、FILTER関数を使う方法があります。ただし、完全自動化は難しいため、定期的なメンテナンスが必要です。

Q3. 空白行を除外したことをメンバーに知らせたいのですが、どうすればいいですか?
セルにコメントを付ける、ピボットテーブルのタイトルに「(空白を除外済み)」と記載する、または別シートに処理の説明を残すなどの方法があります。

Q4. 管理者に確認すべきことはありますか?
会社の共有フォルダにあるデータや、他の部署が管理するデータベースを直接編集する場合は、必ず管理者の許可を得てください。また、FILTER関数を使う場合、会社のExcelのバージョンが対応しているか確認しておくと安全です。

まとめ

ピボットテーブルの空白行を除外するには、元データを修正する方法が最も確実ですが、状況に応じてピボットフィルターやFILTER関数を使い分けるとよいでしょう。空白行が表示される原因は、行ラベルや列ラベルに指定したフィールドの空白セルであることがほとんどです。元データを変更できない場合は、フィルターで非表示にするか、動的配列関数を使って常に空白を除外したデータで集計する方法を検討してください。ピボットテーブルを更新した後は、必ず空白行が適切に処理されているか確認する習慣をつけましょう。

📊

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

🏆

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