Can a teacher of excel conditional statistical functions help me explain what the following functions mean? Thank you.

Excel conditional statistical function teacher can help me explain what the following function means. Thank you

1. Two English double quotes "" represent blank characters;

Next, let’s introduce a very useful function-SUBSTITUTE(B2, ";", ""). This function can replace the English semicolon ";" in cell B2 with a null character to achieve the replacement effect. This function is very simple and easy to use, and is very useful when you need to replace specific characters. By using the SUBSTITUTE function, we can easily implement character replacement operations, making data processing more convenient and efficient.

3. SUBSTITUTE(SUBSTITUTE(B2,";",""),";","") is to replace the value of the result of SUBSTITUTE(B2,";","") again, and replace the Chinese points The sign ";" is also replaced.

4. LEN(B2) is to calculate the number of value characters in cell B2.

LEN(SUBSTITUTE(SUBSTITUTE(B2, ";", ""), ";", "")) function is used to calculate the number of characters after the semicolon is replaced in cell B2.

The subtraction of these two numbers is the number of Chinese and English semicolon ";" and ";" characters. Right now:

LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,";",""),";",""))

5. Finally execute the IF function. If the number of English semicolons LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,";",""),";","")) in B2 is not zero, then display the number LEN(B2 )-LEN(SUBSTITUTE(SUBSTITUTE(B2,";",""),";","")), otherwise "" will not be displayed. The formula is:

=IF(LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,";",""),";","")),LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2," ;",""),";","")),"")

How to count functions based on multiple conditions in excel

Enter the function in cell e2 of the red box area, which will return the number of times this person appears on the corresponding date

The multiplication of two equations means that two conditions are met at the same time. The function will count the number of true in an array composed of true and false, that is, the number of colleagues who meet the condition

Right-click the cell, click Copy in the pop-up menu, then select the large box area, Paste Special, formula

If you use the sum function, you can also achieve the same effect, but after completing the formula input, you need to press ctrl shift and press Enter to get the return value

After pressing ctrl shift and pressing Enter, you can see that the formula in the edit bar has a pair of curly brackets, which is the symbol of the array formula

Conditional statistics can also be achieved using the dcount function, but you need to manually set a conditional area as the third parameter

Or through automatic filtering, then select the data area in column b, then locate the visible cells, and observe the count in the lower right corner to get the same result

Note: When using dcount, the second parameter can specify the cell in the upper left corner of the condition area

The above is the detailed content of Can a teacher of excel conditional statistical functions help me explain what the following functions mean? Thank you.. For more information, please follow other related articles on the PHP Chinese website!