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

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

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

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


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


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

条件の入力完了で[OK]を押す。セルとか範囲をそのまま選択すると入力されるので
効率的である。

SUMIF関数は、条件に合うデータを見つけ、その同じ行にある値を合計することが
できる関数である。
●条件に合うデータの合計金額を求める。

セルD3に、「=SUMIF(C6:C17,D2,F6:F17)」と入力する。
「=SUMIF(条件のデータを探す範囲、条件、合計する範囲)」の様に3つの引数を指定する。
C6からC17の範囲から、D2の値と同じ値がある行を探して、その行のF列の金額を合計する。
条件を変更すれば、特定の品名の合計も算出できる。
又、引数の[条件]に直接品名を入力するには、"AEF"の様に文字データを「" "」(ダブルクオー
テーション)でくくる必要がある。[条件]が、数値の場合は、「"<=200"」の様に比較演算子を
付けて「" "」でくくる。

●商品別の売上金額を求める。

売上データの表から、商品ごとの売上金額を計算する場合、C3セルに次の関数を入力する。
「=SUMIF($C$9:$C$20,B3,$F$9:$F$20)」と入力し、それをC6セルまでコピーする。

セルC6までに各合計金額が表示された。
「=SUMIF(商品名のデータ範囲、集計表の商品名セル、金額のデータ範囲)」と指定し、
データ範囲を絶対参照にすることによって、コピーしても範囲は変わらない。

●商品別の売上累計を求める。

セルG4に、「=SUMIF($C$4:C4,C4,$F$4:F4)」とC列とF列を絶対参照にして入力する。

G4セルをG15セルまでコピーすることにより、それぞれが４行目から関数を入力する行までを
範囲とすることができる。先頭行のみ絶対参照にするのがポイントである。
品名AAの累計が14行目で、正常に計算されていることが結果からわかる。

COUNTIF関数は、条件に合うデータの件数を数える関数である。
D3セルに、「=COUNTIF(C6:C17,D2)」と入力すると「3」と表示される。
D2セルの「A」のある行を数えている。
引数に[範囲]と[条件]を指定する。


●D2セルの[条件]を、単価に変更して「<500」とし、D3セルに
　「COUNTIF(D6:D17,D2)」と入力すると、単価が500以下の4行が抽出されて、
　D3セルに「4」と入る。


●又、上記の様にD2セルの[条件]を「＊」にすると、文字入力の件数を数える
　事もできる。この場合、D3セルに空白セルを除いて「10」と表示される。


●又、上記の様にD2セルに[条件]を「??」にすると、「文字数」のデータ件数を数える
　こともできる。この場合、文字数を「?」の数で指定する。


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


●又、入力ミスを検出するのに、COUNTIF関数を使用する事もできる。
　H4セルに、「=COUNTIF(C:C,C4)」と入力すれば、C列にC4セルと同じ値があれば、
　そのデータ数を表示する。他に無い場合は、「1」を表示する。


●又、H4セルに、「=IF(AND(COUNTIF(C:C,C4)>1,COUNTIF(D:D,D4)>1),"重複",1)」
　と入力すると、重複が入力があれば「重複」と表示されるので分かりやすい。


H4の関数を下までコピーしたので、
H5セルを見ると、「IF(AND(COUNTIF(C:C,C5)>1,COUNTIF(D:D,D5)>1),"重複",1)」と
記入されている。
この関数は、「C列の中に、C５セルの値と同じものがあれば、そのデータ数を数える。
同時に、D列に中に、D5セルの値と同じものがあれば、そのデータ数を数える。
C5セルの値と、D5セルの両方のデータ数が1より大きい場合、「重複」と表示せよ。」となる。
この表では、売上日と商品名の同じものが入力されていると重複入力と規定している。
同じ日に、同じ商品名で、数量の違うものを、入力する可能性がある場合は、「AND」の
中を、3つにする必要がある。

●それでは、重複データを入力した時に、すぐ見つけるには、どうしたら良いのでしょうか。

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


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

COUNTBLANK関数は、未記入セルを数える関数である。
E2セルに、「=COUNTBLANK(E5:E16)」と入力すると、E列の指定範囲の
未入力セルを数えて、「3」と表示する。
但し、スペースで「空白」を入力したセルは数えられない。

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



