I'm looking for a multiple axis line chart similar to the one in the image below, (which is a javascript chart made by amcharts).
我正在寻找一个与下图相似的多轴线图(这是一个由amcharts制作的javascript图)。
Does excel have an option to draw charts line these? Note there are 3 Y axes, and 3 line charts allowing you to compare data.
excel是否有绘制图表的选项?注意，这里有3个Y轴和3个线形图，允许您比较数据。
Is it possible to get more than 3 data points, each with unique axis on one chart ?
是否有可能得到3个以上的数据点，每个点都有唯一的轴在一个图表上?
6 个解决方案
#1
10
It is possible to get both the primary and secondary axes on one side of the chart by designating the secondary axis for one of the series.
通过为系列中的一个指定辅助轴，可以在图表的一侧获得主轴和辅助轴。
To get the primary axis on the right side with the secondary axis, you need to set to "High" the Axis Labels option in the Format Axis dialog box for the primary axis.
要使主轴与辅助轴在右侧获得，您需要在主轴的“格式轴”对话框中设置“High”轴标签选项。
To get the secondary axis on the left side with the primary axis, you need to set to "Low" the Axis Labels option in the Format Axis dialog box for the secondary axis.
要获得左侧的辅助轴和主轴，需要在辅助轴的“格式轴”对话框中设置“低”轴标签选项。
I know of no way to get a third set of axis labels on a single chart. You could fake in axis labels & ticks with text boxes and lines, but it would be hard to get everything aligned correctly.
我知道不可能在一个图表上得到第三组轴标签。您可以用文本框和行来伪造axis标签和刻度，但是要使所有的东西都正确对齐是很困难的。
The more feasible route is that suggested by zx8754: Create a second chart, turning off titles, left axes, etc. and lay it over the first chart. See my very crude mockup which hasn't been fine-tuned yet.
更可行的路径是zx8754建议的:创建第二个图表，关闭标题、左坐标轴等，并将其放在第一个图表上。看看我那还没调好的粗糙的模型。
#2
4
The picture you showd in the question is actually a chart made using JavaScript. It is actually very easy to plot multi-axis chart using JavaScript with the help of 3rd party libraries like HighChart.js or D3.js. Here I propose to use the Funfun Excel add-in which allows you to use JavaScript directly in Excel so you could plot chart like you've showed easily in Excel. Here I made an example using Funfun in Excel.
您在问题中显示的图片实际上是一个使用JavaScript制作的图表。在第三方库(如HighChart)的帮助下，使用JavaScript绘制多轴图表实际上非常容易。js或D3.js。在这里，我建议使用Funfun Excel外接程序，它允许您在Excel中直接使用JavaScript，以便您可以像在Excel中那样轻松地绘制图表。这里我用Excel中的Funfun做了一个例子。
You could see in this chart you have one axis of Rainfall at the left side while two axis of Temperature and Sea-pressure level at the right side. This is also a combination of line chart and bar chart for different datasets. In this example, with the help of the Funfun add-in, I used HighChart.js to plot this chart.
你可以在这张图中看到左边有一个降雨轴，右边有两个温度轴和海平面。这也是不同数据集的线图和条形图的组合。在本例中，在Funfun插件的帮助下，我使用了HighChart。js要绘制这张图表。
Funfun also has an online editor in which you could test your JavaScript code with you data. You could check the detailed code of this example on the link below.
Funfun还有一个在线编辑器，您可以在其中使用数据测试JavaScript代码。您可以在下面的链接中检查这个示例的详细代码。
https://www.funfun.io/1/#/edit/5a43b416b848f771fbcdee2c
https://www.funfun.io/1/ /编辑/ 5 a43b416b848f771fbcdee2c
Edit: The content on the previous link has been changed so I posted a new link here. The link below is the original link https://www.funfun.io/1/#/edit/5a55dc978dfd67466879eb24
编辑:先前链接的内容已经更改，所以我在这里发布了一个新的链接。下面的链接是原始链接https://www.funfunfun.io/1/# /编辑/5a55dc978dfd67466879eb24
If you are satisfied with the result you achieved in the online editor, you could easily load the result into you Excel using the URL above. Of couse first you need to insert the Funfun add-in from Insert - My add-ins. Here are some screenshots showing how you could do this.
如果您对在线编辑器中的结果感到满意，您可以使用上面的URL轻松地将结果加载到您的Excel中。当然，首先你需要从insert - My add-ins中插入Funfun外接程序。下面是一些屏幕截图，展示了如何做到这一点。
Disclosure: I'm a developer of Funfun
信息披露:我是Funfun的开发者。
#3
2
There is a way of displaying 3 Y axis see here.
这里有一个显示3 Y轴的方法。
Excel supports Secondary Axis, i.e. only 2 Y axis. Other way would be to chart the 3rd one separately, and overlay on top of the main chart.
Excel支持辅助轴，即只有2个Y轴。另一种方法是分别绘制第三张图，并将其覆盖在主图的顶部。
#4
2
An alternative is to normalize the data. Below are three sets of data with widely varying ranges. In the top chart you can see the variation in one series clearly, in another not so clearly, and the third not at all.
另一种方法是对数据进行规范化。下面是三组具有广泛变化范围的数据。在上面的图表中，你可以清楚地看到一个系列中的变化，在另一个系列中不那么明显，而第三个系列则完全不一样。
In the second range, I have adjusted the series names to include the data range, using this formula in cell C15 and copying it to D15:E15
在第二个范围中，我调整了系列名称以包含数据范围，使用单元格C15中的这个公式，并将它复制到D15:E15
=C2&" ("&MIN(C3:C9)&" to "&MAX(C3:C9)&")"
= c2”(min(C3:C9)&“,”马克斯(C3:C9)和“)”
I have normalized the values in the data range using this formula in C15 and copying it to the entire range C16:E22
我使用C15中的这个公式对数据范围中的值进行规范化，并将其复制到整个范围C16:E22
=100*(C3-MIN(C$3:C$9))/(MAX(C$3:C$9)-MIN(C$3:C$9))
3:加元= 100 *(C3-MIN(9)加元)/(MAX(加元加元3:9)分钟(加元加元3:9)
In the second chart, you can see a pattern: all series have a low in January, rising to a high in March, and dropping to medium-low value in June or July.
在第二个图表中，你可以看到一种模式:所有系列在1月份都处于低位，3月份升至高位，6月或7月跌至中低位。
You can modify the normalizing formula however you need:
可根据需要修改正火公式:
=100*C3/MAX(C$3:C$9)
= 100 * C3 / MAX(加元加元3:9)
=C3/MAX(C$3:C$9)
= C3 / MAX(加元加元3:9)
=(C3-AVERAGE(C$3:C$9))/STDEV(C$3:C$9)
=(C3-AVERAGE(加元3:9)加元)/方差(加元加元3:9)
etc.
等。
#5
0
Taking the answer above as guidance;
以上述答案为指导;
I made an extra graph for "hours worked by month", then copy/special-pasted it as a 'linked picture' for use under my other graphs. in other words, I copy pasted my existing graphs over the linked picture made from my new graph with the new axis.. And because it is a linked picture it always updates.
我为“按月工作的时间”做了一个额外的图表，然后复制/特别粘贴它作为一个“链接图片”用于我的其他图表。换句话说，我将现有的图形粘贴到与新坐标轴上的新图形的链接图上。因为它是一个链接的图片，所以它总是更新。
Make it easy on yourself though, make sure you copy an existing graph to build your 'picture' graph - then delete the series or change the data source to what you need as an extra axis. That way you won't have to mess around resizing.
让自己轻松一点，确保你复制了一个现有的图形来构建你的“图片”图形——然后删除这个系列或者将数据源更改为你需要的额外轴。这样，你就不必在调整大小上浪费时间了。
The results were not too bad considering what I wanted to achieve; basically a list of incident frequency bar graph, with a performance tread line, and then a solid 'backdrop' of hours worked.
考虑到我想要达到的目标，结果还不算太糟;基本上是一个事件频率条形图的列表，带有性能踏线，然后是工作时间的坚实“背景”。
Thanks to the guy above for the idea!
感谢上面那个家伙的想法!
#6
0
Best and Free ( maybe only) solution for this is google sheets. i don't know whether it plots as u expected or not but certainly you can draw multiple axes.
最好的和免费的(可能是唯一的)解决方案是谷歌表。我不知道它是否像你想的那样画但你可以画多个坐标轴。
Regards
问候
keerthan
keerthan
#1
10
It is possible to get both the primary and secondary axes on one side of the chart by designating the secondary axis for one of the series.
通过为系列中的一个指定辅助轴，可以在图表的一侧获得主轴和辅助轴。
To get the primary axis on the right side with the secondary axis, you need to set to "High" the Axis Labels option in the Format Axis dialog box for the primary axis.
要使主轴与辅助轴在右侧获得，您需要在主轴的“格式轴”对话框中设置“High”轴标签选项。
To get the secondary axis on the left side with the primary axis, you need to set to "Low" the Axis Labels option in the Format Axis dialog box for the secondary axis.
要获得左侧的辅助轴和主轴，需要在辅助轴的“格式轴”对话框中设置“低”轴标签选项。
I know of no way to get a third set of axis labels on a single chart. You could fake in axis labels & ticks with text boxes and lines, but it would be hard to get everything aligned correctly.
我知道不可能在一个图表上得到第三组轴标签。您可以用文本框和行来伪造axis标签和刻度，但是要使所有的东西都正确对齐是很困难的。
The more feasible route is that suggested by zx8754: Create a second chart, turning off titles, left axes, etc. and lay it over the first chart. See my very crude mockup which hasn't been fine-tuned yet.
更可行的路径是zx8754建议的:创建第二个图表，关闭标题、左坐标轴等，并将其放在第一个图表上。看看我那还没调好的粗糙的模型。
#2
4
The picture you showd in the question is actually a chart made using JavaScript. It is actually very easy to plot multi-axis chart using JavaScript with the help of 3rd party libraries like HighChart.js or D3.js. Here I propose to use the Funfun Excel add-in which allows you to use JavaScript directly in Excel so you could plot chart like you've showed easily in Excel. Here I made an example using Funfun in Excel.
您在问题中显示的图片实际上是一个使用JavaScript制作的图表。在第三方库(如HighChart)的帮助下，使用JavaScript绘制多轴图表实际上非常容易。js或D3.js。在这里，我建议使用Funfun Excel外接程序，它允许您在Excel中直接使用JavaScript，以便您可以像在Excel中那样轻松地绘制图表。这里我用Excel中的Funfun做了一个例子。
You could see in this chart you have one axis of Rainfall at the left side while two axis of Temperature and Sea-pressure level at the right side. This is also a combination of line chart and bar chart for different datasets. In this example, with the help of the Funfun add-in, I used HighChart.js to plot this chart.
你可以在这张图中看到左边有一个降雨轴，右边有两个温度轴和海平面。这也是不同数据集的线图和条形图的组合。在本例中，在Funfun插件的帮助下，我使用了HighChart。js要绘制这张图表。
Funfun also has an online editor in which you could test your JavaScript code with you data. You could check the detailed code of this example on the link below.
Funfun还有一个在线编辑器，您可以在其中使用数据测试JavaScript代码。您可以在下面的链接中检查这个示例的详细代码。
https://www.funfun.io/1/#/edit/5a43b416b848f771fbcdee2c
https://www.funfun.io/1/ /编辑/ 5 a43b416b848f771fbcdee2c
Edit: The content on the previous link has been changed so I posted a new link here. The link below is the original link https://www.funfun.io/1/#/edit/5a55dc978dfd67466879eb24
编辑:先前链接的内容已经更改，所以我在这里发布了一个新的链接。下面的链接是原始链接https://www.funfunfun.io/1/# /编辑/5a55dc978dfd67466879eb24
If you are satisfied with the result you achieved in the online editor, you could easily load the result into you Excel using the URL above. Of couse first you need to insert the Funfun add-in from Insert - My add-ins. Here are some screenshots showing how you could do this.
如果您对在线编辑器中的结果感到满意，您可以使用上面的URL轻松地将结果加载到您的Excel中。当然，首先你需要从insert - My add-ins中插入Funfun外接程序。下面是一些屏幕截图，展示了如何做到这一点。
Disclosure: I'm a developer of Funfun
信息披露:我是Funfun的开发者。
#3
2
There is a way of displaying 3 Y axis see here.
这里有一个显示3 Y轴的方法。
Excel supports Secondary Axis, i.e. only 2 Y axis. Other way would be to chart the 3rd one separately, and overlay on top of the main chart.
Excel支持辅助轴，即只有2个Y轴。另一种方法是分别绘制第三张图，并将其覆盖在主图的顶部。
#4
2
An alternative is to normalize the data. Below are three sets of data with widely varying ranges. In the top chart you can see the variation in one series clearly, in another not so clearly, and the third not at all.
另一种方法是对数据进行规范化。下面是三组具有广泛变化范围的数据。在上面的图表中，你可以清楚地看到一个系列中的变化，在另一个系列中不那么明显，而第三个系列则完全不一样。
In the second range, I have adjusted the series names to include the data range, using this formula in cell C15 and copying it to D15:E15
在第二个范围中，我调整了系列名称以包含数据范围，使用单元格C15中的这个公式，并将它复制到D15:E15
=C2&" ("&MIN(C3:C9)&" to "&MAX(C3:C9)&")"
= c2”(min(C3:C9)&“,”马克斯(C3:C9)和“)”
I have normalized the values in the data range using this formula in C15 and copying it to the entire range C16:E22
我使用C15中的这个公式对数据范围中的值进行规范化，并将其复制到整个范围C16:E22
=100*(C3-MIN(C$3:C$9))/(MAX(C$3:C$9)-MIN(C$3:C$9))
3:加元= 100 *(C3-MIN(9)加元)/(MAX(加元加元3:9)分钟(加元加元3:9)
In the second chart, you can see a pattern: all series have a low in January, rising to a high in March, and dropping to medium-low value in June or July.
在第二个图表中，你可以看到一种模式:所有系列在1月份都处于低位，3月份升至高位，6月或7月跌至中低位。
You can modify the normalizing formula however you need:
可根据需要修改正火公式:
=100*C3/MAX(C$3:C$9)
= 100 * C3 / MAX(加元加元3:9)
=C3/MAX(C$3:C$9)
= C3 / MAX(加元加元3:9)
=(C3-AVERAGE(C$3:C$9))/STDEV(C$3:C$9)
=(C3-AVERAGE(加元3:9)加元)/方差(加元加元3:9)
etc.
等。
#5
0
Taking the answer above as guidance;
以上述答案为指导;
I made an extra graph for "hours worked by month", then copy/special-pasted it as a 'linked picture' for use under my other graphs. in other words, I copy pasted my existing graphs over the linked picture made from my new graph with the new axis.. And because it is a linked picture it always updates.
我为“按月工作的时间”做了一个额外的图表，然后复制/特别粘贴它作为一个“链接图片”用于我的其他图表。换句话说，我将现有的图形粘贴到与新坐标轴上的新图形的链接图上。因为它是一个链接的图片，所以它总是更新。
Make it easy on yourself though, make sure you copy an existing graph to build your 'picture' graph - then delete the series or change the data source to what you need as an extra axis. That way you won't have to mess around resizing.
让自己轻松一点，确保你复制了一个现有的图形来构建你的“图片”图形——然后删除这个系列或者将数据源更改为你需要的额外轴。这样，你就不必在调整大小上浪费时间了。
The results were not too bad considering what I wanted to achieve; basically a list of incident frequency bar graph, with a performance tread line, and then a solid 'backdrop' of hours worked.
考虑到我想要达到的目标，结果还不算太糟;基本上是一个事件频率条形图的列表，带有性能踏线，然后是工作时间的坚实“背景”。
Thanks to the guy above for the idea!
感谢上面那个家伙的想法!
#6
0
Best and Free ( maybe only) solution for this is google sheets. i don't know whether it plots as u expected or not but certainly you can draw multiple axes.
最好的和免费的(可能是唯一的)解决方案是谷歌表。我不知道它是否像你想的那样画但你可以画多个坐标轴。
Regards
问候
keerthan
keerthan