☰

Excel：可更改起点的自动更新图表

tag-icon • tag-icon microsoft-excel
Excel：可更改起点的自动更新图表

我已经将 Excel 连接到自动更新数据库，但现在我想更进一步，让所有必要的报告项目也自动化。不仅如此 - 我还想让报告看起来可以通过几个值轻松控制。

所以这就是问题的核心。

假设我有以下形式的数据：

2012    I   3.6
    II  3.4
    III 3.3
    IV  2.8
    V   2.2
    VI  1.9
    VII 1.7
    VIII    1.7
    IX  1.9
    X   1.6
    XI  1.5
    XII 1.6
2013    I   0.6
    II  0.3
    III 0.2
    IV  -0.3
    V   -0.1
    VI  0.2
    VII 0.3
    VIII    -0.2
    IX  -0.4
    X   -0.1
    XI  -0.4
    XII -0.4

从这里我可以使用以下名称管理器创建一个对象（示例）：

=OFFSET(CPI!$C$2;0;0;COUNT(CPI!$C$2:$C$145))

这将计算所有包含某些值的单元格，并在添加值时自动更新引用名称对象的图表。但我希望 OFFSET() 和 COUNT() 中的 $C$2 引用都是可控制的。我的意思是，举个例子，我有一个单元格 $X$1，其中的值为 2012，它决定了名称对象的起点，因此决定了引用它的图表。

我确实知道如何通过使用 VLOOKUP() 匹配单元格值来引用特定单元格，但它返回的是单元格的值，而不是坐标。因此它毫无用处（至少我认为如此）。

答案1

另一种方法如下，我使用自己的值来测试：

注意：你需要根据你的系统将逗号替换为分号

使用命名范围公式和下面的设置将从您选择的年份开始命名范围，并一直持续到数据底部（或C1000，如果这先发生）

=OFFSET(Sheet1!$C$1,MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)-1,,COUNTA(INDIRECT("C"&MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)&":C1000")))

请注意C1000公式末尾的 -C15000如果您有更多数据，您可以这样做或类似操作。

在此处输入图片描述

方法如下：

OFFSET(
Start: C1
Rows: Find year using MATCH() formula and move down this many cells -1
Columns: N/A
Height: INDIRECT effectively creates the range between Cx, where x is the row of the 
        selected year and C1000. Then using COUNTA over this range gives the required 
        height of the range
Width: N/A
)

答案2

我建议使用数据透视图

稍微不同地设置您的数据（如下所示）并将以下字段拖入数据透视图中。

Filter: Year
Row Values: RN
Values: Value

在此处输入图片描述

按您需要的年份进行筛选

相关内容