假设在工作表sheet2排除sheet1中的合计数为0的项目;
1、在sheet2的a3单元格输入公式:
=offset(sheet1!a$3:f$11,small(if(sheet1!$f$3:$f$11=0,4^8,row($a$1:$a$9)),row(a1))-1,0,1,1)
数组公式按:ctrl+alt+enter 三键结束输入;
见图一
2、在sheet2的g3单元格输入公式:
=offset(sheet1!a$3:f$11,small(if(sheet1!$l$3:$l$11=0,4^8,row($a$1:$a$9)),row(a1))-1,0,1,1)
数组公式按:ctrl+alt+enter 三键结束输入;
见图二
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkLGWsAvyIF90-AACgk_ruM4sAAZ1uwGLOIIAAKCr131.jpg)
3、在sheet2的m3单元格输入公式:
=offset(sheet1!a$3:f$11,small(if(sheet1!$r$3:$r$11=0,4^8,row($a$1:$a$9)),row(a1))-1,0,1,1)
数组公式按:ctrl+alt+enter 三键结束输入;
见图三
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkK2WsAvyIZIWjAACm7pzuiscAAZ1uwGMJ_8AAKcG653.jpg)
4、选中a3横拉填充至f3;
见图四
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkLGWsAv2IR1UqAACkyHbfu5MAAZ1uwGNHFsAAKTg357.jpg)
5、选中g3横拉填充至l3;
见图五
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkLGWsAv6IKt1EAACmQBKE7b8AAZ1uwGODuwAAKZY489.jpg)
6、选中m3横拉填充至r3;
见图六
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkK2WsAv-IJsvGAAConKeSmzUAAZ1uwGPAoAAAKi0102.jpg)
7、选中a3:r3;
见图七
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkK2WsAwCIWzJgAACo-iOhwb8AAZ1uwGP-c4AAKkS792.jpg)
8、下拉填充至表格内的a11至r11,最终结果:
见图八
![](https://ask-fd.zol-img.com.cn/g7/M00/0D/02/ChMkK2WsAwGIGFBpAACqQ5Zrx8cAAZ1uwGQ8VcAAKpb577.jpg)
说明:本来可以设计一个公式,但计算量反而大,所以设计成三段式公式,
sheet2会自动排除sheet1的合计数为0的项目。