【保存版】AGGREGATE関数完全マスター！業務効率が3倍になる実践テクニック

エクセル

Excelの作業がとにかく遅い…

もっと楽に集計作業できないかな…？

そんな悩みを抱える方は多いのではないでしょうか。

実は、AGGREGATE関数を使いこなすだけで、これらの課題を一気に解決できるんです！

本記事の内容

・従来の10倍の速さで集計作業が完了
・エラー処理も自動化で作業ミスがゼロに
・条件付き集計もシンプルな数式で実現

AGGREGATE関数は、Excel 2010以降で使える最強の集計関数。実際に導入した企業では、月次レポートの作成時間が平均65%削減されたというデータもあるとか。

ぜひ最後までお読みいただき、あなたのExcel作業を劇的に効率化する方法を見つけてください！

【完全マスター】これだけで全部わかる！AGGREGATE関数の魅力

【完全マスター】これだけで全部わかる！AGGREGATE関数の魅力

「従来の関数では実現できなかった複雑な集計も、シンプルな方法で実現できる」これがAGGREGATE関数の魅力ですね。

・Excel 2010以降および Microsoft 365で使用可能

・旧バージョンでの代替関数：
　→SUBTOTAL関数（基本的な集計）
　→DSUM/DAVERAGE関数（条件付き集計）
　→LARGE/SMALL関数（順位付け）
　→SUMIF/AVERAGEIF関数（条件付き集計の基本関数）

「こんなとき使おう！」AGGREGATE関数が輝く5つの瞬間！

ビジネスシーンで特に役立つ活用シーンとしては以下。

・非表示セルや、フィルター後のデータだけを集計したいとき

・エラーを含むデータ範囲から、正常な値のみを集計したいとき

・条件付きで上位値や下位値を抽出したいとき

・動的な範囲で最大値や最小値を求めたいとき

・複数の条件を組み合わせた高度な集計が必要なとき

もう古い関数には戻れない！解決できる3つの悩み

従来のエクセル作業では避けられなかった以下の課題を、AGGREGATE関数は簡単に解決します！

ゆきえ
ゆきえ

非表示セルを除外した集計ができない

ゆうま
ゆうま

自動で除外して集計します！

たまえ
たまえ

エラー値の処理が面倒

ゆうま
ゆうま

エラー値を自動的にスキップします！

ゴンザレス
ゴンザレス

条件付き抽出に複雑な数式が必要

ゆうま
ゆうま

シンプルな数式で実現可能です！

導入したその日から変わる！4つの劇的ビフォーアフター

AGGREGATE関数の導入で得られる具体的なメリットを知れば、あなたもきっと明日から「匠」を自称することでしょう←

①集計作業時間が大幅に削減

②複数関数の組み合わせが不要で数式がスッキリ

③エラー処理が自動化され、データ管理が容易に

④条件変更に強い、メンテナンス性の高い集計表が作成可能

【初心者歓迎】誰でも今すぐ使える！AGGREGATE関数の基礎知識

【初心者歓迎】誰でも今すぐ使える！AGGREGATE関数の基礎知識

AGGREGATE関数は一見複雑に見えますが、基本的な仕組みを理解すれば誰でも簡単に使いこなせます。

まずは基礎から順番に見ていきましょう！

すぐわかる！基本構文と4つの引数の使い方

基本構文↓

PowerShell
＝AGGREGATE(関数番号, オプション, 範囲1, [範囲2], …)
各引数の役割
  • 関数番号
    どんな計算をするか

    （1～19の数字で指定）

  • オプション
    集計方法の詳細設定

    （0～7の数字で指定）

  • 範囲1
    メインの計算範囲
  • 範囲2以降
    必要に応じて追加する計算範囲

よく使うオプション設定としては、

0すべての値を計算に含める（標準設定）
1数式内で参照されているセルを無視
4手動で非表示にされた行を無視
5オートフィルターで非表示になっている行を無視
6エラー値を無視
7非表示行、自動フィルター、エラー値をすべて無視

が、あります！

これさえ押さえれば完璧！19種類の集計パターン総まとめ

最初の引数である関数番号の一覧ですね！

1AVERAGE（平均値）
2COUNT（数値の個数）
3COUNTA（空白セル以外のセルの個数をカウント。エラー値も1としてカウントされる）
4MAX（最大値）
5MIN（最小値）
6PRODUCT（数値の積。結果が±1.79769313486232E+308を超える場合は#NUM!エラーを返す）
7STDEV.S（標本標準偏差）
8STDEV.P（母標準偏差）
9SUM（合計値）
10VAR.S（標本分散）
11VAR.P（母分散）
12MEDIAN（中央値）
13MODE.SNGL（最頻値）
14LARGE（降順でn番目の値。nは1以上の整数）
15SMALL（昇順でn番目の値。nは1以上の整数）
16PERCENTILE.INC（0から1の範囲で指定する累積分布のパーセンタイル）
17QUARTILE.INC（0:最小値、1:第1四分位、2:中央値、3:第3四分位、4:最大値を返す）
18PERCENTRANK.INC（百分位順位）
19MODE.MULT（最頻値が複数ある場合は配列数式として使用する必要があり、通常の数式では最初の最頻値のみを返す。AGGREGATE関数では配列結果に対応していないため、最初の最頻値のみを返す）

