Workers who use Excel often need to use various function formulas. Today I will bring some commonly used Excel function formulas. Accumulating it can help you improve your work efficiency.
First, how to find duplicate content?
=IF(COUNTIF(A:A,A2)>1,"Repeat","")
If the result of the COIUNTIF function is greater than 1. That means there is duplicate content.
Second, how to calculate the age based on the date of birth?
=DATEDIF(B2,TODAY(),"y")
The TODAY function returns the current date of the system. B2 is listed as the date of birth. The user can modify it according to the actual situation.
Third, how to extract the date of birth through the ID number?
=--TEXT(MID(B2,7,8),"0-00-00")
The B2 column refers to the ID card number column. 7 means starting from the 7th position in cell B2. Extract 8 characters representing the birth year and month. At the same time use the text function to turn it into date style text. If the cell of birth year and month shows a 5-digit value. The user only needs to set it as a date format.
Fourth, how to get the week based on the date?
=TEXT(A3,"AAAA")
The TEXT function can convert a value to a specified number format.
Fifth, how to count whether it is qualified?
Assume that more than 8 digits are qualified. Otherwise, it is unqualified. The formula is as follows:
=IF(B3>=8,"Qualified","Unqualified")
Sixth, how to compare two columns of different data?
=IF(A3=B3,"same","different")
Seventh, how to quickly call up information by name?
=VLOOKUP(E4,A2:C10,2,0)
Among them, E4 is the recall position. A2 is the name column. C10 is the department. Through this function. We can quickly find the corresponding employee's information by name.
Eighth, calculate the number of working days between two dates?
=NETWORKDAYS(start date. end date. [holiday])
For example: =NETWORKDAYS(B2,EOMONTH(B2,0))
EOMONTH(B2,0) is to calculate the last day of the specified date.
The IFERROR function is basically used when masking error values. Commonly used wording is: IFERROR. It refers to the content to be returned when the result of the formula is an error value.
The AND function is used to determine whether multiple conditions are met. Commonly used writing is: =IF. It is AND (B2="Yes", C2="Yes", D2="Yes"), "spare tires are preferred", "not considered"
VLOOKUP function is commonly written as: VLOOKUP. It is looking for whom and where. It returns the contents of the first few columns. And the matching method.
The MID function starts at a specified position in a cell. Extract characters of the specified length. Commonly used wording is: MID. It is the character to be processed, where does it start. And intercept a few characters.
Commonly used writing of FREQUENCY function: FREQUENCY. It is the data area to be counted. And specify the interval points of different intervals.
To sum it up, we only need to remember 7 functions. They are IFERROR, AND, VLOOKUP, MID, FREQUENCY, AVERAGEIF, AVERAGEIFS. There are many combinations of functions in Excel. But everything changes. We only need to remember the common writing of these 7 functions. Basically, it can meet the function formulas that Excel needs in the use process.
