Excel。テーブルでSUMIF関数を使うと算出されるけど、数式が変？というご質問
＜テーブルとSUMIF関数＞
先日ご質問を受けた中で、テーブルにして、SUMIF関数を使って算出してみたら、
答えはあっているようなんだけど、数式がおかしいというか？
なんか変なのですが、大丈夫なんですかね？というもの。
よく聞いてみると、なんでも数式を作るたびに、引数が変わっていて、
それも気になるということでした。
これは、【構造化参照】のことだな。と気づきましたが、せっかくですので、
SUMIF関数からおさらいして、
テーブルでのSUMIF関数を使った場合も合わせて確認していきましょう。まず、下記の表があります。
F2にBと入力されていて、G2に商品Bの合計金額を算出するという表ですね。
では、早速G2をクリックして、SUMIF関数ダイアログボックスを表示しましょう。
範囲には、B2:B24
検索条件には、F2
合計範囲には、D2:D24
を設定してOKボタンをクリックしましょう。
これで、算出されましたね。
このSUMIF関数が苦手な方の多くが、最初の範囲についてどこを範囲選択すればいいのか？
悩んじゃう人のようです。
ここでいう範囲は、その次に設定する、検索条件が含まれている列のことなのです。
確かにちょっと、ややこしいですね。
さて、通常のSUMIF関数は問題ないのですが、
今度は、A1:D24をテーブルにしてSUMIF関数を使って同じように算出してみましょう。
せっかくテーブルにしたので、ついでではありますが、集計も表示しておきましょう。
先ほどと同じようにG2にSUMIF関数ダイアログボックスを表示しましょう。
今回のようにドラッグできる大きさならいいのですが、
大きなテーブルということもありますので、ショートカット。
Ctrl + Shift + ↓
で、いっぺんに範囲選択できますよ。練習もかねてショートカットを使いながら作っていきましょう。
範囲は、テーブル1[商品]
検索条件は、F2
合計範囲には、テーブル1[[#データ],[#集計],[金額]]
あれれ？先ほどと変わって、[商品]と表示されていますね。
これは、【構造化参照】といって、
テーブルで計算式を使うと、そのフィールド名を使うようになるのです。
ちなみに、テーブル1とは、テーブル名なので、テーブル名を設定すれば、変わります。
ところで、今回の質問で、不安に思ったのは、合計範囲なんでしょうね。
[[#データ],[#集計],[金額]]
これは、なんなのか？というと、金額フィールドのデータと集計行という意味です。では、OKボタンをクリックしましょう。
数式は、
=SUMIF(テーブル1[商品],F2,テーブル1[[#データ],[#集計],[金額]])
今回集計行が含めているけど、大丈夫なのか？と思うかもしれませんが、B列の商品がBだったら計算するわけなので、
集計行の商品には、Bと入力されていませんので、関与することはありません。
ですので、本来は、データのところだけを範囲選択してあげると数式としては、
わかりやすくなります。
今度は、合計範囲に集計行を含めないでSUMIF関数の数式を作ってみると、
合計範囲は、テーブル1[金額]とフィールド名だけが設定されてました。
=SUMIF(テーブル1[商品],F2,テーブル1[金額])
という数式ですね。
このように、テーブルにすると【構造化参照】になるということ、
そして、集計行を表示していると、範囲選択によって、
【構造化参照】されているところが細分化されることがあるということを知っていると、
ビックリしなくなるかと思います。
