SUMIFS関数かピボットか
先の「エクセルで$マークを使いこなす」で紹介した
9割が解けなかったエクセルの入試試験問題
この2問目は"「SUMIFS関数」か「ピボットテーブル」の二択"とありますが、ビッグテーブルを対象に考えた場合、どうでしょうか。
実際に体験してその違いを見てみましょう。
上のファイルは店舗別の年間顧客数と商品購入数の表です。
顧客は男女区分と年齢区分がなされていて、全体で1万8千件以上の行があります。
まずは別シートに上記の表を作るとします。
=SUMIFS(合計する範囲,条件1を適用する範囲,条件1,...)
（...には条件2,条件3などを加えることができる）
折角なのでINDIRECTを使うことにし、それぞれの範囲をあらかじめ周囲に配置しておきます。
その上で一つのセルに参照式を入れ、ドラッグコピペするための$マークを入れます。（$マーク入れ方の詳細は「エクセルで$マークを使いこなす」）
そのまま横にドラッグして、2018年までの数字が入ることを確認してください。
同様に男性・女性毎の集計も行います。
今度は条件2として男性=1,女性=2と追加します。
=SUMIFS(INDIRECT($A$5),INDIRECT($A$4),C$4,INDIRECT($A$6$),1)
最初の式に、3つ目のINDIRECT以降が追加されました。女性の場合は最後の1を2にします。
$マークの付け方が間違っていなければ、横へドラッグするときれいに表が埋まるはずです。
これを応用すると、年齢別とか区分別も作れます。
ただし、それぞれが多くのデータから条件抽出して計算されたものなので、条件が増えると非常に重くなります。（店舗別を作成したら私の16GB PCでも10分くらい再計算に時間をとられました）
これに対し、ピボットテーブルは軽々と計算してくれて、しかも縦横の項目を簡単に入れ替えることができるという利点はあります。
ピボットテーブルの作り方はググると一杯出てくるので、それを参考にしてやってみてください。
上記の表だと、
こういう配置でできるはずです。
一点だけ、∑値のところ（赤枠）が合計 / UU数になっていることを確認してください。たまに、データの個数 / UU数になっている場合があります。
その変更は、その横にある▼を右クリックして下の値フィールドの設定を呼び出すことで可能です。(表のUU数はUnique User数＝顧客数の意味です）
そのほか店舗別とか、年齢区分別など、いろいろな切り口で試してみてください。
それをSUMIFSでもやってみると、違いが実感できます。（ただしSUMIFSを店舗別でやると相当時間がかかります）
まとめると、ビッグテーブルの場合は
・重くなりがちなので、まずピボットテーブルを第一に考える。
・ピボットテーブルはいろいろな切り口がある場合に簡単に切り口を変えて集計できる。
・一方切り口での集計結果は、その都度コピペして残しておかなければならない。
・SUMIFS関数は、重くなりがち。
・切り口が決まっている場合はあらかじめ集計表を用意しておくと、一度に集計ができる。
（先の例では範囲指定しましたが、A:Aのような列指定にすると、データ行数が増えても対応できます。ただ対象が増えるせいかさらに重くなる感じです。）
この記事が気に入ったらサポートをしてみませんか？