パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

エクセル関数COUNTIFSを動画で勉強!事例で使い方を解説

Excel 関数 countifs

この記事ではエクセル関数のCOUNTIFsを実務で使えるレベルになるために必要なことを学んでいきます。

具体的には以下のことを動画解説も含めて紹介していきます。

●COUNTIFsを動画で学ぶ
1. 基本編|2条件でカウントする
2. 応用編|日付の条件を追加する
3. 発展編|データが増えても自動調整する

上記の内容を動画だけでなく解説も合わせて紹介していきます。

エクセル関数COUNTIFsを動画で学ぶ

動画1. 基本編|2条件でカウントする
動画2. 応用編|日付の条件を追加する
動画3. 発展編|データが増えても自動調整する

動画1. 基本編|2条件でカウントする


エクセル関数|COUNTIFSの解説(1/3)|使い方を学ぼう

動画2. 応用編|日付の条件を追加する


エクセル関数|COUNTIFSの解説(2/3)|日付の条件を追加する

動画3. 発展編|データが増えても自動調整する


エクセル関数|COUNTIFSの解説(3/3)|データ増減しても自動で範囲変更する

動画はうごきを含めて理解できるので、イメージがつかみやすいです。

そのため、動画をご覧になることをおすすめします。

COUNTIFsとは範囲内で複数条件に合致するデータの件数を調べる関数

COUNTIFsは範囲内のセルに、条件に合うものがいくつあるのか件数を調べる関数です。

たとえば上記の画像のように、「担当者」と「取引先」の2つの条件を満たすデータ数をカウントするときにCOUNTIFsを使います。

COUNTIFsには以下の要素があります。

●COUNTIFSの要素
=COUNTIFS(検索条件範囲1 , 検索条件1, 検索条件範囲2 , 検索条件2, 検索条件範囲3 , 検索条件3, ...)

検索条件範囲1:検索したい範囲(1つ目)
検索条件1:調べたい条件(1つ目)
検索条件範囲2:検索したい範囲(2つ目)
検索条件2:調べたい条件(2つ目)
検索条件範囲3:検索したい範囲(3つ目)
検索条件3:調べたい条件(3つ目)

4つ以上の条件を設定することも可能です

以下で事例を通じて解説していきます。

解説1. 基本編|2条件でカウントする


エクセル関数|COUNTIFSの解説(1/3)|使い方を学ぼう

2つの条件を満たすデータ数をCOUNTIFsでカウントしていきます。

f:id:gene320:20190609000631j:plain

上記のように「担当者」と「取引先」の2つに合致するデータをカウントし、表に出力します。

=COUNTIFS($B$2:$B$11,$F6,$C$2:$C$11,G$5)

検索条件範囲1$B$2:$B$11
検索条件1$F6
検索条件範囲2$C$2:$C$11
検索条件2G$5

上記の条件を入れることで、2つの条件を満たすデータのみをカウントするエクセル関数を作成できます。

エクセル関数を他のセルに適用する場合はマウスでドラッグする

作成した関数を他のセルに適用させる場合は、上記の画像のようにマウスでクリックしたままドラッグします。

そうすると、セル一つ一つに関数を記入する必要がなくなります。

注意|絶対参照、複合参照を使う

絶対参照や複合参照とは、参照範囲を固定する参照方法を意味します。

実は絶対参照や複合参照を使わずに、エクセル関数をドラッグして適用すると以下のように範囲がずれることがあります。

これを防止するために、絶対参照や複合参照を使用します。

逆に絶対参照にすると、以下のように範囲がずれることはありません。

絶対参照にするには、絶対参照したいセルを選択し、[F4]を押します。

f:id:gene320:20190609021310j:plain

そうすると、「$」マークが出現し、絶対参照に変更することができます。

=COUNTIFS($B$2:$B$11 , $F6 , $C$2:$C$11 , G$5)

$B$2:$B$11:絶対参照
$F6:複合参照(F列を固定)
$C$2:$C$11:絶対参照
G$5:複合参照(5行目を固定)

解説2. 応用編|日付の条件を追加する


エクセル関数|COUNTIFSの解説(2/3)|日付の条件を追加する

解説1の2つの条件(担当者、取引先)に加えて、上記のように「開始:2016/10/5」~「終了:2016/10/9」の期間に含まれるデータのみカウントする条件を追加します。

そうすると、以下のようなエクセル関数を作成する必要があります。

=COUNTIFS($B$2:$B$11 , $G8 , $C$2:$C$11 , H$7 , $D$2:$D$11 , ">="&$H$4 , $D$2:$D$11 , "<="&$H$5)

検索条件範囲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で説明しているため省略

上記のように記述することで、日付でデータをカウントするエクセル関数を作成できます。

日付の条件をエクセル関数に組み込む

f:id:gene320:20190609012502j:plain

●データ範囲, ">="&日付1
「日付1」を含めた日より大きい日付に合致する場合

●データ範囲,"<="&日付2
「日付2」を含めた日より小さい日付に合致する場合

日付の条件を組み込む場合は、上記のように記載します。

解説3. 発展編|データが増えても自動調整する


エクセル関数|COUNTIFSの解説(3/3)|データ増減しても自動で範囲変更する

解説2で紹介したエクセル関数にOFFSETとCOUNTAを使ってデータに応じて範囲を自動調整させるようにしていきます。

f:id:gene320:20190609013047j:plain

=COUNTIFS(OFFSET($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1 ), $G8 ,OFFSET($C$2 , 0 , 0 , COUNTA($C:$C)-1 , 1 ), H$7, OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 ) , ">="&$H$4 ,OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 ), "<="&$H$5 )

検索条件範囲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は、「参照」を起点にして、下に「行数」、右に「列数」ずれたセルから「[高さ]」と「[幅]」を指定した範囲を意味します。

たとえば、「=OFFSET(B2 , 1 , 2 , 3 , 4 )」で指定された範囲を図解すると以下です。

f:id:gene320:20190604011528j:plain

補足:[高さ]と[幅] は省略可能のため、[] がついています。

この記事で紹介している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の担当者を除外するためのものです。

f:id:gene320:20190609020440j:plain

したがって、OFFSET=($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1)は以下のようになります。

●OFFSET=($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1)とは
B2セル~B列一番下のセルまでの範囲
例1. B列一番下のセルが10の場合、B2~B10
例2. B列一番下のセルが15の場合、B2~B15

これで、データが増えても自動で範囲を調整してくれるCOUNTIFSを作成することができました。

=COUNTIFS(OFFSET($B$2 , 0 , 0 , COUNTA($B:$B)-1 , 1 ), $G8 ,OFFSET($C$2 , 0 , 0 , COUNTA($C:$C)-1 , 1 ), H$7, OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 ) , ">="&$H$4 ,OFFSET($D$2, 0 , 0 , COUNTA($D:$D)-1 , 1 ), "<="&$H$5 )

検索条件範囲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

他のエクセル関数を解説

www.fastclassinfo.com