Computer excel function
Computer Excel function:
(1) Introduction to commonly used functions
① Absolute value function ABS
② Max function MAX (return the maximum value of the value)
③ Min function MIN (return the value of the minimum)
④ rounding function ROUND (rounding numbers, retaining the number of decimal places)
⑤ rounding function TRUNC (cut off the date or number of decimal places)
⑤ rounding function TRUNC (cut off the date or number of decimal places, retaining the number of decimal places) ④ rounding function ROUND (rounding numbers, retain the number of decimal places)
5 rounding function TRUNC (date or number of intercepts, return to the specified value)
6 down to the next rounding function INT (Example: = INT (-227.568) results -228)
(2) sum function
①SUM (for the specified value and)
②SUMIF (single-condition sum, example: = SUMIF (B2: B10, “> 5”))
The first parameter: the condition of the region, used to determine the condition of the cell region
The second parameter: the sum of the conditions, to determine which cells will be used to sum the conditions
The third parameter: the actual summation of the region, the actual cell, region or reference
③ multi-conditional summation function SUMIFS (Example: = SUMIFS (A2: A10, B2: B10, “& gt; 0”, C2: C10, “& lt; 5”))
The first parameter: the actual cell region to participate in the summation of the region
The first parameter: the actual cell region to participate in the summation of the region
The first parameter: the actual cell region to participate in the summation of the region
The first parameter: the actual cell region to participate in the summation of the region
The first parameter. Participate in the summation of the actual cell area
The second parameter: the first group of conditions specified in the region
The third parameter: the first group of conditions specified in the conditions
The latter parameter: the conditions of the second group, but also other multi-group conditions
④ product and function SUMPRODUCT (first calculated between the elements of each array of the same location within the region) product, and then calculate the sum of them)
(3) average function
①average (average)
② averageif (the specified cell area in line with a set of conditions in the cell for the average)
The first parameter: the conditions of comparison of the cell area
The second parameter: the average of the conditions, the form of which can be a number, expression, cell reference, text or function
Third parameter: optional parameters, the average of the requirements of the actual cell area
③ multi-conditional average function AVERAGEIFS (Average_Range, Criteria_Range1, [Criteria1, [Criteria1], [Criteria1], [Criteria1], [Criteria1], [Criteria1]). Criteria1,[Criteria_Range2,Criteria2]……..)
(4) counting function
① counting function COUNT (mostly used to calculate the number of people)
② counting function COUNTA (counting the number of cells that are not empty)
③ conditional counting function COUNTIF (statistics of the number of specified cells in the region of the cell in line with the conditions of the single cell)
④ multi- Conditional Counting Function COUNTIFS (Criteria_Range1,Criteria1,[Criteria_Range2,Criteria2]……..)
(5) IF function
Function: If the specified conditions for the calculation of the results of TRUE, IF function to return a value; the results of the calculation of FALSE, IF function to return to another value
Example: IF (C2>=60, “pass”, “fail”)
(6) VLOOKUP function
(7) RANK function (calculate the ranking)
(8) date and time function Introduction
① current date and time function NOW (show the current date and exact time)
② current date function TODAY (show the current date)
③ year function YEAR (parameters: date value)
Example: YEAR (“2015/12/5”)
④ month function MONTH (display the current month)
(9) Text Class Function Introduction
①Text merge function CONCATENATE (Text1, [Text2]……)
② intercept string function MID (intercept the fifth digit after the three numbers)
Example: MID (13456789754,5,3)
③ left to intercept the string function LEFT (Text, [Num_chars])
④ right to intercept the string function RIGHT (Text, [Num_chars])
④ right to intercept the string function RIGHT (Text, [Num_chars])
Text, [Text2]) [Num_chars])
⑤ remove spaces function TRIM (Text)
6 number of characters function LEN (Text)
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”