EXCELで条件が2つ以上のテーブルから値を選びたい

Question

EXCELで複数条件の表（テーブル）から値を自動的に求める計算式を作りたいと思っています。

言葉で表すのが難しいのですが、具体的には下記の表です。

点　数　（Ｗ５２）　　　　　　　10点　　　　　8点　　　　　　6点　　　　　　4点　　　　　　2点　　　　　　0点

年間平均完成工事高　／　公認会計士等の数値（＝公認会計士等の数×１＋経理試験２級合格者数×0.4）

600億円以上　　　　　　　13.6以上　　10.8以上　　　7.2以上　　　5.2以上　　　2.8以上　　　2.8未満

150億円以上　　　　　　　　8.8以上　　　6.8以上　　　4.8以上　　　2.8以上　　　1.6以上　　　1.6未満

40億円以上　　　　　　　　　4.4以上　　　3.2以上　　　2.4以上　　　1.2以上　　　0.8以上　　　0.8未満

10億円以上　　　　　　　　　2.4以上　　　1.6以上　　　1.2以上　　　0.8以上　　　0.4以上　　　0.4未満

1億円以上　　　　　　　　　　1.2以上　　　0.8以上　　　0.4以上　　　　　-　　　　　　　　-　　　　　　　0　　　　　

1億円未満　　　　　　　　　　0.4以上　　　　　　-　　　　　　　-　　　　　　　-　　　　　　　　-　　　　　　　0

出展：

http://www.ktr.mlit.go.jp/honkyoku/kensei/construction/keisin/content/pdf/tebiki_betten.pdf

PDF36ページ または

http://www.jmenet.com/keishin/score_w.html

(5)-(ii) 項

条件が２つありまして、たとえば、

完成工事高＝150億円以上600億円未満で、数値＝5.2 の場合、点数は6点

完成工事高＝1億円以上10億円未満で、数値＝1.0 の場合、点数は8点

・・・という風にしたいのです。

条件１（完成工事高）は、数値入力でもいいのですが、プルダウンメニューから選ぶようにしようと思っています。

条件２（公認会計士等の数値）は、人数を入力して計算させればいいのでＯＫ。

この２つの条件から、自動的に点数（10点～0点）を選ばせるいい方法は無いでしょうか？

if文でたくさんネストすれば、できないこともなさそうなのですが、

もうちょっとスマートにできれば素敵です。

今考えているのが、条件１の完成工事高６種類について、条件２に応じてそれぞれ点数を計算しておき、右セルに出力しておいて

さらに条件１で、右セルの数値の中から選ぶ、という感じ？うーん言葉で表すのが難しい。

もっといい方法がありましたらどなたかご教授ください。

Answer 1

元データを以下のようなリスト（A2セル以下に完成工事高、B1セルから右に評点を昇順に並べ替える）に変更すれば、該当する評点は以下のような関数で表示することができます（A12セルに「150億円以上」のような文字列で完成工事高を、B12セルに数値が入力されている場合）。

=INDEX($B$1:$G$1,MATCH(B12,OFFSET($A$1,MATCH(A12,$A$2:$A$7,0),1,1,6),1))

	0点	2点	4点	6点	8点	10点
600億円以上	0	2.8	5.2	7.2	10.8	13.6
150億円以上	0	1.6	2.8	4.8	6.8	8.8
40億円以上	0	0.8	1.2	2.4	3.2	4.4
10億円以上	0	0.4	0.8	1.2	1.6	2.4
1億円以上	0	-	-	0.4	0.8	1.2
1億円未満	0	-	-	-	-	0.4

Answer 2

できました！！すごい！！

しかしこれは難しいですね・・・理解のために中身をよく拝見しました。

=INDEX($B$1:$G$1,MATCH(B12,OFFSET($A$1,MATCH(A12,$A$2:$A$7,0),1,1,6),1))

【１】　INDEX($B$1:$G$1,MATCH(・・・) )

　範囲 $B$1:$G$1（0点～10点）の中から、MATCH(・・・) の行位置にあるデータを取り出す

【２】　MATCH(B12,OFFSET(・・・),1)

　検索値B12（数値0～13.6）以下の最大数値が、OFFSET(・・・)の範囲中の何番目にあるかを調べる

【３】　OFFSET($A$1,MATCH(・・・),1,1,6)

　基準セル$A$1（左上）から、MATCH(・・・)行分シフトした行、1列分シフトした列（=2列目）の

　高さ1セル、幅6セル分の範囲の値を返す

【４】　MATCH(A12,$A$2:$A$7,0)

　検索値A12（「600億以上」など）と完全に一致する値が、

　$A$2:$A$7（「1億円未満」～「600億以上」の列）の範囲中の何番目にあるかを調べる

■例：検索値A12が、10億円以上　・　検索値B12が、1.5の場合

【４】は、「10億円以上」が4番目なので、＝4

【３】は、OFFSET(A1,4,1,1,6)　になるので

　Ａ１から４行下にシフトした行（「10億円以上」の行）＝B列

　Ａ１から１列右にシフトした列＝5行

　＝セルB5　から始まって、高さ１・幅６の範囲を返す

　＝B5:G5　＝「10億円以上」の行の、（0, 0.4, 0.8, 1.2, 1.6, 2.4）

【２】は、MATCH(1.5, B5:G5, 1)　になるので

　（0, 0.4, 0.8, 1.2, 1.6, 2.4）　のうち、1.5以下で最大の数値の位置は、4番目の1.2（1.2以上1.6未満）

　＝4

【１】は、INDEX(B1:G1,4)になるので、

　（0点, 2点, 4点, 6点, 8点, 10点）のうち、4番目の数値を取り出す

　＝6点

うーん、合ってます。すばらしい・・・

OFFSET関数というのは知りませんでした。

勉強になりました。ありがとうございました。

AND,OR関数も存在は知っていたのですが、使ったことはないので

活用しようと思います、ありがとうございます（＾－＾）ノ

Answer 3

isa_MSAnswers さん、こんにちは。

複数条件という事でしたら、例えば、AND 関数や OR 関数 などは利用できないでしょうか？

　AND 関数

　OR 関数

　例 : IF ( AND ([条件1],[条件2] ),TRUE,FALSE)

こちらの関数を利用すると IF 文をネストするよりもすっきりと出来るのかなと思いました。

もしよければ、お試しくださいね。

他にもこんな方法があるよ、という方がいましたら、ご投稿お待ちしています。