vba 创建具有多个调查结果的 Excel 图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10161192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating an excel chart with multiple survey results
提问by gagneet
I recently did a survey, which had upto 10 questions. Now I need to create a graph out of the values, but am stuck. The data is below:
我最近做了一个调查，最多有 10 个问题。现在我需要从这些值中创建一个图表，但我卡住了。数据如下：
Customer ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10
1797493605 Yes Yes Yes Yes Compare totals Yes Yes Yes Yes None of the above
1797155787 Yes Yes Yes Yes Compare prices Yes Yes No Yes None of the above
1797116920 Yes Yes Yes Yes Compare totals No No Yes No Catalogues
1797105343 Yes Yes Yes Yes Compare prices Yes Yes Yes Yes None of the above
1797076252 Yes Yes Yes Yes Same places Yes Yes Yes No None of the above
1797015113 No No No No Everything online No No No No None of the above
1796959310 Yes Yes Yes Yes Compare prices Yes No No Yes None of the above
1796950913 Yes Yes Yes Yes Compare prices Yes Yes Yes Yes Catalogues
1796931846 Yes Yes No Yes Compare prices Yes No Yes Yes Email/SMS
I tried using the Excel normal functionality, but it just does not provide the correct graph.
我尝试使用 Excel 正常功能，但它只是没有提供正确的图表。
would like to get a Bar Graph, with each question having 2 bars - for each answer. With the X-axis having the questions and the Y-axis the number of people who answered?
想得到一个条形图，每个问题有 2 个条形 - 每个答案。X 轴表示问题，Y 轴表示回答的人数？
Can the data values then should be the actual Yes, No, ? If so, how?
那么数据值应该是实际的 Yes, No, 吗？如果是这样，如何？
采纳答案by Marc
If I understand correctly, you don't care about customer ID.
如果我理解正确，您不在乎客户 ID。
What you're trying to get is a grid of this format, where row 1 is your heading (I'm fabricating the numbers here):
您想要获得的是这种格式的网格，其中第 1 行是您的标题（我在这里编造了数字）：
| | A | B | C |
===========================
| 1 | Q | #Y | #N |
| 2 | Q1 | 6 | 1 |
| 3 | Q2 | 5 | 2 |
| 4 | Q3 | 2 | 5 |
And in order to populate cell B2(which holds the # of YESanswers to Q1, you would use a formula like this one against your grid:
并且为了填充单元格B2（其中包含对 的YES答案的# Q1，您可以对您的网格使用这样的公式：
=SUMIF($B:$B,"YES")
That SUMIF()formula counts the number of "YES" responses in the Q1 column of your grid.
该SUMIF()公式计算网格的 Q1 列中“是”响应的数量。
(I'm assuming that in YOUR grid "Q1" responses are contained in Cells B2thru B10.)
（我假设在您的网格中“Q1”响应包含在 Cells B2thru 中B10。）
Build that table, and then you'll be able to highlight that table, click into the Excel Chart Wizard, and manipulate it into the bar chart you're seeking.
构建该表格，然后您将能够突出显示该表格，单击进入 Excel 图表向导，然后将其操作到您正在寻找的条形图中。
If that's not your answer, you will need to be more specific about what you're trying to achieve.
如果这不是您的答案，您将需要更具体地说明您要实现的目标。
回答by nicolas dejean
If I understand Your problem correctly, you want to create a specific bar graph with the data format you showed.
如果我正确理解您的问题，您想使用您显示的数据格式创建一个特定的条形图。
Well as Marc pointed out, to create the graph that you want in native excel, you must rearrange your data.
正如 Marc 指出的那样，要在本机 excel 中创建您想要的图表，您必须重新排列您的数据。
However, if you want to keep your data in the same format, I propose you write Javascript to draw this chart in excel. Here is a working code I have written for you :
但是，如果您想让数据保持相同的格式，我建议您编写 Javascript 来在 excel 中绘制此图表。这是我为您编写的工作代码：
https://www.funfun.io/1/edit/5a463a3fb848f771fbcdef58
https://www.funfun.io/1/edit/5a463a3fb848f771fbcdef58
What I do in this example, at first, is to get the data with a json file :
在这个例子中，我首先做的是使用 json 文件获取数据：
{ "data": "=A1:J9" }
{ "data": "=A1:J9" }
After having acces to it, I use javascript to select the data I need and select a library to create my bar chart (here I used chart.js, it is pretty easy to use):
在访问它之后，我使用 javascript 来选择我需要的数据并选择一个库来创建我的条形图（这里我使用了 chart.js，它非常易于使用）：
var myBarChart = new Chart(ctx, {
type: 'bar',
data: data,
options: {
title: {
display: true,
text: "Excel chart with multiple survey results"
},
barValueSpacing: 20,
scales: {
yAxes: [{
ticks: {
min: 0,
max: 10
}
}]
}
}
});
If this is the chart you are after, you can directly load my code with your spreadsheet by pasting the URL in the Funfun add-in. Here is how it looks like :
如果这是您想要的图表，您可以通过将 URL 粘贴到Funfun 插件中，直接将我的代码与您的电子表格一起加载。这是它的样子：
You can of course modify the code in the online editor or in the embedded editor in Excel. You can add other labels different than Yesor No, you can have a third bar per question named Compare price.
您当然可以在在线编辑器或 Excel 中的嵌入式编辑器中修改代码。您可以添加不同于Yes或 的其他标签No，您可以为每个名为 的问题添加第三个栏Compare price。
In theory, you could code whatever you want, including creating complex charts by using powerful libraries like chart.js and plotly.js.
理论上，您可以编写任何您想要的代码，包括使用强大的库（如 chart.js 和 plotly.js）创建复杂的图表。
I hope I answered your problem, if not don't hesitate to comment below.
我希望我回答了你的问题，如果没有，请不要犹豫在下面发表评论。
Disclosure : I'm a developer of Funfun.
披露：我是 Funfun 的开发人员。
回答by Jon Peltier
Sounds like you want a table. Customer ID as column headers, Q1-Q10 as row headers. The transposed version of what you've presented.
听起来你想要一张桌子。客户 ID 作为列标题，Q1-Q10 作为行标题。您所展示内容的转置版本。