【実践力アップ】プロ直伝！AGGREGATE関数の極意

【実践力アップ】プロ直伝！AGGREGATE関数の極意

AGGREGATE関数の基本を押さえたら、次は実践的な活用法を習得しましょう。

ここからは業務効率を劇的に向上させる実践テクニックをご紹介します！

「すごい！」と言われる条件付き集計ワザ

条件付き集計の実践テクニック
  • ①
    売上データの条件付き集計例

    ・特定の部署の上位3件を抽出

    Power Query
    　=AGGREGATE(14,6,IF(部署="営業部",売上,NA()),1) ※1位
　=AGGREGATE(14,6,IF(部署="営業部",売上,NA()),2) ※2位
　=AGGREGATE(14,6,IF(部署="営業部",売上,NA()),3) ※3位
  • ②
    金額範囲を指定した集計

    ・10万円以上の売上の平均値を算出

    Power Query
    =AGGREGATE(1,6,IF(売上>=100000,売上,""),1)
  • ③
    複数条件での活用

    ・地域と商品で絞り込んだ売上合計

    Power Query
    =AGGREGATE(9,6,IF(AND(地域="東京",商品="A"),売上,0),1)
=AGGREGATE(9,6,IF(OR(地域="東京",地域="大阪"),売上,0),1)

パワーが倍増！他関数とのベストマッチング術

AGGREGATE関数は他の関数と組み合わせることで、さらにパワーアップします

  • INDEX/MATCH との組み合わせ

    ・条件に合う上位データの商品名を取得

    Power Query
    =INDEX(商品名列,AGGREGATE(15,6,IF(売上>=100000,ROW(売上列)-MIN(ROW(売上列))+1),1))
  • OFFSET関数との相性

    ・動的範囲の平均値を計算

    Power Query
    =AGGREGATE(1,6,OFFSET($A$2,0,0,MATCH("*",$A:$A)-2,1))

作業時間が激減！自動化の裏ワザテクニック

定型作業を自動化する実践的なテクニック
  • 自動更新型の集計表を作成

    ・データ範囲の動的設定

    Power Query
    =AGGREGATE(9,6,INDIRECT("A2:A"&MAX(2,MATCH("",A:A,0))))

    ※他シート参照の場合

    Power Query
    =AGGREGATE(9,6,INDIRECT("'シート名'!A2:A"&MAX(2,MATCH("",'シート名'!A:A,0))))
  • 条件変更に強い集計システム

    ・プルダウンと連動した集計

    Power Query
    =AGGREGATE(9,6,IF(部門=$B$1,売上,0)) ※$B$1はプルダウンリストを設定したセル
  • エラー対策を含めた完全自動化

    ・IFERROR関数との組み合わせ

    Power Query
    =IFERROR(AGGREGATE(14,6,IF(ISNUMBER(売上データ),売上データ,NA()),ROW(1:1)),"該当なし")

【トラブル撃退】もう怖くない！AGGREGATE関数のエラー解決法

【トラブル撃退】もう怖くない！AGGREGATE関数のエラー解決法

AGGREGATE関数を使いこなす上で避けては通れないのがエラー対策です。

ここでは、よくあるトラブルとその解決方法を具体的にご紹介します。

要注意！初心者が必ずハマる3大エラーの正体

