Embedded Files
Spreadsheets
Examples: Microsoft Excel, Open office Calc, Google spreadsheet
Exam coverage:
cell references, standard functions (sum, subtotal, average)
Maths, logical and relational operator (similar to those in Database)
Filtering, searching, sorting (multiple criteria)
Pivot table
charting
What-if analysis
7.1 Introduction to Spreadsheet
Input and organized data, create charts and perform data analysis.
7.2 Basic Operations of Microsoft Excel
- Cell address, Column and row e.g. A1 (Column A and Row 1)
- formula and display value.
- Cell Format: Date and time is a number. (P.217)
Practical 1: P.218 - P.222
Upload the file "chap7_activity1.csv" below to your Google Drive under the shared folder "S4XYY" (csv file - comma separate value)
Open it with Google Sheets, Rename it as "S4XYY_chp4_act"
Name the active worksheet as "practical01"
Follows the instructions in P.218-221
- AutoFill, Copy and Paste, Paste Special
Practical 2 - P.224-226
Add a new sheet in the file used in Practical 1.
Name the new sheet as "Practical02"
Complete the instructions in text book P.224 - 226
7.3 Formula and Cell Reference
Practical 3: Activity 3 (P.230-231),
Add a new sheet in the file used in last practical
Name the new sheet as Practical03
Complete P.230-231
Example 1 on P.232
Add a new sheet.
Name the sheet as Ex01
Complete example 1 on P.233
Reference to other worksheet.
7.4 Functions
Statistical function
Mathematical Functions
Logical functions
Text, Date and time functions
7.5 Creating Charts
7.6 Data Manipulation Techniques
Filtering: Display those records matching the specified criteria (condition)
multiple criteria for different column (AND only)
Sorting: multiple criteria.
ascending
descending
Searching
7.7 Data Analysis
Tools - Scenario
Allows users to create several sets of input value for specific cells. Users can switch between scenarios and compare the results in different scenarios. It can produce summary report.
Activities 8 (P.255 - 259)
Goal Seek
Goal seek (目標搜尋) is another useful analysis tool to find out how the changes in variables (cell) affect the outcome (p.260)
Example:
Annual interest rate: 5%
Monthly investment: HKD1000
Find the Total amount after 3 years.
How much should I invest per month if I want to have 50000 (Goal)
In Microsoft Excel 2003
Tools - Goal Seek
Set cells (address) to value (50000) by changing cell.
Pivot Table
Specify the following information:
1. Specify the table of data you want to analysis (Spreadsheet, Database, ... )
2. You can set the pivot table with the following parameter (Different name in different versions of Office)
Report Filter / Page Area / Page field.
Column Labels / Column Area / Column field.
Row Labels / Row Area / Row field.
Values / Data Area / Data items.
3. Sub-layer can also be set.
Microsoft Excel 2003: Data - Pivot Table
Homework for Chapter 7
On-or-before: May 4, 2016 (Next Thursday)
P.278 - 280 of text book
Short question: Q1 - 4
Long question: Q2
Page updated
Report abuse