書式     SUBTOTAL(集計方法,範囲1,範囲2,...)

 

        集計方法   集計に使用する等価関数

        　1     　　AVERAGE関数

        　2     　　COUNT関数

        　3     　　COUNTA関数

        　4     　　MAX関数

        　5     　　MIN関数

        　6     　　PRODUCT関数

        　7     　　STDEV関数

        　8     　　STDEVP関数

        　9     　　SUM関数

        　10    　　VAR関数

        　11    　　VARP関数

 

引数の範囲1, 範囲2,・・・は２９個まで指定できます。集計方法は１〜１１の番号で指定します。各集計方法については該当する関数を参照してください。範囲内の非表示のセルは無視され、表示されているデータだけを集計します。

 

 

【まとめ】

SUBTOTAL          計算の重複を防いだ集計値を返す

 

 

［サンプルファイル］SUBTOTAL R00 〜 R04

 

■商品別の集計

商品などの売上明細表に入力されたさまざまなデータの中から、ある商品だけの金額や個数などを集計するときＳＵＭＩＦ関数で求めることができます。数値を検索値に使うと、指定した範囲に入っているデータだけの集計を求めることができます。たとえば、販売単価が２００円以下の売上総額、といった集計ができます。

 

１．商品を指定した集計

次図Ａのような明細表があるとき、１つの商品だけの売上総額を求めてみましょう。セル範囲内の指定するデータだけを合計するには、ＳＵＭＩＦ関数を使うと便利です。ＳＵＭＩＦ関数の書き方は以下のとおりです。

 

●ＳＵＭＩＦ関数

この関数は、指定された検索条件に一致するセルの値を合計します。

 

書式     SUMIF(範囲,検索条件,合計範囲)

 

範囲             検索するセル範囲を指定する

検索条件 検索条件を指定する

合計範囲 計算するセル範囲を指定する

 

