パソコンスキルの教科書

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

解説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

他のエクセル関数を解説

エクセル関数COUNTIFを動画で勉強!事例で実務の使い方を学ぶ
この記事ではエクセル関数のCOUNTIFを実務で使えるレベルになるために必要なことを学んでいきます。 具体的には以下のことを動画解説も含めて紹介していきます。 ・COUNTIFの基本的な使い方 ・COUNTIF、OFFS […]