16 Excel function Formulas Do you understand?

To facilitate daily work, the following commonly used 16 Excel function formulas are collected.

1, find the duplicate content formula:

=if (COUNTIF (A:A,A2) >1, "Repeat", "")

2. Find duplicate ID number formula:

=if (COUNTIF (a:a,a2& "*") >1, "Repeat", "" ")

3, the date of birth to calculate the age formula:

=datedif (A2,today (), "Y")

4, according to the ID number of the date of birth formula:

=--text (MID (a2,7,8), "0!/00!/00")

5, according to the identity card number of the extraction of gender formula:

=if (MOD (a2,15,3), 2, "male", "female")

6. Calculate the retirement time according to the ID number:

=edate (TEXT (mid a2,7,8), "0!/00!/00"), MOD (Mid (a2,15,3), 2) *120+600)

7. Calculate the contract expiration formula:

=edate (a4,3)

8, the condition sum.

Calculate the total sales of a sales department:

=SUMIF (B:B, "sales department", C:C)

9, multiple conditions sum.

Calculate sales of more than 1000:

=sumifs (C:C,B:B, "sales department", C:c, ">1000")

10, calculate a certain date is the first few quarters:

=len (2^month (A2)) & "Quarter"

11, specify the minimum value of the condition:

{=min (IF (b2:b10= "Sales", C2:C10))}

Note: The formula here has a pair of curly braces on the outermost layer. This is the legend of the array formula, you need to press and hold Shift+ctrl, press ENTER. The curly braces are automatically added, but the manual addition is not valid OH.

12, specify the maximum value of the condition:

{=max (IF (b2:b10= "Sales", C2:C10))}

This formula is similar to the one used for the first formula, except that the Min function that calculates the minimum value is replaced by the Max function, which calculates the maximum value.

13, the average of the conditions specified:

=averageif (B2:B10, "sales department", C2:C10)

The grammar of the AverageIf function is converted to Mandarin in general:

=averageif (condition range, specified condition, range that calculates the average)

The third parameter can be ignored, such as using the following formula:

=averageif (C2:C10, ">950")

is used to calculate the average of sales over 950.

14, multiple conditions count:

=countifs (B2:B10, "sales department", C2:C10, ">950")

The grammar of the COUNTIFS function is converted to Mandarin in general:

=countifs (Region 1, Condition 1, area 2, Condition 2 ...) Area N, Condition N)

In addition, you can use the Sumproduct function to calculate a multiple-condition count:

=sumproduct ((b2:b10= "Sales Department") * (c2:c10>950))

With the Sumproduct function of multiple conditional counting grammar, the meaning of Mandarin is roughly:

=sumproduct ((area 1= condition 1) * (Region 2= Condition 2) * (area n= condition N))

15, multiple conditions sum:

In the previous section, we say the sumifs function of the sum of the conditions, if you use the Sumproduct function to sum the multiple conditions, the syntax is:

=sumproduct (Region 1= condition 1) * (Region 2= Condition 2) * Summation region)

16, with the VLOOKUP function query data:

=vlookup (c14,b1:c10,2,)

If you change the grammar of the VLOOKUP function to Mandarin, it means:

=vlookup (value of query, range, return contents of column, match type)

The VLOOKUP function is one of the most frequently used functions, and is often used in daily query applications. Here are a few questions to note:

① The first column of the second parameter range must contain the query value.

② The third parameter is the first column of the data range, not the first column of the worksheet.

③ If the fourth argument is omitted, the VLOOKUP function looks for a fuzzy match, but requires the data source region to be sorted in ascending order.