Liengme A Guide to Microsoft Excel 2013 for Scientists and Engineers

1. Auflage 2015
ISBN: 978-0-12-802816-2
Verlag: Elsevier Science & Techn.
Format: EPUB
Kopierschutz: Adobe DRM (»Systemvoraussetzungen)

E-Book, Englisch, 382 Seiten

Completely updated guide for students, scientists and engineers who want to use Microsoft Excel 2013 to its full potential. Electronic spreadsheet analysis has become part of the everyday work of researchers in all areas of engineering and science. Microsoft Excel, as the industry standard spreadsheet, has a range of scientific functions that can be utilized for the modeling, analysis and presentation of quantitative data. This text provides a straightforward guide to using these functions of Microsoft Excel, guiding the reader from basic principles through to more complicated areas such as formulae, charts, curve-fitting, equation solving, integration, macros, statistical functions, and presenting quantitative data. - Content written specifically for the requirements of science and engineering students and professionals working with Microsoft Excel, brought fully up to date with the new Microsoft Office release of Excel 2013 - Features of Excel 2013 are illustrated through a wide variety of examples based in technical contexts, demonstrating the use of the program for analysis and presentation of experimental results New to this edition: - The Backstage is introduced (a new Office 2013 feature); all the 'external' operations like Save, Print etc. are now in one place - The chapter on charting is totally revised and updated - Excel 2013 differs greatly from earlier versions - Includes many new end-of-chapter problems - Most chapters have been edited to improve readability

Dr. Bernard Liengme attended Imperial College in London and received a BSc & Ph.D. in Chemistry. He also received post-docs at Carnegie-Mellon University in Pittsburgh and the University of British Columbia. He has conducted extensive research in surface chemistry and Mossbauer Effect. He has been at St Francis Xavier University in Canada since 1968 as professor, Associate Dean, and Registrar as well as teaching chemistry and computer science. He is the author of four previous versions of 'A Guide to Microsoft Excel for Scientists and Engineers, most recently the Excel 2013 version."

Autoren/Hrsg.

Liengme, Bernard

Leseproben

