Practical Excel skills sharing: 10 most commonly used formulas among professionals

This article has compiled 10 of the most commonly used excel formulas for professionals. I hope it can help you solve your problems. Come and take a look!

##Nope It is really inefficient to use formula functions to work, but there are so many formula functions that it is impossible to learn them all at once. This is a dilemma faced by many professionals.

Today I have summarized ten frequently used formulas to share with you. I believe that if you learn these ten formulas, you can also be successful in the workplace.

Formula 1: Conditional Counting

Conditional counting is very common in Excel applications. For example, the number of women in the statistical list of personnel is the condition. Typical representation of counting.

Conditional counting requires the use of the COUNTIF function. The function structure is

=COUNTIF (statistical area, condition). In this example, the first formula =COUNTIF(B:B,G2), B:B is the statistical area, G2 is the condition, and the formula result indicates that there are 14 "female" data in column B.

The second parameter condition can not use cell references, but directly use the specific content as the condition. When the condition is text, you need to add English double quotes on both sides of the condition, such as the second formula

= COUNTIF(B:B,"女")That's it.

Formula 2: Quickly mark duplicate data

In daily work, we often encounter the problem of marking duplicate values, for example, in a document In the sales detail table, mark the duplicate salesperson names.

First use the formula

=COUNTIF(A:A,A2) to calculate the number of times each name appears. When the result is greater than 1, it means that the name is repeated. , and then use the IF function to get the final result.

The formula is:

=IF(COUNTIF(A:A,A2)=1,"","Repeat"), the result is as shown in the figure.

There is another way to determine whether a name is repeated: the first occurrence is not considered a repetition, and only the second occurrence is considered a repetition.

When encountering this situation, you only need to modify the condition area of ​​the COUNTIF function. The formula is:

=IF(COUNTIF($A$1:A2,A2)=1,"","Repeat "), the result is as shown in the figure.

Formula 3: Multi-condition counting

If you want to count multiple conditions, you must Use the COUNTIFS function, for example, you need to count the number of men with a bachelor's degree in secondary school.

The function structure of COUNTIFS is =COUNTIFS(data area 1, condition 1, data area 2, condition 2,...), and there can be up to 127 sets of conditions.

In the first formula of this example =COUNTIFS(B:B,G2,C:C,G3), there are two sets of conditions. Column B is used to judge gender, and column C is used to judge educational background. .

Similarly, conditions can refer to cells or use specific content directly, which is exactly the same as the COUNTIF function.

Formula 4: Conditional summation

In addition to conditional counting, conditional summation is also widely used, such as statistics in sales details Total TV sales.

The SUMIF function needs to be used when summing by conditions. The structure of the function is =SUMIF (conditional area, condition, summation area). In this example, the conditional area It is column B, and the summing area is column C. The conditions can use cells or specific content directly. To calculate the total of TV sales, there are two ways to write: =SUMIF(B:B,"TV",C:C ) and

=SUMIF(B:B,B2,C:C).

Formula 5: Multi-condition summation

If there is a conditional summation, there will be a multi-condition summation, for example, based on salesperson and product When summing two conditions, the multi-condition summation function SUMIFS is used.

The structure of the SUMIFS function is =SUMIFS (summation area, condition area 1, condition 1, condition area 2, condition 2,...), in this example, The summation area is the sales quantity in column D, the first condition area is the salesperson in column B, and the second condition area is the product name in column C, so the final formula is:

= SUMIFS(D:D,B:B,"Shen Yijie",C:C,"Wall-mounted air conditioner")

It should be reminded that the location of the summation area of ​​SUMIFS is different from that of SUMIF. The summation area of ​​SUMIFS is in the first parameter, while the summation area of ​​SUMIF is in the third parameter. Don't get confused!

Formula 6: Calculate the date of birth based on the ID number

To get the date of birth from the ID number, this problem is very difficult for people who work Friends who are in administrative positions must be familiar with it, and the formula is relatively simple:

=TEXT(MID(A2,7,8),"0-00-00") to get the required results, as shown in the figure Shown:

