COUNTIFS関数は、複数条件を満たすデータの件数を数えるものである。


C4セルに、「=COUNTIFS(E7:E18,C2,D7:D18,C3)」と入力すると「4」と表示された。
COUNTIFS関数の引数は、「=COUNTIFS(条件1を数える範囲,条件1,条件２を数える範囲,条件2)」と
指定する。設定できる条件の数は、127個となっている。

●クロス集計に件数を表示する。


集計表の左端と上端のデータが交差するセルC3に、COUNTIFS関数を入力する。
「=COUNTIFS($D$8:$D$19,$B3,$E$8:$E$19,C$2)」を入力すると、合致する件数が
表示される。絶対参照にしているので、表内のすべてのセルにコピーしても、式が正常に
入力される。C3の式で、10行目と18行目が抽出されていることが分かる。

SUMIFS関数は、2つ以上の条件を設定して、合計を求める関数である。
●二つの条件に合うデータの合計を求める。

条件に、販売店の「東京」、品名に「BB」を指定して、合致したものの金額を合計する。
C4セルに、「=SUMIFS(H7:H18,D7:D18,C3,E7:E18,C2)」と入力する。
SUMIFS関数の引数は、
「=SUMIFS(合計を計算する範囲,条件１を探す範囲,条件１,条件2を探す範囲,条件2)」の
様に指定する。
「＝SUMIFS（金額,品名,"BB",販売店,"東京")」となる。C4セルで合計が検証できる。

●指定した期間で売上合計を求める。


「〇日から〇日まで」と言う様に、期間を指定して合計を求めるには、日付データに対して
「〇日から」と「〇日まで」と言う二つの条件を設定する。
C4セルとC5セルに、「2008/2/20」と日付だけを入力している。
上記の日付条件では適切ではない。C6に関数を入力しても「0」と表示されている。



「〇日から」と「〇日まで」と指定するには、「〇日から」は「>=2008/2/20」と書き
「〇日まで」は「<2008/5/20」と、入力しなければ期間にならない。
この場合、四つの条件を設定して、条件に合うデータの合計を求めるものである。
C6セルに、「=SUMIFS(H9:H20,E9:E20,C2,D9:D20,C3,C9:C20,C4,C9:C20,C5)」と
入力する。C6セルに、「2008/2/20から2008/5/20まで」のデータが集計されていることが
分かる。



尚、複数条件を入力するには、[関数の挿入]からダイアログを出して、入力した方が
間違いがない。



ダイアログの[条件]を増やすには、右側の「V」を押すと下に追加される。
入力完了で[OK]を押します。

INDIRECT関数は、引数のセルに入力してある文字列を参照する機能がある。
仮に、A1セルに「B1:B10」と言う文字列が入力してあった場合、B1からB10にある数値を合計
しようとして、「=SUM(A1)」と、どこかのセルに入力してもB1:B10は参照されない。
A1にある「B1:B10」はただの文字列なので、参照の指定にはならない。
そこで、INDIRECT関数を使う。「=SUM(INDIRECT(A1))」とすると、A1セルの文字列へ参照
することが可能になり、B1からB10までを合計することができる。

●シート別、商品別の売上金額を求める。


上記のような、別シートにあるデータを累計シートにまとめて集計する時には、SUMIF関数を
使用する。


C4セルに、「=SUMIF('7月'!C4:C15,B4,'7月'!F4:F15)」と入力すると7月シートの
商品名AAの合計金額が表示される。
この意味は、7月シートのC4からC15の中に、累計シートのB４の商品名AAがあれば、
7月シートのその行のF列の金額を合計せよ。と言うことになる。
このセルの関数は正常にできたので、8月分、9月分のセルに入力するためにコピーしたい。



C4セルをコピーして、D4に貼り付けると、「=SUMIF('7月'!D4:D15,C4,'7月'!G4:G15)」と
なり、列位置がずれてしまう。これでは、C4からE８までのすべてにセルに、関数を入力しな
ければならない。そこで、INDIRECT関数の機能が必要になる。



