[ Pobierz całość w formacie PDF ]
.You can use simple mathematical and logical operators to construct logical formu-las, or you can use an assortment of built-in functions.For information about using condi-tional test functions, see Understanding Logical Functions on page 421.Each of the following formulas performs a rudimentary conditional test:=A1>A2=5-3A2,the greater than (>) logical operator compares the values in cells A1 and A2.Table 12-4 liststhe six logical operators.393Chapter 12 Part 5: Creating Formulas and Performing Data AnalysisMicrosoft Office Excel 2003 Inside OutTable 12-4.Logical OperatorsOperator Definition= Equal to> Greater than= Greater than or equal to0, SUM(A1:A10), " ") returns a null string (" ") ifthe conditional test is false.The logical_test argument can also consist of text.For example,the formula =IF(A1="Test", 100, 200) returns the value 100 if cell A1 contains the string Testor returns 200 if it contains any other entry.The match between the two text entries must beexact except for case.The AND, OR, and NOT FunctionsThree additional functions help you develop compound conditional tests: AND, OR, andNOT.These functions work with the logical operators =, >, =, =80, A1=60, A1A2, A2=$G$15 stipulates that the value of each cell inD2:D7 must be 6 or greater, and the expression $F$2:$F$7B1 in the Formula box.720Chapter 28 Part 9: Managing Databases and ListsManaging Information in ListsTo specify more than one validating criterion, use the AND function, the OR function, orboth.For example, to ensure that C1 is greater than A1 but less than B1, enter=AND(C1>A1,C1B1 as your validationformula, Excel ensures that A1 is greater than B1, A2 is greater than B2, and so on.To makea reference absolute, add the appropriate dollar signs.For example, to make all values inA1:A10 greater than B1, change the formula to =A1>$B$1.As usual, you can press the F4 keyafter typing a cell reference to make that reference absolute.Specifying an Input Message (Prompt)To supply an input prompt to guide your user, click the Input Message tab in the Data Vali-dation dialog box.You have the opportunity to specify both the content of the message andthe title of the window in which it s delivered.The message will be displayed as a commentbeside the validated cell whenever the user selects that cell.Specifying Error Alert Style and MessageIf you do no more than supply validation criteria for a cell or range, Excel displays a standarderror message when the user enters invalid data and forces the user to retry or cancel (cancel-ing leaves the cell s previous value in place).To supply your own error message, click theError Alert tab in the Data Validation dialog box.In the dialog box that appears, you canenter title and text for your message.You can also use the Error Alert tab to specify the style of message that appears.Your choicesare Stop, Warning, and Information.These three message styles display different icons besideyour message text, and they have differing consequences for the user as well.If your messagestyle is Stop (the default), the user is forced to retry or cancel.If you choose Warning, the userwill be told that his or her entry is invalid but will be given the chance to leave it in the cellanyway.If you choose Information, the user will be told about the error but will not be givena retry option.Using Excel s Form Command to Work with ListsYou can add new information to a list by moving to the first blank row below the list and typ-ing, but you might find it easier to choose Data, Form.The Form command generates a dia-log box that can help you or someone else who uses the list you design add data to andotherwise manipulate your list.Figure 28-12 shows this data-entry form in the context of thestaff list shown in Figure 28-1.721Chapter 28 Part 9: Managing Databases and ListsMicrosoft Office Excel 2003 Inside Outf28ie12Figure 28-12.The Form command generates a no-frills dialog box for manipulating informa-tion in a list.At the top of the form, Excel displays the name of the worksheet (not the workbook) thatcontains the list on which the form is based.Immediately below this title bar are all the list scolumn headings.If you already entered some rows in your list, you see the entries for thefirst row of data alongside the column headings.(The form always shows the first row s datainitially, regardless of which cell in the list is currently active.)In Figure 28-12, the data for the first seven fields appears in edit boxes.The value for the lastfield, Age, does not appear in an edit box because this value is the result of a calculation.Youcan use the form to change any noncalculated value in the list.Adding RowsTo add a new row to your list, click New.Excel displays a blank form, in which you can enterthe values for your new row.To add another row, click New again; to return to the worksheet,click Close.When you add rows to your list with the Form command, Excel expands the list downwardwithout affecting any cells outside the list.If expanding the list will overwrite existing data,Excel alerts you and refuses to accept new data [ Pobierz całość w formacie PDF ]