Get documents about "Spreadsheets and Non-Spatial Databases
Document Sample
Spreadsheets and Non-
Spatial Databases
Unit 4: Module 15, Lecture 1- Spreadsheet Software
Spreadsheets
A spreadsheet is a
collection of data Site Date/Time Depth Temp pH
meters ° C
EC25 DO DOSat Turb
uS/cm mg/L @temp NTU
organized in tables of IceLake
IceLake
9/11/2004 12:08
9/11/2004 12:10
3.9
4.8
18.9
18.6
8.3 123.1
8.3 123.6
6.3
5.8
70
64
9.7
11.1
columns and rows. IceLake
IceLake
9/11/2004 12:12
9/11/2004 12:13
5.8
6.8
18
16
8.1 125.2
7.9 131
5.42
3.54
59
37
9.8
10.9
IceLake 9/11/2004 12:15 7.9 12.6 7.6 139.2 1.13 11 10.1
Data entered can IceLake
IceLake
9/11/2004 12:16
9/11/2004 12:16
8.8
9.9
9.8
8.3
7.5 146.8
7.3 146.6
0.76
0.57
7.6
5
12.5
18.8
included almost IceLake
IceLake
IceLake
9/11/2004 12:18
9/11/2004 18:03
9/11/2004 18:05
10.8
1
1.9
7.6
19.9
19.7
7.2 148.8
8.3 122.3
8.3 123.2
0.37
5.89
6.3
3.2
66.7
71.1
11.8
11.9
8.8
anything: IceLake
IceLake
9/11/2004 18:07
9/11/2004 18:08
2.9
3.9
19
18.9
8.3 123.2
8.3 123
6.52
6.25
72.5
69.3
9.4
9
measurements IceLake
IceLake
9/11/2004 18:10
9/11/2004 18:11
4.8
5.8
18.6
18.2
8.3 123.5
8.2 124.3
6.03
5.54
66.5
60.7
9.1
9
IceLake 9/11/2004 18:13 6.8 16.3 7.9 130.6 3.67 38.6 9.1
names IceLake 9/11/2004 18:14 7.9 12.6 7.6 140.8 1.5 14.6 10.2
IceLake 9/11/2004 18:15 8.9 10.4 7.5 144.4 0.99 9.1 11.4
numbers IceLake
IceLake
9/11/2004 18:16
9/11/2004 18:18
9.9
10.8
8.6
8.2
7.4 145.2
7.3 146.5
0.67
0.4
5.9
3.5
10.5
14.3
IceLake 9/12/2004 0:04 1.1 19.3 8.2 122.5 5.41 60.5 11.1
commentary IceLake 9/12/2004 0:07 1.9 19.6 8.2 122.9 5.09 57.3 9.4
IceLake 9/12/2004 0:12 3.1 19 8.3 123.2 6.01 66.8 8.9
formulas, etc. IceLake
IceLake
9/12/2004 6:03
9/12/2004 6:06
1.1
2.1
19.1
19.2
8.2 122.9
8.2 123.1
5.39
5.98
60.1
66.8
9.9
8.7
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s2
Software options
Spreadsheets can be
created using:
Microsoft Excel
Corel Quattro Pro
Lotus 1-2-3
Many other options
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s3
Microsoft Excel Basics
Each Excel workbook is
organized into a series of
worksheets (spreadsheets)
Every electronic spreadsheet is
organized into rows and
columns.
The intersection of each row
and column forms a box called a colum
cell. n
Cell D4 is in column D, row 4
Three types of information can
row
cell D4
be entered into each cell.
Text
worksheet
Number
Formula
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s4
Microsoft Excel Basics
If a number entered is
displayed differently
than entered
Select cell (left click)
Right click
Select format cell
Choose:
Category: number, text,
date, etc.
Type: style, significant
digits, etc.
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s5
Microsoft Excel Basics
If a number entered
displays as (####), this
means it is too large for
the row or column.
Highlight column or row
Select Format from menu bar
Select column or row
auto-fit selection
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s6
Microsoft Excel Basics
Data should be
organized into a table
when entered
Clear titles for columns
should be included
Include measurement
units
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s7
Microsoft Excel Basics
In order to move data around
it may be necessary to
cut/copy and paste
Copy/Cut
Highlight the data to be
copied or cut using the
mouse.
Right click and select
copy or cut
Shortcut: ctrl + c (copy)
ctrl + x (cut)
Paste
Highlight the destination
cell using the mouse
Right click and select
paste
Shortcut: ctrl +v
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s8
Microsoft Excel Basics
Sometimes data may be
entered out of order or needs
to be organized
Highlight the data to be
sorted
Use the Sort feature under
the data menu
Organize by column
Ascending or
Descending
Text will be organized
alphabetically
Numbers will be organized
numerically
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s9
Microsoft Excel Basics
Formatting borders for
display/printing
Select table
Right click
Select format
Click on borders tab
Select borders to be
shown
Alternative:
Select area to print
Select print preview from
file menu
Select setup
Click on sheet tab
Check the box marked
gridlines under the print
subtitle
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s10
Microsoft Excel Basics
Helpful shortcuts
When entering data if the
same number, formula or
text is repeated several
times using the fill
command is helpful
Fill right
Highlight the cells to be
filled
Select Fill Right from
edit menu
Shortcut: Ctrl + r
Fill down
Highlight the cells to be
filled
Select Fill Down from
edit menu
Shortcut: Ctrl + d
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s11
Microsoft Excel Basics
It is possible to do the
following to an entire
column or row by right
clicking on the column
or row heading:
Insert
Delete
Cut/Copy
Paste
Clear contents
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s12
Microsoft Excel Basics
Operations on the entire
worksheet
Rename, Insert,
delete, move or copy
Preserves data in its
original form while allowing
changes to be made in the
copy.
Useful when combining
data from separate
projects
Simply right click on the
worksheet name
And select desired option
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s13
Microsoft Excel Basics
Entering Formulas
Excel formulas always
start with an equal sign
(=)
Point to the 1st cell
Enter the “operator”
add (+), subtract (-),
multiply (*), divide (/).
Point to the 2nd cell
Hit “Enter” or move off
cell
The answer magically
appears!
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s14
Microsoft Excel Basics
Basic statistical
functions
Summation
=sum (selected range)
Average
=average (range)
Median
=median (range)
Standard Deviation
=stdev (range)
Many other statistical
functions are also available
Enter formula name,
then highlight range of
values
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s15
Microsoft Excel Basics
Creating Charts and
Graphs
First step is to decide what
data to graph
Highlight the data to be
graphed
Select the chart icon in
the tool bar or select insert
from menu bar and select
chart
Follow the steps in the chart
wizard
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s16
Microsoft Excel Basics
Chart Wizard-Step 1
Select Chart Type
Column, line, bar, etc.
Select Chart Sub-type
Click Next
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s17
Microsoft Excel Basics
Chart Wizard-Step 2
Chart Source Data-
Displays image of
graph
If incorrect
Try another graph type.
Try changing columns
to rows.
Try clicking on series
and reselecting source
data.
Once the graph
displays correctly,
click Next
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s18
Microsoft Excel Basics
Chart Wizard-Step 3
Chart Options
Titles
Give graph a title and
name each of the axis,
including units
Legend
Choose to show or hide
Choose where on the
graph it is to be
displayed
Other options available
for formatting
Axes, gridlines and data
labels
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s19
Microsoft Excel Basics
Chart Wizard-Step 4
Chart location
Select destination of
new graph.
New sheet
Within existing sheet
Finally: click Finish
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s20
Example: Ice Lake, Minnesota
Example:
Ice Lake, Minnesota
Data from:
http://www.waterontheweb.or
g/data/icelake/realtime/weekl
y.html
September 5, 2004-
September 11, 2004
Question: What do the
average temperature, pH,
EC25, DO, DOSat, and
Turbidity depth profiles look
like for Ice Lake during the
week of 9/5/04-9/11/02?
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s21
Example: Ice Lake, Minnesota
The Excel version of the
weekly summaries of RUSS
data for Ice Lake provide a
tutorial for graphing this data Depth Profile: Ice Lake, Minnesota
into a “standard limnological
chart”. 0.0
0.0 20.0 40.0 60.0 80.0 100.0 120.0 140.0 160.0
Chart 1: Shows how to 2.0
graph data that have the
same depth range. 4.0
Depth (m)
Cut and paste any 6.0
information into the 8.0
template and it will 10.0
automatically graph it
below. 12.0
Avg. Temp Avg. pH Avg. EC25 Avg. DO Avg. DOSat Avg. Turb
Chart 2: Shows how to
graph data that have
different depth ranges.
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s22
Example: Ice Lake, Minnesota
In order to create this type of
chart without a template
follow these instructions: Depth Avg. Temp Avg. pH Avg. EC25 Avg. DO Avg. DOSat Avg. Turb
Step 1: meters °C uS/cm mg/L @temp NTU
Calculate average
1.0 19.4 8.2 122.2 6.0 67.5 10.3
2.0 19.3 8.2 122.6 6.3 70.5 10.5
temperature, pH, EC25, DO,
DOSat, and Turbidity for each 3.0 19.0 8.2 123.0 6.3 70.5 10.6
water depth (1-11 meters) 4.0 18.6 8.2 123.7 6.2 68.7 9.8
Delete spaces between dates 5.0 18.5 8.2 124.0 6.2 68.6 9.9
and use the Data → Sort 6.0 18.1 8.2 125.0 5.9 64.5 9.6
feature to sort data by depth
7.0 16.1 7.9 131.0 4.0 42.3 10.2
Calculate the average temp,
8.0 12.2 7.6 139.7 1.4 13.7 10.3
pH, EC25, DO DOSat, and
Turb for each depth using the 9.0 9.9 7.4 143.2 0.9 8.0 10.8
=average(range) function 10.0 8.4 7.3 144.1 0.6 4.9 12.5
Create a table showing the 11.0 7.7 7.2 146.8 0.3 2.8 12.1
averages at each depth
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s23
Example: Ice Lake, Minnesota
Step 2:
Highlight the table and
click the chart symbol.
Select XY(Scatter) for
the chart type.
Highlight the bottom left
chart sub-type.
Click next
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s24
Example: Ice Lake, Minnesota
Step 3:
From the chart source data
page select the series tab
To plot the independent
variable (depth) on the y-
axis the source data must
be changed
For each series (Avg.
Temp, Avg. pH, etc)
Click on the Y values
button and the worksheet
will display and then
highlight the values in the
depth column
Click on the X values
button and highlight the
correct values for each
series in the worksheet
(Avg. Temp, etc.) Y values button
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s25
Example: Ice Lake, Minnesota
Step 4
Chart options
Give graph a name
Label the axis
Format legend
In order to display the
depth in meters from Depth Profile: Ice Lake, Minnesota
top to bottom click on 0.0
0.0 50.0 100.0 150.0 200.0
the y-axis and select 2.0
4.0
values in reverse Meters 6.0
8.0
order. 10.0
12.0
Avg. Temp ° C Avg. pH Avg. EC25 uS/cm
Avg. DO mg/L Avg. DOSat @temp Avg. Turb NTU
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s26
Microsoft Excel Basics: Help
When using Microsoft
Excel it is important to
remember the help
menu. Many questions
can be answered using
the office assistant to
access:
Microsoft Office
Online
Microsoft Excel Help
Developed by: Forbes/Host Updated: 2/14/05 U4-m15-1-s27
Related docs
