Excel function Application Tutorial: Parameters for Functions

The part of the function's right parenthesis is called a parameter, and if a function can use more than one argument, the argument is delimited with a half-width comma.

Arguments 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 E1:H1), or even another or several functions. The type and location of the parameter must satisfy the function syntax, or an error message will be returned.

(1) constant

A constant is a number or text that is entered directly into a cell or formula, or a number or text value represented by a name, such as the number "2890.56", the date "2003-8-19", and the text "Dawn" as constants. But either the formula or the result calculated by the formula is not a constant, because as long as the parameter of the formula changes, it will change itself or the calculated result.

(2) Logical value

A logical value is a special kind of parameter that has only true (true) or False (false) two types. For example, in the formula "=if (A3=0," ", A2/a3)", "a3=0" is a parameter that can return either true (True) or False (false) results. When "A3=0" is True (true), fill in "0" in the cell where the formula is located, otherwise fill in the cell with the result of "a2/a3".

(3) array

Arrays are used to produce multiple results, or you can calculate formulas that hold a set of parameters in rows and columns. There are two types of arrays in Excel, constant and range. The former is placed in the "{}" (press Ctrl+shift+enter key combination automatically generated) inside, and the values of the internal columns to be separated by commas, "", the values of each row with a semicolon ";" Separated. If you want to represent 90, 76, 80 in lines 56, 78, 89, and 2nd of line 1th, you should create a constant array of 2 row 3 columns "{56,78,89;90,76,80}."

A range array is a rectangular range of cells in which cells share a formula. For example, when the formula "=trend (B1:B3,A1:A3)" is used as an array formula, the rectangular range of cells that it refers to "b1:b3,a1:a3" is an array of regions.

(4) error value

The main use of error values as arguments is informational functions, such as "error." TYPE "function is an error value as an argument. Its syntax is "ERROR." TYPE (Error_val), and returns a value of "6" if the argument is #num!.

(5) Cell reference

A cell reference is the most common parameter in a function that is intended to identify a worksheet cell or range of cells, and to indicate the position of the data used by the formula or function to make it easier for them to work with data from all parts of the worksheet, or to use the same cell's data in multiple functions. You can also reference cells from different worksheets in the same workbook, or even refer to data in other workbooks.

Depending on the position of the cell where the formula is located, we can refer to the three types of reference as relative, absolute, and mixed. As an example of the formula "=sum (A2:E2)" That is stored in cell F2, the reference in the formula changes to F3 (=sum) when the formula is copied from the F2 cell to the A3:e3 cell. If the formula continues to copy down from column F, the line label in the formula is automatically incremented by 1 for each additional 1 rows.

If the formula above changes to "=sum ($A $ $E $)", the location of the reference is always the "a3:e3" area, regardless of where the formula is copied.

Mixed references have both "absolute and relative rows", or "absolute rows and relative columns". The former such as "=sum ($A 3: $E 3)", the latter such as "=sum (a$3:e$3)".

The above examples refer to data from the same worksheet, and you use a three-dimensional reference if you want to analyze data on multiple worksheets in the same workbook. If the formula is placed in the C6 cell of the worksheet Sheet1, to refer to the A1:a6 and Sheet3 area of the worksheet Sheet2 for the sum operation, the reference in the formula is "B2:B9" (=sum a1:a6,sheet3! B2:B9) ". This means that the three-dimensional reference contains not only the cell or range reference, but also the front with the "!" Name of the worksheet.

If you're referring to data from another workbook, such as the SUM function in workbook Book1 to absolutely refer to the data in the workbook Book2, the formula is "=sum" ([book2]sheet1! SA S1:sa s8,[book2]sheet2! SB s1:sb S9) ", that is, precede the original cell reference with" [book2]sheet1! ". Inside the brackets are the workbook names, with the "!" is the name of the worksheet. That is, when a cell or range is referenced across a workbook, the reference object must be preceded by a "!" As a worksheet separator, and then use the brackets as the workbook separator. However, three-dimensional references are subject to more restrictions, such as the inability to use array formulas.

Tip: This is the default reference for Excel, referred to as "A1 reference style." If you want to calculate the rows and columns that are in the macro, you must use the R1C1 reference style. In this reference style, Excel uses "R" plus "row and C" plus "column labels" to indicate the cell position. Enable or close the R1C1 reference style you must click the tools → options menu command, open the General tab of the dialog box, and select or clear the R1C1 reference style option under Settings. Since this reference style is rarely used, this article does not make further introductions.

(6) Nested functions

In addition to the circumstances described above, a function can also be nested, that is, a function is an argument of another function, such as "=if" (OR rightb (e2,1) = "1", RIGHTB (e2,1) = "3", RIGHTB (e2,1) = "5", RIGHTB (e2,1) = "7", RIGHTB (e2,1) = "9"), "male", "female" ". The IF function in the formula uses the nested RIGHTB function and the result returned by the latter as the logical basis for the IF.

(7) Name and logo

To more visually identify cells or ranges of cells, we can give them a name that is referenced directly in a formula or function. For example, the "b2:b46" area holds students ' physical scores, and the formula for solving average points is generally "=average (b2:b46)". After naming the b2:b46 area "physical fraction", the formula can become "=average (physical fraction)", making the formula more intuitive.

To name a cell or range, select the cell or range of cells you want to name, click the Name box at the top of the formula bar, and enter the name in the column. You can also select the cell or range of cells you want to name, click the insert → name → definition menu command, and then type the name in the Open Definition Name dialog box. If you want to delete a named area, you can open the Define Name dialog box in the same way, and select the name you want to delete.

Because most Excel worksheets have column labels. For example, the first line of a score table usually comes with "serial number", "Name", "mathematical, physical, and so on column labels (also known as fields), if you click the tools → options menu command, click the Recalculate tab in the Open dialog box, and then select Accept formula labels in the Workbook Options option group. option, the formula can refer directly to the column label. For example, the "b2:b46" area holds students ' physical scores, and B1 cell has entered the word "physics", the formula for the physical average is written as "=average (physics)".

In particular, a name reference is actually an absolute reference, and the created name can be referenced by all worksheets, and the reference does not need to add a worksheet name before the name (which is the primary advantage of using the name). However, when a formula references a column label, it can only be referenced below the current data column, not across worksheet references, but formulas that refer to column labels can be copied under certain conditions. In essence, names and flags are a way of referring to cells. Because they are not text, you cannot add quotes with names and flags when you use them.