先日、VBAについて書いた記事が割と好評で、じっくり読んでいただけているようです。プログラムの最低限の決まりごとと、３つ構文だけでかなりのことが出来ることに気付いて頂けているのではないかと期待しています。
VBAを推奨しておいて、相反することになるかもしれませんが、今日は「sumif」「sumifs」関数について触れたいと思います。「プログラムはエンドユーザーの領域を外れてますよ」という方、あるいは「属人的になるのを避けたいのでエクセルのメニューの範囲でどうにかならないか？」という方には、「sumif」「sumifs」がお薦めだと思います。
データーベースの操作
「これだけ知っていれば充分！易しいVBAの教科書」でも、体験していただきましたが、基本的にデータベースを加工することを前提にお話しします。何故なら、仕事の場で集計や分析を行うにあたって、データベースを元に作業を行うことが自然だからです。実務に携わる方ならお分かりだと思いますが、表形式のデータを別の切り口で再集計するより、データベースを好きな切り口で編集する方が遥かに楽だからです。
データベースの説明は別の機会に譲るとして、今回も「これだけ知っていれば充分！易しいVBAの教科書」で使用したサンプルを使用してデータベースから集計する作業をやってみたいと思います。
サンプルのダウンロードはこちら → sample_02
sumif関数
まずは、sumif関数です。サンプルは、ダウンロードしたエクセルの”加工data”シートを使います。
ご覧のようなデータを、sumif関数を使って前期と当期の実績の比較をすることにします。
新規のシートを追加して下さい。（ここでは、sheet2とします）
sheet2に、下図のような前期／当期の集計表を作成します。
セルC3を選択して、数式バー左端のfx記号をクリックします。
すると、関数のウィザードが立ち上がります。
関数名から「sumif」を選択して下さい。すると、
ご覧の３つのボックスが表示されます。「範囲」「検索条件」「合計範囲」の３つです。関数を苦手とする多くの方が、この名前の意味が分からず挫折するようです。説明しますと…
範囲 ＝ここの場合、条件（前期か当期か）を判断できる情報が存在する列です。
検索条件＝集計したい条件です。
合計範囲＝合計したい数値情報を持っている列です。
まず、範囲のボックスをクリックし、「前期か当期か判断できる情報を持っている」”加工data”シートを選択します。そして、下図のように、Ａ列を選択します。（この時Ａ列は列の絶対参照にして下さい。）→絶対参照についてはこちらのサイトをご覧ください。
次に、検索条件のボックスをクリックし、「集計したい条件」を指定します。セルC３には、前期の数値を集計したいので、セルＢ３の”前期”を指定します。（この時も列の絶対参照）
最後に、合計範囲のボックスをクリックし、「合計したい数値情報を持っている」”加工data”シートを選択します。そして、下図のように、H列を選択します。（この時は列の相対参照にしてください。）
設定する条件は以上です。OKをクリックして下さい。
ご覧のような数値になりましたか？違っていたら、数式バーの式を確認して下さい。どこを間違っているかわかるはずです。
あとは、C３の数式を他のセルにコピペすれば完成！
インサイトを得るために、差と成長率を加えましょう。
販売数量は増えているのに、売上高が減少。粗利も大きく減少。安売りしてるのではないか？あるいは、単価が低く、且つ、利益率が低い商品が売れているのではないか？という状況が見えてきます。
以上が、sumifの使い方です。慣れれば、プログラムを組むよりは圧倒的に短い時間で集計できます。データの増減にも対応できます。
しかし、ほとんどの場合、一つの条件で集計することは稀でしょう。このような当期、前期の比較でも、前期は１２ヶ月揃っていても、当期は直前の月までであれば、揃えて比較できません。6月なら6月、7月なら7月までの累計値で比較するのが普通です。そうなると、条件は２つ。年（当期／前期）と月の条件を指定する必要があるのです。
そんな時に使う関数が、sumifs関数です。sumifの複数形のようで覚えやすいです。使い方もsumif関数とほぼ同じです。sumifでは最後に指定した「合計範囲」が、sumifsでは最初に指定するくらいの違いです。
sumifs関数
では、sumifs関数をやってみましょう。先ほどのエクセルの同じデータ（シート”加工data”）を使います。シート”東京”に数字を埋めるのにsumifs関数を使うことにします。
シート”東京”のセルＢ５にsumifs関数を設定します。sumifs関数を選択して下さい。
ご覧のように、合計対象範囲から指定します。まずは数量の合計なので、シート”加工data”のH列を指定します。（ここでは列の絶対参照）
次に、カテゴリーを決めましょう。条件範囲１で、シート”加工data”のE列を指定します。（ここも列の絶対参照で）
そして、条件１を設定します。（条件１のボックスを選択した時点で、条件範囲２のボックスが表示されます。また、カテゴリーの条件１は行の絶対参照にして下さい。）
ここまでで、カテゴリーの設定が完了しました。引き続き、サイズの設定を行います。
ここまでと同じ要領で、条件設定２と条件２を設定します。（どちらも列の絶対参照）
次に東京を指定します。（もし、条件範囲３のボックスが表示されていない場合は右端のコントロールバーを下にずらして下さい。下から現れます。）
東京と表記されたセルがないときは、下のように「” ”」で囲むことで、直接テキスト入力することも可能です。
最後は、当期か前期かを指定します。
以上で、東京の前期のカテゴリー①、サイズ①の合計が計算されました。このセルの計算式を、Ｂ５〜Ｊ１３のセルに貼り付けると以下のようになります。
エクセルVBAでは、条件指定に不等号（”＜”や”＞”）が使えましたが、sumif関数やsumifs関数では一致した場合の集計だけです。したがって、残りのセルは、条件を減らしながら計算することになります。
例えば、各カテゴリー毎の合計は、サイズの条件を削除すれば算出されます。
同様に、各サイズ毎の集計は、カテゴリーの条件を削除すれば算出されます。
総計は、 ‘東京’ ‘前期’以外の条件を外せば算出されます。
other zizeやother categoryの条件を外すことで計算が可能になります。
式を正しく設定できれば、以下のようになります。
まとめ
いかがでしょう。正しく集計できましたか？「これだけ知っていれば充分！易しいVBAの教科書」のような手法でなくても、式さえ設定しておけば、データの追加だけで最新の集計結果が得られます。
もちろん、ピボットテーブルでもクロス集計は可能ですが、セグメントの増減や実績のないカテゴリー／サイズの存在で、表のフォームが定まらない可能性があることが欠点です。
sumifs関数は、フォームを設定した上での集計ができるので、集計の自動化が実現します。VBAに劣る点があるとすれば、前述した通り、不等号の計算ができないことでしょう。しかし、その問題さえ回避できるならば、VBAよりはエンドユーザー向けの方法だろうと思われます。
是非、習得してエクセルの自動化、業務改善にご活用ください。