検索条件に式および文字列を直接指定する場合は、">32"、"Windows"のように半角のダブルクォーテーション(")で囲む必要があります。合計範囲に含まれるセルの中で、検索条件を満たすセルに対応するものだけが計算されます。また、範囲と合計範囲が同じ場合は、合計範囲の指定を省略することができます。

 

［例］

　各引数を図Ａの表に当てはめると範囲と合計範囲は次のようになり、検索条件をセルＨ５に入力する場合の関数は以下のようになります。範囲と合計範囲を絶対セル番地にしているのは、入力した数式（Ｉ５）をＩ６に複写できるようにしているためです。

 

範囲     　＄Ｂ＄５：＄Ｆ＄１３

合計範囲　＄Ｆ＄５：＄Ｆ＄１３

 

セルＩ５　＝ＳＵＭＩＦ（＄Ｂ＄５：＄Ｆ＄１３，Ｈ５，＄Ｆ＄５：＄Ｆ＄１３）

セルＩ６　＝ＳＵＭＩＦ（＄Ｂ＄５：＄Ｆ＄１３，Ｈ６，＄Ｆ＄５：＄Ｆ＄１３）

 

検索されるデータ列は範囲の先頭列になります。ここでは商品コードの列を検索して金額を集計するためにＢ列からＦ列を範囲にしています。もし、商品名を検索する場合は、範囲は＄Ｃ＄５：＄Ｆ＄１３になります。

 

図Ａ

　EX4A258Z

�r＞操作

�@セルＨ５に集計する商品コードを入力する

�AセルＩ５にＳＵＭＩＦ関数を利用した数式を入力する

◆セルＨ６に商品コード、セルＩ６に数式を入力する

［Ａ］集計結果

 

図Ｂ

　EX4A259Z


EX4B528

 

　ここでは、検索値をセル参照を使ってセルＨ５あるいはＨ６に入力していますが、関数に直接記述する場合は、次のように文字を表す半角のダブルクォーテーション「”」で囲む必要があります。

 

セルＩ５　＝ＳＵＭＩＦ（＄Ｂ＄５：＄Ｆ＄１３，”Ａ−１００”，＄Ｆ＄５：＄Ｆ＄１３）

 

セルＨ５〜Ｈ６に入力する検索値は、全角/半角は区別されますが、半角の英字の大文字/小文字は区別されません。

 

 

２．単価の範囲で集計する

　検索値が数値の場合、比較演算子（＜＞＝など）を使って範囲指定することができます。この場合の検索内容は数値ですが、セルに入力する検索値は文字になります。単価を検索するので、引数［範囲］は＄Ｅ＄５：＄Ｆ＄１３になり、関数は以下のようになります。

 

セルＩ１７　＝ＳＵＭＩＦ（＄Ｅ＄５：＄Ｆ＄１３，Ｈ１７，＄Ｆ＄５：＄Ｆ＄１３）

セルＩ１９　＝ＳＵＭＩＦ（＄Ｅ＄５：＄Ｆ＄１３，Ｈ１９，＄Ｆ＄５：＄Ｆ＄１３）

 

次の例では、セルＩ１７では、単価２００円以上（２００円を省く）の金額の合計、セルＩ１９では単価２００円以上（２００円を含む）の金額の合計を求めています。なお、セルに入力する比較式（「＞２００」や「＜＝２００」）は半角で入力します。全角だと正しく計算されません。

 

�r＞操作

�@セルＨ１７に「＞２００」を半角で入力する

�AセルＨ１９に「＜＝２００」を半角で入力する

�BセルＩ１７に数式を入力する

◆セルＩ１９に数式を入力する

［Ａ］結果が表示される

 

　EX4B761Z

 

　EX4B762Z

 

注意する点は、検索を数値の範囲で行うセルＨ１７とＨ１９の入力内容です。＞２００や＜＝２００は比較式になりますが、セルに入力すると文字として入力されます。文字を表す「”」を付けて入力すると「”」も入力内容になるので正しく検索されません。また、Ｉ１９の比較式を＝＜にすると、先頭が数式を表す＝になるので、これもダメです。しかし、単価を１つだけ指定するには＝１２０のようにしなければなりません。このときは、以下のように比較式をダブルクォーテイションで囲み、先頭の「”」の前に＝を書きます。

 

        ＝”＝１２０”

 

なお、ここからここまで（＞２００　Ａｎｄ　＜３００、「２００以上３００以下」）といった指定はできません。

 

 

 

【まとめ】

●SUMIF         検索条件に一致するセルの値を合計する

●検索条件をセルに入力すると文字列として扱われる

●数値を対象として検索条件は半角で入力する

●検索条件を関数内に直接記述する場合は、ダブルクォーテイションで囲む

 

 

■総合計を一度に計算する（数量＊単価）

請求書やお買いあげ明細書などで、数量と単価だけを記入して個々の小計（数量×単価）を省き、合計金額だけを明記している伝票（図Ｂ）をしばしば見かけます。手書きの伝票などではその場で小計を記入せずに、後でまとめて集計する、といったスタイルの伝票があります。このような伝票の合計金額はどのようにして計算するのでしょうか。合計を求めるセルに、すべての数量＊単価を計算する数式を入力するのでしょうか。以下の図の内容で考えれば次のようになります。

 

＝Ｄ７＊Ｅ７＋Ｄ８＊Ｅ８＋Ｄ９＊Ｅ９＋・・・・・・

 

すごく長い数式になりそうですね。このようなスタイルの計算はＳＵＭＰＲＯＤＵＣＴ関数を使って簡単に計算できます。

 

 

●ＳＵＭＰＲＯＤＵＣＴ関数

この関数は、引数として指定したセル範囲（あるいは配列）の対応する要素間の積を計算して、さらにその和を返します。

 

書式     ＳＵＭＰＲＯＤＵＣＴ（範囲１，範囲２，範囲３，・・・）

 

        範囲１，範囲２，範囲３，・・・      計算の対象となるセル範囲（あるいは配列）を指定する

                                        範囲は２個から３０個まで指定できる

 

引数［範囲］で指定するセル範囲は、それぞれ同じ方向の同じ数でないとエラー#VALUE!が返されます。範囲内の数値以外は０と見なされます。

 

使用例

図Ａの伝票があります。販売数×単価の総額（合計金額）は次の数式で求められます。下図の場合、数量のセル範囲と単価のセル範囲はそれぞれ同じセル数で、同じ縦方向に並んでいるので、次のようにＳＵＭＰＲＯＤＵＣＴ関数を使うことができます。

 

＝ＳＵＭＰＲＯＤＵＣＴ（Ｄ７：Ｄ１５，Ｅ７：Ｅ１５）

　　　　　　　　　　　　　　↑　　　　　　↑

　　　　　　　　　　　　数量のセル範囲　単価のセル範囲

 

�r＞操作

�@セルＣ４数式を入力する

［Ａ］結果が表示される

　
EX4A313Z　　　　　　　　　　　　　　　　　　EX4A315Z

 

【まとめ】

●ＳＵＭＰＲＯＤＵＣＴ関数  指定セル範囲の積の総和を返す

●指定セル範囲は、同じ方向の同じ数（セル数）であること

 

 

 

■まとめ買いの割引率

ある値を基準に計算方法を使い分けるとき、ＩＦ関数と比較演算子を使って数式を作成します。たとえば、あるお店で、同じ商品を５個以上買うと５％の割り引きがあります。１個〜４個までは通常の価格、５個から以上だとその商品の価格を５％引きにします。この場合の計算式を考えてみましょう。

 

 

１．計算方法

図Ａのような伝票（この状態の数式は表Ａです。）で考えると、項目に値引率を作って、図Ｂのように販売数により０％と５％の表示を切り替えるようにすればいいのです。ここでは、値引率を各セルに直接入力しています。値に％を付けて入力すれば自動的にパーセント形式で表示され、なおかつパーセント値で入力されます。セルＦ９の５％を例にすると、５％を入力すると表示は５％ですが、実際の入力内容は０．０５に変換された値が入力されています。

 

図Ａ　　　　　　　　　　　　　　　　　　　　　図Ｂ

　

　EX4A316Z　　　　　　　　　　　　　　　　　　EX4A318Z

 

入力する数式

セルＣ４　　＝ＳＵＭ（Ｆ７：Ｆ１５）

セルＦ７　　＝Ｄ７＊Ｅ７           セルＦ１２　＝Ｄ１２＊Ｅ１２

セルＦ８　　＝Ｄ８＊Ｅ８           セルＦ１３　＝Ｄ１３＊Ｅ１３

セルＦ９　　＝Ｄ９＊Ｅ９           セルＦ１４　＝Ｄ１４＊Ｅ１４

セルＦ１０　＝Ｄ１０＊Ｅ１０                セルＦ１５　＝Ｄ１５＊Ｅ１５

セルＦ１１　＝Ｄ１１＊Ｅ１１

 

値引率で入力しているので、率から割引金額を計算して、小計から引くことになりますね。計算式は次のように考えられます。

 

        ＝販売数×単価−（販売数×単価×値引率）

 

図ＢのセルＧ７を例にすると、実際の値引率を加えた計算式（小計−（値引額））は次のようになります。

 

        ＝Ｄ７＊Ｅ７−（Ｄ７＊Ｅ７＊Ｆ７）

 

この数式をセルＧ７に入力して、下に続くセルＧ８〜Ｇ１５に複写すれば、図Ｂになります。確認のために、図Ｂの数式を表Ｂに載せておきます。

 

表Ｂ

セルC4  =SUM(G7:G15)

セルG7  =D7*E7-(D7*E7*F7)                セルG12 =D12*E12-(D12*E12*F12)

セルG8  =D8*E8-(D8*E8*F8)                セルG13 =D13*E13-(D13*E13*F13)

セルG9  =D9*E9-(D9*E9*F9)                セルG14 =D14*E14-(D14*E14*F14)

セルG10 =D10*E10-(D10*E10*F10)   セルG15 =D15*E15-(D15*E15*F15)

セルG11 =D11*E11-(D11*E11*F11)

 

 

２◆ＩＦ関数で処理方法を使い分ける

　このままでも正しく計算できますが、その都度、値引率も入力しなければなりません。しかも、５個以上なのかを見て、５％と０％を入力することになり、入力ミスも起こしやすいことでしょう。このようにどちらかの値を選択して入力するような場合、ＩＦ関数を使って自動的に選択させることができます。ＩＦ関数を使う目的は、２つの処理を条件で使い分けるところにあります。ここでの目的は、０％と５％のどちらを使う（セルに入力する）のかです。ＩＦ関数の書き方は次のようになります。

 

●ＩＦ関数

　指定された条件式の結果を真/偽（ＴＲＵＥ/ＦＡＬＳＥ）の二通りの結果で調べ、真のとき「条件に一致したときの処理」を処理し、偽のとき「そうでないときの処理」を処理します。IF関数は値または数式が条件を満たしているかどうかをテストします。真/偽とは、条件の成立/不成立を調べ、成立したとき真、成立しなかったとき偽になることです。

 

        IF(論理式,真の場合,偽の場合)

 

論理式           テストする条件や数式を指定する

真の場合 論理式が成立した（真，ＴＲＵＥ）ときの処理を記述する

偽の場合 論理式が成立しなかった（偽，ＦＡＬＳＥ）ときの処理を記述する

 

　最大７つまでIF関数をネスト（関数の中に関数を組み込むこと）することができます。例「IF(論理式,IF(論理式,真の場合,偽の場合),IF(論理式,真の場合,偽の場合))」

　記述内容を別の表現で表すと以下のようになります。

 

        ＝ＩＦ（条件式，条件に一致したときの処理，そうでないときの処理）

　　　　　　　　　　　　　　　　　↑　　　　　　　　　　　↑

　　　　　　　　　　　　　　　　　�@　　　　　　　　　　　�A

 

真と偽のときに実行する処理をそれぞれカンマ（，）で区切った�@�Aの位置に入力します。この数式にここで行う処理内容を当てはめると次のようになります。�@の部分に入力する５％を５と入力すると５００％になるので注意しましょう。０％は０でも０％でも０に違いないので０だけ入力してもかまいません。

 

        ＝ＩＦ（条件式，５％，０％）

 

条件は販売数が５以上かそうでないか、ですね。この条件を作るためには、比較演算子を使います。Ｐａｒｔ１（Ｐ＿＿＿）で解説しているように、いくつかの比較演算子がありますが、使用する比較演算子により、指定する値が以下のように違ってきます。例としてセルＦ７に使う条件で示すます。

 

        販売数　　　　比較値

        Ｄ７　　＞　　　４ Ｄ７が４以上

        Ｄ７　　＞＝　　５ Ｄ７が５あるいは５以上

        比較値　　　　販売数

        ４　　　＜　　　Ｄ７       販売数と比較値の位置が入れ替わった

        ５　　　＜＝　　Ｄ７　　

 

上の４つの条件式のどれを使っても同じ結果がえられます。ここではＤ７＞４を使うことにして、数式を完成させると次のようになります。

 

セルＦ７ ＝ＩＦ（Ｄ７＞４，５％，０）

 

この数式をセルＦ７に入力して、下へ続くセルＦ８〜Ｆ１５に複写して集計表を完成させます。図Ｃでは０％を０で入力しています。なお、セルＦ７〜Ｆ１５の表示形式がすでにパーセント形式になっているときは、５％を０．０５にしても構いません。確認のために、複写後の各セルの数式を載せておきます。

 

�r＞操作

�@セルＦ７に数式を入力する

［Ａ］結果が表示される

［Ｂ］セルＦ７をセルＦ８〜Ｆ１５に複写する

［Ｃ］小計を求める数式に値引率をまだ組み込んでいないので小計値は変わりません。

 

図Ｃ

　　　

EX4A317Z　　　　　　　　　　　　　　　　　　　　　　　　　EX4B529Z

 

　EX4B530Z

 

入力している数式

セルＦ７　　＝ＩＦ（Ｄ８＞４，５％，０）      セルＦ１２　　＝ＩＦ（Ｄ１２＞４，５％，０）

セルＦ８　　＝ＩＦ（Ｄ９＞４，５％，０）      セルＦ１３　　＝ＩＦ（Ｄ１３＞４，５％，０）

セルＦ９　　＝ＩＦ（Ｄ１０＞４，５％，０）    セルＦ１４　　＝ＩＦ（Ｄ１４＞４，５％，０）

セルＦ１０　＝ＩＦ（Ｄ１１＞４，５％，０）    セルＦ１５　　＝ＩＦ（Ｄ１５＞４，５％，０）

セルＦ１１　＝ＩＦ（Ｄ１２＞４，５％，０）

 

●条件式

　ある値を比較して一致する、一致しない、あるいは成立する、成立しない、を調べる数式を条件式といいます。また、比較するところから比較式ともいう。

 

 

２．値引額で表示する

　先の方法は値引率を表示しましたが、値引金額で求めたいときは、ＩＦ関数の中にその計算式を組み込んで求めます。先の方法では、ＩＦ関数の中にセルに表示する（入力する）内容を記述しましたが、数式を入力しても構わない、ということです。ここでは使いませんが、数式（あるいは関数を含む）でもいいということです。覚えておきましょう。値引額は小計を表示しているセルに入力している数式の「Ｄ７＊Ｅ７＊Ｆ７」の部分です。

 

        ＝Ｄ７＊Ｅ７−（Ｄ７＊Ｅ７＊Ｆ７）

　　　　　　　　　　　　　　　↑

　　　　　　　　　　　　　　値引額を求める数式

 

　この数式をＩＦ関数に組み込めば、計算結果が表示されるようになります。ここでも、０は計算の必要がないので、０のままにしてあります。また、この数式の両端の（）は計算の優先順位を示すだけなので、省いています。もちろん付けたままでもかまいません。また、数式の中にパーセント形式の値を使うことができるので、�Aの書き方でもかまいません。

 

セルＦ７ ＝ＩＦ（Ｄ７＞４，Ｄ７＊Ｅ７＊０．０５，０）　　　数式［１］

　　　　 ＝ＩＦ（Ｄ７＞４，Ｄ７＊Ｅ７＊５％，０）　　　　　数式［２］

 

　この数式をセルＦ７に入力してみましょう。これまで、パーセントで表示していたため、セルの表示形式がパーセントのままになっていて、結果は％で表示されます。セルＦ７の表示形式を通貨に設定しなおします。（セルＦ７にセルポインタを合わせ、ツールバーの［通貨］ボタンをクリックする）。値引率を値引額に変更したことで、小計の数式を変更しなければなりません。次の数式に変更します。

 

セルＧ７ ＝Ｄ７＊Ｅ７−Ｆ７

 

 

�r＞操作

�@セルＦ７の数式を書き替える（ここでは数式［２］を入力）

◆セルＦ７を表示形式を通貨に設定する

�AセルＧ７の数式を「＝Ｄ７＊Ｅ７−Ｆ７」に書き替える

 

数式を書き替えたセルＦ７とＧ７を下へ続くセルＦ８〜Ｆ１５とＧ８〜Ｇ１５に複写します。確認のために、複写後の各セルの数式を表Ｄに載せておきます。図Ａは項目名「値引率」を「値引額」に書き替えています。

 

 

　EX4A319Z


EX4A320Z


図Ｄ

　EX4A321Z

入力する数式

F7  =IF(D7>4,D7*E7*5%,0)

F8  =IF(D8>4,D8*E8*5%,0)

F9  =IF(D9>4,D9*E9*5%,0)

F10 =IF(D10>4,D10*E10*5%,0)

F11 =IF(D11>4,D11*E11*5%,0)

F12 =IF(D12>4,D12*E12*5%,0)

F13 =IF(D13>4,D13*E13*5%,0)

F14 =IF(D14>4,D14*E14*5%,0)

F15 =IF(D15>4,D15*E15*5%,0)

 

G7  =D7*E7-F7           G12 =D12*E12-F12

G8  =D8*E8-F8           G13 =D13*E13-F13

G9  =D9*E9-F9           G14 =D14*E14-F14

G10 =D10*E10-F10        G15 =D15*E15-F15

G11 =D11*E11-F11

 

【まとめ】

ＩＦ関数         ２つの処理を使い分けるときに使う

比較演算子（＜＞＝）       ２つの値の大小（＜，＞）あるいは等価（＝）を比較するときに使う

 

 

■記号の集計（晴雨表）

記号などの集計方法を晴雨表を利用して解説しましょう。全角文字に含まれている●や■などを記号と呼んでいますが、全角のひらがなや漢字と同じ文字として扱われます。このことから、ワークシート上に記号を入力するということは、文字を入力することになります。つまり文字データを集計することになります。文字データの集計操作は、同じ文字がいくつあるのか、といった集計になります。このような集計は、ＣＯＵＮＴＩＦ関数使って行うことができます。

 

１．晴雨表

図Ａの晴雨表があります。この表では、晴れの日数、雨の日数、などをＣＯＵＮＴＩＦ関数で求めています。ＣＯＵＮＴＩＦ関数の詳細は以下の通りです。

 

図Ａ

　EX4A779Z

●ＣＯＵＮＴＩＦ関数

指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返す。

 

書式     COUNTIF(範囲, 検索条件)

 

範囲       セルの個数を求めるセル範囲を指定する。

検索条件    検索の条件を指定する

 

検索条件は、数値、式、または文字列で指定します。関数内に直接引数を入力する場合は、式および文字列を指定は、">32"、"Windows" のように半角のダブルクォーテーション(")で囲みます。

 

例      ＝ＣＯＵＮＴＩＦ（Ａ１：Ｄ１０，”＞３２”）

 

２．記号の個数

ある記号を検索条件に指定する場合、ＣＯＵＮＴＩＦ関数内に直接入力する場合は上記のようにダブルクォーテイションで囲んで入力します。

 

        ＝ＣＯＵＮＴＩＦ（検索範囲，”●”）

 

他のセルに記号を入力してセル参照で指定するときは、入力する記号をダブルクォーテイションで囲む必要はありません。次の数式を入力して晴れの日数を数えてみましょう。数式は以下のようになります。検索範囲を絶対競る番地で指定している理由は、複写操作で下のセルに続く雨やくもりの数式を入力するためです。

 

        ＝ＣＯＵＮＴＩＦ（＄Ｂ＄６：＄Ｈ＄１３，Ｋ５）

 

�r＞操作

�@数式を入力する

［Ａ］結果が求められる

 

検索範囲に指定したセル範囲内に「○」が１２個見つかったということになります。

 

 

　EX4A780Z


　EX4A781Z

次に、セルＬ５に入力した数式を下へ続くセルＬ６〜Ｌ９に複写して各日数の表示を完成させましょう。

 

�r＞操作

◆セルＬ５をドラッグアンドドロップ編集でセルＬ６〜Ｌ９に複写する

［Ａ］結果が求められる

 

　EX4A782Z


ここで、不都合な点が１つ表れました。それは、未記録の数です。検索範囲内には２０日と２１日が空白になっています。この数はセルＬ９で空白の数として求めるていますが、正しく数えられていません。この現象は、空白を検索条件にする場合に表れます。空白セルを正しく検索するためには、空白を検索する、ということを明示する必要があります。ただセルを空白にするだけでは、検索条件にならないということです。空白を明示する方法は、ダブルクォーテイションを２つ並べればいいので、条件（記号）を入力するセルＫ９に＝””を入力します。

�r＞操作

�@セルＫ９に数式「＝””」を入力する

［Ａ］空白の数が求められる

 

　EX4A783Z


　EX4A784Z


空白とスペースは、どちらも何も表示されないので見た目には同じですが、別のものとして取り扱われます。次の例は、セルＨ１１にスペースを入力した結果です。未記録の数が１になっています。

 

�r＞操作

�@セルＨ１１にスペースを入力する

［Ａ］空白の数が１つ減って未記録（セルＬ９）が１になる

 

　EX4A785Z

【まとめ】

●全角の●や■▲などの記号文字は、漢字と同じ文字として扱う

●文字を関数内に引数として記述するときはダブルクォーテイションで囲む

●半角文字の記号もある（キーボード上に並ぶ記号など）。

 

 

 

 

 

 

 

作成手順

　さて、どこから書き始めればいいのでしょう。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Part2
●

関数の活用

　集計を求めるとき、共通する科目ごとの小計を求めて、さらにすべての科目を合計することがあります。このとき、集計表の途中に入れた小計値の扱い方に考慮しなければなりません。