ピボットテーブルから複数の条件で抽出した結果を、別のセルに転記する「GETPIBOTDATA関数」

ピボットテーブルの集計結果を抜き出して表示する

　「ピボットテーブル」を使えば、複合集計やクロス集計を行って「表」を作成することができます

　このピボットテーブルで作成した「集計表」は、分析作業であれば簡単に集計条件を変えることができるのでとても便利な方法です。

　しかし、この集計表を「説明資料」として使う場合は集計結果が読み取りにくいという欠点があります。

　そんな場合はピボットテーブルで作成した集計表から、結果を抜き出して表示することで情報が伝わりやすくなります。

　今回はピボットテーブルから、集計結果を抽出して、集計結果を分かりやすく表示する「表」を作成する方法を説明します。

ピボッテーブルで複数の条件で抽出した結果を特定のセルに表示する

　ピボットテーブルでいろいろな条件で作成した「結果」を別の場所（集計表）に表示して分かりやすくします。

　用意した「支社名」、「商品名」、「年」のボックスに「条件」を入力すれば、集計結果が「金額」欄に表示されます。

　この方法を使えば、最後の方で紹介している「リスト入力」を使うことで「データベース」的な使い方をすることができます。

　　　を

リスト形式の「データ」を集計して表示する方法は複数あります

1. 通常の表から「SUMIFS関数」で集計
2. ピボットテーブルから「GETPIBOTDATA関数」で集計
3. テーブル化した上で「フィルター機能」を使って集計
4. ピボットテーブルで「スライサー」と「タイムライン」を使う

　それぞれの集計方法には一長一短があり、状況によって使い分けることで効率よく資料を作成することができます。 

１．SUMIFS関数で「複合集計」する

　複数の条件で集計するにはSUMIFS関数が有効です。

　SUMIFS関数を使った数式で、集計表を作成すれば別の場所に表示させることができます。

　しかしSUMIFS関数を使って集計する方法では「複合集計」は簡単にできますが、「クロス集計」は数式が複雑になり大変です。

　過去記事で紹介したとおり、複数の条件を設定して「抽出」する方法には色々ありますがあります。

VLOOKUPで「重複したデータ」を抽出して表示する - Excelの機能を活用して、事務作業の省力化や経営分析をする

1000行以上ある大きな表の集計

　　　　

　Excel2007から新しく加わった、SUMIFS関数は従来のSUMIF関数ではネスト（入れ子）の手法を使わなければできなかった「複数条件」の設定を簡単にできるようになりました。

　※ IF関数の複数条件は、最大7個まで

　しかしこのSUMIFS関数はそのままではクロス集計ができません。

「SUMIFS関数」は、リストから直接集計することができます

　SUMIFS関数を使えばリスト形式の表から直接、複数の条件で集計することができます。

　SUMIFS関数の弱点は、一つの系列で複数の条件を設定するのは簡単ですが、二つの系列に合致したものを抽出するクロス集計は数式がとても複雑になってしまいます。

「支社名」と「商品名」の複合条件に、「年」という条件を加えてクロス集計にする

　SUMIFS関数でクロス集計をするためには、クロス集計の基準にしたいフィールド（項目）をあらかじめ作成しておけば簡単に集計することができます。

　　　

「日付」のデータから、「YEAR関数」を使って「年」を抜き出す

　数式を使って「日付」のデータから、「年」のフィールド（項目）を作成します。

　　　　

「日付」から「年」が抜き出されました

　　　　

※ フラッシュフィルを使って「年」を抜き出す

　「年」を抜き出す作業を、数式ではなくフラッシュフィルを使えば、簡単に「年」のフィールド（項目）を作成することができます。

フラッシュフィルで「日付」から「年」を抜き出す

　　　　

フラッシュフィルの操作方法

1. フィルハンドルをダブルクリック
2. データが下方向にコピーされ、画面の最下行の「オートフィルオプション」ボタンをクリック
3. 出てきたボックスで「フラッシュフィル」を選択

　　　　

フラッシュフィルで「年」が抜き出されました

フラッシュフィルは、条件さえ整えば関数を使うより簡単に作業をすることができます。

　　　　

SUMIFS関数を使って「支社名」「商品名」「年」の条件で、データを集計する

