今回の記事では
ピボットテーブルの参照元のデータに追加があると自動的に反映されないのはどうして？
自動反映されない時の対処方法を知りたい
ピボットテーブルで参照元のデータに追加があっても自動的に反映する方法は無いの？
自動化出来るならなるべく簡単な方法を教えてほしい
こういった疑問に答えます
✔ピボットテーブルの参照範囲を自動化する方法のテーマ
①ピボットテーブルの参照元にデータを追加したら範囲の再設定が必要です
②テーブルとして書式設定をすればピボットテーブルデータを自動化出来ます
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「ピボットテーブルの参照範囲を自動化する方法のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
ピボットテーブルの参照元にデータを追加したら範囲の再設定が必要です
ピボットテーブルの参照元にデータを追加したら参照範囲を再設定する必要があります
なぜなら、ピボットテーブルは手動で作成した表の範囲を自動的に認識してくれる機能が無いからです
それでは具体的な方法について解説していきます
最初にデータベースとピボットテーブルが用意してある状態で話を進めていきますが、ピボットテーブルの作り方の基本がいまいち分からない場合は「ピボットテーブルは情報分析に特化した表」という記事で紹介しているので参考にして下さい
今回は「年間テント販売管理一覧表」をデータベースにします
この表は手作業で作った一般的な表です
ピボットテーブルでは問題なく集計することが出来ています
そこで5件ほど追加する項目が増えたとしましょう
データベースに5件追加してみます
こんな風に5件追加しました
では、ピボットテーブルを更新してみましょう
結果は全く一緒です
本来なら「ドーム」というカテゴリーが増えなくてはいけません
なぜこんなことが起こるのでしょうか？
それは、参照元のデータベースに項目を追加したことが、ピボットテーブルに理解できないからです
では、ピボットテーブルの参照元のデータを見てみましょう
「ピボットテーブルツール」の「ピボットテーブル分析タブ」の「データソースの変更」ボタンをクリック
データベースに自動的に移動します
参照しているデータ範囲を見てみると「A4～G14のセル範囲」を対象にしていることが分かります
つまり、追加項目である「A15～G19」のセル範囲は含まれていないことが分かりますね
では、参照先の範囲を変更するにはどうしたら良いのでしょうか？
やり方はすごく簡単です
ダイアログが表示されている状態で追加された行まで範囲選択します
「OK」ボタンをクリックします
自動的にピボットテーブルに戻り、追加された項目がちゃんと反映されています
こんな風に手作りで作った表に対しては、手動でデータベースの参照範囲を変更する必要があります
繰り返しになりますが、ピボットテーブルの参照元にデータを追加したら参照範囲を再設定する必要があります
よくある質問：どうしても自前の表で自動的に参照範囲を自動取得する方法はありませんか？
オススメしませんが範囲に名前をつけてOffset関数を使えば自前の表でも自動取得する方法はあります
オススメしない理由は、もっと簡単にピボットテーブルの参照元のデータベースを自動取得する方法があるからです
どうしても自分で作った表をピボットテーブルの参照元として使い自動で取得したい場合は、範囲に名前をつける際にOffset(オフセット)関数を組み合わせることで実現可能に出来ます
では、実際の設定方法を見ていきましょう
今回は「名前の定義」という「セルやセル範囲」に名前を設定することで名前がついているセルや範囲を指定できる機能の応用編になるので、良く分からない場合は素直に諦めて下さい
やり方は次の通りです
今回はセル範囲の名前を「テント販売管理テーブル」とします
「数式」タブの「定義された名前」グループにある「名前の定義」をクリック
「新しい名前」ダイアログが表示されるので「テント販売管理テーブル」と入力
「参照範囲」に「=OFFSET(データベース!$A$4,0,0,COUNTA(データベース!A:A)-1,7)」と入力します
設定が終わったら「OK」ボタンをクリック
名前は何でもOKです
ポイントはOffset関数ですが、Offset関数の基本的な使い方は「=OFFSET(参照,行数,列数,高さ,幅)」です
コレを日本語にすると「=OFFSET(A4セルから,行方向へ「0」移動,列方向へ「0」移動,COUNTA関数分の高さを取得,7列の幅まで取得してね)」という意味になります
つまり、Offset関数の基本は基準のセルを移動することと、基準のセルから◯行◯列分の範囲を取得するという動きをします
今回のポイントは「COUNTA(データベース!A:A)-1」です
COUNTA(カウント・エー)関数は文字列が入力されているセルの数を数えてくれる関数で「A:A」というのは「A列全て」という意味があります
最後の-1はタイトル行(1行)分多く高さを取得してしまうのでその調整用です
つまり、A列に文字列が入力された文字列の数を数えるので自動的に高さ(セルの行数を取得)してくれるという訳です
あとは、A4セルは固定されているのでそこからA列の最終行までを高さの範囲とします
列は幅の7つまり7列目までをセル範囲として取得するという訳です
これで「X行7列」の範囲を自動的に取得出来るという仕組みです
文字で説明しているので余計難しいですが、分からない場合は「ふ～ん」と思ってもらえるだけでOKです
最後にピボットテーブルの「データソース」にさっき付けた名前の範囲を指定してあげましょう
名前は「テント販売管理テーブル」なのでこの文字をコピーしておくと便利ですよ
仕上げに「データソースの変更」ボタンをクリック
「テーブルまたは範囲を選択の項目」にさっき設定した名前を入力
「OK」ボタンをクリック
これなら自動で参照範囲を調整出来ます
ただ、オススメしないのはもっと簡単な方法があるからです
どうしても試してみたい人だけチャレンジしてみましょう
もちろん。データベースの追加後は更新ボタンをクリックしないと結果は反映されませんのでご注意を
繰り返しになりますが、オススメしませんが範囲に名前をつけてOffset関数を使えば自前の表でも自動取得する方法はあります
テーブルとして書式設定をすればピボットテーブルデータを自動化出来ます
テーブルとして書式設定すればピボットテーブルが自動化出来て劇的に使いやすくなります
なぜなら、テーブルとして設定をすると「変更・削除・追加」全ての変更に対してピボットテーブルが変更内容を検知出来るようになるからです
では具体的にピボットテーブルの参照範囲を自動化する方法について解説していきます
びっくりするくらい簡単なので是非覚えておきましょう
まずは、テーブルにしたい範囲を範囲選択します
テーブルとは「表」のことです
「ホーム」タブの「スタイル」グループにある「テーブルとして書式設定」ボタンをクリック
テーブルの作成ダイアログが表示されるので「OK」ボタンをクリック
それぞれの項目名に「フィルター」ボタンが設置されればOKです
そんままピボットテーブルの作成をすると、「テーブル1」が自動で設定されるので
「OK」ボタンをクリック
各項目を設定すれば準備完了です
では、データベースにテスト用の情報を追加してみましょう
データべースに11項目名を追加してテストとしました
テーブルに設定してあるので最終行にデータを追加すると自動でテーブルに含めてくれます
ピボットテーブルを更新するとちゃんと追加した分が反映されていますね
これで作業は終了です
こんな風にテーブルとして書式設定をすると、すごく簡単にサクッとピボットテーブルとの連携ができるのでこの方法を是非覚えておきましょう
繰り返しになりますが、テーブルとして書式設定すればピボットテーブルが自動化出来て劇的に使いやすくなります
オススメのピボットテーブルの解説書 5選
ピボットテーブルを本で学ぶ時は次に上げるものがオススメです
本はいつでも見ることができ知識の宝庫なので読んで損なしです
スキマ時間などを活用して本で独学や教わったことを復習するなど活用の幅はたくさんあります
コメントを残す