Contents
練習問題１／条件の複数列指定、複数条件に合うデータの合計
【練習問題１ 問題】
下表は、4月上旬の地区別売上金額一覧です。
以下の条件に合うデータの売上金額の合計を求めてください。
・地区に「日本」という文字を含む
・日付が「4月10日」以前
・売上金額「150000」円以上
なお、売上金額合計（セルF21）は計算済みです。（青字で記載しています）。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題１ 解答】
それでは解答を見ていきましょう。
※上の図の解答はわかりやすいよう、地区が地区で「日本」を含む、日付が「4月10日」以前、売上金額「150000」円以上の売上金額合計の解答を赤文字で記入しています。
【セル】 【関数式】 【 値 】
D19 ： =SUMIFS(D7:D16,B7:B16,”*日本”,C7:C16,”=150000″)⇒ 900,000
【セル】 【関数式】【 値 】
D21 ： =SUM(D7:D16) ⇒ 2,350,000
※青字の式は参考式です。今回の解答対象ではありません。
▶解答のExcelデータをダウンロード
【練習問題１ 解説】
複数の条件に一致する売上金額合計を求める流れは以下のようになります。
複数の条件にあったセルの数値のみを合計したい場合、以下のSUMIFS関数を利用します。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2・・・])
[合計対象範囲] = 合計したい値が入力されているセル範囲を指定します。
[条件範囲1] = 条件を検索する対象のセル範囲を指定します。
[条件1] =条件となる文字や数値を指定します。
[条件範囲2,条件2・・・] = 追加で「条件」やそれを検索する「条件範囲」を指定します。
下の図のように、
[条件範囲1] をセルB7からB16までの中に[条件1]である「日本」を含む地区の条件を満たし、
かつ[条件範囲2] をセルC7からC16までの中に[条件2]である日付が「4月10日以前」の条件を満たし、
かつ[条件範囲3] をセルD7からD16までの中に[条件3]である売上金額が「150000円以上」の条件を満たした時に
[合計対象範囲]のセルD7からD16の中から対応するセルD8、D9、D14、D15を合計します。
地区で「日本」を含む、日付が「4月10日」以前、売上金額「150000」円以上の売上金額合計は「900,000」円となります。
下の図のように、先に[合計対象範囲]を指定します。そのあと条件範囲と条件を組み合わせて記載していきます。
【参考】指定の文字を含む条件の、ワイルドカード「*」の使い方
今回は、東日本と西日本、両方とも含めた合計をしたいのですが、
こういった場合は「ワイルドカード」と呼ばれる便利な文字を活用して検索しましょう。
ワイルドカード（あいまい指定）とは・・・
抽出したい文字の前か後、もしくは前後両方に付けることで、
対象の文字列を含んだ文字が検索対象となる方法で、
付け加える文字としては、以下の通りになっています。
| 記号 | 意味 |
|---|---|
| * | 任意の複数文字を表す |
| ? | 任意の１文字を表す |
ワイルドカード（あいまい指定）の使い方
「なにかの文字列 ＋ 日本 ＋ なにかの文字列」といった特定の文字列（ここでは「日本」）を含むセルを検索する場合は、「*」(アスタリスク)を利用して次のように条件指定します。 →「”*日本*”」
=SUMIFS(D7:D16,B7:B16,“*日本*”,C7:C16,”<=4/10″,D7:D16,”>=150000″)
特定の文字を含むセルをカウントしたいときは任意の文字を表す「*」を利用します。
例）「日本」を含むセルを検索したい時は？
| 文字列の前方に「日本」を含む文字の検索 例）日本株式会社など |
=SUMIF(範囲,”日本*“,合計範囲) |
| 文字列の中央に「日本」を含む文字の検索 例）東日本商社など |
=SUMIF(範囲,”*日本*“,合計範囲) |
| 文字列の後方に「日本」を含む文字の検索 例）西日本、など |
=SUMIF(範囲,”*日本“,合計範囲) |
【参考】条件に日付を記載する場合について
日付が「4月10日以前」の場合の、売上金額を合計する。今回の練習問題では[条件2]に記載しています。
(1)[条件範囲2] ： 日付の入力されているセル範囲[C7:C16]を指定します。
(2)[条件2] ：条件に『日付』を指定したい場合、「4/10」や「4月10日」など、「/」(スラッシュ)や「月日」を利用して表記することで、エクセルが自動で認識する機能があります。
「4月10日以前」の場合の、売上金額を求めるには、次のように条件を複数指定します。 → 「 ”<=4/10″ 」
=SUMIFS(D7:D16,B7:B16,”*日本*”,C7:C16,”<=4/10″,D7:D16,”>=150000″)
また、4月1日のみの売上金額を合計するには、次のように条件指定します。 → 「 ”4/1″ 」 ※等号と不等号は書きません。
=SUMIFS(D7:D16,B7:B16,”*日本*”,C7:C16,”4/1″,D7:D16,”>=150000″)
「4/1」「4月1日」といった形式で、「年」を省いて日付指定した場合、自動的に最新年(その年が2017年なら、2017年4月1日)として認識されます。過去の「年」を指定したい場合は、
「”2015/4/1″」「”2015年4月1日”」というように、明確に「年」を記入しましょう。
【参考】SUMIFS関数の[範囲]指定はずれないように要注意！
下の図のように同じ行を選択してください。
今回の練習問題では、「売上金額」が、[合計対象範囲] であり、[条件範囲3]でもあります。
どちらもセルD9～D16となるように関数へ記載してください。
下の図は、上の図の行列を入れ替えた表になります。
この場合には列の開始位置が合うように記載してください。
下の図の緑枠のようにずれてしまうと、条件に一致するセルの相対的な位置にあるセルを合計してしまい、
想定する計算結果と異なってしまいます。
それぞれの範囲の指定がずれたままでも関数としてエラーにならない場合もありますので、注意しましょう。
また、想定している計算結果とずれている場合には、範囲の指定がずれていないか確認なさってみてください。
【練習問題１ まとめ】
Excelのセルの中で、複数の条件にあったセルの数値のみを合計するにはSUMIFS関数を利用することを覚えましょう。
複数の条件を指定して合計を計算できるので、応用範囲の広い関数となります。
条件には日付も指定できますので、条件での日付の指定方法も確認しましょう。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2・・・])
[合計対象範囲] = 合計したい値が入力されているセル範囲を指定します。
[条件範囲1] = 条件を検索する対象のセル範囲を指定します。
[条件1] =条件となる文字や数値を指定します。
[条件範囲2,条件2・・・] = 追加で「条件」やそれを検索する「条件範囲」を指定します。
なお、類似しているSUMIF関数との違いも確認なさってください。
引数の指定順序が異なります。
集計対象が、SUMIFS関数では第1引数ですが、SUMIF関数では第3引数となります（上下の枠内のそれぞれ赤字部分）。
コピーして使用する際は注意が必要になります。
=SUMIF(範囲, 検索条件, 合計範囲)
[範囲] = 条件を検索する対象のセル範囲を指定します。
[検索条件] = 条件となる文字や数値を指定します。
[合計範囲] = 合計したい値が入力されているセル範囲を指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
練習問題２／or条件｜複数条件のいずれかに合うデータの合計
【練習問題２ 問題】
下表は、4月上旬の地区別売上金額一覧です。
地区が「関東」または「西日本」で、かつ4月6日以降の売上金額合計を求めてください。
なお、その他の３つの売上金額合計にはSUM関数が設定されており、計算済みです。（青字で記載しています）。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題２ 解答】
それでは解答を見ていきましょう。
※上の図の解答はわかりやすいよう、地区が「関東」または「西日本」で、かつ4月6日以降の売上金額合計の解答を赤文字で記入しています。
【セル】 【関数式】 【 値 】
F19 ： =SUM(SUMIFS(D7:D16,B7:B16,{“関東”,”西日本”},C7:C16,”>=4/6″))⇒ 1,400,000
【セル】 【関数式】【 値 】
F21 ： =SUMIFS(D7:D16,B7:B16,”関東”,C7:C16,”>=4/6″) ⇒ 800,000
F23 ： =SUMIFS(D7:D16,B7:B16,”西日本”,C7:C16,”>=4/6″)⇒ 600,000
F25 ： =SUM(D7:D16) ⇒ 2,350,000
※青字の式は参考式です。今回の解答対象ではありません。
▶解答のExcelデータをダウンロード
【練習問題２ 解説】
売上金額合計を求める流れは以下のようになります。
複数の条件のうち、いずれか条件を満たしたセルの数値のみを合計したい場合、以下のSUM関数とSUMIFS関数を利用します。
=SUM(数値1, [数値2・・・])
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2・・・])
[合計対象範囲] = 合計したい値が入力されているセル範囲を指定します。
[条件範囲1] = 条件を検索する対象のセル範囲を指定します。
[条件1] =条件となる文字や数値を指定します。
[条件範囲2,条件2・・・] = 追加で「条件」やそれを検索する「条件範囲」を指定します。
下の図のように、
[条件範囲1] をセルB7からB16までの中に[条件1]である地区が「関東」または「西日本」の条件を満たし、
かつ[条件範囲2] をセルC7からC16までの中に[条件2]である日付が「4月6日以降」の条件を満たした時に
[合計対象範囲]のセルD7からD16の中から対応するセルD12およびD14からD16を合計します。
地区が「関東」または「西日本」で、かつ4月6日以降の売上金額合計は「1,400,000」円となります。
今回は、関東または西日本、いずれかに一致する場合の合計をしたいのですが、
SUMIFS関数では、複数の条件に全て一致した場合に数値が合計されます。
そこで、「{}」（波かっこ）とSUM関数を利用します。
「{}」（波かっこ）とSUM関数を使った意味について・・・
「{}」で条件を囲むことによって、「どちらかの条件」（左側から優先）という意味になります。
そこで、SUM関数の無い
=SUMIFS(D7:D16,B7:B16,{“関東”,“西日本”},C7:C16,“>=4/6”)
とすると、表示される合計結果は、「関東」のみが優先された「800,000」となります。
これを解決する為に、上記の全ての数式を「SUM()」で囲み、
=SUM( SUMIFS(D7:D16,B7:B16,{“関東”,“西日本”},C7:C16,“>=4/6”) )
と修正することで、「関東で4月6日以降」の場合と、「西日本で4月6日以降」の場合が合計されるという仕組みになっています。
ただし、使用できない関数もありますので注意が必要です。
【練習問題２ まとめ】
Excelのセルの中で、複数の条件にあったセルの数値のみを合計するにはSUMIFS関数を利用することを覚えましょう。
複数の条件を指定して合計を計算できるので、応用範囲の広い関数となります。
条件の書き方やSUM関数と組み合わせるなどの工夫で、いずれかの条件を満たした場合（or条件）でも
数値を合計することが可能となります。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2・・・])
[合計対象範囲]= 合計したい値が入力されているセル範囲を指定します。
[条件範囲1] = 条件を検索する対象のセル範囲を指定します。
[条件1] =条件となる文字や数値を指定します。
[条件範囲2,条件2・・・] = 追加で「条件」やそれを検索する「条件範囲」を指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
参考問題／SUMIFを利用した複数条件／andの利用方法
【参考問題】
SUMIF関数を利用した複数条件に一致する場合に合計する問題をリンクします。
SUMIF関数は1つの条件に対して一致する場合に数値を合計する関数です。
そのため、複数の条件に一致した場合に合計できるようにするには工夫が必要になりますので、
上記の練習問題１～２と合わせて、参考問題も解いてみましょう。
[範囲] = 条件を検索する対象のセル範囲を指定します。
[検索条件] = 条件となる文字や数値を指定します。
[合計範囲] = 合計したい値が入力されているセル範囲を指定します。
【参考問題 まとめ】
Excelのセルの中で、条件にあったセルの数値のみを合計するにはSUMIF関数を利用することを覚えましょう。
条件を指定して合計を計算できるので、応用範囲の広い関数となります。
今回の【参考問題】のように、複数の条件であっても、先に条件を満たすデータを判定しおくと、SUMIF関数が利用できるようになります。
活用例として参考になさってください。
=SUMIF(範囲, 検索条件, 合計範囲)
[範囲] = 条件を検索する対象のセル範囲を指定します。
[検索条件] = 条件となる文字や数値を指定します。
[合計範囲] = 合計したい値が入力されているセル範囲を指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
