Excel

SUMIF関数の使い方|以上・未満などの比較演算子とワイルドカード

 

Excel(エクセル)の作業効率と正確さを10倍高める基本関数の1つ、SUMIF(サムイフ)関数は、[条件に合うセルを対象にして合計を求める]関数です。

SUMIF関数の使用例

 

[=SUMIF( 範囲 , 検索条件,合計範囲 )]という書式で表します。

 

範囲
条件に合うかどうかを判定する(条件判定の対象となる)セル範囲を指定します。

 

検索条件
データを合計するための条件を、数値、比較演算子やワイルドカードなどを使った文字列([""]ダブルクォーテーションで囲む)などで指定します。

 

合計範囲
データを合計する(集計する)セル範囲を指定します。※この合計範囲は、省略することができます。省略した場合は、範囲が合計の対象になります。

 

この記事では、SUMIF関数基本的な使い方から比較演算子ワイルドカードを使う応用テクニックまでを説明します。

 

 

SUMIF関数の基本的な使い方

SUMIF関数の書式

SUMIF(サムイフ)関数は、条件に合うセルを対象にして合計を求める関数です。

[=SUMTIF( 範囲,検索条件,合計範囲 )]という書式で表します。

 

SUMIF関数の基本的な使い方

SUMIF関数を使って4月度5月度6月度の売上をそれぞれ合計してみます。

4~6月度の売上をそれぞれ合計する

 

テクニック
日付から月を求める
には、MONTH関数を使います。

日付から月を求めるにはMONTH関数を使う

 

SUMIF関数を入力する範囲を選択

SUMIF関数を入力するF2セル~F4セルを選択します。

F2セル~F4セルを選択

 

SUMIF関数を入力する

(日本語入力OFFで)F2セルに[=su]と入力します。

すると、SUMIFが表示されるので、SUMIFを選択[Tab]キーを押します。

F2セルに[=su]と入力し、SUMIFを選択

 

