リストにはあるのに、ピポッドテーブルの計算に入らない…
ピポッドテーブルの更新を押しているのに、合計が変わらない…
ピポッドテーブルの計算がおかしくなり、困った経験をした人は多いのではないでしょうか？慣れると便利なピポッドテーブルですが、少しトラブルが起きると全く対処法が分からないという方も多いと思います。
本記事では、そんなときの対処方法を7つのチェック項目に分けて紹介します。上から順に試すだけで、9割の問題は解決するので、ぜひひとつひとつ順番に見ていってください。
また、ピポッドテーブルの使い方についても、別記事で紹介しています。ぜひそちらの記事も参考にしてみてください。
ピポッドテーブルが反映されない時の7つのチェック項目
ここからチェック項目を順に紹介します。
上から順にみていって、試していないものからチェックしていってください。
リストの更新を行う
ピポットテーブルは基本的に更新をしないと、リストの変更内容は反映されません。まずは更新しているかを確認しましょう。
更新をするときは、ピボットテーブル上で右クリックし、「更新」を選択してください。
ちなみに更新はファイルを開くたびに自動更新することもできます。ピボットテーブルオプションの「ファイルを開くときに更新」にチェックを入れておくと、ブックを開くたびに最新状態が保たれます。
ソース範囲が正しく指定されているか
次に確認するのは、データの範囲が間違っていないかどうかです。
「ピポットテーブル分析」タブ→「データソースの変更」を選択し、範囲があっているか確認してください。
トラブルは、列を追加した後によく起こります。
ちなみに元リストを選択して Ctrl + T でテーブル化すると、行列の増減を自動で反映してくれます。列を追加することが多いリストでピポットテーブルを使うときは、テーブル化も試してみてください。
列名・行名・値の配置ミスを見直す
意外と多いのが、配置ミスです。ピボット右側のフィールドリストで各エリアに置かれている項目名を確認しましょう。
エラー値を除去して集計を正常化する
元データにエラー値が混在すると、計算値が正しく表示されません。その為、データに #N/A や #DIV/0! などのエラーが無いか確認をしましょう。
どこにあるかわからない場合は、ジャンプ機能を使えばエラーの場所をすぐに見つけられます。
まず、Ctrl + G でジャンプ画面を開き、「セル選択」をクリック
「関数」→「エラー値」を選ぶと、エラー値になっている部分が一括選択されます。
なお関数が不要な場合は一括で変更が可能です。エラー値の一括変換については、この後ご紹介します。
空欄行を取り除き連続データにする
ピボットは「連続したリスト」を前提としています。途中に完全な空白行が挟まると、その下のデータが無視されるため注意が必要です。行ごと飛んでいないか確認するときは、「Ctrl」+「↓」キーを押して、止まらないか確認してください。
なお、エラー値の確認で使った「ジャンプ」では、空欄も選択できます。データが多い場合は、ジャンプで確認しましょう。
集計方法が目的に合っているか確認する
値フィールドの設定が間違っていると、思った数値になりません。「合計」と「個数」を間違うと、全然違った数値が出てくることになってしまいます。
変更方法は、ピボット上で対象セルを右クリックします。そして「値フィールドの設定」から適切な集計方法に変更しましょう。
数値に見える文字列を正しい型に直す
Excelが文字と判定すると、数字であっても集計対象から外れてしまいます。数字が左詰めになっていたり、セルの左上に緑の印が付いていると、文字列になっている可能性が高いです。
数字が文字列になっている場合、カンマやスペースを取り除くことで、数値に戻すことができます。
これが一番見つけにくく、修正も手間がかかります。修正については、この後紹介する方法が使える場合もあるので、試してみてください。
エラー値・文字列混入を一括修正する方法
エラー値を一気にゼロに変える
値の貼り付けを行ってから置換するのが、エラーの数が多い場合の一番早いやり方です。
①全選択→コピー→値貼り付け
②検索と置換で「#N/A」を「0」に変換
これで一気にエラーの値を「0」に変換できます。
IFERROR 関数で関数を残したままエラー回避
計算式を残したい場合には IFERROR を使います。たとえば、VLOOKUP の結果が見つからないときゼロを返す式は以下のように記述します。
=IFERROR(VLOOKUP(A2, マスタ!A:B, 2, FALSE), 0)これでピボットに流れる値は常に数値となり、エラーで空欄になる問題を解消できます。
文字列を数値に一気に変換
数値に見える文字列が大量にある場合は、区切り位置ウィザードを活用します。
対象列を選択→［データ］→［区切り位置］を選択。
そして「次へ」を2回クリックして、区切り位置指定ウィザードの3/3まで進みます。最後に、列のデータ形式で「G/標準」を指定して完了を押してください。
それでも直らない時の最終手段
最後にもう一回更新をしてみる
いろいろリストを修正して、最後更新を忘れることがよくあります。
最後にもう一度更新をして、反映されるか確認をしましょう。
新しいブックでピボットテーブルを作り直す
ここまでしても反映されない場合、ピボットが壊れている可能性が高いです。ここまでくると、新しくエクセルを作り直した方が、早く問題が解決することが多いです。
新しくする際は、元リストを丸ごとコピーして新規ブックに貼り付け、名前を付けて保存してください。そしてExcel を一度すべて閉じてからそのファイルを開き、新規のピボットテーブルを作成しましょう。
まとめ
今回紹介した7つのチェック項目を確認すれば、ピポットテーブルの値が反映されない事象の9割は解消することができると思います。
ピポットテーブルの値に困ったときは、ぜひこの記事で紹介した内容を順番に試してみてください。
今回のトラブル解決のように、ITスキルはあなたの仕事をより効率的にしてくれます。以下の記事では、Kindle Unlimitedで読める業務効率化に役立つ本を厳選して紹介していますので、ぜひこちらも参考にしてみてください！
コメント