WPS table function and function simple use

Today's small series for you to introduce the WPS table commonly used functions and functions of a simple method.

Foundation of Function Application

(First) Functions and formulas

1. What is a function

The WPS table function is a special formula that defines, performs calculation, analysis, and so on to process data tasks. Take the commonly used SUM function sum as an example, its syntax is "SUM (value 1, value 2,......)". where "SUM" is called a function name, a function has only one name, which determines the function and purpose of the function. The function name is followed by an opening parenthesis, followed by a comma-separated content called a parameter, and a closing parenthesis is used to indicate the end of the function. The parameter is the most complex part of the function, which stipulates the Operation object, order or structure of the function. Allows the user to deal with a cell or region, such as to determine the score ranking, the calculation of trigonometric values.

2. What is a formula

Functions and formulas are both different and interrelated. If the former is a special formula of the WPS table, the latter is the formula that the user designs the calculation and processing of the worksheet. Take the formula "=sum (E1:H1) *a1+26" as an example, it starts with the equal sign "=", and its interior can include functions, references, operators, and constants. "SUM (E1:H1)" In the formula is a function, "A1 is a reference to cell A1 (using the data stored in it)," 26 "is a constant, and" * "and" + "are arithmetic operators (in addition to comparison operators, text operators, and reference operators). If a function is to appear as a formula, it must have two components, one is the equal sign before the function name, and the other is the function itself.

(Second) The parameters of the function

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 truth or false values), 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

Logical value is a special kind of parameter, it is only true or false two types. For example, in the formula "=if (A3=0," ", A2/a3)", "a3=0" is a parameter that can return either true or false results. When "A3=0" is true, fill in "0" in the cell where the formula is located, or 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. The WPS table has a constant and a range of two types of arrays. 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 code), and returns a value of "6" if the argument is #num!.

5. Cell references

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 $3: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:e3)", 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 the WPS table, referred to as the "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, the WPS table 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

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.

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). Because it is not text, you cannot add quotes when using the name and flag.