NSS-ICT‎ > ‎Unit A‎ > ‎

Chapter 07

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
 ElementDescription 
 ConstantRaw Data, not calculated result 
Boolean: True, False
 OperatorArithmetic operators:  +, -, *, /,
             ^ (exponential), %

Logical operator: >, <, =, >=, <=, <> (not equal)

Text concatenation: & 
 Cell referenceRange of Cell

Absolute Cell reference (e.g. $A$1) 
Relative Cell reference (e.g. A1)
Mixed Cell reference (e.g. $A1, A$1)
 Function 



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

Subpages (1): Practical
ċ
Act06.csv
(0k)
T. Chan,
Mar 2, 2016, 7:27 PM
ĉ
T. Chan,
Feb 24, 2016, 5:27 AM
ĉ
T. Chan,
Dec 13, 2015, 10:52 PM
ċ
chap7_activity1.csv
(0k)
T. Chan,
Dec 2, 2015, 6:30 AM
Comments