Question

excel函数，求大神帮忙解决

左数第二个数字如果是质数在I列显示质数，如果是合数在J列显示合数，谢谢

Answer 1

如果添加辅助列是可以达到你的要求的，因为公式太长了，不要辅助列的话超过了excel的限制，主要是麻烦在要判断每行的第二个数据上，导致公式加长，设定K列为辅助列，在K列中输入公式：VALUE(LEFT(RIGHT(IF(FIND(":",CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))=1,RIGHT(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)),LEN(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))-1),CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2))),LEN(IF(FIND(":",CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))=1,RIGHT(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)),LEN(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))-1),CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2))))-FIND(":",IF(FIND(":",CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))=1,RIGHT(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)),LEN(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))-1),CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2))))),FIND(":",RIGHT(IF(FIND(":",CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))=1,RIGHT(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)),LEN(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))-1),CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2))),LEN(IF(FIND(":",CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))=1,RIGHT(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)),LEN(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))-1),CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2))))-FIND(":",IF(FIND(":",CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))=1,RIGHT(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)),LEN(CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2)))-1),CONCATENATE(IF(A2="","",A2),IF(B2="","",":"&B2),IF(C2="","",":"&C2),IF(D2="","",":"&D2),IF(E2="","",":"&E2),IF(F2="","",":"&F2),IF(G2="","",":"&G2),IF(H2="","",":"&H2))))))-1))
以下公式是数组公式，按ctrl+shift+enter结束输入
再在I2中输入公式：=IF(AND(k2>=2,N2=INT(k2)),IF(SUM(--(MOD(k2,ROW(INDIRECT("1:"&INT(SQRT(k2)))))=0))>1,"","质数"),"")
再在j2中输入公式：=IF(AND(k2>=2,N2=INT(k2)),IF(SUM(--(MOD(k2,ROW(INDIRECT("1:"&INT(SQRT(k2)))))=0))>1,"合数",""),"")
然后向下复制公式即可

Answer 2

Excel 好像没有提供这样的函数，需要自己写自定义函数才解决了。

追问

那这个问题没办法解决了，我再问你另一个问题吧，我上张图，我希望就是后面显示语数比能以公式来实现，不要手动输入

Answer 3

12、17、23、2、7、17、23是质数，其余是合数