2007版本excel数字相加等于目标值

发布网友发布时间：2022-10-18 07:13

共1个回答

热心网友时间：2023-11-09 18:17

一共有2^7=128种组合
a1-a7为数字
b1=MAX(IF(IF(ROW(1:128)<=64,A17,0)+IF(MOD(ROW(1:128),64)<32,A18,0)+IF(MOD(ROW(1:128),32)<16,A19,0)+IF(MOD(ROW(1:128),16)<8,A20,0)+IF(MOD(ROW(1:128),8)<4,A21,0)+IF(MOD(ROW(1:128),4)<2,A22,0)+IF(MOD(ROW(1:128),2)<1,A23,0)=68,ROW(1:128),""))
数组公式，ctrl+shift+enter,
c1=IF(b1<=64,"A1+","")&
IF(MOD(b1,64)<32,"A2+","")&IF(MOD(b1,32)<16,"A3+","")&IF(MOD(b1,16)<8,"A4+","")
&IF(MOD(b1,8)<4,"A5+","")&IF(MOD(b1,4)<2,"A6+","")&IF(MOD(b1,2)<1,"A7","")&"=68"
就是你要的结果
或者：你还可以用下面数组公式查看所有组合：
=IF((ROW()-1)*8+COLUMN()<=64,A17,0)+IF(MOD((ROW()-
1)*8+COLUMN(),64)<32,A18,0)+IF(MOD((ROW()-
1)*8+COLUMN(),32)<16,A19,0)+IF(MOD((ROW()-
1)*8+COLUMN(),16)<8,A20,0)+IF(MOD((ROW()-
1)*8+COLUMN(),8)<4,A21,0)+IF(MOD((ROW()-
1)*8+COLUMN(),4)<2,A22,0)+IF(MOD((ROW()-1)*8+COLUMN(),2)<1,A23,0)