Excel图表动态范围选择
我有一个客户对 Excel 工作表设置有一个简单但复杂的请求，但我不知道从哪里开始。我正在画一个空白。
我们有一个数据范围。示例：
Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
我在此基础上构建了一个图表。更改数据、图表更改、保护工作表以防止其他...呃...用户更改旧数据。简单的。
我想要发生的事情：当我在 Q4 下面添加下一个 Q1 时，图表“自动”选择最近的 4Q。因此，当我将数据更新为：
Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
2011Q1 7
该图表将显示过去 4 个季度的数据（2010 年第二季度至 2011 年第一季度）。目标是：将“旧”数据保留在同一张表上，但将图表更新到最近几个季度。
我在想：“固定”数据位置，反转数据（顶部的新数据），然后在每个新季度插入行：
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。等等
- 数据也位于与表格不同的工作表上。
通过：此偏移描述，我尝试做的是调整类似这样：
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/等数据的长度/位置与 A 联系起来。因此，如果我在 A 上添加一列，与 Data1/2 相关的内容会相应调整（或 3/4/5/等，它们是不同工作表上的不同图表 ）
我希望数据单元格由第一行选取，然后是偏移量以获取数据 x 列。公式的变化似乎不起作用。
我尚未解决的 1 个问题：数据未正确对齐：
“数据”始终是最后一列第二季度至上季度。最后一个季度总是空的。数据向右移动（在本例中，在 2010 年第 3 季度下 - 不在正确的列下。11 应在 2010 年第 4 季度下。9.5 应在 2010 年第 2 季度下）。
我知道我犯了一些简单的错误...
似乎正在工作。我必须更改的第一件事是 CountA - 9 （而不是 CountA - 8）。接下来是“列偏移”（0、1、2、3，...）。还要拆分一些内容，使其更加划分（我确实必须培训其他人如何满足她的报告需求）。
谢谢兰斯:)
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.
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
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
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".
EDIT:
@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: 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)
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
)
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 issue I haven't solved yet: the data is not aligning properly:
"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...
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).
Thanks Lance :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果图表与数据位于同一工作表上：
将数据 (A2) 的第一个单元格命名为命名范围，例如 TESTRANGE。
按照以下公式创建了命名范围 MYDATA：
现在，转到图表“源数据”对话框的“系列”选项卡，并将 VALUES 语句更改为：
现在，每次添加新行时，它都会更改图表。
If the chart is on the same sheet as the data:
Name the first cell of the data (A2) as a named range, say TESTRANGE.
Created a named range MYDATA as the following formula:
Now, go to the SERIES tab of the chart SOURCE DATA dialog, and change your VALUES statement to:
Now everytime you add a new row, it'll change the chart.
我知道这是一个老问题，但我想分享一个可能更简单的替代方案。
将季度数据范围更改为 Excel 表格。选择范围，然后按 Ctrl+T。在“插入表”中，确保选择了正确的数据范围，并选中“我的表有标题”，然后按“确定”。这将简单的范围转换为具有神奇属性的特殊数据结构。
然后创建一个链接到该表的最后四行的新范围，并基于该新范围创建一个图表。如下图所示。该表格是A1：B9中特殊格式的范围（您可以选择不那么直接的样式），绘图范围是D1：E5。
单元格 D2 到 D5 中的公式如下。复制 D2:D5 并粘贴到 E2:E5 以完成绘图范围中的公式。
D2: =INDEX(表1[季度],ROWS(表1[季度])-3)
D3: =INDEX(表1[季度],ROWS(表1[季度])-2)
D4: =INDEX(表1[季度],ROWS(表1[季度])-1)
D5: =INDEX(Table1[Quarter],ROWS(Table1[Quarter]))
Table1 是分配给表的名称，Quarter 是表第一列的名称（也是列标题）。您不需要输入所有这些内容，只需选择表中的列即可。当表扩展或收缩时，Table1[Quarter] 会跟踪更改。
现在添加一个新的数据点。表展开，D1:E5 中的小暂存区域链接到表的新最后四行。
当我们添加多年的数据时，公式和图表会跟上。
I know this is an old question, but I wanted to share an alternative that may be easier.
Change your Quarter-Data range to an Excel Table. Select the range, and press Ctrl+T. In the Insert Table, make sure the correct data range is selected, and that My Table Has Headers is checked, and press OK. This converts the simple range into a special data structure with magical properties.
Then make a new range which links to the last four rows of this table, and create a chart based on this new range. This is illustrated below. The table is the specially formatted range in A1:B9 (you can choose a less in-your-face style), and the plotting range is D1:E5.
The formulas in cells D2 through D5 are below. Copy D2:D5 and paste into E2:E5 to complete the formulas in our plotting range.
D2: =INDEX(Table1[Quarter],ROWS(Table1[Quarter])-3)
D3: =INDEX(Table1[Quarter],ROWS(Table1[Quarter])-2)
D4: =INDEX(Table1[Quarter],ROWS(Table1[Quarter])-1)
D5: =INDEX(Table1[Quarter],ROWS(Table1[Quarter]))
Table1 is the name assigned to the Table, and Quarter is the name of the first column of the Table (and also the column header). You don't need to type all this, just select the column in the Table. As the Table expands or contracts, Table1[Quarter] keeps track of the changes.
Now add a new data point. The Table expands, and our little staging area in D1:E5 links to the new last four rows of the table.
And as we add years worth of data, the formulas and the chart keep up.