|Processes and Production Skills||Analyse and visualise data to create information and address complex problems, and model processes, entities and their relationships using structured data (ACTDIP037).|
Note: Criteria are cumulative
|Quantity of knowledge||Quality of understanding|
|Pivot tables||No evidence of understanding||Student is able to find the spreadsheet pivot table tool||Student is able to use the spreadsheet pivot table tool when given the required field entries||Student is able to use the spreadsheet pivot table tool without assistance||Student is able to predict the results of the spreadsheet pivot table tool|
Despite their ready availability, many students have little sophisticated understanding of spreadsheets. Spreadsheets are used in this lesson to analyse data and to produce information using a relatively little known but remarkably powerful tool: pivot tables. You may wish to discuss the analytical mindset which would be of use to learners in this learning sequence.
Ask them to have a look at the next tab on the sheet: Pivot tables and advanced filtering (XLSX) > Sheet: Pivot table 1A
Can they see how easy it appears now?
Data > Create Manual Pivot table
(If students click Automatic Pivot table by mistake, they can drag the entries out of the boxes and it will leave all fields empty.)
Note: if this Pivot Builder window disappears, it can be switched back on from the ribbon Data > View > Builder
c. In Field names select checkboxes for Surname, Episode and Earnings.
d. Change Values drop downs to read:
as shown below:
The result appears immediately as a new worksheet.
Set students the task of reproducing the remaining pivot tables provided as examples in the spreadsheet file.
They may be familiar with this data set from a previous lesson sequence.
(These two sheets may be removed before providing the file to students.)
These are freely available as .csv files. Select the Datasets tab at top of page.
Note: check students’ proposed data sets for suitability before they commence.