Excelで部署×入社日×全社平均超えの平均給与を求める方法｜LET・FILTERで補助列なし

Excelで「部署がITまたはHR」「入社日が2020/1/1より後」「給与が全社員の平均より高い」人だけの平均給与を、補助列なしで求めたい場面は意外と多いです。LET＋FILTERで素直に解く方法を中心に、ゼロ件時の対策や旧Excel向けの代替式までまとめます。

やりたい集計と前提データ

社員一覧のような表があり、条件をすべて満たす社員だけを対象に平均給与（平均年収・平均月給など）を出したい、という要件です。

• 部署がIT または HR
• 入社日が2020/1/1 より後（「以降」にしたい場合は条件を変更）
• 給与が全社員（全部署）の平均給与より大きい

ここでのポイントは、給与の条件が「固定の数値」ではなく、表全体の平均（全社平均）という“計算結果”に依存している点です。

サンプル（列B=部署、列C=入社日、列D=給与）

例として、見出しが1行目、データが2～10行目に入っているケースを想定します（実務ではテーブル化がおすすめです）。

社員	部署（B列）	入社日（C列）	給与（D列）
Aさん	IT	2021/04/01	580000
Bさん	HR	2019/10/01	480000
Cさん	営業	2022/01/15	600000
Dさん	IT	2018/07/01	450000
Eさん	HR	2020/02/01	560000
Fさん	IT	2023/03/01	650000
Gさん	経理	2020/06/01	550000
Hさん	HR	2021/09/01	540000
Iさん	IT	2020/01/01	490000

この例だと、全社員の平均給与は 544,444.44… です。条件（部署=IT/HR・入社日>2020/1/1・給与>全社平均）を満たすのは Aさん（580,000）、Eさん（560,000）、Fさん（650,000）の3名で、平均は 596,666.66… になります。

なぜ「部署×入社日×全社平均超え」の平均が難しいのか

単純に「条件付き平均なら AVERAGEIFS」と考えがちですが、今回の要件には落とし穴が3つあります。

落とし穴	よくある症状	回避の考え方
OR条件（ITまたはHR）	AVERAGEIFS は基本的に AND 条件の積み上げが得意で、OR をそのまま書きにくい	FILTERでまとめて抜く／SUMIFSを部署ごとに分けて合算する
全社平均という「計算結果」を条件に使う	平均をどこで計算するかが曖昧になり、式が長くなりがち	LETで全社平均を一度だけ計算し、以降は変数として使う
補助列なしで、式だけで完結させたい	「対象フラグ列」を作れないので検算が難しい	FILTERで“対象者リスト”を一時的に出して確認できる設計にする

おすすめ：LET＋FILTERで素直に解く（補助列なし）

最も読みやすく、後から条件を増やしても破綻しにくいのが LET＋FILTER です。考え方はシンプルで、

• まず全社平均（Avg）を計算
• 条件に合う給与だけを FILTER で抜き出す
• 抜き出した給与の平均を取る

という3ステップを、1本の数式に畳み込みます。

基本形（質問の要件どおり）

=LET(
  Avg, AVERAGE(D2:D10),
  Flt, FILTER(
    D2:D10,
    ((B2:B10="IT")+(B2:B10="HR"))*(C2:C10>DATE(2020,1,1))*(D2:D10>Avg)
  ),
  AVERAGE(Flt)
)

式の読み解き（どこがキモか）

パーツ	意味	実務メモ
Avg, AVERAGE(D2:D10)	全社員（全部署）の平均給与	ここが「全社平均超え」の基準値。LETで1回だけ計算して使い回す
((B="IT")+(B="HR"))	部署がITまたはHR（OR条件）	TRUE/FALSEを加算して 1/0 のマスクにする定番。部署が増えるなら別パターンも便利
(C>DATE(2020,1,1))	入社日が2020/1/1より後	「以降」にしたい場合は >= に変更
(D>Avg)	給与が全社平均より大きい	全社平均は部署条件とは無関係に“全体”で計算する点が重要
条件1*条件2*条件3	すべて満たす（AND）	掛け算で 1/0 を作ると見通しが良く、条件追加もラク

対象者が合っているか不安なときの「デバッグ用」

平均だけ出すと、式が合っているか不安になることがあります。そんなときは、同じ条件で 対象行そのものを抜き出すと確認が一気にラクになります。

=LET(
  Avg, AVERAGE(D2:D10),
  FILTER(
    A2:D10,
    ((B2:B10="IT")+(B2:B10="HR"))*(C2:C10>DATE(2020,1,1))*(D2:D10>Avg)
  )
)

