エクセルINDEX関数でアドバイス欲しいです!!

質問

現在INDEX関数を使い歩合計算をしています。
条件は
個人売上0～300000で達成率93％で4％歩合
個人売上0～300000で達成率100％で6％歩合
他パターン20通りほどあります。
なお人件費が53％以上になったら、歩合が1％ダウンします。

とても細かく分かれていて、現在使っている数式は、
=IF(OR($A$3="",$B$3="",$K$3>=53%),
INDEX(sheet1!$B$11:$H$16,MATCH($H$3,sheet1!$A$11:$A$16,1),MATCH(C9,sheet1!$B$10:$H$10)),
INDEX(sheet1!$B$3:$H$8,MATCH($H$3,sheet1!$A$3:$A$8,1),MATCH(C9,sheet1!$B$2:$H$2)))

sheet1のB2～H2に
0
300000
600000
900000
1200000
1500000
1800000

sheet1のA3～A8に
0％
92.9％
99.9％
100％
106％
112％

上記数値設定があり、人件費53％以下なら、
B3～H8に入っている％の数値を拾ってきます。

同様に人件費53％以上なら
B11～H16入っている％の数値を拾ってきます。

今回はさらに売上全体に対してその歩合％に対して、
以下の条件を付ける事になりました。

店舗目標予約率50％以下＝歩合-1％
個人目標予約率40％以下＝歩合-2％

添付資料「指名売上」の％はそのままで、
％をダウンした数式を作りたいと思って試行錯誤したのですが、
行き詰ってしまいました。

現在の数式を整理すると
★個人指名売上1.250.000円
★人件費51％(添付資料だと53％以下参照)
★達成率102％
★F6セルの「11％」を拾う

となっています。

これに、
店舗目標予約率50％以下＝歩合-1％
個人目標予約率40％以下＝歩合-2％

店舗目標が50％以下で個人目標が40％以上なら-1％
店舗目標が50％以上で個人目標が40％以下なら-2％
店舗目標が50％以下で個人目標も40％以下なら-3％

のような感じです。

なかなか文章の着地点が見えないのですが、
このような感じで1つのセルに数式を作る事は出来るのかどうかです。

私には複雑なのか組むことが出来なかったので、
お分かりになる方、参考にさせていただけたらと思っています。

また、INDEX・MATCH関数ではなく、
別の関数による数式があればそちらでトライしてみますので、
それも含めアドバイスいただけたらと思います。

長文で内容が分かりづらいかもしれませんが、
よろしくお願いいたします。

回答No.3

tom04

こんばんは！
すでに解決しているようなので、余計なお世話になると思いますが・・・
前半のINDEXとMATCH関数部分だけ！

Sheet1のB3～H8セルを範囲指定　→　名前ボックスに仮に　以下　と入力し、OK
Sheet1のB11～H16セルを範囲指定　→　名前ボックスに　以上　と入力しOK
（どんな名前でも構いませんが、数値・アルファベットは「_」を入れないと使えないことがあります）
これでそれぞれの範囲が名前定義されましたので、これを利用する方法です。

=IF(COUNTBLANK($A$3:$B$3),"",INDEX(IF($K$3<53%,以下,以上),MATCH($H$3,Sheet1!$A$3:$A$8,1),MATCH(C9,Sheet1!$B$2:$H$2,1)))

としてみてはどうでしょうか？
後はkeithinさんが仰っているように単純にマイナスするだけだと思います。

以上、どうも失礼しました。m(_ _)m

回答No.2

keithin

ん？

＞「1+($K$3>=53%))-(店舗目標予約率<=50%)*1%-(個人目標予約率<=40%)*2%)」
＞この先頭の「1」とはどのような意味なのでしょうか？

数式の区切りを間違っています。

一般にINDEX関数は（アナタが最初に使っている式も同じで）
=INDEX(範囲，行，列）
のようにして，縦と横の交わるセルを参照します。

今回回答で使ったのは，もう一つ別のINDEX関数の使い方で
=INDEX((範囲１，範囲２），行，列，領域番号）
のようにして，Ｋ３が５３％より大きい小さいで範囲１，２を切り替えて計算しています。

もう少しふつーに書けば
=INDEX((B3:H8, B11:H16), MATCH(行), MATCH(列), IF(K3>=53%,2,1))
のようになりますが，
IF(K3>=53%,2,1)
の部分を
1+(K3>=53%)
として計算しています。

実際にこのように書くことで，IFで書いたのと同じ計算結果が得られることを別のセルにここだけ切り取って書き出し，計算させて確認してみてください。

このようにしてINDEX関数の計算を終えた後に，更に補正として－１％，－２％を追加して計算しています。

回答No.1

keithin

表から求めた歩合から，そのまま１％や２％や３％を引き下ろしてしまっていいのですか？

幸い歩合の２つの表の縦横項目は全く同じなので，INDEXを１つにまとめるのは簡単にできます。
=IF(OR($A$3="",$B$3=""),"",INDEX((Sheet1!$B$3:$H$8,Sheet1!$B$11:$H$16),MATCH($H$3,sheet1!$A$3:$A$8,1),MATCH(C9,sheet1!$B$2:$H$2),1+($K$3>=53%))-(店舗目標予約率<=50%)*1%-(個人目標予約率<=40%)*2%)

その上で，「店舗目標予約率」や「個人目標予約率」とは一体どこを見たらいいのか不明ですが，それぞれの値を使って１～３％を単純に差し引く計算をするだけのようです。

補足

＞表から求めた歩合から，そのまま１％や２％や３％を引き下ろしてしまっていいのですか？
まさにその通りです!!

結果数式を参考にさせていただき、
無事完成しました!!

INDEX・MATCHも簡単にまとめる事が出来て、
よりスッキリした数式ができました!!

失礼ですが、一つ質問があります。
INDEX・MATCHの後の
「1+($K$3>=53%))-(店舗目標予約率<=50%)*1%-(個人目標予約率<=40%)*2%)」
この先頭の「1」とはどのような意味なのでしょうか？
これが無いとエラーになりますし、
最終的には、sheet1の歩合表の％を全て-1％にしたら合わせられたので、
この「1」がデフォルトで指定しないといけないのだと思いました。

解釈、正しいでしょうか？