データをカウントする/複数条件、空白以外、重複、文字データなど

50058010501-2 基礎コース

データの数を数える

Excelのデータのあるセルの個数を数えるには以下の関数を利用します。

=COUNT(範囲)

[範囲] = セルの数を数える範囲を指定します。

関数の説明 数値が入力されているセルをカウントする関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=COUNTA(範囲)

[範囲] = セルの数を数える範囲を指定します。

関数の説明 数値、または、文字列が入力されているセルの個数を数える関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)入力済みセルの個数をカウントする/COUNT関数、COUNTA関数

下の表は、エクセル研究会の参加状況のリストで、招待した大学、および、参加連絡のあった大学の参加人数が入力されている一覧です。下の表にある参加人数が入力されているセルの個数および招待大学のセルの個数をカウントして、実際に①参加した大学の数と②招待した大学の数を求めてみましょう。

50058010201

① 参加人数が入力されているセル範囲[C5:C9]を指定

参加人数が入力されているセルの個数を求めています。
=COUNT(C5:C9)

② 招待した大学が入力されているセル範囲[B5:B9]を指定

大学名が入力されているセルの個数を求めています。
=COUNTA(B5:B9)

POINT!

数える対象が「数値」か、「文字列」なのかで使用する関数が違っていることに注意してください。

COUNT関数→「数値」が入力されたセルのみをカウント
COUNTA関数→「数値あるいは文字」が入力されたセルをカウント

※文字が入力されたセルのみをカウントし、数字のみのセルを除外したい場合、以下のように
表記することで文字が入力されたセルのみを数えることができます。

=COUNTA(範囲A)-COUNT(範囲A)

また次のような場合は誤ったカウント結果が表示されますので注意が必要です。

誤った入力と結果の例)

ph1

① COUNT関数は、セルに「0」値が入っている場合もカウントしてしまう

COUNT関数は、参加数が「0」でも値が入っていればカウントされるため、本来「3」校である大学の「参加数」が「4」校となってしまいます。この場合、参加数が「0」人の時は入力しないか、次に説明するCOUNTIF関数を利用してカウントするようにしましょう。

② COUNTA関数は、空白文字(スペース)が入力されている場合もカウントする

見た目が何も入力されていないようでもスペースや白文字が入っていればカウントされるため、注意が必要です。

 

データに条件を指定してカウントする

Excelのセルの中で、特定の条件を満たすデータのあるセルの個数を数えるには以下の関数を利用します。

=COUNTIF(範囲, 検索条件)

[範囲] = セルの数を数える範囲を指定します。
[検索条件] = 数える対象となる条件を「”」(ダブルクォーテ-ション)で指定します。

関数の説明 カウントするデータに条件を指定し、これを満たすセルの個数を数える関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013 ・ Excel2016

 

使い方例1)条件に合うセルを数える(文字列,数値以上・以下)/COUNTIF関数

下記の表は、とあるネットショップの日付ごとの各注文の売上一覧表です。売上のあった「日付」「商品」「価格(単価)」「個数」「売上金額」「代引払い」の情報が記載されています。以下の売上一覧表から、①「代引払い」指定の数と、②5個以上売り上げた回数を、それぞれ条件を指定して求めてみましょう。

50058010501-2

① 代引払いに「○」が入力されているセルの個数を数える。

(1)[範囲] : 代引き払いの入力されているセル範囲[G5:G18]を指定します。
(2)[検索対象] : 「○」を「”」で囲んで指定します。 →「”○”」
=COUNTIF(G5:G18,”○”)

② 5個以上売り上げた回数を数える。

(1)[範囲] : 個数の入力されているセル範囲[E5:E18]を指定します。
(2)[検索対象] : 5個以上を表すため不等号を使用して指定します。 →「”>=5″」
=COUNTIF(E5:E18,”>=5″)

 

使い方例2)条件に合うセルを数える(日付、~を含む)/COUNTIF関数

同じ表を使って、今度は①「~バター」を含む全ての売上回数と、②4月2日の売上回数を求めてみましょう。

countif5-large

① 商品名に「バター」の名前を含むセルの個数を数える。

(1)[範囲] : 商品名の入力されているセル範囲[C5:C18]を指定します。
(2)[検索対象] : 「なにかの文字列 + バター」といった特定の文字列(ここでは「バター」)を含むセルを検索する場合は、「*」(アスタリスク)を利用して次のように条件指定します。 →「”*バター”」
=COUNTIF(C5:C18,”*バター”)
特定の文字を含むセルをカウントしたいときは任意の文字を表す「*」を利用します。

例)「バター」を含むセルを検索したい時は?

文字列の前方に「バター」を含む文字の検索
例)バタージャム、バターサンド、など
=COUNTIF(範囲,”バター*“)
文字列の中央に「バター」を含む文字の検索
例)十勝バタージャム、北海バターサンド、など
=COUNTIF(範囲,”*バター*“)
文字列の後方に「バター」を含む文字の検索
例)十勝バター、北海バター、など
=COUNTIF(範囲,”*バター“)

