I have a cell which wants to be a total of a table row multiplied by reference cells above the table. For example, $F$2 * F4 + $G$2 * G4 + $H$2 * H4. The table extends for about 100 columns which is why I'd like an automatic way to do this (also in case the table ever expands).
我有一个单元格，它希望是表行的总和乘以表上面的引用单元格。例如，$F$2 * F4 + $G$2 * G4 + $H$2 * H4。这个表扩展了大约100个列，这就是为什么我想要一个自动的方法来做这个(同样在表不断扩展的情况下)。
To clarify, I know my example works, but I want an automatic way (where I can specify two rows, like f2:bz2 as the 'reference' row and f4:bz4 as the other row) to do this. Since the second row is in a table, it would be ideal if the solution allowed for adding another column to the table too.
为了澄清这一点，我知道我的示例是有效的，但是我希望使用一种自动的方法(我可以指定两行，比如f2:bz2作为“引用”行，f4:bz4作为另一行)来实现这一点。由于第二行在表中，如果解决方案允许向表中添加另一列，那么这将是理想的。
2 个解决方案
#1
1
If you want something that expands automatically with your table, may be this:
如果你想要一个随你的桌子自动展开的东西，可以是:
=SUMPRODUCT(Table1[@]*OFFSET(Table1[#Headers],-1,0))
which assumes that your fixed coefficients are one row above the headers of the table. But it also assumes that you're entering the formula on the same row that you want to process. Unfortunately, if you have the formula to process a different row, you will need to wotk with normal ranges, such as the formula suggested by @pnuts in the comments section. But then it will not scale automatically as your table grows (unless you expand it by insertion of columns).
假设固定系数是表头上方的一行。但它也假设你输入的公式和你想处理的行是一样的。不幸的是，如果您有处理不同行的公式，那么您将需要使用正常范围的wotk，如注释部分中@pnut建议的公式。但是，当您的表增长时，它不会自动伸缩(除非您通过插入列来扩展它)。
#2
0
If you use:
如果你使用:
=SUMPRODUCT(($F$2:$XFD$2)*(F4:XFD4))
You will both:
你都将:
- avoid REALLY long formulas
- 避免很长的公式
- have a formula that automatically incorporates new columns of data as you add them
- 是否有一个公式在添加数据时自动合并新的数据列
#1
1
If you want something that expands automatically with your table, may be this:
如果你想要一个随你的桌子自动展开的东西，可以是:
=SUMPRODUCT(Table1[@]*OFFSET(Table1[#Headers],-1,0))
which assumes that your fixed coefficients are one row above the headers of the table. But it also assumes that you're entering the formula on the same row that you want to process. Unfortunately, if you have the formula to process a different row, you will need to wotk with normal ranges, such as the formula suggested by @pnuts in the comments section. But then it will not scale automatically as your table grows (unless you expand it by insertion of columns).
假设固定系数是表头上方的一行。但它也假设你输入的公式和你想处理的行是一样的。不幸的是，如果您有处理不同行的公式，那么您将需要使用正常范围的wotk，如注释部分中@pnut建议的公式。但是，当您的表增长时，它不会自动伸缩(除非您通过插入列来扩展它)。
#2
0
If you use:
如果你使用:
=SUMPRODUCT(($F$2:$XFD$2)*(F4:XFD4))
You will both:
你都将:
- avoid REALLY long formulas
- 避免很长的公式
- have a formula that automatically incorporates new columns of data as you add them
- 是否有一个公式在添加数据时自动合并新的数据列