 Download Download Presentation Lecture 11B

# Lecture 11B

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Lecture 11B Using Excel Chapter 8

2. Example of an Excel Worksheet

3. Starting Excel Figure 1-2, page 1.04

4. The Excel Window • Tool bar • Formula bar • Worksheet window • Columns and Rows • Cells • Pointer • Sheet tabs

5. Use the mouse Use the keyboard Arrow keys Page Up Page Down Home Crtl + Home F5 Moving Around a Worksheet

6. Open 1-OhmsLaw.xls • File Open... Figure 1-8, page 1.11

7. 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

8. Entering Information • Text - letters, symbols, numbers, and spaces • Values - numbers that represent a quantity • Formulas - used to calculate values • Functions - special pre-written formulas

9. Range—used in functions • Range - a group of cells: A1:C4 Figure 1-16, Page 1.19

10. Saving a Workbook • File Save As... Figure 1-19, Page 1.23

11. Playing “What-if?” • Change a value in a cell • Excel automatically recalculates the worksheet

12. 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

13. Getting Help • Microsoft Excel Help • Contents • Answer Wizard • Index • Show/Hide the Office Assistant • What’s This? • Office on the Web • About Microsoft Excel

14. Printing the Worksheet • File Print... Figure 1-28, Page 1.32

15. Closing and Exiting • File Close • File Exit

16. 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

17. 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

18. 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

19. 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

20. Building the Worksheet • Establish the layout • Enter the data • Enter the formulas • See 2-AirDensity.xls example

21. 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

22. Entering Data • Values can be: • Numbers • Formulas • Functions • The data is the information you need to perform the calculations

23. 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

24. Order of Precedence • Predefined rules used to perform a calculation: 3 + 4 * 5 = ? • Is the answer 35 or 23?

25. Order of Precedence (continued) • Exponent: ^ • Multiplication and Division: * and / • Addition and Subtraction: + and - 3 + 4 * 5 = 23 (3 + 4) * 5 = 35

26. Copying Formulas • Menu Commands • Toolbar buttons • Fill Handle

27. 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

28. Entering Functions • Type the function by hand • Use the Paste Functionbutton • Do the example in 3-Functions.xls Figure 2.17, page 2.17

29. Relative vs Absolute Reference • Relative Cell Reference = A5*B5 • Absolute Cell Reference = \$A\$5*\$B\$5

30. 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

31. Mixed References • Part of the reference is absolute and part is relative: \$A5 • Do the example in 4-AbsoluteVsRelative.xls

32. 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.

33. Renaming the Worksheet • Double click on the worksheet tab • Enter the new name

34. 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

35. 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

36. Question • The speed of sound in air depends on the temperature, humidity, and air pressure. • What are the independent variables? • …dependent variables?

37. Table and Graph Requirements • Label the axes on your graphs. • Include units on the axes and on column headings. • Use landscape graphs.

38. Proper Use of Tables & Graphs You can copy straight into your final report!

39. 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.

40. Table Example

41. 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

42. 1. Pie Chart Graphs - Types 2. Bar Graph

43. Graphs - Types 3. 3-D Graph 4. Line Graph Body Temperature (0C Distance (m) Speed (m/s)

44. 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.

45. 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

46. 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.

47. 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.

48. 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

49. Velocity of Three Runners During a 5 km Race Example

50. 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