ピボットテーブルでフィルターを活用し分析を効率化する方法（並べ替え・抽出編）

Excelには「ピボットテーブル」という機能があるのをご存知ですか？ピボットテーブルを使えば、関数の知識がなくても日付などで範囲指定して大量のデータを一瞬で並べ替えたり集計できます。さらに、「集計の方法を合計から平均に変える」「集計する項目を解除して変更する」といった操作も、クリックやドラッグといった基本的な操作だけで完了します。作業効率アップを目指すビジネスパーソンはぜひ習得したいスキルです。
今回は、フィルター機能を使って、目的のデータに素早くたどり着く方法をご紹介します。

• 作成した表の中から見たいデータだけを表示させる
• 指定した順番に並べ替える
• 基準値以上のデータや、上位10件だけを表示させる

といった活用方法をお見せします。実際の操作画面も表示していますので、ぜひ仕事の効率化に役立ててください。

ピボットテーブルの使い方を紹介した過去の記事：
ピボットテーブルで何ができる？表の集計・分析を効率化しよう
集計以外もできる！ピボットテーブルで分析を効率化する方法（平均・比率編）
日付や番号ごとに仕分けよう！ピボットテーブルで分析を効率化する方法（階層構造・グループ化編）

ピポットテーブルフィルター機能でデータを並べ替えよう

 

ピボットテーブルのデータは、Excelで作成した他の表と同様、昇順または降順並べ替え流ことができます。例えば売上数が多いものから順に並べると、売れ行きの良い商品や、客足の伸びる季節などを分析することができます。

フィルターで範囲指定して並べ替え

実際の作業画面を見てみましょう。

ピボットテーブルの行ラベルの横にある▼マークをクリックすると、フィルター機能を利用することができます。今回は数量の合計が多い順に並べたいので、「降順」をクリックし、並べ替える基準を「数量／合計」に設定します。

ピボットテーブルの項目が販売数量の多い順に並べ替えられました。

階層構造のあるピボットテーブルでフィルター機能を使いたいときは？

フィルター機能は下の図のように階層構造になっているピボットテーブルにも使用することができます。

「階層構造の中で、特定の項目だけ並べ替えたい」という場合も、先ほどと同様に▼をクリックして操作できます。並べ替えたい項目、昇順・降順、並べ替えの基準となる値を選んでウィンドウを閉じると、並べ替えが完了します。「分類」の数量値を降順に並べ替える場合は下のように操作します。

別の方法でも並べ替えができます。並べ替えたい項目の集計数値の部分をクリックして選択し、その状態で「データ」タブの「降順」をクリックすると、指定された階層の項目だけを並べ替えることができます。

「分類」「商品」の構造が崩れることなく並べ替えられています。ピボットテーブルではこのように、階層構造のある表のレイアウトやカテゴリの上位・下位の関係を保ったままフィルター機能を使うことができます。

フィルターを使って文字列を並べ替える

• 「商品名をアイウエオ順で並べたい」
• 「支店のある地名を北から順番に並べたい」

という場合はどうでしょうか。Excelのフィルターで文字列を並べ替える場合、カタカナやひらがなの項目は五十音順で並べ替えられますが、漢字を含む項目は「漢字コード」の順番で並べ替えられ、必ずしも人間が読んだときの五十音順にはなりません。（漢字コードとは、文字ごとに与えられているコードのことです。）五十音順に並べ替えたい場合は、「ふりがな」や「支店番号」などの項目を用意しておくか、次に紹介する「ユーザー設定リスト」を使います。

フィルター機能を使った並べ替えに適しているもの（例）

• 数字・アルファベット（全角含む）
• ひらがな・カタカナ

フィルター機能を使った並べ替えに適さないもの（例）

• 漢字
• 特殊記号

英数字は全角・半角の区別なくフィルターによる並べ替えが可能です。ただし、同じデータベースに半角入力したデータと全角入力したデータが混在している場合は正しく集計できません。資料を作るときは、まず会社や部署が定めた表記ルールに従ってデータベースを作りましょう。

ユーザー設定リストで並べ替える（通し番号がないときの裏技）

