練習問題1/文字列の複数条件を満たすセルをカウントする/「文字列含む」「~以外」

【練習問題1 問題】

下表は、4月上旬の売上表です。
売り上げた日付、商品、価格、単価および金額を記載して管理しています。
1.価格が2000円以外のボールペンを売り上げた回数を求めてください。
2.12色以外の色鉛筆を売り上げた回数を求めてください。

sponsored link

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
データの個数をカウントする関数がわからない場合は・・・Excel 個数 数える 条件 関数
複数の条件の書き方が分からない場合は・・・Excel 個数 数える 条件 指定 複数 関数
~以外の条件の書き方が分からない場合は・・・Excel 個数 数える 条件 以外 複数 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

 

【練習問題1 解答】

解答は以下の通りです。

※上の図の解答はわかりやすいよう、価格が2000円以外のボールペンを売り上げた回数符の解答を青文字、12色以外の色鉛筆を売り上げた回数の解答を赤文字で記入しています。

【セル】【関数式】    【 値 】
E20 :=COUNTIFS(D4:D17,”<>2000″,C4:C17,”*ボールペン*”)⇒ 2  

【セル】【関数式】    【 値 】
E22 :=COUNTIFS(C4:C17,”<>*12色*”,C4:C17,”*色鉛筆*”)⇒ 3  

▶解答のExcelデータをダウンロード

【練習問題1 解説】

各回数を求める流れは以下のようになります。
複数の条件を全て満たすセルを数えるにはCOUNTIFS関数を利用します。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)
[範囲1、2、・・・] = セルの数を数える範囲を指定します。
[検索条件1、2、・・・] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。
※最大127組の範囲/検索条件のペアを指定できます。


(a) 価格が2000円以外のボールペンを売り上げた回数をカウントする。

青枠のように「名前入りボールペン」と「ボールペン印鑑付」の計2回が条件を満たすのでカウントされます。なお、「3色ボールペン」は価格が2,000円なので、条件を満たしておらず、カウントされません。
=COUNTIFS(D4:D17,”<>2000″,C4:C17,”*ボールペン*”)

(b) 12色以外の色鉛筆を売り上げた回数をカウントする。

赤枠のように「36色・色鉛筆」が1回および「24色・色鉛筆」が2回の計3回が条件を満たすのでカウントされます。なお、「12色・色鉛筆」は12色なので、条件を満たしておらず、カウントされません。
=COUNTIFS(C4:C17,”<>*12色*”,C4:C17,”*色鉛筆*”)

【参考】特定の文字を含むセルをカウントしたいときは任意の文字を表す「*」を利用します。
例)「ボールペン」を含むセルを検索したい時は?

文字列の前方に「ボールペン」を含む文字の検索
例)ボールペン印鑑付など
=COUNTIF(範囲,”ボールペン*“)
文字列の中央に「ボールペン」を含む文字の検索
例)3色ボールペン+シャープペンなど
=COUNTIF(範囲,”*ボールペン*“)
文字列の後方に「ボールペン」を含む文字の検索
例)3色ボールペン、名前入りボールペン、など
=COUNTIF(範囲,”*ボールペン“)

 

【参考】~以外を条件に指定する場合には不等号の記号を利用します。
条件に記載できる等号および不等号は下表にてご確認ください。

記号 意味
< 指定した値よりも小さい(未満)
<= 指定した値よりも小さいor等しい(以下)
> 指定した値よりも大きい(超える)
>= 指定した値よりも大きいor同じ(以上)
<> 指定した値と等しくない(以外)

【練習問題1 まとめ】

Excelのセルの中で、複数の条件を全て満たすデータのあるセルの個数を数えるにはCOUNTIFS関数を利用することを覚えましょう。
条件を指定する方法はいくつかありますので、使いやすい指定方法から少しずつ覚えて活用していきましょう。
また1つの条件を満たすセルの個数を数えるにはCOUNTIF関数を利用します。違いを確認しましょう。
条件に「~以外」を指定できることを確認しましょう。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)
[範囲1、2、・・・] = セルの数を数える範囲を指定します。
[検索条件1、2、・・・] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。
※最大127組の範囲/検索条件のペアを指定できます。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
複数条件にあったセルの数を数える/COUNTIFS関数について解説します。

 

練習問題2/複数の数値条件に合うセルをカウントする/~以上、~以下

【練習問題2 問題】

下表は、4月上旬の売上表です。
売り上げた日付、商品、価格、単価および金額を記載して管理しています。
1.価格が3000円以上5000円以下のボールペンを売り上げた回数を求めてください。
2.金額が100,000円以上150,000円以下のボールペンを売り上げた回数を求めてください。

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
データの個数をカウントする関数がわからない場合は・・・Excel 個数 数える 条件 関数
複数の条件の書き方が分からない場合は・・・Excel 個数 数える 条件 指定 複数 関数
ある数値の以上、以下の条件の書き方が分からない場合は・・・Excel 個数 数える 条件 以上 以下 複数 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

 

