skills in Excel that many people may not be able to master?
Contents
skills in Excel that many people may not be able to master?
1. VLOOKUP, the king of Excel efficiency
VLOOKUP is a must-learn point that every Excel user cannot avoid, but most (more than 90%) students can’t learn how to learn it.
First, let’s take a look at the basic usage of VLOOKUP:
2. IF function
The IF function is the backbone of the Excel logic family. As long as it is a logical judgment, it is inseparable from the IF function. First, let’s take a look at the syntax of the IF function:
The IF function is very simple, and almost everyone who has contacted Excel can understand it. However, in the operation, it is often necessary to nest IF or various functions for judgment. The IF of a single condition appears less often, so how does the IF nest? What about the suit?
If: the average score is over 90 as excellent, over 60 as qualified, less than 60 as unqualified .
Let’s first look at how to split the logic:
- First, judge whether the average score is greater than or equal to 80, if yes, it is excellent, end, otherwise enter 2;
- Judge whether the average score is greater than or equal to 60, if yes, it is qualified, end, otherwise go to 3;
- Fail, end.
So how to express it in Excel? Let’s write the first layer first:
=IF(D2>=80, “Excellent”, to be judged)
The above formula only judges the cases of greater than or equal to 80 points, and there are two cases of less than 80 points: qualified and unqualified, and an additional layer of IF can be nested in the place to be judged:
To be judged =IF(D2>=60,”qualified”,”unqualified”)
Nested entry gets the full formula:
=IF(D2>=80,”Excellent”,IF(D2>=60,”Qualified”,”Unqualified”))
The result is as follows:
If the logic is complex enough, the number of IF nesting levels will increase, which is extremely inconvenient to read, so how can we make the code easier to read?
It can be seen that the use of Alt+Enter to enter makes the function more readable, and the judgment logic of each layer of formula can be clearly seen.
Of course, if you want to evaluate so many levels of ABCDEF, it is too cumbersome to use IF nesting. You can use the VLOOKUP
function to quickly filter the combination.
3. SUM function
The sum function is also the most commonly used function in Excel, and its syntax is very simple:
This is the most basic syntax of the SUM function, but in use, the SUM function can play many tricks, such as cumulative summation, array formulas , and so on.
For example: use =SUM($B$3:B3) to complete the cumulative sum:
How does the SUM function use an array formula?
For example, there is a sales table, which has unit price and sales quantity respectively. If you want the total sales of all products, you can use the array formula to complete, enter the formula:
=SUM(B2:B9*C2:C9)
Then press Ctrl+Shift+Enter to complete the input of the array formula, so that you can quickly sum up “sales * unit price”:
4. SUMIF function
The SUMIF function is a conditional version of the SUM function, that is, the calculation is performed only when the corresponding conditions are met:
For example, ” calculate the total sales of all salesmen with [camera sales] over 800,000 yuan “, which sounds convoluted. In fact, it is very simple, sum the sales of the salesperson, but this part of the salesperson must be [camera sales greater than 80w] .
To briefly explain this function with the case, the [condition area] is [camera sales]; the [judgment condition] is [greater than 80], and the [summation area] is [the total sales of a single salesperson]. The following demonstration animation formula is:
=SUMIF(B2:B10,”>80″,H2:H10) .
In the above formula, we can see that the calculation result is 2855, and there are only 4 salesmen with [camera sales>80] in total, which are marked in yellow in the figure, and we can see that the calculation result is also 2855.
The SUMIF function has an enhanced version, the multi-condition version – SUMIFS, which is used similarly to SUMIF:
5. Pivot table
Pivot table is the most convenient data analysis tool in Excel. Using pivot table can easily complete basic data analysis work. Let’s take a brief look at some of the functions of the pivot table:
The pivot table can be dragged and dropped to try the next year’s data analysis. Due to the time problem, there is no time to animate it (I will come back and update it later). But I saw a good study material on the Internet, which can provide a reference, basically the effect I want:
Read Also :
