Excel function is a predefined special formula, which is used to perform data processing tasks such as calculation and analysis. The concepts of Excel function and Excel formula are easily confused. They are not the same concept, but there are differences and connections. Therefore, we must first master the related knowledge of Excel functions and formulas. Let's learn from me.
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. What is its grammar? SUM (number 1, number 2, ...)? . Among them? SUM？ Known as the function name, a function has only one name, which determines the function and purpose 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. For example, "=TODAY ()" is a function of the current date.
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. For example:? =SUM(A 1，B2，C3，D4)？ B2, C3 and D4 in brackets are parameters.
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. And the latter must click? Tools? Plug-in? Menu commands are loaded before they can be used like built-in functions.
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.
By the formula? = SUM(e 1:h 1)* a 1+26？ For example, does it need an equal sign? =? First, it can include functions, references, operators and constants. In the above formula? SUM(E 1:H 1)？ Is a function. A 1？ Is it a reference to cell A 1 (using the data stored in it)? 26? Is a constant. *? And then what? +? It is an arithmetic operator (there are also comparison operators, text operators and reference operators).
To appear as a formula, a function must have two components, one is the equal sign before the function name and the other is the function itself.
(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. Its grammar is
? SUM (number 1, number 2, ...)? . Among them? SUM？ Known as the function name, a function has only one name, which determines the function and purpose 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. And the latter must click? Tools? Plug-in? Menu commands are loaded before they can be used like built-in functions.
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. By the formula? = SUM(e 1:h 1)* a 1+26？ For example, does it need an equal sign? =? First, it can include functions, references, operators and constants. In the above formula? SUM(E 1:H 1)？ Is a function. A 1？ Is it a reference to cell A 1 (using the data stored in it)? 26? Is a constant. *? And then what? +? It is an arithmetic operator (there are also 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.
(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 constant a number or text entered directly into a cell or formula, or a number or text value represented by a name, such as a number? 2890.56? , date
? 2003-8- 19? And text messages? Dawn? Is a constant. 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 TRUE or.
There are two types of FALSE. For example, in formula? =IF(A3=0，“”，A2/A3)？ Yes,? A3=0？ Is a parameter that can return a TRUE or FALSE result. What time? A3=0？ Fill in the cell where the formula is located when it is true? 0? , otherwise fill in the cell? A2/A3？ The calculation results of.
Step 3 arrange
Arrays are used for formulas that can produce multiple results or calculate a set of parameters stored in rows and columns. There are two types of arrays in Excel: constants and ranges. Where is the former? {}? (press Ctrl+Shift+Enter to generate automatically) inside, and the value of the internal column should be comma? ,? Separate, use semicolons for each line of values? ; ? Separate. If you want to represent 56, 78, 89 in line 1 and 90, 76, 80 in line 2, you want to create a constant array with 2 rows and 3 columns? {56,78,89; 90,76,80}。 A range array is a rectangular range of cells, and the cells in this range * * * use formulas. Like a formula? =TREND(B 1:B3，A 1:A3)？ When used as an array formula, what rectangular cell range do you mean? B 1:B3，A 1:A3？ Is an array of regions.
4. Wrong value
The main parameters of the error value are information functions, such as? Mistake. Type? This function takes the wrong value as an argument. Its grammar is
? Mistake. TYPE(error_val)？ If the parameter is #NUM! Returns a numeric value? 6? .
5. Cell reference
Cell references are the most common parameters in functions. The purpose of a reference is to identify a worksheet cell or range of cells, and to indicate where formulas or functions use data, so that they can use data from all parts of the worksheet or the same cell 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 formula, when the position of the cell changes, the cell reference will also change.
As for the situation, we can divide the reference into three types: relative reference, absolute reference and mixed reference. Formula stored in F2 cell? =SUM(A2:E2)？ For example, when a formula is copied from F2 cell to F3 cell, the reference in the formula will also become? =SUM(A3:E3)？ . If the formula continues to be copied from column F,? Line mark? Every time 1 line is added, the line label in the formula is automatically added with 1. If the above formula is changed to? = SUM($ A $ 3:E $ 3)？ No matter where the formula is copied, its reference position is always. A3:E3？ Area. What are the mixed references? Absolute columns and relative rows? Or? Absolute rows and relative columns? Two forms. What about the former? =SUM($A3:$E3)？ What is the latter 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 in C6 cell of worksheet Sheet 1, what should you refer to in worksheet Sheet2? A 1:A6？ What about the third one? B2:B9？ What is the reference form in the formula for regional summation operation? =SUM(Sheet2！ A 1:A6，Sheet3！ B2:B9)？ . That is to say, a three-dimensional reference not only contains a cell or range reference, but also is preceded by a band? ! ? 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, supplement? [Book2]Sheet 1！ ? . In parentheses is the name of the workbook, with. ! ? The name of the worksheet. That is, when referring to a cell or range in a workbook, the reference object must be preceded by? ! ? As 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 count places? Macro? Rows and columns in must use? R 1C 1 reference style? . In this reference style, Excel uses? r？ Besides? Line mark? And then what? c？ Besides? Listing? The method of indicates the cell location. To enable or disable R 1C 1 reference style, you must click? Tools? Options? Menu command to open the dialog box? Ordinary? Tab, select or clear? Settings? Under it? R 1C 1 reference style? Options. 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 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(E2， 1)="5 "，RIGHTB(E2， 1)="7 "，RIGHTB(E2， 1)="9 ")。 . 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. Name and logo
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. take for example
? B2:B46？ This area contains students' physics scores. What is the formula for solving the average score? = Average (B2:B46)? . Name B2:B46 area? Physics grades? The formula can be changed to? = average score (physics score)? Thus making the formula more intuitive. To name a cell or range, select the cell or range you want to name, and then click? Name box? , enter a name in it, and then press Enter. You can also select a cell or range of cells to name, and then click? Insert? Name? Definition? Menu command, opening? Define the name? Enter a name in the dialog box and confirm. If you want to delete a named area, you can open it in the same way? Define the name? Dialog box, select the name to delete and delete it. Because most Excel worksheets have it? Column logo? . For example, the first line of the score statistics table usually has? Serial number? 、? Name? 、? Math? 、? Physics? Wait? Column logo? (also called field), if you click? Tools? Options? Menu command, click? Recalculate? Tab, select? Workbook options? In the options group? Accept formula flag? Option, formula can be directly referenced? Column logo? Yes Like what? B2:B46？ The area contains students' physics scores, and the cell B 1 has been entered? Physics? The formula for finding the average physical score can be written as? = average (physical)? . 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. But formula reference? Column logo? When there are many restrictions, it can only be referenced under the current data column, not across worksheets, but references? Column logo? The formula can be copied under certain conditions. 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. =SUM(Sheet2！ A 1:A6，Sheet3！ B2:B9)？ For example, the specific process of entering a formula in Excel is as follows: first, select the cell where the calculation result is stored (that is, the cell where the formula needs to be applied), and click? fx？ Button, indicating the beginning of the formula? =? Appears in cells and formula bars, and then in the open? Insert function? In the dialog box? Select function? Did you find the list? SUM？ Function. If the function you need is not in it, you can turn it on? Or choose a category? Drop-down list to select. Click last? Are you sure? Button, open? 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, you should first put the cursor in the dialog box. Number 1? Box, in the workbook, click? Sheet2！ ? Sheet label,? Sheet2！ ? You can enter it automatically, and then drag the mouse to select the area to refer to. Then click with the mouse? Number two? Box, in the workbook, click? Sheet3！ ? Sheet label, its two names? Sheet3！ ? You can enter it automatically, and then select the range of cells to reference 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 the input in the field
If you want to apply ready-made formulas, or enter some formulas with complex nested relationships, it will be faster to enter them using the formula bar.
First, select the cell where the calculation result is stored; Click on the Excel edit bar, and input the parts in turn according to the composition order of the formula. After entering the formula, click? Input? Just press the button (or enter). When entering manually, you can also use the method described above to refer to the area, and use the formula? =SUM(Sheet2！ A 1:A6，Sheet3！ B2:B9)？ For example, you can enter it in the edit field first? =SUM()？ , then insert the cursor in the middle of brackets, and then refer to the input formula as described above. However, commas separating references must be entered manually, not like? Insert function? Dialog box.
;