Chapter 2 Basic Operations
Abstract
This chapter begins by explaining how to enter numbers including fractions and percentages. It introduces the concept of simple formulas. The order of precedence of mathematical operators in Excel is examined. The errors such as #DIV/0! and #VALUE! are explained. Information is given on how to format numbers; the difference between the displayed and the stored value is emphasized. The evaluation tool is introduced. The reader is informed about the rounding errors that can arise due to the IEEE 754 protocol for storing numbers in binary form. Keywords Range finder Evaluate Errors IEEE 754 Shortcuts Subscripts Superscripts Mathematical limitation Chapter Contents Exercise 1: Simple Arithmetic   12 Exercise 2: The Mathematical Operators   14 Exercise 3: Formatting (Displayed and Stored Values)   16 Exercise 4: Working with Fractions   17 Exercise 5: A Practical Worksheet   19 Copying Formulas: What Happens to References?   20 What's in a Name?   22 Exercise 6: Another Practical Example   24 Exercise 7: The Evaluate Formula Tool   27 Special Symbols, Subscripts, and Superscripts   28 Mathematical Limitations of Excel   30 Play It Again, Sam   32 This book is about problem solving so we shall spend little time on the preparation of presentation-worthy worksheets. We will give some information on how to make a worksheet more readable, but the emphasis is on mathematical operations. The topics in this chapter include  entering numbers, including fractions and percentages;  simple formulas such as = A1+B1+C1;  range finders (colored borders showing what cells are used in a formula);  arithmetic operators +, -, * , /, and ˆ;  the Evaluate Formula tool;  error values such a #DIV/0! and #VALUE!;  copying with commands and shortcuts;  formatting numbers;  the difference between stored and displayed values;  round-off errors resulting from the IEEE 754 standard. If you are familiar with an earlier version of Microsoft Excel, you may be tempted to skip this chapter. You are urged to at least read the exercises to find out about new Excel 2013 features. Exercise 1: Simple Arithmetic
Imagine that from time to time, you are given some data consisting of rows of three numbers and you are asked to find the sum and product of each triple. Of course, this could be done with a simple calculator, but a spreadsheet offers three advantages: we can reuse our spreadsheet from day to day, we can see the values we have entered, and we can make a neat printout of the results. Our completed spreadsheet will look like Figure 2.1: ¦ Figure 2.1 a. In cells A1 to E1, enter the text shown in Figure 2.1. In A2:C3, enter the numbers shown. You will note that as you enter the text, it is left aligned in a cell, while numbers are right aligned. b. Use the mouse to select A1:E1. On the Home tab, click the right alignment command in the Alignment group; it is the third command in the second row of this group. c. Unless we have used a spreadsheet before, we might be tempted to type = 1+3+4 in cell D2. Try this (remembering to press when finished) and it will give the correct answer, but this totally ignores the main idea behind a worksheet. We should not have to retype data. Wherever possible, formulas should refer to cell values. Click on D2 and tap the key to remove this formula. d. Now type = A2+B2+C2 and click the check mark to the left of the formula bar when the formula is complete. Notice that, as you type, the status bar displays ENTER, but once the checkmark tool to the right of the formula bar (or the key) is used to commit the formula, it shows READY. e. Next, we see another way to build a formula. In D3, type an equals sign (=), but rather than typing A3, click the A3 cell. Now type + and continue building the formula with this pointing method. The status will alternate between POINT and ENTER. Note how the cells take on a colored border that matches the colors of the cell references in the formula (Figure 2.2). Again, click the checkmark to commit the formula once it is finished. In this case, pointing has little advantage over typing, but in other cases, it has some advantages. Pointing helps to ensure we reference the correct cell in a complex worksheet, and it is very useful to reference a cell on another sheet that could be in another workbook. ¦ Figure 2.2 Of course, we do not have to rebuild the formula for every cell. We can copy from one cell to another. Here, we look at two ways of doing this, and a little later, we will see a third (and the fastest) method. f. The first method uses the Copy and Paste commands located on the Clipboard group of the Home tab (far left)—see Figure 2.3. With D3 as the active cell, click the Copy command. Select D4:D5 and use the Paste command (this is the larger icon on the group). Note how the cell we copied (D3) has a mobile dotted border. While this “ant track” is visible, the contents of the cell are still on the Clipboard and may be copied to any other range or cell. The ant track disappears as soon as you start to edit any cell but can also be removed by pressing . ¦ Figure 2.3 Note: The key may be used whenever you what to cancel what you are doing. Also if you start to edit a cell and wish to terminate the operation without making any changes, you can use the tool located between the Name Box and the Formula Bar. If you allow the mouse pointer to hover over the commands in the Clipboard group, screen tips pop up to tell the purpose and the shortcut keystrokes for each command. So Copy is + C and Paste is + V. Note: It is customary to show shortcuts with capital letters like + C but it is not necessary to hold the key to generate a capital letter when using a shortcut. g. Delete D3:D5 and repeat the copy-and-paste action using the + C and + V shortcuts. If you accidentally delete D2, use + Z to undo the action. h. Delete D3:D5 again in preparation for another way to copy D2 down to D5. Move to D2 and note that the active cell border has a small solid square in the lower right corner; this is the fill handle. Carefully move the mouse pointer until it is over the fill handle—the pointer changes from an open cross to a solid cross. Hold down the left mouse button and drag the solid cross down to D5. In step (k) below, we shall see yet another method of filling a range. For the final stage in this exercise, we look at another approach to building formulas. Rather than typing the formula in the cell, we will type it in the formula bar. There is an advantage to doing this when the formula is long, but we shall do it here for demonstration purposes: i. Make E2 the active cell. In the formula bar, type =. Now complete the formula to be = A2*B2*C2 either by typing or by pointing. Commit the formula with either or the checkmark on the formula bar. Note that the multiplication operator is an asterisk (*). j.  Lastly, we will fill in cells E3:E5. With E2 as the active cell, move the mouse pointer over the fill handle (watch for the change from open to solid cross) and double-click the fill handle. The formula from E2 is copied down to E5. This Auto Fill feature can be used with vertical tables (data arranged in columns) but not with horizontal tables. It can be used to renew formulas when you make a change to the top cell. k. Double-click on any cell in the range D2:E5. Note that the status bar displays EDIT. But more importantly, observe the colored borders around the cells in the corresponding cells in columns A, B, and C. Excel uses these range finders to pictorially show you which cells a formula refers to. l. In A6:C6, type some numbers. When you complete the last entry, Excel will automatically add the formulas in D6 and E6. This is an example of Auto Extend. It requires that the table has at least four rows of entries above the current row. For more information on this topic, see http://support.microsoft.com/kb/231002. m. In the QAT,...