「元になるデータに支店コードやふりがなの項目が設定されていないのに、大量の項目を指定した順番に並べ替えなければならない」という場合は、「ユーザー設定リスト」という機能を使います。
今回はピボットテーブル上の支店の並び順を変更してみましょう。まずはあらかじめ並べたい順番を別シートに作成しておきます。以前の資料などが社内で共有されている場合はコピー・ペーストでも構いません。その場合はスペース挿入や全角・半角の入力間違いなどがあると正しく集計されませんので注意してください。

並べ替え規則を読み込むため、「Excel」の「環境設定」をクリックし、「ユーザー設定リスト」を選択します。(Macの場合)


Windowsの場合は「ファイル」タブから「オプション」を選択して「詳細設定」をクリックし、「ユーザー設定リストの編集」をクリックします。

ウィンドウの真ん中に「リストの取り込み元範囲」という部分があります。入力欄をクリックし、最初に入力しておいた並び順のセル範囲をドラッグして選択します。選択範囲に間違いがなければ「インポート」をクリックしてください。

「ユーザー設定リスト」の一番下に支店の並び順が登録されたことが確認できたら、ウィンドウを閉じます。

ここで一度ピボットテーブルを作成していた画面に戻りましょう。並べ替えたい項目が表示されたセルをどこでも良いのでひとつクリックし、「データ」タブから「並べ替え」→「オプション」を選択して、先ほど登録した並び順をクリックします。


登録が完了したら「OK」をクリックして並べ替えウィンドウを閉じましょう。

最初に作った順番のとおりに支店のデータが並べ替えられました。ドラッグ・ドロップを使って並べ替えることもできますが、手動ではミスが起こる可能性がありますし、毎月同じデータ処理を行うような場合は手間ですよね。ユーザー設定リストを活用して業務を省力化し、ミスをなくしていきましょう。

ピポットテーブルフィルター抽出機能を使って目的のデータを抽出しよう

フィルター機能を使えば、行ラベルや列ラベルから特定の文字列だけを含むものだけを抽出することもできます。例えば、

• 顧客リストの名前から『（株）』や『法人』を含むものだけ表示したい
• 商品の注文リストから「限定」を含む商品だけを見たい

というときに便利です。

フィルターで文字列を抽出する方法

今回は商品リストの中から「ラテ」という文字列を含むものだけを抽出してみましょう。行ラベル横の▼をクリックすると、フィルターを適用する項目やフィルターの種類が選べます。項目に含まれる（または含まれない）文字列でデータを絞りこみたいときは「ラベル別」を使います。（Windows版は「ラベルフィルター」にポインターを合わせ、表示される項目の中からフィルターを選びます。）

今回は「ラベル別」（または「ラベルフィルター」）から「（指定の値）を含む」を選択し、入力欄に「ラテ」と打ち込みます。すると、「ラテ」という文字列を含む商品だけを抽出できます。

フィルターの種類を選ばずに下のチェックボックスから該当する項目だけを手動で選ぶこともできます。比較する項目が少ない場合はそのほうが手間を省けるのですが、数十項目以上の中から抽出する場合はフィルターを活用しましょう。（ミスを少なく、早く確実に処理するためです。）

フィルター設定の注意点と解除方法

なお、項目を列や行から削除しても、フィルター設定は解除されません。設定は削除されずに残っており、このフィールドを再度列や行に指定した際は前回のフィルター設定がそのまま適用されます。


「今後も指定の項目だけを対象に分析する」という場合は便利なのですが、「もう一度すべての項目で分析したい」という場合はフィルターの解除が必要です。

フィルターを解除するには、該当のラベル横のマークをクリックして「フィルターのクリア」を選ぶだけです。

数値をもとに目的のデータを抽出しよう

合計や平均など集計した値の大小でデータを抽出することもできます。例えば、一定の金額以上の売上を記録した商品をピックアップする場合や、逆に売上目標に届いていない支店などをチェックするときにも役立ちます。

集計した数値でデータを抽出するときはフィルターの「値別」から抽出条件を指定します。
（または「ラベルフィルター」にポインターを合わせ、表示される項目の中からフィルターを選びます。）

