Question

求EXCEL公式

求个公式，见下图，谢谢！

Answer 1

• 大与小，可以用substutite()，把数字换成大或小，再判断出是哪种情况

B7公式：

=IF(INDEX({"全大","全小","两大一小","两大一小","两大一小","两小一大","两小一大","两小一大"},MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A7,1,"小"),2,"小"),3,"小"),4,"小"),0,"小"),5,"大"),6,"大"),7,"大"),8,"大"),9,"大"),{"大大大","小小小","大大小","大小大","小大大","小小大","小大小","大小小"},0))=B$3,"◎","")

• 奇偶，也可以用这个公式，稍微改一下。

F7公式：

=IF(INDEX({"全奇","全偶","两奇一偶","两奇一偶","两奇一偶","两偶一奇","两偶一奇","两偶一奇"},MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A7,1,"奇"),2,"偶"),3,"奇"),4,"偶"),0,"偶"),5,"奇"),6,"偶"),7,"奇"),8,"偶"),9,"奇"),{"奇奇奇","偶偶偶","奇奇偶","奇偶奇","偶奇奇","偶偶奇","偶奇偶","奇偶偶"},0))=F$3,"◎","")

Answer 2

在B7单元格输入公式=IF(IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)>=5))=3,"全大",IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)<=4))=3,"全小",IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)>=5))=2,"两大一小",IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)<=4))=2,"两小一大",""))))=B$3,"●","")

右到公式到E列，再下拉公式。

在F7单元格输入公式=IF(IF(SUMPRODUCT(N(ISODD(--MID($A7,ROW($1:$3),1))))=3,"全奇",IF(SUMPRODUCT(N(ISEVEN(--MID($A7,ROW($1:$3),1))))=3,"全偶",IF(SUMPRODUCT(N(ISODD(--MID($A7,ROW($1:$3),1))))=2,"两奇一偶",IF(SUMPRODUCT(N(ISEVEN(--MID($A7,ROW($1:$3),1))))=2,"两偶一奇",""))))=F$3,"●","")

右到公式到I列，再下拉公式。

追答

0既不是奇数也不是偶数，如果非要把0算成偶数，F7单元格公式改成=IF(IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)={1,3,5,7,9}))=3,"全奇",IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)={0,2,4,6,8}))=3,"全偶",IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)={1,3,5,7,9}))=2,"两奇一偶",IF(SUMPRODUCT(N(--MID($A7,ROW($1:$3),1)={0,2,4,6,8}))=2,"两偶一奇",""))))=F$3,"●","")

Answer 3

先写大小判断

B7输入  

=IF(CHOOSE(SUMPRODUCT(N(MID($A7,COLUMN($1:$3),1)<"5"))-16380,"全大","两大一小","两小一大","全小")=B$6,"○","")

公式右拉下拉

Answer 4

B7按下图输入公式，公式中=3为全大，=2为两大一小，=1为一大两小，=0为全小，自己做相应改变

追答

奇偶判断，只需把value(mid(a7,1,1))>4改为n(iseven(mid(a7,1,1)))