Excel枢纽分析表可计算非数字数据? [英] Excel pivot table that counts non-numeric data?

问题描述

我正在帮助同事解决问题，而我本人也碰壁了.在完成一组课程后，他通过对学生回答的调查收到了CSV文件，并且他希望生成报告以显示每个问题在每个类别中的回答数量(即#同意，#强烈同意等).

I'm helping a coworker with a problem and I've run into a bit of a wall myself. He receives a CSV file from a survey of student responses after a set of classes are completed, and he wants to generate reports showing the number of responses in each category in each question (i.e. # Agree, # Strongly Agree, etc).

CSV文件的格式与此类似:

The CSV file has a format similar to this:

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 ...

他想要的是看起来像这样的报告:

What he would like is a report that looks similar to this:

School     Class     Q1 Agree     Q1 Disagree    ...    Q1 Strongly Agree  ...
======     =====     ========     ===========          =================
  A          1          2             1                         0
  A          2          1             1                         0
... etc ...

很显然，我正在查看数据透视表，但是我遇到了有关如何定义数据透视表的问题.我不是Excel方面的专家，但是当我们尝试各种选择时，我们得出的结果基本上是荒谬的.

Obviously I'm looking at a pivot table, but I'm running into an issue with how to define the pivot table. I'm not an expert at Excel, but when we tried various options we came up with essentially non-sensical results.

是否可以设置数据透视表以有意义的方式提供此数据?计算非数字值的实例并按学校和班级对计数进行分组?要实现此目标，什么是好的(容易重复的)策略?

Is it possible to set up a pivot table that will give this data in a meaningful way? Counting instances of non-numeric values and grouping the counts by school and class? What would be a good (easily repeatable) strategy to accomplish this?

非常感谢您的帮助.

(我们正在使用Office 2007)

(We are using Office 2007)

推荐答案

问题是您的数据已准备就绪，可以部分透视. 理想情况下，格式为:

The issue is that your data is all ready partially pivoted. Ideally it would be in the format:

DateTime     School     Class     Question        Answer        
========     ======     =====     ==========      ==========        
1/1/2012       A          1       Question 1      Strongly Agree    
1/1/2012       A          1       Question 2      Agree             

等

该格式将使其更好地与数据透视表一起使用. 此处是有关使用Excel 2016进行取消透视的说明

That format would lend itself to better work with a pivot table. Here is instructions on doing an unpivot with Excel 2016

这篇关于Excel枢纽分析表可计算非数字数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！