Wondershare EdrawMind
MindMap Gallery Function application
- 30
- 1
Function application
A useful mind map to share about the application of excel functions! For your reference, this mind map systematically organizes the text functions, array formulas, date and time functions, logical functions and statistical functions of excel and the parameter introduction of the functions.
Edited at 2023-11-14 00:43:23
- bacteria
This is a mind map about bacteria, and its main contents include: overview, morphology, types, structure, reproduction, distribution, application, and expansion. The summary is comprehensive and meticulous, suitable as review materials.
- Plant asexual reproduction
This is a mind map about plant asexual reproduction, and its main contents include: concept, spore reproduction, vegetative reproduction, tissue culture, and buds. The summary is comprehensive and meticulous, suitable as review materials.
- Reproductive development of animals
This is a mind map about the reproductive development of animals, and its main contents include: insects, frogs, birds, sexual reproduction, and asexual reproduction. The summary is comprehensive and meticulous, suitable as review materials.
Function application
- bacteria
This is a mind map about bacteria, and its main contents include: overview, morphology, types, structure, reproduction, distribution, application, and expansion. The summary is comprehensive and meticulous, suitable as review materials.
- Plant asexual reproduction
This is a mind map about plant asexual reproduction, and its main contents include: concept, spore reproduction, vegetative reproduction, tissue culture, and buds. The summary is comprehensive and meticulous, suitable as review materials.
- Reproductive development of animals
This is a mind map about the reproductive development of animals, and its main contents include: insects, frogs, birds, sexual reproduction, and asexual reproduction. The summary is comprehensive and meticulous, suitable as review materials.
- Recommended to you
- Outline
Function application
statistical function
count
The count function can count the number of cells containing numbers and the number of parameter lists.
count (value1,value2...)
value1
―Required. Represents the first item in which the number of numbers is to be calculated. Can be an array, cell reference, or range. Only numeric data will be calculated, such as numbers, dates, or text representing numbers such as "1")
value2
Optional. Indicates that the 2nd to 255th items of the number of numbers are to be calculated, that is, up to 255 parameters can be specified like the parameter valuel.
COUNTIF
The COUNTIF function can count the number of cells that meet a certain condition.
cOUNTIF(range,criteria)
range
Required. Indicates the range of cells to be searched.
criteria
Required. Indicates search conditions. Can be a number, expression value, or text.
AVERAGE
The AVERAGE function calculates the average (arithmetic mean) of data.
AVERAGE(number1,number2....
number1
Required. Represents the first number, cell reference, or range of cells to be averaged.
number2...
Optional. Indicates the 2nd to 255th numbers, cell references or cell ranges to be calculated, and can contain up to 255
AVERAGEIF
The AVERAGEIF function calculates the average (arithmetic mean) of all cells within a range that meet a given condition.
AVERAGEIF (range, criteria, average_range)
range is required. Represents one or more cells to be averaged (that is, the range to be judged), which contains a number or a name, array, or reference that contains a number.
criteria required. represents a given condition. The condition can be a number, expression, cell reference, or text.
average_range is optional. Represents the actual range of cells to be averaged. If this parameter is omitted, the cell range specified by the range parameter is used.
MAX
MAX plot number can return the maximum value in a set of values.
MAX(number1,number2...)
number1
Required. Represents the first numeric parameter to find the maximum value, which can be a number, array, or cell reference.
number2. Optional. Indicates that you want to find 2~255 numeric parameters with the maximum value, that is, you can specify up to 255 parameters like parameter number1.
LARGE
The LARGE function can return the kth largest value in the data group.
MIN
The MIN function returns the smallest value in a set of values.
SMALL
The SMALL function can return the kth smallest value in the data group.
SMALL(array.k)
array is required. An array or data area representing the k-th minimum value to be found.
k is required. Indicates the position of the return value in the array or data cell range (arranged from small to large).
MODE.SNGL
The MODE.SNGL function can return the mode in an array or data range.
MODE.SNGL(number1,number2...)
number1
Required. Represents the first parameter whose mode is to be calculated. Can be numbers, names containing numbers, arrays, and cell references.
number2..
Optional. Indicates the 2nd to 255th parameters to calculate the mode, that is, you can specify up to 255 parameters like parameter number1.
FREQUENCY
The FREQUENCY function can calculate the frequency of occurrence of a value in a certain area and then return a vertical array
FREQUENCY(data_array,bins_array)
data array
Required. Represents a set of numeric values or a reference to a set of numeric values whose frequency is to be counted. If the parameter data array does not contain any values, the function FREQUENCY returns an array of zeros.
bins array
Required. Represents an array of intervals or a reference to an interval into which the values in the parameter data array are to be inserted. If the parameter bins array does not contain any values, the FREQUENCYV function returns the number of elements in the data array.
text function
EXACT
The function of the EXACT function is to compare two text strings to see if they are identical.
EXACT(text1,text2)
text1 is required. Represents the first text string.
text2 is required. Represents the second text string.
CONCATENATE
The CONCATENATE function can combine multiple text strings into one text string
cONCATENATE(text1,text2....)
text1 is required. Represents the first text item to be merged into a single text.
text2...._optional. Indicates the 2nd to 255th text items that will be merged into a single text.
LEN
The function of the LEN function is to return the length of the text character
LEN(text)
text is required. Represents the text whose length is to be found. Spaces will be counted as characters.
SEARCH
The function of the SEARCH function is to find the specified text string in other text strings and return the number of the starting position in the character (letters are not case-sensitive).
SEARCH(findtext, within_ text,start _num)
findtext
Required. Represents the text string to be found (case-insensitive).
within _text
Required. Represents a text string in which to search for the value of the find text parameter.
start_num
Optional. Represents the character number from which to start searching in the within_text parameter.
LEFT
The function of the LEFT function is to return the first or first few characters in the text character rate based on the specified number of characters.
LEFT(text,num chars)
text is required. Represents a text string containing the characters to be extracted.
num chars optional. Represents the number of characters to be extracted by LEFT. If this parameter is omitted, its value is assumed to be 1.
RIGHT
The function of RIGHT is to return the last character or characters in the text according to the specified number of characters.
RIGHT (text, num Chars)
text is required. Represents a text string containing the characters to be extracted.
num Chars is optional. Indicates the number of characters you want the RIGHT function to extract.
SUBSTITUTE
The function of the SUBSTITUTE function is to replace the old text with new text in a textless string.
SUBSTITUTE(text,old text,new text, instance_num)
text is required. Represents text whose characters need to be replaced, or a reference to a text cell containing characters whose characters need to be replaced.
old text is required. Indicates the text that needs to be replaced.
new text is required. Represents the text used to replace l's old text.
instance _num is optional. Indicates an event that specifies to replace old_text with new_text.
REPLACE and REPLACEB
The function of the REPLACE function and the REPLACEB function is to replace part of the text in a text string with another text string according to the specified number of characters.
REPLACE (old text,start num, num chars, new _text)REPLACEB(old _text, start_num, num bytes, new_text)
old_text
Required. Represents text whose partial characters are to be replaced.
start_num
Required. Indicates the character position in old text to be replaced with new text.
numchars
Required. Indicates the characters in old_text that you want the REPLACE function to use new text to replace.
num bytes
Required. Indicates the number of bytes in old text that the REPLACEB function is expected to use new text to replace.
Array formula
Different from entering formulas, array formulas can input array constants or array ranges as array parameters, and the array formula must be entered through the key combination (Ctl Shift Enter]. At this time, Excel will automatically insert the formula.
Enter "=SUM(target area 1: target area 2 * target area 3: target area 4)", confirm the formula, and press the key combination [Ctrl Shift Enter].
datetime function
YEAR
The YEAR function can return the year corresponding to a certain date, that is, an integer from 1900 to 9999
YEAR(serial_number)
Required. Represents the date value of the year to be found. Dates can be entered in many ways: quoted text strings, series numbers, the result of other formulas or functions
MONTH
The MONTH function can return the month corresponding to a certain date, which is an integer from 1 to 12
MONTH(serial_number)
Required. Represents the day value of the month to be found. Dates can be entered in many ways: quoted text strings, series numbers, the result of other formulas or functions
DAY
The DAY function can return the number of days corresponding to a certain date, which is an integer from 1 to 31
DAY(serial_number)
Required. A date value representing the number of days to be found. Dates can be entered in many ways: quoted text strings, series numbers, the result of other formulas or functions
DATEDIF
DATEDIF figures can calculate the number of years, months and days between two dates
DATEDIF(start date,end date,unit)
start_date
Required. Indicates the starting date. It can be a numerical value (serial number value) of the specified date or the month of the cell reference "start_date" is treated as "0" for calculation
end_date
Required. Indicates the expiration date.
unit
Required. Indicates the type of information to be returned.
NETWORKDAYS
NETWORKDAYS calculates the number of days between two dates
NETWORKDAYS(start_date, end_date, holidays)
start_date
Required. Indicates the starting date. It can be a numerical value (serial number value) of the specified date or the month of the cell reference "start date" is treated as "0" for calculation
end_date
Required. Indicates the expiration date. Can be a specified serial number value or cell reference.
holidays
Optional. Indicates days off such as festivals or holidays. Can be a specified serial number value, cell reference, or array constant. When this parameter is omitted, the number of days in the specified period except Saturday and Sunday is returned.
Numeric functions
PRODUCT
The PRODUCT function multiplies all numbers given as arguments
PRODUCT (number1,number2)
number1
Required. Indicates the first number or area to be multiplied. Can be a number, cell reference, or range reference.
number2
Optional. Indicates the 2nd to 255th numbers or areas to be multiplied, that is, number2 can specify up to 255 parameters like number1.
S
The SUM function is a summation function that can return the sum of numbers, logical values, text table SUM expressions of numbers, and directly entered numbers in a certain cell range.
SUM (number1,number2...)
number1
Required. Represents the first number or range to be added. Can be a number, cell reference and cell range referenceInumber1.
number2....
Optional. Indicates the 2nd to 255th numbers or areas to be added, that is, you can specify up to 255 parameters like number1...
SUMIF
The SUMIF function is a conditional summation function. That is, sum the values of the specified cells according to the given conditions.
SUMIF(range,criteria,(sum _range))
range
Required. Represents the cell area that is calculated based on conditions, that is, the cell area where conditions are set. Cells within the range must be numbers, names, arrays, or gold-wrapped number references. Empty values and text values will be ignored.
criteria
Required. Indicates the conditions for summation. It can be in the form of a number, expression, cell reference, text, or function criteria. Specified conditions (except quoted cells and numbers) must be enclosed in double quotes
sum range
Optional. Represents the actual summed range of cells. If you omit this parameter, Excel 2016 will set the range of cells specified in the range parameter as the actual summing range.
QUOTIENT
The QUOTIENT function calculates and returns the integer part of the division
QUOTIENTnumeratordenomiator)
denomlator
Required. Represents the divisor. This can be a number, a cell reference, or a range of cells.
numerator
Required. Represents the dividend. This can be a number, a cell reference, or a range of cells.
ROUND
ROUND figure number can round a value to a specified number of digits.
ROUND(numbernum digits)
number
Required. Indicates the value to be rounded.
num digits
Required. Indicates the number of digits to be rounded to.
logical function
IF
The function of the F function is to perform true and false value judgment and return different results based on the true and false values calculated by the logical value.
IF (logical test, value if true, value if false)
logical test
Required. Indicates the condition to be tested.
value if true
Required. Indicates the value expected to be returned when the result of logical test is TRUE.
value if false
Optional. When the result of logical test is FALSE, the value expected to be returned
AND
The function of the AND function is to perform intersection calculations on multiple logical values to determine whether all conditions in the test are TRUE.
AND (logical1, logica12....)
logical1
Required. Represents the first condition that needs to be tested and can evaluate to TRUE or FALSE.
logical2.
Optional. Indicates other conditions that need to be tested and the calculation result can be TRUE or FALSE (up to 255 conditions)
OR
The function of the OR function is to perform a union calculation on multiple logical values to determine that all conditions in the test set are TRUE.
OR (logical1, logical2,..)
logical1Required. Represents the first condition that needs to be tested and can evaluate to TRUE or FALSE.
logical..._optional. Represents other conditions that need to be tested and can evaluate to TRUE or FALSE (up to 255 conditions).