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

or

如何在Excel中创建动态交互式图表?

在Excel中,如果您基于范围数据系列创建了多个图表,并且希望使图表看起来很漂亮和干净。 为此,您可以在工作表中创建动态交互式图表,当您选择一个选项时,相应的图表将显示为以下屏幕截图。 在这里,我将介绍两种类型的交互式图表:使用下拉菜单的交互式图表和使用选项按钮的交互式图表。

使用下拉菜单创建动态交互式图表

使用选项按钮创建动态交互式图表

DOC-交互式charts2-2,2 DOC-交互式charts23-23

将多个工作表或csv文件组合/导入到一个工作表或工作簿中:

在您的日常工作中,将多个工作表,工作簿和csv文件合并到一个工作表或工作簿中可能是一项巨大而头痛的工作。 但是,如果你有 Kutools for Excel,其强大的功能 - 结合,您可以将多个工作表,工作簿或csv文件快速合并到一个工作表或工作簿中。

doc结合了多个工作表-1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


使用下拉菜单创建动态交互式图表


如果您希望更改图表并从下拉列表中选择该项目,则可以使用以下步骤解决此任务:

首先,您需要为单独的图表创建一些范围名称。

1。 创建你想要的所有图表并将其整齐地放置在工作表中,如下所示:

DOC-交互式charts3

2。 然后选择第一个图表所在的所有范围单元格,然后单击 公式 > 定义名称,看截图:

DOC-交互式charts4 -2 DOC-交互式charts5

3。 在 新名字 对话框中,为此图表指定一个范围名称(例如Chart1)并选择 工作簿 来自 范围 下拉列表,看截图:

DOC-交互式charts3

4。 点击 OK,然后重复step2和step3为其他每个图表(如Chart2,Chart3 ...)提供范围名称...

其次,为交互式图表创建一个组合框。

5。 在空白区域单元格中,键入您的图表名称,如下所示:

DOC-交互式charts3

6。 然后通过单击将组合框插入工作表 开发人员 > 插页 > 组合框 (如果 开发人员 标签不显示在功能区中,请点击 文件 > 选项 > 自定义功能区 去检查 开发人员 将其显示在功能区上),然后拖动鼠标以绘制一个 组合框 在工作表中,看截图:

DOC-交互式charts8 -2 DOC-交互式charts9

7。 然后右键点击 组合框,然后选择 格式控制 从上下文菜单。

DOC-交互式charts3

8。 在 格式对象 对话框中,单击 德国国际质量控制及仪器仪表展 选项卡,然后单击 DOC-按钮1 按钮 输入范围 突出显示您在Step5中列出的图表名称,然后单击 DOC-按钮1 按钮 单元格链接 选择组合框旁边的空白单元格。 看截图:

DOC-交互式charts3

9。 然后点击 OK,现在,当您从组合框中选择一个项目时,链接的单元格将显示链接的数字。

DOC-交互式charts3

10。 然后选择链接单元N2,然后单击 公式 > 定义名称新名字 对话框中,输入链接单元格的范围名称,然后键入此公式 =选择($ N $ 2,Chart1,Chart2,Chart3) 字段,(N2 是链接的单元格引用,和 Chart1,Chart2,Chart3 是您在前面的步骤中创建的图表范围名称。)并单击 OK 要完成此选项,请参阅以下屏幕截图:

DOC-交互式charts3

11。 然后选择chart1范围单元并按 按Ctrl + C 复制范围图表,然后指定一个单元格并右键单击,选择 选择性粘贴 > 链接的图片 图标,看截图:

DOC-交互式charts3

12。 现在Chart1的链接图片已经插入指定的位置,然后点击这个图表图片,然后输入这个公式 = Selectedchart (Selectedchart 是您在Step10中创建的链接单元格的范围名称)输入到编辑栏中,然后按 输入 键。 看截图:

DOC-交互式charts3

13。 现在,您的动态交互式图表已经成功创建。 当您从组合下拉列表中选择一个项目时,图表将自动切换。

DOC-交互式charts4 -2 DOC-交互式charts5

演示:使用下拉菜单创建动态交互式图表