【練習問題2 解答】

それでは解答を見ていきましょう。

※解答はわかりやすいよう、価格が3000円以上5000円以下のボールペンを売り上げた回数の解答を緑文字、金額が100,000円以上150,000円以下のボールペンを売り上げた回数の解答を青文字で記入しています。

【セル】【関数式】【 値 】 
F20 :=COUNTIFS(D4:D17,”>=3000″,D4:D17,”<=5000″,C4:C17,”*ボールペン*”)⇒ 2   

【セル】【関数式】【 値 】 
F22 :=COUNTIFS(F4:F17,”>=100000″,F4:F17,”<=150000″,C4:C17,”*ボールペン*”)⇒ 2   

▶解答のExcelデータをダウンロード

【練習問題2 解説】

各回数を求める流れは以下のようになります。
複数の条件を全て満たすセルを数えるにはCOUNTIFS関数を利用します。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)
[範囲1、2、・・・] = セルの数を数える範囲を指定します。
[検索条件1、2、・・・] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。
※最大127組の範囲/検索条件のペアを指定できます。


(a) 価格が3000円以上5000円以下のボールペンを売り上げた回数をカウントする。

緑枠のように6行目の商品「名前入りボールペン」かつ価格「3000」と17行目の商品「ボールペン印鑑付」かつ価格「5000」の計2回が条件を満たすのでカウントされます。なお、5行目と16行目の「3色ボールペン」は価格が2,000円なので、条件を満たしておらず、カウントされません。
=COUNTIFS(D4:D17,”>=3000″,D4:D17,”<=5000″,C4:C17,”*ボールペン*”)

(b) 金額が100,000円以上150,000円以下のボールペンを売り上げた回数をカウントする。

青枠のように5行目の「3色ボールペン」かつ金額「100000」と17行目の「ボールペン印鑑付」かつ金額「125000」の計2回が条件を満たすのでカウントされます。なお、「3色ボールペン」について、6行目の「名前入りボールペン」は金額が15000円であり、16行目の「3色ボールペン」は金額が60000円なので、条件を満たしておらず、カウントされません。
=COUNTIFS(F4:F17,”>=100000″,F4:F17,”<=150000″,C4:C17,”*ボールペン*”)

【参考】特定の文字を含むセルをカウントしたいときは任意の文字を表す「*」を利用します。
例)「ボールペン」を含むセルを検索したい時は?

文字列の前方に「ボールペン」を含む文字の検索
例)ボールペン印鑑付など
=COUNTIF(範囲,”ボールペン*“)
文字列の中央に「ボールペン」を含む文字の検索
例)3色ボールペン+シャープペンなど
=COUNTIF(範囲,”*ボールペン*“)
文字列の後方に「ボールペン」を含む文字の検索
例)3色ボールペン、名前入りボールペン、など
=COUNTIF(範囲,”*ボールペン“)

 

【参考】ある数値の以上、以下を条件に指定する場合には不等号の記号を利用します。
条件に記載できる等号および不等号は下表にてご確認ください。

記号 意味
< 指定した値よりも小さい(未満)
<= 指定した値よりも小さいor等しい(以下)
> 指定した値よりも大きい(超える)
>= 指定した値よりも大きいor同じ(以上)
<> 指定した値と等しくない(以外)

【練習問題2 まとめ】

Excelのセルの中で、複数の条件を全て満たすデータのあるセルの個数を数えるにはCOUNTIFS関数を利用することを覚えましょう。
条件を指定する方法はいくつかありますので、使いやすい指定方法から少しずつ覚えて活用していきましょう。
また1つの条件を満たすセルの個数を数えるにはCOUNTIF関数を利用します。違いを確認しましょう。
条件にある数値の以上、以下を複数指定することができるのを確認しましょう。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)
[範囲1、2、・・・] = セルの数を数える範囲を指定します。
[検索条件1、2、・・・] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。
※最大127組の範囲/検索条件のペアを指定できます。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
複数条件にあったセルの数を数える/COUNTIFS関数について解説します。

 

練習問題3/日付範囲指定されたセルをカウントする

【練習問題3 問題】

下表は、4月上旬の売上表です。
売り上げた日付、商品、価格、単価および金額を記載して管理しています。
1.日付が4月1日~4月5日の間にシャープペンシルを売り上げた回数を求めてください。
2.日付が4月6日~4月10日の間にボールペンを売り上げた回数を求めてください。

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
データの個数をカウントする関数がわからない場合は・・・Excel 個数 数える 条件 関数
複数の条件の書き方が分からない場合は・・・Excel 個数 数える 条件 指定 複数 関数
日付の範囲指定の条件の書き方が分からない場合は・・・Excel 個数 数える 条件 日付 複数 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

 

