【Excel】ピボットテーブルで空白が多く表示される時の元データ確認

ピボットテーブルを作成した際、行ラベルや値フィールドに「(空白)」と表示されるケースが多くあります。この現象は、元データに空白セルや空白行、あるいは見かけ上の空白文字が含まれていることが原因です。本記事では、空白が多く表示される原因を切り分け、元データを確認・修正する具体的な手順を解説します。会社で共有しているExcelファイルを扱う前に、必ずコピーを取ってから作業を進めてください。

ピボットテーブルで空白が表示される主な原因

ピボットテーブルに「(空白)」が表れる原因は、大きく分けて4つあります。それぞれの特徴を理解することで、効率的に対処できます。

空白行が元データに存在するケース

元データの途中に空白行が1行でもあると、その行のすべてのフィールドが「(空白)」としてピボットテーブルに集計されます。例えば、売上データの表で2行目と3行目の間に空白行があると、行ラベルに「(空白)」が表示され、その行の値が合計に含まれます。

空白セルが元データに存在するケース

特定の列に空白セルがある場合、そのセルが含まれる行の該当フィールドが「(空白)」となります。例えば、「部署」列に空白セルがあると、ピボットテーブルの行ラベルに「(空白)」が出現します。

書式設定やスペースによる見かけ上の空白

セルにスペース(全角・半角)や改行、非表示文字(CHAR(160)など)が含まれていると、見た目は空白に見えてもExcelは「空白ではない」と認識します。この場合、ピボットテーブルには実際の文字列として集計され、「(空白)」とは異なる独自のラベルが表示されます。ただし、スペースだけのセルが多数あると、ラベルが増えて見づらくなります。

数式の結果が空文字列(“”)の場合

元データにIF関数などで =IF(A1="","",A1) のような数式があると、条件に合致した場合に空文字列が返されます。この空文字列は空白セルとは異なり、ピボットテーブルでは「(空白)」ではなく、見かけ上何も表示されないセルとして扱われます。ただし、値フィールドとして集計する際に問題が生じることがあります。

元データを確認する具体的な手順

以下の手順で元データをチェックし、空白の原因を特定します。作業前に元データを別シートにコピーしておくと安全です。

1. 手順1: ピボットテーブルで「(空白)」をクリックして該当データを確認
   ピボットテーブルの行ラベルに表示された「(空白)」をダブルクリックすると、該当する元データの行が新しいシートに抽出されます。これでどのようなデータが空白扱いされているか把握できます。
2. 手順2: 元データの表全体を選択し、条件付き書式で空白セルを強調表示
   元データの全範囲(ヘッダーを含む)を選択し、[ホーム]タブ→[条件付き書式]→[セルの強調表示ルール]→[空白]を選択します。これで空白セルに色が付き、視覚的に把握できます。
3. 手順3: 空白行を検出する(Ctrl+G でジャンプ)
   元データの全範囲を選択し、Ctrl+G(ジャンプ)→[セル選択]→[空白セル]をクリックします。空白セルがすべて選択された状態になります。このとき、行全体が空白の場合、その行のすべてのセルが選択されます。該当行を確認し、必要に応じて削除します。
4. 手順4: 不要なスペースを削除(TRIM関数または置換)
   データに含まれる余分なスペースを確認するには、TRIM関数を使って別列に変換するか、[ホーム]タブ→[検索と選択]→[置換]でスペースを削除します。置換の場合、検索文字列に半角スペース、置換後に空文字列を指定し、すべて置換します。全角スペースも同様に処理します。
5. 手順5: 数式による空文字列をチェック
   空白セルを選択した状態で、数式バーに =IF(...) などの数式が入っていないか確認します。空文字列が含まれるセルは、条件付き書式の「空白」では検出されません。代わりに、[ホーム]タブ→[検索と選択]→[条件を指定してジャンプ]→[数式]→[文字列]で検索し、空文字列のセルを探します。
6. 手順6: 元データの最終行・最終列を確認
   データ範囲を超えて余分な空白行や列が含まれていないか確認します。特に、表の下に不要な空白行があると、ピボットテーブル作成時にその範囲が含まれ、大量の「(空白)」が表示される原因になります。Ctrl+Endで最終セルに移動し、実際のデータ範囲と食い違っていないか確認します。

