画像: muchomor、ゲッティイメージズ/iStockphoto
以前のTechRepublicの記事「Microsoft Excelシートで上位n個の値を強調表示する方法」では、Microsoft Excelのデータセット内の上位n個の値を強調表示するための2つの異なる条件付き書式設定方法について説明しました。すべてのデータを表示したい場合は、どちらの方法でも問題ありません。上位n個のレコードのみを表示し、それ以外のレコードを除外したい場合は、別の方法が必要になります。
開示事項： TechRepublicは、このページで紹介されている一部の製品から手数料を受け取る場合があります。TechRepublicと著者は、この独立したレビューに対して報酬を受け取っていません。
幸いなことに、Microsoft Excelにはピボットテーブル用のフィルターが組み込まれており、上位（または下位）n件のレコードを表示できます。この記事では、シンプルなピボットテーブルを作成し、組み込みフィルターを使用してデータソース内の上位10件のレコードのみを表示します。その後、結果に生じる問題とその解決策について説明します。この記事の後半部分は、読者の皆さんにとって最も新しい情報となるかもしれません。
私は（デスクトップ版の）Office 365を使用していますが、以前のバージョンのリボン形式もご利用いただけます。ご自身のデータを使用することも、デモ用の.xlsxファイルをダウンロードすることもできます（この記事は以前の.xlsバージョンには適用されません）。ブラウザ版ではピボットテーブルは表示されますが、ブラウザでコードを実行することはできません。
Excelでピボットテーブルを作成する方法
このセクションはピボットテーブルの作成方法に関するチュートリアルです。ピボットテーブルの作成方法をすでにご存知の場合は、このセクションをスキップしてください。図Aのデータセットを使用してデモ用のピボットテーブルを作成するには、次の手順に従います。
- データセット内の任意の場所をクリックし、「挿入」タブをクリックします。「テーブル」グループで、「ピボットテーブル」をクリックします。
- 表示されるダイアログで「OK」をクリックします。デフォルト設定を変更する必要はありません。
- ピボットテーブルフレーム内をクリックします。ピボットテーブルのフィールドリストウィンドウで、「製品名」を「行」セクションにドラッグします。「単価」を「値」セクションにドラッグします（図B）。フィールドを追加すると、フレームの内容が更新されます。
図A
図B
デフォルトでは、ピボットテーブルは商品ごとに単価の値を合計しますが、今回の商品リストは重複していないため、値は変化しません。単価列の書式を設定するには、ヘッダーセルを右クリックし、表示されるサブメニューから「数値の書式」を選択します（私は通貨を選択しました）。
現状では、ピボットテーブルにはすべてのレコードが表示されます。では、上位n個の単価値を表示する最初の方法を見てみましょう。
組み込みオプションの使い方
フィルターの適用は簡単で、選択肢も豊富です。欠点は動的ではないことですが、これについては後ほど説明します。それでは、フィルターを適用してみましょう。
- 行ラベルドロップダウン フィルターをクリックします。
- 「値フィルター」を選択し（商品ではなく単価の値を評価することに注意してください）、次に「上位10件」を選択します（図C）。単価列は値列なので、ドロップダウンメニューはありません。多くのオプションがあるので、後ほど詳しく見ていきましょう。
- 表示されるダイアログにはいくつかのオプションがありますが、デフォルト設定 (図 D ) は、この例に最適です。つまり、単価合計列の上位 10 項目を表示します。
- [OK]をクリックすると、図 Eの結果が表示されます。
図C
図D
図E
作成されたピボットテーブルをそのまま使用することもできますが、まずいくつか考慮すべき点があります。次に見ていきましょう。
いくつかの小さな問題を修正する
さて、結果を少し調整してみましょう。今回の場合、下部の合計行は意味がないので、非表示にすることができます。非表示にするには、「デザイン」コンテキストタブをクリックします。「レイアウト」グループで「総計」ドロップダウンをクリックし、「行と列」を「オフ」にします。
Excelは行ラベル（製品）で並べ替えることで、複数の値を正しくグループ化して合計します。今回のケースではこの並べ替えは不要であり、製品による並べ替えは特に意味がありません。おそらく、単価で並べ替えたいでしょう。そのためには、単価セルを右クリックし、「並べ替え」を選択して、「最大から最小へ並べ替え」を選択します。ヘッダーテキストは分かりにくいので、変更するには、セル内をクリックしてデフォルトのヘッダーを置き換えます（または置き換えないこともできます）。ドロップダウン矢印を非表示にする場合は、この手順を省略できます。
おそらく、閲覧者にフィルターを変更させたくないでしょう。フィルターを変更するには複雑な方法がいくつかありますが、最も簡単な方法はフィルターのドロップダウンを削除することです。これを行うには、ピボットテーブル内の任意の場所をクリックし、コンテキストメニューの「ピボットテーブル分析」タブをクリックします。次に、「ピボットテーブル」グループ（左端）の「オプション」ドロップダウンをクリックし、「オプション」を選択します。表示されるダイアログで「表示」タブをクリックし、「フィールドのキャプションとフィルターのドロップダウンを表示する」オプションのチェックを外します。これにより、ヘッダーテキストとドロップダウンの両方が非表示になりますが、実際には問題ありません。行を非表示にし、ピボットテーブルの上に適切なテキストまたはタイトルを追加するだけです。（このオプションはデモファイルでは使用されていません。）
ピボットテーブルに馴染みのない方は、覚えておくべき重要な動作があります。ピボットテーブルは自動更新されません。ソースデータの値を変更して上位10位のピボットテーブルに影響が出た場合、ピボットテーブルを更新するまでその変更は反映されません。しかし、完璧な解決策ではないにしても、役立つヒントはあります。
それほど動的ではない回避策
ピボットテーブルを更新するには、任意のセルを右クリックし、「最新の情報に更新」を選択します。ただし、閲覧者はこの操作を知らないため、表示が更新に遅れてしまう可能性があります。最も簡単な解決策は、誰かがブックを開いたときにExcelでピボットテーブルを強制的に更新することです。手順は次のとおりです。
- ピボットテーブル内の任意の場所をクリックし、コンテキスト ピボットテーブル分析タブをクリックします。
- ピボットテーブル グループ (左端) で、[オプション] ドロップダウン ([ピボットテーブル名] ボックスの下) をクリックし、ドロップダウン リストから [オプション] を選択します。
- 表示されるダイアログで、[データ] タブをクリックします。
- ファイルを開くときにデータを更新するオプションをチェックします (図 F )。
- [OK]をクリックします。
図F
このオプションを選択すると、閲覧者は最新の情報を見ることができますが、ファイルを開く必要があるため、問題を完全に解決することはできません。（このオプションはデモ ファイルでは設定されていないため、動作を自分で確認することはできません。）おそらく、リスト Aのシンプルなイベント プロシージャを使用する方がよい解決策でしょう。
リストA
プライベートサブ Worksheet_Deactivate()
ThisWorkbook.RefreshAll
終了サブ
データソースにアタッチされたシートモジュールに、このプロシージャを追加します。データを更新してそのシートから離れると、Deactivateメソッドによってrefreshメソッドが呼び出されます。この簡単な解決策には、いくつかの制限があります。まず、ピボットテーブルとデータソースは異なるシート上になければなりません。さらに、RefreshAllメソッドはすべてのピボットテーブル、クエリなど、すべてを更新します。更新対象をピボットテーブルに限定したい場合は、代わりに次のrefreshメソッドを使用してください。
sheetname .PivotTables(“ pivottablename “).PivotCache.Refresh
ご自身の作業に適用する際は、 sheetnameとpivottablename を適宜更新してください。デモファイルにはコードが含まれていますが、コメントアウトされています。ご自身の作業にコードを適用する際は、この Web ページからコピーしないでください。Visual Basic Editor (VBE) では、表示されない文字をコンパイルできないためです。コードをご自身で入力するか、Word (または他のテキストエディタ) にコピーして、そこから VBE にコピーしてください。また、コードをワークブックに追加する場合は、ファイルをマクロ有効ワークブック (.xlsm) として保存してください。
Microsoft Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
