Transcript U7 Spreadsheet Operations II

U7 Spreadsheet
Operations II
(Optional)
Presented by: Law Hing Man
(11 Dec 2001)
7.1 Overview of Using Spreadsheet as a
Teaching and Learning tool
 Spreadsheet is not just a tool for handling
marks and statistics.
 Spreadsheet can be used in teaching and
learning.
 By using charts, auto-recalculation
and macro, spreadsheet can be used to
develop an Interactive Learning
Environment (ILE).
7.2 Creating and Formatting Charts
Steps to create a chart (Example1)
Step1: Select the data to be included in the chart
(Including column and row labels)
Step2: Click the Chart wizard button (or select Insert ->
Chart…)
Step 1
Step 2
Step3: Select the Chart type and click next.
Step4: Change the data range if necessary.
Step5: Key in title and axis labels
Step6: Select the location of your chart.
(either as a new sheet or as an object in an
existing worksheet)
The changes in each item will interactively change
the corresponding portion in the chart.
7.3 Spreadsheet as a “What-if” Analysis
Tool
 “What-if”
What will happen to that if I change this.
 Example 2
Mortgage repayment analysis
PMT(r,nper,loan) calculates the periodic payment of a
loan where
r
:
Interest rate per period
nper :
Number of payments
loan :
The amount of loan
A
1 loan
2 Interest rate (p.a.)
3 number of years
4 monthly payment
B
2,000,000.00
3.00%
variables
20
11,091.95
=-PMT(B2/12,B3*12,B1)
By changing different variables, we will get different
results.
What is the minimum number of years if one can only
afford $10000 monthly repayment?
Example 3 – Mark Adjustment
=(B2+C2)/2
A
1 Name
B
C
Test1
Test2
D
E
Term Mark
2 MARY
21
45
3 PETER
45
76
4 JOHN
56
34
5 HELEN
43
65
6 KITTY
45
56
Copy
If we want to adjust the marks by multiplying a weight,
we can change the cell D2 to =(B2+C2)/2*$E$1 and put
a weight to E1.
Relative Address
 A relative address in a formula is the location
of the specified cell or range relative to the
cell that contains the formula.
 If you copy a formula that contains a relative
address, Excel adjusts the address to reflect
the new location.
 For example, if you type =A2 in A4, it means
“2 cells up”. If you copy the contents of A4 to
B5, the contents of B5 will be =B3, still “2
cells up”.
Absolute Address
 An absolute address in a formula (specified
by “$” before both column letter and row
number in the cell address) is the specified
range or cell address.
 If you copy a formula that contains an
absolute address, the address does not
change.
 For example, if you type =$A$2 in A4, it
means A2. If you then copy the contents of
A4 to B5, the contents of B5 will still be
=$A$2.
Mixed Address
 A mixed address in a formula is an address
that contains both relative and absolute
references.
 If you copy a formula that contains a mixed
address, Excel adjusts the relative part, but
not absolute part.
 For example, if you type =$A2 in A4, column
A is absolute and row 2 is relative. If you
then copy the formula from cell A4 to B5, the
contents of B5 will be =$A3.
Challenge Problem 1
* Construction of a multiplication table.
a) Fill the numbers 1 to 10 into range A2
to A11
b) Fill the numbers 1 to 10 into range B1
to K1
c) Enter a formula in B2 (? Formula)
d) Copy the formula in c) to the range B2
to K11 to complete the table.
A B C
1
1 2
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
11 10
D E F
3 4 5
=$A2*B$1
G H I
6 7 8
J
9
K
10
Challenge Problem 2
* Construction of a mortgage table.
a) Fill the numbers 2.00% to 4.00%
(interval=0.25%) into range A2 to A10.
b) Fill the numbers 5 to 30 (interval=5)
into range B1 to G1
c) A1 stores the loan (say 100,000)
d) Enter a formula in B2 (? Formula)
e) Copy the formula in c) to the range B2
to G10 to complete the table.
1
2
3
4
5
6
7
8
9
10
A
B
100,000 5
2.00%
2.25%
2.50%
2.75%
3.00%
3.25%
3.50%
3.75%
4.00%
C
10
D
E
15 20
F
25
G
30
7.4 Spreadsheet as a Tool for Developing
Explorative Learning Environment
 Macro
Macro is a series of commands that Excel
carries out automatically.
Record a Macro
Step1: Select Tools->Macro->Record New Macro
Step2:
Step3:
Step4:
In the Macro Name Box, fill in a name.
[Optional]Short Cut Key, Store Macro In
Click the OK button.
Step5: Carry out the actions that you want to record
Step6: Click the Stop Macro button.
[ To record a Macro with relative address,
click the option Relative Reference before Step 5.]
Stop Macro
button
Execute a Macro
Step1: Select Tools->Macro->Macro…
Step2: In the Macro Name Box, select a name.
Step3: Click the Run button.
OR
Press Short Cut Key
Run button
Edit a Macro
Step1:
Step2:
Step3:
Step4:
Select Tools->Macro->Macro…
In the Macro Name Box, select a name.
Click the Edit button.
Make the necessary changes.
Edit button
Create a Macro By Visual Basic
 Macro is actually written in Visual Basic,
so you may create macro by yourself.
 For more information about Simple Visual
Basic, you may refer to my homepage.
 For more advanced VB, refer to other
references.
Challenge Problem 3
 Develop an ILE for students learning the
relationship between two quantities (say x
and y) by using macro.
Challenge Problem 4
 Develop an ILE for solving the following
problem.
A student has $90. He wants to buy
hotdogs and drinks. The price for hotdog is
$7@ and that for drink is $5@. The
number of drinks should be half of the
number of hotdogs. What is the maximum
number of hotdogs and drinks that the
student can buy?
THE END