Transcript Simplicity lecture slides

Spreadsheet Design
Simplicity
ACIS 1504 - Introduction to Data Analytics & Business Intelligence
Business
Intelligence
Quality
Information
Functions
SDLC
Design
• Spreadsheet*
• Database
Data
Analytics
Big Data*
Methods
•
•
•
•
•
Data Mining*
Text Mining*
OLAP*
Visualization*
Query
Database
Benefits 
Components 
Implement with:
* Excel
 Access
Concept Map
Spreadsheet
Development
Design
Simplicity
Implementation
Fill, Find, Header,
Transpose
Objectives
• Explain the Simplicity Principle, part 1 and 2
& explain how to support it in spreadsheets.
• Demonstrate Excel features often used with
large spreadsheets.
Segment A:
Productivity
Moving Efficiently
CTRL + arrow keys
Mac Users: CMD + SHIFT + arrow
Jumps cursor to end of block or beginning of
next block
Block = contiguous occupied cells
1504 Grades Example
Open Grades.xlsx from course home page
Step 3
1. How many columns of data?
2. How many rows of data?
3. What is the number of the last empty
row in the spreadsheet?
4. What are the letters of the last empty
column in the spreadsheet?
5. What do the green triangles mean?
Fill vs. Copy
Fill
Highlight
pattern then
drag fill
handle
Copy
Highlight
single cell
then drag fill
handle
Double-click fill handle to fill to bottom of block
Find
Long Tail Theory
Total $ Sales of each item
• Chris Anderson, Wired’s ex-Editor in Chief
Inventory Items
Long Tail Examples
• Amazon - "We sold more books today that
didn't sell at all yesterday, than we sold today
of all the books that did sell yesterday.“
• Netflix – “In aggregate, unpopular movies are
rented more than popular movies.”
• iTunes – “A miss is just another sale with the
same margins as a hit.”
Long Tail Stocks Example
1. Open these files:
•
•
•
Amazon Stock
Apple Stock
Netflix Stock
Files available from home page under Step 3
Long Tail Stocks Example
2. Insert a new column at the beginning of each file and
label it: Symbol.
3. Add the appropriate stock market symbol to the first
stock quote in each file:
• Amazon = amzn
• Apple = aapl
• Netflix = ntflx
4. Use the fill handle (not copy/paste) to copy the stock
market symbol to all quotes in each file.
5. Combine these stock quotes into one worksheet in a new
workbook named Long Tail Stocks. List Amazon quotes
first, then Apple, then Netflix. (1 worksheet, 7 columns,
over 450 rows)
6. Insert a new column at the beginning of your new
workbook. Label it ID. Assign each quote a unique
number. Begin numbering at 1 and increment by 1.
Long Tail Stocks Example
5. Combine these stock quotes into one
worksheet in a new workbook named Long
Tail Stocks. List Amazon quotes first, then
Apple, then Netflix. (1 worksheet, 7 columns,
over 450 rows)
6. Insert a new column at the beginning of your
new workbook. Label it ID. Assign each quote
a unique number. Begin numbering at 1 and
increment by 1.
7. I made a mistake in the Netflix symbol. It
should be nflx. Fix this.
Segment B:
Simplicity – Part 1
Spreadsheet Design Goals
• Simplicity
• Clarity
• Verifiability
• Accuracy
• Efficiency
Simplicity Part 1
• One idea per row and
• One idea per column
One Idea
Not
One Idea
Segment C:
Simplicity – Part 2
Simplicity – Part 2
• Numbers to be compared should align in
columns.
Rows vs. Columns
Simple
VT Football. VT. 2011. <http://www.hokiesports.com/football/stats/2010/?gbg
Not Simple
VT Football. VT. 2011. <http://www.hokiesports.com/football/stats/2010/?gbg
Not Simple
Detail Report: Salaries Reported. VT Career Services. 2011.
<http://www.career.vt.edu/PostGraduationReport/DetailReportSalaries.asp
?College=00&Majors=N&Cohort=2009-2010>
Simple
Detail Report: Salaries Reported. VT Career Services. 2011.
<http://www.career.vt.edu/PostGraduationReport/DetailReportSalaries.asp
?College=00&Majors=N&Cohort=2009-2010>
Header & Footer
Select VIEW, NORMAL to return to regular worksheet display
Simple?
Detail Report: Bonuses Reported. VT Career Services. 2011.
<http://www.career.vt.edu/PostGrad2006/DetailReportBonuses.asp?College=00&Major
s=Y&Cohort=2008-2009>
Simple?
Detail Report: Employment related to career goals & major. VT Career Services. 2011.
<http://www.career.vt.edu/PostGraduationReport/DetailReportEmploymentRelated.asp?College=00&Majors=Y&Cohort=
2009-2010>
Enrollment Example
I want to compare Fall enrollments for years
‘09-’13.
Transpose – Step 1: Copy
First, select data range.
Transpose – Step 2: Paste
First, select new
sheet.
Stock Summary Example
1. Open Stock Summary
2. Setup the worksheet so I can most easily
analyze Apples highest monthly closing
prices. Then I’d like to do the same for
Amazon and Netflix.