Excelで非表示の行を集計しないためにSUBTOTAL関数とAGGREGATE関数を利用する。

<スポンサーリンク>  
 

こんにちは。かわべです。

Excelで合計値を表示する場合にはSUM関数等を利用するかと思いますが、SUBTOTAL関数かAGGREGATE関数を使用すると非表示にした行を集計するかどうかを選択することができます。

SUBTOTAL_16

(使用Excel;2013)

参考

参考 microsoft;SUBTOTAL関数

参考 microsoft;AGGREGATE関数


<スポンサーリンク>    
 

SUBTOTAL関数

SUBTOTAL関数の記述方法

SUBTOTAL関数は次のように記述します。

「=SUBTOTAL(集計方法,範囲,[範囲2],…)」

 

SUBTOTAL関数の集計方法

SUBTOTAL関数の集計方法は数値を指定しますが、次のように決まっています。

(microsoft;SUBTOTAL関数より)

SUBTOTAL_11

 

SUBTOTAL関数の使用例

合計値を算出する方法

SUBTOTAL関数では、SUM関数と同じように指定範囲の合計値を算出することができます。

SUBTOTAL関数で合計値を算出するには、集計方法「9」と記述します。

SUBTOTAL_12

上の画像では、C12セルにSUBTOTAL関数の記述をしてあります。(上の画像の赤色の枠囲み部分)C5~C11の合計値を表示しています。

※ SUM関数で記述する場合

上の画像のSUBTOTAL関数の式は、SUM関数を使って記述することができます。(SUM関数と使う方が多いかと思います。当たり前ですが、表示される結果は同じです。)

SUBTOTAL_13

 

非表示の行を集計しない方法

SUBTOTAL関数では非表示の行を集計しないことができます。

先にSUM関数を使用している場合、行を非表示にしたらセルがどのような結果を表示するかを確認していみます。

SUBTOTAL_14

上の画像ではC12セルにSUM関数を使って週(8/3~8/9)の合計値を表示していますが、平日(8/3~8/7)を非表示にしても週の合計金額184,000円に変化はありません。

SUM関数では「平日を非表示にして毎週土日の売上のみを集計したい」という処理をするのは難しそうですが、SUBTOTAL関数を使うとどうなるでしょうか。

SUBTOTAL関数の場合、集計方法を「9」から「109」に変更すると次のように非表示にした行の売上を集計しなくなります。

SUBTOTAL_15

上の画像では、C12セルにSUBTOTAL関数を使った式を記述しています。集計方法は「109」です。(上の画像の水色の矢印)集計範囲はC5~C11セルのままです。

このように記述すると、非表示の8/3から8/7までは集計されず、8/8と8/9の2日間の数値の合計77,000円が表示されます。

AGGREGATE関数

AGGREGATE関数の記述方法

SUBTOTAL関数と似た働きをする関数でAGGREGATE関数という関数があります。

AGGREGATE関数は次のように記述します。

「=AGGREGATE(集計方法,集計方法, オプション, 範囲 1, [範囲 2], …)」

 

AGGREGATE関数の集計方法

AGGREGATE関数での集計方法は、SUBTOTAL関数と同じく数値を指定しますが、次のように決まっています。

(microsoft;AGGREGATE関数より。引用の画像は小さいので、詳細はリンク先でご確認ください。)

AGGREGATE_11

 

AGGREGATE関数のオプション

上の画像の集計方法の数値で確認できるようにAGGREGATE関数にはSUBTOTAL関数のような100桁の数値はありません。

その代わり、「オプション」のところで非表示等の行の取り扱いを指定します。オプションは次のように決められています。

(microsoft;AGGREGATE関数より)

AGGREGATE_13

AGGREGATE関数を使って非表示の行を集計しない記述

このAGGREGATE関数を使って、SUBTOTAL関数のように非表示の行を集計しないためには、集計方法「9」、オプション「1」を指定して記述します。

AGGREGATE_12

次の画像のとおり、非表示の行を集計しないことができました。

AGGREGATE_14

まとめ

なかなか便利な関数です。

私は、税理士業務では非表示にする行があってもSUM関数を使用しています。給与の集計等で表示した行だけを集計したい場合はSUBTOTAL関数を使うことが多いです。


■□◆◇ 編集後記 ◇◆□■

かなりの大雨です。風に強い傘を購入しようと思っていましたがレインコートのほうが良いかな?

(↑風に強い傘だそうです。)


<スポンサーリンク>


お知らせ等

<このブログの管理人>
勤務先 ⇒ UI総合事務所
 
<お問い合わせ先>
 税務顧問契約等をご希望の方は、UI総合事務所のお問い合わせページよりお問い合わせください。

 税理士かわべと直接、顧問契約等をご希望の方はお問い合わせページよりお問い合わせください。

<スポンサーリンク>


<スポンサーリンク>








Pocket
このエントリーを Google ブックマーク に追加
LINEで送る