1. Basics of function application
(1) Functions and formulas
1.What is a function
WPS table functions are pre-defined special formulas that perform calculations, analysis and other data processing tasks. Take the commonly used summation function SUM as an example. Its syntax is "SUM(value 1, value 2,...)". "SUM" is called the 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 list of contents called parameters, and finally a closing parenthesis to indicate the end of the function. Parameters are the most complex component of a function, which specify the operand, order or structure of the function. Allows users to process a certain cell or area, such as determining grade ranking, calculating trigonometric function values, etc.
2.What is a formula?
Functions and formulas are both different and related to each other. If the former is a special formula predefined by the WPS form, the latter is a formula designed by the user to calculate and process the worksheet. Take the formula "=SUM(E1:H1)*A1+26" as an example. It starts with the equal sign "=" and can include functions, references, operators and constants inside. "SUM(E1:H1)" in the above formula is a function, "A1" is a reference to cell A1 (using the data stored in it), "26" is a constant, "*" and "+" are Arithmetic operators (in addition to comparison operators, literal operators, and reference operators). If a function is to appear as a formula, it must have two components, an equal sign in front of the function name, and the function itself.
(2) Parameters of function
The part in parentheses on the right side of the function is called the parameter. If a function can use multiple parameters, then use half-width commas to separate the parameters. 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 E1:H1), or even another or several functions, etc. The type and position of the parameters must meet the requirements of the function syntax, otherwise an error message will be returned.
1.Constant
Constants are numbers or text that are entered directly into cells or formulas, or numbers or text values represented by names. For example, the number "2890.56", the date "2003-8-19" and the text "Dawn" are all constants. But neither the formula nor the result calculated by the formula is a constant, because as long as the parameters of the formula change, it itself or the calculated result will change.
2. Logical value
Logical values are a special type of parameter, which only have two types: true or false. For example, in the formula "=IF(A3=0,"",A2/A3)", "A3=0" is a parameter that can return true or false results. 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.Array
Arrays are used for formulas that produce multiple results, or that calculate a set of parameters stored in rows and columns. There are two types of arrays in the WPS table: constant and area arrays. The former is placed inside "{}" (automatically generated by pressing the Ctrl+Shift+Enter key combination), and the values in each column inside should be separated by commas ",", and the values in each row should be separated by semicolons ";". If you want to represent 56, 78, 89 in row 1 and 90, 76, 80 in row 2, you should create a constant array with 2 rows and 3 columns "{56,78,89;90,76,80 }.
The range array is a rectangular range of cells, and the cells in this range share a formula. For example, when the formula "=TREND(B1:B3,A1:A3)" is used as an array formula, the rectangular cell range "B1:B3,A1:A3" it refers to is a range array.
4. Error value
The main information functions that use error values as parameters, such as the "ERROR.TYPE" function, use error values as parameters. Its syntax is "ERROR.TYPE (error code)". If the parameter is #NUM!, the value "6" is returned.
5. Cell reference
Cell reference is the most common parameter in a function. The purpose of the reference is to identify the worksheet cell or cell range and indicate the location of the data used by the formula or function, so that they can use data everywhere in the worksheet, or in multiple places. Use data from the same cell in two functions. You can also reference cells in different worksheets in the same workbook, or even reference data in other workbooks. According to the change of cell reference when the position of the cell where the formula is located changes, we can divide the reference into three types: relative reference, absolute reference and mixed reference. Take the formula "=SUM(A2:E2)" stored in cell F2 as an example. When the formula is copied from cell F2 to cell F3, the reference in the formula will also change to "=SUM(A3:E3)" ". If the formula continues to be copied from column F downwards, every time the "row label" increases by 1 row, the row label in the formula will automatically increase by 1. If the above formula is changed to "=SUM($A $3:E $3)", no matter where the formula is copied, the location it refers to will always be the "A3:E3" area.
There are two forms of mixed references: "absolute column and relative row", or "absolute row and relative column". The former is like "=SUM($A3:E3)", and the latter is like "=SUM(A$3:E$3)". The above examples all refer to data in the same worksheet. If you want to analyze data on multiple worksheets in the same workbook, you must use three-dimensional references. If the formula is placed in cell C6 of Sheet1, and you want to reference the "A1:A6" of Sheet2 and the "B2:B9" areas of Sheet3 for the sum operation, the reference form in the formula is "=SUM(Sheet2! A1:A6,Sheet3!B2:B9)". That is to say, the three-dimensional reference not only contains the cell or range reference, but also has the worksheet name with "!" in front. If the data you want to reference comes from another workbook, for example, the SUM function in workbook Book1 must absolutely reference the data in workbook Book2, and its formula is "=SUM([Book2]Sheet1! SA S1: SA S8,[Book2 ]Sheet2! SB S1:SB S9)", that is, add "[Book2]Sheet1!" in front of the original cell reference. The one enclosed in square brackets is the workbook name, and the one with "!" is the worksheet name. That is, when referencing cells or ranges across workbooks, "!" must be used as the worksheet separator in front of the referenced object, and square brackets must be used as the workbook separator. However, three-dimensional references are subject to more restrictions, such as array formulas cannot be used.
Tip: What is introduced above is the default citation method of WPS tables, called "A1 citation style". If you want to count rows and columns within a macro, you must use the "R1C1 citation style". In this reference style, the WPS table uses "R" plus "row label" and "C" plus "column label" to indicate cell location. To enable or turn off 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 citation style is rarely used, this article will not introduce it further due to space limitations.
6. Nested functions
In addition to the situations introduced above, functions can also be nested, that is, one function is a parameter 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 uses the result returned by the latter as the logical judgment basis of IF.
7. Name
In order to identify cells or cell ranges more intuitively, we can give them a name and reference them directly in formulas or functions. For example, the "B2:B46" area stores students' physics scores, and the formula for calculating the average score is generally "=AVERAGE(B2:B46)". After naming the B2:B46 area "Physical Fraction", the formula can be changed to "=AVERAGE (Physical Fraction)", making the formula more intuitive.
The method to name a cell or range is: select the cell or range of cells to be named, click the "name box" at the top of the edit bar, enter the name and press Enter. You can also select the cell or range of cells to be named, click the "Insert→Name→Define" menu command, enter the name in the "Define Name" dialog box that opens, and then confirm. If you want to delete a named area, you can open the "Define Name" dialog box in the same way, select the name you want to delete, and delete it.
It should be noted that the created name can be referenced by all worksheets, and there is no need to add the worksheet name in front of the name when referencing (this is the main advantage of using names), so the name reference is actually an absolute reference. Because it is not text, neither the name nor the logo can be quoted.
(3) Function input method
For WPS table formulas, functions are the main component, so formula input can be attributed to the problem of function input.
1. "Insert Function" dialog box
The "Insert Function" dialog box is an important tool for entering formulas in WPS forms. Taking the formula "=SUM(Sheet2!A1:A6,Sheet3!B2:B9)" as an example, the specific process of entering this formula in WPS forms is:
First select the cell that stores the calculation result (that is, the formula needs to be applied), click the "fx" button in the edit bar (or toolbar), then the "=" that means the beginning of the formula appears in the cell and edit bar, and then open Locate the "SUM" function in the "Select Function" list in the "Insert Function" dialog box. If the function you need is not there, you can open the "or select category" drop-down list to select it. Finally click the "OK" button and "=SUM()" will appear in the cell.
For the SUM function, it can use a total of 30 parameters starting from the value 1 to the value 30. For the above formula, you should first place the cursor in the brackets, click the "Sheet2!" worksheet label in the workbook, continuously select the A1:A16 area, hold down the Ctrl key, and click "Sheet 3!" Select the B2:B9 area and press Enter.
The biggest advantage of the above method is that the referenced area is very accurate, especially when referencing in three dimensions, it is not easy to enter the wrong worksheet or workbook name.
2. Input in the edit bar
If you want to apply a ready-made formula, or enter some formulas with complex nested relationships, it is faster to use the edit bar to enter.
First select the cell where the calculation results are stored; click the WPS table edit bar and enter each part in the order of the formula. After the formula is entered, click the "Enter" (i.e. "√") button in the edit bar (or Enter). When manually inputting, you can also use the method introduced above to reference the area. Taking the formula "=SUM(Sheet2!A1:A6,Sheet3!B2:B9)" as an example, you can first enter "=SUM()" in the edit bar. Then insert the cursor between the brackets and follow the method described above to quote and enter the formula. But the commas separating the references must be entered manually and cannot be added automatically as in the Insert Function dialog box.
