求助高手，帮助设定、修改公式

hbdyyx

公式设定的“有效行”是表1的3—85行，
本意：不受表1删除行变化。也就是说当表1删除行时，公式中的“有效行”始终是表1的3—85行。
出现的问题：
    当表1删除行时，公式中的“有效行”随之减少。当删除1行时变成了3—84；在删除1行变成了3—83；
……
求助：
    帮助设定、修改公式：不受表1删除行变化。公式中始终保持“有效行”是表1的3—85行。

lwwcom

用OFFSET函数
OFFSET('1'!$B$2,1,,85)
=SUMPRODUCT((OFFSET('1'!$B$2,1,,85)<>"")*(OFFSET('1'!$CL$2,1,,85)="")*(OFFSET('1'!$F$2,1,,85)>=21))-SUMPRODUCT((OFFSET('1'!$B$2,1,,85)<>"")*(OFFSET('1'!$CL$2,1,,85)="")*(OFFSET('1'!$F$2,1,,85)>25))

[ 本帖最后由 lwwcom 于 2011-6-23 16:41 编辑 ]

hbdyyx

原帖由 lwwcom 于 2011-6-23 16:39 发表
用OFFSET函数
OFFSET('1'!$B$2,1,,85)
=SUMPRODUCT((OFFSET('1'!$B$2,1,,85)"")*(OFFSET('1'!$CL$2,1,,85)="")*(OFFSET('1'!$F$2,1,,85)>=21))-SUMPRODUCT((OFFSET('1'!$B$2,1,,85)"")*(OFFSET('1'!$CL$2,1,,85)=" ...

提示 公式中含有错误

wangjguo44

原帖由 hbdyyx 于 2011-6-23 16:29 发表
公式设定的“有效行”是表1的3—85行，
本意：不受表1删除行变化。也就是说当表1删除行时，公式中的“有效行”始终是表1的3—85行。
出现的问题：
    当表1删除行时，公式中的“有效行”随之减少。当删除1行时变 ...

笨办法：=SUMPRODUCT((INDIRECT("'1'!B3:B86")<>"")*(INDIRECT("'1'!CL3:CL86")="")*(INDIRECT("'1'!F3:F86")>=26))-SUMPRODUCT((INDIRECT("'1'!B3:B86")<>"")*(INDIRECT("'1'!CL3:CL86")="")*(INDIRECT("'1'!F3:F86")>30))

hbdyyx

原帖由 lwwcom 于 2011-6-23 16:39 发表
用OFFSET函数
OFFSET('1'!$B$2,1,,85)
=SUMPRODUCT((OFFSET('1'!$B$2,1,,85)"")*(OFFSET('1'!$CL$2,1,,85)="")*(OFFSET('1'!$F$2,1,,85)>=21))-SUMPRODUCT((OFFSET('1'!$B$2,1,,85)"")*(OFFSET('1'!$CL$2,1,,85)=" ...

非常抱歉，是本人复制粘贴时发生了错误，请谅解

w83365040371

把直接引用的区域B3:b85, 改为indirect()间接引用就行了
INDIRECT("'1'!B3:B85")

hbdyyx

谢谢各位高手，帮助解决了求助的问题！
深表感谢！！！

TFQWFN

留下学习了

wangjguo44

原帖由 hbdyyx 于 2011-6-23 16:52 发表
谢谢各位高手，帮助解决了求助的问题！
深表感谢！！！

1. =SUMPRODUCT((INDIRECT("'1'!B3:B85")<>"")*(INDIRECT("'1'!CL3:CL85")="")*(INDIRECT("'1'!F3:F85")>=--LEFT(B$2,2))*(INDIRECT("'1'!F3:F85")<=--MID(B$2,FIND("-",B$2)+1,2)))

复制代码

公式可以右拖，不必一个个修改编辑

hbdyyx

函数公式真是太深奥了，看来永远也学不明白了。
还好，有各位高手们热心、无私的帮助。