# excel function for computer grade 2 exam

### Computer level 2 excel test points

Computer level 2 excel test points

Computer level 2 test is one of the four levels of the national computer grade test, sponsored by the Ministry of Education Examination Center, assessing basic computer knowledge and the use of a high-level computer language to write programs as well as the basic skills of on-line debugging. The following is my organization of computer level II excel test points, welcome to share.

The common functions in Excel are the summation function, the average function, the counting function, the IF function, the VLOOKUP function and so on.

The following describes the application of each function.

(1) Introduction to commonly used functions

① Absolute value function ABS (Number)

The main function: the absolute value of the parameters.

Parameter Description: Number indicates that the absolute value of the value or reference to the cell.

Application examples: = ABS (-2) for the absolute value of -2;

= ABC (A2) for the absolute value of the value in cell A2.

② Max function MAX (Number1, Number2 ……)

Main function: to find out the `maximum value of each parameter.

Parameter description: there is at least one parameter and it must be a numeric value, and can contain up to 255.

Application example: if A2:A4 contains the numbers 3, 5, and 6, then:

=MAX(A2:A4) returns a value of 6;

=MAX(A2:A4, 1, 8, 9, 10), which returns a value of 10.

Description: Ignored if there is a text or logical value in the argument.

③ Minimum value function MIN(Numberl, Number2……)

Main function: find the minimum value in each parameter.

Parameter description: there is at least one parameter and it must be a numeric value, and can contain up to 255.

Application example: if A2: A4 contains the numbers 3, 5, 6, then:

=MIN(A2: A4) returns 3;

=MIN(A2: A4, 1, 8, 9, 10), returns 1.

Description: if the there is a text or logical value in the argument, it is ignored.

④ Rounding function ROUND (Number, Num_digits)

Main function: round the parameter Number by the specified number of digits Num_digits.

Parameter description: the parameter Number indicates the number of digits to be rounded; the parameter Num_digits indicates the number of decimal places to be retained.

Application example: = ROUND (227.568, 2) returns 227.57.

⑤ rounding function TRUNC (Number, [Num_digits])

The main function: according to the number of digits specified by Num_digits on the parameter Number to round by the specified number of digits Num_digits.

Parameter description: truncate the fractional part of the parameter Number, return to the integer; parameter Num_digits for the number of precision, the default is 0.

Application examples: = TRUNC(227.568) returns 227.

=TRUNC(-) 227.568) returns -227.

⑥ downward rounding function INT (Number)

The main function: the parameter Number downward rounding to the nearest integer, Number is a required parameter.

Parameter Description: Number indicates the need to round to the nearest integer or referenced cell.

Application examples: = INT (227.568) returns 227;

= INT (-227.568) returns -228.;; = INT (-227.568) returns -228.

### Computer secondary excel commonly used function summary

MID (text, start_num, num_chars), that is, MID (to extract the characters of the text string, to extract the location of the first character)

RIGHT (text, [num_chars]), that is, RIGHT (to extract the characters of the text string, number of characters to extract)

LEFT(text,[num_chars]), i.e., LEFT(text string of characters to extract, number of characters)

SUMIFS(sum_range, criteria_range1,criteria1,[criteria_ range2,criteria2],…) , i.e. SUMIFS(summed_cells,criteria_range1,criteria1,criteria_range2,criteria2……)

countifs(criteria_range1,criteria1,criteria_range2,criteria2, … …) i.e. COUNTIF(first_conditional_range,first_criteria,second_conditional_range,second_criteria ……)

countifs(range,criteria) i.e. COUNTIF(conditional_range,criteria)

IF (logical_test, value_if_true, value_if_false), that is, IF (conditional judgment, the results of the true return value, the results of the false return value)

RANK (number, ref, [order]), that is, RANK (ranking object, ranked data area, ascending or descending order)

MAX(number1,[number2],…) , i.e. MAX(data area).

COUNT(value1,value2,…) , that is, COUNT (cell area)

AVERAGE(number1,[number2],…) AVERAGE (cell area)

SUM(number1,[number2],…) , that is, SUM (cell area)

. Summation function

① Summation function SUM

=SUM (value, cell, cell region, another function)

Applicable scenarios: the simplest form of summation

② Conditional Summation Function SUMIF

=SUMIF (the condition of the cell region, the condition of the actual sum of cell regions)

Applicable scenarios: the most simple form of summation

② Conditional Summation Function SUMIF

=SUMIF (the condition of the cell region, conditions, the actual sum of cell region)

Applicable scenarios: with a single condition of the summation

3 multi-conditional summation function SUMIFS

=SUMIFS (the actual summation of the cell area, the condition of the region where a condition, condition one, the condition of the region where the condition two, the condition two.)

Applicable scenarios: two and more conditions of the sum

④ product and function SUMPRODUCT

=SUMPRODUCT (the first set of data, the second set of data….)

Applicable scenarios: total sales

2. average function

①Average function AVERAGE ② Conditional average function AVERAGEIF ③ Multi-conditional average function AVERAGEIFS

3. VLOOKUP function (mandatory)

=vlookup (against the basis of a value)

=vlookup(control based on a value, compared to the absolute region, the return value in the selected region of the number of columns, 0)

Applicable scenarios: multi-table control

★Note: the second parameter must be an absolute reference

4. IF function

=IF (the complete expression, the expression for the return value of the true expression for the return value of the false return value)

Applicable scenarios: to carry out the conditions of the or multi-conditional judgment, and give the return value

More complex cases: single-conditional multiple judgment, multi-conditional multiple judgment combined with and function and or function.

5. Count function

①Count function, applicable scenarios: numerical counting

② Counta function, applicable scenarios: non-empty counting

③ Countif function, = countif (region, condition), applicable scenarios: single-conditional counting

④ Countifs function, = countifs (region, condition), applicable scenarios: single-conditional counting

④ Countifs function, = countifs (region, condition), applicable scenarios: single-conditional counting

5. countifs (region, condition, region, condition)

6. string interception function

① left interception function LEFT

= LEFT (to intercept the string, from the left side of the start of the interception of a few)

Applicable Scenarios: the left side of the number of rules character interception

② side interception RIGHT

③ middle intercept string MID

=MID (to intercept the string, from the beginning of the first place, to intercept a few)

7. Time and date function

=now ()

=today ()

=year ()

=weekday ()

= Datedif(start date, end date, difference type)

“D/M/Y”