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

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

[Excel 2016/2013/2010/2007]

SUMIF関数AVERAGEIF関数COUNTIF関数を使用すると、表の中から条件に合うデータを探して合計、平均、データ数を求められます。ここでは、「ワイルドカード」と呼ばれる記号を使用して、「○○を含む」「○○を含まない」「○○で始まる」「○○で終わる」のようなあいまいな条件を指定する方法を紹介します。

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

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

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

スポンサーリンク

作例…商品名に「エアコンを含む」商品のデータを集計する

図1の売上表の「商品名」欄(セルA3~A9)から「エアコンを含む」商品を検索して、「売上高」欄(セルB3~B9)の数値の合計と平均、およびデータ数を求めます。

「エアコンを含む」という条件を指定するには、引数[検索条件]に「"*エアコン*"」と指定します。「エアコン」がセルE2に入力されている場合は、「"*" & E2 & "*"」と指定します。「*」は、任意の文字列を表すワイルドカードです。

セルE6 | =SUMIF(A3:A9,"*" & E2 & "*",B3:B9)

セルE7 | =AVERAGEIF(A3:A9,"*" & E2 & "*",B3:B9)

セルE8 | =COUNTIF(A3:A9,"*" & E2 & "*")

図1 商品名に「『エアコン』を含む」商品を集計する。

条件の指定例

図1では「『エアコン』を含む」という条件を指定しましたが、「*」の使い方に応じて「始まる」「終わる」など、さまざまな条件を指定できます。

条件 直接指定 セル参照指定 該当データ
「エアコン」で始まる "エアコン*" E2 & "*" エアコン、エアコンフィルター
「エアコン」で終わる "*エアコン" "*" & E2 エアコン、マルチエアコン、業務用エアコン
「エアコン」を含む "*エアコン*" "*" & E2 & "*" エアコン、マルチエアコン、業務用エアコン、エアコンフィルター、マルチエアコン室外機
「エアコン」を含まない "<>*エアコン*" "<>*" & E2 & "*" 室外機、配管化粧カバー

ワイルドカードの種類と使用例

ワイルドカードには、「*」のほかにも下表の種類があります。いずれも使用するときは半角で入力してください。「~」(チルダ)は、一般的なキーボードでは、半角英数モードで[Shift]キーを押しながら[^]キー(ひらがなの「へ」のキー)を押すと入力できます。

ワイルドカード 説明
*(アスタリスク) 0文字以上の任意の文字列
?(クエスチョンマーク) 任意の1文字
~(チルダ) 「*」「?」の前に付けて「*」「?」を検索

文字数制限のない「*」と1文字を表す「?」を組み合わせることで、さまざまな条件を指定できます。例えば、「?*」は「1文字以上の任意の文字列」、「??*」は「2文字以上の任意の文字列」を表します。

下表は、ワイルドカード「*」と「?」を使用して、8つの苗字

 , 岡,川原, 森, 加賀, 天王寺, 長部, 長

を検索した場合の例です。

使用例 意味 該当データ
"*谷*" 「谷」を含む 8件 , 岡, 川原, 森, 加賀, 天王寺, 長部, 長
"?谷?"  1文字+「谷」+1文字 2件 長部, 長
"?谷*"  1文字+「谷」+0文字以上 3件 森, 長部, 長
"谷*" 「谷」で始まる 3件 , 岡, 川原(1文字の「谷」はヒットする)
"谷?" 「谷」+1文字 1件 
"谷?*" 「谷」+1文字以上 2件 岡, 川原
"*谷" 「谷」で終わる 4件 , 森, 加賀, 天王寺(1文字の「谷」はヒットする)
"?谷"  1文字+「谷」 1件 森 
"?*谷"  1文字以上+「谷」 3件 森, 加賀, 天王寺
"??*谷"  2文字以上+「谷」 2件 加賀, 天王寺

「~」は「*」「?」を検索したいときに使用します。

使用例 説明
" ~*"  「*」を検索する
"~?"  「?」を検索する
" *~**"  「*」を含む文字列を検索する
"*~?*"   「?」を含む文字列を検索する
スポンサーリンク

関連記事

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