Скачать 130.88 Kb.
Purpose: To give the student experience with using Microsoft Excel
Apparatus and Materials: PC and Microsoft Excel.
Microsoft® Excel is a spreadsheet program that allows the user to conveniently store, manipulate, and plot data in various formats. The focus of this lab is to teach the student how to efficiently generate graphs with trendlines. By using Excel the student can eliminate common errors associated with plotting data and calculating slopes of trendlines.
In Microsoft® Excel, data is stored in cells which are identified by column and row. For instance, cell B7 is located in column B, row 7. The cell is thus the fundamental unit for this software
Data can be manipulated by entering the appropriate combinations of mathematical operators. For instance, if one wishes to divide the contents of cell B8 by the contents in cell D9, one merely needs to type the following command in a cell other then B8 and D9:
Note that an equal sign needs to precede any mathematical operations. Mathematical operations are carried out in a predetermined hierarchical fashion. One may use parentheses to control the order of the mathematical operations.
The symbols for common mathematical operations are listed below:
Equal to =
Less than or equal to <=
In addition, Excel has a multitude of built-in functions such as average, summation, and rounding. These functions can be accessed from the Insert Function icon (fx) located underneath the toolbars. When creating spreadsheets one should try to organize the data in a logical fashion and clearly label the components. In addition, unique descriptive names should be given to spreadsheets to ensure easy retrieval.
1. Open a blank workbook in Excel. Enter the following information from the table below. Place the words “x-coordinate” in cell A1 and “y-coordinate” in cell B1.
Column A Column B
2. Place the cursor in the cell A2 and highlight all the cells that that contain numbers by holding down the left mouse button and moving the cursor down and to the right until the cursor is over cell B7. Do not highlight the cells containing “x-coordinate” and “y-coordinate”.
3. Click on the Chart Wizard icon on the toolbar at the top of the screen. Select XY (Scatter) for the Chart Type in the Chart Wizard (Step 1 of 4) dialog box. Select Next.
4. Select Next in the following Chart Wizard (Step 2 of 4) dialog box.
5. Input a title and labels for the x and y axis in the next Chart Wizard (Step 3 of 4) dialog box.
6. Select “As new sheet:” in the final Chart Wizard (Step 4 of 4) dialog box and input a name for your chart. Select Finish.
7. Move the cursor away from any gridlines, axes, and data points on the chart and right click. Select “Format Plot Area”. In the Format Plot Area dialog box select “None” under area. This will get rid of the gray shading on the plot and save ink when you print the graph.
8. Move the cursor to a data point and right click. Select “Add Trendline”. Select “Linear” under the Type tab of the Add Trendline dialog box. Click on the options tab and select all three boxes at the bottom of the dialog box. Ensure that a zero is displayed in the “Select intercept =” dialog box. By selecting these three options, the trendline will forced through the origin and the equation of the line and a statistical parameter (involving how good the fit is) will be displayed on the chart.
9. The legend on the plot may be removed by moving the cursor to the legend, right clicking on it, and selecting “Clear”. Save the workbook with a unique name to a floppy disk or e-mail it to yourself. Print the plot.
1. Using what you have learned from Exercise 1, plot and print the following data. In step 8 select “Polynomial” of order 2 instead of “Linear”.
Column A Column B
1. Introduction to Microsoft Excel http://chemed.chem.purdue.edu/genchem/lab/datareports/excel/intro.html
2. Microsoft® Office Word Help.
3. Microsoft® Office Excel Help.