パソコンスキルの教科書

パソコンスキルの教科書

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

エクセル関数COUNTIFを動画で勉強!事例で実務の使い方を学ぶ

f:id:gene320:20190604001434j:plain

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

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

・COUNTIFの基本的な使い方
・COUNTIF、OFFSET、COUNTAを使って集計範囲を自動調整する関数を作成

エクセル関数COUNTIF|事例を使って実務での使い方を学ぶ

この記事で学べるのは以下です。

f:id:gene320:20190604000927j:plain

この記事の内容は、以下の動画で詳しく解説しています。

動画1|COUNTIFの基本的な使い方
動画2|OFFSET、COUNTAを使ってCOUNTIFの範囲を自動調整

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

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

動画1|COUNTIFの基本的な使い方

www.youtube.com

動画2|OFFSET、COUNTAを使ってCOUNTIFの範囲を自動調整

youtu.be

解説1|COUNTIFの基本的な使い方

f:id:gene320:20190603220744j:plain

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

COUNTIFの要素
=COUNTIF(範囲 , 検索条件)

範囲:検索したい範囲
検索条件:調べたい条件

COUNTIFは範囲内のセルで条件に合致するものをカウントする

f:id:gene320:20190603214853j:plain

=COUNTIF($B$2:$B$11,F4)
範囲:セルB2~B11($がつくと、絶対参照になる)
検索条件:セルF4(ここでは「A」を検索する)

つまり、セルB2からセルB11までに含まれる値で、「A」(セルF4の値)の個数をカウントします。

結果として、以下の値が返ります。

3

f:id:gene320:20190603215045j:plain

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

f:id:gene320:20190603222712j:plain

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

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

注意|絶対参照を使わないと範囲がずれることがある

マウスでドラッグするとき、絶対参照になっていることを確認するのが大切です。

絶対参照とは、参照範囲を固定することを意味します。

もし絶対参照にしないままで、エクセル関数をドラッグして適用すると以下のように範囲がずれることがあるからです。

f:id:gene320:20190603223705j:plain

この原因は、ドラッグしたセルと同じだけ相対的に移動するからです。

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

f:id:gene320:20190603223425j:plain

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

そうすると、以下のように「$」マークが出現し、絶対参照をしてくれます。

f:id:gene320:20190603224453j:plain

データが増えると範囲を再設定する必要がある

ここまでCOUNTIFで範囲内に条件を満たす件数がどれくらいあるのかを検証してきました。

しかしデータが増えると、範囲を修正する必要があります。

f:id:gene320:20190603225755j:plain

そこで範囲を自動調整してくれるようにエクセル関数を改編する方法を以下で紹介していきます。

解説2|COUNTIF、OFFSET、COUNTAを使って集計範囲を自動調整する関数を作成

youtu.be

上記の動画で解説していますので、動画をご覧になることをおすすめします。

範囲を自動調節するCOUNTIFの作り方

f:id:gene320:20190603230847j:plain

=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個増えても自動で範囲を変えてくれます。

実際、以下のようにデータが増えても自動で件数が変更されます。

f:id:gene320:20190603231824j:plain

COUNTIFの範囲「OFFSET($B$2,0,0,COUNTA($B:$B)-1」を解説

f:id:gene320:20190603232131j:plain

データ数に応じて、範囲を自動調整するときの肝となる「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:20190603235630j:plain

したがって、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が完成

f:id:gene320:20190604011817j:plain

以下のように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」を検索する)