【17.1.3】汇总列公式筛选后sumproduct计算错误

Wilson.Zhang

如题，在官方demo即可复现。

创建一个表格，下面添加一个汇总行，未筛选前：
image.png946675109.png

筛选后：这里的值应该是14.多结果显示295.多
image.png100516356.png

Wilson.Zhang · Wilson.Zhang

本帖最后由 Wilson.Zhang 于 2024-9-2 09:47 编辑

写BUG的程序员发表于 2024-8-30 14:30
demo在附件

SUBPRODUCT公式计算不忽略被筛选掉的行，还是根据筛Tablle范围内所有被引用单元格的数据计算，因此该公式的计算结果不变，而SUBTOTAL公式对“订单号_计数”列的统计结果为实际被筛选中的数据的计算结果，这才导致I15单元格的计算结果不正确。Excel中SUBPRODUCT公式也是如此，看来需要自定义公式来实现了，主要思路如下：
1. 自定义公式在计算前检查引用列中是否有未被筛选的单元格，即通过Worksheet:getRowVisible()查询每行是否可见，可见即被筛选了，不可见即未被筛选。如果有则舍弃该单元格的数据，否则保留以计算。
2. 待检查结束，根据既定规则计算数据即可。

您可以尝试看能否满足您的需要。

Wilson.Zhang · Wilson.Zhang

您好！根据您提供的信息了解了您的问题，需要您提供一个能够复现该问题的完整demo。

写BUG的程序员 · 写BUG的程序员

Wilson.Zhang 发表于 2024-8-30 11:26
您好！根据您提供的信息了解了您的问题，需要您提供一个能够复现该问题的完整demo。

demo在附件

Wilson.Zhang · Wilson.Zhang

您好！可以参考如下代码自定义：

function CustomSumProduct() {
this.name = 'CUSTOMSUMPRODUCT';
this.maxArgs = 1;
this.minArgs = 1;
}
CustomSumProduct.prototype = new GC.Spread.CalcEngine.Functions.Function();
CustomSumProduct.prototype.evaluate = function(arg) {
var sumProduct = 0;
console.log('function arg: ', arg);
var designer = GC.Spread.Sheets.Designer.findControl('gc-designer-container');
var spread = designer.getWorkbook();
var sheet = spread.getActiveSheet();
var tableRange = sheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.table);
console.log('table range: ', tableRange);
for (var i = tableRange.row + 1; i < tableRange.row + tableRange.rowCount - 1; i++) {
// 如果当前行被筛选中，则计算D列和I列的乘积并累加
if (sheet.getRowVisible(i)) {
console.log(sheet.getValue(i, 3), ' ', sheet.getValue(i, 8));
sumProduct += sheet.getValue(i, 3) * sheet.getValue(i, 8);
}
}
console.log('sum product: ', sumProduct);
return sumProduct || '#VALUE!';
};
var customSumProduct = new CustomSumProduct();
sheet.addCustomFunction(customSumProduct);

复制代码

Wilson.Zhang · Wilson.Zhang

您好！由于您较长时间未回贴，且从跟帖回复中了解到已得到有效解决方案，因此就结帖了。如有问题，欢迎继续发帖沟通。

香香 · 香香

可以直接使用SUBTOTAL(function_num,ref1,[ref2],...)，然后第一个参数传106，就可以ignores hidden rows，也就是说，隐藏行不会参与运算。

Wilson.Zhang · Wilson.Zhang

香香发表于 2024-9-23 17:34
可以直接使用SUBTOTAL(function_num,ref1,[ref2],...)，然后第一个参数传106，就可以ignores hidden rows， ...

您好！这里主要想要通过SUMPRODUCT函数计算被筛选的数据项之间的乘积和，而后对全局的订单号总和做除法。SUBTOTAL确实可以不计算隐藏行，但是对于两列乘积和这样的场景如何支持呢？我尝试了您提供的方式，可能存在操作误差，计算结果不正确，所以向您请教下。

香香 · 香香

Wilson.Zhang 发表于 2024-9-23 18:18
您好！这里主要想要通过SUMPRODUCT函数计算被筛选的数据项之间的乘积和，而后对全局的订单号总和做除法。 ...

是我弄错了，SUBTOTAL(106)计算的是两个range乘积的乘积，不是乘积的和，所以这个方法无法解决您的问题，抱歉。

Wilson.Zhang · Wilson.Zhang

香香发表于 2024-9-27 18:24
是我弄错了，SUBTOTAL(106)计算的是两个range乘积的乘积，不是乘积的和，所以这个方法无法解决您的问题， ...

好的，了解了，谢谢您提供方案。

		自动登录	找回密码
密码			立即注册

[已处理] 【17.1.3】汇总列公式筛选后sumproduct计算错误

最佳答案

9 个回复