関数を教えてください

質問

シート１（採点表）
年齢|回数|得点
---------------
|
シート２（データ）
年齢|１|２|３|４　
-----------------------
得点|
100 |
このような表を作成しています.
シート１で回数入力したら、シート２の得点データ（年齢によって配点が違う）からシート１の得点を表示するようにするにはどうしたら良いのでしょうか？
LOOKUPやIFでいろいろ試しましたがどうも上手くいきません。

回答No.4

imogasi · ベストアンサー

補足有難うございます。
この問題は難しいと思いました。まだ解答も出ていないようです。
取りあえず解答を載せますが、条件次第では潰れます。
例を挙げますのでフォローして理解してください。
（１）例としてＡ１からＤ６まで下記表を入力してください。
（Ｓｈｅｅｔ２へ）
点／年 年２０ 年２１ 年２２以下略
100 6 5 4
99 5 4 3
98 4 3 2
97 3 2 1
96 2 1 0　　（計数は回数）
以下略
（２）ここが問題含み（手数がかかる）なんですが。
Ｂ２：Ｂ６に「年２０」という名前をつける。
Ｃ２：Ｃ６に「年２１」という名前をつける。
Ｄ２：Ｄ６に「年２２」という名前をつける。
やり方は範囲指定し,挿入－名前－定義－ＯＫです。
面倒だが本番では全年齢について行うこと。
（３）Ａ９（何処でも良いが仮に）年齢を入れる。数字
　　２２ではなく「年２２」と入れる。
　　（ちょっと常識と違うが本件やむを得ない点です）
（４）Ｂ９（何処でも良いが仮に）に回数「４」（数字）を入　　れる。
（５）Ｂ１１（何処でも良いが仮に）に式「=MATCH(B9,INDIRECT(A9),-1)」を
　　入れる。点数は降順（上から下へ大－＞小）となって
　　入ると仮定（仮定１）。その場合最後の引数は－１です。
（６）Ｂ１２（何処でも良いが仮に）に式「　　=INDEX(A2:A7,B11)」を入れる。
（７）やり方Ａ９セルに「年２０」とか入力。
　　　Ｂ９セルに回数「３」とか入力。
　　　点数がＢ１１に出る。
●質問ではＢ９、Ｂ１１やＢ１２セルはＳｈｅｅｔ１にあるようだがこれはＳｈｅｅｔ２！○○と変えれば良い。解くのに精一杯で、そこまで気を配れなくてすみません。
●（難しいと思った点）
関数はほとんど１つの値を返すものです。例えば２２歳の列Ｄ２：Ｄ６を返す関数があれば良いが、見つけられなかった。
たまたま雑誌の解説に「Indirect(名前）」の例が目にとまり
使いました。
　本件質問の「表」で上記「仮定１」も成り立つかどうか、心配です。また重複値（同回数）があるのかも心配です。
●もっとスマートな解答が出ることを待ちます。

お礼

imogasiさん、懇切な解答ありがとうございました。
こんなに複雑になるなんて想像もしなませんでした。
やってみます！
シート２の得点表のレイアウトにも問題があるのかもしれません。
できましたら報告させていただきます！

回答No.3

imogasi

下記のようなことですか。問題の内容を補足お願いします。
シート１（採点表）
年齢|回数|得点
---------------
入力｜入力｜Ｘ
済？
「回数数字」を入力すると（＜－シート１で回数入力したら、）「シート２」の第１行の年齢を索引
しないと、第２行めの配点？得点？は判らないのだが、その年齢は
何処より採ってくるのか。回数を入力する瞬間には、シート１のＡ列に年齢は入力済みですか？最終目的はシート１のＸのセルに値をセットすることですね。？

シート２（データ）
|１ |２ |３ |４｜ <---年齢--->
-----------------------
100 |95 | 90|85 |・・・・
　　　（年齢別満点表？年齢別得点表？得点となっているが
　　　　配点のこと）年齢別にハンディキャップをつけている
　　　　と言うことですか。
なぜ年齢で満点・配点が異なるのですか。得点ですか？
●配点・得点？は年齢と関係するとして、回数とは関係しないのですか。回数数字を入力は、あくまで本件得点索引の「きっかけ」であると言うことですか。あるいは「回数倍」をして上記「ｘ」のセルにセットするのですか。回数と得点の関係がわからない。

補足

imogasiさん、ありがとうございます。
質問の仕方が悪くてすみません(^_^;)
流れはこうです。
１　シート１で回数と年齢を入力します。
　　氏名|教えて具
　　年齢|21歳
　　回数|4回
　　得点|？
２　シート２で作成した年齢に応ずる点数表から得点を検索します。
　 20歳|21歳|22歳
100点| 6回| 5回|4回
99点| 5回| 4回|3回
98点| 4回| 3回|2回

３　シート１の表に得点を出力します。
※　点数表を別シートにしているのは、表が膨大なためです。

回答No.2

grumpy_the_dwarf

これはvlookup関数で最後のパラメータをTRUEにする数少ない例ですね。

懸垂か反復横とびか知りませんけど、そういう類のことが何回出来るかで年齢に応じた
得点をつけていくわけですが、シート2の表が

年齢＼回数　　　１　　　　　２　　　　　３
　　　０　　　１５　　　　２０　　　　２３
　　１０　　　１０　　　　１５　　　　１０
　　２０　　　　５　　　　１０　　　　１３
　　３０　　　１０　　　　１５　　　　１８
　　４０　　　１５　　　　２０　　　　２２
　　５０　　　２０　　　　２５　　　　２８

こんな感じで、シート1の得点欄を
=vlookup(年齢,sheet2!表,回数+1,TRUE)
というふうにしておけば、各年代の得点が得られるはずです。こいつの弱点は表の回数
の列を1回ずつ全部書かなくちゃいけないところですね。そこで、表の上の部分を

年齢＼回数　　　０　　　　　２　　　　　５
　　　　　　　　２　　　　　３　　　　　４
　　　０　　　　０　　　　１５　　　　２３

のように拡張します。10才未満が2回未満なら0点、2～4回なら15点、というわけですね。
で、得点欄は

=vlookup(年齢,sheet2!表,hlookup(回数,sheet2!表,2,TRUE),TRUE)

とします。これでだいたいOKなはずです。

お礼

grumpy_the_dwarfさん、ありがとうございます。
=vlookup(年齢,sheet2!表,hlookup(回数,sheet2!表,2,TRUE),TRUE)
でチャレンジしてみます

回答No.1

k-nksm

index関数で問題があるのでしょうか？

index(sheet!2得点セル範囲,sheet1!回数セル)
でいいんじゃないでしょうか。

いまひとつ、シート１と２の関係がよくわかりませんが。

お礼

k-nksmさん、ありがとうございました。
index(sheet!2得点セル範囲,sheet1!回数セル) でチャレンジしてみます。