COUNT関数で同じように、D3セルに「=COUNT(E6:E17)」と入力すると、
「9」と表示される。E7セルが「空白」で、E9セルが「文字列」、E13セルが「A」、
E15セルに「0」が入っている。
これで在庫数が「9」なら、数値の「0」は数えるが、「空白」と「文字列」だけは
数えていないことが分かる。
COUNTA関数は、数値を数えて、数値以外は数えないことが分かる。

データベースから必要な項目を抽出して、その抽出した項目だけの合計を求めるには、
SUBTOTAL関数を使用する。
まず、表のフィールドB2からG2までを選択する。上部の[データ][フィルター]をクリック
する。


フィルターが有効になり、表項目に[▼]ボタンが表示される。
G14セルを選択し、「=SUBTOTAL( 」と入力すると、集計方法の一覧が表示されるので、
その中の「9」を選択し入力する。（9はSUMを示す）
続けて、「=SUBTOTAL(9,G3:G12)」と入力する。

G14セルに、G列に表示されているデータの総合計が表示された。


では、フィルターを使用して、6月の売上高だけを抽出してみよう。
売上日の[▼]ボタンを押すと、各月が表示されるので、6月だけにチェックが
付いている状態にして、[OK]ボタンを押す。


6月だけが抽出されて、G14セルに抽出されたデータだけの合計値が入った。


G15セルに、「=SUM(G3:G12)」と入力すると、非表示分も含めた総合計が表示される。
オートフィルター機能で、抽出した結果を集計するのにSUBTOTAL関数を使用するのは、
VBAプログラムでも、データベースから必要なデータを抽出して、抽出結果を集計するのに
使用される。
尚、フィルターを解除するには、[データ]の[フィルター]を再度クリックすればよい。

データを最下行に追加して行く表の場合、追加した行も集計対象にして計算したい。
セルC2に、「=SUM(OFFSET(C4,1,0,COUNTA(B:B)−3,1))」と記入する。
合計は、SUM関数で求めるが、その引数にOFFSET関数を使用して、合計範囲がデータの
行数分になるようにする。データの行数は、COUNTA関数で求めることができるので、
OFFSET関数の引数にCOUNTA関数を指定する。
「OFFSET(起点セル、移動行数、移動列数、行の大きさ、列の大きさ）」の「行の大きさ」に
COUNTAを用いる。COUNTA関数は、空白セルを数えないでデータセルだけを数える。
例では、B列の日付データの行数分を合計の範囲にする。これは、B列全体のデータを
「COUNTA(B:B)」で数え、そこからB列にある「売上計算」、「合計」、「日付」の3行の
余分なデータ数を引く。これで、日付データの行数が求められ、「列の大きさ」は「1」とする。

合計する範囲をその都度変更するには、SUM関数の引数にOFFSET関数を使う。
セルC4に、「=SUM(OFFSET(C6,C2,0,C3−C2+1,1))」と入力する。
SUM関数の引数に、OFFSET関数を使った場合、
「OFFSET(起点セル,移動行数,移動列数,行の大きさ,列の大きさ)」を指定する。
「起動セル」をC6セル、「移動行数」をC3セル、「移動列数」は「0」を指定する。
「行の大きさ」は、「C3−C2+1」の計算式を指定、「列の大きさ」は「1」を指定する。
ここでは、「起点セル」をC6セルと決め、「移動行数」に「開始日付」のC2セルを指定し
て範囲の開始行を決める。「行の大きさ」に「終了日付(C3)−開始日付(C2)+1」を指定し
て範囲の行数を求めている。

上記の表は、人数が多くなる程一人当たりの料金が安くなるような場合、指定した人数では
いくらになるのかを計算するものである。
C3セルに、「=SUM(OFFSET(C6,0,0,C2,1))」と入力する。
SUM関数の引数に指定する、「OFFSET(C6,0,0,C2,1)」は「C6を起点として、C2に指定し
た行数分の範囲」と言う意味になる。
OFFSET関数は、起点のセルから、指定した行数、列数だけ移動したセルを参照する関数で
あるが、起点のセルから、指定した行数、列数の「範囲」を参照することもできる。
OFFSET関数の引数は、「=OFFSET(起点セル,移動行数,移動列数,行の大きさ,列の大きさ)」の
様に指定し「行の大きさ」と「列の大きさ」で範囲を指定する。
ここでは、起点セルから移動はしないので、引数の「移動行数」と「移動列数」は「０」にする。
「行の大きさ」にSUM関数で合計したい範囲の行数（ここではC2セルに入力した値）を
指定し、合計したいのは1列なので「列の大きさ」は「1」を指定する。

「移動和」とは、E3セルの場合2007年5月から2008年3月までの合計に、2008年4月の
値を加算する計算である。これは、過去12ヶ月分を移動しながら合計して行き、その値の
示す傾向を分析するための計算方法である。値をグラフに示すと傾向がよくわかる。

関数の入力方法は、E3セルに、「=SUM(C4:$C$14,$D$3:D3)」と入力する。


E3セルをコピーして、E4からE14セルへ貼り付ける。


E14セルの関数は間違っている。D3からD14までで12ケ月なので、
「C$14:$C15」は不必要である。

「=SUM(D3:D14)」に変更する。最終行だけは、コピーしたままでは正しくない。
式の指定範囲に、間違いがなければ完了である。

「売上合計」シートにある集計表に、5月、6月、7月のシートにある売上高の合計を
集計したい。表の形式は同じである。



「売上合計」シートのC3からE6までを選択して、[オートSUM]を押すと、[数式バー]に
「=SUM( )」が記入される。

次に、[5月]シートを選択すると、[数式バー]に5月のシート名が入る。


次に、[Shift]を押しながら[7月]シートを選択すると、数式バーに[7月]と名前が入る。


セルC3を選択すると、数式バーに「'!C3」が入るので、[オートSUM]ボタンを押すと
入力が完了する。


「売上合計」シートを見ると、5月から７月の合計が入っている。
ここで重要なことは、C3を選択した後に「オートSUM」ボタンを押すことである。
[Enter]キーを押してしまうと、SUM関数の一括入力が出来ないことである。
「オートSUM」から始めて、「オートSUM」で終わることで完了する。

「第一四半期シート」の表の合計金額を、「上半期集計シート」に書き出すものである。
「=SUM(第一四半期 ! C4:E9)」の、「第一四半期 ! 」がシートを指定する記号である。

「上半期集計シート」に、「=SUM(第一四半期 ! C4:E9)」の式を入力するときは、
SUM入力後に、「第一四半期シート」を選択して、C4からE9までを選択すると式が
入力される。

累計計算式は、E5セルに「D4+D5」の結果を書き、E6セルに「D4+D5+D6」の
結果を書くことである。セルE4の式の、「SUM($D$4:D4)」は、E4のセルをコピーして
下方のE14まで貼り付けるためのもので、このD4を絶対参照にしないと、コピーした時に
セル値が変わって、累計計算にならない。

コピー結果、E6セルの式が「$D$4:D6」となっていることが分かる。
尚、表の作成が年度の中間で、すべての月に売上高が記入されていない場合を見込んで、
売上高セルが空白、又は、0と入っている場合に、E列セルは空白にする式を記入するとよい。

上記表の小計の総計を求めたい時には、「=SUM(C4:C15)/2」と入力すれば、
求めることができるが、SUBTOTAL関数を使用すれば、個別の集計が見られる。


セルC16に、「=SUBTOTAL(109,C4:C15)」と入力すると、「表示行の合計」に
すべての行の値が合計されて表示される。


「=SUBTOTAL( 」と入力すると、[集計方法の選択]が表示される。


ここでは、「合計」だから 「109」を入力し、「C4:C15)」と入力して完了する。


地域小計を見てみよう。表の白色行を[Ctrl]を押しながらすべて選択し、
左の行番号列を選択し、[非表示]をクリックする。

地域の合計だけが集計されて、表示行の合計金額も出来ている。
逆にして、個別の地域の集計も見てみよう。

元の全体表示に戻して、緑色行を非表示にすると、個別の県の合計が表示れた。
このように表に書かれていても、SUBTOTAL関数は、非表示行の計算はされない。
[

　
関数の実用事例と引数の解説
集計に使用する関数の解説

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