あまり知られていない機能ですが 複数のワークシート範囲を指定することで 様々な処理ができます。「
複数の範囲から重複を省いたリストを作る」などがそれです。今回はそれを応用して「A○B×」「A×B○」「A○B○」の3つを ひとつのピボットテーブルで振り分けします。
因みに Excel2007では ピボットテーブルウィザードがデフォルトでは表示されていないので この機能がなくなったとお思いの方もいらっしゃるかもしれませんが クイックアクセスツールバーのカスタマイズで[すべてのコマンド]から[ピボットテーブル/ピボットテーブルグラフ]のボタンは追加できます。手っ取り早く済ませるなら Alt + D・Pで起動してもいいと思います。
まず これだけはしないといけませんが 列ごとの重複データはフィルタオプションの設定などで省いておいて下さい。1列内で重複がない場合は気にする必要はありません。左端列は1列開けておいて下さい。ではピボットテーブルを起動します。
★Excel2003以前
[データ]→[ピボットテーブルや……]
★Excel2007以降
「Alt」キーを押しながら「D」「P」の順にキーを押す
→[複数のワークシート範囲]を選択して[次へ]
→[指定]を選択して[次へ]
次の範囲指定の仕方が少し特殊です。範囲は少し多めにとっておいてもグループ化が必要な場合以外は大丈夫です。
[範囲]に
「A1:C20」を[追加]
「A1:B20」を[追加]
→[次へ]
A列の方を二重に追加するのがポイントです。(11.06.04 修正)
[既存のワークシート範囲]を選択
今回は「E1」セルを指定
→[レイアウト]※
行フィールドの[行]と 列フィールドの[列]をドラッグで外す
行フィールドに 右の欄から[値]項目をドラッグで挿入
→[OK]でひとつ前の画面に戻る
→[オプション]で[行・列の総計]と[表のオートフォーマット]のチェックを外して[OK]
→[完了]※
※Excel2007以降は [レイアウト]がなくなりましたが 画面の右側のペインから直接指定できます。
これでできあがりです。
「A○B○(AにもBにもある)」 ……「3」
「A○B×(AにあってBにない)」……「2」
「A×B○(AになくてBにある)」……「1」
→参考ファイル(041.xls)
出来上がったピボットテーブルは振り分けはできていますが 抽出はできていません。抽出しようにもピボットテーブルの複数のワークシート範囲の合計は選択して抽出できません。「これじゃ意味ねーよ」と云われそうですが 抽出はオートフィルタを使えばできます。
ピボットテーブルにオートフィルタは設定できないと思ってらっしゃる方も多いかもしれませんが そんなことはありません。実は簡単です。上の表の場合
G1セル上で
[データ]→[フィルタ]→[オートフィルタ]
以上です。
フィルタ以外の隣のセルから設定してやれば フィルタは作れます。