COUNTIFSの検索条件(画像添付の上再質問）

質問

COUNTIFSの複数条件で 男　または　女　の年齢層別の人数を検索するとき
検索条件の設定で
・10歳未満
・10歳以上20歳未満
・20歳以上30歳未満
・30歳以上40歳未満
・40歳以上50歳未満
・50歳以上60歳未満
・60歳以上70歳未満
・70歳以上80歳未満
・80歳以上90歳未満
・90歳以上100歳未満
・100歳以上
のすべてを条件としてドロップダウンリストにして設定するにはどのようにすればいいのでしょうか？
例えば、画像のC19でドロップダウンリストで、30歳以上40歳未満を選択したときの人数をC21に表示させるということです。

回答No.3

bunjii · ベストアンサー

>この設定では、例えば<40としたとき40歳未満から10歳代までのすべての数が反映されてしまいます。
そうならないように提示の数式で条件を定義しています。
=COUNTIFS([条件範囲1],[条件1],[条件範囲2],[条件2],[条件範囲3],[条件3])
条件範囲1と条件1ではC2:C15の範囲から男女の性別を、条件範囲2と条件2ではD2:D15の範囲からC19へ入力された条件の年齢を、条件範囲3と条件3ではD2:D15の範囲からC19に入力された条件を元に〇〇歳以上の条件を算出した結果で絞り込んでいます。
[条件3]=">="&IF(C19=">=100",100,SUBSTITUTE(C19,"<","")-10)
C19に"<40"を代入すると次のようになります。
[条件3]=">="&IF("<40"=">=100",100,SUBSTITUTE("<40","<","")-10)
　　　　↓　SUBSTITUTE関数の結果を置き換える
[条件3]=">="&IF("<40"=">=100",100,40-10)
　　　　↓　IF関数の結果を置き換える
[条件3]=">="&30
　　　　↓　&演算子の結果を置き換える
[条件3]=">=30"
従って、条件2と条件3を合わせると30以上40未満になります。

お礼

詳しく説明していただきありがとうございました。
やっと理解できました。
自分がやりたいことができました。

回答No.2

bunjii

初めに回答No.1の数式について訂正させていただきます。
=COUNTIFS(C2:C15,C18,D2:D15,C19,D2:D15,">="&IF(C19=">=100",100,SUBSTITUTE(C19,"<","")-10))

>C19のドロップダウンリストの作成で、データ入力規則の「元の値(S)」に、・・中略・・のすべてを設定して、選択出来るようにしたいのですが、出来ますか？
「元の値(S)」にカンマ区切りで列記するだけで良いでしょう。（添付画像参照）
またはセル範囲で指定することもできます。

補足

この設定では、例えば<40としたとき40歳未満から10歳代までのすべての数が反映されてしまいます。
そうではなく、各年代別(例：30歳以上から40歳未満 等それぞれの年代)の数を調べるようにしたいのですが、「元の値(S)」の設定方法はどうすればいいでしょうか？

回答No.1

bunjii

>例えば、画像のC19でドロップダウンリストで、30歳以上40歳未満を選択したときの人数をC21に表示させるということです。
C19セルの入力規則のリストでは「未満」を指定しているので添付画像では10歳以上20歳未満を指定していることになります。
=COUNTIFS(C2:C15,C18,D2:D15,C19,D2:D15,">="&SUBSTITUTE(C19,"<","")-10)

補足

C19のドロップダウンリストの作成で、データ入力規則の「元の値(S)」に、
・10歳未満
・10歳以上20歳未満
・20歳以上30歳未満
・30歳以上40歳未満
・40歳以上50歳未満
・50歳以上60歳未満
・60歳以上70歳未満
・70歳以上80歳未満
・80歳以上90歳未満
・90歳以上100歳未満
・100歳以上
のすべてを設定して、選択出来るようにしたいのですが、出来ますか？