1, function name: SUM
Conditions of use: Calculate the sum of all parameter values.
Usage format: sum (no 1, NO2 ...)
Parameter description: NO 1, NO2 ... indicates the value to be calculated, which can be a specific value, a referenced cell (area), etc.
Application example: as shown in figure 1, enter the formula =SUM(D2:D 1 1) in cell D 12, and then confirm to get the total math score.
Figure 1:
Note: If the parameter is an array or a reference, only the numbers in it will be counted. Blank cells, logical values, text or error values in an array or reference are ignored.
2. Function name: SUMIF
Usage Condition: Calculates the sum of the values in the cell range that meet the specified conditions.
Usage format: SUMIF(Range, Criteria, Sum_Range)
Parameter Description: Range indicates the cell area for conditional judgment; Criteria is the specified condition (expression); Sum_Range represents the cell range where the value to be calculated is located.
Application example: as shown in figure 1, enter the formula =SUMIF(C2:C 1 1, "male", D2:D 1 1) in cell D1,and then you can get the math scores of male students after confirmation.
Note: If the above formula is modified to =SUMIF(C2:C 1 1, "female", D2: d1), the sum of the math scores of "female" students can be obtained; Among them, "male" and "female" need to be put in double quotation marks in English ("male" and "female") because they are textual.
3. Function name: IF
Main function: make logical judgment, and return different results according to the true value of logical expression, so as to perform the conditional judgment task of numerical value or formula.
Use format: IF(logical_test, value_if_true, value_if_false).
Parameter description: the result of Logical_test of any numerical value or expression is TRUE (true and correct) or FALSE (untrue and incorrect); Value_if_TRUE is the return value of the function when Logical_test is TRUE, and Value_if_FALSE is the return value of the function when Logical_test is FALSE.
Application example: formula "= if (a1>; =60, "pass" and "fail"), if the expression A1> =60 is true, then the cell where the formula is located is designated as passed, otherwise it is designated as failed.
Note: Returns TRUE if logical_test is TRUE and value_if_true is omitted. Value_if_true can be an expression;
For example, the formula "= if(C2 >；; =85, "a", if (C2 >; =70, "b", if (C2 >; =60, "c", if (C2 <; 60, "d")) ",in which the second IF statement is also the parameter of the first IF statement, that is, it does not satisfy" C2 >；; =85 "The value returned by this condition. Similarly, the third IF statement is the parameter of the second IF statement, and so on. For example, if the first logical judgment expression C2 >；; =85, the cell where the formula is located is assigned as "a"; If the first logical judgment expression C2 >: =85 does not hold, then the second if statement "IF(C2 >；; =70"; And so on until the end of the calculation, this function is widely used in situations that require logical judgment.
4. Function name: average value
Main function: Find the arithmetic average of all parameters.
Usage format: average (number 1, number 2, ...)
Parameter description: number 1, number 2, ...: average value or reference cell (area) is required, and there are no more than 30 parameters.
Application example: as shown in figure 1, enter the formula =AVERAGE(D2:D 1 1) in cell D 12, and you can get the average math score after confirmation.
Note: If the reference area contains cells with a value of "0", it will be counted; If the reference area contains blank or character cells, it is not counted.
5. Function name: ISERROR
Main function: used to judge whether the value returned by the function (formula) is wrong. If there is an error, the function returns TRUE, otherwise it returns FALSE.
Usage format: ISERROR (value)
Parameter Description: Value indicates the value or expression to be judged.
Application example: If the formula =ISERROR(A2/B2) is entered in the C2 cell, after confirmation, if the B2 cell is empty or "0", then A2/B2 has an error. At this point, the aforementioned function returns a TRUE result, which will be displayed as TRUE in C2, and FALSE otherwise.
Note: This function is usually used with the IF function. If the above formula is modified to =IF(ISERROR(A2/B2), "",A2/B2), if B2 is empty or "0", the C2 cell will be displayed empty, otherwise the result of A2/B2 will be displayed.
6. Function name: MID Function name: MID
Main function: Intercept a specified number of characters from the specified position of the text string.
Usage format: MID(text, start_num, num_chars)
Parameter description: text represents a text string; Start_num indicates the specified starting position; Num_chars represents the number to be truncated.
Application example: suppose the string "I like the celestial pole net" is saved in cell A47, and we enter the formula = mid (A47,4,3) in cell C47, and the character "celestial pole net" will be displayed after confirmation.
Special reminder: the parameters in the formula should be separated by English commas.
7. Function name: RANK
Main function: Returns the ranking of one value in a list of values relative to other values.
Usage format: grade (number, reference, order)
Parameter description: Number indicates the numerical value to be sorted; Ref represents the cell area where the sort value is located; Order indicates the sorting method parameter (if it is "0" or omitted, it will be sorted in descending order, that is, the larger the value, the smaller the sorting result value; If it is not a "0" value, it will be sorted in ascending order, that is, the greater the value, the greater the sorting result value; )。
Application example: If you enter the formula = rank (d2, $ d $2: $ d $ 1 1, 0) in the E2 cell, you can get the ranking result of Zheng Tao's math scores in the whole class after confirmation.
Note: In the above formula, we let the number D2 parameter take the form of relative references, and let the ref parameter ($D$2:$D$ 1 1) take the form of absolute reference (with a "$" symbol added). After completing this setting, select the E2 cell and move the mouse to the lower right corner of the cell. When it becomes a thin crosshair (.
8. Function name: VLOOKUP
Main function: find the specified value in the first column of the data table and return the value of the specified column in the data table relative to the current row.
Use format:
VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)
Parameter Description: Lookup_value indicates the value to be searched; Table_array represents the range of cells for which you need to find data; Col_index_num is the column number of the matching value to be returned in the table_array area (when Col_index_num is 2, the value of the second column of table_array is returned, and when it is 3, the value of the third column is returned ...); Range_lookup is a logical value. If TRUE or omitted, an approximate matching value is returned, that is, if an exact matching value cannot be found, the maximum value less than lookup_value is returned. If FALSE, the exact match value is returned, and if it is not found, the error value #N/A is returned.
Application example:
Figure 2:
Referring to Figure 2, we enter the formula in cell H4:
=VLOOKUP(G4，$A$4:$E$20，2，FALSE)。 After confirmation, just enter a customer's name (such as Chen Wuxiang) in cell G4, and the customer's sales in 65438+ 10 will be immediately displayed in cell H4. If you want to display the customer's sales in February, just enter the formula in cell i4: = vlookup.
Note: Lookup_value must be in the first column of the Table_array area; If the Range_lookup parameter is omitted, the first column of Table_array must be sorted.
9. function name: AVEDEV
Main function: return the average value of the absolute deviation between a set of data and its average value. This function can be used with the average function * * * to calculate the volatility. The volatility formula is AVEDEV/AVERAGE.
Usage format: AVEDEV(number 1, number2, ...)
Parameter Description: Numbers 1, 2, ... are a set of parameters used to calculate the average value of absolute deviation, the number of which can be between 1 ~ 30.
Application example: if A 1=79, A2=62, A3=45, A4=90, A5=25, if the formula "=AVEDEV(A 1:A5)" is entered in cell A6, 20. 16 will be returned.
1 1. Function names: COUNT and COUNTA.
(1)COUNT: Use the function COUNT to calculate the number of numeric items in the cell area.
Usage format: COUNT(value 1, value2 ...)
The value 1, the value 2, ... contains or refers to parameters of various types of data (1 ~ 30). Please note that only numerical data is calculated.
(2)COUNTA: The function COUNTA can be used to calculate the number of data items in the cell area.
Usage format: COUNTA(value 1, value2 ...)
Value 1, value 2, ... The number of parameters is 1 ~ 30.
The difference between COUNTA and COUNTA: When counting, the function COUNT only counts numbers, ignoring wrong values or other words that cannot be converted into numbers. The parameter value of COUNTA can be of any type, and it can contain empty characters (""), but it cannot contain blank cells. As shown in Figure 3:
For example, if the formula =COUNT(B 1:B8) in cell B9 equals 7, then the formula =COUNTA(B 1:B8) equals 8.
12, Conditional Sum Wizard
Main function: it is a multi-conditional summation and an extension of SUMIF function.
Use example:
Figure 4 is the customer classification statistics of Jixi Marketing Department. Taking the number of people with the market type of "city" and the format of "supermarket" as an example, we introduce how to use the "conditional sum" function to realize the data statistics process under various conditions.
Figure 4 Customer Classification Statistics
(1) Select the menu command Tools → Wizard → Conditional Sum. In the pop-up interface of Conditional Sum Wizard-Step 4 1, confirm that the data area of conditional sum is the whole table (just click any cell in the table to execute the command, and the whole table area will be automatically selected), and click Next.
(2) In the Conditional Sum Wizard-Step 2 interface, select Sum Column as Customer Name, add the restrictions such as Market Type = City, Format = Supermarket to the condition list one by one, and click Next (as shown in Figure 5).
Fig. 5 indicates the condition data.
(3) In the Conditional Sum Wizard-Step 4-Step 3 interface, select "Only Display Formula" as the display mode of statistical results, and click "Next".
(4) Finally, select the cell where the target data is stored. In this example, select cell K5, click Next after selection, and finally click Finish. At this time, the data result we want to count will appear in cell K5. The data of other cells can be completed in turn according to this operation.
In this example, we can see that data statistics with simple conditions can actually be achieved through formulas, such as formula "=SUMIF (7)", and of course it can also be achieved through classified summary commands. However, it is difficult to use formulas for data statistics with complex conditions, especially for colleagues who are unfamiliar with functions. Using the "conditional sum" function can easily meet our statistical needs.
Precautions:
1. The Conditional Sum function must be installed first. Click Add-ins on the Tools menu, select the Conditional Sum Wizard check box in the Add-ins dialog box, and then click OK. This feature will be installed automatically.
2. In the area selected in the first step, the header line of the data area should be included.
Typing is not easy. If you are satisfied, please adopt it.