To understand the principle of this formula, you must first know some rules in ID numbers. The ID cards currently used are basically 18 digits. The eight digits starting with the seven digits represent the date of birth.

This formula involves two functions. First, let’s look at the MID function. The MID function has three parameters. The format is: =MID (where to extract, from which word to start, how many words to pick) .

MID(A2,7,8) means to intercept eight digits starting from the seventh number of cell A2. The effect is as shown in the figure:

After the birth date is extracted, it is not the effect we need. At this time, the function magician TEXT comes into play. The TEXT function has only two parameters, the format is =TEXT (the content to be processed, "in what format to display"), in this example The content to be processed is the MID function part. The display format is "0-00-00". Of course, it is no problem if you use the format of "0年00月00日". The formula is changed to =TEXT(MID(A2 ,7,8),"0年00月00日") will do:

##Formula 7: Calculate age based on ID number

With the date of birth, of course you will think of calculating age. The formula is: =DATEDIF(B2,TODAY(),"Y")

An Excel hidden function DATEDIF is used here. The function requires three parameters, and the basic structure is =DATEDIF (start date, deadline, calculation method).

The starting date in this example is the date of birth, using B2 as the first parameter; the deadline is today, using the TODAY() function as the second parameter; the calculation method is calculated by year, using "Y" as the third parameter.

If you need to calculate age directly from the ID number, the formula can be written as:

=DATEDIF(TEXT(MID(A2,7,8),"0-00-00") ,TODAY(),"Y")

Formula 8: Different results are obtained according to the interval

This type of problem is often seen in performance appraisals. For example, when a company conducts performance appraisals for employees, it needs to determine the reward level based on the appraisal results. The grading rules are: E for scores below 50, D for 50-65 (inclusive), and D for 65-75 (inclusive). ) is C, 75-90 (inclusive) is B, and above 90 is A.

You can use the formula =LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"}) to get each The reward level of each employee, the result is as shown in the figure:

It is difficult to explain the principle of this formula. You can refer to the previous tutorial on the LOOKUP function.

In fact, to solve this kind of problem, it is enough to remember the routine: The routine for LOOKUP to return the corresponding results by interval is =LOOKUP(score, {list of lower limit values}, {list of reward levels}), lower limit value They are separated by semicolons, and the reward levels are also separated by semicolons.

You can also enter the corresponding relationship between the lower limit of the score and the reward level in the table. The formula can be modified to =LOOKUP(E2,$I$2:$J$6), and the result is as shown in the figure.

Formula 9: Single condition matching data

If you want to dominate the workplace, what can you do if you don’t know how to match? ? How can I do single condition matching without VLOOKUP?

The basic structure of the VLOOKUP function is =VLOOKUP (what to look for, where to look for it, which column to look for, how to look for it). For example, to find the highest educational level by name, you can use the formula =VLOOKUP(G2,B:E,4 ,0) Get the desired result, as shown in the figure:

There are two key points that you must know when using this function:

①What you are looking for It must be in the first column of the search range. For example, when searching by name, the search range starts from column B instead of column A.

②The column number refers to the column in the search range rather than the column in the table. For example, if you want to find the highest educational level, you should find the 4th column in the search range, not the column number 5 in the table.

Formula 10: Multi-condition matching data

If you learn to match data with multiple conditions, you will be truly invincible!

Give an example of matching sales quantity by name and product name, as shown in the figure:

The formula is =LOOKUP( 1,0/(($A$2:$A$10=E2)*($B$2:$B$10=F2)),$C$2:$C$10)

Use the LOOKUP function The routine for multi-condition matching is: =LOOKUP(1,0/((search range 1=lookup value 1)*(search range 2=lookup value 2)*……*(search range n=lookup value n)), Result range), it should be noted that there is a multiplication relationship between multiple search conditions, and they need to be placed in the same set of brackets as the denominator of 0/.

Okay, the ten most commonly used formulas are shared here. If you use them well, you can really dominate the workplace!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: 10 most commonly used formulas among professionals. For more information, please follow other related articles on the PHP Chinese website!