基本上,我把一个IF函数放在Excel中,它说我有太多的参数。以前我已经超过了64个巢的限制,但我把巢降到了63个。我只是达到了某个极限,还是我的函数中有错误,我可以修复?
我的函数的目标是,每当D列福尔斯落在一定的百分比范围内,E列福尔斯落在一定的赔率范围内时,得出一个下注金额(体育博彩)。我的功能是附加的。谢谢你
=IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-132,E95<=-129),5,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-128,E95<=-125),6,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-124,E95<=-121),7,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-120,E95<=-117),8,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-116,E95<=-113),9,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-112,E95<=-109),10,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-108,E95<=-105),11,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-104,E95<=-101),12,
IF(AND(D95>=0.1,D95<0.12)*AND(E95>=-100),13,
IF(AND(D95>=0.12,D95<0.14,E95>=-132,E95<=-129),6,
IF(AND(D95>=0.12,D95<0.14,E95>=-128,E95<=-125),7,
IF(AND(D95>=0.12,D95<0.14,E95>=-124,E95<=-121),8,
IF(AND(D95>=0.12,D95<0.14,E95>=-120,E95<=-117),9,
IF(AND(D95>=0.12,D95<0.14,E95>=-116,E95<=-113),10,
IF(AND(D95>=0.12,D95<0.14,E95>=-112,E95<=-109),11,
IF(AND(D95>=0.12,D95<0.14,E95>=-108,E95<=-105),12,
IF(AND(D95>=0.12,D95<0.14,E95>=-104,E95<=-101),13,
IF(AND(D95>=0.12,D95<0.14,E95>=-100),14,
IF(AND(D95>=0.14,D95<0.16,E95>=-132,E95<=-129),7,
IF(AND(D95>=0.14,D95<0.16,E95>=-128,E95<=-125),8,
IF(AND(D95>=0.14,D95<0.16,E95>=-124,E95<=-121),9,
IF(AND(D95>=0.14,D95<0.16,E95>=-120,E95<=-117),10,
IF(AND(D95>=0.14,D95<0.16,E95>=-116,E95<=-113),11,
IF(AND(D95>=0.14,D95<0.16,E95>=-112,E95<=-109),12,
IF(AND(D95>=0.14,D95<0.16,E95>=-108,E95<=-105),13,
IF(AND(D95>=0.14,D95<0.16,E95>=-104,E95<=-101),14,
IF(AND(D95>=0.14,D95<0.16,E95>=-100),15,
IF(AND(D95>=0.16,D95<0.18,E95>=-132,E95<=-129),8,
IF(AND(D95>=0.16,D95<0.18,E95>=-128,E95<=-125),9,
IF(AND(D95>=0.16,D95<0.18,E95>=-124,E95<=-121),10,
IF(AND(D95>=0.16,D95<0.18,E95>=-120,E95<=-117),11,
IF(AND(D95>=0.16,D95<0.18,E95>=-116,E95<=-113),12,
IF(AND(D95>=0.16,D95<0.18,E95>=-112,E95<=-109),13,
IF(AND(D95>=0.16,D95<0.18,E95>=-108,E95<=-105),14,
IF(AND(D95>=0.16,D95<0.18,E95>=-104,E95<=-101),15,
IF(AND(D95>=0.16,D95<0.18,E95>=-100),16,
IF(AND(D95>=0.18,D95<0.20,E95>=-132,E95<=-129),9,
IF(AND(D95>=0.18,D95<0.20,E95>=-128,E95<=-125),10,
IF(AND(D95>=0.18,D95<0.20,E95>=-124,E95<=-121),11,
IF(AND(D95>=0.18,D95<0.20,E95>=-120,E95<=-117),12,
IF(AND(D95>=0.18,D95<0.20,E95>=-116,E95<=-113),13,
IF(AND(D95>=0.18,D95<0.20,E95>=-112,E95<=-109),14,
IF(AND(D95>=0.18,D95<0.20,E95>=-108,E95<=-105),15,
IF(AND(D95>=0.18,D95<0.20,E95>=-104,E95<=-101),16,
IF(AND(D95>=0.18,D95<0.20,E95>=-100),17,
IF(AND(D95>=0.20,D95<0.22,E95>=-132,E95<=-129),10,
IF(AND(D95>=0.20,D95<0.22,E95>=-128,E95<=-125),11,
IF(AND(D95>=0.20,D95<0.22,E95>=-124,E95<=-121),12,
IF(AND(D95>=0.20,D95<0.22,E95>=-120,E95<=-117),13,
IF(AND(D95>=0.20,D95<0.22,E95>=-116,E95<=-113),14,
IF(AND(D95>=0.20,D95<0.22,E95>=-112,E95<=-109),15,
IF(AND(D95>=0.20,D95<0.22,E95>=-108,E95<=-105),16,
IF(AND(D95>=0.20,D95<0.22,E95>=-104,E95<=-101),17,
IF(AND(D95>=0.20,D95<0.22,E95>=-100),18,,
IF(AND(D95>=0.22,E95>=-132,E95<=-129),11,
IF(AND(D95>=0.22,E95>=-128,E95<=-125),12,
IF(AND(D95>=0.22,E95>=-124,E95<=-121),13,
IF(AND(D95>=0.22,E95>=-120,E95<=-117),14,
IF(AND(D95>=0.22,E95>=-116,E95<=-113),15,
IF(AND(D95>=0.22,E95>=-112,E95<=-109),16,
IF(AND(D95>=0.22,E95>=-108,E95<=-105),17,
IF(AND(D95>=0.22,E95>=-104,E95<=-101),18,
IF(AND(D95>=0.22,E95>=-100),19,0,)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
3条答案
按热度按时间wlwcrazw1#
基本上你的公式太长了,超过了excell可读公式的数量,
您可以使用
=CHOOSE()
像下面
它给予同样的数量希望
mm5n2pyu2#
我建议将数据放在两个表中,并使用
FILTER
和VLOOKUP
:测试结果:
5t7ly7z53#
**1)**首先将2个变量的所有范围和要提取的相应结果放入如图所示的单元格范围中。
**2)**公式
=INDIRECT(ADDRESS(MATCH(C13,A:A,1),MATCH(D13,1:1,-1)))
有3部分:**a)**匹配函数为变量值查找正确的行和列。
**B)**地址函数将行和列的数字转换成一个单元格范围。
**c)**获取该单元格内容的间接函数。
**N.B.**注意,match函数使用第三个参数(1表示小于,-1表示大于,0表示完全匹配[这里不需要])来捕获满足变量值的第一个范围。