条件付き集計関数の「後方一致検索」(～で終わる)の設定方法
下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「criteria」)の設定方法は以下の通りとなります。
例はCOUNTIF関数で提示していますが、他の条件付き集計関数でも引数「条件/検索条件」の取り扱いは方法同じです(SUMIF/AVERAGEIF/COUNTIFS/SUMIFS/AVERAGEIFS/MAXIFS/MINIFS)。
COUNTIF関数の書式は「=COUNTIF(範囲,検索条件)」です。
設定概要
「(条件)範囲」内が「Aで終わる」というセル・行・列を検索して集計対象とする場合のルールは以下の通りとなります(後方一致検索)。
「ワイルドカード」という特殊記号を使って検索を実現します。「＊」と「?」がメインのワイルドカード記号です。「~」はサブのワイルドカード記号です。
「使用例」は下記のサンプル表を用いての例です。「A」は『文字データ』とします。
| 名称 | 後方一致検索 |
|---|---|
| 意味：集計対象 | Aで終わる |
| (検索)条件の ルール | *には何文字が入っても可とする。 ?は1文字ならば何が入っても可とする。 「*?」はワイルドカードとよぶ。 「*?」のワイルドカード効果を削除するならば「~」を記号前に付ける。 セル参照以外は「”」で囲み文字列とする。 文字列とセル参照が混在している際はそれぞれを「&」でつなぐ。 |
| 使用例・定数 | =COUNTIF(B2:B28,”*入門”) |
| 使用例・セル参照 | =COUNTIF(B2:B28,”*”&H5) |
| 式の結果(下表) | 4 |
使用例・定数
「(条件)範囲」である【B2:B28】内に、「(検索)条件」で指定した【入門】という文字データで終わるデータが入力されているセル・行・列を集計対象とする場合は以下のような計算式になります。【入門】の前は「何文字でもどんな文字でも可」の意味である(*)を設置します。この「どんな文字でも可」の役割を果たす特殊記号を「ワイルドカード」とよびます。
=COUNTIF(B2:B28,"*入門")
「(条件)範囲」内が「(検索)条件」で【指定した文字で終わる】セルを集計対象とします。下のサンプル表に対して用いると「4」が返ります。
どんな文字でも可」の役割を果たす特殊記号である「ワイルドカード」には「*」のほかに1文字分のワイルドカードを意味する「?」があります。
下記の式ならば「入門」の前に何らかの2文字があるセルを集計対象とします。
=COUNTIF(B2:B28,"??入門")
また「*?」をワイルドカードとしてではなく単独の文字として使用したい場合は前に「~」を付けます。
下記の式ならば「?」で終わるデータがあるセルを集計対象とします。
=COUNTIF(B2:B28,"*~?")
使用例・参照
「(条件)範囲」である【B2:B28】内に、「(検索)条件」で指定した【H5】の文字列で終わるデータが入力されているセル・行・列を集計対象とする場合は以下のような計算式になります。
=COUNTIF(B2:B28,"*"&H5)
「(条件)範囲」内に「”*” & 参照セル名」を指定します。*と参照セル名とを&で結合するのです。*は文字列として処理させるので「”」で囲みます。
「(検索)条件」となるセル【H5】は「入門」です。「(条件)範囲」内で【H5】の文字列で終わるセルを集計対象とします。下のサンプル表に対して用いると「4」が返ります。
ワイルドカード・まとめ
| 記号 | 読み | 意味 | 使用例 |
|---|---|---|---|
| * | アスタリスク | 任意の文字列 (文字数指定なし) | =COUNTIF(A1:A10,”apple*”) (appleで終わる任意の文字列を持つセルを数える) |
| ? | クエスチョン マーク | 任意の1文字 | =COUNTIF(A1:A10,”a?ple”) (aとpleの間に任意の1文字があるセルを数える) |
| ~ | チルダ | ワイルドカード文字 そのものを指定 | =COUNTIF(A1:A10,”~*apple”) (*appleという文字列を持つセルを数える) |
使用例・サンプル表
左上がセルA1となります。※「=””」はセルには表示されません。
| 書籍番号 | 書名 | 発刊日 | 金額 | 在庫 | 条件1 | 会社法 | |
| 1001 | ケーススタディ民法 | 2016/10/28 | 1400 | 有 | 条件2 | 1400 | |
| 1002 | 交通六法 | 2016/02/27 | 2700 | 無 | 条件3 | 2014/04/27 | |
| 1003 | 法人税法の基礎 | 2015/12/27 | 2700 | 無 | 条件4 | 1200 | |
| 1004 | 会社法 | 2014/10/27 | 2800 | 無 | 条件5 | 入門 | |
| 1005 | 民法総則 | 2015/03/27 | 2700 | 条件6 | 税 | ||
| 1006 | 判例六法 | 2016/01/27 | 2400 | 有 | 条件7 | ||
| 1007 | 基礎法入門 | 2014/09/27 | 2500 | 有 | 条件8※ | ||
| 1008 | やさしい経済法 | 2015/08/27 | 2500 | 無 | 条件9 | <> | |
| 1009 | 刑法各論 | 2016/07/28 | 2100 | ||||
| 1010 | 現近代民法 | 2014/10/27 | 1400 | 有 | |||
| 1011 | 会社法概論 | 2014/04/27 | 2000 | 有 | |||
| 1012 | 商法総則 | 2014/04/27 | 1900 | 有 | |||
| 1013 | 憲法 | 2016/09/28 | 2500 | 無 | |||
| 1014 | 商法わかるかな? | 2015/11/27 | 1600 | 有 | |||
| 1015 | 倒産法入門 | 2014/01/27 | 2300 | 無 | |||
| 1016 | 破産法 | 2015/02/27 | 1200 | 有 | |||
| 1017 | 法社会学 | 2016/10/28 | 1500 | 無 | |||
| 1018 | 民事訴訟法学 | 2016/08/28 | 2800 | ||||
| 1019 | 法学入門 | 2015/09/27 | 2500 | 有 | |||
| 1020 | 労働法の要点 | 2015/05/27 | 2400 | 無 | |||
| 1021 | ***はやわかり刑法 | 2016/05/28 | 3000 | 無 | |||
| 1022 | 講義式憲法 | 2016/10/28 | 3000 | 有 | |||
| 1023 | 刑法講義 | 2016/05/28 | 2600 | 有 | |||
| 1024 | 会社法入門 | 2016/09/27 | 1300 | 無 | |||
| 1025 | 民法事例集 | 2015/06/27 | 1200 | 無 | |||
| 1026 | 六法全書 | 2014/03/27 | 2300 | 無 | |||
| 1027 | 労働法実務辞典 | 2014/09/27 | 2700 | 有 |
Excelの条件付き集計関数：一覧
このページの説明は下記の「Excel 条件付き集計関数」の引数「条件/検索条件」(criteria)で共通となります。
「条件付き集計関数」の「条件」や「検索条件」という引数は、英語版Excelでは共に「criteria」として表されます。これは特定の条件を設定して、その条件を満たすデータを集計対象とするために用いられる引数です。例えば、特定の値が閾値を超えるかどうかを基準にデータを集計する場合などに利用します。この「条件」を利用することで、大規模なデータセットから必要なデータだけを効率的に抽出して集計を行うことが可能になります。
コメント