1. Number processing
1. Obtain the absolute value function
Formula: =ABS(number)
2. Rounding function
Formula: =INT(number)
3. Rounding function
Formula: =ROUND(number, decimal places)
2. Judgment formula
1. If the calculated result value is wrong, it will be displayed as empty
Formula: =IFERROR(number/number,)
Explanation: If If the calculated result is wrong, it will be displayed as empty, otherwise it will be displayed normally.
2. Multi-condition judgment and return value of IF statement
Formula: IF (AND (cell (logical operator) value, specified cell = return value 1), return Value 2,)
Explanation: Use AND when all conditions are true at the same time, and use the OR function if any one is true.
3. Commonly used statistical formulas
1. Statistics of the same content in two tables
Formula: B2=COUNTIF(data source: location, specified , target location)
Note: If the return value is greater than 0, it means it exists in another table, 0 means it does not exist.
If, the formula used in this example is: B2=COUNTIF(Sheet15!A:A,A2)
2. Count the total data without repetition
Formula: C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
Instructions: Use the COUNTIF function to count the number of occurrences of each person in the source data, and divide by 1 Convert them into fractions and add them up at the end.
4. Data summation formula
1. Application of summation in alternate columns
Formula: H3=SUMIF($A$2:$G$2,H $2,A3:G3) or =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
Note: If there is no rule in the title line, you can use the first 2 formulas
2. Summation of single condition application
Formula: F2=SUMIF(A:A,C:C)
Explanation: This is The most basic usage of the SUMIF function
5. Search and reference formulas
1. Single condition search
Explanation: VLOOKUP is the most commonly used search method in excel
6. String processing formula
1. Merging multi-cell strings
Note: The Phonetic function can only merge character data, not numerical values.
2. Cut off the part other than 3 digits of the result
Explanation: LEN calculates the total length, LEFT cuts the total length from the left to -3 digits
7. Date Calculation related
1. Calculation of years, months and days between dates
A2 is the start date (2011-12-2), B2 is the end date (2013-6-11 ). Calculation:
The formula for the difference in days is: =datedif(A2,B2,d) The result: 557
The formula for the difference in months is: =datedif(A2,B2, m) The result: 18
The formula for the number of years apart is: =datedif(A2,B2,Y) The result: 1
The formula for not considering the number of months between years is: =datedif(A1,B1,Ym) The result is: 6
The formula that does not consider the number of days between years is: =datedif(A1,B1,YD) The result is: 192
The formula that does not consider the number of days between year and month is: =datedif(A1,B1,MD) The result is: 9
Description of the third parameter of the datedif function:
Y time period of whole years.
M The number of whole months in the time period.
D Number of days in the time period.
MD The difference in days in the date. Month and year are ignored.
YM The difference in months in the date. Day and year are ignored.
YD The difference in days in the date. Ignore the year.
Extended information:
Engineering function
BESSELI returns the modified Bessel function IN(X)
BESSELJ returns Bessel Er function JN(X)
BESSELK returns the modified Bessel function KN(X)
BESSELY returns the Bessel function YN(X)
XLFCTBIN2DEC, BIN2DEC Convert binary numbers to decimal numbers
BIN2HEX Convert binary numbers to hexadecimal numbers
BIN2OCT Convert binary numbers to octal numbers
COMPLEX Converts real and imaginary coefficients to complex numbers
CONVERT Converts a number in one system of measurement to another
DEC2BIN Converts a decimal number to a binary number
DEC2HEX Converts a decimal number to a hexadecimal number
DEC2OCT Converts a decimal number to an octal number
DELTA Checks whether two values ??are equal
ERF returns the error function
ERFC returns the residual error function
GESTEP detects whether the number is greater than a certain threshold
HEX2BIN converts hexadecimal numbers for binary numbers
HEX2DEC Converts a hexadecimal number to a decimal number
HEX2OCT Converts a hexadecimal number to an octal number
IMABS Returns a complex number Absolute value (modulo)
IMAGINARY returns the imaginary coefficient of a complex number
IMARGUMENT returns the parameter THETA, an angle expressed in radians
IMCONJUGATE returns the *** of a complex number Yoke complex numbers
IMCOS Returns the cosine of a complex number
IMDIV Returns the quotient of two complex numbers
IMEXP Returns the exponent of a complex number
IMLN Returns a complex number The natural logarithm of
IMLOG10 Returns the common logarithm of a complex number
IMLOG2 Returns the base-2 logarithm of a complex number
IMPOWER Returns the integer power of a complex number< /p>
IMPRODUCT Returns the product of two complex numbers
IMREAL Returns the real coefficient of a complex number
IMSIN Returns the sine of a complex number
IMSQRT Returns the square root of a complex number
IMSUB returns the difference of two complex numbers
IMSUM returns the sum of two complex numbers
OCT2BIN Converts an octal number to a binary number
OCT2DEC converts an octal number to a decimal number
OCT2HEX converts an octal number to a hexadecimal number
Reference:
Baidu Encyclopedia - excel function
p>