Question

高分求助 Excel特殊条件求和

现有A列B列两排数据，量很大。希望可以在另外一张sheet中在单元格中逐个显示以下效果。希望可以只用公式，不要用到vba：
1. 现有一个别处链接过来的上限值数据，随时会动。需要求出一个特殊的和：将B列数据从小到大排，从最小的开始加，依次加上倒数第二、倒数第三。。依次类推，直至加到一个最接近上限值的和，即再多加一个就超过上限值了。我试过用small，但是不知道这样的公式怎么写
2. 需要知道上面那个和，是最小的多少个数字加在一起的，即个数
3. 用上面的个数，求出A列中最大的这些个数的数字的和

希望最好可以不用到vba，高分悬赏啦，拜谢
我希望不要动到A列和B列源数据，也就是保持原来数据的顺序不要更改，因为数据维护的需要，可能随时会有新的数据增加进去

Answer 1

原数据在Sheet1的A、B列，如图数据为Sheet2：

设两列辅助列C、D（设完后可将其隐藏）

A2输入公式：=LARGE(Sheet1!A:A,D2)

B2输入公式：=SMALL(Sheet1!B:B,D2)

C2输入公式：=SUM($B$2:B2)

D2输入1，将A2：D2单元格向下填充。

G4输入公式：=VLOOKUP(G1,C:D,1,1)

G8输入公式：=VLOOKUP(G1,C:D,2,1)

G12输入公式：=SUM(A2:INDIRECT("A"&G8+1))

追问

如果AB两列数据因为各种原因是乱序的呢，我只是想在求和的时候用一下

Answer 2

A列按照从大到小排序，B列按照从小到大排序，然后C/D/E作为辅助列，
C1公式：=SUM($B$1:B1)
E1公式：=SUM($A$1:A1)
公式下拉填充，D列则填充1、2、3、4……等差数列
假设你从的上限值数据放在G2单元格，以下公式可以得到你所要的3个数据：
=LOOKUP(G2,C:C,C:C) （此公式放在H2单元格，如果放在其他单元格，请把下面两个公式中的H2 改为此公式所在单元格）
=VLOOKUP(H2,C:D,2,FALSE)
=VLOOKUP(H2,C:E,3,FALSE)

Answer 3

A、B二列已知
C列：放从倒数累加的每行结果
D列：随时会变的数放第一行，以下全用公式： =上一行
E列：比较C、D列，如C>D=1，C<D="" ，
最下边一个“” 对应的就是第一个问题要求的和
最后一行存本列加和+1，也就是第二个要求的个数
同理，第三个问题也就一样能解决了，说起来太麻烦，你自己排一下吧

这法简单明了，不用什么特殊的函数，希望能管用

Answer 4

在结存金额的分内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>1,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),1),1),"")

在结存金额的角内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>2,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),2),1),"")

在结存金额的元内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>3,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),3),1),"")

在结存金额的十内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>4,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),4),1),"")

在结存金额的百内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>5,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),5),1),"")

在结存金额的千内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>6,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),6),1),"")

在结存金额的万内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>7,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),7),1),"")

在结存金额的十万内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>8,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),8),1),"")

在结存金额的百万内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>9,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),9),1),"")

在结存金额的千万内输入公式
=IF(LEN(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4))>10,LEFT(RIGHT(INT(F4&G4&H4&I4&J4&K4&L4&M4&N4&O4)-INT(P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4),10),1),"")

以上不同的部分代表位数,如1代表分\2代表角____
这种方法看上去有点自繁琐，^_^

还有一种较为简单的方法:
先在结存金额的分内输入公式
=IF(LEN(INT($F4&$G4&$H4&$I4&$J4&$K4&$L4&$M4&$N4&$O4)-INT($P4&$Q4&$R4&$S4&$T4&$U4&$V4&$W4&$X4&$Y4))>=(COLUMN($AI4)-COLUMN(AH4)),LEFT(RIGHT(INT($F4&$G4&$H4&$I4&$J4&$K4&$L4&$M4&$N4&$O4)-INT($P4&$Q4&$R4&$S4&$T4&$U4&$V4&$W4&$X4&$Y4),COLUMN($AI4)-COLUMN(AH4)),1),"")

其它位置的公式直接复制上面的公式即可,
这种方法非常简单方便,但有个缺点就是但你没有输入相关金额时,会显示"#VALUE!"

<----公式经过测试----->