Chemical spreadsheet notes

Yüklə 327,71 Kb.

səhifə	1/5
tarix	09.01.2019
ölçüsü	327,71 Kb.
	#94260

1 2 3 4 5

MassWWP The Massachusetts Water Watch Partnership

Blaisdell House

University of Massachusetts

Amherst MA 01003

413/545-5532 or 545-5531

mwwp@tei.umass.edu

www.masswwp.org

Graphing Water Quality Data With Excel

December 2003

Jerry Schoen

Massachusetts Water Watch Partnership

These guidance materials were produced with support from the Massachusetts Environmental Trust.

Table of Contents

Introduction 4

Overview of the Charting process 4

Steps involved in making graphs. 18

Dealing with missing data points. 18

Deerfield River pH example 19

Arrange data conveniently 19

File Organization 19

One worksheet or several? 22

Copy records to a separate area 22

Creating graphs in Excel 23

Create a graph – example: 23

Step 1: Make a copy of the data. 23

Step 2: Sort / filter the data. 23

Step 3: “Massage” the data. 24

Step 4. Create the “draft” chart. 25

Chart Gallery 28

Charts for River Data 29

One site, several dates: 29

One site, several dates #2: Value line added (series) 29

One site, several dates #3: Value line added (drawn in) 29

One site, several dates #4: Value areas added 30

One site, several dates #5: Value areas added 31

Several dates, several sites: 2-D 31

Several dates, several sites: 3-D view. 32

Several Dates, Several Sites – Multiple Series 32

Multi-year Charts: Several Sites, Multiple Dates 33

Calculated data - Several Dates, several Sites. 34

Two Parameters, Two Axes – One Date, Several Sites. 35

One Date, Several Sites. Dissolved Oxygen % Saturation 36

Charts for Lake Data 38

One site, several dates – data table included 38

Lake depth profile 38

Depth (m) 38

One site, multiple dates – multiple series, multiple years. 39

One site, several dates: Value line added (series) 41

One site, several dates: Value line added (drawn in) 41

DO Saturation example. 41

Introduction

One of the more frustrating tasks associated with running a water quality monitoring program is translating data you collect (numbers) into a graphical representation of your data that helps a viewer understand the condition of your water body. Spreadsheet software is usually the vehicle of choice for volunteer monitoring programs attempting this. While such software does greatly facilitate the work involved, it can still be a fairly a tedious chore to create, modify or replicate a graph. This handbook is our attempt to help you minimize the work involved. It is intended to assist you in using Microsoft Excel software to create graphs from your data^¹. The instructions printed here are intended for readers who have a basic understanding of Excel, including use of graphs. However, even beginning users should be able to pick up these concepts after some practice.

The handbook contains several sections.

Introductory remarks on how to use Excel to create graphs. These remarks aren’t intended to replace Excel Help features or the numerous manuals in print, but we have tried to provide examples and frame questions in ways that are specific to water monitoring.
A recommended process for “preparing” your data: organizing it in a way that facilitates creation of graphs.
A collection of sample graphs of various types that are commonly prepared to represent water quality data, with specific instructions on how to make similar graphs for your program. We use river and lake examples, many of which are interchangeable in terms of the concepts represented. These might also be used for coastal monitoring results.

The examples discussed in this document are taken from Excel workbooks Deerfield data and lakecharts. Each workbook contains several worksheets with tables and graphs reflecting different types of data you might want to capture for river or lake sampling, respectively. To get the best use from this guidance document, you’ll probably want to open the Excel files mentioned and refer to them as you read.

Note: we use the terms chart and graph interchangeably, and use them as both nouns and verbs.

Overview of the Charting process

Graphs are a visual way to display numeric data. They come in a variety of formats: lines, bars, pie charts, disconnected points, and sometimes a mix of different styles.

Their purpose is to help a viewer “see” the data more clearly – spotting similarities or differences in multiple data points, and noticing trends.

When interpreting any data value, there are several different additional information points needed to make sense of it or to preserve its uniqueness. These include:

The parameter that the value represents. e.g. temperature, dissolved oxygen (DO), or water clarity.
Unit of measure (UOM). E.g. milligrams per liter, parts per billion, pH units, etc.
Time: date or time of day sample was taken.
Location: the spot a sample is taken from. This can have 2 or 3 dimensions itself: a geographic location: e.g. mid-lake, or river mile 3.2 (which itself can be further specified by distance from left bank, right bank, etc.); and depth in the water column (e.g. lake bottom, surface, etc.).

A graph typically depicts the results obtained from a parameter under several different instances of one (or more) dimensions. For instance, you might depict DO data taken from several different sites on the same day. Or you might show DO results from one site on several different dates. Or you might use a 3-D graph to show DO at several sites and on several dates. And in all cases, you will want to label your data by listing such information as parameter name, waterbody name, UOM, etc.

