エクセルで非表示の行を集計しない!AGGREGATE関数

エクセルAGGREGATE関数は、集計方法を変えるだけ、色々な集計ができる関数ですが、AGGREGATE関数には、他の集計関数にはない特性があります。AGGREGATE関数は、オプションを設定することで、非表示の行を無視して集計できます。非表示の行を集計しません。その他、エラー値を無視して集計することができます。集計方法とオプションを色々変えることで、複雑な集計ができます。AGGREGATE関数は、2010エクセルから、集計関数に追加されました。1つの集計表で、集計方法を変えて色々な集計を求めたい。非表示の行を無視して集計したいなど、複雑な集計をしたいなら、AGGREGATE関数です。

 

 

 AGGREGATE関数

AGGREGATE関数では、非表示の行やエラー値を無視するオプションを使用して、さまざまな集計をすることができます。

AGGREGATE関数の書式と引数

セル範囲形式:集計方法が1~13

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

配列形式:集計方法が14~19

AGGREGATE(集計方法, オプション, 配列, [k])

集計方法1 ~ 19の数字で、集計方法を指定します。
オプション範囲内の無視する値を数値で指定します。
範囲 1集計する最初の範囲を指定します。

範囲 1、範囲 2,…集計する範囲を 253個まで指定できます。

集計方法が14~19は、配列形式になります。

配列を受け取る関数の場合、配列、配列数式、または集計値を求めるセル範囲の参照を範囲 1 に指定します。

範囲 2 は、特定の関数に必要な2番目の引数を指定します。

AGGREGATE関数の集計方法

集計方法関数説明
1 AVERAGE 平均値を求める
2 COUNT 数値の個数を数える
3 COUNTA 空白でないセルの個数を数える
4 MAX 最大値を求める
5 MIN 最小値を求める
6 PRODUCT 積を求める
7 STDEV.S 標準偏差(標本)
8 STDEV.P 標準偏差(母集団)
9 SUM 合計を求める
10 VAR.S 不偏分散
11 VAR.P 標本分散
12 MEDIAN 中央値を求める
13 MODE.SNGL 最頻値を求める
14 LARGE 指定した順位番目に大きな値を求める
15 SMALL 指定した順位番目に小さな値を求める
16 PERCENTILE.INC 百分位数を求める
17 QUARTILE.INC 四分位数を求める
18 PERCENTILE.EXC 百分位数を求める(0%と100%を除く)
19 QUARTILE.EXC 四分位数を求める(0%と100%を除く)

 

AGGREGATE関数のオプション

オプション内容
0または省略ネストされたSUBTOTAL関数 AGGREGATE関数を無視します
1非表示の行、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します
2エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します
3非表示の行、エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します
4何も無視しません
5非表示の行を無視します
6エラー値を無視します
7非表示の行とエラー値を無視します

 

 

AGGREGATE関数の基本的使い方

エクセルAGGREGATE関数は、集計方法を変えることで、色々な集計ができます。その点では、SUBSTITUTE関数とよく似ています。

SUBSTITUTE関数との違いは、オプションを設定することで、エラー値や非表示の行、ネストされたAGGREGATE関数やSUBSTITUTE関数を無視することができます。

SUBSTITUTE関数との違いを交えながら、AGGREGATE関数の基本的な使い方を、説明していきましょう。

 非表示の行を無視して集計

SUBTOTAL関数で、集計してある表があります。

1.E14の式を見てみましょう。

 

E14 =SUBTOTAL(9,E2:E13)

E14には、合計「343,000」が返されています。

2.次は、「Z」で始まる「商品№」を、非表示してみましょう。「Z」で始まる行番号を選択して、右クリック→「非表示」をクリック。または「ホーム」タブ→「セル」グループ→「書式」→「表示設定」の「非表示/再表示」→「行を表示しない」をクリックでもできます。

3.E14に、式を入力します。

 

E14 =SUBTOTAL(9,E2:E13)

E14には、合計「343,000」が返されました!

非表示にした行は、SUBTOTAL関数の場合、式に含まれてしまいます。

3.今度は、AGGREGATE関数で、E14に、非表示の行を無視するオプションを指定して、集計する式を入力してみましょう。

 

E14 =AGGREGATE(9,5,E2:E13)

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

