ホワイトレイア日常に役立つ情報をお届けします。なりがちですが、集計にはもっと便利な関数があります。セルや数値の個数を数えるなど、色々な集計ができます。SUBTOTAL関数にフィルターを使うと、フィルターで抽出したデータの合計だけ、求めることができます。SUM関数との大きな違いと、便利さは、この点にあります。SUM関数にはできない集計です。SUBTOTAL関数を使うと、1つの集計表で、集計方法を変えて、フィルターをかけることで、色々な集計ができます。一度使うと、SUBTOTAL関数の素晴らしさが分かります。ここでは、SUBTOTAL関数に、フィルターをかけて合計を求めたり、集計方法を変えて集計したりしています。SUBTOTAL関数SUBTOTAL関数は、指定した集計方法で集計します。数字で、集計方法を指定します。集計する最初の範囲を指定します。254個まで指定できます。SUBTOTAL関数には、その特性を生かした使い方があります。使い方によって効率アップができます。SUBTOTAL関数で小計、総合計を求める集計表の小計、合計の式にSUBTOTAL関数を使います。小計に、SUBTOTAL関数の式を入力しましょう。=SUBTOTAL(9,D13:D15)合計に、SUBTOTAL関数の式を入力しましょう。=SUBTOTAL(9,D2:D16)D17には、総合計「282,900」が返されます。SUBTOTAL関数の引数の範囲は、「D2:D16」です。小計は無視されて、合計が算出されます。合計にSUBTOTAL関数の式を入力する場合、小計にはSUBTOTAL関数の式を使わなければいけません。SUM関数を小計に入力している場合、この小計は無視されず、合計に反映されてしまします。SUBTOTAL関数使う場合は、小計、合計、全てにSUBTOTAL関数を使いましょう。SUBTOTAL関数にフィルターを使うフィルター機能を使って表示されているセルの合計を求める合計に、SUBTOTAL関数を入力した場合、フィルターで抽出されたデータの合計が求められます。フィルターで抽出されなかった、他のセルは、集計に含まれません。1.合計に、SUBTOTAL関数の式を入力しましょう。=SUBTOTAL(9,D2:D16)「D2:D16」の合計、「389,900」が返されます。2.「商品No」で、「M2035」でフィルターをかけます。=SUBTOTAL(9,D2:D16)D17の式は、変わりません。「商品No」で、「M2035」の合計、「97,000」が返されます。表示されている、「M2035」だけが集計できました!3.次に、「地域」で、「東京都」でフィルターをかけてみましょう。=SUBTOTAL(9,D2:D16)D17の式は、変わりません。「地域」で「東京都」の合計、「149,000」が返されます。表示されている、「東京都」だけが集計できました!これは、SUM関数では、出来ない集計です。非表示のセルの合計は集計方法で変わります非表示のセルを集計から除外する場合は、集計方法101~111を使いましょう。非表示とは、フィルターで抽出されなかった、見えていないセルとは違います。「書式」の「表示設定」の、「非表示」のことです。1~11で設定すると、非表示にしたセルは、SUBTOTAL関数の計算から除外されません。集計方法で合計が変わるので、気をつけましょう。合計にSUBTOTAL関数の式を設定すると、集計方法を変更するだけでさまざまな集計ができます。平均値を求める点数の平均値を、求めてみましょう。C17に、式を入力します。=SUBTOTAL(1,C2:C16)集計方法は、「1」です。「73」が返されます。「点数」の平均値が求められます。最大値を求める点数の最大値を、求めてみましょう。C17に、式を入力します。=SUBTOTAL(4,C2:C16)集計方法は「4」です。「92」が返されます。「点数」の最大値が求められます。最小値を求める点数の最小値を、求めてみましょう。C17に、式を入力します。=SUBTOTAL(5,C2:C16)集計方法は「5」です。「60」が返されます。「点数」の最小値が求められます。合計を求める点数の合計を、求めてみましょう。C17に、式を入力します。=SUBTOTAL(9,C2:C16)集計方法は「9」です。「1,099」が返されます。「点数」の合計が求められます。小計、総合計にSUBTOTAL関数を使う利点SUBTOTAL関数を使うと、他の関数にはない利点があります。集計範囲に入力されている、SUBTOTAL関数の集計値は、重複を防ぐために除外されます。SUBTOTAL関数の集計値は無視されるので、総合計に入力する、SUBTOTAL関数の引数の範囲には、表の集計範囲を指定することができます。後で行を挿入してデータを追加、または削除したときなど、小計のメンテナンスをする必要はありません。AGGREGATE関数でオプションと集計方法を変えて集計エクセルSUBTOTAL関数では、集計方法を変えて色々な集計ができますが、似ている関数に、AGGREGATE関数があります。AGGREGATE関数では、非表示の行やエラー値を無視するオプションを使用して、さまざまな集計をすることができます。SUBTOTAL関数には、引数にオプションがありません。AGGREGATE関数は、エクセル2010以から追加された関数で、あまり知られていないですが、集計方法を変えて複雑な集計ができる便利な関数です。SUBTOTAL関数とAGGREGATE関数をマスターして、集計に役立てましょう。こちらの記事もご覧ください。コメントをキャンセルメールアドレスが公開されることはありません。複数条件に一致するデータをエクセルCOUNTIFS関数でカウント!RECOMMENDこちらの記事も人気です。