F2セルに[=SUMIF(]と入力されます。

F2セルに[=SUMIF(]と入力される

 

範囲の指定

条件に合うかどうかを判定する(条件判定の対象となる)セル範囲を指定します。

ここでは、4月度を合計(集計)したいので、4月度が含まれるB2セル~B8セルを指定します。

ただし、5月度、6月度も求めるのでB2セル~B8セルが動かないよう絶対参照にします。

 

B2セルを選択します。

B2セルを選択

 

[Shift]+[Ctrl]+[↓](下矢印)と押し、B2セル~B8セルを選択します。

B2セル~B8セルを選択

 

絶対参照にする

[F4](ファンクションキー)を押して、B2セル~B8セルを絶対参照します。

SUMIF|F4キーを押して絶対参照にする

 

検索条件を入力する

[,](半角カンマ)を入力し、続けて、検索条件を入力します。今回の検索条件はE2セルに入力されているので、E2セルをクリックします。

=SUMIF($B$2:$B$8,E2]と入力されます。

[=SUMIF($B$2:$B$8,E2]と入力される

 

合計範囲を入力する

[,](半角カンマ)を入力し、続けて、合計範囲を入力します。

合計するのは、売上なので、売上が入力されているC2セル~C8セルを選択します。

[=SUMIF($B$2:$B$8,E2,$C$2:$C$8]と入力されます

 

先ほどと同じく、[F4](ファンクションキー)を押して、C2セル~C8セルを絶対参照します。

=SUMIF($B$2:$B$8,E2,$C$2:$C$8]となります。

[=SUMIF($B$2:$B$8,E2,$C$2:$C$8]となります

 

[Ctrl]+[Enter]で確定する

[Ctrl]+[Enter]と押して確定します。

[=SUMIF($B$2:$B$8,E2,$C$2:$C$8)]の最後のカッコは入力しなくても、勝手に入力されます。

F2セル~F4セルに各月度の売上の合計が入力されます。

F2セル~F4セルに各月度の売上の合計が入力されます

 

テクニック
[Ctrl]+[Enter]は、ほかにも便利な機能を備えており、Excel(エクセル)の作業効率アップには必須のショートカットキーですので、ぜひ使えるようになってください。このサイトでは、様々な記事で紹介しています。

 

 

検索条件に比較演算子を使う

各月の前半と後半の売上を分けて合計する

月の前半の売上と後半の売上の分析をするため、C2セル~C8セルの売上をSUMIF関数を用いて、15日までの売上と16日以降の分けて合計します。

月の前半と後半の売上を分けて合計する

 

テクニック
日付から日を求める
には、DAY関数を使います。

日付から日を求めるにはDAY関数を使う

 

F2セルにSUMIF関数を入力する

(日本語入力OFFで)F2セルを選択し、[=su]と入力します。

すると、SUMIFが表示されるので、SUMIFを選択[Tab]キーを押します。

F2セルに[=su]と入力し、SUMIFを選択

 

F2セルに[=SUMIF(]と入力されます。

F2セルに[=SUMIF(]と入力される

 

範囲の指定

条件に合うかどうかを判定する(条件判定の対象となる)セル範囲を指定します。

ここでは、日で判定するので、B2セル~B8セルを指定します。

SUMIF|B2セル~B8セルを選択

 

検索条件を入力する

[,](半角カンマ)を入力し、続けて、検索条件を入力します。

「15日まで」を式で表すと["<=15"]となります(詳しくは以下を参照のこと)。

 

テクニック
関数内で不等号や等号(比較演算子)を使用するときは、ダブルクォーテーション[""]で囲む

=SUMIF(B2:B8,"<=15"]と入力します。

[=SUMIF(B2:B8,15以下]と入力する

 

 

SUMIF関数で使える不等号や等号(比較演算子)

Excel(エクセル)関数内で使用するときは、指定する条件を、ダブルクォーテーション[""]で囲んでください。

演算子 使用例 意味
> ">100" 100より大きい
>= ">=100" 100以上
< "<100" 100より小さい(未満)
<= "<=100" 100以下
= "=100" 100に等しい(同じ)
<> "<>東京" 東京に等しくない

 

合計範囲を入力する

[,](半角カンマ)を入力し、続けて、合計範囲を入力します。

合計するのは、売上なので、売上が入力されているC2セル~C8セルを選択します。

SUMIF|合計範囲を入力する

 

[Enter]で確定する

[Enter]と押して確定します。F2セルに15日まで(日度が15以下)の売上が合計されます。

F2セルに15日までの売上が合計される

 

F3セルにSUMIF関数を入力する

(日本語入力OFFで)F3セルに、[=su]と入力します。

すると、SUMIFが表示されるので、SUMIFを選択[Tab]キーを押します。

F2セルに[=su]と入力し、SUMIFを選択

 

F3セルに[=SUMIF(]と入力されます。

F3セルに[=SUMIF(]と入力される

 

範囲の指定

条件に合うかどうかを判定する(条件判定の対象となる)セル範囲を指定します。

ここでは、日で判定するので、B2セル~B8セルを指定します。

SUMIF|B2セル~B8セルを選択

 

検索条件を入力する

[,](半角カンマ)を入力し、続けて、検索条件を入力します。

「16日以降」を式で表すと[">=16"]となります

=SUMIF(B2:B8,">=16"]と入力します。

[=SUMIF(B2:B8,16以上]と入力する

 

合計範囲を入力する

[,](半角カンマ)を入力し、続けて、合計範囲を入力します。

合計するのは、売上なので、売上が入力されているC2セル~C8セルを選択します。

SUMIF|合計範囲を入力する2

 

確定する

F3セルに16日以降(日度が16以上)の売上が合計されます。

F3セルに16日以降の売上が合計される

 




 

 

検索条件にワイルドカード[*]を使う

特定の文字列が含まれるセルを検索条件にする

A2セル~A8セルに特定の文字列が含まれるかどうかを検索条件にしてSUMIF関数で合計を求めます。

SUMIF|ワイルドカード[*]を使う

 

E2セルにSUMIF関数を入力する

(日本語入力OFFで)E2セルを選択し、[=su]と入力します。

SUMIFが表示されるので、SUMIFを選択[Tab]キーを押します。

E2セルに[=su]と入力し、SUMIFを選択

 

E2セルに[=SUMIF(]と入力されます。

E2セルに[=SUMIF(]と入力される

 

範囲の指定

条件に合うかどうかを判定する(条件判定の対象となる)セル範囲を指定します。

ここでは、A2セル~A8セルに特定の文字が含まれるかどうかで判定するので、A2セル~A8セルを指定します。

SUMIF関数の検索範囲を選択する

 

検索条件を入力する

[,](半角カンマ)を入力し、続けて、検索条件を入力します。

A2セル~A8セルの文字列を見ると、「A」+「任意の文字列」という形になっています。

結合演算子とワイルドカード

 

 

A」と「任意の文字列」を意味するワイルドカード*を使って、["A*"]としたいところですが、次に「B」や「C」もあるので、汎用性のある形にしておきます。

「A」はD2セルに入力されているので、これを使います。

 

 

結合演算子&を使って、[D2&"*"]とします。こうすることで、["A*"]を汎用性のある形にできます。

D2セルは、[=SUMIF(A2:A8,D2&"*"]となります。

[D2&*]

 

合計範囲を入力する

[,](半角カンマ)を入力し、続けて、合計範囲を入力します。

合計するのは、売上なので、売上が入力されているB2セル~B8セルを選択します。

SUMIF|合計範囲を入力する

 

絶対参照にしておく

D3セルやD4セルに式をコピーすることを想定して、範囲であるA2セル~A8セルと、合計範囲であるB2セル~B8セルを絶対参照にしておきます。

絶対参照にするときは、[F4](ファンクションキー)を使うと便利です。

A2:A8]の部分を選択し、[F4]キーを押します。

SUMIF|絶対参照にする

 

すると、[=SUMIF($A$2:$A$8,D2&"*",B2:B8]となります。

SUMIF|A2~A8が絶対参照にできた

 

 

つづけて、[B2:B8]の部分を選択し、[F4]キーを押します。

SUMIF|絶対参照にする

 

すると、[=SUMIF($A$2:$A$8,D2&"*",$B$2:$B$8]となります。

SUMIF|B2~B8が絶対参照にできた

 

 

[Ctrl]+[Enter]で確定する

[Ctrl]+[Enter]と押して確定します。

E2セルのSUMIF関数が完成

 

商品コードに「A」を含む、B2セルとB4セルの売上が合計されました。

 

フィルハンドルを使う

B3セル以下に式をコピーします。

E2セルの右下にカーソルを動かすと、カーソルの先端が[+]に変わるところがあります。

 

カーソルの先端が[+]に変わったら、ダブルクリックします。

テクニック
E5セルまでドラッグしてもかまいませんが、実務では、データがタテに長いこともしばしばです。このダブルクリックを覚えておきましょう。

フィルハンドルの説明

 

式がコピーされる

式がE5セルまでコピーされました。

SUMIF|式がE5セルまでコピーされました

 

以下では、うまくいっていないE6セルの検索条件を修正していきます。

 

特定の文字列が含まれるセルを検索条件にする2

A2セル~A8セルの文字列を改めて見ると、「任意の文字列」+「200」という形になっています。

SUMIF|ワイルドカード[*]を使う

 

E2セルのSUMIF関数を修正する

E2セルは、[=SUMIF($A$2:$A$8,D2&"*",$B$2:$B$8]となっています。

D2&"*"]では、任意の文字列が後ろにあるときにしか、うまくいきません。

 

そこで、["*"&D2&"*"]として、任意の文字列が前後どちらにあっても対処できるようにします。

 

E2セルの式を、[=SUMIF($A$2:$A$8,"*"&D2&"*",$B$2:$B$8]とします。

SUMIF|E2セルの式を修正する

 

確定する

[Ctrl]+[Enter]として確定し、フィルハンドルをダブルクリックして、式をコピーします。

フィルハンドルの説明

 

これで、E5セルが正しく表示されます。※わかりやすいように色をつけています。

SUMIF|ワイルドカード版完成

 

 

関連記事

参考文献

-Excel
-,

© 2021 まなびっと Powered by AFFINGER5