エクセル・区間を含む複数条件から該当する値を返す。お助けください。

質問

エクセルの質問です。いろいろ関数を試したり、Ｑ＆Ａを調べても判りませんでしたのでお助けください。体力テストの結果について複数条件から、高い5、やや高い4、普通3、やや低い2、低い1と５段の評価値を返したいのです。sheet1での入力が、(1)性別（２択）(2)年齢(40才から85才以上まで５歳刻み・10択）、(3)テスト数値、以上を入力すると自動で、(4)評価値を同行セルに返したいのです。sheet2には試行的に参照する評価表を作成し、まず(1)(2)については例えば、男性(1)、43歳(40～44歳)なら140という条件値(計20択)に変換しながら試みています。しかし(3)測定値が、例えばの長座位体前屈というテストでは40～44歳男では、～-5cm→1、-4.9～5cm→2、5.1～10cm→3、10.1～20cm→4、20.1cm～→5というように区間数値から評価値を求めなくてはいけません。性別・年齢区分ごとに評価値が変わるのでややこしく、他にも同様に多種類のテストをします。評価表をにらみながらの手作業入力がたいへんなので何とか自動化できればと考えています。VLOOKUPやDGETやIF等あれこれ試しましたが、(1)(2)＋(3)測定値→(4)評価値への変換ができず不可能かとあきらめ気分です。お忙しいところ恐縮ですが、解決へのヒントをお与えください。もし可能ならば初中級レベルなので例文も交えて説明いただけると幸いです。

sheet1
　　A　　　B　　　C　　 　D
1 性別　年齢　テスト値　評価値
2　男　　43　　　 9　　　3　　　
3　女　　68　　 -2　　　 1

sheet2
　　 A　　　　B　　　　　C　　 　
1 性別年齢　テスト値　評価値
2　140　　　　-5　　　1
3　140　　　　 5　　　2
4　140　　　　10　　　3
：　：　　　：　　　：

回答No.5

_Kyle · ベストアンサー

横から失礼します。

例えば、下記参考画像のような形で参照表を用意すれば、

　=MATCH(C2,INDEX(OFFSET($G$3:$K$12,,IF(A2="男",0,5)),MATCH(B2,$F$3:$F$12,1),),1)

で評価値を求めることができます。
---------------------------------------------
・縦軸は年齢
　　「○歳以上□歳未満」の「○」の部分に相当する値を若い順に記入。

・横軸は評価値
　　左から1，2，3，…

・データ部分は境界値
　　「●以上■未満」の「●」の部分に相当する値を数字が小さい順に記入。
　　※「評価１」の欄は●に該当する値がないので「十分に小さい値」（例では-99.9）を記入する。

---------------------------------------------
蛇足

・IF(A2="男",0,5)　
　　⇒　A列が"男"ならば0を、"男"でなければ5を返す。

・OFFSET($G$3:$K$12,,【男性なら0，女性なら5】)
　　⇒　性別に応じた表を返す。
　　　　※男性　⇒　G3:K12　、女性　⇒　（５列右にずらして）L3:P12

・MATCH(B2,$F$3:$F$12,1)
　　⇒　年齢欄(F3:F12)をMATCHで１型検索して、
　　　　年齢に該当する行位置を返す。
　　　　※47歳　⇒　（見出し含めず２行目なので）　2

・INDEX(【性別に応じた表】,【年齢に応じた行位置】,)
　　⇒　【性別に応じた表】から【性別・年齢に応じた行】を抜き出して返す。
　　　　※42歳女性　⇒　L3:P3

・=MATCH(C2,【性別・年齢に応じた行】,1)
　　⇒　【性別・年齢に応じた行】をMATCH１型検索して、
　　　　テスト値に該当する列位置（評価値）を返す。
　　　　※42歳女性15　⇒　（左から４セル目がヒットするので）　4
---------------------------------------------
以上ご参考まで。

お礼

この度はお世話になります。判りやすく図表も交えたご親切なご指導に感謝いたします。この数年、手作業で判定していましたので、今回の自動入力が完成できれば、ほんとうに楽になります。これで私以外の者にも引き継ぐこともできると喜んでおります。_kyle様のご回答の方法が、多数の参照表を作るにあたり、簡便で利用しやすいので使わせていただきたいと考えています。またOFFSETという関数を学ぶこともでき、とても勉強になりました。
　今回、はじめての「教えて！」の投稿で、そんなんも判らんのか！という回答がくるのではと不安でしたが、とてもよい方法をご伝授していただき感謝しております。_kyle様はじめ、ご親切な方に恵まれありがたく思います。誠にありがとうございました。

回答No.6

tom04

毎晩失礼します！m(__)m
私も一生懸命No.5さんのようなきれいな回答を考えていましたが、
いまだに解決していません。
最後に↓のような表を考えてみました。
参考になればいいのですが・・・
ちなみに、E2セルの数式は
=IF(OR(A2="",B2="",C2=""),"",IF(B2="男",INDEX($I$3:$AC$12,MATCH(C2,$H$3:$H$12,-1),MATCH(D2,$I$2:$AC$2,-1)),INDEX($I$16:$AD$25,MATCH(C2,$H$16:$H$25,-1),MATCH(D2,$I$15:$AD$15,-1))))
とかなり長いものになっていますので、この式をコピー＆ペーストしてみてください。
ちなみに、年齢の109歳というのは、ExcelのDATEDIF関数を使った場合に計算できる最大の年数にしてみました。
実際はこの年齢での測定はないかもしれませんが・・・
以上！何度も何度も!失礼しました。m(__)m

お礼

こんにちは。ご連絡遅くなりました。No.3のアドバイスを受け、自分なりに工夫しながら他の表も作成して検証しておりました。tom04様と同様の表と計算式をつくることができ、ご指導のおかげで希望の値を返すことができ、大感激でした。エクセルに詳しい知人に相談しても解決できず、あきらめかけていたので、今回のアドバイスがとてもうれしかったです。
　tom04様には何度も表を修正していただき、未入力を表示させない工夫など、痒い所に手が届くような細かなご配慮、多大の時間を費やしていただき、ほんとうに感謝しております。ありがとうございました。
　今回のことでINDEXとMATCH関数の組み合わせという使い方を学ばせていただき、とても勉強になりました。
　このあと、他にも多数の参照表を作る関係上、No.5さんの簡便な方法を使わせていただこうと考えており、tom04様にはほんとうに申し訳ないと思っています。アドバイスいただいた未入力を表示させない工夫なども交えながら完成させたいと思っています。
　何よりも迅速に誠実に回答していただいたご親切は忘れません。ほんとうにありがとうございました。

回答No.4

tom04

たびたびごめんなさい！
No.3です
先ほどの表では86歳以上のデータがありません。
それから年齢の境界を間違っているような気がします。
例えば、「70代>」という意味は70歳を超えるではなく、70歳以上ということですよね？

もしそうであれば、年齢の欄の数値　71　→　69　と言うようにすべて訂正してください。
つまり表示されている数値までの範囲がその行・列に該当します。
この辺は適当にアレンジお願いします。
それからもう一つ・・・
空白の欄に「1」を入れておかないと、万一空白欄に対応する方がおられた場合、
「0」が表示されると思います。
どうも何度も失礼しました。m(__)m

回答No.3

tom04

こんばんは！
前回は質問内容を取り違えていたようで
ごめんなさい！m(__)m

テスト値の範囲が年齢によって変化するということなのですね？
色々頭を悩まし関数を組み合わせてみましたが、
結局いい案は浮かびませんでした！
そこで無理矢理って感じもしますが、↓の画像のようにしてみました。
男性の場合のみの表をSheet2に作成したものです。

Sheet1のE2セルに
=IF(OR(A2="",B2="",C2="",D2=""),"",IF(B2="男",IF(D2>20,5,INDEX(Sheet2!$C$2:$W$11,MATCH(C2,Sheet2!$B$2:$B$11,-1),MATCH(D2,Sheet2!$C$1:$W$1,-1)))))
この数式をコピーして貼り付けてみてください。
たぶん要望に近いものが出来るのでは？

女性の場合も同じようにSheet3などに作成して、
数式をIF関数の「偽」のところに組み合わせれば、Sheet1のデータが
男女混合でもオートフィルで対応できるかと思います。
もし、これまた的外れならごめんなさいね！m(__)m

回答No.2

tom04

こんばんは！
Sheet2にあるように性別年齢を140という様な数値にしないといけないのでしょうか？
実際の年齢とテスト値を入力するだけで評価値を表示できるようにする方法はあると思います。
INDEX関数とMATCH関数の併用です。
一例ですが・・・
男性・女性別々に評価値の表を作っておく必要がありますが、
男性の場合で回答します。

まず、評価値を↓のように別Sheetに作成しておきます。
今回はSheet2に作成した場合です。
この場合年齢・テスト値両方とも降順に並べておかなければいけません。

　　　　A　　B　　C　　D　　E　　F
　　年齢 　>20　　20 　10　　5　　-5
1　　>84
2　　84
3 　 79
4 　 74
5 　 69
6 　 64
7 　 59
8 　 54
9 　 49
10 　 44 　　 5 　 4 　 3 　 2 　 1
11 　 39
(質問内容の43歳・テスト値=9　というデータしか入力していません）
として、Sheet1は
A　　　　B　　　　C　　　　D
1　性別 年齢 テスト値 評価値
2　男　 　43 　　　9 　　　
3　女 　　68 　　　-2
となっている場合、評価値をD2セルに表示させるとします。
D2セル==IF(OR(B2>84,C2>20),Sheet2!B2,INDEX(Sheet2!C3:F12,MATCH(B2,Sheet2!A3:A12,-1),MATCH(C2,Sheet2!C1:F1,-1)))
これでなんとか希望通りの表示になるのではないでしょうか？
尚、女性の場合は同じように女性用のデータから数式を入れなければなりません。
元データ表の　年齢が84を超えた場合や、テスト値が20を超えた場合は
元データ表のB2セルを表示させるようにしています。
ちなみに、表の説明として
年齢44の行は　39<年齢≦44　の範囲がこの行を参照し、
テスト値9の列は　5<テスト値≦10　の範囲の列を参照します。
すなわち両方のデータが交差する D11セルのデータ「3」が表示されるということです。
以上、参考になったでしょうか？
どうも長々と済みませんでした。
的外れの回答なら読み流してください。m(__)m

お礼

ご指導ありがとうございます。すっきりとした形にできるかと思ったのですが、まだ躓いております。
　ご提示頂いたSheet2の男性の表では１行目の測定値が固定されていて、年齢区分ごとに評価値（得点）の方が変動して返すことになりそうです。でも私の希望は、評価値が固定される必要があり、測定値の範囲の方が変動いたします。（5,4,3,2,1が１行目に固定されていて、それが返ればいいのですが・・）
ちなみに男性の測定値区分は40代>20,20,10,5,-5、45代>18,18,10,5,-5、50代>18,18,8,4,-5、55代>16,16,6,2,-5、60代>16,16,5,0,-9、
65代>15,15,3,-1,-10、70代>15,15,2,-2,-11、75代>12,12,0,-4,-13、80代>12,12,0,-4,-13、85代>10,10,0,-4,-13 です。
　女性の測定値区分は40代>23,23,13,10,4、45代>23,23,13,10,4、50代>23,23,13,9,2、55代>23,23,12,9,3、60代>22,22,12,8,1、
65代>21,21,10,6,-1、70代>20,20,9,5,-3、75代>19,19,8,4,-3、80代>18,18,8,5,-3、85代>17,17,7,3,-5　（長座位体前屈・単位cm）となっています。
　あとまだ次の段階の表の切替えまでは考えられていませんが、今のままでは、性別に関係なくSheet2の値を返しまから、男性表か女性表かの参照の切替えは、Sheet1のA列に入力した値をIF関数？等で判断するように、D2の式に組み込めばいいということなのでしょうね。

　もし、ご指導いただいた内容に反して、私の解釈が誤まっていましたらすみません。ご容赦ください。もし解決方法がございましたら、再度ご指導いただけると幸いです。お手数をおかけして申し訳ありませんでした。ありがとうございました。

補足

早々のご指導をありがとうございます。特に140とする必要はありません。丁寧な図解を添えていただき、感激です！早速、熟読し試してみます。まずはお礼申し上げます。ありがとうございます！

回答No.1

syuyama

同じテスト値でも、性別や年齢によって評価値は変わるのですか？

質問文の例題では、
～-5cm→1、-4.9～5cm→2、5.1～10cm→3、10.1～20cm→4、20.1cm～→5というように

と書いていましたが、これは40～44歳男の場合であって、
年齢が変われば同じテスト値でも評価値は変わってくるということですか？

テスト内容・性別年齢ごとのテスト値に対応する評価値の一覧表を作成すれば関数だけでうまく作れそうな気がします。

ですので補足要求いたします。

補足

さっそくの回答ありがとうございます。おっしゃるとおり性別と年齢区分が変わると数値がかわります。例は40～44歳男でしたが、45～49歳男では低い～普通は同じ値ですが、やや高い(4)が10.1～18cm、高い(5)が18.1cm～となります。５歳刻みで異なっています。ちなみに女性では40～44歳と45～49歳は同じで、(1)～4cm、(2)4.1～10cm、(3)10.1～13cm、(4)13.1～23cm、(5)23.2cm～となっています。でも50歳代、55歳代は異なっています。
　性別２択×年齢区分10択×５段階評価　100択！からの選択になりますが、区間の処理で行き詰っています。どうぞよろしくご指導お願いします。