1. Number processing
1. Take absolute value =ABS(number)
2. Rounding=INT (number)
3. Rounding =ROUND (number, decimal places)
2. Judgment formula
1. Errors caused by the formula The value is displayed as empty
Formula: C2=IFERROR(A2/B2,"")
Description: If it is an error value, it is displayed as empty, otherwise it is displayed normally.
2. IF multi-condition judgment return value formula:
C2=IF(AND(A2
Explanation: Use AND if two conditions are true at the same time, or use the OR function if either condition is true.
3. Statistical formula
1. Statistics of repeated content in two tables
Formula: B2= COUNTIF(Sheet15!A:A,A2)
Note: If the return value is greater than 0, it means it exists in another table, and 0 means it does not exist.
2. Count the total number of non-duplicate people
Formula: C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
Instructions: Use COUNTIF to count the number of occurrences of each person, use division by 1 to convert the number of occurrences into the denominator, and then add them up.
Related recommendations: "excel basic tutorial"
4. Summation formula
1. Sum every other column
Formula: H3=SUMIF($A$2:$G$2,H$2,A3:G3)
or=SUMPRODUCT((MOD(COLUMN( B3:G3),2)=0)*B3:G3)
Explanation: If there is no rule in the title row, use the second formula.
2. Single condition summation
Formula: F2=SUMIF(A:A,E2,C:C)
Explanation : Basic usage of SUMIF function.
3. Single-condition fuzzy sum
Formula: See the figure below for details
Instructions: If you need to perform fuzzy sum, you need to master the use of wildcards, among which the asterisk It means any number of characters. For example, "*A*" means there are any number of characters before and after a, including A.
4. Multi-condition fuzzy summation
Formula: C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7 ,B11)
Note: You can use the wildcard character *
in sumifs. 5. Summing the same positions in multiple tables
Formula: b2 =SUM(Sheet1:Sheet19!B2)
Note: After deleting or adding a table in the middle of the table, the formula results will be automatically updated.
6. Sum by date and product
Formula: F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1) *($B$2: $B$25=$E2) *$C$2:$C$25)
Explanation: SUMPRODUCT can complete multi-condition summation.
5. Search and reference formulas
1. Single condition search formula
Formula 1: C11= VLOOKUP(B11,B3:F7,4,FALSE)
Description: Search is what VLOOKUP is best at, basic usage.
2. Bidirectional search formula
Formula=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2 :H2,0))
Explanation: Use the MATCH function to find the position and the INDEX function to get the value.
3. Find the last record that meets the conditions
Formula: See the figure below for details
Explanation: 0/(condition) can turn those that do not meet the conditions into error values , and lookup can ignore error values.
4. Multi-condition search
Formula: See the picture below for details
Explanation: The formula principle is the same as the previous formula
5. Find the last non-null value in the specified area
formula; see the figure below for details
Instructions: omitted
6. Get the corresponding value from the numerical range
Formula: See the figure below for details
Formula description: Both VLOOKUP and LOOKUP functions can be pressed When selecting interval values, it must be noted that the numbers in the sales column must be arranged in ascending order.
6. String processing formula
1. Multi-cell string merging
Formula: c2 =PHONETIC(A2:A7)
Explanation: The Phonetic function can only merge character content, not numbers.
2. Cut off the part except the last 3 digits
Formula: =LEFT(D1,LEN(D1)-3)
Instructions: LEN calculates the total length, LEFT cuts the total length from the left to -3
3. Cut off the part before -
Formula: B2 =Left(A1,FIND("-",A1)-1)
Explanation: Use the FIND function to find the position and use LEFT to intercept.
4. Formula to intercept any segment of the string
Formula: B1=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20 ))
Explanation: The formula is intercepted by forcibly inserting N null characters.
5. String search
Formula: B2=IF(COUNT(FIND("Henan",A2))=0,"No", "Yes")
Explanation: FIND returns the position of the character if the search is successful, otherwise an error value is returned, and COUNT can count the number of numbers, which can be used to determine whether the search is successful.
6. String search one-to-many
Formula: B2
=IF(COUNT(FIND({"Liaoning", "Heilongjiang","Jilin"},A2))=0,"Other","Northeast")
Instructions: Set the first parameter of FIND to a constant array, and use the COUNT function to count the FIND search results.
7. Date calculation formula
1. Calculation of the number of years, months and days between two dates
A1 is the start date (2011-12-1), and B1 is the end date (2013-6-10).
Calculation:
How many days apart? =datedif(A1,B1,"d") Result: 557
How many months apart? =datedif(A1,B1,"m") Result: 18
How many years apart? =datedif (A1,B1,"Y") Result: 1
Regardless of how many months are between years? =datedif(A1,B1,"Ym") Result: 6
How many days are there between years without considering it? =datedif(A1,B1,"YD") Result: 192
How many days are between the year and the month without considering it? =datedif(A1,B1,"MD") Result: 9
The third parameter description of the datedif function:
"Y" The number of whole years in the time period.
"M" The number of whole months in the time period.
"D" The number of days in the time period.
"MD" The difference in days. Ignore month and year in dates.
"YM" The difference in months. Ignore the day and year in the date.
"YD" The difference in days. Ignore the year in the date.
2. Number of working days after deducting weekend days
Formula: C2=NETWORKDAYS.INTL(IF(B2 Description: Returns all working days between two dates, use parameters to indicate which Which days are weekends and how many days are weekends. Weekends and any days designated as holidays are not considered working days. 
The above is the detailed content of Summary of all formulas of Excel functions. For more information, please follow other related articles on the PHP Chinese website!
