Spreadsheet

Electronic Spreadsheet (Hands-on Module) iSchools Project Team

HUMAN CAPITAL DEVELOPMENT GROUP Commission on Information and Communications Technology

T O T A L S A L ES

b le a c h liq u id 14%

sham poo (s ach e t) 13%

sham poo ( b o t t le ) 22%

b are ta s o ap 25%

b ath s o ap 26%

D a ily S a le s (# )

16 14 12

b a re ta s o a p

10

b a th s o a p

8

b le a c h liq u i d

6

s h a m p o o (s a c h e t)

4

s h a m p o o ( b o t t le )

2 0

CALC ●

●

spreadsheet component of OpenOffice.org (OOo) Advantages in using an electronic spreadsheet: 

Data entry



Easy data manipulation and formatting



Formulas for automatic computation



What-if analysis



Automatic graphs/charts

iSchools - CILC for Teachers

4

Exercise 1. Load Calc. 2. Keep it open as you navigate your way around the application in the next section. 3. Familiarize yourself with the different parts of the Calc screen, especially those that are specific to Calc.

iSchools - CILC for Teachers

5

Calc Terms ● ● ● ● ● ● ● ●

Workbook Worksheet Cells Grid Rows Columns Row/Column Headers Sheets

iSchools - CILC for Teachers

● ● ● ●

Labels Numbers Formula Operators

6

Title Bar

Menu Bar

Column Headers

Active Cell

Row Headers

Toolbars

s

Sheet Tabs

Minimize, Maximize/Restore, Close

Formula Bar

Workbook Operations ● ● ● ● ●

Creating a New Workbook Opening an Existing Workbook Saving Closing Navigating within a Worksheet – –

● ●

Navigator Keyboard strokes

Switching from Sheet to Sheet Worksheet Views

iSchools - CILC for Teachers

9

Exercises 1 & 2 ● ● ●

Exercise 1 Exercise 2 Do not be afraid to explore and experiment.

iSchools - CILC for Teachers

10

Selecting items in a worksheet ● ● ● ●

●

●

●

A cell A range A row/column A range of rows/columns Contiguous cells/rows/columns Non-contiguous cells/rows/columns sheets

iSchools - CILC for Teachers

●

Exercise 3

11

Using a Table Discuss ● Relationship/s among data ● Which should be in ● ●

●

Rows Columns

Fields Data/Information Matrix

When is it better to present data ● ● ●

in a table? in a diagram/ chart/ graph? Cite examples

iSchools - CILC for Teachers

12

Entering Data ● ● ● ●

Text/Label Numbers Date and time Fill range (Edit > Fill or use auto-fill handle) – –

●

● ● ●

Exercise 4 Exercise 5 Exercise 6

Same data Data series

Auto-Complete

iSchools - CILC for Teachers

13

Sample Worksheet (Exercises 4 & 5)

iSchools - CILC for Teachers

14

Inserting/Deleting Rows/Columns ●

●

●

Insert a single row/column Delete a single row/column Insert/Delete Multiple rows/columns

iSchools - CILC for Teachers

●

Exercise 7

15

Worksheet ● ● ● ●

Insert Delete Move/ Re-position Rename

iSchools - CILC for Teachers

●

Exercise 8

16

Editing Data ● ● ● ● ●

Removing text Overwriting text Editing part of a cell Find & Replace Data Sort

iSchools - CILC for Teachers

●

Exercises 9 & 10

17

Formatting Data ●

Format Cells – – –

– ● ●

Numbers Font & Font Effects Alignment & Wrap (Automatic vs. Manual Line Breaks) Borders & Background

Merge Cells Format Rows/Columns

iSchools - CILC for Teachers

● ● ●

● ●

Inserting Objects Auto-Format Conditional Formatting (using Styles & Formatting Catalog) Exercise 11 Experiment with the various formatting tools and features! 18

Sample Output: Exercise 11

iSchools - CILC for Teachers

19

Formulas Operator

Function

( )

Grouping/ Parentheses

^

Exponent

Sample Formula

Note: do not use [ ] nor { } =B4^2

squares the value in B4

=B4^B2

Raises the value in B4 to the power in B2 multiplies the value in B4 by the value in B2 divides the value in B4 by the value in B2 adds the values in B4 and B2

*

Multiplication

=B4*B2

/

Division

=B4/B2

+

Addition

=B4+B2

−

Subtraction

=B4-B2

Combination

iSchools - CILC for Teachers

=B4*((B2+B3)/2)

subtracts the value in B2 from the value in B4 averages the values in B2 and B3, then multiplies the resulting value to the value in B4 20

Formulas ● ●

Exercise 12 lecture/hands-on –

●

Exercise 13

● ● ●

PEMDAS Exercise 14 Exercise 15

Copying Formulas – –

Relative referencing Absolute referencing

iSchools - CILC for Teachers

21

Exercise 14 1. 2. 3. 4. 5. 6.

=A3+B2 =C4-C3 =A1*C3 =A1/C3 =A1+A4+B2/B4 =(A1+A4+B2)/B4

iSchools - CILC for Teachers

1. 2. 3. 4. 5. 6.

=(A1+A4+B2)/B4+C3 =B4-B2-C1 =A4-C3+A1 =A4*B1+A2 =A4*(B1+A2) =C4*C1/B2-A1*A2

22

EXERCISE 15 output

EXERCISE 15 output

Functions ●

built-in formulas in Calc designed to compute specific values – can be used as part of a Calc formula – follow syntax and corresponding set of arguments or components to work with. – use a semi-colon (;) to separate arguments in a function

iSchools - CILC for Teachers

● ● ● ●

● ● ●

SUM AVERAGE PRODUCT MAX, MIN, MEDIAN, MODE ROUND COUNT, COUNTIF IF 25

Exercise 16 1. =SUM(A1:A6) 2. =AVERAGE(A1:A6) 3. =MAX(A1:A6) 4. =MIN(A1:A6) 5. =MIN(A1:A6)+MAX(A1:A6)*5 6. =MEDIAN(A1:A6) 7. =MODE(A1:A6) 8. =COUNT(A1:C6) 9. =COUNTIF(A1:C6;"<0") 10.=PRODUCT(A4;B4:B5) 11.=ROUND(PRODUCT(A4;B4:B5)/3;2) 12.=IF(A4>C5;"greater than";"less than or equal to") 13.=IF(A4>C5;"greater than";IF(C5
Exercise 17

iSchools - CILC for Teachers

26

Freezing and Splitting ●

● ● ●

To view different parts of a large worksheet at the same time Window menu Split Bar Use the current worksheet to experiment with freezing and splitting

iSchools - CILC for Teachers

27

Charts and Graphs ● ●

● ● ● ● ●

Insert Chart Auto-Format Chart dialog box Chart Types Chart Variants Adding Text Formatting Charts Editing Charts –

●

Exercise 18

Figure 37: Choosing a chart type (format)

Chart Toolbar

iSchools - CILC for Teachers

28

Exercise 18: Charts & Graphs Performance in Quizzes Acuña 78.46% Alberto 73.85% Castro 85.38% De Villa 90.00%

Students

Fabul 74.62% Gomez 92.31% Lakandula 77.69% Padua 71.54% Santos 80.00% Silvestre 81.54% Sy 80.00% Tabun 73.85% Tengco 83.08%

%Quiz Scores iSchools - CILC for Teachers

29

Printing ● ● ● ●

●

●

Format rel="nofollow"> Page Print Range Page Preview Print (Row/Column) Breaks Print Scale (% or Fit-toone page) Repeating Row/Column

iSchools - CILC for Teachers

●

Check with the trainer if you have an available printer –

Choose a sheet to print

30

REFLECTION/DISCUSSION ●

Will you be able to integrate what you've learned about ICT in your admin tasks as well as in classroom teaching and learning? – –

●

What will you commit to (vis-a-vis monitoring)? –

●

What? How? When? Why? With whom? Cite concrete examples: topics, strategies, output/product/performance Which will you use extensively? Why?

What kind of support will you need? –

From us? From others?

iSchools - CILC for Teachers

32

Thank you

Commission on Information and Communications Technology