【練習問題3 解答】

それでは解答を見ていきましょう。

※上の図の解答はわかりやすいよう、日付が4月1日~4月5日の間にシャープペンシルを売り上げた回数の解答を青文字、日付が4月6日~4月10日の間にボールペンを売り上げた回数の解答を赤文字で記入しています。

【セル】【関数式】【 値 】
F20 :=COUNTIFS(B4:B17,”>=4/1″,B4:B17,”<=4/5″,C4:C17,”*シャープペンシル*”)⇒   1  

【セル】【関数式】【 値 】
F22 :=COUNTIFS(B4:B17,”>=4/6″,B4:B17,”<=4/10″,C4:C17,”*ボールペン*”)⇒   2  

▶解答のExcelデータをダウンロード

【練習問題3 解説】

各回数を求める流れは以下のようになります。
複数の条件を全て満たすセルを数えるにはCOUNTIFS関数を利用します。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)
[範囲1、2、・・・] = セルの数を数える範囲を指定します。
[検索条件1、2、・・・] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。
※最大127組の範囲/検索条件のペアを指定できます。


(a) 日付が4月1日~4月5日の間にシャープペンシルを売り上げた回数をカウントする。
(b) 日付が4月6日~4月10日の間にボールペンを売り上げた回数をカウントする。

青枠のように7行目「シャープペンシル」が条件を満たすのでカウントされます。なお、15行目の「シャープペンシル」は日付が4月8日なので、条件を満たしておらず、カウントされません。
=COUNTIFS(B4:B17,”>=4/1″,B4:B17,”<=4/5″,C4:C17,”*シャープペンシル*”)

赤枠のように16行目の「3色ボールペン」および17行目の「ボールペン印鑑付」の計2回が条件を満たすのでカウントされます。なお、5行目「3色ボールペン」および6行目「名前入りボールペン」は日付がいずれも4月2日なので、条件を満たしておらず、カウントされません。
=COUNTIFS(B4:B17,”>=4/6″,B4:B17,”<=4/10″,C4:C17,”*ボールペン*”)

【参考】特定の文字を含むセルをカウントしたいときは任意の文字を表す「*」を利用します。
例)「ボールペン」を含むセルを検索したい時は?

文字列の前方に「ボールペン」を含む文字の検索
例)ボールペン印鑑付など
=COUNTIF(範囲,”ボールペン*“)
文字列の中央に「ボールペン」を含む文字の検索
例)3色ボールペン+シャープペンなど
=COUNTIF(範囲,”*ボールペン*“)
文字列の後方に「ボールペン」を含む文字の検索
例)3色ボールペン、名前入りボールペン、など
=COUNTIF(範囲,”*ボールペン“)

 
【参考】条件に日付を記載する場合について

日付が「4月1日から4月5日まで」の場合の、売り上げ回数を数える。

(1)[範囲] : 日付の入力されているセル範囲[B4:B17]を指定します。
(2)[検索対象] :検索条件に『日付』を指定したい場合、「4/5」や「4月5日」など、「/」(スラッシュ)や「月日」を利用して表記することで、エクセルが自動で認識する機能があります。
4月1日から4月5日までの売上回数を求めるには、次のように条件を複数指定します。 → 「 ”>=4/1″ 」「 ”<=4/5″ 」
=COUNTIFS(B4:B17,”>=4/1″,B4:B17,”<=4/5")
また、4月5日のみの売上回数を求めるには、次のように条件指定します。 → 「 ”4/5″ 」 ※等号と不等号は書きません。
=COUNTIF(B4:B17,”4/5”)

【日付指定時の「年」の判定】
「4/5」「4月5日」といった形式で、「年」を省いて日付指定した場合、自動的に最新年(その年が2017年なら、2017年4月5日)として認識されます。過去の「年」を指定したい場合は、
「”2015/4/5″」「”2015年4月5日”」というように、明確に「年」を記入しましょう。

【練習問題3 まとめ】

Excelのセルの中で、複数の条件を全て満たすデータのあるセルの個数を数えるにはCOUNTIFS関数を利用することを覚えましょう。
条件を指定する方法はいくつかありますので、使いやすい指定方法から少しずつ覚えて活用していきましょう。
また1つの条件を満たすセルの個数を数えるにはCOUNTIF関数を利用します。違いを確認しましょう。
条件で日付の指定する方法を確認しましょう。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)
[範囲1、2、・・・] = セルの数を数える範囲を指定します。
[検索条件1、2、・・・] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。
※最大127組の範囲/検索条件のペアを指定できます。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
複数条件にあったセルの数を数える/COUNTIFS関数について解説します。