Microsoft Office Tutorials and References
In Depth Information
These questions cover a multitude of important issues that TiendaMía will want
to explore. Let us assume that we have exercised most of the procedures for ensuring
data accuracy discussed earlier, but we still have some data scrubbing 3 that needs
to be done. We surely will eliminate respondent 13 in Table 5.2 who claims to be
from outer space; thus, we now have data for 29 respondents to analyze.
The PivotTable and PivotChart Report tools can be found in the Tables Group
of the Insert Ribbon . As is the case with other tools, the PivotTable and PivotChart
have a wizard that guides the user in the design of the report. Before we begin to
exercise the tool, I will describe the basic elements of a PivotTable .
A PivotTable organizes large quantities of data in a 2-dimensional format. For a set
of data, the combination of 2 dimensions will have an intersection. Recall that we are
interested in the respondents that satisfy some set of conditions. For example, in our
survey data, the dimension Gender and Product 1 can intersect in how the two cat-
egories of gender rate their preference for Product 1, a count of either good or bad .
Thus, we can identify all females that choose good as an opinion for the webpage
design Product 1, or similarly, all males that choose bad as an opinion for Product
1. Table 5.3 shows the cross-tabulation of Gender and preference for Product 1 .The
table accounts for all 29 respondents, with the 29 respondents distributed into the
four mutually exclusive and collectively exhaustive categories—7 in Female/Bad, 7
in Female/Good, 4 in Male/Bad, and 11 in Male/Good. The categories are mutually
exclusive in that a respondent can only belong to one of the 4 Gender/Opinion cate-
gories; they are collectively exhaustive in that the four Gender/Opinion categories,
taken as a whole, contain all possible respondents. Note we could also construct a
similar cross-tabulation for each of the three remaining webpage designs (Product
2, 3, 4) by examining the data and counting the respondents that meet the conditions
in each cell. This could obviously be a tedious chore, especially if a large data set
is involved. That is why we depend on PivotTables and PivotCharts : they automate
the process of creating cross-tabulations.
Table 5.3 Cross-tabulation
of gender and product 1
preference in terms of
3 The term scrubbing refers to the process of removing or changing data elements that are
contaminated or incorrect, or that are in the wrong format for analysis.