■サンプルデータ
AGGREGATE関数のサンプルデータです。
動作の確認と検証に使用してください。ただし、関数によっては未完成のものもあります。
今後バージョンをアップしていきます。定期的にダウンロードしてみてください。
※未完成なので、ご了承ください。少しづつ完成させ、アップロードしていきます。
[wpdm_button_template id=14470 style=”instagram” align=”left”]
■複数の条件を指定して数値を合計したい / =SUMIFS()
【関数名】：AGGREGATE（アグリゲート）
【用途】：集計方法やオプションを指定することで、特定のセル（空白のセルや、エラーの値）を除外して値を集計できます。
【書式】：=AGGREGATE(集計方法 , オプション , 参照1 , 参照2 ,… ）
【補足１】
- 集計方法とオブションを指定できる。
- SUBTOTAL関数よりも、こちらのほうが機能的。
- セルにエラーがあっても無視して計算できる。
【集計方法】
| 集計方法 | 機能 |
|---|---|
| 1 | 平均値 |
| 2 | 数値の個数 |
| 3 | データの個数 |
| 4 | 最大値 |
| 5 | 最小値 |
| 6 | 積 |
| 7 | 不偏標準偏差 |
| 8 | 標本標準偏差 |
| 9 | 合計値 |
| 10 | 不偏分散 |
| 11 | 標本分散 |
| 12 | 中央値 |
| 13 | 最頻値 |
| 14 | 降順の順位 |
| 15 | 昇順の順位 |
| 16 | 百分位数 |
| 17 | 四分位数 |
【オプション】
| 集計方法 | 機能 |
|---|---|
| 0 | ネスト（入れ子）された関数を無視 |
| 1 | ０の機能 ＋ 非表示の行を無視 |
| 2 | ０の機能 ＋ エラーの値を無視 |
| 3 | ０の機能 ＋ 非表示の行とエラーを無視 |
| 4 | 何も無視しない |
| 5 | 非表示の行を無視 |
| 6 | エラーの値を無視 |
| 7 | 非表示の行とエラー値を無視 |
SUM関数やAVARAGE関数と、どう違うの？
最大の違いは以下の通りです。
ちょっとしたことですが、このエラーを無視できる機能は以外に役に立ちます。
もちろん、AGGREGATE関数を使用しなくてもエラーを省いて計算できますが、関数の組み合わせが煩雑になります。
その点、この関数を使用することでスッキリさせることが可能です。
SUMやAVARAGEよりもこちらをお勧めします。
例１ 売上記録の合計値を算出したい場合
=AGGREGATE(9,6,F5:F17)
表では「F8」がエラーになっていますが。ですがAGGREGATE関数は、オプションの指定によってエラー値を無視できます。
上記では、9（合計値）でオブションに6（エラー値を無視）を指定しています。
例２ 対象範囲のデータの個数を算出したい場合
=AGGREGATE(3,6,C5:C17)
集計方法に３を指定します。
オプションに６を指定します。
これで対象範囲内のデータの個数が算出されます。
例３ 対象範囲の平均値を算出したい場合
=AGGREGATE(1,6,F5:F17)
集計方法に１を指定します。
オプションに６を指定します。
これで対象範囲内の平均値が算出されます。
例４ 最大値を算出したい場合
=AGGREGATE(4,6,F5:F17)
集計方法に４を指定します。
オプションに６を指定します。
これで対象範囲内の最大値が算出されます。
例５ 中央値を算出したい場合
=AGGREGATE(12,6,F5:F17)
集計方法に１２を指定します。
オプションに６を指定します。
これで対象範囲内の中央値が算出されます。
■おさらい：AGGREGATE関数のスタンダードな記述方法
【例１】=AGGREGATE(9,6,F5:F17)
【書式】=AGGREGATE(集計方法 , オプション , 参照1 , 参照2 ,… ）
AGGREGATE関数は集計方法とオプションを指定することにより、幅広い集計に対応した関数です。
また、エラーを無視して計算できることが大きな特徴です。
SUM関数（合計）やAVARAGE関数（平均）などは、どこかの箇所にエラーがあると計算できないからです。
SUBTOTAL関数よりも機能的に優れているので、エクセル2010以上を使っている人はこちらの関数を使用することをお勧めします。
■おさらい：対象範囲のデータの個数を求める方法
【例２】=AGGREGATE(3,6,C5:C17)
データの個数を求める方法は上記の通りです。
集計方法「３」、オプション「６」を指定します。
■おさらい：対象範囲の平均値を求める方法
【例３】=AGGREGATE(1,6,F5:F17)
データの平均値を求める方法は上記の通りです。
集計方法「１」、オプション「６」を指定します。
■おさらい：対象範囲の最大値を求める方法
【例４】=AGGREGATE(4,6,F5:F17)
データの最大値を求める方法は上記の通りです。
集計方法「４」、オプション「６」を指定します。
集計や統計を求める
- 条件を指定して数値を合計したい
- 複数の条件を指定して数値を合計したい
- 集計方法を指定して集計値を求める
- 数値を丸める（桁の切り上げ、切り捨て、四捨五入など）
- 順列を求める
- 組み合わせの数を求める
- 乱数（ランダムな数値）を発生させる
- データの個数を求める
- 空白セルの個数を求める
- 条件に一致するデータの個数を求める
- 複数の条件に一致するデータの個数を求める
- 数値 or データの平均値を求める
- 条件を指定して数値の平均を求める
- 複数の条件を指定して数値の平均を求める
- 数値 or データの最大値を求める
- 数値 or データの最小値を求める
- 特定の範囲に含まれている値の個数を求める
- 数値の中央値を求める
- 数値の最頻値（最も多く表示されている最小の値）を求める
- 大きいほうから何番目かの値を求める
- 小さいほうから何番目かの値を求める
- 特定の範囲から順位（最上位 or 平均値など）を求める
- 四分位数を求める
日付・時刻を求める
- 日付や年、時刻を求める
- 日付から年、月、日、時間などを取り出す
- 日付が何週目かを求める
- 期限（数ヶ月前、数ヶ月後、期間）を求める
条件によって判定させる
- 条件を決めて判定する
- 複数のすべての条件が満たされているかを判定する
- いずれかの条件が満たされているかを判定する
- 条件が満たされていないことを判定する
- エラーだった場合に判定する
セルの内容を調べる
- セルが空白かどうかを調べる
- セルの値がエラーかどうかを調べる
- 文字列かどうか調べる
- 数値かどうか調べる
- 偶数か奇数かを調べる
- 数式かどうかを調べ、取り出す
- エラーの値やデータの種類を調べる
ある特定の値を探す
- 列または行方向に検索する
- 引数のリストから値を検索する
- 配列から特定の値を検索する
- 指定したセルの参照値を検索
- セルの列番号を調べる
- セルの行番号を調べる
- 列数を調べる
- 行数を調べる
- セルの参照を調べる
- 行と列の位置を入れ替える
- ハイパーリンクを作成する
- ピポットテーブルからデータを取り出す
文字列を操作する
- 文字数を求める
- 左端から指定した文字数だけ文字列を取り出す
- 右端から指定した文字数だけ文字列を取り出す
- 指定した位置から文字数だけ文字列を取り出す
- 文字列の位置を調べる
- 指定 or 検索した文字列に置換する
- 文字列を連結する
- 文字列の前後の空白を削除する
- 印刷できない文字を削除する
- ふりがなを取り出す
- 指定回数だけ文字列を繰り返す
- 全角文字 or 半角文字に変換する
- 英字を大文字 or 小文字に変換する
- 数値を表す文字列を数値に変換する
- 英単語の先頭文字を大文字に変換する
- 文字列が等しいかどうか調べる
データベースを制御する
- 条件を満たす数値の個数を求める
- 条件を満たす空白以外のセルの個数を求める
- 条件を満たすセルの合計を求める
- 条件を満たすセルの平均を求める
- 条件を満たす最大値 or 最小値を求める
- 条件を満たすデータを探す