今回は合計金額が10,000,000円以上の商品を抽出してみましょう。「（指定の値）以上」をクリックします。（ちなみに、「指定の値より大きい」「より小さい」を選ぶと、指定した数値ぴったりの集計値は抽出から除外されます。）
また、このとき判定に使用する「集計値」を必ず確認してください。（値に複数のフィールドを指定しているとき、指定した集計値が異なっていると思いどおりに並べ替えができないことがあります。）今回は合計金額で抽出するので「合計／金額」を選択し、入力欄に「10000000」と数値を入力します。

売上金額の合計が10,000,000円以上の商品だけが表示されました。

上位（または下位）の項目だけを表示する

ピボットテーブルのフィルターでは「上位5件」「下位20%」といった条件付けも可能です。このような条件付けは「トップテン機能」とも呼ばれており、大口の顧客や成績上位者の抽出などにも応用できる機能です。ここでは、売上上位の商品を第5位まで表示する場合を例に説明します。

ラベルの横にある「▼」から「値別（または「値フィルター」）」を選択し、「上位10」（Windows版では「トップテン」）をクリックします。

入力欄では抽出する数と単位の組み合わせを選びます。例えば数に「5」、単位に「項目」を指定すると「上位5項目」を表示します。また、数に「20」単位に「パーセンテージ」を指定すると「（全体の）上位20%」が表示されます。

トップテン機能の注意点

集計数値が同じで、ともに1位の項目が２種類存在する場合は、「上位1項目」と指定しても「上位2項目」と指定しても1位である２種類の商品名が表示されます。「上位3項目を合計すると売上全体の50%になり、上位4項目までを合計すると70%になる」というときに「上位60%」と指定する場合は、上位4項目が表示されます。

ピポットテーブルレポートフィルターで特定の項目（例：日付）だけを分析しよう

ここでは、特定の項目だけを抜き出した表を作りたいときに便利な「レポートフィルター」機能を紹介していきます。「各支店の商品ごとの年間売上推移を見たい」という場合を例に説明します。

このピボットテーブルでは、「支店」「商品」「日付」を行・列に振り分けて集計しています。知りたい項目は網羅されていますが、項目が多すぎて少し見辛くなっています。このような場合に「特定の支店の内訳だけ」を取り出すことができるのがレポートフィルターです。

レポートフィルターを使うには、画面右側の作業ウィンドウにある「フィルター」の枠に抽出したい項目を含むフィールドをドラッグ・ドロップさせて追加します。今回は「支店ごと」の売上だけに着目したいので「支店名」を追加します。

ピボットテーブルの上に「支店名」というセルが表示されました。隣のセルから表示したいデータを絞り込むことができます。ここから、特定の支店だけの売上に絞って表示することができます。試しに、「さいたま支店」の売上だけを表示させてみましょう。

「▼」をクリックし、「すべて選択」をクリックするとすべてのチェックを一括で解除できます。そこから、「さいたま支店」だけにチェックを入れます。

さいたま支店だけのデータを表示することができました。表示がすっきりと見やすくなっただけでなく、さいたま支店で売上のなかった商品も表示されなくなりました。これを応用すると「特定商品に絞って各店の月間売上推移を分析したい」「部署ごとの交通経費だけを比較したい」といったことも可能になります。
ただし、レポートフィルターで複数の項目にチェックを入れる場合は注意が必要です。指定した項目の数値を合算集計した数値が表示されますが、内訳は表示されません。

2つ以上の項目を比較したい場合は、もうひとつ別のピボットテーブルを作成して比較するなどして工夫しましょう。

まとめ

今回は、ピボットテーブルで使えるフィルター機能の使い方を説明しました。フィルターを使いこなすことによって特定の項目に的を絞って分析することができ、より効率的に作業を進められます。フィルターなどの機能を駆使すれば、集計によって得られた一定の傾向や課題に対して、さらに要因を分析することもできます。仕事を効率化したり、集計したりするだけでなく、課題の解消に向けた分析を実行できるのがピボットテーブルの強みです。フィルター機能自体は一般的なExcel表計算でも使用できる機能ですが、ぜひピボットテーブルでもフル活用して日々の業務に役立ててください。

こちらもオススメ