excel, useful formulas, how to use excel formulas

Extract the year with the formula in sheet2 and then count it, =if(sheet1!d2="","",if(iserror(--right(left(sheet1!d2,find("$",substitute(sheet1!

d2,",","$",2))-1),4)),"",--right(left(sheet1!d2,find("$",substitute(sheet1!d2,",","$",2))-1),4)))

1. The computer opens the electronic **, understands the degree, and then enters the formula.

2.Select the cell input in the formula, press the Ctrl key to copy it, and paste the Ctrl key into the cell next to it.

3.Or select the cell with the formula and drag down to copy the formula.

Commonly used or not, it has to do with everyone's occupation and use.

Excel functions (listed by category).

It is determined based on how often you actually use it.

First, a preliminary understanding of excel formulas.

1.What is a formula.

As mentioned earlier, we listed "Formula" as a third data type in addition to "Numeric" and "Text". The common feature of formulas is that they start with a "=" sign, and they can be simple mathematical formulas or formulas that contain various excel functions.

2.What elements the formula consists of.

Formulas entered into cells start with an equal sign, followed by the following five elements:

Operator: e.g. "+" or "*".

Cell Reference: It includes a single cell or a range of multiple cells, as well as a named range of cells. These cells or ranges can be in the same worksheet, in other worksheets in the same workbook, or even in other workbook worksheets.

Numeric or textual: The two types of data described earlier. For example, "100" or "Xinyue International Education Group".

Worksheet function: It can be a built-in function in Excel, such as sum or max, or a custom function.

Parentheses: "("and")" They are used to control the priority of each expression in the formula.

Understanding what a formula consists of is a necessary foundation for creating a formula. In addition, formulas in excel sheets can consist of up to 1024 characters, and the formulas we create every day are very few that exceed 100 characters, so this length is enough for us to use.

2. What can the formula do?

To put it simply, we build a formula and provide it with relevant data information, with the aim of hoping that the formula will give us an answer or a calculation result. From this point of view, excel formulas function the same as the formulas we learned in mathematics. Therefore, in the future work, we can see that many formulas are well-known mathematical formulas.

In addition to that, since the formulas of excel can also contain various functions of excel. Therefore, excel formulas can perform many special tasks in addition to the calculation functions that traditional mathematical formulas have. For example, in combination with the if function, the formula can be selective, and different results can be obtained according to different conditions.

In the following practical task of "Calculating Performance Bonus", we will introduce the problem of nesting the if function in the formula in detail.

There are more than 300 built-in functions in Excel, and the flexible use of these functions in formulas can greatly improve the ability of formulas to solve problems and easily handle various complex tasks.

For example, the three formulas shown in the figure below are examples: the first formula automatically calculates the product of the unit price and the quantity to get the amount, the second formula automatically calculates the sum of all sales, and the third formula combines the first and last names together to get the full name.

Let's take a look at some simple concrete examples to strengthen our perceptual understanding of the formula.

Commonly used formulas are:

1. Fuzzy query.

Formula: =vlookup(f3,$b$2:$d$10,3,0).

The so-called fuzzy query is to query data through keywords, we need to use wildcards to achieve similar operations, and the commonly used wildcards in excel have 2 * signs: representing any number of characters? No.:

Represents any single characterNow we want to find the position of [Di Renjie] through the word [Di Ren], we need to construct the search value as [Di Ren? Then use the vlookup function to query directly.

2. Multi-condition query.

Formula: =lookup(1,0 ($a$2:$a$11=e2)*(b$2:$b$11=f2),$c$2:$c$11).

There are many ways to solve multi-condition queries, I think the most practical way is to use the lookup function, we just need to remember that its search format can be directly applied to the format: =lookup(1,0 (condition 1 * condition 2 * condition 3), the result column that needs to be returned), and multiply several by several conditions.

3. The formula for extracting the date of birth of the ID number: =text(mid(a2,7,8),"0-00-00"Here the mid function is used to extract the date of birth in the ID number, and the text function is used to set the display format of the date.

4. The formula for calculating the age of ID number: =datedif(b2,today(),y"This step is based on the fact that you extract the date, datedif is used to calculate the difference of the date, the today function is used to get today's date, and the letter y represents the difference of the calculated year.