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

<スポンサーリンク>  
 

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

SUBTOTAL_16
(使用Excel;2013)

(参考)

参考 microsoft;SUBTOTAL関数

参考 microsoft;AGGREGATE関数


<スポンサーリンク>    
 

● SUBTOTAL関数

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

 

「=SUBTOTAL(集計方法,範囲)」

 

集計方法

集計方法は数値を指定しますが、つぎのようになっています。

(microsoft;SUBTOTAL関数より)

SUBTOTAL_11

 

合計値の表示方法

SUM関数と同じように使うのであれば、集計方法を

「9」と記述し、その後に集計範囲を記述します。

 

例えば次の画像のように記述します。

下の画像では、C12セルにC5セルからC11セルまでを

合計するSUBTOTAL関数の記述をしてあります。

 

SUBTOTAL_12

 

次の画像のとおりSUM関数でも結果は同じです。

SUBTOTAL_13

 

 

● SUBTOTAL関数で非表示の行を集計しない

このような表で、例えば、「平日を非表示にして

毎週土日の売上のみを集計したい」という場合

どうすればよいでしょうか?

 

SUM関数の場合

SUM関数の場合、平日を非表示にしても集計結果は

変わりません。

下の画像のとおり、8/3から8/7までを非表示にしても

週の合計金額184,000円に変化はありません。

SUBTOTAL_14

 

 

SUBTOTAL関数の場合

SUBTOTAL関数の場合、集計方法を「9」から

「109」に変更すると次のようになります。

SUBTOTAL_15

 

非表示の8/3から8/7までは集計されず、8/8と

8/9の2日間の数値の合計77,000円が表示されます。

 

● AGGREGATE関数で非表示の行を集計しない

SUBTOTAL関数と似た働きをする関数で

AGGREGATE関数という関数があります。

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

 

「=AGGREGATE(集計方法,オプション,配列)」

 

集計方法

集計方法は、SUBTOTAL関数と同じく数値を

指定しますが、つぎのようになっています。

(microsoft;AGGREGATE関数より)

AGGREGATE_11

 

オプション

上の画像の集計方法の数値で確認できるように

AGGREGATE関数にはSUBTOTAL関数のような

100桁の数値はありません。

 

その代わり、「オプション」のところで非表示等の

行の取り扱いを指定します。

オプションはつぎのようになっています。

(microsoft;AGGREGATE関数より)

AGGREGATE_13

 

 

非表示の行を集計しない記述

このAGGREGATE関数を使って、SUBTOTAL関数の

ように非表示の行を集計しないという記述をすると次の

ようになります。

集計方法「9」、オプション「1」を指定します。

AGGREGATE_12

 

結果は次のとおりです。

AGGREGATE_14

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

かなりの大雨です。

風に強い傘を購入しようと思っていましたが

レインコートのほうが良いかな?

 

 

 

 


<スポンサーリンク>


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

<スポンサーリンク>


<スポンサーリンク>








Pocket
Bookmark this on Google Bookmarks
LINEで送る

ABOUTこの記事をかいた人

生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)