The use of excel functions and examples

Answer 1

The following are some examples of how to use the Excel functions:

** 1. XLOOkUP Function **
1. ** Function **: Search for matching items in a certain range or array, and return the corresponding items through the second range or array. By default, it is an exact match.
2. ** Syntactic structure **:=XLOOkUP(Find Value, Find Array, Return Array, No Value Found, Match Mode, Search Mode). The fourth, fifth, and sixth parameters can be omitted. Usually, you only need to set the first three functions.
3. ** Instance **: If you want to inquire about the basic salary of someone (such as Zhao Fei), assume that the name is in the A2:A8 cell area and the basic salary is in the D2:D8 cell area. Enter the formula in the target cell: =XLOOkUP(G3,A2:A8,D2:D8)(assume that the name you want to find is in the G3 cell), and then enter.

** 2. Filterer Function **
1. ** Function **: Screen a series of data based on the defined conditions.
2. ** Syntactic structure **:= PROCESER (Array, Including, Empty Value), where "Array" is the filter area,"Including" is the filter condition (for example, filter column = filter condition), and "Empty Value" can be ignored. It is used to set the return information when an error value appears.
3. ** Instance **: Search for the corresponding assessment score, grade, and department information in the table on the right according to the data in the table on the left. If the name is in column A, and the assessment results, grade, and department information are in column B:D, enter the formula in the target cell: = Filterer (B:D,A:A=F3)(Assuming the name is in cell F3), and then enter.

** 3. TEXTSPILL Function **
1. ** Function **: Use the Separator to split the text into rows and columns.
2. ** Syntactic structure **:=TEXtsplit (text to be split, split by column, split by row, ignore empty cells, case-sensitive, exception return value). The parameters "Text to be split" refer to the object to be split;"Split by column" refers to the separation character used to split the text into rows and display it;"Split by row" refers to the separation character used to split the text into columns and display it;"Ignore empty cells" refers to ignoring empty values after splitting;"Match case" refers to "exception return value" refers to the return result of splitting exceptions.
3. ** Instance **: If the customer name on the left is in cell B3 and is separated by commas, and you want to split the text into lines by using the separating character, enter the formula in the target cell: = TEXTSPILL (B3,","), then enter and fill in the fields.

** 4. TEXTJoin Function **
1. ** Function **: A text link function that links lists or text string areas with a terminator.
2. ** Syntactic structure **:=TEXTJoin(Separator, ignore blank cells, string 1…). The "Separator" is a text string, or empty, or one or more characters enclosed in double quote marks, or a reference to a valid text string;"Ignore blank cells" is TRUE to ignore blank cells, and False to not ignore them;"String 1..." is 1 to 253 text items to be joined, which can be a text string or a string array (such as a cell area).
3. ** Instance **: To combine all the names in the A2:A8 cell area and separate them with commas, enter the formula in the target cell: =TEXTJoin(",",True,A2:A8), and then enter.

** 5. IFS Function **
1. ** Function **: Check whether one or more conditions are met and return the value corresponding to the first True condition. It can replace multiple nesting IFs.
2. ** Syntactic structure **:=IFS(Test Condition 1, Truth Value 1, Test Condition 2, Truth Value 2…Test Condition N, Truth Value N).
3. ** Instance **: According to the assessment results, the employees are divided into assessment grades. Assuming that the assessment results are in cell B67, if the assessment results are greater than or equal to 90, the grade is Good; if the assessment results are greater than or equal to 80 and less than 90, the grade is Excellent; if the assessment results are greater than or equal to 60 and less than 80, the grade is Pass; if the assessment results are less than 60, the grade is Failed. Enter the formula in the target cell: =IFS(B67<60,"Failed", B67<80,"Passed",B67<90,"Excellent",B67>=90,"Good"), then enter.

** 6. SUM Function **
1. [** Function **: Mainly used for simple and unconditional sum, calculating the sum of all values in the cell area.]
2. ** Syntactic structure **:=SUM(Number1,[Number2],...).
3. ** Instance **: To sum the salaries of all employees in the C4:C12 cells, enter the formula: =SUM(C4:C12) in the target cell, and then enter.

** 7. Sumifs Function **
1. ** Function **: Multi-condition sum function.
2. ** Syntactic structure **:=SUMIFS(Summation Region, Condition Region 1, Condition 1, Condition Region 2, Condition 2…).

Excel function input method:
1. ** Use the [Automatic Summon] button to insert a function **: First select a cell (such as cell B7), then switch to the [Formula tab], click the [Automatic Summon] button, and finally press the enter key to end the process. For example, calculating the sales amount of all the salespeople.
2. ** Using the function library to insert a function **: click the [Common Function] button on the [Formula] tab, select a sum formula (such as SUM), and the [Function parameters] dialog box will pop up. Then, click the left mouse button to select the corresponding cell area (such as the cell area from B2 to B6), and press the enter key to end the process.
3. ** Manual input of function formula **: This is a common method. For example, if you directly enter a function in a cell, the function must start with the equal sign "=". If you know the function you want to use, you can enter "=" and then enter the first letter of the function (such as "S"). The function starting with S will be listed below the cell. If the function you want to use does not appear, continue to enter the second letter (such as "U"). After the function you want to use appears, double-click the function with the mouse and it will automatically enter the cell.

"Choose" was equally exciting. Everyone was welcome to read it!