提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中输入新数据后自动更新图表?

假设您已经创建了一个图表来跟踪基于工作簿中数据范围的每日销售额。 但是,您需要每天更改或编辑数据的数量,在这种情况下,您必须手动更新图表,使其包含新数据。 在Excel中将新数据添加到现有图表范围时,是否有任何快速技巧可以帮助您自动更新图表?

在创建表格后输入新数据后自动更新图表

使用动态公式输入新数据后自动更新图表


选项卡式浏览和编辑多个Excel工作簿/ Word文档,如Firefox,Chrome,Internet浏览10!

您可能熟悉在Firefox / Chrome / IE中查看多个网页,并通过轻松单击相应的选项卡在它们之间切换。 此处,Office选项卡支持类似的处理,允许您在一个Excel窗口或Word窗口中浏览多个Excel工作簿或Word文档,并通过单击其选项卡轻松切换它们。 点击Office Tab的免费45天试用版!

擅长
Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

箭头蓝色右泡 在创建表格后输入新数据后自动更新图表

如果您有以下范围的数据和柱形图,那么现在您希望在输入新信息时自动更新图表。 在Excel 2007,2010或2013中,您可以创建一个表来扩展数据范围,并且图表会自动更新。 请这样做:

DOC-更新chart1

1。 选择数据范围并点击插页 标签,看截图:

DOC-更新chart2

2。 在 创建表 对话框中,如果您的数据有标题,请检查 我的表有标题 选项,然后单击 OK。 看截图:

DOC-更新chart3

3。 数据范围被格式化为表格,请参阅截图:

DOC-更新chart4

4。 现在,当您为6月添加值时,图表将自动更新。 看截图:

DOC-更新chart5

备注:

1。 您的新输入数据必须与上述数据相邻,这意味着新数据与现有数据之间不存在空白行或列。

2。 在表中,您可以在现有值之间插入数据。


箭头蓝色右泡 使用动态公式输入新数据后自动更新图表

但有时,您不希望将范围更改为表格,并且上述方法在Excel 2003或更低版本中不可用。 在这里我可以为你介绍一个复杂的动态公式方法。 以下面的数据和图表为例:

DOC-更新chart6

1。 首先,您需要为每列创建一个定义的名称和一个动态公式。 点击 公式 > 定义名称.

2。 在 新名字 对话框中,输入 日期名字 框,并从中选择当前工作表名称 范围 下拉列表,然后输入 = OFFSET($ A $ 2,0,0,COUNTA($ A:$ A)-1) 公式进入 框中,看截图:

DOC-更新chart7

3。 点击 OK,然后重复上述两个步骤,可以使用以下范围名称和公式为每个系列创建一个动态范围:

  • B列:Ruby:= OFFSET($ B $ 2,0,0,COUNTA($ B:$ B)-1);
  • C列:James:= OFFSET($ C $ 2,0,0,COUNTA($ C:$ C)-1);
  • D栏:Freda:= OFFSET($ D $ 2,0,0,COUNTA($ D:$ D)-1)

注意:在上面的公式中, OFFSET 函数指的是第一个数据点,和 COUNTA 指的是整列数据。

4。 为每个列数据定义名称和公式后,右键单击图表中的任意列,然后选择 选择数据,看截图:

DOC-更新chart8

5。 在 选择数据源 对话框,从 传奇入围(系列) 部分,点击 红宝石 然后单击 编辑 按钮,在弹出 编辑系列 对话框中输入 = Sheet3!红宝石系列值 部分,看截图:

DOC-更新chart9
-1
DOC-更新chart10

6. 然后点击 OK 回到了 选择数据源 对话框,重复步骤5以更新剩余系列以反映其动态范围:

  • James:系列值:= Sheet3!James;
  • Freda:系列值:= Sheet3!Freda

7。 设置完左侧数据后,现在需要点击 编辑 按钮下 水平(类别)轴标签 要设置此选项,请参阅屏幕截图:

DOC-更新chart11
-1
DOC-更新chart12

8。 然后点击 OK > OK 关闭 选择数据源 对话框,完成这些步骤后,您会发现在向工作表添加新数据时图表会自动更新。

DOC-更新chart13

备注:

  • 1。 您必须以连续的方式输入新数据,如果您跳过行,则此方法无法按预期工作。
  • 2。 如果输入新的列数据,则此方法不会生效。

提示。如果您想快速将范围内容从图纸导出到图形,请尝试使用Kutools for Excel 导出范围为图形 如以下屏幕截图所示。 它在60天没有限制的全功能,请立即下载并免费试用。

将工作簿中的图形(图片/图表/形状/所有类型)导出为Gif / Tif / PNG / JPEG文件夹

如果工作簿中有多种类型的图形,并且您只想将工作表中的所有图表导出到文件夹中作为其他类型图片的gif,则可以使用Kutools for Excel的Export Graphics utilty,它只需要3步骤来处理工作。 点击查看功能齐全的60天免费试用版!
doc导出图形
Kutools for Excel:拥有超过300便利的Excel加载项,可以在60天免费试用,不受限制。

相关文章:

如何在Excel中添加水平的平均线图表?

