我正在帮助一位同事解决一个问题,但我自己也遇到了麻烦。在一组课程结束后,他收到了一份来自学生回答调查的CSV文件,他想生成报告,显示每个问题中每个类别的回答数量(即#同意、#强烈同意等)。
CSV文件的格式类似于:
DateTime School Class Question 1 Question 2 Question 3 ... etc ...
======== ====== ===== ========== ========== ==========
1/1/2012 A 1 Agree Strongly Agree Disagree
1/1/2012 A 1 Disagree Agree Strongly Disagree
1/1/2012 A 2 Agree Strongly Agree Slightly Disagree
1/1/2012 A 1 Agree Agree Disagree
1/1/2012 A 2 Disagree Disagree Disagree
... etc 8,000 rows ...
他想要的是一份类似于以下内容的报告:
School Class Q1 Agree Q1 Disagree ... Q1 Strongly Agree ...
====== ===== ======== =========== =================
A 1 2 1 0
A 2 1 1 0
... etc ...
很明显,我正在研究一个数据透视表,但我遇到了如何定义数据透视表的问题。我不是Excel的专家,但当我们尝试各种选项时,我们得到了基本上没有意义的结果。
是否可以建立一个数据透视表,以有意义的方式提供这些数据?计算非数值的实例,并按学校和班级对计数进行分组?什么是一个好的(容易重复的)策略来实现这一点?
谢谢你的帮助,非常感谢。
(我们使用Office 2007)
选择您的数据范围作为数据透视表。
- 将
Question 1
、Class
和School
拖动到"行标签"框中 - 将
Question 1
、Question 2
和Question 3
拖动到"值"框中 - 如果没有显示"问题1计数",请单击
Value Field Settings.
上的向下箭头 - 在"
Summarize by
"选项卡中,选择"计数"> - 必要时重复
- 在另一张工作表上,链接到特定的数据透视表单元格,以显示所需格式的数据
编辑:我附上了一张图像作为视觉辅助
https://i.stack.imgur.com/UCYE6.jpg
如果你有任何问题,请告诉我。
在第二张纸上填写连接学校、班级和答案的公式。之后的表格会是这样的:
Q1 Q2 Q3
=================================================================
A1Q1:Agree A1Q2:Strongly Agree A1Q3:Disagree
A1Q1:Disagree A1Q2:Agree A1Q3:Strongly Disagree
A2Q1:Agree A2Q2:Strongly Agree A2Q3:Slightly Disagree
其中A1表示学校A一班。你甚至可以使用VLookup将文本替换为数字
你可以通过将这个公式添加到伪表中的单元格D3中来获得:
=sourcetable!$B3&sourcetable!$C3&D$1&":"&sourcetable!D3
然后将其复制到每个单元格。
现在你所要做的就是创建一个这样的结果表:
Q1 Q1 Q1
School Class Agree Disagree Strongly Disagree
========================================================================
A 1 formula
其中公式为
=countif(dummytable!C3:z999,$A4&B4&C$1&":"&C$2)
对所有单元格重复此公式。
第一部分做什么以及如何做应该很清楚。在生产级别的应用程序中,当数据中出现拼写错误时,我会将文本(同意/不同意)替换为数字(谷歌搜索VLookup)。
第二部分统计字符串的所有出现次数(在本例中为A1Q1:Agree)。这应该奏效。如果你想公开它,最好将结果"复制:粘贴值"到一个新表中。。。
您可以按原样重用dummytable和结果表。只需将新数据放入sourcetable。。。
问题是您的数据已经全部准备好部分进行数据透视。理想情况下,它将采用以下格式:
DateTime School Class Question Answer
======== ====== ===== ========== ==========
1/1/2012 A 1 Question 1 Strongly Agree
1/1/2012 A 1 Question 2 Agree
etc
这种格式将有助于更好地使用数据透视表。以下是使用Excel 2016 进行取消透视的说明