オプション「5」は、非表示の行だけを無視します。

E14には、非表示の行が無視されて、「293,000」が返されました!

 

 

エラー値を無視して集計

SUM関数やSUBSTITUTE関数の式で集計した場合、集計範囲にエラー値がでるとエラーが返されます。

1.E14に、SUBTOTALの式を入力してみましょう。

 

E14 =SUBTOTAL(9,E2:E13)

E14には、エラー値「#VALUE!」が返されます。

2.今度は、E14に、AGGREGATE関数の式を入力しましょう。AGGREGATE関数に、エラー値を無視するオプションを指定して、集計します。

 

E14 =AGGREGATE(9,6,E2:E13)

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

オプション「6」は、エラー値だけを無視します。

E14には、エラー値が無視されて、「279,000」が返されました!

 

 

 SUBTOTAL関数とAGGREGATE 関数を無視して集計

AGGREGATE関数は、ネストされたSUBTOTAL関数 、AGGREGATE関数を無視して集計します。

E17に、AGGREGATE関数の式を入力しましょう。

 

小計に、式を入力しましょう。

集計方法は「9」、オプションは省略します。または「0」を入力します。

E6   =SUBTOTAL(9,D2:D5)    または    E6 =AGGREGATE(9,,E2:E5)

E10 =SUBTOTAL(9,D7:D9)       または    E10 =AGGREGATE(9,D7:D9)

E15 =SUBTOTAL(9,D11:D15)  または    E15 =AGGREGATE(9,D11:D15)

合計に、式を入力しましょう。

E17 =AGGREGATE(9,,E2:E16)

E17にはネストされたSUBTOTAL関数 、AGGREGATE関数が無視されて「343,000」が返されました!

 

エラー値、非表示の行、SUBTOTAL関数とAGGREGATE関数を無視する

エラー値、非表示の行、ストされたSUBTOTAL関数、AGGREGATE関数を無視したい場合は、AGGREGATE関数のオプションに、「3」を指定しましょう。

 

 

AGGREGATE関数で色々な集計方法で集計

集計方法を変えて集計

AGGREGATE関数の集計方法を変更するだけで、色々な集計ができます。

最大値を求める

金額の最大値を、求めてみましょう。

C7に、式を入力します。

 

C7 =AGGREGATE(4,3,C2:C6)

集計方法は「4」です。「金額」の最大値が求められます。

最小値を求める

金額の最小値を、求めてみましょう。

C7に、式を入力します。

 

C7 =AGGREGATE(5,3,C2:C6)

集計方法は「5」です。「金額」の最小値が求められます。

合計を求める

金額の合計を、求めてみましょう。

C7に、式を入力します。

 

C7 =AGGREGATE(9,3,C2:C6)

集計方法は「9」です。「金額」の合計が求められます。

こちらの記事もご覧ください。⇒ワード・エクセル・パワーポイント・アクセスが初心者でも短期間でマスターできる楽パソ!音声と映像で自宅で学べる。

こちらの記事もご覧ください。⇒マイクロソフトオフィスソフトとプログラミングスキルが初心者でも短期間でマスターできる楽パソ!音声と映像で自宅で学べる。

非表示の行を無視して集計するAGGREGATE関数

エクセルAGGREGATE関数は、集計方法とオプションを変更することで、色々な集計をすることができる関数ですが、あまりよく知られていません。集計でよく使われている関数は、SUM関数、またはSUBSTITUTE関数です。AGGREGATE関数の最大の特徴は、非表示の行を無視して集計できることです。1つの集計表で、集計方法を変えて色々な集計をしたい、エラー値や非表示の行を無視して集計できるようにしたいときは、AGGREGATE関数を使いましょう。

こちらの記事もご覧ください。⇒さまざまな集計をエクセルSUBTOTAL関数で求める

WEBプログラミングを入門から応用までらくらくマスターできる ➡ 動画パソコン教材

ワード・エクセル・パワーポイント・アクセスを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓

ワード・エクセルパワーポイント・アクセスとエクセルマクロVBA・WEBプログラミング・パソコンソフト作成を入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓

ワード・エクセル・パワーポイント・アクセスとWEBプログラミングを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓
エクセルのマクロとVBAを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓

VBA・Java・WEB・C#・HTML/CSSプログラミングを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