如何在Excel中创建组合图表并添加辅助轴?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mufaddal · 4 months ago
    HI,
    This trick doesnt work if you want to add new columns. This trick is for fixed columns. What if there is a data yearwise in columns and every year a new year column is added so how will it get added to the chart ??

    Any idea you can suggest ??

    Cheers,

    Mufaddal
  • To post as a guest, your comment is unpublished.
    MANISH · 4 months ago
    when I type in cell any no. like - 210 it reflect as = 2.10 , no formula is taking place why? any no. shows devide by 100 as I mentioned , decimal comes automatically how to resolve this ?


  • To post as a guest, your comment is unpublished.
    TB · 2 years ago
    It works well! Thank you!
    One question:
    what if my drop down list is not on the same sheet with the "table" where i have datas?
    how can I modify the function?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Thanks for the message. But I do not get your question clearly, why don't you upload a screenshot of your problem for me to understand easily? More description, easier to understand. Thank u.
    • To post as a guest, your comment is unpublished.
      chris · 1 years ago
      did you manage to solve this issue? having same issue at the moment
  • To post as a guest, your comment is unpublished.
    Kalishwaran A · 2 years ago
    Airtel mobile Bill Payment
  • To post as a guest, your comment is unpublished.
    jasmith4 · 2 years ago
    A chart's ranges can depend on names or not. If a chart range depends on, say A1:E5, and you insert a row at row 3, and a column at column C, the chart will automatically depend on A1:F6. Similarly, if you have a name defined as A1:E5, whether you use it as a chart's range or not, and you insert a column and row at C3, the name's definition will expand to A1:F6. But in either case, if you insert a column and A or E (the endpoints), or a row at 1 or 5, the behavior isn't so well defined: maybe the chart range or name's definition will expand; maybe it won't. To answer Melissa, you'd have to insert rows or columns before or to the left of the chart range.

    I think it's better to use names, because names can be defined using formulas that involve OFFSET, COUNT, INDEX, MATCH, whatever else. So the better answer to Melissa is to define a name for one cell, namely the last of the eight columns or rows, then define the name in term of an OFFSET from that cell: OFFSET(cell,-8,-8,8,8) to go back and up eight cells and use an 8x8 range.
  • To post as a guest, your comment is unpublished.
    Andy · 2 years ago
    Great reminder as I don't do these kind of chart functions that often.
    As an improvement you could mention how to edit defined names in the name manager but I'm not sure if the editing actually enables the function of the adding rows to work completely
    • To post as a guest, your comment is unpublished.
      jasmith4 · 2 years ago
      You can define chart ranges with names or not -- in either case if you physically insert rows or columns in the middle of a range, it automatically expands.

      I think it's best to use names for charts and lots of other things, because you can define names as formulas, not just straight ranges. You must use OFFSET (which resizes too), because that returns a range, but its parameters, which are numbers, can be specified with formulas that use INDEX, MATCH, COUNT, SUM, VLOOKUP, any crazy formula you want. Melissa, that's the best way to handle your situation: give a name to one bookmark cell, then define another name to be offset from that -8 rows or columns, and resize it 8 rows or columns.
  • To post as a guest, your comment is unpublished.
    mnmuhaimin · 3 years ago
    Hi there, thank you so much for this tutorial.
    I am just wondering, how to apply this method to the data that updated in the column not in the row? is there any additional changes that I need to do besides offset formula? thank you.
  • To post as a guest, your comment is unpublished.
    Mohammad_Hosein · 3 years ago
    salaam,
    the problem still exists.
    i have main data in one sheet and the linked data (for the chart) in another sheet.
    so when main data changes the chart can't be updated
    :(
  • To post as a guest, your comment is unpublished.
    melissa · 3 years ago
    This is GREAT! However, I need my graph to sho the last 8 quarter of data, not just keep adding more quarters. Is there a way to do that?
  • To post as a guest, your comment is unpublished.
    Jennifer M · 4 years ago
    You should probably add the setting piece to updating graphs. By going under file, options, Formulas, automatic update data.

    Thanks, Jenn
  • To post as a guest, your comment is unpublished.
    jasmith4 · 4 years ago
    The table method allows you to add both categories and series dynamically. Is there any way to do that with the formula method without having to define a name for each series?
  • To post as a guest, your comment is unpublished.
    Donald benny · 5 years ago
    Hi All,

    How to update the raw data having 28 tabs in template which also contains 28 tabs by using shortcut methods.

    Please suggest on this.
  • To post as a guest, your comment is unpublished.
    Jenna · 5 years ago
    Thank you! This is great. However I'm having hard time adjust these steps for my situation - I have dates going horizontally (flipped table), so every week I would be adding numbers on the right vs on the bottom. I tried adjusting the formula so that instead of COUNTA($A:$A) or COUNTA($B:$B) that stipulated the column, I have COUNTA($1:$1) or COUNTRA($2:$2) but that didn't work. Any advice?
  • To post as a guest, your comment is unpublished.
    Paula · 5 years ago
    what about adding additional data to the table so that it updates the charts? I am having a very difficult time with this. The charts are not holding the full column data ranges but revert to specific cell locations so new data does not automatically appear.
  • To post as a guest, your comment is unpublished.
    Paulm · 5 years ago
    Thanks for this. You've speeded up my monthly reporting and generation of all those "absolutely necessary" KPI's