Excel公式->如何更改SUMPRODUCT公式以跳过空单元格 [英] Excel formula -> how to change SUMPRODUCT formula to skip null cells
问题描述
上下文是 我正在使用调整后的sumproduct公式来计算加权平均值.
context is I am using adjusted sumproduct formula to calculate weighted average.
问题是 sumproduct包含数组中的空单元格. =>加权平均值计算错误.
problem is sumproduct includes empty cells in arrays. => weighted average is calculated incorrectly.
问题是 如何编辑sumproduct以排除数组中的空单元格?
question is How to edit sumproduct to exclude empty cells in arrays?
或者 还有另一种干净整洁的解决方案吗?
alternatively Is there another clean and neat solution?
推荐答案
在sumproduct中，要排除空单元格，假设您在范围A1:A100上使用它，则可以执行以下操作:
In the sumproduct, to exclude empty cells, suppose you're using it on range A1:A100, you could do the following:
= Sumproduct((A1:A100),--(A1:A100<>""))
第二个条件将确保您只查看其中具有值的单元格...
That second criteria will ensure that you're only looking at cells that have a value in them...
作为说明(A1:A100 <>>")将返回True False的数组，其中，如果单元格中有值，则返回true，否则返回false.然后，包括前面的--，它将True/False转换为1/0.因此，实际上，您要将空单元格乘以零(不包括在公式中)，将非空单元格乘以1(将它们包含在公式中).
As an explanation (A1:A100<>"") will return an array of True False, where, if there is a value in the cell, it returns true, otherwise, false. Then, including the -- before it, it converts True/False to 1/0. So, in effect, you're multiplying empty cells by a zero (excluding from the formula) and non-empty cells by 1 (including them in the formula).
--(logical statement for my array)是以许多不同方式与SumProduct()一起使用的非常有用的技巧！
The --(logical statement for my array) is a very useful trick to use with SumProduct() in MANY different ways!!
这篇关于Excel公式->如何更改SUMPRODUCT公式以跳过空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
