エクセル 該当する範囲の名称を返すには？（その２）

質問

当方は企業の人事担当者で、資料を作らなければなりません。
以下のようなことが出来ますでしょうか？

・職種別グレード別で給与が定まっている
・職種/グレード/給与額は分かっている
・その人がどのレンジに属するかを判定したい

具体的にいうと、こんな感じです。

・Sheet1!A1:E13に表があります
職種 グレード 下限 上限 レンジ
営業職 4 350,000 360,000 III
営業職 4 340,000 349,900 II
営業職 4 330,000 339,900 I
営業職 3 303,400 320,000 III
営業職 3 286,700 303,300 II
営業職 3 270,000 286,600 I
技術職 4 370,000 380,000 III
技術職 4 360,000 369,900 II
技術職 4 350,000 359,900 I
技術職 3 313,400 330,000 III
技術職 3 296,700 313,300 II
技術職 3 280,000 296,600 I

・Sheet2!A1:C3に以下内容が入力してあります
職種 グレード 給与額
営業職 3 310,000
技術職 3 310,000

このときに、Sheet2!D2:D3にそれぞれ”III”、”II”
を返したいのです。

長くなって申し訳ないです。

ちょっと前に似たような質問をさせてもらい、丁寧に回答して
いただいた皆さん、ありがとうございました。

回答No.2

noname#204879 · ベストアンサー

Sheet2!D2: =INDEX(Sheet1!E$1:E$13,SUMPRODUCT((Sheet1!A$1:A$13=A2)*(Sheet1!B$1:B$13=B2)*(Sheet1!C$1:C$13<=C2)*(Sheet1!D$1:D$13>=C2),ROW(A$1:A$13)))

お礼

出来ました！素晴らしいです！

式を読み込んで、自分なりに理解してみます。
大変ありがとうございました。

回答No.1

imogasi

相当込み入った回答になるので、印刷して、じっくり考えて
●ＶＬＯＯＫＵＰ関数のＴＲＵＥ型に持ち込む
●ポイントは、職種により、ＶＬＯＯＫＵＰ関数の引くテーブル（セル範囲）を割り出す仕方にある。
●職種は列方向に（横方向に）下記の例のように組み替えて
表を作る。
●ＶＬＯＯＫＵＰ関数のＴＲＵＥ型の利用に都合の良いように給与帯を昇順に並べ変えておく（組み変える）
ーーー
例データ
質問のデータを（ここではＳｈｅｅｔ２とする）
営業職4 営業職3
営業職 4 330,000 339,900 I 営業職 3 270,000 286,600 I
営業職 4 340,000 349,900 II 営業職 3 286,700 303,300 II
営業職 4 350,000 360,000 III 営業職 3 303,400 320,000 III
第１行の「営業職4」などは検索に便利なように（２つに分かれていると面倒）、結合した情報セルを作る。
ーー
Ｓｈｅｅｔ３のＡ１：Ｅ１に
Ａ列　　Ｂ列　　　Ｃ列　　　Ｄ列　　Ｅ列
営業職 3 営業職3 290000 II
Ａ列、Ｂ列は指定のため入力。
Ｃ列は関数=A1&B1で作る。
Ｄ列は給与額を入れる。
問題はＥ列の関数式。
=VLOOKUP(D1,OFFSET(Sheet2!$A$1,0,MATCH(C1,Sheet2!$A$1:$X$1,0)+1,100,3),3,TRUE)
意味は
=VLOOKUP(D1,職種＋グレードで決まる３列のセル範囲,3列目を採る,TRUE型)
ＶＬＯＯＵＰ関数ＴＲＵＥ型は範囲帯から該当コードをみつける仕組みの関数。
さて職種＋グレードから検索に使う表範囲を求める方法
OFFSET(Sheet2!$A$1,0,MATCH(C1,Sheet2!$A$1:$X$1,0)+1,100,3)
について
MATCH(C1,Sheet2!$A$1:$X$1,0）の部分は、
職種＋グレードで決まる最初列を見つけている。２列右から開始なのでずれは＋１しておく（ＯＦＦＳＥＴ関数はずれ列数指定するもの）
１００はここでは最高100列の意味、３は３列
270,000 286,600 I
286,700 303,300 II
303,400 320,000 III
第2列は要らないと思うが時間がなくて確認不備。
第１列も数を変えるべきかもしれないが時間がなくて確認不備。
ーー
実際のデータの場合チェックケースの件数を十分にしてください。
ーー
これよりむつかしい関数の型が２，３あるが、まあそれに次ぐ難しい
タイプでしょう。

お礼

相当読み込まないと理解できなさそうですね・・・

印刷して頑張ってみます。
ありがとうございました。