ＩＦ関数の応用？自動表示される数式を教えてください。

質問

ＩＦ関数の応用？自動表示される数式を教えてください。

画像を見ていただくと、レベルが4段階あります。
％の部分は数字を手入力します。(0％～100％)

求めたいのは黄色のセルの部分で、
「レベル〇」と自動的に表示させたいです。

【レベルの条件】
レベル1のみ％が入力されている場合はレベル1。
レベル1が100％、かつ、レベル2が70％以上の場合はレベル2。

ただし、レベル1が80％でレベル2が70％以上でもレベル1です。
つまりレベル1が100％で、さらにレベル2が70％以上の場合のみ、
レベル2になります。

なのでレベル3の条件は、レベル1と2が100％、かつ、レベル3が70％以上、
レベル4の条件はレベル1と2と3が100％、かつ、レベル4が70％以上となります。

上記のような条件の中で、％枠を手入力していくと、
黄色のセルにレベルが自動的に表示されるようにしたいです。

ＩＦ関数とＡＮＤ関数を混ぜて色々やってみましたが、
レベル3以上を表示させようとするとだめでした。

どうかお知恵を貸してください。

回答No.9

osu_neko09 · ベストアンサー

=MIN(4,IF(B2=0,0,IF(B2=1,99,1)),IF(B3<0.7,1,IF(B3=1,99,2)),IF(B4<0.7,2,IF(B4=1,99,3)),IF(B5<0.7,3,IF(B5=1,99,4)))

