【EXCEL関数②】SUBTOTALとSUMPRODUCTとROUNDDOWNの使い方

スポンサードリンク
EXCELの集計に便利な関数をご紹介します。

集計に使える関数、『SUM』『SUMIF』『SUMIFS』の使い方は前回のブログを見てね。

 

前回は、『SUM』『SUMIF』『SUMIFS』という、指定した範囲、指定した条件での合計値を求める関数を説明しました。これはMicrosoft EXCELで一番最初に覚えるべき入門編の関数ですので、使い方、エクセルでの表現方法についてよく理解しておいてくださいね。

 

今回は、もう少し集計条件が複雑になったり、値の表示方法に工夫が必要な場合に使う関数について、『SUBTOTAL』『SUMPRODUCT』『ROUNDDOWN』の3つを説明していきます。

 

 

④『SUBTOTAL』:指定した条件に対し、さまざまな集計値を求める関数

最初に何を求めるか、「集計方法」を定めたうえで、「集計範囲」より集計値を求めるための関数です。

 

「集計方法」は、1〜11(もしくは101~111)の値で指定します。

※1と101、2と102はどちらの値を選択しても集計方法に違いはありませんが、101〜111の3桁の値を指定した場合は、非表示の行が集計対象から除外されますのでご注意ください。

 

指定する値ごとの集計方法は下表をご覧ください。

指定する数字 集計方法の説明 (関数名)※ここでは使いません
1または101 平均値を求める AVERAGE
2または102 数値の個数を求める COUNT
3または103 データの個数を求める COUNTA
4または104 最大値を求める MAX
5または105 最小値を求める MIN
6または106 積を求める PRODUCT
7または107 不偏標準偏差を求める STDEV.S
8または108 標本標準偏差を求める STDEV.P
9または109 合計値を求める SUM
10または110 不偏分散を求める VAR.S
11または111 標本分散を求める VAR.P

 

使用方法:SUBTOTAL(集計方法,集計範囲1,集計範囲2・・・,・・・)

 

使用例:●●営業部の8月の契約件数合計を求める

⇒『●●営業部 8月売上』は、A係長から順にK社員までの契約件数と売上が入力された表になっています。今回は契約件数の合計を求めますので、契約件数を示しているC列(C4セルからC13セル)の合計を求める関数を使った計算式を、C14セルに入力します。

=SUBTOTAL(9,C4:C13)

 

すると、数式を入力したC14セルには、●●営業部の契約件数合計『153』が計算されました。

 

1-1

        ↓

1-2

 

注意事項:

・C8セルには1課の小計が、C13セルには2課の小計が入力されていますね。ここで注意しなければならないのは、先ほどのようにSUBTOTAL関数を使ってそのままC4セルからC13セルまでの合計を求めようとすると、1課の小計と2課の小計を示すセルもそのまま合計対象に含まれてしまい、『306』という誤った答えになってしまいます。

 

そこで、この小計セルを重複計算しないためには、C8セルとC13セルについては、あらかじめ以下のような計算式をそれぞれ入力しておくことで回避することができます。覚えておきましょうね。

C8セル:=SUBTOTAL(9,C4:C7)

C13セル:=SUBTOTAL(9,C9:C12)

 

 

⑤『SUMPRODUCT』:指定した複数の条件同士を掛け合わせ、その合計値を求める関数

複数の「配列」について、各配列内でのセル位置が同じ要素同士をそれぞれ掛け合わせた結果の合計を求める関数です。配列には条件式や集計範囲を入力します。

 

使用方法:SUMPRODUCT(配列1,配列2,・・・)

 

使用例:●●営業部の1課の売上合計を求める

⇒先ほどと同じ『●●営業部 8月売上』を少し加工して、C列「所属」を追加しました。今回は、C列「所属」より1課を抽出し、E列「売上」について、1課の人の分の合計を求める関数を使った計算式をC13セルに入力します。

=SUMPRODUCT((C4:C11=”1課”)*(E4:E11))

 

すると、数式を入力したC13セルには、1課の売上合計『15,000』が計算されました。

 

1-3

          ↓

1-4

Sponsored Links

 

⑥『ROUNDDOWN』:指定した桁数で値を切り捨てる関数

「数値」を「桁数」で切り捨てた結果を求める関数です。

 

使用方法=ROUNDDOWN(数値,桁数)

 

★桁数については次のようなルールがあります。

桁数=0 :小数点以下の値をすべて切り捨て

桁数=1以上:小数点第●+1位で値を切り捨て

桁数=-1以下:整数第●位で値を切り捨て

 

使用例:J社員の売上単価について、小数点第二位以下を切り捨てる

⇒先ほどと同じ『●●営業部 8月売上』の表に、F列「売上単価」を追加しました。売上単価は「売上÷件数」で求められますので、J社員の売上単価は「E10/D10」、かつ小数点第二位以下を切り捨てるとのことですので、これを表す計算式は次のようになります。

=ROUNDDOWN(E10/D10,1)

 

すると、F10セルは261.904762・・・となっていたところ、小数点第二位で切り捨てられ、『261.9』と計算されました。

 

1-5

          ↓

1-6

 

参考:

指定した桁数で切り上げたい場合は『ROUNDUP』、また、四捨五入したい場合は『ROUND』という関数が使用できます。使用方法はROUNDDOWNと同じですので小技として覚えておくと便利ですよ。

 

ここまで6つの関数をご紹介しましたが、これでもう簡単な集計はできるようになったと思います。

この調子で次も覚えていきましょうね。

 

次回は入力の補助に役立つ、日付に関する関数を5つご紹介します。

スポンサードリンク
PREV:【EXCEL関数①】SUMとSUMIFとSUMIFSの使い方
NEXT:【EXCEL関数③】TODAYとWEEKDAYとDATESTRINGの使い方

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です