Excel图表动态范围选择 [英] Excel chart dynamic range-selection
问题描述
我有一个客户端，有一个简单而复杂的Excel表单设置的请求，我不能为世界的事情从哪里开始。我正在绘制一个空白。
I have a client that has a simple yet complicated request for an excel sheet setup, and I can't for the world thing of where to start. I'm drawing a blank.
我们有一个数据范围。示例：
We have a data range. Example:
Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
我有一个图表。更改数据，更改图表，保护工作表，以防止其他用户更改旧数据。简单。
I have a chart built on top of that. Change data, chart changes, protect worksheet to keep other idi... er... users from changing old data. Simple.
我想要发生什么：当我添加Q4下的下一个Q1时，图表自动选择最近的4Q。因此，当我将数据更新为：
What I want to have happen: When I add the next Q1 below Q4, the chart "automagically" selects the most recent 4Q. So when I update the data to:
Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
2011Q1 7
图表将显示过去4个季度（2010Q2至2011Q1）。目标是：在同一工作表上保留旧数据，但将图表更新到最近的季度。
The chart will show data for the last 4 quarters (2010Q2 thru 2011Q1). The goal being: keep "old" data on the same sheet, but have the charts update to most recent quarters.
我在想：固定的数据位置，反转数据（新数据在顶部），只需在每个新季度插入行：
I'm thinking: "fixed" data locations, reverse the data (new data at top), and just insert row each new quarter:
Quarter Data
2011Q2 9
2011Q1 7
2010Q4 1
2010Q3 4
2010Q2 3
2010Q1 1
但是这将涉及到已经存在的excel表的很多变化，我希望可能有一个更容易/更好的修复。
But this will involve a lot of changes to the already existing excel sheets and I was hoping that there may be an easier/better "fix".
编辑：
@ Lance Roberts〜运行你的建议：
- 更多细节...数据设置列信息在A中，但多个表的数据在B +中。表1是B / C。表2为D / E。等等。
- 数据也与表格不同。
@Lance Roberts ~ Running with your suggestion:
- Little more detail... The data is setup such that the column information is in A, but data for multiple tables are in B+. Table 1 is B/C. Table2 is D/E. Etc.
- Data is also on a different sheet than the tables.
Going by：此偏移说明，我尝试做的是类似于以下调整：
Going by: This Offset Description, what I've tried doing is adjusting similar to such:
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)
目标是绑定长度/所以如果我在A上添加一个列，那么绑定到Data1 / 2的东西相应地调整（或3/4/5 / etc，它们是不同图表上的不同图表
）
Goal being to tie the length/location for B/C/etc data to A. So if I add a column on A, stuff tied to Data1/2 adjust accordingly (or 3/4/5/etc, which are different charts on different sheets )
我想要数据单元格被第一行拾取，然后是一个偏移数字来获取数据x列。公式上的变化似乎不工作。
I want data cells to be picked by the first row, and then an offset number to get data x columns over. Variations on the formula don't seem to be working.
1个问题我尚未解决：数据未正确对齐：
1 issue I haven't solved yet: the data is not aligning properly:
数据始终为第2到最后一个季度的最后一列。上个季度总是空的。数据向右移动（在这个例子中，在3Q10以下 - 不在正确的列下，11应在4Q10之下。9.5应在2Q10之下。）
"Data" is always, last column under 2nd to last Quarter. Last quarter is always empty. Data is shifting to the right (in this example, under 3Q10 - NOT under the correct column. 11 should be under 4Q10. 9.5 should be under 2Q10).
我遇到了一些简单的错误...
I know I'm getting something simple wrong...
看起来工作正常。我不得不改变的第一件事是CountA - 9（不是CountA - 8）。接下来是列偏移（0,1,2,3，...）。
Seems to be working. First thing I had to change was CountA - 9 (not CountA - 8). Next was the "column offset" (0, 1, 2, 3,...). Also split some stuff up to make it more compartmentalized (I do have to train someone else how to do this for her reporting needs).
感谢Lance：）
推荐答案
如果图表与数据在同一张纸上：
If the chart is on the same sheet as the data:
第一个单元格的数据（A2）作为命名范围，说TESTRANGE。
按以下公式创建命名范围MYDATA：
Name the first cell of the data (A2) as a named range, say TESTRANGE. Created a named range MYDATA as the following formula:
=OFFSET(TESTRANGE, COUNTA($A:$A) - 5, 0, 4, 2)
现在，转到图表的SERIES选项卡SOURCE DATA对话框，并将您的VALUES语句更改为：
Now, go to the SERIES tab of the chart SOURCE DATA dialog, and change your VALUES statement to:
=Sheet1!MYDATA
现在每次添加新行时，它都会更改图表。
Now everytime you add a new row, it'll change the chart.
这篇关于Excel图表动态范围选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
