Créer une présentation
Télécharger la présentation

Télécharger la présentation
## Lecture 11B

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Lecture 11B**Using Excel Chapter 8**Starting Excel**Figure 1-2, page 1.04**The Excel Window**• Tool bar • Formula bar • Worksheet window • Columns and Rows • Cells • Pointer • Sheet tabs**Use the mouse**Use the keyboard Arrow keys Page Up Page Down Home Crtl + Home F5 Moving Around a Worksheet**Open 1-OhmsLaw.xls**• File Open... Figure 1-8, page 1.11**Some Basic Excel Stuff**• Learn how to enter text, values, formulas, and functions • Describe a range of cells • Perform what-if analysis • Learn how to use online Help • Demonstrate how to print a worksheet**Entering Information**• Text - letters, symbols, numbers, and spaces • Values - numbers that represent a quantity • Formulas - used to calculate values • Functions - special pre-written formulas**Range—used in functions**• Range - a group of cells: A1:C4 Figure 1-16, Page 1.19**Saving a Workbook**• File Save As... Figure 1-19, Page 1.23**Playing “What-if?”**• Change a value in a cell • Excel automatically recalculates the worksheet**Correcting Mistakes**• Before you press the Enter key • Use the Backspace key • After you pressed the Enter Key • Double click on the cell to put it in Edit modeDon’t use the Space bar to clear a cell**Getting Help**• Microsoft Excel Help • Contents • Answer Wizard • Index • Show/Hide the Office Assistant • What’s This? • Office on the Web • About Microsoft Excel**Printing the Worksheet**• File Print... Figure 1-28, Page 1.32**Closing and Exiting**• File Close • File Exit**Intermediate Objectives**• List the components of a well designed worksheet • Understand “Order of Precedence” • Demonstrate several methods for copying information • Understand Relative vs Absolute Cell Referencing • Change the name of the worksheet**A Well Designed Worksheet**• Clearly identifies its goal • Presents information in a clear, well organized format • Includes all necessary data to produce the intended results**Developing the Worksheet**• Determine the worksheet’s purpose • Enter the data and formulas • Test the worksheet • Correct errors and make modifications • Document the worksheet • Improve the appearance • Save and print the completed worksheet**Planning the Worksheet**• What is the goal of the worksheet? • What data is needed to calculate the results? • What calculations are needed? • EXAMPLE—Calculate density of air as a function of temperature from 0-50C in 5 degree steps • r=P/RT • P=stnd atmosphere 101.3kPa • R=gas constant for air 286.9 J/kg-K • T=temp in Kelvins**Building the Worksheet**• Establish the layout • Enter the data • Enter the formulas • See 2-AirDensity.xls example**Entering Labels**• Helps to identify the cells where you will enter the data and formulas • Alignment is left justified, and spill into empty cells to the right**Entering Data**• Values can be: • Numbers • Formulas • Functions • The data is the information you need to perform the calculations**Entering Formulas**• Formulas are equations that perform a calculation • An = sign at the beginning of a cell indicates you are entering a formula • If formulas contain more than one operator, Excel performs the calculation according to the standard order of precedence**Order of Precedence**• Predefined rules used to perform a calculation: 3 + 4 * 5 = ? • Is the answer 35 or 23?**Order of Precedence (continued)**• Exponent: ^ • Multiplication and Division: * and / • Addition and Subtraction: + and - 3 + 4 * 5 = 23 (3 + 4) * 5 = 35**Copying Formulas**• Menu Commands • Toolbar buttons • Fill Handle**AutoSum Button**• Automatically creates a formula that contains the SUM function • Looks at the cells adjacent to the active cell and guesses which range of cells you want to sum • Excel’s guess is displayed**Entering Functions**• Type the function by hand • Use the Paste Functionbutton • Do the example in 3-Functions.xls Figure 2.17, page 2.17**Relative vs Absolute Reference**• Relative Cell Reference = A5*B5 • Absolute Cell Reference = $A$5*$B$5**Relative vs Absolute References when Copying Formulas**• Relative Cell References - cell references change when they are copied • Absolute Cell References - cell references do not change when they are copied**Mixed References**• Part of the reference is absolute and part is relative: $A5 • Do the example in 4-AbsoluteVsRelative.xls**Copying using Copy & Paste**• Select the cell or cells to be copied • Select EditCopy • Select the cell or cells you want to replace • Select EditPaste The copied cells are placed in the Clipboard, and can be pasted many times.**Renaming the Worksheet**• Double click on the worksheet tab • Enter the new name**Excel Plotting Skills**• Learn to use tables and graphs as problem solving tools • Learn and apply different types of graphs and scales • Prepare graphs in Excel • Be able to edit graphs**Plotting Data**• Independent Variables • “The Cause” • X-Axis on Graphs (abscissa) • Left Columns on Tables • Dependent Variables • “The Effect” • Y-Axis on Graphs (ordinate) • Right Columns on Tables**Question**• The speed of sound in air depends on the temperature, humidity, and air pressure. • What are the independent variables? • …dependent variables?**Table and Graph Requirements**• Label the axes on your graphs. • Include units on the axes and on column headings. • Use landscape graphs.**Proper Use of Tables & Graphs**You can copy straight into your final report!**Tables**• Tables should always have: • Title • Column headings with brief descriptive name, symbol and appropriate units. • Numerical data in the table should be written to the proper number of significant digits. • The decimal points in a column should be aligned. • Tables should always be referenced and discussed (at least briefly) in the body of the text of the document containing the table.**Graphs**• Proper graphing of data involves several steps: • Select appropriate graph type • Select scale and gradation of axes, and completely label axes • Plot data points, then plot or fit curves • Add titles, notes, and or legend**1. Pie Chart**Graphs - Types 2. Bar Graph**Graphs - Types**3. 3-D Graph 4. Line Graph Body Temperature (0C Distance (m) Speed (m/s)**Graphs**• Each graph must include: • A descriptive title which provides a clear and concise statement of the information being presented • A legend defining point symbols or line types used for curves needs to be included • Labeled axes • Graphs should always be referenced/discussed in the body of the text of the document containing the table.**Titles and Legends**• Each graph must be identified with a descriptive title • The title should include clear and concise statement of the information being presented • A legend defining point symbols or line types used for curves needs to be included**Length (km)**Axis Labels • Each axis must be labeled • The axis label should contain the name of the variable and its units. • The units can be enclosed in parentheses, or separated from the label by a comma.**Scale Graduations,**Smallest Division=3.33 Scale Graduations, Smallest Division=1 Acceptable Not Acceptable Gradation • Scale gradations should be selected so that the smallest division of the axis is an integer power of 10 times 1, 2, or 5. • Exception is units of time.**Data Points and Curves**• Data Points are plotted using symbols • The symbol size must be large enough to easily distinguish them • A different symbol is used for each data set • Data Points are often connected with lines • A different line style is often used for each data set**Velocity of Three Runners**During a 5 km Race Example**Building a Graph In Excel**• Select the data that you want to include in the chart by dragging through it with the mouse. • Then click the Chart Wizard