エクセルの関数について教えてください。

質問

以前に質問してお答えいただきましてありがとうございました。
再度分からなくなってしまったので、質問です。

今、下記の関数で目標数字に対して、4つの平均の数字の差引をしたときに表示される記号を変える関数を出しています。

=IF(AVERAGE(AL5:AO5)=AJ5,"",IF(AVERAGE(AL5:AO5)<AJ5,IF(AVERAGE(AL5:AO5)>=AJ5-10,"△",IF(AVERAGE(AL5:AO5)<=AJ5-50,"◎","○")),IF(AVERAGE(AL5:AO5)<=AJ5+10,"▲","×")))

こちらはの関数の意味は下記のとおりです。
目標 － 4つの平均 ＝
同じ場合は●
０～１０上回る時は、△
１０～５０上回る時は◎

０～１０下回る時は▲
１０以上下回る時は×

という形にしています。

今回お聞きしたいのは、さらにマイナスの場合
１０～５０下回る時は×
５０～１００下回る時は××
１００以上下回る時は×××
と表示させたいです。

すみませんが、上記の関数を少しいじっていただきそのようになる関数を教えてください。

宜しくお願い致します。

回答No.6

kagakusuki · ベストアンサー

>こちらでかまいません。

の意味が、回答番号：No.3に対する、質問者様が付けられた補足では、「上回る」と「下回る」が逆である事も含んでいるものと仮定して、回答させて頂きます。
　この仮定が正しいのか否か、明確には確認出来ていない事、数式を回答文に入力する際に、私のキーの打ち間違いがないと断言までは出来ない事、等の要注意点がありますから、実用に供する前に、動作確認して頂くよう、御願い致します。
（一応、私の方でも投稿前にパソコン上で動作確認はしておりますが、パソコン⇔携帯間では、コピー＆ペーストが出来ないのです）

　本題に入りまして、以下の数式で良いと思います。

=IF(AVERAGE(AL5:AO5)<AJ5-50,"◎",IF(AVERAGE(AL5:AO5)<AJ5-10,"○",IF(AVERAGE(AL5:AO5)<AJ5,"△",IF(AVERAGE(AL5:AO5)=AJ5,"●",IF(AVERAGE(AL5:AO5)>AJ5+10,IF(AVERAGE(AL5:AO5)>AJ5+50,IF(AVERAGE(AL5:AO5)>AJ5+100,"×××","××"),"×"),"▲")))))

　それから、mako_sea様の回答にあるVLOOKUP関数を使う方法の、一例も、併記しておきます。>
　まず最初に、目的のセルがあるのと同じファイル内の、適当な場所（以下の例ではAM7:AO11）に、

AM7に-101,AM8に-100,AM9に-50,AM10に-10,AM11に0,
AN7に"◎",AN8に"◎",AN9に"○",AN10に"△",AN11に"●",
AO7に"×××",AO8に"××",AO9に"×",AO10に"▲",AO11に"●"

と入力されている、一覧表を作っておきます。
　次に、目的のセルに、以下の数式を入力して下さい。

=VLOOKUP(-IF(ABS(AVERAGE(AL5:AO5)-AJ5)>100,101,ABS(AVERAGE(AL5:AO5)-AJ5)),$AM$7:$AO$11,2+(AVERAGE(AL5:AO5)>AJ5))
　尚、一覧表を作っておく場所は、印刷されない様にするために、設定した印刷範囲の外や、別のシートにする場合が、一般的です。

お礼

色々とご迷惑をおかけしてしまいすみませんでした。

大丈夫でした。

本当にありがとうございます。

回答No.5

kagakusuki

それから、100以上という範囲には、言葉の意味の上では、丁度100の場合も含んでいますから、御質問文中の

>１００以上下回る時は×××
という箇所を読んで、丁度100の場合は「×××」だと思っておりましたが、本当は
『１００を超えて下回る時は×××』
であり、丁度100の場合は「××」と表示させるのでしょうか。

