求能下拉的公式

风雨相随3

求能下拉的公式 求能下拉的公式.rar (13.76 KB, 下载次数: 43)

2024-2-22 07:40 上传

点击文件名下载附件

丢丢表格

低版本公式


=1/LOOKUP(9,1/C$2:C2)*B2/SUM((LOOKUP("唑",A$1:A2)=LOOKUP(ROW($2:9),ROW($2:9)/(A$2:A9>0),A$2:A9))*B$2:B9)

jivy1212

1. F2=IF(A2<>"",C2/LOOKUP(1,SUBTOTAL({3,9},OFFSET(A2,,{0,1},ROW($1:$66))))*B2,F1/B1*B2)

复制代码

quqiyuan

=SUM((LOOKUP(ROW($2:$7),ROW($2:$7)/($A$2:$A$7<>""),$A$2:$A$7)=LOOKUP("座",$A$2:A2))*$C$2:$C$7)/SUM((LOOKUP(ROW($2:$7),ROW($2:$7)/($A$2:$A$7<>""),$A$2:$A$7)=LOOKUP("座",$A$2:A2))*$B$2:$B$7)*B2

xlllw203

1. =LOOKUP(,0/C$2:C2,C$2:C2)/SUMPRODUCT((LOOKUP("坐",A$2:A2)=LOOKUP(ROW($2:$7),ROW($2:$7)/(A$2:A$7<>""),A$2:A$7))*B$2:B$7)*B2

复制代码

wlc5201130

1. =LOOKUP(1,0/C$2:C2,C$2:C2)/SUM((LOOKUP(ROW($2:$7),IF(A$2:A$7<>"",ROW($2:$7)),A$2:A$7)=LOOKUP("座",$A$2:A2))*B$2:B$7)*B2

复制代码

我要成为专家

1. =B2/SUM((LOOKUP(ROW($2:$7),ROW($2:$7)/($A$2:$A$7<>0),$A$2)=LOOKUP("座",$A$2:A2,$A$2))*$B$2:$B$7)*LOOKUP("座",$A$2:A2,$C$2)

复制代码

D2输入公式，三键下拉

我要成为专家

jivy1212 发表于 2024-2-22 09:08

offset三维引用+subtotal落地+lookup定位累计数值，lz函数用的真是炉火纯青，跪服

丢丢表格

jivy1212 发表于 2024-2-22 09:08

如借用上一单元格的话， 我是写成：


=IF(A2>0,C2/SUM(OFFSET(B2,,,MODE(ROW($1:$7)+(A3:B9>0)%))),F1/B1)*B2

  用借用上一个单元格的方法 简洁许多。。。

hcm19522

D2=B2/SUMPRODUCT((SUBTOTAL(3,OFFSET(A$2,,,ROW($1:8)))=COUNTIF(A$2:A2,"<>"))*B$2:B9)*1/LOOKUP(9,1/C$2:C2)