これで「誰が対象になっているか」を目で確認でき、納得してから平均式に戻せます（補助列は作らずに検算できるのが強みです）。

該当者ゼロのとき（#CALC!）を安全に扱う

条件が厳しいと、該当者が0人になることは普通に起こります。FILTERは該当がないと #CALC! を返すため、そのまま平均を取るとエラーになります。運用では IFERROR（またはIFNA）で包むのが安全です。

=LET(
  Avg, AVERAGE(D2:D10),
  Flt, FILTER(
    D2:D10,
    ((B2:B10="IT")+(B2:B10="HR"))*(C2:C10>DATE(2020,1,1))*(D2:D10>Avg)
  ),
  IFERROR(AVERAGE(Flt), "該当者なし")
)

「該当者なし」を数値の0にしたいなら、最後を IFERROR(AVERAGE(Flt),0) にします。ただし、レポート上で0が実データなのか“該当なし”なのか判別しづらくなるので、表示は文字列・内部計算は別セルで数値…という設計も現場ではよく使われます。

テーブル（構造化参照）で崩れない式にする

範囲 D2:D10 のような固定範囲は、行が増えるたびに手修正が必要になりがちです。実務では、社員一覧をテーブル化（Ctrl+T）して、構造化参照で書くのが安定します。

テーブル名を 社員、列名を [部署]、[入社日]、[給与] とした場合は次の形になります。

=LET(
  Avg, AVERAGE(社員[給与]),
  Flt, FILTER(
    社員[給与],
    ((社員[部署]="IT")+(社員[部署]="HR"))*(社員[入社日]>DATE(2020,1,1))*(社員[給与]>Avg)
  ),
  IFERROR(AVERAGE(Flt), "該当者なし")
)

テーブル参照にしておくと、データが100行→1,000行に増えても式がそのまま使えます。さらに、列の入れ替えや追加にも強く、メンテナンス性が段違いです。

互換性重視：SUMIFS＋COUNTIFSで補助列なし平均（動的配列なしでも）

Excelの環境によっては FILTER が使えないことがあります（社内PCのバージョンが揃っていない、ファイルを他部署へ回す、など）。その場合は、SUMIFS（合計）とCOUNTIFS（件数）で「合計÷件数」を作る方法が堅実です。

OR条件（ITまたはHR）は、部署ごとに合計と件数を出して最後に足し合わせます。

=LET(
  Avg, AVERAGE(D2:D10),

  SumIT, SUMIFS(D2:D10, B2:B10, "IT", C2:C10, ">"&DATE(2020,1,1), D2:D10, ">"&Avg),
  CntIT, COUNTIFS(B2:B10, "IT", C2:C10, ">"&DATE(2020,1,1), D2:D10, ">"&Avg),

  SumHR, SUMIFS(D2:D10, B2:B10, "HR", C2:C10, ">"&DATE(2020,1,1), D2:D10, ">"&Avg),
  CntHR, COUNTIFS(B2:B10, "HR", C2:C10, ">"&DATE(2020,1,1), D2:D10, ">"&Avg),

  IFERROR( (SumIT+SumHR)/(CntIT+CntHR), "該当者なし" )
)

この方法は「抜き出しリスト」が見えない分、検算しづらいのが弱点です。迷ったら、まずLET＋FILTERで対象者を可視化してロジックを固め、配布用にSUMIFS版も用意する…という二段構えが現場で使いやすいです。

もう一つの選択肢：SUMPRODUCTでマスクして平均

条件を満たす行を 1/0 のマスク（重み）にして、

• 分子：給与×マスク の合計
• 分母：マスク の合計（=件数）

で平均を作るのが SUMPRODUCT の王道です。動的配列がなくても動くケースが多く、1セル完結で書けます。

=LET(
  Avg, AVERAGE(D2:D10),
  M, ((B2:B10="IT")+(B2:B10="HR"))*(C2:C10>DATE(2020,1,1))*(D2:D10>Avg),
  IFERROR( SUMPRODUCT(D2:D10*M)/SUMPRODUCT(M), "該当者なし" )
)

SUMPRODUCTは配列計算の「何でも屋」ですが、範囲が大きいと重くなりやすいのも事実です。データ量が多いときは、テーブル化＋必要範囲だけ参照、あるいはPower Queryやピボットへの寄せ方も検討すると安定します。

よくあるハマりどころ（ここで崩れると結果がズレます）

入社日が「文字列」になっている