補足

丁度100の場合は「××」と表示させるのでしょうか。

こちらでかまいません。
宜しくお願い致します。

回答No.4

kagakusuki

何度もすみません。

>1～１０上回る時は「△」
>ちょうど１０の場合「△」
>１０～５０上回る時は「○」
>ちょうど５０の場合「○」
>５０以上上回る時は「◎」

>平均値が目標値を、
>1を超えて10未満、下回っている場合「▲」
>丁度10だけ下回っている場合「▲」
>10を超えて50未満、下回っている場合「×」
>丁度50だけ下回っている場合「×」
>50を超えて100未満、下回っている場合「××」
>丁度100だけ下回っている場合「××」
>それ以上下回っている場合「×××」

とのことですが、前々回の私の回答に対する、質問者様が付けられた補足では、

>　目標値に対して、平均値が上回ることは今回の目的上、マイナスになってしまいます。
>目標に対して平均値が少なければ少ないほど結果的には良くなります。

>丁度50だけ上回っている場合 ××
>50を超えて100未満上回っている場合も××

>100以上上回っている場合 ×××と一旦なるようにしたいです。

と書かれており、上回る場合と下回る場合が逆になっています。

>　目標値に対して、平均値が上回ることは今回の目的上、マイナスになってしまいます。
>目標に対して平均値が少なければ少ないほど結果的には良くなります。

と書かれている事からしますと、前々回の回答に対する補足の方が正しく、目標値が幾つであるかには関わらず、兎に角、平均値の数値が小さい方が良く、そのため平均値が最も大きい範囲の表示は、「×××」だと思ったのですが、私の勘違いでしょうか。
　もし、平均値が最も大きい範囲の表示が「×××」だとしますと、前回の回答に対する補足に書かれている、「上回る」や「下回る」という言葉は、目標値を基準に平均値を評価するのではなく、平均値の方を固定した基準値として考えて、それに対して設定した目標値がどれだけ、上下しているのか、という視点から見た場合の話しになると思うのですが、前回の回答に対する補足では、「上回る」と「下回る」が逆ではないでしょうか。
　それとも、前々回の回答に対する補足は無視して、平均値は大きい程良いとして、前回の回答に対する補足を、優先した方が良いのでしょうか。

回答No.3

kagakusuki

折角、補足して頂いたところを申し訳御座いませんが、未だに不明な点が、多々あります。
　元の数式では、平均値が目標値を、丁度10だけ下回っている場合、平均値がより大きい場合と同じ表示であるのに対し、丁度50だけ下回っている場合には、平均値がより小さい場合と同じ表示となっていて、丁度10下回っている場合と、丁度50下回っている場合では、扱いが異なっていますが、それで宜しいのでしょうか。

　同様に、数式の改良後は、平均値が目標値を、丁度50上回っている場合と、丁度100上回っている場合には、平均値がより大きい場合と同じ表示をさせる予定であるのに対し、元の数式の丁度10上回っている場合には、平均値がより小さい場合と同じ表示となっていて、やはり扱いが異なっていますが、こちらもそれで宜しいのでしょうか。

　平均値と目標が等しい場合には、式の意味が説明されている文中では、「●」を表示する事になっているのに対し、数式では、何も表示しない事になっていますが、どちらが正しいのでしょうか。

　式の意味が説明されている文中では、

>１０以上下回る時は×

と記されておりますが、数式では、10（10以上に含まれる）の場合の表示は、「▲」になっていますが、どちらが正しいのでしょうか。

　式の意味が説明されている文中では、

>０～１０上回る時は、△
>１０～５０上回る時は◎

と記されておりますが、数式では、丁度10の場合には「△」、10を超えて50未満の場合には、説明文には記述のない「○」、丁度50の場合には「◎」が、表示される様になっています。
　又、説明文には50を超える場合には、何と表示させるのか、記述されていないのに対して、数式では「◎」が、表示される様になっていて、説明文と数式が食い違っています。

