创建包含多个调查结果的Excel图表

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?

那么数据值是否应该是实际的是，否，？如果是这样，怎么样？

3 个解决方案

#1

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 YES answers to Q1, you would use a formula like this one against your grid:

并且为了填充单元格B2（其中包含Q1的YES的答案，您将使用类似于此网格的公式：

=SUMIF($B$2:$B$10,"YES")

That SUMIF() formula counts the number of "YES" responses in the Q1 column of your grid.

SUMIF（）公式计算网格Q1列中“YES”响应的数量。

(I'm assuming that in YOUR grid "Q1" responses are contained in Cells B2 thru B10.)

（我假设在你的网格中“Q1”响应包含在B2到B2的单元格中。）

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.

如果那不是你的答案，那么你需要更具体地了解你想要实现的目标。

#2

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指出的那样，要在native 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

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 :

如果这是您所使用的图表，则可以通过粘贴Funfun加载项中的URL直接在电子表格中加载我的代码。它是这样的：

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 Yes or No, you can have a third bar per question named Compare price.

您当然可以在在线编辑器或Excel中的嵌入式编辑器中修改代码。您可以添加不同于“是”或“否”的其他标签，每个问题可以有一个名为“比较价格”的第三个栏。

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的开发者。

#3

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作为行标题。你所呈现的转置版本。

#1