Courses/Computer Science/CPSC 203/CPSC 203 2007Fall L04/CPSC 203 2007Fall L04 Lectures/Lecture 6
Contents
Lecture 6
We continue to introduce spreadsheets as a flexible medium for playing with data, organizing data, analyzing data.
The objectives of today's class are:
- House Keeping
- What do you need to know for lab quiz next week
- Assignment 1: You can use Excel, Open Office Spreadsheet, or Google Spreadsheet & Docs. BUT -- must save the file as .xls format
- Spreadsheets 001
- Spreadsheet Interface versus Spreadsheet Model
- Expanded glossary on spreadsheets and data model review
- Spreadsheet Design Principles Overview -- Adding the design principles to a basic spreadsheet
- Spreadsheet Design Principles -- 3 examples of good design.
- Spreadsheet design principles
Lecture Glossary (Expanded Slightly from Lecture 5)
- Spreadsheet: Row by column array of cells. Values are passed from cell to cell.
- Cell: "Atomic" unit of spreadsheet. In a cell there is data (values), formats (how cell looks) + data types (what kind of value: integer, text...etc)
- Data Model: A model of relationships between pieces of data.
- Operator & Function: Works on values to transform them.(i.e. +, -, X...)
- Domain: Cells from which values are taken.
- Range: Cells to which data is given.
- Case: A unit of observation
- Variable: An observed attribute
- Properties
- Value(s)
- Relative and Absolute Reference ("Location")
Spreadsheet Data Model (Reviewed Last Class)
- cell X -----> cell Y
- Domain X -----> Range Y
- Dots -- the cells
- Edges -- Operators and Functions
- Properties of a Spreadsheet Cell:
- Format (i.e. colour, shading, background, font-size)
- Type (i.e. data type)
A Spreadsheet is a "lattice" or "grid" of cells. Functions and operators allow values to be moved from cell to cell. A cell can not write back into itself. Essentially a spreadsheet is a very user friendly programming environment.
Mini-Tutorial: Spreadsheet Design (introduced end of last class)
Below are some general rules for building a well designed spreadsheet.
Some Basic Rules about Design
- Design the spreadsheet on paper first. Graph paper often works well.
- Test and edit your calculations. Where appropriate use intermediate calculations and check-sums to ensure calculations are correct.
- Keep the components of a calculation visible. No "magic numbers". Place fixed numbers used in a calculation in their own cell with a descriptive title.
- Be aware of the "space" or "geography" of the spreadsheet. Arrange your information so that it is well spaced and easy to take in at a glance.
Parts of a Well Designed Spreadsheet
These parts can be considered components of a disciplined approach to building spreadsheet so they are self-documenting. The parts could each be in their own sheet, or they could be in a single sheet.
- Introduction -- What is this spreadsheet or workbook about. Note the title, purpose, author, creation and revision dates etc.
- Model and Assumptions. Justify any models, summary statistics, or calculated variables you are using.
- Data Dictionary. For every variable in the spreadsheet note: its
- location (cell range),
- name,
- the Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
- Field Type (e.g. Integer, Text, Currency, Date, etc.) and
- Description (a description of the data or what it's 'purpose' is).
- Raw Data. Present your raw data in tabular form -- with columns representing variables and rows representing cases.
- Calculated Data.
- Summary Statistics --Usually Summary statistics result from calculations across rows for a single column.
- Derived variables are often based on calculations across columns for a row.
- Presentation' (Reporting)
- Emphasize the final information you wish to show without excessive background details.
- Use charts wherever appropriate to summarize large volumes of data
Well Designed Spreadsheet Examples
- We take a basic spreadsheet, and sketch out some of the additions we can make to it for good design.
- We look at 3 examples of good design from Last Semester's Assignment 1, and critique what works and what does not.
Homework
Look up the basic functions and formats for:
- Statistical Data Summarization: Summation, Mean, Standard Deviation, Mode, Median, Variance.
- How "If Then" functions work.
- How "Lookup" works.
- Can you figure out the function to combine two pieces of text????
This sets us up to shift next week into Data Analysis using spreadsheets, and introducing Assignment 1
Resources
The Elements of Spreadsheet Style. 1986. By J.M. Nevison (Great, cheap, book on spreadsheet design. Out of print, but easily ordered from Amazon).
A Brief History of Spreadsheets: http://dssresources.com/history/sshistory.html A nice little article on the origins of spreadsheets by some guys with too much facial hair.
Another overview on Spreadsheets is at: http://en.wikipedia.org/wiki/Spreadsheet