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
Bernard Liengme attended Imperial College in London and received a BSc & PhD in Chemistry. He also received post-docs at Carnegie-Mellon University in Pittsburg and the University of British Columbia. He has conducted extensive reseach 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.
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
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
Exercise 6: Another Practical Example 24
Exercise 7: The Evaluate Formula Tool 27
Special Symbols, Subscripts, and Superscripts 28
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,...
Erscheint lt. Verlag | 17.3.2015 |
---|---|
Sprache | englisch |
Themenwelt | Informatik ► Office Programme ► Office |
Mathematik / Informatik ► Mathematik ► Algebra | |
Mathematik / Informatik ► Mathematik ► Angewandte Mathematik | |
Technik | |
ISBN-10 | 0-12-802816-5 / 0128028165 |
ISBN-13 | 978-0-12-802816-2 / 9780128028162 |
Haben Sie eine Frage zum Produkt? |
![PDF](/img/icon_pdf_big.jpg)
Größe: 34,5 MB
Kopierschutz: Adobe-DRM
Adobe-DRM ist ein Kopierschutz, der das eBook vor Mißbrauch schützen soll. Dabei wird das eBook bereits beim Download auf Ihre persönliche Adobe-ID autorisiert. Lesen können Sie das eBook dann nur auf den Geräten, welche ebenfalls auf Ihre Adobe-ID registriert sind.
Details zum Adobe-DRM
Dateiformat: PDF (Portable Document Format)
Mit einem festen Seitenlayout eignet sich die PDF besonders für Fachbücher mit Spalten, Tabellen und Abbildungen. Eine PDF kann auf fast allen Geräten angezeigt werden, ist aber für kleine Displays (Smartphone, eReader) nur eingeschränkt geeignet.
Systemvoraussetzungen:
PC/Mac: Mit einem PC oder Mac können Sie dieses eBook lesen. Sie benötigen eine
eReader: Dieses eBook kann mit (fast) allen eBook-Readern gelesen werden. Mit dem amazon-Kindle ist es aber nicht kompatibel.
Smartphone/Tablet: Egal ob Apple oder Android, dieses eBook können Sie lesen. Sie benötigen eine
Geräteliste und zusätzliche Hinweise
Buying eBooks from abroad
For tax law reasons we can sell eBooks just within Germany and Switzerland. Regrettably we cannot fulfill eBook-orders from other countries.
![EPUB](/img/icon_epub_big.jpg)
Größe: 19,9 MB
Kopierschutz: Adobe-DRM
Adobe-DRM ist ein Kopierschutz, der das eBook vor Mißbrauch schützen soll. Dabei wird das eBook bereits beim Download auf Ihre persönliche Adobe-ID autorisiert. Lesen können Sie das eBook dann nur auf den Geräten, welche ebenfalls auf Ihre Adobe-ID registriert sind.
Details zum Adobe-DRM
Dateiformat: EPUB (Electronic Publication)
EPUB ist ein offener Standard für eBooks und eignet sich besonders zur Darstellung von Belletristik und Sachbüchern. Der Fließtext wird dynamisch an die Display- und Schriftgröße angepasst. Auch für mobile Lesegeräte ist EPUB daher gut geeignet.
Systemvoraussetzungen:
PC/Mac: Mit einem PC oder Mac können Sie dieses eBook lesen. Sie benötigen eine
eReader: Dieses eBook kann mit (fast) allen eBook-Readern gelesen werden. Mit dem amazon-Kindle ist es aber nicht kompatibel.
Smartphone/Tablet: Egal ob Apple oder Android, dieses eBook können Sie lesen. Sie benötigen eine
Geräteliste und zusätzliche Hinweise
Buying eBooks from abroad
For tax law reasons we can sell eBooks just within Germany and Switzerland. Regrettably we cannot fulfill eBook-orders from other countries.
aus dem Bereich