آرتين لتعليم اللغات https://forum.art-en.com/
كتاب لتعليم برنامج Exel https://forum.art-en.com/viewtopic.php?t=8242	صفحة 1 من 1

---

الكاتب:	daherstar [ الثلاثاء مارس 25, 2008 4:06 pm ]
عنوان المشاركة:	كتاب لتعليم برنامج Exel
[img][img][img]أصدقائي الأعزاء بحكم عملي الهندسي وجدت من المفيد المشاركة و لو بشكل متواضع جداً لتعم الفائدة في تعلم هذا البرنامج لمن يرغب بذلك. يرجى تحميل الملف الـ PDF و من ثم فتحه. أتمنى منكم الدعاء[/ ------------------------------------------------------------------------------------------------------ [english]A Guide to Microsoft Excel 2002 for Scientists and Engineers Third Edition Bernard V. Liengme St. Francis Xavier University Nova Scotia, Canada ELSEVIER BUTIERWORTH HEINEYANN AMSTERDAM BOSTON HEIDELBERG LONDON NEW YORK OXFORD PARIS SAN DIEGO SAN FRANCISCO SINGAPORE SYDNEY TOKYO Elsevier Butterworth-Heinemann Linacre House, Jordan Hill, Oxford OX2 8DP 200 Wheeler Road, Burlington, MA 01803 First published 2000 Third edition 2002 Reprinted 2003 Copyright 0 2000, 2002 Bernard V. Liengme. All rights reserved The right of Bernard V. Liengme to be identified as the author of this work has been asserted in accordance with the Copyright, Designs and Patents Act 1988 No part of this publication may be reproduced in any material form (including photocopying or storing in any medium by electronic means and whether or not transiently or incidentally to some other use of this publication) without the written permission of the copyright holder except in accordance with the provisions of the Copyright, Designs and Patents Act 1988 or under the terms of a licence issued by the Copyright Licensing Agency Ltd, 90 Tottenham Court Road, London, England W1T 4LP. Applications for the copyright holder’s written permission to reproduce any part of this publication should be addressed to the publisher. Permissions may be sought directly from Elsevier’s Science and Technology Rights Department in Oxford, UK: phone: (44) (0) 1865 843830; fax: (+a(0)) 1 865 853333;e -mail: permissions@elsevier.co.uk .You may also complete your request on-line via the Elsevier homepage (http://www.elsevier.corn), by selecting ‘Customer Support‘ and then ‘Obtaining Permissions’. British Library Cataloguing in Publication Data A catalogue record for this book is available from the British Library Library of Congress Cataloguing in Publication Data A catalogue record for this book is available from the Library of Congress ISBN 0 7506 5613 1 For information on all Butterworth-Heinemann publications visit our website at http://www.bh.com" onclick="window.open(this.href);return false; Printed and bound in Great Britain by Martins the Printers, Berwick upon Tweed Contents Preface xi 1 The Microsoft@E' xcel Window Concepts Exercise 1 : Anatomy of the Window Exercise 2: The Workspace Exercise 3: The Menu Bar Exercise 4: The Toolbars Exercise 5: Customizing Menus and Toolbars Exercise 6: Getting Help Problems 2 Basic Operations Concepts Exercise 1 : Filling in a Series of Numbers Exercise 2: Entering and Copying a Formula Notes on Copying Formulas Exercise 3: Formatting the Results Notes on Precision and Formatting Exercise 4: Displayed and Stored Values Exercise 5: Formats Get Copied Exercise 6: Too Many Digits Exercise 7: Calculation Example Exercise 8: Entering Formulas by Pointing Exercise 9: References: Relative, Absolute and Mixed Exercise 10: Editing and Formatting Exercise 1 1 : What's in a Name? Exercise 12: Custom Formats Exercise 13 : Symbols and Such Exercise 14: Fractions Natural Language Formulas Problems 3 Printing a Worksheet Concepts Exercise 1 : A Quick Way to Print Exercise 2: Another Way to Print Exercise 3: Page Setup Exercise 4: Changing Margins Exercise 5: Header and Footer Documenting Worksheets vi A Guide to Microso$ Excel 2002 for Scientists and Engineers Exercise 6: Gridlines and Row/Column Headings Exercise 7: Setting the Print Area Exercise 8: Printing Titles Exercise 9: Forcing Page Breaks Exercise 10: Viewing and Printing Formulas Summary of Print Commands 4 Using Functions Concepts Exercise 1 : AutoSum and AutoCalculate Exercise 2: Insert Function Exercise 3: Entering a Function Directly Exercise 4: Mixed Numeric and Text Values Exercise 5: Trigonometric Functions Exercise 6: Exponential Functions Exercise 7: Rounding Function Exercise 8: Array Functions Some Other Mathematical Functions Working with Time Problems 5 Decision Functions Concepts The IF and the Logical Functions Exercise 1 : A What-if Analysis Exercise 2: Avoiding Division by Zero Exercise 3: Quadratic Equation Solver Exercise 4: Protecting the Worksheet Exercise 5: Imaginary Roots Exercise 6: Logical Functions Table Lookup Functions Exercise 7: Horizontal Lookup Exercise 8: Vertical Lookup Exercise 9: Conditional Summing and Counting Exercise 10: Array Formulas Problems 6 Charts Concepts Types of Charts Line and XY (Scatter) Charts Embedded Charts and Chartsheets Anatomy of a Chart Smoothing Option Exercise 1: Creating an XY Chart Contents vii Exercise 2: Modifying a Chart Exercise 3: Line Chart with Two Data Series Exercise 4: XY Chart with Two Y-Axes Exercise 5: Combination Chart Exercise 6: Chart with Error Bars Exercise 7: Changing Axis Crossings Exercise 8: Blank Cells in a Data Series Exercise 9: Selecting Non-adjacent Data Exercise 10: A Chart with Two X-Ranges Exercise 1 1 : A Bar Chart with a Difference Exercise 12: Displaying Units Exercise 13: Setting the Default Chart Type Selecting a Chart Component Too Much Data Dynamic Charts Printing a Chart Problems 7 Curve Fitting Concepts Exercise 1 : Finding the Slope and Intercept Exercise 2: Adding the Trendline to a Chart Exercise 3: Adding the Trendline Equation Exercise 4: The LINEST Function Exercise 5: LINEST with Polynomial Data Exercise 6: Non-linear Plots Exercise 7: Residuals Exercise 8: Calibration Curve Exercise 9: Interpolation Exercise 10: Difference Formulas and Tangents Problems 8 User-defined Functions Concepts Security Alert Exercise 1 : The Visual Basic Editor Syntax for a Function Exercise 2: A Simple Function Naming Functions and Variables Worksheet and VBA Functions Exercise 3: When Things Go Wrong Programming Structures Exercise 4: The IF Structure Exercise 5: Boolean Operators Exercise 6: The SELECT Structure viii A Guide to Microsoft Excel 2002 for Scientists and Engineers Exercise 7: The FOR.. .NEXT Structure Exercise 8: The DO.. .LOOP Structures Variables and Data Types Exercise 9: A User-defined Array Function Exercise 10: Inputting an Array Exercise 1 1 : Improving Insert Function Exercise 12: Some Debugging Tricks Using Functions from Other Workbooks Problems 9 Modelling I Concepts Exercise 1 : Model of a Bouncing Ball Exercise 2: Population Model Exercise 3: Titration Model Exercise 4: Making Waves Exercise 5: Taking Control Problems 10 Solving Equations Concepts A: Finding Roots Exercise 1 : The Bisection Method Finding Roots with Goal Seek Exercise 2: A Simple Quadratic Equation Exercise 3: Solving a Cubic Equation Exercise 4: Transcendental Equations Using Excel’s Solver Exercise 5: Roots of a Cubic Equation with Solver Exercise 6: Using a Constraint Solver Options Concepts B: Solving Simultaneous Equations Exercise 7: A Simple Simultaneous Equations Problem Exercise 8: An Improved Simultaneous Equations Solver Exercise 9: Non-linear Simultaneous Equations Solver Concepts C: Matrix Algebra Exercise 10: Some Matrix Operations Exercise 1 1 : Solving Systems of Linear Equations Concepts D: Curve Fitting Exercise 12: A Linear Curve Fit Exercise 13: A Gaussian Fit with Solver Matrix Diagonal Problems Contents ix 11 Numerical Integration Concepts Exercise 1 : The Trapezoid Rule Exercise 2: Simpson’s ’/3 Rule Exercise 3: Adding Flexibility Exercise 4: Going Modular Exercise 5: Tabular Data Improper Integrals Exercise 6: Gaussian Integration Exercise 7: Monte Carlo Techniques Problems 12 Differential Equations Concepts Exercise 1 : Euler’s Method Exercise 2: The Runge-Kutta Method Exercise 3: Solving with a User-defined Function Simultaneous and Second-order Differential Equations Exercise 4: Solving a Second-order Equation Exercise 5: The Simple Pendulum Problems 13 Modelling I1 Concepts Exercise 1 : The Four-bar Crank: Using Solver Exercise 2: Temperature Profile: Circular References Exercise 3: Temperature Profile: Matrix Method Exercise 4: Emptying the Tank Exercise 5: An Improved Tank Emptying Model Problems 14 Statistics for Experimenters Concepts Exercise 1 : Descriptive Statistics Exercise 2: Frequency Distribution Exercise 3: The Confidence Limits Exercise 4: Experimental and Expected Mean Exercise 5: Pooled Standard Deviation Exercise 6: Comparing Paired Arrays Exercise 7: Comparing Repeated Measurements Exercise 8: The Calibration Curve Revisited Exercise 9: More on the Calibration Curve Problems 15 Report Writing Concepts Exercise 1 : Copy and Paste Exercise 2: Object Embedding Embedding vs Linking Exercise 3: Embedding and Linking Exercise 4: Creating an Equation Exercise 5: Interactive Web Page Answers to Starred Problems Index 315 Preface Microsoft@ Excel is a ‘number crunching’ application with the accountant as its primary target. However, it also provides the scientist or engineer with a very powerful computational tool. True, there are more sophisticated mathematical applications, such as Mathematica, MathCAD, Maple, etc., but none are as widely available as Microsoft Excel. Furthermore, the learning curve for Excel is very gentle; a little learning goes a long way! Once a few basic skills have been mastered, many spreadsheets may be developed in much the same way one would proceed with pencil, paper and calculator, but with more speed, higher precision and greater flexibility. This Guide is designed to give readers a wide range of examples from which they may learn how to apply Excel to problems in their specialized fields. For the student reader, no advanced knowledge of science or engineering is expected and no one who has taken, or is currently taking, an introductory calculus course should find the mathematics difficult. In many cases numerical methods are used to find approximate answers to problems which can be solved by analytical methods. It is a great confidence booster to know you have obtained the correct result and encourages one to try problems for which the exact methods are either very complex or nonexistent. This is very much a practical book designed to show how to get results. The problem sets at the ends of the chapters are part of the learning process and should be attempted. Many of the questions are answered in the last chapter. The Guide is suitable for use as either a textbook in a course on scientific computer applications, as a supplementary text in a numerical methods course, or as a selfstudy book. Professionals may find Excel useful to solve one-off problems rather than writing and debugging a program, or for prototyping and debugging complex programs. A few topics are not covered by the Guide: the major ones being the database functions and subroutine modules. These are fully covered in Excel books targeted at the business community and the techniques are applicable to any field. When I was working on the first edition of this book Microsoft introduced Excel 97, Since then we have had Excel 2000 and then xii A Guide to Microsoft Excel 2002 for Scientists and Engineers Information box: Boxes like this contain additional information, shortcuts, etc. The Save tool New to Excel 2002 Website: http://www.bh.com" onclick="window.open(this.href);return false; /companions/0750656 13 1 Excel 2002. While these have only a marginal effect on functionality, there have been some significant changes in the appearance of dialog boxes and in some terminology. This book uses Excel 2002 for its screen captures but readers with earlier versions should have no difficulty following the instructions. I am indebted to many people: my students and colleagues for helpful suggestions, to readers of earlier versions who e-mailed comments, Nikki Dennis and Matthew Flynn for agreeing to publish the first edition, Rachel Hudson ofButtenvorth-Heinemann for her invaluable assistance with the present project, and my wife, Pauline, without whom this book would still be a pipe-dream. I welcome e-mailed comments and corrections (hopefully with a third edition, these will be few and minor!), and will try to respond to them all. Please check my web site and the Guide’s web site at http://www.bh.com/companions/0750656" onclick="window.open(this.href);return false; 13 1 for updates. I hope you enjoy learning to ‘excel’. Bernard V. Liengme bliengme@stfx.ca http:/www,stfx.ca/people/bli engme Conventions used in this book Information boxes in the left margin are used to convey additional information, tips, shortcuts, etc. Data which the user is expected to type is displayed in a monospaced font. This avoids the problems of using quotes. For example; in cell A1 enter the text Resistor Codes. Non-printing keys are shown as graphics. For example, rather than asking the reader to press the Control and Home keys, we use text such as: press [ctrll+[Homel. When two keys are shown separated by f, the user must hold down the first key while tapping the second. Generally when a new reference is made to a button on a toolbar, a graphic of the icon is shown in the left column. New features in Microsoft Excel 2002 are flagged in the left margin. In the Problems section of each chapter, an asterisk against a problem number indicates that a solution is given at the end of the book. Excel files for answered problems and additional files may be found at: http://www.bh.com/companions/0750656113" onclick="window.open(this.href);return false;. The Microsoft@E xcel Window Concepts Exercise 1: Anatomy of the Window You are probably anxious to start using Microsoft@ Excel. However, you must first become familiar with its window and we, the reader and the writer, must agree on some basic terminology. It is useful to conform to the terminology that Microsoft uses so that you will know what topic to search in Help when more assistance is needed. Unless you are new to Windows@, many of the topics covered in this chapter will be familiar. You should learn the names of the various parts of the window and how to access commands using menus and toolbars. Begin this exercise by starting Excel. Your screen will look similar to that in Figure 1 .l. There could be some minor differences because Excel allows the user to customize the menus and toolbars. Furthermore, starting with Excel 2000, the items displayed in toolbars and in the initial drop down menus change with usage. A tool that has been recently used will be displayed on the initial menu. We will look at customization in Exercise 5. It is convenient to divide the window into seven main parts: title bar, menu bar, Standard toolbar, Formatting toolbar, workspace, task pane and status bar. You will be familiar with the first four areas from using other applications so they will be described only briefly. Title bar In starting Microsoft@E xcel, we have opened a new workbook. Because we have not yet saved our work, Excel has given this the default name of Bookl. Menu bar The menu bar provides the user with one way to access the Microsoft Excel commands. Commands are actions you perform on your worksheet. Examples are: saving the data to a file, printing a worksheet, changing the appearance of some text, etc. Toolbars Toolbars are another, more intuitive and quicker, method of accessing commands. Each tool on a toolbar is depicted by an icon. 2 A Guide to Microsoft Excel 2002 for Scientists and Engineers Specifications: For other limits (such as the maximum number of characters allowed in a cell) use the Answer Wizard in Help to search for specifications. We may speak of, for example, ‘clicking on the Copy tool’ or the ‘Copy button.’ Formula bar We will examine this more closely in a later chapter. For now, click the mouse in several places within the workspace and watch the information change in the name box which is the left-hand part of the formula bar. As you select different cells you will also note how the corresponding column and row headings are highlighted. Works pace This is the central part of your work. It is here that you will type data and perform calculations. Note how the main part of the space is divided by gridlines into rows and columns. The smallest unit of space, where a row and a column intersect, is called a cell. At the top of the worksheet are the 256 column headings starting with A and ending with IV. To the left are the row headings numbered 1 to 65,536. How many cells are there on a single worksheet? I Title I bar MeI nu bFaorr mula bar tSotoantbdaarr d tFooorlmbaart ting Questi1o n bToaxs kDane What we are looking at in Figure 1.1 is only part of one of the worksheets which makes up your workbook. To the far right, and at the bottom of the workspace, you will see the vertical and The Microsoft Excel Window 3 New to Excel 2002 horizontal scroll bars which allow you to view other parts of the worksheet. Also at the bottom are the sheet tabs which give you access to the other worksheets. Excel normally opens a new workbook with three worksheets. It is possible that your copy of Excel has been configured to give a new workbook some other number of worksheets. We may delete or add extra worksheets to a maximum of 255 depending on the amount of memory in your computer. Later, we will be introduced to chart sheets. We will not investigate module sheets which were made redundant with Microsoft Excel 95. Task pane In Excel 2002, to the right of the workspace is the task pane. This is designed as a productivity tool. It adds no new functionality but groups frequently used tools in one place. However, it greatly reduces the amount of the workspace that is visible so you may wish to close it. There is more on this topic in Exercises 4 and 5. Question box In Excel 2002, to the right of the menu bar is the question box. This provides a handy way of starting Help and recalling earlier topics you have looked up. Status bar The status bar provides information. To the left is the message area. If your mouse pointer is within the workbook area, this should be showing the word ‘Ready’. To the right are some sculptured boxes called the keyboard indicators. Press the [Capslock] key a few times and watch the text ‘CAPS’ appear and disappear. Cells and ranges Clearly, we need a way to refer to a specific cell on the worksheet. We have seen that a cell occurs at the intersection of a column and a row. We speak of a cell reference which is a combination of the column heading and the row number. The cell at the top left, which is at the intersection of column A and row 1, has the cell reference Al. The cell below is A2 while the cell to the right is B1. This method ofreferring to cells using the column letter is called the A1 method. There is another method in which the column letter is converted to a number; this is called the R1 C1 method since the top left cell has the address R1 C 1 using this method. We shall not pursue this. 4 A Guide to Microsoft Excel 2002 for Scientists and Engineers 3 length 4 width Exercise 2: The Workspace Carefilly note that in this paragraph we have not spoken of a cell name. In Chapter 2 we find that this has a very specific meaning. A range is a rectangular block of cells. The cells Al, A2, A3, B1, B2 and B3 form a range which we can refer to using Al:B3. In general a range is denoted by the cell references of the top left cell and the bottom right cell separated by a colon. Since a range may be a one by one block, the word range may also refer to a single cell. In a later chapter, we will learn how to reference a range from another worksheet in the current or another workbook. area 42 perimetre 26 In this exercise you will learn how to enter data into a cell, edit this data and move around (navigate) the worksheet. We also introduce the formula concept. By the end of step (8) of this exercise your worksheet should resemble that in Figure 1.2, complete with misspellings. If you do not have time to complete the exercise in one session go to step (a) of Exercise 3 to see how to save your work. I I A I B I C I I 1 ]Area and Perimeter calculations I (a) We wish to enter some data into cell A1 so this needs to be the active cell. There are two ways of knowing which is the active cell. Look in the name box of the formula bar (it is just above the A column heading). Does it say ‘A1 ’? Alternatively, in the worksheet is there a border around cell AI? This is called the cell selector. If AI is not the active cell, the quickest way to make it so is to press @+[Home]. (b) With AI as the active cell, type the heading Area and Perimeter calculations and then press the [Enterl key. Note how the active cell becomes A2. Pressing [Enterl always moves the cell selector down one row. If you make a typing error continue on; we will see how to make corrections later. Press [Enterl once more to move to A3. The Microsoft Excel Window 5 may be completed in one of severa ways. They differ in how the: relocate the cell pointer. -We ma-y use -the ke-ys: (c) In A3 type the word length but do not press [Enter]. This time use the mouse to click the Enter button (the green t4 button) in the formula bar - see Figure 1.3. This time the cell selector has not moved. We have now seen two methods of completing a data entry: pressing the [Enter] key and clicking the Enter button on the formula bar. Comnleting an entrv: A cell entry \Enter&], I c - S h i f t J + ( E n t e r ~ ] , [TabSl, [z5xshlf+[TabS), P J, m, FJ , @ (d) To move to cell A4 , press the @ or the [wke]y. Do not left click the cell A4. It will get you to A4 but this is a bad We may also use the Enter button habit. If you are working with a formula, clicking another cell (4)on the formula bar. This enters its reference into the formula. Continue typing the displays only when a cell is being words width, area and perimetre in A4, A5 and A6, modified. respectively. We will correct the incorrect spelling later. are urged not to develop the habit of clicking another cell to (e) In cells B3 and B4 enter the values 6 and 7, respectively. complete an entry; this will give erroneous results when you start working with formulas. If you start an entry in the wrong cell. use the [Escl key or the Cancel button (X) on the formula bar to terminate the action. (f) In B5 we will use a formula to calculate the area. In this cell type =B3*B4. There are two important items to note here: (1) a formula always begins with the equal sign, and (2) the multiplication operator is not x but *. Ifyou pressed [Enter] rather than the Enter (4b)utt on in the formula bar to complete the formula, return to cell B5 by clicking it once. Notice how the cell now displays the result of the formula while in the formula bar we can see the actual contents of this cell. Cancel Enter button button Figure 1.3 (g) In B6 we shall purposely make a mistake. To compute the perimeter, type the formula =2*B3+B4 and click the Enter button (4)I.f you pressed [Enter], go back to B6 since we are going to correct it. The formula we need is 2*(63+B4) to give 6 A Guide to Microsoft Excel 2002 for Scientists and Engineers the correct result; the reason for this is given in Chapter 3. Here we will see one way of making a correction. Move the mouse until the pointer is in the formula bar; note how the pointer changes shape from a hollow + shape to a 1 (I-beam) shape. Move the I-beam to just after the * symbol and press the a key to insert the opening parenthesis. Now press the (End] key and add the closing parenthesis, a.Cl ick the Enter button (d)to complete the editing. The cell should now show the correct value. Your worksheet should now be similar to that in Figure 1.2. Type another number in B3. Note how the values shown in cells B5 and B6 change as soon as the entry is completed. We have entered three types of data: text, numbers and formulas. Note how text is left justified while numbers are displayed right justified. Later we will see how to change this justification or alignment. We have learnt how to move armnd the worksheet using the mouse and the arrow keys. Before completing this exercise, take some time out to explore how the scroll bars work and find what happens when you Ip-r[ess an .]pUd=[ Compare the effects with pressing these keys when the key is held down. Remember that m+m will always return you to A1 . If your mouse has a wheel between the two buttons you may wish to experiment with it. With cell AI as the active cell, see what @ + a does. Try the same command starting first from A3 and then from A6. Return to A1 and find the effect of + [End]. You may also try Go To - either from the Edit menu or by using either [F51 or [ctrl+G as a shortcut. Now we will alter the text in cells AI to A6. For the purpose of the exercise we will assume that we want each word to start with a capital letter. There are a number of ways of doing this. (h) Make A3 the active cell and retype the entire word Length to make the correction. (i) The next correction will be made by editing. Using [ctrll+lHome], make AI active. To edit the cell press m. The status bar will display Edit rather than Ready and the mouse pointer has become an I-beam. You can also tell you are in Edit mode by The Microsoft Excel Window 7 looking at the lower right corner of the cell selector border. In Ready mode it has a block shape called the cell handle; in Edit mode the corner resembles an inverted L shape. Position the pointer before the first letter of ‘calculations’, press [Deletel and then an upper ‘cy. While we are in A1 , we can try another experiment. Because A1 contains text and the cells to its right are empty, the text in A 1 can overflow into B 1 and C 1. Click on C 1, type some text and press [Enter]. Now we have ‘lost’ some characters of the last word in A1 . To restore it click the Undo key or use the Undo item on the Edit menu. The Undo tool Shortcut: The keyboard shortcut for Undo is m+Z. Hold down m while tapping the Z key. (i) Double click on A4 and you will find that this too places you in edit mode. Press the [End) key. What happens? Now press [Home]. Type a ‘W’. Now we have two ‘w’s because the default in Excel is insert mode - anything we type is inserted into existing text. You can toggle between insert and overtype (also called typeover) mode with the [Insert] key. In overtype mode the status bar displays OVR and in the cell one or more characters are highlighted. Experiment with the [CBksp] and [Deletel keys while editing the cell entry to Width. (k) In steps (i) and (i) we did the editing within the cell. Make A5 the active cell but this time click the mouse in the formula bar and do the editing there. Press [ww]hen you have made the change. Do the same with A6 changing only the first letter; do not correct its spelling. After clicking the Entry button (a), click the Spelling button and let Excel find this and any other H T h e Spelling tool Shortcut: The keyboard shortcut for Spelling is a. spelling errors. Exercise 3: The Menu In this exercise we look at the main menu bar and find out how to use some of its various commands. (a) We are going to save the file containing the work you did in Exercise 2. Click File on the menu bar to pull down its menu. Choose the Save item to bring up the Save dialog which is common to all Windows applications. Select the folder you have chosen to keep your Excel file in. I suggest you name this workbook CHAP1 .XLS so that we can refer to it again. Bar Note how the title bar has changed to reflect the new name. Next time you start Excel, the name of this file will appear at the bottom of the File menu. This gives you a quick way of 8 A Guide to Microsoft Excel 2002 for Scientists and Engineers reopening the file. This feature may not be available if you are on a network. (b) Next we will explore the Format menu. You may be wondering Keyboard alternative: An why some letters in the names of menu items are underlined. alternative way to open a menu is: You can open a menu using this letter in association with the Press @, use the @ and keys key in place of clicking the mouse. To open the Format to the main menu and menu, hold down and press the @ key. Note the triangle press [Entert']. symbol (b) to the right of the Columns item. When you click on an item with this symbol a submenu appears. Click on -Co lumns and note the ellipses (three dots .. . ) after the Width item. When you click on such an item, in a menu or a submenu, a dialog box is opened. Click on Width to open its dialog box. Click the Cancel button since we do not wish to change the column width at this time. (c) Open the Format menu again. Excel 2000 and 2002 display a drop down menu with a downward pointing arrow at the bottom. To make the menu less cluttered, items that you seldom use are not immediately displayed. The menu will expand if you wait or if you click on the arrow - see Figure 1.4. Most expanded menus are much larger than this example! Use the (Escl key to close the menu. In Exercise 4 we learn how to customize Excel so that all menu items show immediately. W C l i c k to expand menu Figure 1.4 The Microsoft Excel Window 9 Exercise 4: The Tool bars The Print tool B T h e What’s This tool WThe Spell tool (d) You may sometimes wish to use a keyboard shortcut rather than clicking the mouse. Open the Edit menu. If you have not copied something since logging on to your computer, the clipboard will be empty and the Paste feature will be displayed in light text - we say it is greyed out. If an item in a menu is greyed out it is currently unavailable to you. To the right of Cut we see the text [Ctrll+X, while to the right of Go To is ICtrl]+G or [. These are notes telling you that Cut may be accessed by holding down the [Ctrll key while you press the X key without ever opening a menu. Experiment with the @+C and m+V to copy the entry in A6 to A10. To close a menu opened by mistake either click the mouse anywhere in the workbook window or repeatedly press the [Escl key until nothing in the menu bar is highlighted. Microsoft Excel provides a number of toolbars. The Standard and the Formatting toolbars are generally displayed all the time. Others appear when the context is right. An icon on a toolbar allows the user to quickly access one of the Excel commands without the need to open a menu. In addition, since the icons are simple pictures, there is little to learn. For example, if you wish to print a worksheet using the menu bar, you either know the Print command is in the File menu or you must go searching for it. On the other hand, the icon depicting a printer is self-explanatory and easy to find. (a) The pictures for some buttons are less obvious. However, when you move the mouse pointer over an icon without clicking and wait a second or two, Excel displays a tool tip naming the tool associated with the icon. For some icons you may find that this method does not give enough information. Let’s use the context-sensitive method to get more information. Open the Help menu and click on the item What s This? The cursor changes to a question mark. Move the cursor to the Spell tool (it has ABC on its icon) and left click it. A larger tool tip with additional information is displayed. Note that if you need additional information about another icon you must visit the Help menu item again. (b) By default, the Standard and Formatting toolbars are shown side by side in newer versions of Excel to give more room for the worksheet area. Of course, this means that fewer tools can IO A Guide to Microsoft Excel 2002 for Scientists and Engineers be displayed. Figure 1.5 shows the Formatting toolbar. Note the double arrow on the right of the toolbar; this gives us access to the otherwise hidden tools. Figure 1.5 The handle on the left of the toolbar may be used to move the toolbar. When the cursor is moved over the handle we get a cursor with four arrows +. Carefully drag the Formatting toolbar’s handle to the left to reveal more formatting tools but fewer Standard tools. If your toolbar moves below the Standard toolbar, use the menu commandToolslCustomize and click on the Options tab. Remove the check mark from Show Standard and Formatting toolbars on two rows - see Figure 1.6. Note also the options concerning the Menu commands. If you drag the toolbar down too far it will become a floating menu; it will be a window with its own title bar. To dock the toolbar drag it with the title bar into the area where Excel generally displays toolbars. Figure 1.6 The Microsoft Excel Window I I (d) While by default Excel displays only the Standard and the Formatting toolbars, it has a wide variety of specialized toolbars. Generally these become visible when needed. So, for example, when we are working on a chart, Excel displays the Chart toolbar provided it is checked in the YiewlToolbars menu. The Drawing toolbar is useful for annotating a worksheet with arrows and other symbols. Use the menu command -Vi ewlIoolbars to make the Drawing toolbar visible. Note that its normal docking place is above the status bar but it can be made floating by dragging its handle. Experiment with the drawing tools and then use YiewlIoolbars to hide the toolbar. The command YiewJTask Pane may be used to open and close the task pane. Exercise 5: Customizing Menus The menus and toolbars are set up for the average user. Sometimes we would like something different. Microsoft Excel makes it easy to modify the menus and the toolbars. (a) A menu or toolbar can be customized only when the Customization window is open. We do this with -To olslcustomize. The window is depicted in Figure 1.7. It is similar to Figure 1.6 but this time the Toolbars tab is open. and Toolbars (b) When Figure 1.7 was captured, the Drawing toolbar was displayed. We saw in step (c) of Exercise 4 how to open and close toolbars with YiewlIoolbars. The Customize window provides another method. Once again all that is needed is to place or remove a check mark in the appropriate box. If you scroll down the list of toolbars you will find another way of displaying the task pane. WThe Hyperlink tool (c) Let us assume the user does not need the Insert hyperlink tool. With the Customize window open, place the mouse pointer over the Insert hyperlink tool in the Standard toolbar and, holding down the left mouse button, drag the icon off the toolbar. To restore the tool, select Standard in the Toolbars tab and click on the Reset button. Click on Yes when Excel asks you to confirm the action. The same dragging method may be used to reposition an icon on a toolbar. 12 A Guide to Microsoft Excel 2002 for Scientists and Engineers Figure 1.7 (d) To find even more tools, click on the Command tab of the Customize window. Select a topic such as Web and move the vertical slider to review the available tools. To add a tool, drag it onto the toolbar at the required position. Try dragging the Back tool on the standard toolbar next to the Insert hyperlink tool. Use the Beset button on the Toolbars tab to restore Excel to the default setting. (e) Items on the menu bar and on popup menus may be dragged to new positions in the same manner as tools. Items may be added to the menu bar. A tool may be placed on the menu bar if you so wish. To restore the menu bar, locate Worksheet Menu Bar in the list of toolbars and click the Reset button. To have all commands display immediately you open a menu bar item, remove the check mark from the Menus show recently used commandsflrst box of the Options tab. Exercise 6: Getting In this exercise we briefly look at how to get help in Microsoft Excel. Suppose we wish to know how to change the width of a column. There is no need to type a complete question such as How do I change a column width? You need enter only the key words; so in the case we could use column width. Help @ The Help tool The Microsoft Excel Window I3 (a) Click on either the Help item of the menu bar or on the Help tool, or press [F11. (b) Unless your copy of Excel has been set to do otherwise (see step (c) below), one of the Assistants will appear together with a dialog box into which you type your question. Then click on Search and click the item that seems closest to your question - Figure 1.8. This will open the full Help window. Figure 1.8 rthevlbii'of a 3 Dch3tt ice data for a PIV ze chart items wth the 5 from moving and 5121 n imparted text tile Iw / / Change column width and row height Change the width: b Of a single cokmn P Ofrnuft~pleccob.nnns b To fR the contmts b TO a WeClfK 4 t h P To match another cbmn change the ddbult width 1 I Right-click a sheet tab, and then clickselect All Sheets on the shortcut menu 2 On the Format mem, pomt to Column, and then ckk Standard Width 3 Type a new meawrement The number that w a r s n the Standard column ddth box IS the average number of &nts 0-9 of the standard font that Flt in a cell b Tip Change the Mqht P Ofasmglerow b OF multiple rows P To fit the contents b Toa spec& hetoht Figure 1.9 I4 A Guide to Microsoft Excel 2002 for Scientists and Engineers New to Excel 2002 If no Assistant appears you will see the full Help window where you can type your question in the text box associated with the Answer Wizard - Figure 1.9. With Excel 2002 one can type the question in the question box - less intrusive than the Assistant. Furthermore, the question box retains your question during a session so you can quickly return to a Help topic. (c) The information you need is to the right of the Help window. Remember that text in blue will expand to give more information or is a link to another part of Help. (d) In this step we remove the Assistant. There are two reasons for doing so: (1) some users find the animation annoying and (2) you may wish to see more of the Help system. With the Assistant’s dialog box showing (click on the Help tool or on the Assistant graphic) and select Option in the box. Alternatively, right click on the Assistant and select the Option item in the popup menu. In the resulting dialog box, remove the check mark from Use the Ofice Assistant box. At this point you may wish to look at the other options. Restoring the Assistant is simple: click on Help and on the drop down menu select the item Show the Office Assistant. (e) Experiment with the tools on the Help toolbar - you can do no harm! One of the tools hidedunhides one half of the Help window. Did you find it? Open the Contents tab and explore that part of Help. The Index tab is sometimes useful if the Answer Wizard fails to locate your answer. The Microsoft Excel Window 15 Problems It would take a large book to explain every item on the menu and toolbars. There are two ways to find what various items do: using Help and experimenting. These problems are designed to encourage you to try both ways. You should also learn how to use the Help command Index. 1. Using Exercise 4(b) as a guide, find the purpose of these buttons. Use Help on the menu bar to learn more about these buttons. Using either the Help button or the Help command, find how to hide a column. 2. Construct a worksheet with the misspellings as shown in the figure below. (a) Use the Spelling tool to correct the misspellings. (b) Experiment with items on the Format menu to make the text: (i) in A1 italic, (ii) in A2 centred in the cell, and (iii) in A3 centred in the cells A3 and B3. (c) Click the Undo button. What ha pens? (d) Locate the Zoom tool ( d 100% v ) and experiment to find its purpose. (e) Find a menu command with the same purpose as the tool just used. (f) Locate the Borders tool and put a black border around each cell in A1 :A3. (g) Select Cl:C3 and experiment with the FormatlCglls command to find how to draw a red border around the outside of C 1 :C3. (h) Select Al:C3 and experiment with the command -Ed itlCleg-[Formatst o see what it does. 3. On a new worksheet enter 1234567.8 in Al. Observe how the column width is automatically adjusted to accommodate the number. Using the Decrease Decimals tool, format the cell to show no decimal places. I*oxI The Decrease Decimals tool 16 A Guide to Microsoft Excel 2002~foSr cientists and Engineers In C1:Dl enter the words: Acceleration, Velocity. The first word is truncated. Use the mouse to drag the line between the C and D column headers and widen column C to have the whole word displayed. 4. Find out how to use the Office Clipboard. This has the advantage over the Windows Clipboard in that it can hold more than one object. You can even place a series of objects on the Office Clipboard and paste the entire collection with one command. Basic Operations Concepts Note: Cell reference is the correct term to use when speaking about, for example, AI or (320. You may find books that use the term cell address. However, this is a term that is never used in Microsoft documentation. It was used by Lotus 1-2-3, a spreadsheet application that preceded Microsoft Excel. Surprisingly, Excel does have a worksheet function called ADDRESS that returns a cell reference! Do not use the term name in place of reference; later in this chapter we will learn that this term has a very specific use in Excel. This chapter introduces a number of features needed to prepare a worksheet. We see how to fill a range with a series of numbers, use a simple formula, copy a formula to a range, format numbers and change column widths. The concepts of relative and absolute references are examined. First we review some terminology. Cell reference This term has been used before; it is how we refer to a specific cell. The reference for a cell consists of the letter for the column in which the cell is located followed by the row holding the cell. The top left cell in a worksheet has the reference A1 . To refer to the cell A 10 on a worksheet named Data when that is not the current sheet, we would use Data!Al 0. To refer to a cell in another, open workbook a reference in the form [Book2.xls]Sheetl !A1 is used. A reference to a cell in an unopen file requires the full path and the file name. We may use 'C:\MyData\[Book2.~ls]SheetI'!Al for example; note that the path and file name are enclosed in single quotes. Range A range is a rectangular group of one or more cells. To reference a range we use the top left cell reference separated from the bottom right reference by a colon. Thus AI : B2 refers to the cells A1 , A2, B1 and B2. Similarly, AI:A4 refers to the cells A1 to A4. References may be made to ranges in other worksheets and files. We may refer to the range 'C:\MyData\[Book2.~ls]Shee't!l AI :B4 for example. Active cell The active cell is the one with a heavy black border. A cell must be active if we wish to type data into it. A cell is made active by moving the border with various keys. The key [wm)ove s it down one row ]w-[+Ih-[ ile moves it up one row; moves it right by one column and [Shift]+[TabS] moves it left one column. The keys ~ l + ldo~ the~ salme . Of course, you can move the mouse pointer and click on a cell to make it active. The active cell is surrounded by the selection box which has afill handle at the bottom right corner. The reference of the active cell I8 A Guide to Microsoft Excel 2002 for Scientists and Engineers Note: The format in a cell may be Exercise 1: Filling in a Series of Numbers is displayed in the name box of the formula bar. In addition, the column and row headings for the active cell are enhanced. Selection If a cell is clicked and the mouse is moved while the button is held down (dragging), a range may be selected. Figure 2.1 shows a selected range. A range may also be selected with 1~~~~ while holding down [Shift]W. hen a range is selected, all the cells other than the active cell have a blue-grey background. f-- Selection box i t i --- Fill handle Figure 2.1 Formatting The manner in which a cell entry is displayed may be changed by formatting the cell. Frequently, we use formatting to change the way a cell displays its numeric value. We may change the number of decimals, show the value in scientific notation or with commas, etc. Other formatting options are associated with the typeface and the colour of the displayed entry. The horizontal and vertical positioning of the entry within the cell is also a formatting feature. In addition, cells and ranges may be given borders. In this book we concentrate on formatting for practical rather than presentation purposes. For Exercises 1 to 3, imagine we have a laboratory heating apparatus with a thermometer calibrated in degrees Fahrenheit. We need a table to give approximate Celsius values. On completion of the exercise, the worksheet will resemble that in Figure 2.2. (a) Open a new workbook. Type the text in cells AI to B3 as in Figure 2.2. Note that the text is automatically left justified. Remember to pre 1s-1s to complete the entry in each cell. You may also complete an entry by any of the methods mentioned in Exercise 2 of Chapter 1 including clicking the green check mark in the formula bar. The check mark is visible only when you are entering or editing an entry. Basic Operations 19 - The Right Align tool The Save tool (b) Select A3:B3 and click the Right Align button on the Formatting toolbar. This will vertical align the text with the numbers we are about to enter. A 1 B I C 1 Temperature Conversion Table I I Figure 2.2 (c) Type the numbers 50 and 100 in A4 and A5, respectively. (d) Rather than type the rest of the values we will use the AutoFill feature. Select A4:A5 and move the mouse pointer over the fill handle. The pointer changes from an open to a solid cross - see Figure 2.3. Drag the fill handle down to A 13. Note the screen tip showing the value in the last cell as you drag the mouse down. The range A4:A13 will now have the values shown in Figure 2.2. The same result can be obtained using the command -Ed itlFjlllSeries. More information on this convenient way of getting data into a worksheet can be found in Help under Fill in a series of numbers or Fill Series command. (e) Save the workbook as CHAP2.XLS using either the menu command FilelSave or the Save tool. 20 A Guide to Microsoft Excel 2002 for Scientists and Engineers Exercise 2: Entering and Copying a Formula Correcting an error going to make mist learning Excel. Here a quickly correcting then 1. If you start existing entry, press before the error is made. 2. You compl rea.liz.e you ha -Ed itlLJndo or the shortcut jCtrl4-Z to go back one step. 3. If all else fails, selec press [Delete] and start again. A formula is an expression telling Excel to perform an operation. Initially, we will limit ourselves to arithmetic operations. All formulas begin with the equal symbol (=). To perform an arithmetic operation we need to follow the = symbol with an arithmetic expression. The expression may contain numeric values, cell references and arithmetic operators. An example of a formula is =AI +A2+A3 which returns the value resulting from the addition of the values in the three cells. If you were asked to compute the simple expression 3 + 4 x 2 would you reply 1 1 or 14? Most of us would give the former as would Microsoft Excel. In Excel, and in other programs written in all the major languages, an expression is evaluated following this order of precedence for the arithmetic operators: Negation (-) Exponentiation (A) Multiplication and division (*, /) Addition and subtraction (+, - ) This precedence order may be overridden by using parentheses. So 2*5 + 6 (or 6 + 2*5) evaluates to 16 while 2*(5+6) evaluates to 22. Note that -3"2 evaluates to 9 not -9 because negation has a higher ranking than exponentiation so it is evaluated as 'the square of negative 3' not as 'the negative of the square of 3'. To get the negative value use -(3"2). (a) On Sheet1 of the CHAP2.XLS workbook, in B4 type the formula to convert Fahrenheit to Celsius, i.e. =(5/9)* (A4 - 32). Do not overlook the equal sign. The spaces around the operators are optional but they do make the formula more readable. If you type lowercase letters for a cell reference, Microsoft Excel automatically converts them to uppercase. The cell displays the value 10. Ifyou make B4 the active cell, you will see =(5/9) * (A4 - 32) in the formula bar. (b) We need to copy this formula to B5:B12. If you are familiar with Windows you will know how to copy and paste using the -Ed it menu, shortcuts such as [Ctrll+C and ICtrl]+V, or the Copy and Paste toolbar buttons. We will use another method. Select B4, 'grab' the fill handle and drag it down to B13 to copy the contents of B4 to these cells below it. (c) Now we will explore yet another way to copy cells. Begin by selecting B5:B 12 and pressing [oeletel to clear the entries in the range. Place the pointer over the fill handle of B4 and double Basic Operations 21 click. Excel detects that columns A and B form a table and completes the Autofill. This trick works only with vertical tables. (d) Save the workbook by clicking the appropriate tool. The values in B4:B 13 will be similar to those in Figure 2.2 but will not be integer values. In the next exercise we format the results to show integer values. Notes on Copying Select B5 and look in the formula bar. This cell contains the formula =(5/9)*(A5 - 32) while the formula we copied from B4 has =(5/9)*(A4 - 32). When the formula was copied from B4 to B5, Microsoft Excel automatically adjusted it to reflect its new position, changing the A4 to A5. We need to understand the concept of a relative reference. How does Excel interpret A4 in the original formula in cell B4? Rather than ‘thinking’ that it needs the value in A4, Excel ‘reasons’ along these lines: the formula is in B4, so A4 refers to the value in a cell one to the left but in the same row. When it copies the formula to B5, the A4 becomes A5 - one cell to the left in the same row as the formula’s new location. Formulas Figure 2.4 shows the results of copying a formula to various other cells. Cells Al:B4 contain values. The formula in C2 is =2*A1. Relative to C2, the cell A1 is two columns to the left and one row up. When C2 is copied to C4, the resulting formula is =2*A3 since relative to C4 the cell A3 is two columns to the left and one row up. Similarly, when C2 is copied to D3, we get =2*B2 since, starting at D3, and moving two columns to the left and one row up we arrive at B2. =2*82 Figure 2.4 You should note that if a formula is moved, rather than copied, no adjustment takes place. Later in this chapter we will explore absolute referencing which allows us to write a formula in such a way that copying will modify cell references such that only the row, the column or neither is changed. 22 A Guide to Microsoft Excel 2002 for Scientists and Engineers Exercise 3: Formatting Resu I ts Our project is almost complete. All that remains is to change the way the results are displayed. For this project it is inappropriate to display so many digits after the decimal; we need integer values. (a) Select range B4:B13 on the worksheet of the previous exercise. From the Format command on the menu select Cells. In the resulting dialog box select the Number tab - see Figure 2.5. The General format will be highlighted in the Category box. This is the default number format. In most cases, an entry in a cell having the General format is displayed the same way as it was typed. When the cell is not wide enough to show the entire number, the General format rounds numbers with decimals and uses scientific notation for very large and small numbers . the (b) In the Category box, click the Number tab. Change the value in the Decimal places box to 0. You may use the spinner or type the value in the box. Click the OK button to close the dialog box. Your worksheet now displays integer values. Figure 2.5 (c) There is another way to do this. Click the Undo button on the The Undo tool Standard toolbar to display the original values. (d) Select B4:B 15 and click once on the Increase Decimals button: Basic Operations 23 pJ The Increase Decimals tool b"gl The Decrease Decimals tool if you try to use the Decrease Decimals tool Excel will make a ping sound to warn you this is impossible - B4 has an integer value so it cannot be made to display any fewer decimal places. All the numbers now display one decimal place. Click the Decrease Decimals button once to display them all as integers. It is important to know that formatting changes only the way a value is displayed. It does not change the actual value stored in a cell. We look at this in Exercise 4. (e) Save the workbook CHAP2.XLS. Notes on Precision Numbers are stored with 15-digit precision by Microsoft Excel. The number of digits displayed depends on the format and width of the cell. If the user has not applied a format, Excel uses the General format. A cell may be formatted to display a required number of decimal places or to use scientific notation. We do this with the command Fo-mat(Cgll(Numbero r by using the Increase and Decrease Decimals button. and Formatting In the next exercise we demonstrate that formatting does not alter the stored value. Later we will examine functions that round values to a specified number of decimal places. You should also be aware that when a cell is copied or moved, the target cell gets the same format as the source cell. Excel can store positive numbers as large as 9.99 999 999 999 999 x and as small as 1 x 10-'07. The range for negative values is - 9.99 999 999 999 999 x 1 0+30to7 -1 x 1 O-307. The range of values in Microsoft Excel is thus while a typical hand calculator has a range of 1 O*99. You should be aware that conversion from decimal to binary can result in round-off errors. Suppose you perform two complex calculations and expect A99 and B99 to have the same values. Because of round-off errors, the two values may differ by a small amount and the formula =A99 - 699 may not give exactly zero but a value such as 0.000 000 000 000 008 or 8E-15. Just as in decimal notation (base 10) the result for 10/3 cannot be written with infinite precision as a real number, so in binary (base 2) there are some real numbers that cannot be represented exactly. 24 A Guide to Microsoft Excel 2002 for Scientists and Engineers A I B I C I Displayed and stored values Enter the values 27.05 and 26.1 in A1 and A2 of an empty worksheet. In A3 enter =AI - A2 and the value 0.95 is displayed as expected. Now we look at the actual value stored for A3. Progressively increase the number of displayed decimal digits by clicking the appropriate button on the Formatting toolbar. After a while the value 0.94999 ... is displayed. Clearly there has been round-off error since the result should be 0.95 exactly. Programmers seldom test if two numbers are exactly equal but rather they test if the absolute difference in the two numbers is less than some arbitrarily small quantity. D E Exercise 4: Displayed The purpose of this exercise is to demonstrate that formatting changes only the way in which a value is displayed. The stored value is unaltered. When completed the worksheet will resemble that in Figure 2.6. and Stored Values 2 1 1 Figure 2.6 (a) Open the workbook CHAP2.XLS and click on the Sheet2 tab to begin a new worksheet. Begin by typing the text in A 1 :C3. Entering the text in B3:C3 presents a small problem. The equal sign alerts Excel to use a formula but this is not what we want. Before typing the equal sign, type a single quote (an apostrophe) to tell Excel that you want text. (b) In row 4 enter the following: A4: the value 1.234. B4: the formula =A4 C4: the formula =A4+2 D4: the formula =A4*2 (c) Using the Decimal tools, format A4 to display one decimal place. The cells A4:D4 should show the same values as in Figure 2.6. Basic Operations 25 (d) Make A4 the active cell. The value displayed in A4 is 1.2 but from the formula bar we see that the stored value is 1.234. Unfortunately, one cannot see the applied format by looking here. You may check how a cell is formatted by selecting the cell and using the FgmatlCglllNumber command. If we wish to have a value stored with a set number of decimal digits, we use the ROUND function which is discussed in a later chapter. It is possible to use the IoolslQption command to have Excel use the same precision as the displayed value. This may be useful in financial worksheets but is not recommended. In the next part of the exercise we see an oddity of Microsoft Excel. When a formula is typed into a cell which has not been previously formatted (Le. it has the General format) and the formula contains only (i) references to one or more cells with identical formats and (ii) either no operator, or only the addition or subtraction operator, then the cell with the formula gets the format of the referenced cells. (e) Type the text in A6. (f) Enter the value 1.234 in A7 and format it to one decimal place. (g) In B7 enter the formula =A7. The value 1.2 is displayed. Cell B7 has taken on the format of A7 because the formula is a simple reference to a formatted cell. We may format the cell to restore the value of 1.234 if that is required. (h) In C7 enter =A7+2. Again the value is displayed with one decimal place. However, when =A7*2 is entered in D4 we get 2.468 -this cell does not take on the format of A7. If you use a formula such as =B7+C7 in D7, the result will be displayed with one decimal place since that is how B7 and C7 are now formatted. (i) Save the workbook. Exercise 5: Formats In this exercise we see that with the Copy and Paste commands both the values and the formats are copied. However, Excel 2002 provides a way to avoid copying formats. The worksheet will resemble Figure 2.7 when the exercise is complete. Get Copied 26 A Guide to Microsoft Excel 2002 for Scientists and Engineers (a) On Sheet3 of CHAP2.XLS, type the text shown in Al:A5. Type 12.555 in B3 and copy this to C3:E3 by dragging the fill handle to the right. U v L L L I I I U I I Y a v1 $ * X U LV"l.7 "11 I Standard toolbar. But there are t7 more ways; you may find one them more convenient Paste action we could use the menu Pnmm-mAo nv the tnnla nn the XO of Keyboard shortc [Ctrll+C and for Paste use [Ctrl/ (b) Format cells in B3:E3 to display the values as in Figure 2.7. (c) Select B3:E3 and click the Copy tool. Make B5 the active cell and click the Paste tool. Note that the values in the destination cells are displayed with the same formatting as the source cells. The Copy operation places material on the Clipboard. To warn you that this has occurred, an animated border (the 'ant track') is placed around the copied material. While the ant track is present you can paste the material on the same worksheet; on another worksheet in the same or another workbook, or in any open Windows document. When you perform any other action in Excel, the material is removed for the Clipboard and the ant track goes way. This is a safety precaution not used in other applications; numeric material erroneously pasted into a worksheet might not be spotted and could lead to incorrect business decisions. (d) Excel 2002 has a new feature associated with the Paste operation: a Paste option smart tag appears. It resembles the Paste icon on the toolbar but when the mouse hovers near it a down arrow is added to the icon. Click the icon to open the smart tag. It offers options to use the formatting from the New Excel 2002 feature Basic Operations 27 source or to match the formatting of the destination. Use Help to learn about the other options when you know more about Excel. Save the workbook. Exercise 6: Too Many In this exercise we discover what to do when the value in a cell has too many digits to display. There are a variety of ways to change the column width to accommodate the value. Digits (a) When we opened the worksheet that was to become CHAP2.XLS it very likely had three worksheets since this is the default setting. We need a new worksheet for this exercise. Use the command InsertlWorksheet to make a new one. Now look at the sheet tabs and locate the tab for Sheet4. It was placed to the left of whichever was the current sheet when you use the insert command. Clearly it is in the wrong place. Click on the Sheet4 tab and drag it to the right of the Sheet3 tab to locate it correctly. (b) Enter the value 123.456 in Al. (c) Using the Increase Decimal icon, increase the number of decimal digits. After a few clicks the value has filled the cell and Microsoft Excel automatically widens the cell to keep pace with the number of characters displayed. There is, however, a limit; a cell cannot hold more that 256 characters. Furthermore, do not be misled by all those zeros! Microsoft Excel stores numbers to a precision of 15 digits. Adjust the value to about 8 decimal places. (d) Place the cursor in the column headings and position it on the divider between the A and B headings. The cursor will change to a new shape - see Figure 2.8. Drag the column divider to the left making column A narrower. The cell now displays ########## indicating that the cell is not wide enough to display the value with its current format. Figure 2.9 (e) In the last step we changed the width of a column but we have 28 A Guide to Microsoft Excel 2002 for Scientists and Engineers Shortcut: You can also open the Format dialog box fi-om the popup menu that appears when you right click in a cell. Shortcut: To change a column width to accommodate the widest entry in that column, double click on the divider to the right of the column header. Exercise 7: Ca I cu lat i o n Example no idea what the actual width is. This time we will change the width to a specified size. With A1 as the active cell, use the command Fo-rmatlColumnlWidth. In the Column Width dialog box (see Figure 2.9) enter the value 8.43. This is the default column width with Aria1 font of size 10 or 11 points and is large enough to display eight digits. (f) The cell may still display #######. Use Format(Cgl1t o display the value with 3 decimal places. It should now fit the column. (g) In A2 enter the value 12345678912. This time Excel does not expand the column to accommodate all the digits because the column has been given a fixed width. Rather, Excel displays the value in scientific format as 1.23E+10 which is to be interpreted as 1.23 x 10". Microsoft Excel behaves differently with text entries. If you type a text entry with more characters than the cell can hold, the entry will overflow into the cells to the right provided they are empty. (h) Type Sample heading, in B 1. Both words are readable but much of the second overflows into column C. Now type Another heading in C1. Most of the characters of the second word in B 1 are now lost. B 1 is not wide enough to display its contents and text overflow is not permitted now that C1 is occupied. (i) We now experiment with another way of widening a column. With B1 and C1 selected, use the command FormatlColumnl -Au toFit Selection. The two columns are made exactly wide enough for B 1 and C 1 to hold their contents. The AutoFit command may be used with any type of data, numeric or textual. If we select the column headings rather than a range, the AutoFit command makes the columns the correct width for the cell in each column with the greatest need for space. (i) Save the workbook. Once a worksheet has been set up to solve a problem, it may be used repeatedly for the same type of problem but with different input values. For example, if you had one quadratic equation to solve, it might not be worth the effort to design a worksheet to do it. If you had a dozen or so equations to solve, then a worksheet Basic Operations 29 solution would be more efficient than using a pocket calculator. Other advantages of the worksheet are (i) the ability to see what values you have used and (ii) the facility to modify the calculation without re-entering all the data. In this exercise we will design a worksheet to compute the effective resistance of four resistors in parallel. The four resistors (RI, R2, R3 and R4) in Figure 2.10 have the equivalent resistance value of the single resistor (Re) whose value is determined by the relationship shown in the figure. 3 Resistors 1IR (a) Using the method in the last exercise, insert Sheet5 in the correct position on the workbook CHAP2.XLS. (b) Enter only the text and values shown in A1 :A1 0 and B 1 :B3 of Figure 2.10. (c) In B4 enter the formula =1/A4. Copy this formula to B5:B7 by either dragging the fill handle of €34 or double clicking on B4's handle of I34 causes the formula t fill handle. be copied down the table. (d) The formula in B9 is =B4+B5+B6+B7, giving the value 1/Re. Remember you may add spaces around the addition operators if you wish. Later we shall use a function to evaluate a summation like this. (e) The formula in B10 is =1/B9 to give the value of Re. (f) Test your worksheet with the values 2,2,4,4. Since 1/Re will be % + 'h + % + % or 1%, your worksheet should give Re as 0.666667. Whenever possible, check a new worksheet with a 30 A Guide to Microsoft Excel 2002 for Scientists and Engineers Exercise 8: Entering Formulas by Pointing few manual (or mental) calculations. Save the workbook. While our worksheet is able to compute the equivalent resistance of any four resistors in parallel, it cannot be used for fewer. If we enter 0 in A7 (for example), Excel will return the error value #DIV/O! in B7. The same error value will be displayed for all formulas that use B7. Incorporating an IF function (see Chapter 5 ) in the formulas used in B4:B7 would make the worksheet much more versatile. What we did in A4:B 10 is similar to how we would manually solve this problem with paper, pencil and calculator, writing down every intermediate result rather than using the calculator’s memory. Wherever there is repetition (e.g. calculating the reciprocals in this example), the worksheet method simply requires us to copy formulas. These two points may help you design your own worksheets. In this exercise we look at an alternative method to typing cell references when building a formula. When you type a cell reference you must take care to use the correct address. With larger, more complex worksheets, it is easy to make a mistake. The alternative method is to point to the cell with the mouse. It is akin to saying ‘use that one’. For the problem in Exercise 7, clearly we could combine steps (d) and (e) and compute the value of the effective resistence in one formula: = 1/( B4+B5+B6+B7). The parentheses are essential. (a) In A12 enter the text Re. (b) In B 12, begin the formula by typing =1 I(. Now left click on cell B4 and observe the result in the formula bar - the formula is now =Bl4/. (Ty pe the plus sign and click on B5. Continue until the formula reads =1/(84+85+B6+87 and click on the green check mark in the formula bar. (c) If you have entered everything correctly, Microsoft Excel politely points out that there is a small error and offers to correct it by adding the closing parenthesis. Click the Yes button of the dialog box. (d) Double click on B12 and note the status bar now reads Edit rather than Ready. But the more obvious change is the Range Basic Operations 31 Finder feature which causes the cells and ranges to which the formula refers to be displayed in colours, and matching colour borders to be applied to the cells and ranges referenced in the formula. This provides a convenient graphical way for us to check formulas. Save the workbook. Exercise 9: In Exercise 2 we saw that Excel normally treats cell references as relative references when a formula is copied. There are times when References: Relative’ this is not what we need. If the formula refers to the cell A1 we Absolute and Mixed may modify the reference by adding one or more $ symbols. Reference Result when formula is copied =A1 the row and the column may change =A$1 the row remains constant, the column may change =$A1 the column remains constant, the row may change =$A$l both the row and the column remain constant A cell reference in the form A1 is called a relative reference while $A$1 is called an absolute reference. The forms $A1 and A$1 are mixed references. Remember that when a formula is copied to the same row, the row reference is unchanged without the need for the $ symbol. Similarly, when a formula is copied to the same column, the column reference is unchanged without the $ symbol. To demonstrate the use of mixed references, we will develop a simple worksheet that displays a multiplication table as shown in Figure 2.1 1. Shortcut: To enter a series of I A I B l C l D I E l F I G 1 H I I I J 11 I 21 31 41 51 61 71 SI 91 10 Figure 2.11 (a) Insert Sheet6 in the workbook CHAP2.XLS. Start by entering the values 2 and 3 in B 1 and C 1. Use the Series Fill method to complete the row. Enter the data in column A in a similar manner. 32 A Guide to Microsoft Excel 2002 for Scientists and Engineers Exercise I O : Editing and Formatting Note: We could, of course, use the equivalent formula: =( B4 * B8)/(A9 - E4)- D4/(A9/'2). (b) In B2 we need a formula to compute A2 x B 1. If we use =A2*B1 we will not be able to copy it. What we need is =$A2*B$l. The $ before the A in the first term ensures that, when the formula is copied across the worksheet, the reference will always be to that column. Similarly, the $ in the second term keeps the reference to row 1 constant when the formula is copied down the worksheet. The $ signs may be typed as you enter the formula but we shall use another technique. Type =A2 to start the formula. Now press [F4) repeatedly until the formula reads =$A2. Next add *B1 to the formula and again use IF4] to make the formula read =$A2*B$l. (c) Copy B2 to B2:JlO. (d) Examine the values and the formulas in a few cells to make sure you understand the process. (e) Save the workbook. In this exercise we construct a table to display the pressure of a gas at various temperatures and volumes using the van der Waals equation: p=---R T a V-b V2 We would like to be able to change the values of a and b so that our table may be used with different gases, so we will place these values in their own cells rather than in the formulas. We will also place the value of the gas constant R in a cell to provide documentation - and to allow us to change it quickly if we use the wrong value. The final worksheet will resemble that in Figure 2.12. Do not worry about the negative pressure value - the gas has condensed under these conditions and the equation is not really applicable. (a) Open the workbook CHAP2.XLS and move to Sheet7. Type the text shown in rows 1 to 7 of Figure 2.12. In C4 type C02. Enter the values in B4:E4. We will format the cells (centring, subscript, etc.) later. Basic Operations 33 (b) Enter the values in B8:H8 and in A9:A method. 8 using the Series Fill (c) In B9 enter the formula =(B4*B8)/(A9 E4)-D4/(A9*A9). The pointing method from Exercise 8 would be appropriate here. Click the check mark in the formula bar when the formula is complete. The cell should show the value 1374.21. Correct the formula if needed. Examine the formula making sure you understand how it computes the pressure for V= 0.05 litres and T= 250 K. Figure 2.12 We need to modify the formula in B9 before copying it to the range B9:H18. There are three considerations: (i) In the formula, B4 refers to the value of the gas constant, D4 to the a constant and E4 to the b constant. These references must not change when the formula is copied; (ii) On the other hand B8 refers to the temperature and we have a range of these in row 8. When the B9 formula is copied, the reference to B8 must still refer to row 8 but the column must change; we need to replace B8 by B$8; and (iii) The references to A9 must continue to point to the volume values in column A but as the formula is copied the row must change. So we need to use $A9. In summary, we need to edit the formula to read =($B$4*B$8)/(A$9 - $E$4) - $D$4/($A9*$A9). We shall not retype the formula, rather we shall edit the existing one. We shall do this in a series of steps to illustrate the various options that are available. 34 A Guide to Microsoft Excel 2002 for Scientists and Engineers HMerge and Center tool - Center Align tool (d) Move to cell B9 and enter editing mode by either double clicking or by pressing [. (e) We have a variety of options for editing the formula. In cell B9, move the mouse pointer in front of the reference to B4 and click to position the insertion point. Type the $ symbol. Move the insertion point in front of the $ in B4 and type another $ symbol. Now move the insertion point between the B and the 8 in B8 and insert a $ symbol. (f) Next move the insertion point anywhere within the first reference to A9 -you may have the insertion point in front of the A, between the A and the 9, or just after the 9. Now press [F41 repeatedly and watch the reference cycle through the values $A$4, A$4, $A4 and A4. Stop when you have the required value of $A9. Use the same technique to change E4 to $E$4. Click the green arrow in the formula bar to complete the entry and return to ready mode. (g) We have not completed the editing so we will re-enter the edit mode and explore another way of editing. This time we will activate the edit mode by pressing m. It has the same effect as double clicking but some users find it more convenient. For variety this time, rather than doing the editing in the cell we will do it in the formula bar. We need to replace D4 by $D$4 and A9*A9 by A$9*A$9. Use whichever method you prefer: typing the $ or using m. Click on the green arrow in the formula bar when you have completed the task. (h) B9 should now read: =($B$4*B$8)/($A9- $E$4)- $D$4 /($A9*$A9). Copy it to row 18 and column H. Check that your values agree with those in the table. If they do not, you may need to edit B9 and recopy it. (i) Now some formatting to improve the appearance of some cells. (i) Format the range B9:H18 to display two decimal places. (ii) Select AI:Hl and centre the van der Waals ... text over these cells using the Merge and Center button. Use the second item in the Formatting toolbar to increase the size of the font to 14. Click the Bold button on the same tool bar. (iii) Select B3:E4 and centre the entries with the Center button on the Formatting toolbar. (iv) Using the technique in (i), centre the text in A6 over columns A:H and the text in B7 over columns B:H. Basic Operations 35 (v) In C4 we have C02 but wish to have CO, with the ‘2’ as a subscript. Select C4 and start the edit mode. Highlight the 2 and, from the menu bar, select Foymat(Cel1.s. Using the Effect portion in the resulting dialog box (lower left), click on the Subscript box to place a J in it. Click the OK button. (j) We will do more with this worksheet later. Giving the worksheet a name other than Sheet7 will help us locate it. Right click the sheet’s tab, select Rename from the menu and type the name VanderWaals. Save the workbook CHAP2.XLS. Exercise 1 1 : What’s In the exercises so far, we have constructed formulas that use other cells’ values by using cell references. It is possible to give a cell, or a range of cells, a name. The advantages of this are twofold:(i) it is easier to remember where a value is stored if the cell has a name and (ii) names are always treated as absolute references. in a Name? In this exercise we use names with one letter. This is not a requirement, we may use a name such as Gasconstant. Note that names are not case sensitive so GASCONSTANT and Gasconstant are treated as the same name. When single letters are used, R and C are invalid since Excel uses these letters for other purposes. Similarly, we may not use a name which could be a cell reference. However, we may add an underscore to create names such as R-, c-, X I , etc. (a) Open CHAP2.XLS and move to the VanderWaals worksheet. Delete the range B9:H 18. (b) With the range B3:E4 selected use the command InsertlBamelCreate. A dialog box similar to Figure 2.13 appears. Excel has detected text in the top row and values in the lower one, so it correctly assumes that you wish to apply the names in the top row to the corresponding cells in the lower row. Click OK. (c) Move to I1 and use InsertlNamelPaste. In the resulting dialog box (Figure 2.1 1) click Paste List. This lets us check what names have been assigned to what cells. Note that B4 got the name R- not R. Delete the list or press the Undo button. (d) In cell B9 type the formula =(R-*B$8)/($A9 - b) - a/( $A9 * $A9). Check its value and copy it to B9:HlS. 36 A Guide to Microsoft Excel 2002 for Scientists and Engineers (e) Save the worksheet. A ' R c.- ' D E I F G ' van der Waals Equation of State R Gas a b 008206 C02 359 00427. I i I Figure 2.13 We used a semi-automatic method to create names for some cells. We may also select a single cell and use ZnsertlEamelDefine to name a specific cell. There are a number of ways of referencing a named cell (or range) in a formula. To enter, for example, R- in a formula we may (i) type the name R-, (ii) point to the corresponding cell with the mouse, and (iii) use the command lnsertlN_amelPastea, nd select the required name and click the OK button or simply double click on the required name. We began this exercise by deleting the range B9:HlS. We did this to show how to build a formula with names. Alternatively, after we had created the cell names, we could have used the command InsertlN_amelApply to replace cell references by cell names. A named cell or range may be referenced in any sheet of the same workbook. It is possible to use the same name for two cells (or ranges) in different worksheets of the same workbook. Suppose a cell in Sheet1 is named Mass, and a cell in Sheet2 has the same name. A reference to Mass in Sheet 1 will automatically refer to the Basic Operations 37 cell in that worksheet. If, in Sheet2, you need to reference the Mass cell of the other sheet, you would use SheetI!Muss. If we move to Sheet3, where no cells are named, what would a reference to Mass mean? Generally, it would refer to the cell in Sheet 1 since this was first named. However, it would be safer to qual@ the name using either Sheetl!Mass or Sheet2!Mass as required. Exercise 12: Custom In Exercise 3 we discovered how to format numbers. We may, for example, arrange to have three decimal places displayed, or to use scientific notation. Excel also permits the user to develop custom formats. To demonstrate this we will format numbers so that the exponent is always a multiple of three - the engineering notation. Formats Figure 2.14 (a) Open CHAP2.XLS and insert a new worksheet. You may need to drag the sheet’s tab to the right and correctly locate it. (b) Enter the text shown in A1 :B3 of Figure 2.14. Enter the values shown in A3:A9. When you enter the large number in A9, Excel will display it in scientific notation; change this to the number format with two decimal places. (c) In B3 enter =A3 and copy this to C3 by dragging the fill handle to the right. (d) Select B3 and format it to scientific notation with two decimal places. Copy B3 down to B9 by double clicking its fill handle. (e) Select C3 and open the Format dialog either by using the command FormatlC& or by right clicking the cell and selecting the Format Cells item in the popup menu. 38 A Guide to Microsoft Excel 2002 for Scientists and Engineers J Figure 2.15 (f) Open the Custom tab on the dialog - Figure 2.15. Place the cursor in the Type box and backspace out whatever is there already. Do NOT use the Delete button on the dialog box to do this. Type in the custom format in this form: ##O.OOE+O; -##O.OOE+O; 0. A custom format has three parts separated by semicolons. The first part specifies the format for positive values, the second for negative values, and the third for zero. By limiting the number of digits before the decimal to three we have forced Excel to use the engineering notation where the exponent is a multiple of three. Unfortunately, the result is not very pleasing for values less than 1000. We may display as many digits after the decimal as we wish. If you wish not to have zeros displayed use I' for the third part of the format. (8) Double click on the fill handle of C3 to copy the formula down to C9. Save the workbook. Basic Operations 39 Exercise 13 and Such ,: Symbols We may need to use symbols and Greek letters in table headings so we need to know how to get such text as Temp OC and AV=x?Ah into cells. The reader is encouraged to investigate these ways of obtaining such results. (a) Certain symbols are readily generated by holding down the key and entering a four-digit code on the number pad (it must be the number pad, not the digits on the top row of the 'typewriter' keys). The first two rows of Figure 2.16 show some useful symbols. Thus (Anl+O I 77 gives f, the plus-minus symbol. This table is most easily made by entering the numbers in the second row, typing the formula =CHAR(A2) in A3 and copying it across the row. So we may have a cell display "C either by typing mAt0186 followed by C, or with the formula =CHAR(186)&"C". The former is, of course, more convenient. Note that we can use this method to get superscripts in text (as, for example, R2 and m3) but we can also get these by formatting a normal digit as superscript - see step (i) of Exercise 10. ~~~ Figure 2.16 (b) Greek letters are obtained with the Symbol font. Suppose you want a cell to display AV. Begin by entering the two letters DV. Now either select the D in the formula bar, or double click the cell to enter edit mode and select the D in the cell. Next change the font of the selected letter. This may be done with the command Fgmat(Cg1ls and opening the Font tab. But it is more convenient to use the Font box on the Formatting toolbar. Remember that there is no need to scroll down the list, typing 40 A Guide to Microsoft Excel 2002 for Scientists and Engineers S will jump to the first font beginning with this letter. The formula bar does not display the formatted value. When you select a cell having some or all the characters formatted in Symbol font, you will see Roman (regular) characters in the formula bar generally in Aria1 font. New Excel 2002 feature (c) If you are familiar with Word you will know that one can use InsertlSymbol to get symbols and Greek characters into a document. Until the latest version, Excel did not support this feature. However, one could use the Windows procedure (StartlProgramsIAccessories)CharacterM ap) to copy symbols to the clipboard for pasting into a cell. With Excel 2002 one has access to the InsertlSymbol dialog. Indeed, it is a much improved feature - see Figure 2.17. Exercise 14: Fractions Figure 2.17 From Figures 2.16 and 2.17 we can see that it is possible to enter fraction symbols into cells. It is important to realize that fractions entered in either of these ways are just symbols. They are not numerical values and cannot be used in mathematical operations. Functional fractions are possible. A cell can display, for example, 2 1/4 but its stored value is, of course, 2.25 -the display results from formatting. Basic Operations 41 3 4 If you mistakenly type 1/8 in A4, 3.5 2 7 Total 28 Excel Will assume you want (I August or 8 January dep on your regional settings). the entry and use EditlClearlEo to remove the date formatting. Natural Language Formulas The so-called natural formula feature is mentioned the reader finds a re elsewhere. Your authorrespectfbl advises you not to use this feature The option Accept Iahels in that was active and to all new created when the option is in e (a) In Figure 2.18 the entry in A3 was made by typing 2 114 - note the space between the integer and the fraction. The entry in A4 was made by typing 0 118 - Excel does not display the leading zero. Select each cell in turn and observe the values displayed in the formula bar; they will be decimal values since that is how Excel stores the numbers. 2 1/2 1/16 Figure 2.18 (b) The formulas in B3 and B4 are =A3*2 and =A3/2, respectively. These are copied down to row 4. It is most likely that C4 will initially display 0. Open the Format dialog and select the Fractions category to adjust this. One of the most hailed new features of Excel 97 was called natural language formulas. This allows the use of column or row labels in formulas without creating names. For example, with the worksheet shown in Figure 2.19, the natural language feature allows you, without going through the process of creating names, to use formulas such as =Density*Mass in C2:C3 and =SUM(Mass) in C4. s Density Volume Figure 2.19 This feature proved to be a mixed blessing and, while it was retained in later Excel versions, it is switched off when you first install the product. It is controlled by the box labelled Accept labels in formulas in the Calculations tab (ToolslQptions). The reader is encouraged to leave it switched off and to avoid using this feature since it can easily lead to errors. 42 A Guide to Microsoft Excel 2002 for Scientists and Engineers Problems 1 .* On a new worksheet enter these numbers: 1, 2, 3 and 4 in A1:DI. These represent the values of w, x, y and z, respectively. Do not name the cells for this problem. In row 2 enter formulas to compute the following. Check the results. (b) x2 + w (a) 2w-y w+x ( 4 - Y-= 2.* Construct a formula to find: (a) the square root of the value in D1, without using the SQRT function which we look at in the next chapter, (b) the cube root of D2, and (c) the reciprocal of D3. 3. Use the pointing method on the worksheet used for Exercise 7 to build these formulas: =II/A/4(+ 1/A5+1/A6+1/A7) and Do you get the same, correct result? =(l/A4+1/A5+1/A6+1/A7)"-1 4. Some of the formatting commands are available on the popup menu that appears when you right click on a cell or a column (or row) heading. Experiment! 5. If P dollars/pounds are invested in a savings account with an interest rate of R per year, compounded A4 times a year, then at the end of N conversion periods the accumulated amount is given by A, = P(I + WwNC. onstruct a table showing the accumulated amount for annual interest rates of 5, 6, 7, 8, 9 and 10% with interest compounding monthly, quarterly and semi-annually. Namedcells and formulas with mixed absoluterelative references will work well here. 6. The data validation feature (accessed using PataIValidation from the menu) permits you, for example, to require only integers in the range 1 to 10 in a specified cell. Insert a new worksheet in the CHAP2.XLS workbook and experiment with this feature. Printing a Worksheet Concepts Sooner or later you will wish to print your Microsoft Excel worksheet so we will examine this topic now although we have hardly scratched the surface of Excel. In this chapter we will see how to print all the used area or a selected part of a worksheet. We will also explore various options such as changing the header and footers on a printed page, removing the gridlines, having the column and row headings in the printout and making the selected print area fit one page of paper. Exercise 1 : A Quick (a) To begin this exercise we will open the file which was saved in the previous chapter. Open the File menu and look at the bottom. Excel saves the names of the user's last four workbooks. Click on CHAP2.XLS to open your workbook. If you are working on a network, or if you have worked on other files since doing the exercises in Chapter 1, the name of this file may not be present. Use the menu item FilelQpen to locate the appropriate folder and select CHAP2.XLS. You may wish to type your name in an empty cell such as D4 if you are using a network environment. Way to Print H T h e Print tool (b) Click on the first sheet tab to select the worksheet where you made the temperature conversion table. (c) Click on'the Print button on the Standard toolbar and retrieve your printout. Note that the printed area is AI :D14 since this is the area containing data. (d) Inspect your worksheet screen. There will be a vertical and a horizontal dotted line. The exact positions depend on the margin setting (we look at these later). Generally, the vertical line runs between columns I and J and the horizontal one between rows 5 1 and 52. These lines show you what data will fit on a single page in the printout. 44 A Guide to Microsoft Excel 2002 for Scientists and Engineers Exercise 2: Another This exercise uses the menu to print a worksheet. To demonstrate how this is more versatile than the Print button, we will print just a part of the worksheet. Way[/english] ------------------------------------------------------------------------------------------------ لو سمحتم أخوتي الأعزاء اشرحوا طريقة وضع رابط تحميل ضمن الموضوع Upload لأن تحميل ملف الكتاب أفضل أكيد من هذه الطريقة . -----------------------------------------------------------------------------------------------

---

الكاتب:	عبد الرحمن [ الثلاثاء مارس 25, 2008 7:00 pm ]
عنوان المشاركة:	كتاب لتعليم برنامج Exel
daherstar, شكراً الك وعلى الأهتمام بس وين الرابط وكمان كأنوا ها ناسخ الرابط من عندك من مكان مانك حطط الرنامج شكراً على كل حال

---

الكاتب:	Obada Arwany [ الجمعة مارس 28, 2008 8:15 pm ]
عنوان المشاركة:	كتاب لتعليم برنامج Exel
شكرا اخي على الموضوع اذا بدك تحط الكتاب كرابط افتح شي موقع upload و حمل الملف عليه ... و عطينا الرابط في كتير مواقع للرفع منن http://www.tntup.com" onclick="window.open(this.href);return false; و غيرهم كتير وشكرا الك على الموضوع و يا ريت تحطلنا الكتاب كامل وشكرا معلم

---

الكاتب:	بثينة [ الأحد مارس 30, 2008 3:13 pm ]
عنوان المشاركة:	كتاب لتعليم برنامج Exel
شكرا كتير عالموضوع و هي رابط تاني لموقع لرفع الملفات : http://www.fileflyer.com/" onclick="window.open(this.href);return false; هاد الموقع أنا مجربتو كتير من قبل لأنو كتير سهل

---

صفحة 1 من 1	جميع الأوقات تستخدم التوقيت العالمي+03:00
Powered by phpBB® Forum Software © phpBB Group http://www.phpbb.com/