excel中对AN、AO、AP的组合结果进行判断若为000时在AU栏显示4,7,；其他排列组合显示其他的数字。

发布网友发布时间：2022-04-23 06:24

我来回答

共4个回答

热心网友时间：2023-10-05 06:49

if函数最多只能嵌套7层，你的已经远远超过，使用vlookup函数进行查找，就可以得到结果。

如下图，做一个辅助列。最后将E:H列隐藏就可以了。

公式为=VLOOKUP(A5&B5&C5,IF({1,0},$E$1:$E$27&$F$1:$F$27&$G$1:$G$27,$H$1:$H$27),2,0)

使用组合键ctrl+shift+enter确定公式。

如果不使用辅助列也可以，就采用下面的公式

=VLOOKUP(A5&B5&C5,{"000","4,7";"001","2,3";"002","4,9";"010","1,0";"011","8,6";"012","6,2";"020","3,4";"021","5,7";"022","9,1";"100","8,1";"101","2,5";"102","8,0";"110","6,4";"111","1,6";"112","2,8";"120","7,7";"121","0,2";"122","8,4";"200","1,7";"201","8,1";"202","4,8";"210","5,8";"211","5,9";"212","0,6";"220","3,9";"221","7,4";"222","6,3"},2,0)

热心网友时间：2023-10-05 06:49

你可能if嵌套太多，引起的出错。

给你另外一个公式，在AU3输入：=VLOOKUP(--(AN3&AO3&AP3),{0,"4,7";1,"2,3";2,"4,9";10,"1,0";11,"8,6";12,"6,2";20,"3,4";21,"5,7";22,"9,1";100,"8,1";101,"2,5";102,"8,0";110,"6,4";111,"1,6";112,"2,8";120,"7,7";121,"0,2";122,"8,4";200,"1,7";201,"8,1";202,"4,8";210,"5,8";211,"5,9";212,"0,6";220,"3,9";221,"7,4";222,"6,3"},2,0)

如下效果图：

热心网友时间：2023-10-05 06:50

=MID("4,72,34,91,08,66,23,45,79,18,12,58,06,41,62,87,70,28,41,78,14,85,85,90,63,97,46,3",MATCH(AN1*100+10*AO1+AP1,{0,1,2,10,11,12,20,21,22,100,101,102,110,111,112,120,121,122,200,201,202,210,211,212,220,221,222},0)*3-2,3)

热心网友时间：2023-10-05 06:50

又是玩彩票吧，你的目的是什么？