この関数は必見です。とても便利な関数になってます。
エクセルを使用するうえでエラーや空白は付き物。
その際にエラーや空白を無視して計算したいですよね。今回はそれを可能する関数です。
この関数を使用するだけでエラーが発生する可能性がある場面は防ぐことができます。
それでは見ていきましょう。
■関数の仕組み
=AGGREGATE(計算方法,除外のオプション,計算範囲)
計算方法：どのような計算をするか選択する（合計や平均など）
除外のオプション：エラーや空白等、スルーする条件を決める
計算範囲：計算する範囲を決定する
■ エラーを無視して計算する
見本で下図のような得点表を作成しました。
今回はA～Jさんの点数の合計・平均を求めていきます。
各得点に様々なエラーが発生していますね。
この状態で計算してみます。
計算した結果がこちらです。
はい、このようにエラーになってしまいます。
このエラーを無視できるのが今回の関数です。
では、AGGREGATE関数の説明に移ります。
関数の内容はこちら。
=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 | 百分位数（0％と100％）を除外 | PERCENTILE.EXC |
| 19 | 四分位数（0％と100％）を除外 | QUARTILE.EXC |
よく使うのは1.平均、2.カウント、4.MAX、5.MIN、9.合計ぐらいでしょうか。
標準偏差は分析マンには必需品のアイテムですね。
・除外のオプション
ここでは無視したいオプションを選択します。
文字通りなので説明しませんが、
迷ったら「7」の「非表示の行とエラー値を無視します」を選択で大丈夫です。
非表示の行の無視や、入れ子、エラー値のみ無視など様々なオプションがあるので場面によって
使い分けてみてください。
■実践する
・エラーを無視して合計を計算
では合計を打ち込んでみましょう。
計算方法は合計なので「9」。除外方法は「7」で設定します。
そして合計したい範囲を関数で打ち込むと以下のようになります。
=AGGREGATE(9,7,C3:C12)
では実際に打ち込んでみましょう。
エラーにならず、計算してくれていますね。
・エラーを無視して平均を計算
計算方法は平均なので「1」。除外方法は「7」で設定します。
そして合計したい範囲を関数で打ち込むと以下のようになります。
=AGGREGATE(1,7,C3:C12)
こちらもうまくいきましたね！
ぜひ活用してみてください。
■練習問題
・ 非表示の行とエラー値を無視して最大値（MAX）を計算
では下図の表を参考にしてみましょう。
対象はＣ3からC８とします。しかしエラーと空白が混じっているので無視します。
答え：=AGGREGATE(4,7,C3:C8)
解説：最大値(MAX)は「4」、非表示の行とエラー値を無視は「7」、範囲は「 C3:C8 」
・ 非表示の行とエラー値を無視して最小値（MIN）を計算
先ほどの計算を最小値（MIN）で計算してみましょう。
対象は先ほどと同様Ｃ3からC８とします。今回もエラーと空白が混じっているので無視します。
答え：=AGGREGATE(5,7,C3:C8)
解説：最大値(MAX)は「5」、非表示の行とエラー値を無視は「7」、範囲は「 C3:C8 」
■公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
AGGREGATE 関数 – Office サポート (microsoft.com)
■まとめ
いかがだったでしょうか。
とても便利ですよね。私もこの関数を学んで
SUM関数やAVERAGE関数を使わない場面が増えました。
個人的にとても好きな関数です.
それでは次回の記事でお会いしましょう。
このサイトでは独学でエクセルの技術を磨くことができます。基本操作やVBA、関数を随時更新中です。
VBAではストーリー形式で説明していますが途中からでもわかるようにしています。ぜひ参考にしてみてください。
いつかはエクセルの教科書のようなサイトを目指してます。
コメントで知りたいことや悩みなど記入していただければお答えしていきます。
ぜひ、ほかの記事もご覧ください。皆様の参考になれれば幸いです！
コメント