UC知道EXCEL中如何让一个单元格数据在大于且小于不同的数值范围内显示为各种数值
来源：百度知道 编辑：UC知道 时间：2024/07/05 20:55:12
EXCEL中如何让一个单元格数据在大于且小于不同的数值范围内显示为各种数值
我要做的表格是这样的，一个单元格是合格率 ，另一个单元搁是系数。合格率由我自己填写，我就是想在填写合格率之后会B1会自动生成系数。两个单元格之间的关系为 合格率≤90% 系数为0.7 90%<合格率≤91% 系数为0.8 91%<合格率≤92% 系数为0。9 92%<合格率≤93% 系数为1。0 93%<合格率≤94% 系数为1。1 94%<合格率≤95% 系数为1。2 95%<合格率≤96% 系数为1。3 96%<合格率≤97% 系数为1。4 97%<合格率≤97.5% 系数为1。5 97.5%<合格率≤98% 系数为1。6 98%<合格率≤98.5% 系数为1。7 98.5%<合格率≤99% 系数为1。8 99%<合格率≤100% 系数为1。9 合格率=100% 系数为2。 请哪位高手帮忙设计下 或者是教下我用什么公式。 我是个新手 第一次在百度请教问题，还请大家多帮忙 我在等答案 谢谢 谢谢了
=LOOKUP(A1-0.0000001,{0,0.9,0.91,0.92,0.93,0.94,0.95,0.96,0.97,0.975,0.98,0.985,0.99,1},{"0.7","0.8","0.9","1","1.1","1.2","1.3","1.4","1.5","1.6","1.7","1.8","1.9",""})
栋爸爸的这个公式可以 但是如果合格率是100% 不能显示系数位呀 ，怎么处理啊
我要做的表格是这样的，一个单元格是合格率 ，另一个单元搁是系数。合格率由我自己填写，我就是想在填写合格率之后会B1会自动生成系数。两个单元格之间的关系为 合格率≤90% 系数为0.7 90%<合格率≤91% 系数为0.8 91%<合格率≤92% 系数为0。9 92%<合格率≤93% 系数为1。0 93%<合格率≤94% 系数为1。1 94%<合格率≤95% 系数为1。2 95%<合格率≤96% 系数为1。3 96%<合格率≤97% 系数为1。4 97%<合格率≤97.5% 系数为1。5 97.5%<合格率≤98% 系数为1。6 98%<合格率≤98.5% 系数为1。7 98.5%<合格率≤99% 系数为1。8 99%<合格率≤100% 系数为1。9 合格率=100% 系数为2。 请哪位高手帮忙设计下 或者是教下我用什么公式。 我是个新手 第一次在百度请教问题，还请大家多帮忙 我在等答案 谢谢 谢谢了
=LOOKUP(A1-0.0000001,{0,0.9,0.91,0.92,0.93,0.94,0.95,0.96,0.97,0.975,0.98,0.985,0.99,1},{"0.7","0.8","0.9","1","1.1","1.2","1.3","1.4","1.5","1.6","1.7","1.8","1.9",""})
栋爸爸的这个公式可以 但是如果合格率是100% 不能显示系数位呀 ，怎么处理啊
=LOOKUP(A1-0.0000001,{0,0.9,0.91,0.92,0.93,0.94,0.95,0.96,0.97,0.975,0.98,0.985,0.99,1},{"0.7","0.8","0.9","1","1.1","1.2","1.3","1.4","1.5","1.6","1.7","1.8","1.9",""})
-------------------------------
合格率≤90% 系数为0.7 90%<合格率≤91% 系数为0.8 91%<合格率≤92% 系数为0。9 92%<合格率≤93% 系数为1。0 93%<合格率≤94% 系数为1。1 94%<合格率〈95% 系数为1。2 95%≤合格率〈96% 系数为1。3 96%≤合格率<97% 系数为1。4 97%≤合格率〈97.5% 系数为1。5 97.5%≤合格率〈98% 系数为1。6 98%≤合格率〈98.5% 系数为1。7 98.5%≤合格率〈99% 系数为1。8 99%≤合格率〈100% 系数为1。9 合格率=100% 系数为2。：
=LOOKUP(A1-0.00001,{0,0.9,0.91,0.92,0.93,0.94,0.94999,0.95999,0.96999,0.97499,0.97999,0.98499,0.98999,0.99999,1},{"0.7","0.8","0.9","1","1.1","1.2","1.3","1.4","1.5","1.6","1.7","1.8","1.9","2"})
在s