Aさん SUMIFSやCOUNTIFSは使えるようになりましたが、集計の条件を「○○を含む」のようにできるともっと便利なんですが。
こうした条件に対応した関数はありますか？
森田 関数自体はSUMIFSやCOUNTIFSで大丈夫ですが、条件の指定の仕方を工夫すれば対応できます！
では、詳細を解説していきますね。
はじめに
この記事はSUMIFS・COUNTIFSの使い方を把握していることが前提です。
SUMIFS・COUNTIFSの使い方については以下の記事をご参照ください。
はじめに この記事は関数の概要とSUMの使い方を把握していることが前提です。 参考記事 関数の概要とSUMの使 …
はじめに この記事は関数の概要とCOUNTAの使い方を把握していることが前提です。 参考記事 関数の概要とCO …
SUMIFSやCOUNTIFSの集計条件は原則「完全一致」
SUMIFSやCOUNTIFSの難点は、「条件と完全一致」が原則であることです。
テキスト/数値/日付フィルターで指定可能な条件と比べると、バリエーションが少ないことが分かります。
SUMIFSやCOUNTIFSは「指定の値と等しい」がデフォルト
もし、完全一致（=指定の値と等しい）以外の条件で集計したい場合は、「ワイルドカード」と「比較演算子」を活用すればOKです。
テキストフィルター同様の条件は「ワイルドカード」を使う
まず、テキストフィルターと同じような条件で集計したい場合の方法から解説していきましょう。
テキストフィルター同様の条件とは、具体的には以下の通りです。
- （指定の値）で始まる
- （指定の値）で終わる
- （指定の値）を含む
- （指定の値）を含まない
試しに、「フルーツ」で終わる商品名が何レコードあるかをCOUNTIFSでカウントしてみましょう。
このテクニックのポイントは、条件となる引数部分を「“*”&I2」のようにアスタリスク（*）を活用していることです。
このアスタリスク（*）は「ワイルドカード」と呼ばれ、「任意の文字列の代わり」となる記号のことを指します。
ワイルドカードは、トランプでいうジョーカーのように、「万能な文字」と思ってください。
このワイルドカードは3種類あります。
- *：任意の文字列の代わりになる（文字数制限なし）
- ?：任意の文字列の代わりになる（1文字単位）
- ~：「*」や「?」を検索する
ワイルドカードで代わりにしたい文字数が決まっている場合は「?」を使いましょう。（例：2文字の場合→「??」）
【参考】テキストフィルター同様の条件の数式例
テキストフィルター同様の条件を数式で表現する際、以下のようなパターンとなります。
| 条件 | 指定の値を直接入力する場合 | 指定の値が入力されたセルを参照する場合 ※例：A1セル |
|---|---|---|
| （指定の値）で始まる | “指定の値*” | A1&”*” |
| （指定の値）で終わる | “*指定の値” | ”*”&A1 |
| （指定の値）を含む | “*指定の値*” | ”*”&A1&”*” |
| （指定の値）を含まない | “<>*指定の値*” | ”<>*”&A1&”*” |
ワイルドカードを1文字単位にしたい場合は「*」を「?」に置き換えてください。
「<>」の部分の詳細は「数値/日付フィルター同様の条件は「比較演算子」を使う」をご参照ください。
上記を実際にExcelで使ってみたイメージは以下をご覧ください。
セルの値がワイルドカード込みで入力されている場合、そのセルを参照するのみ（通常のSUMIFS・COUNTIFSの使い方）で上記と同じ効果を得られます。
数値/日付フィルター同様の条件は「比較演算子」を使う
続いて、数値/日付フィルターと同じような条件で集計したい場合の方法です。
数値/日付フィルター同様の条件とは、具体的には以下の通りです。
- （指定の値）に等しくない
- （指定の値）より大きい ※より後
- （指定の値）以上 ※以降
- （指定の値）より小さい ※より前
- （指定の値）以下 ※以前
- （指定の）範囲内
「指定の値に等しくない」はテキストフィルター上にもあります。
試しに、「3000」以上の金額が何レコードあるかをCOUNTIFSでカウントしてみましょう。
このテクニックのポイントは、条件となる引数部分を「“>=”&I4」のように等号（=）や不等号（>）を活用していることです。
この等号・不等号は「比較演算子」と呼ばれ、値の大小を比較する場合に使用します。
【参考】数値/日付フィルター同様の条件の数式例
数値/日付フィルター同様の条件を数式で表現する際、以下のようなパターンとなります。
| 条件 | 指定の値を直接入力する場合 | 指定の値が入力されたセルを参照する場合 ※例：A1セル |
|---|---|---|
| （指定の値）に等しくない | “<>指定の値” | “<>”&A1 |
| （指定の値）より大きい ※より後 | “>指定の値” | ”>”&A1 |
| （指定の値）以上 ※以降 | “>=指定の値” | ”>=”&A1 |
| （指定の値）より小さい ※より前 | “<指定の値” | ”<”&A1 |
| （指定の値）以下 ※以前 | “<=指定の値” | ”<=”&A1 |
「範囲内」は上記「以上」と「以下」を組み合わせることで実現できます。
「以上」と「以下」は不等号を前にしないとエラーになるため注意してください。
上記を実際にExcelで使ってみたイメージは以下をご覧ください。
セルの値が比較演算子込みで入力されている場合、そのセルを参照するのみ（通常のSUMIFS・COUNTIFSの使い方）で上記と同じ効果を得られます。
【参考】OR条件での集計するには
SUMIFSやCOUNTIFSは複数条件を指定できますが、基本的にAND条件となります。
もし、OR条件で集計したい場合は、複数のSUMIFS・COUNTIFSを加算（+）することがもっともお手軽です。
一例として、商品名が「いちご」または「ぶどう」の金額合計を求める場合は以下の通りとなります。
もし、あまりにもOR条件の要素が多い場合は、そもそも元データ側に集計対象かどうかをIF等でフラグを立てておき、それを条件として1つのSUMIFSやCOUNTIFSで集計した方がシンプルな数式になります。
ケースに応じて使い分けてください。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_SUMIFS・COUNTIS応用.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
以下と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
単独でも便利なSUMIFSやCOUNTIFSですが、ワイルドカードや比較演算子を活用することで、より広いケースに対応できるようになります。
また、ワイルドカードは「置換」機能等でも使いますし、比較演算子はIF等の論理関数を用いる際に必須な知識です。
実務でExcelを使いこなすためにも、この2つはしっかりと把握しておくことをおすすめします。
なお、SUMIFSやCOUNTIFS以外にもExcelでのデータ集計の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！
ご参考になれば幸いですm(_ _)m
森田 今回のテクニックはSUMIFSやCOUNTIFSを中心に解説しましたが、類似のSUMIF、COUNTIFでも当然使えます。
知っておくと、元データ側で条件を用意せずに各種条件で集計できるようになるため、地味に便利です。
知らなった方は、ぜひこの機会に学習してみてくださいね！
