メディア個別 【エクセル術】SUMIF関数で、条件を満たすデータの数値だけを合計 | editeur エディトゥール

editeur

検索
第4回 | デキる男の必修科目──大人のエクセル一問一答

【エクセル術】SUMIF関数で、条件を満たすデータの数値だけを合計

ビジネスマンがエクセルを使う際に、もっとも頻繁に利用する機能のひとつが「合計」だろう。「すべての商品の売り上げ総額」をSUM関数で求める程度は、すでにマスターしているはずだが、これが「特定の商品の売り上げ総額」となれば話は別。計算法がわからず、ひそかに目視で商品名を探し個別計算している人も、意外と多いのでは? これではエクセルを使う意味がない。今回の問題に挑戦し、ワンランク上の「合計」が出せる男を目指してみよう。

問題:
関数を使い、名前に『顆粒』を含む商品の合計販売数を求めよ。

難易度:☆☆☆☆☆
実用度:☆☆☆☆☆
目標ステップ数:5

解答:

「SUMIF(サムイフ)」関数を使えば、指定した条件(キーワード)に一致する数値だけの合計が出せる。問題で取り上げた「『顆粒』を含む」というように、複雑な検索条件を指定する場合は、関数の中で「ワイルドカード」を使う。「ワイルドカード」については、後で詳しく説明するので、まずは標準解答の手順を確認しよう。

●STEP1

計算結果を表示させたいセル(ここではD9)をクリックして選択。半角で「=SUM」と入力し、表示される関数の候補から「SUMIF」をクリックする。

●STEP2

検索対象となる「商品名」が含まれたセルの範囲(ここではA2:A10)を選択し、キーボードから「,(カンマ)」を入力。

●STEP3

キーボードから半角で「”*”&」と入力。検索条件が入ったセル(ここではD6)を選択し、さらに半角で「&”*”」と入力し、最後に「,(カンマ)」を入力。セルを選択せず、直接「”*”&D6&”*“,」と入力しても構わない。

●STEP4

合計したい数値が含まれるセルの範囲(ここではB2:B10)を選択し、「Enter」キーを押す。

●STEP5

「商品名」の欄で『顆粒』を含む商品の合計販売数が表示される。

【今回のまとめ】

検索条件に一致する数値だけを合計する「SUMIF」関数は「=SUMIF(検索範囲,検索条件,合計する範囲)」という構文になる。範囲の合計を求めるSUM関数よりも一歩進んだ、さまざまな使い道がある関数なので、ぜひおぼえておこう。

今回の問題で、もっとも難しいのは「検索条件」の指定方法だ。「『顆粒』を含む」商品名を検索するというと、エクセルの数式で文字列を直接指定する場合は「”」で囲むのがルールなので、

=SUMIF(A2:A10,“顆粒”, B2:B10)

としても良いのでは? と思うかもしれない。しかし、これでは「『顆粒』という文字列そのもの」という意味になってしまい、『顆粒』以外の文字列が含まれる「オキルネン顆粒」などは、すべて対象外となってしまう。そこで登場するのが、「ワイルドカード」と呼ばれる特殊文字だ。ここでは「任意の(0字以上の)複数文字」をあらわす「*」を使っている。

たとえば、「*顆粒」とすれば、『顆粒』の前に文字列が並ぶ「ネムニールA顆粒」や「オキルネン顆粒」などが検索対象になるし、「顆粒*」とすれば、『顆粒』の後に文字列が並ぶ「顆粒・オキルネンDX」などが検索対象になる。

さらに、今回の解答例のように「*顆粒*」とすれば、前後や中盤にかかわらず、どこかに『顆粒』が含まれる文字列を、検索対象にできるわけだ。

なので今回の問題の場合、入力する関数は、

=SUMIF(A2:A10,”*顆粒*“,B2:B10)

としても正解になる。ただし、これでは『顆粒』が含まれる場合にしか使えないので、ちょっと物足りないはず。そこで、

=SUMIF(A2:A10,”*”&D6&”*“,B2:B10)

として、「セルD6に入力されたキーワードを含む文字列」を検索対象に指定した。「”*”&D6&”*“」は「*《D6の内容》*」という意味になる。これならば、

というように、D6に別のキーワードを入力した場合でも、対応する商品の販売数が合計できるので便利だ。

最後におぼえておきたいのが、セルに入った文字列同士を連結する際に使う「&」という演算子。たとえば「A1+B1」とした場合には、A1とB1に入った数値を足す、という意味になるが、「A1&B1」とした場合には、A1とB1の内容を連結する、という意味になる。

A1に「12」、B1に「34」という数値が入っている場合、「A1+B1」は足し算なので「46」になる。いっぽう「A1&B1」はセル内容の連結なので「1234」になる、というわけだ。

今回は、関数よりもワイルドカードのほうが難しい問題となってしまったが、エクセルをデータベース的に使う場合、「*」の使い方を知っておけば、目的に合ったデータを簡単に検索できるようになる。ちなみに、「*」はワードの検索・置換でも利用できるので、機会があったら試してみると良いだろう。

Text by Toshiro Ishii

editeur

検索