C4セルに、「=SUMIF(INDIRECT(C$3&"!C4:C15"),$B4,INDIRECT(C$3&"!F4:F15))」と
入力する。結果は同じ2000になる。



C4セルをE8セルまで、すべてのセルにコピーした結果で、E8セルの関数を見てみると、
「=SUMIF(INDIRECT(E$3&"!C4:C15"),$B8,INDIRECT(E$3&"!F4:F15"))」となって、
9月シートまで正常に計算できていることが分かる。
SUMIF関数の場合、他のシートを参照するには、「シート名!範囲」の様にシート名の指定が
必要である。このシート名の部分をINDIRECT関数に置き換えると、シート名と同じ文字列が
入力してあるセルを指定することができる。これをコピーすれば、一つの式ですべての合計を
求めることができる。
SUMIF関数は、「=SUMIF(商品名のデータ範囲,集計表の商品名のセル,金額のデータ範囲)」で
第一引数の「商品名のデータ範囲」第三引数の「金額のデータ範囲」にINDIRECT関数を
使うことで、別のシートのデータ範囲を参照できる。又、絶対参照と相対参照を併用した混合参照
を使用することによって、一つのセルの関数を他のセルにコピーしても、計算式が正確に反映され
る。尚、「E$3」の、Eは相対参照で3は絶対参照と言うことになり、他のセルにコピーした場合、
Eはコピーした場所によって列が変化するが、3行目指定と言うのは変化しない。
「!」このマークは「シート」を指す。

●入力時にミスをして、同じものを重複して入力してしまった場合に、それを検出する
　事もCOUNTIF関数を使用すればできる。
「=COUNTIF(C:C,C4)は、C列にC4セルの値と同じものがあれば、そのデータを返す
と言うもので、C4も数に入るから、他に無くても「1」を返す。




「=IF(AND(COUNTIF(C:C,C5)>1,COUNTIF(D:D,D5)>1),"重複",1)」は、
C列の中に、C5セルの値があれば、そのデータ数を出せ、同時にD列の中に、D5セルの
値があれば、そのデータ数を出せ、ANDで、C列の数が1より大きくて、D列の数が1より
大きい場合、「重複」と表示し、両方が1より大きくなければ、「1」と表示せよ。
と言うことになる。
この表では、No2とNo6の売上日、商品名の両方の同じものが、存在するので重複と判断できる。
ANDの中を増やし、比較する列項目を増やすほど、項目の多いデータの重複を見つけることが
できるようになる。
●では、重複データの1番目ではなく、2番目のデータを見つけるには、どうしたらよいので
　しょうか。


「=COUNTIF($B$4:B4,B4)」と入力して、下のセルにコピーする。



セルH7の関数は、「=COUNTIF($B$4:B7,B7)」となる。B7の値が、B7より上の行に
何個あるのか、と言う問いになり、2個あるから「2」と表示される。と言うことは、2番目の
データと言うことになる。

COUNTIF関数は、条件に合うデータの件数を数える関数である。


「=COUNTIF(C6:C17,D2)」の様に、引数に「範囲」と「条件」を指定する。
「=COUNTIF(D6:D17,D2)」の様に、条件を単価列に変更して抽出することも
可能である。




●上記の様に、[条件を]を「＊」にすると、文字入力の件数を数えることもできる。


上記の様に、[条件]を「??」にすると、決まった文字数のデータ件数を数えることも
できる。文字数を「?」の数で指定する。



●上記の様に、[条件を]を「＊B＊」にすると、指定文字を含むデータ件数を数える事も
　できる。指定文字の前後に「＊」を使うと、指定文字の前後の文字は何ぜもよい、と
　言う意味になる。


●「=COUNTIF(G6:G11,">=" & AVERAGE(G6:G11))」と入力すると、平均より
　大きい値のデータの件数を数えることができる。COUNTIF関数の引数にAVERAGE
　関数を使用する事によって、平均を算出でき、平均より大きい値として指定することが
　できる。

取扱商品数が12種類ある商品の、現在庫種類を求める場合。
D2セルに、「=COUNTA(C6:C17)と入力すると、取扱商品の全数が表示される。
これは、B列の「No」の「12」と同じである。
D3セルに、「=COUNTA(E6:E17)」と入力すると、E7セルが空白なので、現在の在庫数の
「11」が表示される。このことから、E7セルの空白を数えていないことが分かる。
と言うことは、COUNTA関数は、「文字列」でも「0」でも「空白」以外は数える。
D3セルに、「=COUNT(E6:E17)」と入力すると、「9」と表示される。このことから、
COUNT関数は、「0」と「数値」のセル以外、文字列と空白は、数えていないことが分かる。

　
関数の実用事例と引数の解説
　表引きに使用する関数の解説

実際に使っているシステムの内容で解説
　　EXCEL VBA 講座