Saving and retrieving workbooks
Yüklə 99,17 Kb.
|
- Bu səhifədəki naviqasiya:
- Formula bar Active cell
- Title bar Shows the name of the current workbook Menu bar
- Formula bar This is where you enter data and formulas. Active cell
- This illustrates how you can fill cells with a constant value, or with an incrementing sequence. Notice from column C that the increment does not have to be one.
- Saving and retrieving workbooks
- Saving the current workbook
- Opening an existing workbook
- Saving worksheets in other file formats
- Opening worksheets in other file formats
- Entering a formula in a cell
- Copying a formula to other cells
- Enter the formula =( A1+B1)*$D$1 in cell C1 .
- Other summary functions
- Using the format painter
- Hiding rows and columns
- Changing column widths and row heights
- Defining the Page Setup
- Printing just a part of the worksheet
MA310: COMPUTINGMICROSOFT EXCEL BASICS IntroductionWhat is Microsoft Excel?Excel is a spreadsheet program. You can use it to enter data, do calculations, draw graphs, produce tables and undertake many other types of analysis. It is compatible with other Microsoft programs, such as Word and more and more non-Microsoft programs, such as Minitab, are providing links to Excel. Most people find that Excel is quite easy to use, once you’ve mastered the basics. In Excel, an individual spreadsheet is called a worksheet. It consists of a rectangular array of cells, with 256 columns and 65536 rows – big enough to cope with most data sets. Excel stores worksheets in a workbook. A single workbook can contain up to 255 worksheets. An Excel file, with file extension .xls, stores a single workbook. How to use these notesThe easiest way to learn Excel is to try it out - read through these notes while sitting at a computer. You need to be familiar with the following mouse operations:
To indicate menu choices, we use bold arial font. Most things that can be done in Excel can be done in several different ways. Here, to keep things simple, we will usually show you just one way. Other sources of helpExcel has a lot of on-line help available. This is very useful, particularly for reminding yourself of the details of how things are done. When you start Excel, the screen may well include the Office Assistant, a small animated paperclip that responds to questions you type in ordinary English. If the assistant is not displayed, you can summon it up by clicking on the Help menu and selecting the top option, Microsoft Excel Help. The second option enables you to turn the assistant on or off. Many people find the assistant rather irritating, but it can be quite helpful at times. Help can also be obtained on some procedures by clicking on the ? button. Sometimes this will lead to a “Show me” option. There are huge numbers of books about Excel (though not many in the University of Kent library) and many tutorials on the web, see for example http://www.usd.edu/trio/tut/excel/ or www.fgcu.edu/support/office2000/excel. However, these notes cover most things that you are likely to need to know whilst at the University of Kent. Starting ExcelClick the Start button at the bottom left of the screen, and click on the Excel icon if it is showing. Otherwise select All Programs and then click on Microsoft Office Excel 2003. You can quit Excel by clicking the × button at the top right of the Excel window. However, you will usually want to save your work first and this is discussed later under Saving and Retrieving Workbooks. The screen layout W Title bar Formula bar Active cell Menu bar   Standard toolbarFormatting toolbar   hen you start up Excel, the screen will look similar to this:
The main components that we shall refer to in these notes are:
Note: If the tool-bar on the screen appears as one row you can change it by clicking in turn on View, Toolbars, Customize and deselecting the box “Standard and Formatting toolbars share one row”. Entering data into Excel Before you startIt is always a good idea to think about how best to organise your data before you start entering it. This is particularly important if you will need to export your data from Excel to another package. Many data sets can be arranged as a simple rectangle, called a data matrix, with the columns storing different variables (e.g. age, height, weight) and each row storing the data for one individual. Types of dataEach cell in a worksheet can store a single data value. This might be a number, like 4, 17.2 or 2.9E-4, or a piece of text, like Female, Sample 17 or Mary Poppins. If you want a number to be considered as a text value rather than as a number, precede it by an apostrophe, e.g. ‘2000. Excel can also deal with dates and times, but these are not discussed here; consult the on-line help if you need to know about this. If an entry is too wide to be displayed in the column only part of it will be displayed. See the section on Formatting a Worksheet for details about changing column widths. Headings If you are entering different variables in different columns, it is usually sensible to give information about the variables in the first few rows, for example:
Entering a data valueFollow these steps:
Entering systematic data Quite often, your data will have some systematic features – for example, there might be a variable called SampleNumber that increases by one each time you move down a row. Excel has a useful feature that can speed up data entry in this situation, and which is illustrated by the following example. Enter the following values into the top left-hand corner of a blank worksheet:
Use the mouse to highlight the block of 6 cells. Notice that there is a small square (called the fill handle) in the bottom right hand corner. When you move the mouse over the fill handle, the cursor changes to a small ‘+’ sign. When this is showing, drag the mouse down a few rows, and Excel will automatically fill these with values, as follows:
This illustrates how you can fill cells with a constant value, or with an incrementing sequence. Notice from column C that the increment does not have to be one.Missing valuesReal data sets often contain missing values, and most statistical packages can cope sensibly with missing data. Excel isn’t completely satisfactory in this respect. You can leave missing values blank. But if you do this you need to remember that blank values will be treated as zeros in calculations. Alternatively, you can enter the special missing value indicator #N/A into a cell. However, any calculations that involve this cell will generate #N/A as the answer. Correcting errorsIf you realise halfway through entering a value that you are entering it in the wrong cell, press the Esc key to quit. If you’ve already finished the entry, you can usually still undo it by clicking on the Edit menu and selecting Undo Typing. You can delete the contents of a cell with the Delete key. You can delete the contents of a block of cells by highlighting the block and then pressing Delete. To edit the contents of a cell, just click on the cell and edit the cell contents that are displayed in the formula bar. Saving and retrieving workbooksIt is important to save your workbook at frequent intervals, to avoid losing work if the computer crashes or if you accidentally and irretrievably mess up your worksheet. File handling in Excel works in the same way as in other Microsoft programs (and quite a few non-Microsoft programs, such as MAPLE). Saving the current workbookClick on the File menu and choose either Save or Save As.
The  icon on the Standard toolbar is a shortcut for File | Save.
Excel workbooks have file extension .xls. Do not include the file extension in the filename – Excel will add this automatically. Opening an existing workbookClick on the File menu and choose Open. This starts up the Open dialog box. Locate the workbook you want and then click the Open button near the top right of the dialog box. If you’ve been working on this file recently, there may be a quicker way. When you click on the File menu, look down the bottom (above Exit ) where there will be a list of the most recently saved files. If you see your file there, just click on it. The Closing a workbookClick on the File menu and choose Close. If you haven’t already saved your workbook (or if it has changed since you last saved it), Excel will prompt you to save it now. If you want to close down Excel altogether, click the × button at the upper right-hand corner of the Excel window (or click File|Exit). This will also prompt you to save any unsaved workbooks. Saving worksheets in other file formatsSometimes you may need to move data from Excel to other applications. Some applications (e.g. Minitab, SPSS) will allow you to import an Excel worksheet directly. Otherwise you need to use the Save as type feature at the bottom of the Save As dialog box to choose an appropriate format to save your data in. For some applications (e.g. some versions of the dBASE database package) there may be a built-in format. Otherwise you should select the Text (MS-Dos) option. This will save your data in a plain text format that can be read into most other applications. You can only save a single worksheet in this way, not a whole workbook. Opening worksheets in other file formatsYou can import data that are stored in other file formats into Excel. The simplest example is a plain text (ASCII) file, with one or more columns of data. In the Open file dialog, set the drop-down Files of type box to Text Files (or All Files if the file extension is something other than .txt, e.g. .dat). Then select the appropriate file and click the Open button. This will start up the Text Import Wizard, which you use to tell Excel how the data are organised in the file. Starting a new workbookClick on the File menu and choose New. The new workbook will be displayed. If you have more than one workbook open at once, you can switch between them by clicking on the Window menu and selecting the workbook you want from the list displayed at the bottom. The Working with formulas Perhaps the single most important feature of a spreadsheet program is that, as well as storing numbers and text, cells can store formulas that refer to data stored in other cells. When any of the cells used in a formula is updated, the cell containing the result of the calculation is updated automatically. As well as basic arithmetic operations, Excel provides a wide range of specialised functions that enable quite sophisticated calculations to be undertaken.Entering a formula in a cellClick on the cell where you want the result of the formula to be displayed and type in the formula, preceded by an = sign. When you are new to Excel, there is a tendency to forget the = sign. If you do this, the formula you type is treated as a piece of text rather than a formula. Simple arithmeticYou can write arithmetic operations using +, – , *, / and ^. The usual rules of precedence apply, as on a calculator. Use brackets to alter the order of evaluation. You can also use spaces within expressions to improve readability; Excel ignores spaces. Examples of valid formulae are: =A1+B1+C1 (no spaces) = (a1+a2) ^ C1 (upper or lower case letters can be used for cell addresses). When you’ve finished entering the formula, press the Enter key. Note that Excel now displays the result of applying the formula, rather than the formula itself. However, the formula is still stored internally and can be modified subsequently. Editing a formulaClick on the cell that stores the formula. The formula is displayed in the formula bar. If you now click on the formula bar, the formula is colour-coded, to help you distinguish the components. You can edit the formula directly in the formula bar. Copying a formula to other cellsSuppose you have data in columns A and B and you want to add together corresponding A and B values, storing the results in column C. You do this in two steps:
The effect of this will be to set C2=A2+B2, C3=A3+B3, etc. This happens because the cell references in the original formula, A1 and B1 are relative cell references. If you copy a formula from one cell to another, any relative cell references in the formula are incremented in the same way. For example, if you copy a formula from B1 to E3, relative cell references are increased by 3 columns and 2 rows. Sometimes you don’t want this sort of automatic updating. For example, you might want to add together columns A and B, but multiply all entries by a fixed constant, stored in cell D1, say. To do this, you need to specify an absolute address for D1. You do this by preceding both the row and column label by a $ sign. So step 1 becomes
You can also use just one $ sign – e.g. if you use $A1 in a formula, the row will be incremented, but not the column. Showing formulae If you want to see what formulae have been entered in the cells of the spreadsheet, for example to check that formulae have been copied as intended, click on Tools and then Options. Select the View page and then select Formulas under Window options. Deselect Formulas to return to the results of calculations in the cells. Functions in ExcelExcel has a huge range of functions that you can include in formulas. To explore what is available, click on the  icon on the standard toolbar. You will want to look particularly in the Math & Trig, Statistical and Financial categories. You will be familiar with many of the functions already; others will become familiar later in your course.
Some functions have a single argument and others have two or more, separated by commas. For example =LN(C1) calculates the natural logarithm of C1. =COMBIN(4,2) calculates the number of ways of choosing 2 things from 4. Summary functionsSome functions calculate summary values from data stored in a range of a cells. Examples are SUM, MIN and MAX. To illustrate how to use these functions, suppose we want to calculate the sum of the data values in cells A1 to D4, and store the result in cell E1. This can be done as follows:
There is an alternative way of selecting a range like A1:D4 that is particularly useful if the range is too big to fit on the screen and you want to use the mouse for others things, for example to click the arrow buttons on the scroll bar. Instead of holding the mouse button down, click on A1 and press and hold down the Shift key. Then move the mouse to D4 and click again. You can select a complete row by clicking on its row number or a complete column by clicking on its column letter. You can also name a block of data. This is useful if you need to refer to the same block in several formulas, or just to make your formulas more readable. For example, to name cells A1:A25 as ‘Height’, highlight these cells, click on the Insert menu, choose Name and then Define… and type Height in the box provided, and then click on Add. You can then use expressions such as SUM(Height) in your formulas. You can name single cells as well as blocks of cells.
Beware of empty cells when using these functions – empty cells are treated as if they contained zero. The SUM and COUNT functions have useful variants, called SUMIF and COUNTIF. COUNTIF counts data values that satisfy some criterion. For example, COUNTIF(A1:A25, “>0”) counts how many of the (non-empty) cells in A1:A25 are greater than zero. Similarly, SUMIF(A1:A25, “>0”) sums the values in cells A1:A25 that are greater than zero.
calculates the total salary of all the female employees. Use the on-line help to find more details about this. Errors in formulasYou will sometimes make typing errors in formulas, for example not matching brackets properly. Excel will report this type of syntax error, sometimes suggesting a suitable correction (which will not always be what you intended!). Other errors arise when your formula has valid syntax, but produces an error when Excel tries to perform the calculation. Here are some errors and their possible causes:
Simulation Excel has an option for simulating values from standard statistical distributions, that is choosing values at random. This is under Tools/Data Analysis. Highlight Random Number Generation in the list of Analysis Tools. Clicking on OK leads to a dialogue box where the number of variables and the number of random numbers can be specified. Clicking on the arrow beside Distribution gives rise to a list including the uniform, normal, binomial and Poisson distributions. By specifying a random seed the same sequence can be reproduced again if desired. Note the option to generate patterned data, that is data that changes in a systematic way. Use of solver techniques The Solver option under Tools can be used to find values of variables maximising or minimising a function, known as the objective function, under constraints. If Solver is not showing in the version of Excel you are using, you need to go to Add-Ins (under Tools) first and check the Solver Add-in box. As an example of the procedure suppose that we wish to maximise the linear function 20 X1 + 6.8 X2 + 12 X3 - 65000 X4 subject to the constraints: X1 + X2 + X3 17000 X1 3000 X2 – 0.05X3 0 X4 0.2 X4 0.45 X1 , X2 , X3 , X4 0 Note that in the description of the procedure below it is assumed that the constraints are written with the variables on the left hand side and the constants on the right hand side - as above. In an Excel worksheet we need a column for each variable in the problem. It is sensible to label these and also to leave two or three columns for indicating what is entered in each row. In A2, say, type in maximise. Then in row 2 type in the coefficients of the four variables in the appropriate columns. So if column C is to be used for X1 then type 20 in C2. Similarly, if rows 4 to 8 are to be used for the constraints, type in the coefficients of the variables of the first five constraints in the appropriate columns (see lp.xls). Non-negativity constraints can be declared when using Solver to obtain a solution. As a last row enter 1 in each column corresponding to a variable. These are initial solutions, and will be replaced by the optimal solutions. Label one column LHS, leave the next column unlabelled, and label the column after that RHS. In the column RHS enter the values of the right-hand side of all except the non-negativity constraints in the appropriate rows, so in this example enter 17000 in I4, 3000 in I5, and so on. In the blank column enter or to show the form of the constraint. This is just to make it easier to check that the constraints are entered correctly when finding a solution. Next enter a formula in the column labelled LHS to calculate the function that we wish to maximise in the row corresponding to that function. For the linear function in the example under consideration the formula is entered in G2 and is =SUMPRODUCT(C$10:F$10,C2:F2). We can copy this formula to other rows of column G to find the values of the left-hand sides of the constraints. Notice that we need to use the values entered as initial solution values in each calculation. Warning: take care if adding or deleting rows or columns after entering these formulae as they will change. Next click on Tools and then Solver. In the dialogue box Solver Parameters enter the cell where the value of the objective function is calculated in the box Set Target Cell. In the box By Changing Cells enter the cells containing the initial solution values. In the Subject to the Constraints box click on the Add button. The references of cells in the LHS column are typed in the Cell Reference box, and the references of cells in the RHS column are typed in the Constraint box, taking care to choose the appropriate form of constraint from the central drop-down box. Click on Add after each new constraint and Cancel after entering details of the last constraint. Then click on Options in the main dialogue box. Make sure that Assume Linear Model and Assume Non-Negative boxes are checked and click on OK. Finally click on Solve. The optimal solutions should appear in the row where the initial solutions of 1 were entered, and the maximised value of the objective function in the cell where the formula for calculating the value of the objective function was entered (G2 in this example). This procedure can easily be adapted for other problems with different numbers of variables and constraints. Formatting a worksheet If you are using Excel to produce a report that will be read by someone else, it is worth expending some effort in formatting, to make it more readable. But bear in mind that with visual programs like Excel it is easy to waste a lot of time playing around with different formatting possibilities without really improving the end result.The best approach to formatting is to get your worksheet set up first, without worrying about formatting. Once the contents and general layout are correct, you can do the formatting. You can highlight blocks of cells and apply formatting operations to all the highlighted cells at once. Most formatting can be done using the icons on the formatting toolbar. Before you startWhen you are new to Excel, you can easily make mistakes when trying to alter the format of your worksheet. So it’s a good idea to save the workbook before you start. However, you can usually undo mistakes by clicking on the Edit menu and selecting the Undo option that is first on the list. You can use this repeatedly to go back several steps. Setting the fontYou can select a new font from the drop-down list in the Font box at the left-hand end of the formatting toolbar and use the drop-down list next to it (the Size box) to set the font size. The next three icons on the format toolbar,    , are used respectively to embolden, italicise or underline text. It is best not to use too many different font effects in a single document.
JustificationThe next set of icons on the toolbar is to do with justification within cells. The icons    are used respectively to left, centre or right justify the text within cells. The last icon in this group,  , is used to centre text across several cells, e.g. to produce a heading. To see how this works, type a piece of text in cell A1. Use the mouse to highlight cells A1:D1 and click the icon.
Formatting numbersThe next set of icons on the formatting toolbar is for formatting numbers. These affect the way the numbers are displayed, not how they are stored internally within Excel. Use The The The icons
Rows can be hidden similarly. Freezing panes This is useful if you want to keep row and/or column names visible when you scroll across or down a large data set. Either select the row below the row where you want the split to occur, or the column to the right of where you want the split, or both. Then click on Window followed by Freeze panes. To reverse the operation: Window and Unfreeze panes. Changing column widths and row heightsTo make a column wider or narrower, position the mouse on the right hand boundary of the cell containing the column’s letter label (for example, for column C position the cursor over the vertical line between C and D). The cursor will change to a vertical black line with arrows extending to left and right. Hold the mouse button down and move the mouse right or left to widen or narrow the column. Alternatively, double click the mouse in this position and the column will automatically adjust its width so that all contents can be displayed (if a column is too narrow to display the contents of some cells, these will appear as ###### ). Rows can be altered similarly, by positioning the cursor over the line below the row number. Sorting the worksheetClick on any cell in the block of data that you want to sort. Click on the Data menu and choose Sort…. This highlights all cells that are ‘connected to’ the active cell, in the sense that there are no blank cells in between, and brings up the Sort dialog box, which you use to specify which columns the sorting is to be based on. For example, if you opt to sort by Age and then by Surname, people will be sorted on the basis of their age and when several people have the same age, they will be sorted by their surname. If you just want to sort the whole worksheet based on values in a single column, you can just click on a cell in that column and use the sorting icons If you only want to sort a subset of the worksheet, highlight this before clicking on Data and Sort. You need to be sure that this is really what you want to do though, as you can easily scramble your worksheet irretrievably by doing this. If your worksheet contains formulas, you need to be careful that these still give correct results after sorting. You should be safe with formulas that only refer to cells in the same row (which is perhaps the commonest situation), but there are likely to be problems otherwise. Sort orderFor the purposes of sorting, numbers are treated as coming before letters. Upper and lower case letters are not distinguished, but you can change this by clicking the Options button in the Sort dialog box. Blank cells always sort to the bottom, regardless of whether ascending or descending order is chosen. Undoing a sortIf you want to undo the effects of a sort immediately, you can click on the Edit menu and select Undo Sort. If you want to sort data, work with the sorted data, and return to the original order at some later stage, you need to add an index to your worksheet, if you don’t already have one. An index is simply a column that that takes values 1, 2, 3, … through successive rows of data. You must insert the index column adjacent to your data. You can then return to the original order at any stage by sorting on the index column. Printing a worksheetPrinting a worksheet is very simple – just click the File menu and select Print… or click the  icon on the Standard Toolbar. However, it’s worth going into a little more detail.
Defining the Page SetupClick on the File menu and select Page Setup…. This starts up the Page Setup dialog box, where you can control many features of the print. The Page tab lets you set the orientation of the page (portrait or landscape) and the Margin tab allows you to alter the widths of the margins. The Header/Footer tab is useful for adding something to identify the print (particularly useful on communal printers). Click on the Custom Header… button and enter your text. You can also add standard things like the date by clicking the icons provided. You use the Sheet tab to specify whether things like Gridlines and Row and column headings are displayed. Using Print PreviewYou can save a lot of wasted printing by using Print Preview to see exactly what your print will look like. Print Preview is accessible from the File menu or by clicking on the  icon on the Standard Toolbar.
Fitting to the pageQuite often you will find that your worksheet will almost fit on a single page, but there is perhaps a single column that spills over to another page. Not only does this waste paper, but it makes your print very difficult to read. You can try using the Scaling options (Adjust to: and Fit to:) on the Page tab of the Page Setup dialog box to cure this problem. Of course it’s important not to shrink the print to such an extent that it becomes unreadable. Printing just a part of the worksheetThis can be done by highlighting the part of the worksheet that you want to print, clicking File and Print… and then clicking Selection in the Print what area of the Print dialog box. Notes written by Martin Ridout Updated by Flavia Jolliffe 2004, 2005, 2006. Yüklə 99,17 Kb. Dostları ilə paylaş:
|
icon on the Standard toolbar is a shortcut for File | Open.
icon on the Standard toolbar is a shortcut for File | New.
for a currency format. By default, this will use £, but you can set almost any other currency symbol – use the online help if you need to do this. 
icon converts numbers to percentages. For example, 0.05 is displayed as 5%.
icon inserts commas in long numbers, like 1,234,567 and fixes the number of decimal places.
and 
increase and decrease the number of decimal places shown. You can use these icons to ensure that all data within a highlighted block has the same number of decimal places.
is on the standard toolbar. You can use it to apply the format of a particular cell to another group of cells. To do this, click on the formatted cell, click on the format painter icon and then use the mouse to paint over another group of cells that you want formatted in the same way.
and 
on the Standard Toolbar.