Microsoft Excel. Charts Charts pictorially represent data. Excel can draw a variety of two-dimensional and three-dimensional charts. Open Excel 2000.

10  Download (0)

Full text

(1)

Microsoft Excel

Microsoft Excel is a spreadsheet program that allows you to organize data, complete calculations, make decisions, graph data, and develop professional-looking reports. Here we learn two major parts of Excel:

Worksheets

Worksheets allow you to enter calculate, manipulate, and analyze data such as numbers and text. The term worksheet means the same as spreadsheet.

Charts

Charts pictorially represent data. Excel can draw a variety of two-dimensional and three-dimensional charts.

Open Excel 2000.

The worksheet is organized into a rectangular grid containing columns (vertical) and rows (horizontal). A column letter above the grid, also called the column heading, identifies each column. A row number on the left side of the grid, also called the row heading, identifies each row.

(2)

Toolbar shortcut menu Auto Calculation area Keyboard indicator Status bar Name box Formatting toolbar Standard Toolbar Menu bar Formula bar Toolbar shortcut menu Auto Calculation area Keyboard indicator Status bar Name box Formatting toolbar Standard Toolbar Menu bar Formula bar Toolbar shortcut menu Auto Calculation area Keyboard indicator Status bar Name box Formatting toolbar Standard Toolbar Menu bar Formula bar

Locate all the toolbars in your Excel Window.

Entering text and numbers

Enter box Text displays in Formula bar Cancel box

Edit formula box Active cell

Enter box Text displays in Formula bar Cancel box

Edit formula box Active cell

(3)

Edit formula

As mentioned before, the most powerful function of Worksheets is to enter calculate, manipulate, and analyze data. There are a lot of functions that spreadsheet help performing. In the following, we try to perform some of them.

Calculating an average

Highlight the cell H2, click “Edit formula box” and the pull-down menu. Find the function “AVERAGE” from the list.

The text “=AVERAGE(B2:G2)” displayed in the formula bar means taking the average of the numbers stored in B2 to G2. You may change the range of the cell if they are not the range you want.

(4)

Exercise:

In an experiment about the growth of a plant by measuring the height of the plant everyday, you tabulate your results and record them in the following table. Find the average height of the plant from day 1 to day 5 by using the spreadsheet. Write down your results in the spaces provided below.

Day 1/cm Day 2/cm Day 3/cm Day 4/cm Day 5/cm

Sample 1 0.5 0.8 1.3 3.3 5.1 Sample 2 0.5 1.1 1.3 3.5 4.9 Sample 3 0.7 0.9 1.1 2.8 4.6 Sample 4 0.6 0.7 1.0 2.6 4.2 Average Calculating a sum

Open a new worksheet and entering the following text and numbers.

Highlight the cell B6, click “Edit formula box” and the pull-down menu. Find the function “SUM” from the list and write down the Total in the following.

The total of the expenditure as shown in the spreadsheet is .

There are many other function provided by the “Edit formula box”. Let explore them. Do you know each of them?

(5)

Chart

What is a chart?

Most spreadsheet programs can automatically create charts from the values and labels in a worksheet.

The chart remains linked to this data, which means that any changes made to the data are automatically reflected in the chart.

Charts are useful for:

Summarizing numeric information

Detecting trends over time (for example, temperature at different times of the year) Searching for patterns among a large number of figures

(6)

Chart types

Here are different chart types that you can find from the chart wizard.

Open a new worksheet and click the “chart wizard” button to explore the different types of chart.

In each type of chart, we must identify the axes to be displayed in the chart. Are you ready to identify them?

(7)

Exercise:

In the following chart, identify the X-and Y- axes.

Creating chart by accessing the chart wizard

Open a new worksheet and save it as “Fruit store.xls”. Enter the value as shown in the following screen.

(8)

Highlight the cells from A1 to B9, click the chart wizard button to select the “bar chart”.

Follow the steps indicated by the chart wizard to complete the chart. Step 1: Select the chart type

Step 2: Select the source of data

Step 3: Select the chart options like title, labels etc.

Step 4: Select the location of the chart. You can save the chart as a new worksheet or an object in the active worksheet.

Can you produce the following chart?

Fruit stored in Qtr 1 0.0 1000.0 2000.0 3000.0 4000.0 5000.0 6000.0 7000.0 8000.0 9000.0

Melons Apples Mandarins Grapefruit Kiwi Fruit Pears Pineapples Oranges

Fruit

(9)

Explore different type of chart by using the same data in “Fruit store.xls”. Produce another chart to show the data graphically.

Sometimes, a comparison is required to make on different data on the same worksheet. Here we consider all the data inputted in the spreadsheet “Fruit store.xls”.

Exercise:

Highlight all cells from A1 to C9, click the chart wizard button to select the “line chart”.

Similar to the previous steps, produce the following chart.

Fruit stored in Qtr 1 and 3

0.0 1000.0 2000.0 3000.0 4000.0 5000.0 6000.0 7000.0 8000.0 9000.0 10000.0 Me lons Appl es Ma ndari ns Grapef ruit Kiwi Frui t Pea rs Pin eappl es Orang es Fruits Unit Qtr1 Qtr2

(10)

Exercise:

The following is the summary of meteorological observations in Hong Kong 2002.

Air Temperature

Month Mean Daily

Maximum (deg. C) Mean (deg. C) Mean Daily Minimum (deg. C) Mean Relative Humidity (%) Mean Amount of Cloud (%) Total Rainfall (mm) January 19.3 17.3 15.5 75 55 25.0 February 20.0 18.2 16.6 77 63 4.6 March 23.5 21.5 20.0 81 77 238.7 April 26.9 24.6 22.8 82 73 12.4 May 29.3 27.0 25.2 81 77 275.6 June 31.0 28.8 27.0 80 77 237.6 July 31.3 28.9 26.7 82 79 320.8 August 30.7 28.4 26.6 81 68 365.9 September 29.1 27.2 25.5 80 71 723.0 October 26.8 25.2 23.7 77 71 199.0 November 23.4 21.5 19.7 72 56 23.3 December 20.2 18.2 16.4 80 76 64.1 Mean/Total 26.0 23.9 22.1 79 70 2490.0 Normal 25.7 23.0 20.9 77 65 2214.3

* Source: Hong Kong Observatory, HKSAR. (http://www.hko.gov.hk/wxinfo/pastwx/ywx2002.htm)

(a) Produce a bar chart with heading “Total Rainfall (mm) in Hong Kong 2002” showing all rainfall in 2002.

(b) Produce a line chart with “Mean daily maximum and minimum temperature in Hong Kong 2002” which includes both mean daily maximum and mean daily minimum temperature.

Figure

Updating...

References

Related subjects : status bar