在Excel的sumproduct中使用通配符 [英] Using a wildcard in a sumproduct in excel

问题描述

我试图找到一种在excel中使用sumproduct的方法，但仅适用于以某个特定字符串开头的记录.

I'm trying to find a way to use sumproduct in excel but for only the records that begin with a certain character string.

基本和积

=SUMPRODUCT(BC:BC,BD:BD)/SUM(BC:BC)

是否有任何方法可以使每个范围取决于搜索条件?假设只使用BC:BC&中的记录BD:BD在BA:BA中具有以Stack开头的相应记录.我认为您不能在sumproduct中使用通配符，但是我可以使用其他功能吗?

Is there any way to make each range dependent upon a search criteria? So let's say, only use records in BC:BC & BD:BD that have a corresponding record in BA:BA that begins with Stack. I don't think you can use wildcards in sumproduct but is there another function I could be using?

谢谢.

推荐答案

您可以使用sumproduct执行此操作...诀窍是使用-运算符更改 True / False 转换为 1 / 0 ，然后您可以将其他列乘以该向量，从而仅获得语句所在的行评估为真

You CAN do this using sumproduct... The trick is to use the -- operator to change True / False to 1 / 0 and then you can multiply your other columns by that vector so as to get only the rows where your statement evaluates to true

尝试一下:

=SUMPRODUCT(--(ISNUMBER(FIND("Stack",A:A))),BC:BC,BD:BD)/SUM(BC:BC)

要说明，部分-(ISNUMBER(FIND("Stack"，A:A)))基本上说如果单词"Stack"存在于A列的单元格中，求值为1，否则求为0 -然后，您将 BC &中的值相乘 BD 以获得最终结果.

To explain, the part --(ISNUMBER(FIND("Stack",A:A))) basically says If the word "Stack" exists in the cell in column A, evaluate to 1, otherwise, evaluate to 0 - Then you multiply by the values in BC & BD to get your final result.

一个注释:

您可以使此公式的计算效率更高/更快(如果您在工作表中使用很多公式，则可以替换例如 BC:BC 以包括最大行数例如，您想要的 BC:BC 变为 BC1:BC1000 -因此不必尝试针对整个列进行计算.

You could make this formula more efficient / faster to calculate (if you're using it A LOT in your sheet, by replacing, for example, BC:BC to include the maximum number of rows you want, so, for example BC:BC becomes BC1:BC1000 - Therefore not having to try and calculate it for the entire column.

希望这会有所帮助！

这篇关于在Excel的sumproduct中使用通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！