これだと、５レベルが追加されても式の一部を変更するだけですみます。
・最高でも４レベル　=MIN(4,
・レベル1の％が入力されていない場合はレベル０、レベル1が100％なら上位レベル、でなければレベル１。IF(B2=0,0,IF(B2=1,99,1)),
・レベル2の％が７０％未満の場合はレベル１、レベル２が100％なら上位レベル、でなければレベル２。IF(B3<0.7,1,IF(B3=1,99,2)),

（以下同様）

お礼

回答いただき、ありがとうございます。

ほんとだ！！
できました！！
MINでもできるんですね！
思いつかなかったです。

説明も簡潔でわかりやすかったです。
助かりました。

ありがとうございます！！

回答No.8

MackyNo1

複雑な入れ子のIF関数が作成しにくい場合は、すべてのレベルに対して、それぞれの結果を表示する式を直列でつなげるのが簡単かもしれません。

レベル1(B2が空白でなく、B2が1未満またはB3が0.7未満）
=IF(AND(B2<>"",OR(B2<1,B3<0.7)),1,"")

レベル２
=IF(AND(B2=1,B3>=0.7,B3<1),2,"")

レベル３
=IF(AND(B2=1,B3=1,B4>=0.7,B4<1),3,"")

レベル4
=IF(AND(B2=1,B3=1,B4=1,B5>=0.7),4,"")

まとめると
=IF(IF(AND(B2<>"",OR(B2<1,B3<0.7)),1,"")&IF(AND(B2=1,B3>=0.7,B3<1),2,"")&IF(AND(B2=1,B3=1,B4>=0.7,B4<1),3,"")&IF(AND(B2=1,B3=1,B4=1,B5>=0.7),4,"")

お礼

回答ありがとうございました。

しかし数式をコピペすると「数式中に対応するかっこがありません」
と表示されてしまいました(T△T)

自分でも試行錯誤で下記の数式に落ち着きました。

=IF(AND(B2=100%,B3=100%,B4=100%,B5>=70%),"レベル4",IF(AND(B2=100%,B3=100%,B4>=70%),"レベル3",IF(AND(B2=100%,B3>=70%),"レベル2","レベル1")))

考え方、勉強になりました！どうもありがとうございます！
また困ったときは助けてくださいm(　_　_　)m

回答No.7

某HN クロメート(Chromate)（@CoalTar）

＃6です。訂正です。人の訂正してないで、自分のをよく見ろって言われそう(>o<")

C3セルの =IF(AND(B2=1,B3>=0.7,C2<>""),"合格","不合格")
は良いのですが

C4セルは =IF(AND(B3=1,B4>=0.7,C3<>"不合格"),"合格","不合格")
下へオートフィルでした

あと=IF(C2="","","レベル"&COUNTIF(C2:C5,"合格"))
はC8セルへの入力ですね

お礼

私の質問の説明がわかりづらかったと思います。
ごめんなさい。

試行錯誤で下記の数式に落ち着きました。

=IF(AND(B2=100%,B3=100%,B4=100%,B5>=70%),"レベル4",IF(AND(B2=100%,B3=100%,B4>=70%),"レベル3",IF(AND(B2=100%,B3>=70%),"レベル2","レベル1")))

どうもありがとうございました。
また何かあったら是非よろしくお願いしますm(　_　_　)m

回答No.6

某HN クロメート(Chromate)（@CoalTar）

＃2のmshr1962さんの方法で少々訂正と独自解釈をいれて
=IF(AND(SUM(B2:B4)=300%,B5>=70%),4,
IF(AND(SUM(B2:B3)=200%,B4>=70%),3,
IF(AND(B2=100%,B3>=70%),2,IF(B2>0,1,""))))
早い話、ANDの終わりのカッコを忘れてるということです。
良い案だと思うので参考にさせていただきました

で当方は作業列案
C2セル =IF(B2>0,"合格","")
C3セル =IF(AND(B2=1,B3>=0.7,C2<>""),"合格","不合格")
下へオートフィル
B8セル =IF(C2="","","レベル"&COUNTIF(C2:C5,"合格"))

ちなみに 標準の表示形式で計算可能(数値と判断される)な値は右揃えになります。
日付や「$80」、「100%」など

回答No.5

imogasi

質問の内容が複雑で判りにくい。
下記横列（下記で第1行）もレベル１，２，３，４判定後（下記でA列）も質問ではレベル１，２，３，４となっているが、紛らわしいと思い、判定後はランク１，２，３，４としてみた。
ーー
質問をそのまま表にすると
ー レベル１ レベル２ レベル３ レベル４
ランク１ あり ＊ ＊ ＊
ランク２ 100 70以上 ＊ ＊
ランク１ 80 70以上 ＊ ＊
ランク３ 100 100 70以上 ＊
ランク４ 100 100 100 70以上
ーー
補足要求。
質問の言わんとする意味の疑問点。
（１）＊の部分は入力されることはないのか。入力されても無いものとする？
（２）100の個所は１００％に限ると言うことですね。
１００％未満ではどのランクに行くのか？
（上例でランク３、４などの場合は？）
（３）８０は８０％以上の意味か（質問では以上が抜けたのか？）
レベル１の数値は１００％か８０％のどちらかしか入れないのか？
（４）「あり」のところは１００％以外もありえるのですね？
（５）＞ランク２ 100 70以上 ＊ ＊
の例の場合７０％未満ではランクはどうなるのですか？
（６）各レベルでの１００％は、該当が良くあるケースなのか。他の場合では、１００％は普通のイメージでは、パーフェクトで、そんなに該当がないケースを想像するが。
＞数字を手入力します。(0％～100％)
ならレベル１も１００％以外も入力される、８０％未満もある、７０％もそれ未満も在るようだな。
具体的なイメージがわかない。
レベル１－４
ーーー
一見したところ、VBAでも使わないと（場合分けが自由に出来ないと。ユーザー定義関数）関数では複雑になりすぎるように思うが。

補足

=IF(AND(B2=100%,B3=100%,B4=100%,B5>=70%),"レベル4",IF(AND(B2=100%,B3=100%,B4>=70%),"レベル3",IF(AND(B2=100%,B3>=70%),"レベル2","レベル1")))

が私のやりたい事の答えでした。

どうもありがとうございました。

回答No.4

Cupper

おっと…添付された図を見るのを忘れてた

手で入力するのは数値ですよね。
文字列として入力した場合 単位の％はそのまま文字になるので 演算子（＝，＞＝など）は使えません。

％を表示させたいのでしたら、セルの書式設定で「パーセテージ」を選択しましょう。
100％を１とする小数を入力するだけで％記号が表示されます。
あるいはセルの書式設定の「ユーザー定義」で％を付加する書式を書くかですね。
　例：0"％"

回答No.3

Cupper

上手くできなかったと言う関数を示してもらえると、どう直せば良いのか、どう考えれば良いのかを示す事ができるんですけど…まあいいか。

まず成立する条件を書き出す
　条件１：下記以外すべて
　条件２：すべて AND 、Level1＝100、Level2＞＝70
　条件３：すべて AND 、Level1＝100、Level2＝100、Level3＞＝70
　条件４：すべて AND 、Level1＝100、Level2＝100、Level3＝100、Level4＞＝70

それぞれを数式にしてみる
　条件２：AND(Level1=100，Level2>=70)
　条件３：AND(Level1=100，Level2=100，Level3>=70)
　条件４：AND(Level1=100，Level2=100，Level3=100，Level4>=70)
Level○は該当するセルに書き直すこと
（この AND関数を複数使ったときの組み合わせができなか 演算子 >= が分からないのだろうと推測する）

あとは IF関数で組み合わせればOK
試行錯誤してがんばれ

　「IF関数を使った組み合わせが分からない(´；ω；`)」
なんて泣きごと言うんじゃないよ
　＝IF(条件３，結果３，IF(条件２，結果２，結果１））
などとするだけだ

これはあくまでも一例です。
他にもやり方は多数ありますが、やはりどのようなアプローチをするかを示してくれないと方法を絞り込むことができません。

お礼

=IF(AND(B2=100%,B3=100%,B4=100%,B5>=70%),"レベル4",IF(AND(B2=100%,B3=100%,B4>=70%),"レベル3",IF(AND(B2=100%,B3>=70%),"レベル2","レベル1")))

として、やりたい事ができました。
Cupperさんのおっしゃってる事で、私が質問している内容は間違いありません。
すごく参考になりました。

が、数式もこれに限っているわけではないですし、
この数式を見て、これよりもっと簡単にできるのに、
と思われる方もいらっしゃると思うので、
色々なやり方を知りたかった、というところもあります。

どうもありがとうございます。
また何かあればよろしくお願いします(泣き言も聞いてください。笑)

回答No.2

mshr1962

説明の通りなら
=IF(AND(SUM(B2:B4)=300%,B5>=70%,4,IF(AND(SUM(B2:B3)=200%,B4>=70%,3,IF(AND(B2=100%,B3>=70%,2,IF(B2>=70%,1,""))))

または
=(B2>=70%)+(B2=100%)*(B3>=70%)+(SUM(B2:B3)=200%)*(B4>=70%)+(SUM(B2:B4)=300%)*(B5>=70%)

お礼

ありがとうございます！！
質問の意味をちゃんと捉えていただき、ありがたいです！！
(わかりづらい説明なのに感動です)

教えていただいた1つ目の式はエラーが出てしまいましたが、
2つ目はバッチリです！！

回答も簡潔でわかりやすいです！ありがとうございます！！

回答No.1

lesskey

＞ＩＦ関数とＡＮＤ関数を混ぜて色々やってみましたが、

その色々やった途中の数式はありませんか？
それを元に考えた方が分かりやすいと思うので、あれば提示してください。

お礼

試行錯誤で

=IF(AND(B2=100%,B3=100%,B4=100%,B5>=70%),"レベル4",IF(AND(B2=100%,B3=100%,B4>=70%),"レベル3",IF(AND(B2=100%,B3>=70%),"レベル2","レベル1")))

に落ち着きました。
お騒がせしました、ありがとうございました。

また困った時は助けてくださいm(　_　_　)m