　平均値が目標値を、
丁度10だけ下回っている場合、
10を超えて50未満、下回っている場合、
丁度50だけ下回っている場合
50を超えて下回っている場合

の各々の場合には、何と表示させれば良いのでしょうか。

　再度、補足をして頂く様、御願い致します。

補足

たびたびすみません。

ご不明な点に関して再度記載いたします。

平均値と目標が等しい場合「●」
1～１０上回る時は「△」
ちょうど１０の場合「△」
１０～５０上回る時は「○」
ちょうど５０の場合「○」
５０以上上回る時は「◎」

平均値が目標値を、
1を超えて10未満、下回っている場合「▲」
丁度10だけ下回っている場合「▲」
10を超えて50未満、下回っている場合「×」
丁度50だけ下回っている場合「×」
50を超えて100未満、下回っている場合「××」
丁度100だけ下回っている場合「××」
それ以上下回っている場合「×××」

こちらで大丈夫でしょうか？
当初の数式が誤解を招くようでしたら無視してください。
宜しくお願い致します。

回答No.2

kagakusuki

すいません、質問者様のやりたい事が、良く理解出来なかったので、補足を要求したいのですが。

　御質問にある元の関数は、確認させて頂いたところ、

平均値が目標値に等しい場合には、何も表示されず
平均値が目標値を、10未満下回っている場合には「△」
平均値が目標値を、丁度10だけ下回っている場合には「△」
平均値が目標値を、10を超えて50未満、下回っている場合には「○」
平均値が目標値を、丁度50だけ下回っている場合には「◎」
平均値が目標値を、50を超えて下回っている場合には「◎」
平均値が目標値を、10未満上回っている場合には「▲」
平均値が目標値を、丁度10だけ上回っている場合には「▲」
平均値が目標値を、10を超えて50未満、上回っている場合には「×」
平均値が目標値を、丁度50だけ上回っている場合には「×」
平均値が目標値を、50を超えて上回っている場合には「×」

という表示がされましたが、本当にそれで宜しいのでしょうか。
　特に、平均値が目標値を、丁度10だけ下回っている場合の表示が、10未満下回っている場合と同じであるのに対し、丁度50だけ下回っている場合の表示が、50を超えて下回っている場合と同じになっていて、10未満下回っている場合とは異なっています。
　又、平均値が目標値を、丁度10だけ上回っている場合の表示は、10未満下回っている場合と同じですが、こちらはそれで宜しいのでしょうか。
　それから、式の意味を説明されている文中には、平均値が目標値を、50を超えて下回っている場合に関しては、何と表示するのかが、明記されてはおりませんが、本当に「◎」で宜しいのでしょうか。

　これらを踏まえた上で、新しい式では、平均値が目標値を、丁度50だけ上回っている場合や、平均値が目標値を、丁度100だけ上回っている場合には、どの様な表示にすれば良いのかを、御教え願います。

補足

分かりづらい説明で申し訳ありません。

まず最初に補足ですが、目標値に対して、平均値が上回ることは今回の目的上、マイナスになってしまいます。
目標に対して平均値が少なければ少ないほど結果的には良くなります。

それを踏まえてですが…
丁度50だけ上回っている場合 ××
50を超えて100未満上回っている場合も××

100以上上回っている場合 ×××と一旦なるようにしたいです。

こちらで宜しいでしょうか？
ご確認宜しくお願い致します。

回答No.1

mako_sea

質問者さんのやりたいことを見ると、
IFでネストするよりVLOOKUPでランクを表示するほうが
イメージに合ってる気がします。
やたらに長い式はちょっとした修正が入ったとき、
難しくて変更しにくくなりがちですので
作業用の列またはシートを作るなどして
簡潔な関数にしてみてはどうでしょうか。
図では、平均値を一旦算出してから基準(目標)との差でランクをみつけるようにしています。