見た目は日付でも、実体が文字列だと >DATE(2020,1,1) の比較が正しく動きません。特にCSV取込や他システム出力のデータで起こりがちです。

• セルの表示形式だけ変えても、実体が文字列のままなことがあります
• 簡単な判定：=ISNUMBER(C2) が TRUE なら日付（シリアル値）、FALSE なら文字列の可能性

変換方法としては、区切り位置（テキストを列に分割）で日付に変換するのが手早いです。関数で変換するなら DATEVALUE を使い、必要に応じて列全体を置換（値貼り付け）してから集計すると事故が減ります。

給与が数値ではなく、文字列や「￥」付きになっている

給与列に文字が混ざると、AVERAGE 自体は無視してくれる場合もありますが、D2:D10>Avg の比較で #VALUE! になって式全体が壊れることがあります。

よくある原因と対策をまとめます。

原因	例	対策
数値が文字列	「580000」（左寄せ）	区切り位置、VALUE、先頭にアポストロフィを消す等で数値化
通貨記号やカンマが文字として含まれる	「￥580,000」	SUBSTITUTEで記号を除去してVALUE、またはNUMBERVALUE
欠損を「-」で表現	「-」	空白に統一するか、集計前に0/空白へ変換してルール化

条件範囲のサイズが揃っていない

FILTER・SUMPRODUCTは、参照する配列のサイズが揃っていないとエラーになります。例えば B2:B100 なのに D2:D10 になっている、といったケアレスミスが典型です。式を組むときは、まず「どの行からどの行までがデータか」を固定し、同じ行数で揃えてから条件を足していくと安全です。

「2020/1/1より後」と「2020/1/1以降」を混同する

日付条件は、要件定義で食い違いが起きやすいポイントです。

言い方	条件式	2020/1/1入社は？
より後	>DATE(2020,1,1)	対象外
以降	>=DATE(2020,1,1)	対象

「1日違い」で対象者が変わるので、式を渡す前に関係者と表現を揃えておくのがおすすめです。

応用：コピペで使える“実務向け”パターン

部署のOR条件を増やす（IT/HR/総務…）

部署が2つなら (="IT")+(="HR") が簡単ですが、3つ以上に増えると式が伸びます。その場合は MATCH を使うとスッキリします。

=LET(
  Avg, AVERAGE(D2:D10),
  DeptOK, ISNUMBER(MATCH(B2:B10, {"IT","HR","総務"}, 0)),
  Flt, FILTER(D2:D10, DeptOK*(C2:C10>DATE(2020,1,1))*(D2:D10>Avg)),
  IFERROR(AVERAGE(Flt), "該当者なし")
)

部署リストを別セル範囲にしておけば、組織改編があってもリストを書き換えるだけで済みます。

条件をセル参照にして、運用しやすくする

日付の基準や部署名を式に直書きすると、後から変更が入ったときに「どのセルの式を直すのか」が分かりにくくなります。例えば、

• 基準日：H2セル（例：2020/1/1）
• 部署1：H3セル（IT）
• 部署2：H4セル（HR）

のように置いておき、式では参照するだけにすると管理が楽になります。

=LET(
  Avg, AVERAGE(D2:D10),
  Flt, FILTER(
    D2:D10,
    ((B2:B10=H3)+(B2:B10=H4))*(C2:C10>H2)*(D2:D10>Avg)
  ),
  IFERROR(AVERAGE(Flt), "該当者なし")
)

平均ではなく「中央値」や「上位n件平均」にしたい

給与は極端な値の影響を受けやすいので、「平均」だけでなく中央値や上位平均を併用すると、意思決定がぶれにくくなります。

• 中央値：MEDIAN(Flt)
• 上位5件平均：AVERAGE(TAKE(SORT(Flt,,-1),5))（動的配列が使える環境向け）

LETで Flt を作っておけば、「何を対象にするか」は共通のまま、集計指標だけを差し替えられます。

まとめ：最短はLET＋FILTER、配布するなら代替案もセットで

• 補助列なしで「部署（OR）×入社日×全社平均超え」の平均給与を出すなら、LET＋FILTER→AVERAGE が読みやすくて強い
• 該当者ゼロでエラーになるので、実運用では IFERROR で必ずガードする
• データが増える前提なら、テーブル化＋構造化参照で範囲修正の事故を防ぐ
• FILTERが使えない環境には、SUMIFS/COUNTIFS や SUMPRODUCT の代替式が有効

一度「対象者を抜き出して確認できる式」を作っておくと、要件変更（部署追加、基準日変更、平均→中央値など）にも強く、給与データの集計が“属人化しない”形で回るようになります。