SUMIF・AVERAGEIF・COUNTIF関数で「○○でない」を条件にする

投稿日:2017年10月17日 更新日:

[Excel 2016/2013/2010/2007]

SUMIF関数AVERAGEIF関数COUNTIF関数を使用すると、表の中から条件に合うデータを探して合計、平均、データ数を求められます。ここでは、条件として「○○ではない」を指定する方法を紹介します。

[範囲]から[検索条件]を探し、見つかった行の[合計範囲]のデータの合計を求める
=SUMIF(範囲, 検索条件[, 合計範囲])

[範囲]から[条件]を探し、見つかった行の[平均対象範囲]のデータの平均を求める
=AVERAGEIF(範囲, 条件[, 平均対象範囲)

[範囲]から[検索条件]を探してデータ数を求める
=COUNTIF(範囲, 検索条件)

スポンサーリンク

作例…役職が「なし」でない社員のデータを集計する

図1の営業成績表の「役職」欄(セルB3~B9)から「なし」ではないデータを検索して、「契約数」欄(セルC3~C9)の数値の合計と平均、およびデータ数を求めます。

「『なし』でない」という条件を指定するには、引数[検索条件]に「"<>なし"」と指定します。「なし」がセルF2に入力されている場合は、「"<>" & F2」と指定します。

セルF6 | =SUMIF(B3:B9,"<>" & F2,C3:C9)

セルF7 | =AVERAGEIF(B3:B9,"<>" & F2,C3:C9)

セルF8 | =COUNTIF(B3:B9,"<>" & F2)

図1 「『なし』でない」を条件に集計する。

条件の指定例

「○○でない」を条件とするときは、比較演算子「<>」とデータ「○○」を組み合わせて指定します。

条件 直接指定 セル参照指定
「30」でない "<>30" "<>" & F2
「2017/10/1」でない "<>2017/10/1" "<>" & F2
「課長」でない "<>課長" "<>" & F2
「長」を含まない "<>*長*" "<>*" & F2 & "*"

条件判定の対象のセル範囲にある空欄を除外するには

「"<>○○"」という条件では、図2のように未入力のセルも集計の対象になります。

図2 「『なし』でない」を条件にすると、「課長」「主任」のほか、未入力のセルが集計の対象となる。

未入力のセルを無視して、データが入力されているセルの中から「○○でない」データを検索して集計を行うには、複数の条件を指定できるSUMIFS関数AVERAGEIFS関数COUNTIFS関数を使用します。

=SUMIFS(合計対象範囲, 条件範囲1, 条件1[, 条件範囲2, 条件2…])

=AVERAGEIFS(平均対象範囲, 条件範囲1, 条件1[, 条件範囲2, 条件2…])

=COUNTIFS(検索条件範囲1, 検索条件1[,検索条件範囲2, 検索条件2…])

図3のように、「○○でない」かつ「データが入力されている」という条件を指定します。データが入力されていることを示す条件は、「"<>"」です(「SUMIF関数・AVERAGEIF関数・COUNTIF関数で入力済/未入力を条件にする」参照)。

セルF6 | =SUMIFS(C3:C9,B3:B9,"<>" & F2,B3:B9,"<>")

セルF7 | =AVERAGEIFS(C3:C9,B3:B9,"<>" & F2,B3:B9,"<>")

セルF8 | =COUNTIFS(B3:B9,"<>" & F2,B3:B9,"<>")

図3 「『なし』でない、かつ、空欄でない」を条件に集計する。

スポンサーリンク

関連記事

Copyright© Officeのチカラ , 2018 All Rights Reserved.