Excel公式->如何更改SUMPRODUCT公式以跳过空单元格

本文介绍了Excel公式-&gt;如何更改SUMPRODUCT公式以跳过空单元格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 上下文是我正在使用调整后的sumproduct公式来计算加权平均值.context isI am using adjusted sumproduct formula to calculate weighted average. 问题是sumproduct包含数组中的空单元格. =>加权平均值计算错误.problem issumproduct includes empty cells in arrays. => weighted average is calculated incorrectly. 问题是如何编辑sumproduct以排除数组中的空单元格?question isHow to edit sumproduct to exclude empty cells in arrays? 或者还有另一种干净整洁的解决方案吗?alternativelyIs 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公式-&gt;如何更改SUMPRODUCT公式以跳过空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！