使用选项按钮创建动态交互式图表

有时您想使用选项按钮选择相关图表,以下步骤可帮助您使用选项按钮创建交互式图表。

1。 为图表范围创建范围名称的过程与上述Step1到Step4的过程相同。

2。 然后插入选项按钮并创建交互式图表,如下所示:

(1。) 点击 开发人员 > 插页 > 选项按钮 然后拖动鼠标绘制一个选项按钮,然后右键单击并选择 编辑文字 从上下文菜单中查看屏幕截图:

DOC-交互式charts8 -2 DOC-交互式charts9

(2。) 然后输入您的选项按钮的标签。

DOC-交互式charts3

(3。) 然后通过重复上述步骤(1)和(2)来绘制其他选项按钮并根据需要更改标签,请参阅以下屏幕截图:

DOC-交互式charts3

(4。) 然后选择一个选项按钮并右键单击,然后选择 格式控制,看截图:

DOC-交互式charts3

(5。) 格式对象 对话框中,单击 德国国际质量控制及仪器仪表展 选项卡,然后单击 DOC-按钮1 按钮 单元格链接 选择选项按钮旁边的空白单元格。

DOC-交互式charts3

(6。) 然后点击 OK,现在当您单击某个选项按钮时,链接的数字1,2或3将出现在您选择的单元格中。

(7。) 然后选择链接的单元格M19,并通过单击给出它的范围名称 公式 > 定义名称新名字 对话框中,指定一个名称并键入此公式 =选择($ M $ 19,Chart1,Chart2,Chart3) 文本框。 (M19 是链接的单元格引用,和 Chart1,Chart2,Chart3 是在前面的步骤中创建的图表范围名称。)然后单击 OK。 看截图:

DOC-交互式charts3

(8。) 然后复制一个图表范围,并将其作为链接的图片粘贴到一个位置,然后选择图表图片,输入此公式 = Selectedchart (Selectedchart 是您创建的链接单元格的范围名称)添加到编辑栏中,然后按 输入 键。 而现在当你点击一个选项按钮时,相关的图表就会显示出来。

DOC-交互式charts4 -2 DOC-交互式charts5

演示:使用选项按钮创建动态交互式图表


相关文章:

