この記事ではエクセル関数のCOUNTIFsを実務で使えるレベルになるために必要なことを学んでいきます。
具体的には以下のことを動画解説も含めて紹介していきます。
1. 基本編｜2条件でカウントする
2. 応用編｜日付の条件を追加する
3. 発展編｜データが増えても自動調整する
上記の内容を動画だけでなく解説も合わせて紹介していきます。
目次
エクセル関数COUNTIFsを動画で学ぶ
動画2. 応用編｜日付の条件を追加する
動画3. 発展編｜データが増えても自動調整する
動画1. 基本編｜2条件でカウントする
動画2. 応用編｜日付の条件を追加する
動画3. 発展編｜データが増えても自動調整する
動画はうごきを含めて理解できるので、イメージがつかみやすいです。
そのため、動画をご覧になることをおすすめします。
COUNTIFsとは範囲内で複数条件に合致するデータの件数を調べる関数
COUNTIFsは範囲内のセルに、条件に合うものがいくつあるのか件数を調べる関数です。
たとえば上記の画像のように、「担当者」と「取引先」の2つの条件を満たすデータ数をカウントするときにCOUNTIFsを使います。
COUNTIFsには以下の要素があります。
=COUNTIFS(検索条件範囲1 , 検索条件1, 検索条件範囲2 , 検索条件2, 検索条件範囲3 , 検索条件3, …)
検索条件範囲1：検索したい範囲(1つ目)
検索条件1：調べたい条件(1つ目)
検索条件範囲2：検索したい範囲(2つ目)
検索条件2：調べたい条件(2つ目)
検索条件範囲3：検索したい範囲(3つ目)
検索条件3：調べたい条件(3つ目)
4つ以上の条件を設定することも可能です
以下で事例を通じて解説していきます。
解説1. 基本編｜2条件でカウントする
2つの条件を満たすデータ数をCOUNTIFsでカウントしていきます。
上記のように「担当者」と「取引先」の2つに合致するデータをカウントし、表に出力します。
検索条件範囲1：$B$2:$B$11
検索条件1：$F6
検索条件範囲2：$C$2:$C$11
検索条件2：G$5
上記の条件を入れることで、2つの条件を満たすデータのみをカウントするエクセル関数を作成できます。
エクセル関数を他のセルに適用する場合はマウスでドラッグする
作成した関数を他のセルに適用させる場合は、上記の画像のようにマウスでクリックしたままドラッグします。
そうすると、セル一つ一つに関数を記入する必要がなくなります。
注意｜絶対参照、複合参照を使う
絶対参照や複合参照とは、参照範囲を固定する参照方法を意味します。
実は絶対参照や複合参照を使わずに、エクセル関数をドラッグして適用すると以下のように範囲がずれることがあります。
これを防止するために、絶対参照や複合参照を使用します。
逆に絶対参照にすると、以下のように範囲がずれることはありません。
絶対参照にするには、絶対参照したいセルを選択し、[F4]を押します。
そうすると、「$」マークが出現し、絶対参照に変更することができます。
$B$2:$B$11：絶対参照
$F6：複合参照（F列を固定）
$C$2:$C$11：絶対参照
G$5：複合参照（5行目を固定）
解説2. 応用編｜日付の条件を追加する
解説1の2つの条件（担当者、取引先）に加えて、上記のように「開始：2016/10/5」～「終了：2016/10/9」の期間に含まれるデータのみカウントする条件を追加します。
そうすると、以下のようなエクセル関数を作成する必要があります。
検索条件範囲1：$B$2:$B$11
検索条件1：$G8
検索条件範囲2：$C$2:$C$11
検索条件2：H$7
検索条件範囲3：$D$2:$D$11
検索条件3：“>=”&$H$4
検索条件範囲4：$D$2:$D$11
検索条件4：“<=”&$H$5)
※検索条件範囲1、検索条件1、検索条件範囲2、検索条件2は「解説1で説明しているため省略
上記のように記述することで、日付でデータをカウントするエクセル関数を作成できます。
日付の条件をエクセル関数に組み込む
「日付1」を含めた日より大きい日付に合致する場合
●データ範囲,“<=”&日付2
「日付2」を含めた日より小さい日付に合致する場合
日付の条件を組み込む場合は、上記のように記載します。
解説3. 発展編｜データが増えても自動調整する
解説2で紹介したエクセル関数にOFFSETとCOUNTAを使ってデータに応じて範囲を自動調整させるようにしていきます。
検索条件範囲1：OFFSET($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1 )
検索条件1：$G8
検索条件範囲2：OFFSET($C$2 , 0 , 0 , COUNTA($C:$C)-1 , 1 )
検索条件2：H$7
検索条件範囲3：OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 )
検索条件3：”>=”&$H$4
検索条件範囲4：OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 )
検索条件4：”<=”&$H$5
上記のようにOFFSETとCOUNTAをつかうことで、データ数を自動で読み込んで範囲を調整してくれるようになります。
ここでは「検索条件範囲1：OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)」を代表例にして説明していきます。
「OFFSET($B$2,0,0,COUNTA($B:$B)-1」を解説
まずOFFSETについて解説します。
OFFSET=(参照 , 行数 , 列数 , [高さ] , [幅] )
このOFFSETは、「参照」を起点にして、下に「行数」、右に「列数」ずれたセルから「[高さ]」と「[幅]」を指定した範囲を意味します。
たとえば、「=OFFSET(B2 , 1 , 2 , 3 , 4 )」で指定された範囲を図解すると以下です。
補足：[高さ]と[幅] は省略可能のため、[] がついています。
この記事で紹介しているOFFSETの要素は以下です。
参照：$B$2
行数：0
列数：0
[高さ]：COUNTA($B:$B)-1
[幅]：1
ここまでをまとめると、「OFFSET($B$2,0,0,COUNTA($B:$B)-1」とはセルB2から、高さ「COUNTA($B:$B)-1」と幅1で指定された範囲となります。
「COUNTA($B:$B)-1」はB列のデータ数から1を引いた数
このとき、「COUNTA($B:$B)-1」はB列に含まれるデータ数から1を引いたものを指します。
たとえばCOUNTA($B:$B)はB列に含まれるデータが10であれば「10」、15であれば「15」になります。
なお「COUNTA($B:$B)-1」の「-1」はセルB1の担当者を除外するためのものです。
したがって、OFFSET=($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1)は以下のようになります。
B2セル～B列一番下のセルまでの範囲
例1. B列一番下のセルが10の場合、B2～B10
例2. B列一番下のセルが15の場合、B2～B15
これで、データが増えても自動で範囲を調整してくれるCOUNTIFSを作成することができました。
検索条件範囲1：OFFSET($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1 )
検索条件1：$G8
検索条件範囲2：OFFSET($C$2 , 0 , 0 , COUNTA($C:$C)-1 , 1 )
検索条件2：H$7
検索条件範囲3：OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 )
検索条件3：”>=”&$H$4
検索条件範囲4：OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 )
検索条件4：”<=”&$H$5