代表的なエラーとその原因
  • [#VALUE!エラー]

    ・数値以外のデータ型（文字列等）が計算対象に含まれている
    ・参照範囲が無効（削除されたシートの参照等）
    ・配列数式として評価できない参照が含まれている

  • [#NUM!エラー]

    ・存在しない順位を指定している（例：データ数より大きい順位）
    ・関数番号が1～19の範囲外
    ・オプション番号が0～7の範囲外
    ・計算結果がExcelの計算可能範囲（-1E308から1E308）を超えている

  • [#NAME?エラー]

    ・関数名のスペルミス
    ・範囲名の未定義
    ・カンマの代わりにセミコロンを使用

焦らず解決！エラーを倒す必勝攻略法

対処方法
  • 基本的な確認事項

    ・関数の構文が正しいか
    ・各引数の値が適切な範囲内か
    ・参照するセルや範囲が正しいか

  • 具体的な対処法

    ・範囲の最終行を動的設定

    Power Query
    =AGGREGATE(9,6,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1))

    ・NULL値対策

    Power Query
    =IFERROR(AGGREGATE(1,6,IF(セル<>"",セル,NA()),1),"データなし")

    ・エラースキップ

    Power Query
    =AGGREGATE(9,6,IF(ISNUMBER(セル),セル,NA()),1)

失敗知らずの達人になる！プロ直伝の予防テクニック

エラーを未然に防ぐための実践テクニック
  • ①
    データ入力時の対策

    ・入力規則の設定例

    数値

    Power Query
    =AND(ISNUMBER(A1),A1>0,A1<=1000000)

    日付

    Power Query
    =AND(A1>=DATE(2024,1,1),A1<=DATE(2024,12,31))

    リスト

    Power Query
    =MATCH(A1,{"営業部","技術部","管理部"},0)>0
  • ②
    空白セル対策

    条件付き書式で空白を強調表示

    Power Query
    =ISBLANK(A1)

    デフォルト値の設定

    Power Query
    =IF(A1="",0,A1)
  • ③
    数式検証の自動化

    エラーチェック数式（基本）

    Power Query
    =AND(ISNUMBER(A1),A1>=0)

    エラーチェック数式（詳細）

    Power Query
    =SWITCH(TRUE,ISBLANK(A1), "空白セルです",ISERROR(A1), "エラー値です",NOT(ISNUMBER(A1)), "数値ではありません",A1<0, "負数です",TRUE, "OK")
  • ④
    堅牢な数式作成のコツ

    ・数値のみを確実に集計

    Power Query
    =AGGREGATE(9,6,IF(ISNUMBER(データ),データ,""),1)

    ・条件付き集計でのエラー回避

    Power Query
    =IFERROR(AGGREGATE(14,6,IF(条件,値,0),1),0)

    ・テキスト型数値の自動変換

    Power Query
    =AGGREGATE(1,6,--TEXT(データ,"0"),1)
  • ⑤
    エラーの早期発見のために

    ・条件付き書式でエラーセルを強調表示
    ・データの入力規則で型を統一
    ・定期的な数式の見直しと最適化

まとめ

まとめ

AGGREGATE関数は、エクセルの集計作業を革新的に効率化できる強力なツールです。

本記事で解説した実践テクニックを活用することで、複雑な集計作業も簡単に自動化することができます！

・非表示セルやエラー値を自動で除外して集計可能
・19種類の集計パターンを1つの関数でカバー
・条件付き集計も複雑な数式なしで実現
・従来の関数と組み合わせて活用することで更にパワーアップ

ゆうま
ゆうま

まずは簡単な集計作業から始めて、徐々に応用的な使い方にチャレンジしてみましょう！

本記事で紹介した実践例を参考に、明日からでもすぐに業務に活用できます。一度マスターすれば、データ処理の効率は劇的に向上するはずです！

さあ、今すぐAGGREGATE関数を使って、あなたのエクセル作業を次のレベルに引き上げましょう！

【永久保存版】Excel関数全486種の完全制覇マニュアル！初心者からプロまで使える究極ガイド
Excel業務を劇的に効率化！VLOOKUP、IF関数、マクロなど、実務で使える関数の使い方を初心者向けにステップ別で解説。即実践できる例文付き。
www.rakushoubito.com
2024.11.08

「帰宅したら23：00か…」

毎日の残業、本当に仕方ないことなのでしょうか？

「帰宅すると家族は寝てる…」
「冷たくなったご飯を1人で食べて寝るだけ…」
「自分の時間なんて一切ない…」

ガマンし続けたその先に、あなたの望む未来はホントにありますか？

私が経験した驚きの変化

  • 2時間の残業が【完全消滅】
  • 手作業の99%が自動化
  • 出社1時間で仕事がほぼ完了

すべては、ある”気づき”から始まりました

大手企業の管理職として10年。
最初はエクセルの知識ゼロから始めた私。

  • パソコン苦手
  • 機能が多すぎてパニック。
  • 「電源どうやってつけるの？」状態
  • ブラインドタッチどころか指一本で入力
  • 悩んでる時間がムダすぎて残業が月70時間
  • 20時間かけて作った資料が間違いだらけでやり直し

そんな私でも、ある方法に出会い、驚くほどの「ヒマ」を手に入れることができました。

詳しく見る

※このページは近日中に非公開となります

著者プロフィール
この記事を書いた人
ゆうま

2024年10月から個人ブログを運営。本業で管理職をやる傍ら、趣味として会社に頼らずに生きるためのスキル磨きをしています。将来の夢は、家族と田舎で半自給自足しながらのんびり生きること。出社後1時間で仕事が終わるエクセルの使い方や、その他個人的なおススメなどを紹介していきます！

ゆうまをフォローする
エクセル関数
業務効率化スキルアップExcel実践テクニックAGGREGATE関数
シェアする
ゆうまをフォローする
ゆうま
タイトルとURLをコピーしました