Summary of all formulas of Excel functions
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!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Tips for dynamically creating new functions in golang functions
Apr 25, 2024 pm 02:39 PM
Go language provides two dynamic function creation technologies: closure and reflection. closures allow access to variables within the closure scope, and reflection can create new functions using the FuncOf function. These technologies are useful in customizing HTTP routers, implementing highly customizable systems, and building pluggable components.
Considerations for parameter order in C++ function naming
Apr 24, 2024 pm 04:21 PM
In C++ function naming, it is crucial to consider parameter order to improve readability, reduce errors, and facilitate refactoring. Common parameter order conventions include: action-object, object-action, semantic meaning, and standard library compliance. The optimal order depends on the purpose of the function, parameter types, potential confusion, and language conventions.
How to write efficient and maintainable functions in Java?
Apr 24, 2024 am 11:33 AM
The key to writing efficient and maintainable Java functions is: keep it simple. Use meaningful naming. Handle special situations. Use appropriate visibility.
Complete collection of excel function formulas
May 07, 2024 pm 12:04 PM
1. The SUM function is used to sum the numbers in a column or a group of cells, for example: =SUM(A1:J10). 2. The AVERAGE function is used to calculate the average of the numbers in a column or a group of cells, for example: =AVERAGE(A1:A10). 3. COUNT function, used to count the number of numbers or text in a column or a group of cells, for example: =COUNT(A1:A10) 4. IF function, used to make logical judgments based on specified conditions and return the corresponding result.
Comparison of the advantages and disadvantages of C++ function default parameters and variable parameters
Apr 21, 2024 am 10:21 AM
The advantages of default parameters in C++ functions include simplifying calls, enhancing readability, and avoiding errors. The disadvantages are limited flexibility and naming restrictions. Advantages of variadic parameters include unlimited flexibility and dynamic binding. Disadvantages include greater complexity, implicit type conversions, and difficulty in debugging.
What are the benefits of C++ functions returning reference types?
Apr 20, 2024 pm 09:12 PM
The benefits of functions returning reference types in C++ include: Performance improvements: Passing by reference avoids object copying, thus saving memory and time. Direct modification: The caller can directly modify the returned reference object without reassigning it. Code simplicity: Passing by reference simplifies the code and requires no additional assignment operations.
What is the difference between custom PHP functions and predefined functions?
Apr 22, 2024 pm 02:21 PM
The difference between custom PHP functions and predefined functions is: Scope: Custom functions are limited to the scope of their definition, while predefined functions are accessible throughout the script. How to define: Custom functions are defined using the function keyword, while predefined functions are defined by the PHP kernel. Parameter passing: Custom functions receive parameters, while predefined functions may not require parameters. Extensibility: Custom functions can be created as needed, while predefined functions are built-in and cannot be modified.
C++ Function Exception Advanced: Customized Error Handling
May 01, 2024 pm 06:39 PM
Exception handling in C++ can be enhanced through custom exception classes that provide specific error messages, contextual information, and perform custom actions based on the error type. Define an exception class inherited from std::exception to provide specific error information. Use the throw keyword to throw a custom exception. Use dynamic_cast in a try-catch block to convert the caught exception to a custom exception type. In the actual case, the open_file function throws a FileNotFoundException exception. Catching and handling the exception can provide a more specific error message.
