结构化引用:绝对和相对寻址 [英] Structured References: Absolute and Relative addressing

问题描述

这是一个加权平均"公式，其中 Q14:Q21 包含权重"；C列是第一列数据，后面的列是D:P

This is a "weighted average" formula where Q14:Q21 contains the "weights"; Column C is the first column of data, and subsequent columns are D:P

我可以使用这个公式，并正确填写.数据列将发生变化，而权重"列保持不变.

I can use this formula, and fill right. The data columns will change and the "weight" column remain the same.

=SUMPRODUCT(Sheet1!C$14:C$21,Sheet1!$Q$14:$Q$21)/SUM(Sheet1!$Q$14:$Q$21)

我想将数据更改为表格"，以便能够使用结构化引用.权重"列标题为 Percent，数据列是以 2000 开头的年份数字.

I would like to change the data to a "table" so as to be able to use structured references. The "weights" column header is Percent, and the data columns are year numbers starting with 2000.

有没有什么办法可以在单个单元格中输入公式的结构化引用"形式，如下图，然后右填，只改变第一列(2000")?

Is there any way of entering the "Structured Reference" form of the formula, as shown below, in a single cell, and then fill right with only the first column ("2000") changing?

=SUMPRODUCT(Table3[2000],Table3[Percent])/SUM(Table3[Percent])

我查看了 Excel HELP 并尝试在 Google 上搜索，但没有成功.

I've looked at Excel HELP and also tried searching on Google, with no luck.

推荐答案

请尝试:

=SUMPRODUCT(Table3[2000],Table3[[Percent]:[Percent]])/SUM(Table3[[Percent]:[Percent]])

这篇关于结构化引用:绝对和相对寻址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！