I. Basis of Function Application (1) Function and Formula 1. What is a function Excel function is a predefined special formula, which is used to perform data processing tasks such as calculation and analysis. Take the commonly used summation function SUM as an example, and its syntax is "SUM(number 1, number2, ...)", where "SUM" is called the function name, and a function has only one unique name, which determines the function and use of the function. The function name is followed by a left parenthesis, followed by the contents called parameters separated by commas, and finally by a right parenthesis, indicating the end of the function. Parameter is the most complex part of a function, which specifies the operation object, order or structure of the function. It enables users to handle a cell or area, such as analyzing deposit interest, determining score ranking, calculating trigonometric function value, etc. According to the source of functions, Excel functions can be divided into built-in functions and extended functions. As long as Excel is started, users can use it. The latter must be loaded by clicking the "Tools → Plug-ins" menu command before it can be used as a built-in function. 2. What is a formula? Functions and formulas are different and interrelated. If the former is a special formula predefined by Excel, then the latter is a formula designed by users for calculating and processing worksheets. Take the formula "= sum (e1:h1) * a1+26" as an example. It starts with the equal sign "=" and can contain functions, references, operators and constants. In the above formula, "SUM(E 1:H 1)" is a function, "A 1" is a reference to the cell A 1 (using the data stored in it), "26" is a constant, "*" and "+". (2) Parameters of the function The part in brackets on the right side of the function is called a parameter. If a function can use multiple parameters, the parameters are separated by commas. Parameters can be constants (numbers and text), logical values (such as TRUE or FALSE), arrays, error values (such as #N/A) or cell references (such as E 1:H 1), or even another function or functions. The type and location of parameters must meet the requirements of function syntax, otherwise an error message will be returned. 1. constant is a number or text directly entered into a cell or formula, or a number or text value represented by a name. For example, the number "2890.56", the date "2003-8- 19" and the text "Dawn" are all constants. But the formula or the result calculated by the formula is not constant, because as long as the parameters of the formula change, it itself or the calculated result will change. 2. Logical value Logical value is a special parameter, which has only two types: true or false. For example, in the formula "= if (A3=0,", a2/a3) ","A3=0 "is a parameter that can return a TRUE (true) or FALSE (false) result. When "A3=0" is TRUE, fill in "0" in the cell where the formula is located, otherwise, fill in the calculation result of "A2/A3" in the cell. 3. Arrays Arrays are used for formulas that can generate multiple results or calculate a set of parameters stored in rows and columns. There are two types of arrays in Excel: constants and ranges. The former is placed in "{}" (generated automatically by pressing Ctrl+Shift+Enter), the values in the internal columns are separated by commas ",",and the values in each row are separated by semicolons "; "separate. If you want to represent 56, 78, 89 in line 1 and 90, 76, 80 in line 2, you should set up a constant array "{56, 78, 89; 90,76,80}。 A range array is a rectangular range of cells, and the cells in this range * * * use formulas. For example, when the formula "=TREND(B 1:B3, A 1:A3)" is used as an array formula, the rectangular cell range "B 1:B3, A 1:A3" it refers to is a regional array. 4. The error value is mainly an information function using the error value as a parameter. For example, "Error. The TYPE function takes the error value as a parameter. Its grammar is "error. If the parameter is #NUM, please type (error_val) "! , the return value is "6". 5. Cell reference Cell reference is the most common parameter in a function. The purpose of a reference is to identify a worksheet cell or range of cells, and to indicate where a formula or function uses data, so that they can use data from the same cell in all parts of the worksheet or in multiple functions. You can also refer to cells in different worksheets in the same workbook, or even refer to data in other workbooks. According to the change of cell reference when the cell position of the formula changes, we can divide the reference into three types: relative references, absolute reference and mixed reference. Take the formula "=SUM(A2:E2)" stored in F2 cell as an example. When the formula is copied from F2 cell to F3 cell, the reference in the formula will also become "=SUM(A3:E3)". If you continue to copy the formula from column F, the line label in the formula will be automatically added with 1 every time you add a line label. If the above formula is changed to "= sum ($ a $3: $ e $3)", the reference position of the formula is always "A3:E3" area no matter where it is copied. There are two forms of mixed references: absolute columns and relative rows, or absolute rows and relative columns. The former is like "=SUM($A3:$E3)" and the latter is like "=SUM(A$3:E$3)". The above examples all refer to the data in the same worksheet. If you want to analyze data on multiple worksheets in the same workbook, you should use three-dimensional references. If the formula is placed in C6 cell of Sheet 1 and "A 1:A6" of Sheet2 and "B2:B9" of Sheet3 are cited for summation, the reference form in the formula is "=SUM(Sheet2! A 1:A6，Sheet3！ B2:B9). That is to say, the three-dimensional reference not only contains cell or range references, but also is preceded by "!" The worksheet name of. If the data to be referenced comes from another workbook, such as the SUM function in the workbook Book 1, the formula is "=SUM([Book2]Sheet 1! SA S 1: SA S8，[Book2]Sheet2！ SB S 1: SB S9) ",that is, add" [Book2]Sheet 1! " Before the original cell reference. . The one in brackets is with "!" The name of the workbook that is the name of the worksheet. That is, when referring to a cell or range in a workbook, the reference object must be marked with "!" Start with a worksheet separator, and then use parentheses as a workbook separator. However, three-dimensional reference is subject to many restrictions, such as the inability to use array formulas. Tip: The above is the default reference method of Excel, which is called "A 1 reference style". If you want to calculate rows and columns in a macro, you must use "R 1C 1 reference style". In this reference style, Excel uses the methods of "R" plus "row label" and "C" plus "column label" to indicate the cell position. To enable or disable R 1C 1 reference style, you must click the Tools → Options menu command, open the General tab of the dialog box, and select or clear the R 1C 1 reference style option under Settings. Because this quotation style is rarely used, this paper will not introduce it further due to the limitation of space. 6. Nested functions In addition to the situation described above, functions can also be nested, that is, one function is the parameter of another function, such as "= if (or (rightb (E2,1) ="1",rightb (E2,1) =" 3 ",rightb. The IF function in the formula uses the nested RIGHTB function, and the result returned by the latter is used as the logical judgment basis of IF. 7. Names and symbols In order to identify cells or cell ranges more intuitively, we can give them a name, which can be directly referenced in formulas or functions. For example, the "B2:B46" area stores students' physics scores, and the formula for solving the average score is generally "= average (B2:B46)". After the B2:B46 area is named "Physical Achievement", the formula can be changed to "= Average" to make the formula more intuitive. You can name a cell or range by selecting the cell or range you want to name, clicking the Name Box at the top of the formula bar, entering a name in it, and then pressing enter. You can also select a cell or a range of cells to be named, click the menu command Insert → Name → Define, and enter a name in the open Define Name dialog box and confirm. If you want to delete a named area, you can open the "Define Name" dialog box in the same way and select the name to delete. Because most Excel worksheets have "column tags". For example, the first line of the score statistics table usually has column labels (also called fields) such as serial number, name, mathematics and physics. Formulas are available if you click the menu command Tools → Options, click the Recalculation tab in the Open dialog box, and select the Accept Formula Markup option in the Workbook Options option group. For example, the "B2:B46" area stores students' physics scores, and the word "physics" has been entered in the cell B 1, so the formula for finding the average physical score can be written as "= average". It should be noted that the created name can be referenced by all worksheets, and there is no need to add the worksheet name before the name (this is the main advantage of using the name), so the name reference is actually an absolute reference. However, there are many restrictions when formulas refer to column flags. It can only be referenced under the current data column, not across worksheets. However, in some cases, you can copy formulas that reference column flags. In essence, name and logo are both cell references. Because they are not words, neither the name nor the logo can be quoted. (3) Function Input Method For Excel formulas, function is the main component, so the formula input can be attributed to the problem of function input. 1. Insert Function Dialog Box Insert Function Dialog Box is an important tool for Excel to input formulas. The formula "=SUM(Sheet2! A 1:A6，Sheet3！ B2:B9) "as an example, the specific process for Excel to input this formula is as follows: first, select the calculation result (that is, the cell to which the formula needs to be applied), click the fx button in the edit bar (or toolbar), that is, the = at the beginning of the formula appears in the cell and edit bar, and then find the" SUM "function in the selection function list in the open Insert Function dialog box. If the function you need is not in it, you can open" or select a category. Finally, click OK to open the function parameters dialog box. For the SUM function, it can take 30 parameters from the number 1 to the number 30 * * *. For the above formula, first place the cursor in the "number 1" box of the dialog box, and then click "Sheet2! "In the workbook. Sheet tab, "Sheet 2! "You can enter it automatically, and then drag the mouse to select the area you want to refer to. Then use the mouse to click the "number 2" box in the dialog box and click "Sheet3! "In the workbook. Sheet label, its name is "Sheet3! "You can enter it automatically, and then select the cell range to refer to in the same way. The biggest advantage of the above method is that the reference area is very accurate, especially the problem that it is not easy to enter the wrong name of the worksheet or workbook when referencing in three dimensions. 2. Edit field input If you want to apply ready-made formulas, or enter some formulas with complex nested relationships, it is faster to use the edit field for input. First, select the cell where the calculation result is stored; Click the Excel edit bar with the mouse, and enter the parts in turn according to the composition order of the formula. After entering the formula, click the "Enter" button (or enter) in the edit bar. Manual input can also take the above method to refer to the area, and the formula is "=SUM(Sheet2! A 1:A6，Sheet3！ B2:B9) ",for example, you can first enter" =SUM () "in the edit field, then insert the cursor in the middle of brackets, and then refer to the input formula according to the above method. However, commas separating references must be entered manually and cannot be added automatically as in the insert function dialog box. In the corresponding article "Computer News 2003 Combined Edition with CD-ROM", we listed all the functions of Excel 2003 for readers' quick reference.