　「日付」のフィールドから「年」を抜き出すことによって、SUMIFS関数でも「クロス集計」ができます。

1. 合計対象範囲に「金額」のフィールド（範囲）を指定します
2. 条件範囲１に「支社名」 のフィールド（範囲）を指定します
3. 条件１に「支社名」のデータを入力するセルを指定します
4. 条件範囲２に「商品名」のフィールド（範囲）を指定します
5. 条件２に「商品名」のデータを入力するセルを指定します
6. 条件範囲３に「年」のフィールド（範囲）を指定します
7. 条件３に「年」のデータを入力するセルを指定します

　　※ 「TABキー」を押すことで、「項目」を移動できます

　　※ 「条件2」でTABキーを押すと「条件範囲3」に移動します

　　※ SUMIFS関数は「条件」を127個まで設定できます

　　　

 ２．GETPIBOTDATA関数を使って、ピボットテーブルから「支社名」「商品名」「年」の条件で、データを集計する

　ピボットテーブルで作成した集計表は、GETPIBOTDATA関数を使えば簡単にクロス集計をして集計結果を表示することができます。

「注文書」をピボットテーブル化します

1. 「表」のどこか1カ所を選択します
2. 「挿入」タブを選択
3. 「ピボットテーブル」をクリックします
4. ピボットテーブルの作成ボックスで「OK」をクリックします

　　　　

フィールドを各ボックスに配置します

1. 「支社名」を「行」のボックスにドラッグします
2. 「商品名」を「行」のボックスにドラッグします
3. 「金額」を「∑値」のボックスにドラッグします
4. 「年」を「列」のボックスにドラッグします

　　　　

GETPIBOTDATA関数を「金額」に設定します

　集計結果を表示するセルに、GETPIBOTDATA関数を使って数式を入力します

　数式「 =GETPIVOTDATA("金額",B5,B2,B3,C2,C3,D2,D3) 」

1. データフィールドに、「金額」をダブルクォーテーションで囲んで文字化して入力「 "金額" 」
2. ピボットテーブルの先頭のセルを選択して、ピボットテーブルを指定します
3. フィールド１に、「支社名」のセルを指定します
4. アイテム１に、「支社名の条件」を入力するセルを指定します
5. フィールド２に、「商品名」のセルを指定します
6. アイテム２に、「商品名の条件」を入力するセルを指定します
7. フィールド３に、「年」のセルを指定します
8. アイテム３に、「年の条件」を入力す済セルを指定します

　※ 「データフィールド」の項目は「セルを指定」ではないことに注意

　　　

GETPIBOTDATA関数を使って、ピボットテーブルから特定の条件で集計することができました。

　　　

※ 集計する条件を「リスト入力」を使って簡単にしてみます

　集計の条件を「リスト入力」で入力できるようにすることによって、簡単なデータベースを作成します。

　※ リスト入力の設定を簡単にする方法を紹介します

リスト入力用のデータを作成します

　コマンドを使って、「支社名」のデータ全てをコピーします

　　　　

重複しているデータを削除します

1. どこか適当な場所に、コピーした「支社名」のデータを貼り付けて、そのままの状態にしておく（※ データが選択されている状態）
2. 「データ」タブを選択
3. 「重複の削除」をクリック　　
4. 「列」が正しく選択されていることを確認
5. 「OK」をクリック

　　　　

重複していた「支社名」が削除され、「リスト入力」用のデータができました

　「商品名」、「年」も同様に、リスト入力用のデータを作成します

　　　　

　　　　

設定が簡単になるように、セル範囲に名前を付けます

リスト入力用のデータ範囲に「名前」を付けます

1. 「支社名」のデータ部分を範囲選択します
2. 名前ボックスに「支社名一覧」と入力します

　　※ データ範囲に名前を付けておくことで、「F３キー」で呼び出すことができます

　　※「商品名」「年」部分も同様に「名前登録」します

　　　　

リスト入力を設定します

1. リスト入力を設定するセルを選択します
2. 「データ」タブを選択
3. 「データの入力規則」をクリック
4. 入力値の種類で、プルダウンリストから「リスト」を選択
5. 元の値のボックスで「F３キー」を押して、「名前の貼り付けボックス」を表示させる
6. 名前の貼り付けボックスで、名前を付けた範囲「支社名一覧」を選択

　　　　

