【SUMIF】【SUMIFS】条件を指定してスマートに足し算

数字を足し算するにはSUM関数を使えばいいと、こちらの記事に掲載しました。

今回は、その仲間の関数です。
めちゃくちゃよく使うので使いこなせるようになってください。

SUMIF関数｜条件を指定した合計

次のようなデータをダミーで用意しました。
何の会社なんでしょうね。

このデータに掲載されている個数の合計を出してみましょう。
はい、SUM関数の登場です。

350個も売れたんですっけ。景気いいですね（？）。

しかし、この中にはレモンもあれば緑茶もあるという、カオスな数字です。このままでは使い物にならなさそう。
というわけで、柑橘類だけの販売個数を取り出してみましょう。

このときのように、ある特定の条件に合致したデータだけを合計する場合にSUMIF関数を使用します。

はい出ました、複数の引数です。
SUM関数のときは「足したい範囲」だけ指定すればよかったけれど、SUMIF関数は引数が複数必要です。
言葉で書いてあってもイメージがしづらいので、実際の式を見てみましょう。

最初の引数は、条件となるデータが入っている範囲を指定します。
この場合はD列の「商品」の列。データそのものは2行目から11行目に入っているからD2:D11という指定をしています。

次は条件。
今回は「柑橘類」だけ取り出して見たいので、”柑橘類”と指定しています。
このように数字ではなく文字列も数式に含めることができます。
文字列を指定するときは” “で囲ってやってください。

最後の引数が、実際に合計したいデータが入っている範囲の指定です。

「ええ〜、そんな引数なんて覚えられない〜」とならなくても大丈夫。
SUM関数のときに見たように、Excelが「ひとつめの引数はコレだよ」と補助してくれるので、記憶する必要はありません。
ワタシもたまにしか使わない関数は、補助なしに書けません。

条件指定をちょっとスマートに

上の例で条件を”柑橘類”で指定しましたが、実際の業務では関数に文字列を含めるのはあまりオススメしません。
なぜなら「柑橘類はわかったから、今度は飲料のデータを拾いたい！」なんてときに、わざわざ数式バーで編集するのは面倒くさいからです。
例のように文字列を直接書き込むのは、検算とかでテンポラリーに作成するときくらいですかね。

というわけで、こんな方法をオススメします。
拾い上げたい条件をピックアップして、データ外のスペースに入力しておきます。

そして、条件の引数を”柑橘類”という文字列ではなく「柑橘類」が入ったセルを指定します。

こうすることで、条件をピックアップしたセルに「飲料」が入っていれば飲料の、「菓子」が入っていれば菓子の合計を出してくれます。

範囲指定をもっとスマートに｜相対参照と絶対参照を理解する

人間とは欲深いもので「柑橘類だけじゃなくて、飲料も菓子も一気にデータを見たいの！」となる生き物です。
仕方ない、それを叶えるべく全部の商品分類を挙げましょう。

柑橘類のためのSUMIF関数が入ったセルを、下の行にもコピーすればいいよね！
と考えたあなた。着眼点はいいけど浅はかです。
そのままずるっとコピペすると、こうなります。

Excelの親切なカラーリングを元に指定範囲を見ると、思った範囲とはズレていることがわかりますね。

これ、Excelでは数式をコピーすると、元のセルと指定範囲との関係性をそのまま持ってきてくれる仕様になっているからです。
これを相対参照といいます。
これはこれで便利なのですが、この場合はやりたいことの意図と違いますよね。

そこで、柑橘類の数式にちょっと細工をします。

なんか、$マークがあちこちにつきました。
これは「指定する行または列は、絶対にここです！」という指示です。
こうして$マークで場所を決めることを絶対参照といいます。

アルファベットは列、数字は行の場所でしたね。
それらの前に$をつけると「その列・行から動かしません」ということになります。
上の数式では、条件の範囲はD2からD11から動いちゃダメと指示しています。
が、条件のD13は「列はDで決まっているけど、行は相対的に動かしていいよ」となっています。
こう指定しておくと何がいいかというと、数式が入ったセルをずるっとコピペすると。

こんなカンジに、条件や合計の範囲は動かさないけれど、条件だけは元のセルとの相対的な位置にしていいよ、となります。

これ怖いのが、データの範囲や数式を入れるセルの位置によっては、相対参照でも絶対参照でも結果が同じ場合になることもままあるということ…。
そのせいで絶対参照せず数式をじゃんじゃかコピペして、後から「あっ」となることもしばしばです。
なのでワタシは数式を書いたら、それを後からコピーして使うかどうかわかんないけど絶対参照が必要な範囲には必ず$をつけておきます。コピペミスを防ぐためもあるし、どういう意図で作った数式かをわかりやすくするためでもあります。

$マークをつけるの、怠いわ〜。となりそうですが、意外と簡単。
数式バーで対象のセルが書かれた箇所の上にカーソルを置いて「F4」キーを押してください。
最初に行も列も固定した絶対参照に、もう一度押すと行だけ絶対参照に、次は列だけ絶対参照に、そしてもう一度押すと行列共に相対参照に戻ります。
Excel操作上で最も使うファンクションキーが、この相対・絶対の切り替えのF4です。と言っても過言ではありません。

SUMIFS関数｜条件を複数指定した合計

人間の欲とは底が知れないもので、「何がいくつ売れたかわかったけどさ、でも関西でどれだけ売れたかも知りたいじゃん…？」となる日が必ず来ます。
仕方ない、その欲望もExcelで満たしましょう。

菓子の関西での販売個数を知りたいということで、次のように条件指定の準備を整えてみました。

先ほどはSUMIF関数を使いましたが、これだと指定できる条件はひとつだけ。
２つ以上条件を指定したい場合は、SUMIFS関数を使います。
IFが複数形になったんですね。覚えやすいですね。

SUMIFは合計範囲を最後の引数に持ってきましたが、SUMIFSは最初に持っていきます。
そしてあとは条件の範囲を指定しては持ってきたいデータの条件を指定し、別の条件範囲を指定しては条件を指定し、また範囲と条件を指定し…と延々と繰り返すだけ。
指定できる条件の上限ってあるのかなあ。結構えぐい数の指定をやったことあるけど、特に文句も言われずExcelは処理してくれますよ。

さっきの例だと、こんな式になります。

なんでSUMIFとSUMIFSって関数が違うのさ。SUMIFSで、条件範囲と条件をひとつずつ指定すればSUM代わりになるんじゃないの？
ワタシの記憶が確かならば、昔はSUMIFS関数ってなかったと思うんですよね。違ったかな…。
その頃、こういう計算をしたいときにどうしていたかというと、IF関数という条件指定をさせる関数を併用して冗長に算出していました。なのでSUMIFSができたときに「うおー、めっちゃラク〜！」と感激した覚えがあるような…。
もし事実と違っていたらごめんなさい。そしてワタシの記憶はどうであれ、SUMIFとSUMIFSは使い分けてください。

おわりに

• 合計したいデータに条件があれば、SUMIF関数とSUMIFS関数を活用
• セルの相対参照と絶対参照にご注意を

てなことを頭に入れて、データをどんどん足してやってください！