如何在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.
    akshay · 1 years ago
    it will not work,its just copying & pasting as a image,you cant change its contents or cant use it as real graph
  • To post as a guest, your comment is unpublished.
    ian · 2 years ago
    i follow all the steps and on the last step i got an error saying Reference is not valid, i am using excel 2010
  • To post as a guest, your comment is unpublished.
    Charlie · 2 years ago
    Wow! thank you so much for this. I was able to create interactive charts following your instructions. I was so happy. :)
  • To post as a guest, your comment is unpublished.
    Salamay · 3 years ago
    Would this work with Pivot Charts? I crash excel when I paste the copied range as image.
  • To post as a guest, your comment is unpublished.
    Mohammed · 3 years ago
    I cant get past step 12, it say, "reference not valid"
  • To post as a guest, your comment is unpublished.
    Todd · 3 years ago
    This worked great for me, but for some reason, one of my 5 graphs compresses and doesn't display properly. There are 5 charts, 4 work. Chart 4 is not readable as it is compressed into one tiny row. Any suggestions as to what may be wrong? Thanks.
  • To post as a guest, your comment is unpublished.
    Frank · 3 years ago
    Works perfectly. But please verify: does this work in versions of Excel earlier than 2013? If it doesn't, then what could the workaround be? Thanks.
  • To post as a guest, your comment is unpublished.
    Jasmeet · 3 years ago
    Great tutorial ! Been using this for one year!!

    One question, How to make 2 charts dynamic together connected to one radio button?
  • To post as a guest, your comment is unpublished.
    Tom · 3 years ago
    For me this gave an error =Choose($N$2,Chart1,Chart2,Chart3)
    I replaced it with =Choose($N$2;Chart1;Chart2;Chart3)

    Thank you for this tutorial. It makes sheets look neat.
  • To post as a guest, your comment is unpublished.
    Candice · 3 years ago
    Yes, this is what I'm looking for as well.
    If we use a picture of graph (linked picture), it shows picture only. However, for a real graph, if we put our mouse on it, it will show the sources and other available option to adjust the graph. Have you found a way to do this?
  • To post as a guest, your comment is unpublished.
    Erin · 4 years ago
    Is it possible to do this, but show the graph itself rather than a picture of the graph?
  • To post as a guest, your comment is unpublished.
    SAJEESH · 4 years ago
    Sir,

    Excellent Work, but i am facing issue with after closing and reopening the excel sheet - While selecting the combo box, only image is showing not showing the graph.

    Thanks and Regards
    SAJEESH - P
  • To post as a guest, your comment is unpublished.
    SAJEESH · 4 years ago
    Sir,

    Excellent work but when i closed the excel and reopened it - The graph which i called using the combo box is not working. It doesn't shows the GRAPH only the blank image.
  • To post as a guest, your comment is unpublished.
    TONI · 4 years ago
    Worked very well ,

    thnx....
  • To post as a guest, your comment is unpublished.
    Gene Monroe · 4 years ago
    I found that when I received "reference not valid" errors, it was (sometimes) because the worksheet name was not included prior to the name range. For example, if I have a named range for a data set ChlyScrnTotX, which refers to a dynamic range on a sheet named "Table" as =OFFSET($B$80,0,0,1,COUNTA($80:$80)), when I entered the named range in the Source Data for a graph on a different page of the workbook, I received the "reference not valid" error. When I re-wrote the named range to include the sheet name, as follows, the error was resolved.

    =OFFSET(Table!$B$80,0,0,1,COUNTA(Table!$80:$80))

    Good luck!
  • To post as a guest, your comment is unpublished.
    Dianne · 4 years ago
    Yes there is a step missing.
    At Step 12 --> before you name the linked picture --> Name the range you pasted it in (using the same name).
    E.g. if you pasted the linked picture in A1:A5, highlight those rows and name them "SelectedChart"
    Then click the chart image as shown in Step 12 and name it "SelectedChart".
    Both the range and the image have to have the same name.
    This worked for me. Hopefully it does for you.
    • To post as a guest, your comment is unpublished.
      matan · 1 years ago
      Hi dianne,
      Its shows me an error when I name the range with name that already exsists.
  • To post as a guest, your comment is unpublished.
    Gene Monroe · 4 years ago
    I followed the directions precisely, and it works just fine. Double-check the spelling of your named ranges, etc. I had to make a couple of minor corrections to my names and formulas to ensure everything matched up. Would suggest keeping a seperate document in Word listing your name ranges and their formulas. That way, you can make changes in Word and copy/paste them into the Name Manager, eliminating any small typos that come with manual entry. The most common cause of my mistake was that my chart names did not match up precisely. Name, case, everything must be identical.
  • To post as a guest, your comment is unpublished.
    Ravi · 4 years ago
    As per above, Step 12 results in an error message - how do we get across that hurdle?
  • To post as a guest, your comment is unpublished.
    DMC · 5 years ago
    This simple approach seems to be a great solution for basic data and only a handful of potential views. However, if you want to offer dynamic interactive charting for complex data, with numerous potential end-user views, you need your drop downs to impact the data source itself, not just flip between predetermined outputs, no?
  • To post as a guest, your comment is unpublished.
    Wayne Peters · 5 years ago
    Hi, I am following the instructions provided above for use of a drop down list. However when I get to step 12 and enter the range name in the formula bar, I keep getting a "reference is not valid" error message. Would you be able to assist me with this please?
    • To post as a guest, your comment is unpublished.
      Gene Monroe · 4 years ago
      Wayne, if your named range is on a different sheet, try including your sheet name in your range definition.

      For example, if you have a range in a sheet named "Table" that reads =OFFSET($B$80,0,0,1,COUNTA($80:$80))

      Try inserting your sheet name for that range as follows:
      =OFFSET(Table!$B$80,0,0,1,COUNTA(Table!$80:$80))

      Good luck!
  • To post as a guest, your comment is unpublished.
    Suzette · 5 years ago
    I am afraid Step 12 did not work as it said an arugument is missing