如何获得Excel中3条形图中的数值计数图表？ [英] How do I get a chart for a count of values in 3 bar chart in Excel?
问题描述
我有一份调查发送给客户。这项调查有3个问题，有3个选项可供选择：
- 不满意
- 满意和
- 非常满意。
结果数据如下所示：
问题1问题2问题3日期
不满意满意满意04/07/2017
满意满意满意04/07/2017
满意满意非常满意04/07/2017
不满意满意满意04/07/2017
非常满意满意满意04/07/2017
不满意非常满意满意05/07/2017
满意满意非常满意05 / 07/2017
非常满意非常满意非常满意05/07/2017
非常满意满意满意06/07/2017
非常满意非常满意满意06/07/2017
不是满意满意满意06/07/2017
非常满意非常满意非常满意06/07/2017
非常满意非常满意满意06/07/2017
如何获得3条形图，显示3个问题，显示不满意，满意和非常满意 'value？
我希望我的图表看起来像这样：
|
| Q1 Q2 Q3 Q1 Q2 Q3 Q1 Q2 Q3
| ___ ___ ___ ___ ___
5 | ___ | | ___ | | | | ___ |
4 | | ___ | | | ___ ___ ___ | | | |
3 | | | | | | ___ | ___ | | | | ___ | |
2 | | ___ | | | | ___ | | ___ | | ___ | | |
1 | | | | | | | | | | | | |
---------------------------------------------- -----------
04/07/2017 05/07/2017 06/07/2017
我尝试制作数据透视表，但我无法弄清楚。
>如果您使用某些公式添加其他表格，则可以执行此操作。它可以设置一些虚拟的，但它的工作。首先，我们来创建一些命名范围。
对于问题1结果（仅限结果，不包括标题），请调用 Question_1_results 。问题2的结果， Question_2_results ，最后问题3的结果名称 Question_3_results 。
对于日期，只需调用命名范围日期。
现在设置这张表：
< （注意：行
17 是为了帮助公式，你只需要使用 B18：K22 行 18 确实有合并的单元格，我通常会避开这些单元格，但您需要这些单元格来获取图表的 X Axis
在 C20 中，输入这个公式。然后，您可以将它复制到 C20：K22
<$ c范围内的所有空白单元格$ c> = COUNTIFS（$ E $ 2：$ E $ 14，C $ 17，INDIRECT（SUBSTITUTE（C $ 19，，_）&_ results），$ B20）
最后，只需突出显示您的新表格，请转到插入 - >堆积柱形图：
I have a survey that is sent out to customer. The survey has 3 questions with 3 options to choose from:
- Not Satisfied
- Satisfied and
- Very Satisfied.
The results data looks something like this:
Question 1 Question 2 Question 3 Date
Not Satisfied Satisfied Satisfied 04/07/2017
Satisfied Satisfied Satisfied 04/07/2017
Satisfied Satisfied Very Satisfied 04/07/2017
Not Satisfied Satisfied Satisfied 04/07/2017
Very Satisfied Satisfied Satisfied 04/07/2017
Not Satisfied Very Satisfied Satisfied 05/07/2017
Satisfied Satisfied Very Satisfied 05/07/2017
Very Satisfied Very Satisfied Very Satisfied 05/07/2017
Very Satisfied Satisfied Satisfied 06/07/2017
Very Satisfied Very Satisfied Satisfied 06/07/2017
Not Satisfied Satisfied Satisfied 06/07/2017
Very Satisfied Very Satisfied Very Satisfied 06/07/2017
Very Satisfied Very Satisfied Satisfied 06/07/2017
How do I get a 3 bar chart indicating the 3 questions that will show the counts of the number of 'Not Satisfied', 'Satisfied' and 'Very Satisfied' values?
I would like my chart to look something like this:
|
| Q1 Q2 Q3 Q1 Q2 Q3 Q1 Q2 Q3
| ___ ___ ___ ___ ___
5| ___| |___| | | |___|
4| |___| | | ___ ___ ___ | | | |
3| | | | | |___|___| | | |___| |
2| |___| | | |___| |___| |___| | |
1| | | | | | | | | | | | |
---------------------------------------------------------
04/07/2017 05/07/2017 06/07/2017
I tried making a Pivot Table but I wasn't able to figure it out.
You can do this if you add another table with some formulas. It can get a little wonky to set up, but it works.
First, let's create some named ranges.
For the Question 1 results (results only, don't include the headers), call those Question_1_results. Question 2 results, Question_2_results, and finally the Question 3 results name Question_3_results.
For the dates, just call the named range dates.
Now set up this table:
(Note: Row 17 is to help with the formula. You'll just be using B18:K22 for the chart. Row 18 does have merged cells, which I usually shy away from, but you need those here to get the chart's X Axis to look right.)
In C20, enter this formula. Then you can copy it to all those blank cells in the range C20:K22
=COUNTIFS($E$2:$E$14,C$17,INDIRECT(SUBSTITUTE(C$19," ","_")&"_results"),$B20)
And finally, simply highlight your new table, go to Insert --> Stacked Column Chart:
这篇关于如何获得Excel中3条形图中的数值计数图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
