Excel。別ブックのデータをSUMIF関数で算出。しかし、別のブックを閉じちゃうと！
＜SUMIF関数とSUMPRODUCT関数＞
何気なく、出来ることでも、急に出来なくなるということも、Excelでは結構あるようでして、
今回ご紹介するのは、ブック間での集計に関してなんですね。
別のブックにあるデータを、SUMIF関数を使って、算出することは出来るのですが、
元の別のブックを閉じてしまうと…ということで、実際に確認してみましょう。
別ブックには、このようなデータがあります。
また、このブック名は、「元データ.xlsx」です。
そして、集計する別のブックには、
B3に、SUMIF関数を使って、ブック間の集計をしてみましょう。
B3をクリックして、SUMIF関数ダイアログボックスを表示しましょう。
範囲には、[元データ.xlsx]SUMIF!$B$3:$B$21
この[元データ.xlsx]は、ブック名を表しています。
なお、範囲選択をすると自動的に、絶対参照で設定されます。
ここが、ブック間での違いの一つでもあります。
検索条件は、A3。支店名を東京ということですね。
合計範囲には、[元データ.xlsx]SUMIF!$F$3:$F$21
あとは、OKボタンをクリックしましょう。
問題なく算出することが出来ました。
ちなみに、B3の数式は、
=SUMIF([元データ.xlsx]SUMIF!$B$3:$B$21,A3,[元データ.xlsx]SUMIF!$F$3:$F$21)
さて、ここからが、問題なのです。
実務では、元データをいちいち開いておく必要はないわけですね。
つまり、この集計してあるブックのみを開いてみると、どうなるのでしょうか？
Excelをすべて閉じて、このSUMIF関数のブックのみを開いてみましょう。
なんと、エラーになってしまいました！
このSUMIF関数を使ったブック間の算出。
実は、元のデータがあるブックを先に開いておく必要があるのです。
でないと、エラーになってしまうのです。
これをどうにかなりませんか？というご質問があるのです。
確かに、実務では、困りますよね。いちいち、元データを開くのは面倒。
そこで、登場するのが、SUMPRODUCT関数なのです。
とりあえず、作成してみましょう。元データ.xlsxも開いておきます。
B7をクリックして、SUMPRODUCT関数ダイアログボックスを表示しましょう。
配列1には、[元データ.xlsx]SUMIF!$B$3:$B$21
配列2には、[元データ.xlsx]SUMIF!$F$3:$F$21
数式を修正しないといけませんので、まずは、ここでOKボタンをクリックしましょう。
数式は、
=SUMPRODUCT([元データ.xlsx]SUMIF!$B$3:$B$21,[元データ.xlsx]SUMIF!$F$3:$F$21)
を
=SUMPRODUCT(([元データ.xlsx]SUMIF!$B$3:$B$21=A7)*[元データ.xlsx]SUMIF!$F$3:$F$21)
というように修正します。
配列1をカッコで囲み、$B$21のあとに=A7をいれて、カンマ(,)とアスタリスク(*)を置換します。
すると、このように算出されました。
先程のSUMIF関数と同じ数値になっています。
では、Excelをすべて閉じて、この算出用のブックのみを改めて開いて確認してみましょう。
ブックを開くと、次のようなメッセージが表示されますが、"更新する"をクリックします。
ちゃんと、SUMPRODUCT関数は算出してくれていることが確認できました。
このように、ケースバイケースですが、SUMIF関数よりも、
SUMPRODUCT関数を使った方が効率がいいということもありますので、
知っておくと、いざという時、いいかもしれませんね
