1. Mathematical functions
1. Take the absolute value function: abs (target number). For example: abs(5)=5; abs(-5)=5.
2. Rounding function: round (target number, how many decimal places to keep). For example: round(5.3347,3)=5.335.
3. Rounding function: int (target number). The int() function does not round but directly deletes the decimal part. For example int(5.78)=5.
4. Remainder function: mod (divisor, divisor). For example: mod(7,3)=1.
5. Find the power function: power (base, exponent). For example: power(10,2)=100.
6. Find the random number function: rand(). This function can generate random numbers greater than or equal to 0 and less than 1.
7. Find the random number function: randbetween(min,max). This function can generate random integers greater than or equal to mix and less than or equal to max.
8. Sum function: sum (area), there is no limit to the number of parameters of the sum function. You can use the mouse to select continuous areas, and you can hold down the ctrl key to select discontinuous areas.
9. Average function: average (area), the usage is the same as the sum() function.
2. Counting functions
1.count(area): Calculate the number of numbers in the area, both text and empty cells Not calculated.
2.counta (area): Count the number of non-empty cells in the area, regardless of format.
3.countblank(area): Count the number of empty cells in a certain area.
4.countif (area, condition) function: Calculate the number of areas that meet a certain condition. The condition must be quoted in English double quotes. If it is a number, it does not need to be used. The condition can also directly quote a cell.
5. Multi-condition counting function: countifs (area 1, condition 1, area 2, condition 2...).
3. Conditional summation function
1. Conditional summation function: sumif (conditional area, condition, summation area). This function can find the area sum of a certain condition.
2. Multi-condition summation function: sumifs (summation area, condition 1 area, condition 1, condition 2 area, condition 2...). This function can find the area sum of multiple conditions.
Related recommendations: "excel basic tutorial"
4. Extreme value ranking functions
1. Find the maximum function: max(area). The maximum value of a certain area can be found.
2. Find the minimum value function: min (area). The minimum value of a certain area can be found.
3. Find the nth maximum value function: large (area, n). The nth maximum value in a certain area can be found.
4. Find the nth minimum value function: small (area, n). The nth minimum value in a certain area can be found.
5. Sorting function: rank (find sorting value, area, order). You can rank a certain area value. The order is 0 or omitted, which means the area is sorted in descending order; if it is not 0, it means it is sorted in ascending order.
5. Text functions
1. Left and right functions: left (text, character n) and right (text, character n). The meaning of these two functions is to intercept n characters on the left and right sides of the text.
2. Interception function: mid (text, starting position k, number of words n). The meaning of this function is to intercept n characters starting from the k-th character of the text.
3. Text query function: find (text 1, text 2, search starting from the nth position). The meaning of this function is to find the character of text 1 when it appears in article 2.
4. I will expand two text functions for you. One is the len() function. len(cell) is used to calculate the length of characters in a certain cell; the second is the text() function. You can convert the contents of cells into the format you want.
6. Query functions
1.match (query value, area, query type): This function can query a certain text in a specific area specific location. The query type is generally 0, which means precise search.
2.index (area, row, column): This function means to return the specified position of the area.
3.vlookup (search target, search range, number of columns of return value, query type). The last parameter query type is generally 0, which means precise search. This function is the most useful function in work, and everyone must learn it.
When the formula is filled downwards, the search range must be referenced absolutely, otherwise the area will change. The vlookup function is very powerful and can be used when the two tables are not in the same excel table. The combined nested use of match() and index() functions can also realize the function of vlookup() function.
4.row(): Returns the number of rows of the current cell
5.column(): Returns the number of columns of the current cell
6. Give you some expansions There are query functions lookup(), hlookup(), and offset(). Interested students can consult relevant information to learn.
7. Date and time functions
1.today(): Get the current date without any parameters.
2.now(): Get the current date and time, without any parameters.
3.year(date): Extract the year of the given date.
4.month(date): Extract the month of the given date.
5.day(date): Extract the day of the given date.
6.hour(time): Extract the number of hours of a given time.
7.minute(time): Extract the minutes of the given time.
8.second(time): Extract the number of seconds of the given time.
9.weeknum(date): Get the week number of this year for a given date.
10.weekday(date): Get the day of the week for a given date.
11.datedif (date 1, date 2, format): This function represents the date difference between date 1 and date 2. The format can be: "y" represents the year; "m" represents the month; " d" represents the day, date 1 is the start time, and date 2 is the end time.
8. Judgment functions
1.if (condition, result 1, result 2) function: if the condition is judged to be true, return result 1; if the condition is judged to be false , return result 2.
2. Nested use of if() function: This function can often be used nested within itself.
3.if error(parameter 1, parameter 2) function: If parameter 1 is an error, it returns parameter 2; if parameter 1 has no error, it returns the normal result of parameter 1.
The above is the detailed content of What are the excel functions?. For more information, please follow other related articles on the PHP Chinese website!