② 日付が「4月2日」の場合の、売り上げ回数を数える。

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

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

使い方例3)条件に合うセルを数える(空白、空白以外、~以外)/COUNTIF関数

同じ表を使って、今度は①「代引払い」以外の全ての売上回数と、②ジャム以外を求めてみましょう。

① 「代引払い」の項目が空白のセルを数える。

(1)[範囲] : 「代引払い」の入力されているセル範囲[G5:G18]を指定します。
(2)[検索対象] :空白セルを検索したい場合、検索条件を記入する””(ダブルクォーテーション)の中に文字列を何も入れず次のように条件指定します。 → 「 ”” 」
=COUNTIF(G5:G18,””)

【空白以外のセルの指定】
空白以外のセルをカウントしたい場合、「~以外」を表す演算子「<>」の後に空白を表す「””」条件を追記し、以下のように記述します。どちらでも同じように空白セル以外を指定可能です。
例1)=COUNTIF( G5:G18 , “<>” & “” )
例2)=COUNTIF( G5:G18 , “<>” )

② 商品が「ジャム」以外のセルの個数をカウントする。

(1)[範囲] : 「商品」の入力されているセル範囲[C5:C18]を指定します。
(2)[検索対象] :「~以外」の条件を指定したい場合、演算子の「<>」を利用します。「ジャム以外」を検索するには、次のように条件指定します。 → 「 ”<>ジャム” 」
=COUNTIF(C5:C18,”<>ジャム”)

使い方例4)条件に合うセルを数える(重複チェック)/COUNTIF関数

エクセルを使用して表を作成する業務の中では、同じデータを誤って入力してしまう事が少なくはありません。今度は下記の社員表にて、それぞれの氏名が重複していないかを「COUNTIF関数」を使用して、確認してみましょう。①社員名がそれぞれいくつあるかを数え、もし、他のセルと氏名が重複していなければ「1」、重複している場合は、重複チェックに「1」より大きい数字が表示されます。

50058010401-1

① 氏名が重複して入力されていないか、それぞれの氏名の個数を数える。

(1)[範囲] : 氏名の入力されているセル範囲[B5:B12]を指定します。
(2)[検索対象] : それぞれの社員名が入力されたセルを指定します。検索の条件に特定のセルの値を指定したい場合、この検索対象項目には、条件指定したいセルの位置を記入します。 →5行目:「B5」、6行目:「B6」、7行目:「B7」・・・(※各行毎に検索対象を変更して、数式を入力してください)
5行目の場合)=COUNTIF(B5:B12,B5) 、6行目の場合)=COUNTIF(B5:B12,B6)

 

POINT!

検索条件を指定する等号(”=”以外)や、不等号については、下記の表を参考にしてください。
また、文字列を検索条件として使用する際は、ワイルドカードが便利です。

【等号・不等号について】

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

【ワイルドカード(あいまい指定)について】

記号 意味
* 任意の複数文字を表す
? 任意の1文字を表す

この他にも、空白のセルを指定するには「””」(ダブルクォーテーション2回)と指定するなど、これらの記号を活用すれば、例えば上の表においても様々な検索方法が可能となり、関数利用の幅が広がります。

データに複数条件指定してカウントする

Excelのセルの中で、複数の条件を指定し、それを満たすセルの個数をカウントする時は、以下の関数を利用します。

=COUNTIFS(範囲1, 検索条件1, [範囲2, 検索条件2]・・・)

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

関数の説明 指定した2つ以上の検索条件を満たすセルの個数を数える関数
EXCELバージョン Excel2007 ・ Excel2010 ・ Excel2013 ・ Excel2016

 

使い方例)複数条件にあったセルの数を数える/COUNTIFS関数

次は下記売上一覧表にて、複数条件に該当するセルをカウントしてみましょう。例えば「①全てのバター商品で、かつ、②売上個数が4個以下だった場合の回数」など、複数の条件がある場合は、COUNTIFS関数が便利です。

countifs-or01

 

① 1つ目の条件「全てのバター商品」を指定する。

(1)[範囲1] : 商品名の入力されているセル範囲[C5:C18]を指定します。
(2)[検索対象1] : 「~バター」のように「バター」を含む文字を検索するには、「*」(アスタリスク)を使用し、次のように指定します。 →「”*バター”」
⇒特定文字を「含む」検索条件指定方法はこちらを参照してください。

② 2つ目の条件「売上個数が4個以下」を指定する。

(1)[範囲2] : 個数の入力されているセル範囲[E5:E18]を指定します。
(2)[検索対象2] : 4個以下を表すため不等号を使用して指定します。 →「”<=4″」
⇒条件指定のための演算子についてはこちらを参照してください。

=COUNTIFS(C5:C18,”*バター”,E5:E18,”<=4″)

 

POINT!

COUNTIFS関数は、いくつもの条件を設定することが出来ますが、「全ての条件に一致するデータ」が
対象となりますので、注意が必要です。
その分、上手に使用すれば、より具体的で細かなデータの絞り込みが可能な関数になります。

Rate article