Waterbody	Date	Sample ID	Site	Temp	DO	DO Sat	Fecal	pH
Deerfield River	4/18/93		COR-010
Deerfield River	4/18/93		DER-010
Deerfield River	4/18/93		DER-015
Deerfield River	4/18/93		DER-016
Deerfield River	4/18/93		DER-020
Deerfield River	4/18/93		DER-021
Deerfield River	4/18/93		DER-025
Deerfield River	4/18/93		NOR-010
Deerfield River	4/18/93		NOR-015
Deerfield River	4/18/93		SOR
Deerfield River	5/1/94		COR-010
Deerfield River	5/1/94		DER-010
Deerfield River	5/1/94		DER-015
Deerfield River	5/1/94		DER-016
Deerfield River	5/1/94		DER-020
Deerfield River	5/1/94		DER-021
Deerfield River	5/1/94		DER-025
Deerfield River	5/1/94		NOR-010
Deerfield River	5/1/94		NOR-015
Deerfield River	5/1/94		SOR
Deerfield River	4/27/96		COR-010
Deerfield River	4/27/96		DER-010
Deerfield River	4/27/96		DER-015
Deerfield River	4/27/96		DER-016
Deerfield River	4/27/96		DER-020
Deerfield River	4/27/96		DER-021
Deerfield River	4/27/96		DER-025
Deerfield River	4/27/96		NOR-010
Deerfield River	4/27/96		NOR-015
Deerfield River	4/27/96		SOR
Deerfield River	4/20/97		COR-010
Deerfield River	4/20/97		DER-010
Deerfield River	4/20/97		DER-015
Deerfield River	4/20/97		DER-016
Deerfield River	4/20/97		DER-020
Deerfield River	4/20/97		DER-021
Deerfield River	4/20/97		DER-025
Deerfield River	4/20/97		NOR-010
Deerfield River	4/20/97		NOR-015
Deerfield River	4/20/97		SOR
Deerfield River	4/19/98		COR-010	6	11.08	89		6.2
Deerfield River	4/19/98		DER-010	10	9.26	82		6.19
Deerfield River	4/19/98		DER-015	9	10.54	91		6.24
Deerfield River	4/19/98		DER-016
Deerfield River	4/19/98		DER-020	6	11.42	92		6.15
Deerfield River	4/19/98		DER-021	6	11.02	89		6.15
Deerfield River	4/19/98		DER-025	6	10.56	85		5.9
Deerfield River	4/19/98		NOR-010
Deerfield River	4/19/98		NOR-015
Deerfield River	4/19/98		SOR	8	10.26	87		6.58
Deerfield River	4/26/1998		COR-010	10	10.58	11.288		6.84
Deerfield River	4/26/1998		DER-010	15	8.7	10.084		7.04
Deerfield River	5/16/1998		COR-010	10	13	11.288		6.84
Deerfield River	5/16/1998		DER-010	15	10	10.084		7.04
Deerfield River	5/16/1998		DER-015	16	11	9.87		7.02
Deerfield River	5/16/1998		DER-016	12	9	10.777		7.04
Deerfield River	5/16/1998		DER-020	13	8.5	10.537		6.91
Deerfield River	5/16/1998		DER-021	8	10	11.843		6.95
Deerfield River	5/16/1998		DER-025	9	12			6.71
Deerfield River	5/16/1998		NOR-010	13	9			7.25
Deerfield River	5/16/1998		NOR-015	13	10			7.25
Deerfield River	5/16/1998		SOR	10	11			7.53
Deerfield River	6/21/1998		COR-010	10	10			7.05
Deerfield River	6/21/1998		DER-010	15	9			6.58
Deerfield River	6/21/1998		DER-015	16	8.6			6.54
Deerfield River	6/21/1998		DER-016	12	7			7.25
Deerfield River	6/21/1998		DER-020	13	8.5			6.97
Deerfield River	6/21/1998		DER-021	8	9			7.06
Deerfield River	6/21/1998		DER-025	9	11			6.5
Deerfield River	6/21/1998		NOR-010	13	9.5			7.02
Deerfield River	6/21/1998		NOR-015	13	9.3			7.2
Deerfield River	6/21/1998		SOR	10	8.88			7.6
Deerfield River	7/18/1998		COR-010	12	11.1			6.5
Deerfield River	7/18/1998		DER-010	17	8.7			7.3
Deerfield River	7/18/1998		DER-015	18	9.1			7.1
Deerfield River	7/18/1998		DER-016	15	10.2			7.3
Deerfield River	7/18/1998		DER-020	15	10.7			6.7
Deerfield River	7/18/1998		DER-021	10	8.9			6.9
Deerfield River	7/18/1998		DER-025	10.5	9.2			7.02
Deerfield River	7/18/1998		NOR-010	17	9			6.6
Deerfield River	7/18/1998		NOR-015	16	8.5			6.4
Deerfield River	7/18/1998		SOR	11.5	9.3			7.2
Deerfield River	4/19/99		COR-010	15	8.82	87.5		5.6
Deerfield River	4/19/99		DER-010	16	9.1	92.2		7.02
Deerfield River	4/19/99		DER-015		7.16			7.04

In most cases, a graph will only show one parameter, but there are cases where multiple parameters are shown together, to help the observer see any causal relation between the two. For instance, a graph might show temperature and dissolved oxygen (DO), or pH and alkalinity together.

Graphs can also compare variable data with a constant value. For instance, fecal bacteria results might be compared against the state water quality standard.

Before you start: save these files!

We recommend you save the sample Excel files in a secure place on your computer, make copies of them, and work only with the copies. That way, if you decide that changes you make are not to your satisfaction, you can go back to the original files and start over again. Once you have modified our examples to create graphs that work better for you, then save them, make copies and work from those as you tinker further.

Yüklə 327,71 Kb.

Dostları ilə paylaş:

1 2 3 4 5