この記事ではエクセル関数のCOUNTIFを実務で使えるレベルになるために必要なことを学んでいきます。
具体的には以下のことを動画解説も含めて紹介していきます。
・COUNTIFの基本的な使い方
・COUNTIF、OFFSET、COUNTAを使って集計範囲を自動調整する関数を作成
エクセル関数COUNTIF｜事例を使って実務での使い方を学ぶ
この記事で学べるのは以下です。
この記事の内容は、以下の動画で詳しく解説しています。
動画1｜COUNTIFの基本的な使い方
動画2｜OFFSET、COUNTAを使ってCOUNTIFの範囲を自動調整
動画はうごきを含めて理解できるので、イメージがつかみやすいです。
そのため、動画をご覧になることをおすすめします。
動画1｜COUNTIFの基本的な使い方
動画2｜OFFSET、COUNTAを使ってCOUNTIFの範囲を自動調整
解説1｜COUNTIFの基本的な使い方
COUNTIFは範囲内のセルに、条件に合うものがいくつあるのか件数を調べる関数です。
COUNTIFの要素
=COUNTIF(範囲 , 検索条件)
範囲：検索したい範囲
検索条件：調べたい条件
COUNTIFは範囲内のセルで条件に合致するものをカウントする
=COUNTIF($B$2:$B$11,F4)
範囲：セルB2～B11($がつくと、絶対参照になる)
検索条件：セルF4（ここでは「A」を検索する）
つまり、セルB2からセルB11までに含まれる値で、「A」（セルF4の値）の個数をカウントします。
結果として、以下の値が返ります。
3
エクセル関数を他のセルに適用する場合はマウスでドラッグする
作成した関数を他のセルに適用させる場合は、上記の画像のようにマウスでクリックしたままドラッグします。
そうすると、セル一つ一つに関数を記入する必要がなくなります。
注意｜絶対参照を使わないと範囲がずれることがある
マウスでドラッグするとき、絶対参照になっていることを確認するのが大切です。
絶対参照とは、参照範囲を固定することを意味します。
もし絶対参照にしないままで、エクセル関数をドラッグして適用すると以下のように範囲がずれることがあるからです。
この原因は、ドラッグしたセルと同じだけ相対的に移動するからです。
逆に絶対参照にすると、以下のように範囲がずれることはありません。
絶対参照にするには、絶対参照したいセルを選択し、[F4]を押します。
そうすると、以下のように「$」マークが出現し、絶対参照をしてくれます。
データが増えると範囲を再設定する必要がある
ここまでCOUNTIFで範囲内に条件を満たす件数がどれくらいあるのかを検証してきました。
しかしデータが増えると、範囲を修正する必要があります。
そこで範囲を自動調整してくれるようにエクセル関数を改編する方法を以下で紹介していきます。
解説2｜COUNTIF、OFFSET、COUNTAを使って集計範囲を自動調整する関数を作成
上記の動画で解説していますので、動画をご覧になることをおすすめします。
範囲を自動調節するCOUNTIFの作り方
=COUNTIF(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1),F4)
範囲：OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)
検索条件：セルF4（ここでは「A」を検索する）
上記のように、範囲に「OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)」を使うとB列のデータ数を自動で読み込んで範囲を調整してくれるようになります。
たとえば、B列のデータが100個増えても自動で範囲を変えてくれます。
実際、以下のようにデータが増えても自動で件数が変更されます。
COUNTIFの範囲「OFFSET($B$2,0,0,COUNTA($B:$B)-1」を解説
データ数に応じて、範囲を自動調整するときの肝となる「OFFSET($B$2,0,0,COUNTA($B:$B)-1」について詳しい解説をしていきます。
まずOFFSETについて解説します。
OFFSETの要素
OFFSET=(参照 , 行数 , 列数 , [高さ] , [幅] )
このOFFSETは、「参照」を起点にして、下に「行数」、右に「列数」ずれたセルから「[高さ]」と「[幅]」を指定した範囲を意味します。
たとえば、「=OFFSET(B2 , 1 , 2 , 3 , 4 )」で指定された範囲を図解すると以下です。
補足：[高さ]と[幅] は省略可能のため、[] がついています。
この記事で紹介しているOFFSETの要素は以下です。
一般｜OFFSET=(参照 , 行数 , 列数 , [高さ] , [幅] )
事例｜OFFSET=($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1)
参照：$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から、高さ「COUNTA($B:$B)-1」と幅1で指定された範囲
高さ「COUNTA($B:$B)-1」は、B列のデータ数が10のとき10、データ数が15のとき15になる
これで、データが増えても自動で範囲を調整してくれるCOUNTIFを作成することができました。
B列のデータが増えても自動で範囲調整してくれるCOUNTIFが完成
以下のようにCOUNTIF, OFFSET, COUNTAの関数を組み合わせることで、データの増減に自動対応して件数をカウントしてくれる関数を作成できます。
=COUNTIF(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1),F4)
範囲：OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)
検索条件：セルF4（ここでは「A」を検索する）
