代码之家 › 专栏 › 技术社区 › WernerCD

Excel图表动态范围选择

worksheet-function offset excel-2007 charts excel

2

WernerCD · 技术社区 · 14 年前

我有一个客户机对Excel工作表设置有一个简单而复杂的请求,我不能从哪里开始。我在画空白。

我们有一个数据范围。例子:

Quarter     Data
2010Q1       1
2010Q2       3
2010Q3       4
2010Q4       1

我在上面有一张图表。更改数据、图表更改、保护工作表以保留其他IDI…呃。。。更改旧数据的用户。简单。

我希望发生的是:当我将下一个Q1添加到Q4以下时,“自动”图表将选择最近的4Q。因此,当我将数据更新为:

Quarter     Data
2010Q1       1
2010Q2       3
2010Q3       4
2010Q4       1
2011Q1       7

图表将显示过去4个季度(2010年第2季度至2011年第1季度)的数据。目标是:将“旧”数据保持在同一张工作表上,但将图表更新到最近的季度。

我在想:“固定”数据位置,反转数据(顶部的新数据),然后在每个新季度插入行:

Quarter     Data
2011Q2       9
2011Q1       7
2010Q4       1
2010Q3       4
2010Q2       3
2010Q1       1

但这将涉及对现有Excel工作表的大量更改,我希望有一个更容易/更好的“修复”。

编辑: @Lance Roberts~按照你的建议跑步:
-更多细节…数据的设置使列信息在A中,但多个表的数据在B+中。表1为B/C。表2为D/E等。
-数据也与表位于不同的工作表上。

路过: This Offset Description ,我尝试做的是类似的调整:

NAME       FORMULA OFFSET(range, rows, columns, height, width )
DATA0      =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 0, 8, 1)
DATA1      =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 1, 8, 1)
DATA2      =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 2, 8, 1)

目标是将B/C/ETC数据的长度/位置绑定到A。因此,如果我在A上添加一列,绑定到数据1/2的内容将相应地进行调整(或3/4/5/ETC,这是不同工作表上的不同图表) )

我希望数据单元格由第一行选择,然后是一个偏移量来获取数据x列。公式上的变化似乎不起作用。

1.我还没有解决的问题:数据没有正确对齐: Example

“数据”始终是第二季度至最后一季度的最后一列。最后一个季度总是空的。数据向右移动(在本例中,在3q10下-不在正确的列下)。11应该低于4Q10。9.5应低于2q10)。

我知道我有个简单的错误…

alt text

似乎在工作。我要改变的第一件事是伯爵-9(不是伯爵-8)。接下来是“列偏移量”(0,1,2,3,…)。同时,把一些东西分开,使其更具条理(我必须培训其他人如何做到这一点,以满足她的报告需求)。

谢谢兰斯:)

2 回复 | 直到 8 年前

1

3

Lance Roberts 14 年前

如果图表与数据位于同一工作表上:

将数据(a2)的第一个单元格命名为命名范围,比如testrange。已创建命名范围mydata作为以下公式:

=OFFSET(TESTRANGE, COUNTA($A:$A) - 5, 0, 4, 2)

现在,转到“图表源数据”对话框的“系列”选项卡,并将“值”语句更改为:

=Sheet1!MYDATA

现在,每次添加新行时,都会更改图表。

2

0

Keshan Nageswaran 8 年前

我知道这是一个古老的问题,但我想分享一个更简单的选择。

将季度数据范围更改为Excel表。选择范围,然后按 Ctrl键 + T . 在“插入表”中,确保选择了正确的数据区域,并且选中了“我的表”的标题,然后按“确定”。这将简单范围转换为具有神奇属性的特殊数据结构。

然后创建一个链接到此表最后四行的新范围,并基于此新范围创建图表。如下所示。表格是A1:B9中的特殊格式范围(您可以在人脸样式中选择一个较小的),打印范围是D1:E5。

单元格d2到d5中的公式如下。复制d2:d5并粘贴到e2:e5中,完成我们绘图范围内的公式。

d2:=索引(表1[季度],行(表1[季度])-3) d3:=索引(表1[季度],行(表1[季度])-2) d4:=索引(表1[季度],行(表1[季度])-1) d5:=索引(表1[季度],行(表1[季度])

Table1是分配给表的名称,Quarter是表的第一列的名称(也是列标题)。您不需要键入所有这些内容,只需选择表中的列。当表扩展或收缩时,表1[季度]跟踪更改。

现在添加一个新的数据点。表展开,d1:e5中的小临时区域链接到表的最后四行。

当我们加上几年的数据时,公式和图表会保持一致。