同様に「商品名」、「年」にリスト入力を設定

　　　

 ピボットテーブルに「検索機能」が付きました

　　　

　このようにピボットテーブルにGETPIBOTDATA関数を組み合わせれば検索機能を付けることができます。

　この方法を使えば、たとえば請求一覧表などから簡単に集計を行うことができます。

３．「フィルター機能」を使って、複数条件で集計する

　リスト形式の「データ」をテーブル化し、フィルター機能を使うことで簡単に集計することができます。

「表」をテーブル化する

1. 「表」のどこか1カ所を選択します
2. 「挿入」タブを選択
3. 「テーブル」をクリックします
4. 「先頭行をテーブルの見出しとして･･･」にチェックを入れる
5. 「OK」をクリック

　　　

表がテーブル化されました

　　　

 「フィルター機能」を使って、複数条件で集計します

支社名に「島根支社」でフィルターをかけます

1. 「支社名」のラジオボタン「▼」をクリック
2. テキストフィルターで「すべて選択」のチェックを外す
3. テキストフィルターで「島根支社」にチェックを入れる

　　　

商品名に「無糖コーヒー」でフィルターをかけます

　　　

年に「2016」でフィルターをかけます

　　　

「島根支社」「無糖コーヒー」「2016年」でフィルターがかかりました

　　　

テーブルツールから「集計行」を追加します

1. 表のどこか1カ所を選択します
2. 「テーブルツール」を選択
3. 「デザイン」タブを選択
4. 「集計行」にチェックを入れます
5. 集計行が追加されました

　　　

テーブル化された表の「フィルター機能」で特定の条件で集計されました

　　　

　このように「表をテーブル化」して、フィルターをかけることによっても複数の条件から集計することができます。

　フィルター機能を使って集計する方法は条件の変更に若干手間がかかるのが難点です。

　しかし、それまでの下準備が簡単なので気軽に集計することができます。

　そして、「テーブル」にはいろいろなスタイルが用意されているので、資料としてプリントアウトする必要があるときには視覚的に訴えることができる資料を作成する有効な方法です。

４．ピボットテーブルから「スライサー」「タイムライン」の機能を使って集計する

　ピボットテーブルにも「スライサー」、「タイムライン」という便利な機能があり、この機能を使うことによって複数条件で集計することができます。

　この方法の便利な点は、条件をリアルタイムで変更することができる特徴を利用して、プロジェクター等を使ってプレゼンする際などに有効な方法です。

「スライサー」を作成する

1. ピボットテーブル内のどこか１カ所を選択する
2. 「ピボットテーブルツール」が表示されているのを確認
3. 「分析」タブを選択する 
4. 「スライサーの挿入」をクリック
5. スライサーの挿入で「支社名」と「商品名」にチェックを入れる

　　　　

 「タイムライン」を作成

1. ピボットテーブル内のどこか1カ所を選択する
2. 「ピボットテーブルツール」が表示されているのを確認
3. 「分析」タブを選択する 
4. 「タイムラインの挿入」をクリック
5. スライサーの挿入で「日付」にチェックを入れる

　　　

 「スライサー」と「タイムライン」を使って集計をします

 　タイムラインの設定をします

　　　

支社名「島根支社」、商品名「無糖コーヒー」、年「2016」で集計します

　　　

　このようにピボットテーブルの「スライサー」と「タイムライン」を使えば、クリックするだけで即座に集計内容を変更できます。

　プロジェクターでリアルアイムでプレゼンする際などに、とても有効な機能です。

　　　

　スライサーやタイムラインを使えば、「報告会」で何種類も資料を用意して「資料Bの中ほどを見てください、資料Eの最後の項目を見てください」のように説明する必要がありません。

　以上のようにExcelにはさまざまな集計方法があります。

　ピボッテーブルを使えば比較的簡単に様々な状況に対応することができますが、簡単な条件での集計であればテーブル化する方法が手軽にできます。

　「紙媒体の資料を作成」する、「プロジェクターを使ってプレゼンする」等、状況によって使い分けることで、資料作成の効率化をしてください。

k-ohmori9616.hatenablog.com