状況別の比較表

空白の原因とその特徴を以下の表にまとめました。ピボットテーブルでの表示の違いも参考にしてください。

原因	ピボットテーブルでの表示	検出方法	対処法
空白行	行全体が「(空白)」として集計	ジャンプ→空白セルで行全体を選択	該当行を削除
空白セル	該当フィールドに「(空白)」が表示	条件付き書式→空白、またはジャンプ→空白セル	該当セルに値を入力、または0などで埋める
スペース・制御文字	「(空白)」ではなく、スペースがラベルとして表示(見た目は空白に近い)	LEN関数で文字数確認、または置換で削除	TRIM関数で除去、または置換で空白を削除
数式の空文字列	セルは空白に見えるが、ピボットでは空文字列として処理(「(空白)」は出ない場合あり)	条件を指定してジャンプ→数式、または数式バーで確認	数式を修正し、空文字列ではなく本当の空白にする(=IF(条件,””,””) を削除)

よくある失敗パターンと対処法

実際の業務でありがちな失敗例を3つ紹介します。同じミスを防ぐためにも、事前に確認しておきましょう。

元データの空白列を含めてしまった場合

ピボットテーブル作成時に、データ範囲として空白の列まで選択してしまうと、その列が「(空白)」としてラベルに大量に表示されます。例えば、A列からZ列までデータがあるのに、AA列以降も範囲に含めると、AA列以降の空白セルがすべて「(空白)」の原因になります。対処法は、ピボットテーブルのデータ範囲を実際のデータが入っている範囲に狭めることです。テーブルに変換してからピボットを作成すると、範囲が自動更新されるためおすすめです。

結合セルを元データに含めた場合

元データで結合セルを使用していると、結合されていないセルが空白と見なされ、ピボットテーブルで「(空白)」が発生します。例えば、部署名を結合セルで表示している表をそのままピボットの元データにすると、結合セルの下の行は空白として扱われます。対処法は、結合セルを解除し、すべての行に同じ値を埋めることです。結合セルを解除するには、結合セルを選択して[ホーム]タブ→[セルを結合して中央揃え]をクリックして解除し、その後にCtrl+DやCtrl+Enterで空白セルに値を入力します。

数式の結果が空白文字列(“”)の場合

前項でも触れましたが、元データにIF関数などで空文字列を返す数式があると、ピボットテーブルで「(空白)」とは異なる動作をします。特に、そのセルを数値として集計する場合、空文字列は数値として扱われず、集計から除外されることがあります。これを避けるには、空文字列ではなく本当の空白(何も入力しない)にするか、0を返すように数式を修正します。例えば、=IF(A1="",0,A1) とすることで空白を0に置き換えられます。

管理者に確認すべき設定項目

空白の原因が元データだけではなく、Excelの設定や共有環境に起因する場合もあります。以下の項目を管理者に確認することで、根本的な解決につながります。

• 共有ファイルの編集権限: 複数人で同時編集している場合、誰かが誤って空白行を挿入した可能性があります。バージョン履歴を確認し、変更箇所を特定します。
• ピボットテーブルのフィルター設定: ピボットテーブルにレポートフィルターが設定されていると、そのフィールドに空白があると「(空白)」が表示されます。フィルターのアイテムを確認し、不要な空白を除外する設定に変更します。
• Excelの表示設定: 「ファイル」→「オプション」→「詳細設定」で「ブック内でゼロ値のセルにゼロを表示する」のチェックが外れていると、空白セルがゼロと誤認されることはありませんが、影響を与える可能性は低いです。
• システム管理者への依頼: もし元データがデータベースから抽出したものであれば、抽出条件に空白が含まれないようにSQLやクエリを修正してもらう必要があります。

まとめ

ピボットテーブルに「(空白)」が多く表示される原因は、ほとんどの場合、元データの空白セルや空白行、またはスペースなどのゴミデータです。まずは本記事の手順で元データをチェックし、該当する空白を特定・修正してください。修正後はピボットテーブルを更新(右クリック→更新)して、表示が正常になるか確認しましょう。どうしても解決しない場合は、管理者にExcelのバージョンや共有設定を確認してもらうとよいでしょう。

📊

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

🏆

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