Click below ↓ Follow, watch Excel professional tutorials for free every day
Set the top of the official account or set it as a star ↑ to receive the push in time every day
Most of the working people use excel tables, but in front of different people, excel plays a very different role: if you can’t even use the most commonly used function formulas, excel is a rigid table, and everything has to be manually tossed; On the contrary, excel will turn into a super intelligent tool to help you automate everything.
In order to help you improve your excel function formula skills in the most cost-effective way, today I will give you 10 of the most commonly used excel formulas in the workplace, and you can apply them directly when you go to work!
If you are worried about not remembering the content of this tutorial, you can share it in the circle of friends to make a backup copy for yourself.
In addition to the content of this article, students who want to comprehensively, systematically and quickly improve their Excel skills and avoid detours, please search the WeChat public account " Learn Excel with Li Rui " and click " Knowledge Store " in the bottom menu or scan the code below to enter .
More Excel video courses with different contents and directions
Long press to identify the QR code↓ to obtain
(Scan code on WeChat on mobile phone ▲ identify the QR code in the picture)
Commonly used excel function formula 1: data query
Query the corresponding data according to the specified conditions.
The formula is as follows:
=VLOOKUP(E2,$B$2:$C$11,2,0)
The schematic diagram is as follows:
Common excel function formula 2: multi-region summation
When summing multiple regions, it is not necessary to select regions one by one and add them together at the end. Use the SUM function to select the region as a whole. The text in it will be ignored, and all values will be automatically summed, which is very convenient. Unfortunately, many people have not used this kind of characteristic.
The formula is as follows:
=SUM(B2:D11)
The effect is shown in the diagram below :
Common excel function formula 3: multi-condition count
According to multiple specified conditions, count the number of data that satisfy all conditions at the same time. This kind of problem can be solved with this formula.
The formula is as follows:
=COUNTIFS(B:B,">80",B:B,"<90")
The effect is shown in the diagram below :
Commonly used excel function formula 4: multi-condition summary
There are many ways to deal with conditional summarization and multi-conditional summarization. It is recommended to use the SUMIF or SUMIFS function to solve them first, which has high computational efficiency and simple writing.
The formula is as follows:
=SUMIF(B:B,E2,C:C)
The schematic diagram is as follows:
Commonly used excel function formula 5: multi-condition average
Calculate the average value according to the specified conditions, do not need to filter the data that meets the conditions and then calculate, directly use the following formula to do it in one step with only one function.
The formula is as follows:
=AVERAGEIF(B:B,E2,C:C)
The effect is shown in the diagram below :
Commonly used excel function formula 6: Judging duplicate data
Judging and repeating this kind of thing, use IF and COUNTIF to solve it perfectly, look at the formula below.
The formula is as follows:
=IF(COUNTIF(A:A,A2)>1,"Repeat","")
The effect is shown in the diagram below :
Commonly used excel function formula 7: extract value
When there is text behind the value, it will cause many calculation formulas to return wrong values. To extract the values, many people still rely on manual extraction one by one. When there are many values, they will go crazy. Use the following short formula with the help of the LOOKUP function. Features easily extract numeric values.
The formula is as follows:
=-LOOKUP(1,-LEFT(B2,ROW($1:$9)))
The schematic diagram is shown below.
Common excel function formula 8: find the last data
Many functions search data from top to bottom, such as VLOOKUP function, or INDEX+MATCH function. When you need to search data from bottom to top according to conditions, you can easily get it with the following formula.
The formula is as follows
=LOOKUP(1,0/(B2:B11=E2),C2:C11)
The usage diagram is as follows.
Commonly used excel function formula 9: count the number of unique data
The problem of non-repeated data statistics is a thorny problem for many people, but with the following formula, it can be used immediately and directly solve the problem.
The formula is as follows:
=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))
The schematic diagram is as follows:
Commonly used excel function formula 10: keyword classification
The most feared thing in data query is the fuzzy keyword classification query. Many functions can only be accurately searched according to complete conditions, but the following formula is recommended for collection, because it can help you easily solve the keyword classification query problem.
The formula is as follows
=LOOKUP(1,0/FIND($A$2:$A$5,C2),$A$2:$A$5)
The schematic diagram is as follows:
These commonly used excel formulas can help you solve problems at critical moments, and those who are interested should quickly collect them.
Hope this article helps you! I'm afraid I can't remember and I can send it to the circle of friends and mark it myself.
> > Recommended reading < <
(click the blue word to jump directly)
When VLOOKUP met her, she became scum in an instant!
Form conversion technology used by 99% of financial accountants
86% of people can't last for less than 90 seconds. This universal formula is poisonous!
One to ten: the 10 laziest Excel batch operations in finance
Why use an Excel pivot table? This is the best answer I've seen
Such a simplified formula has refreshed my understanding of Excel...
Top 10 Excel car accident scenes that mistaken the accelerator for the brake, the last one is on...
The brain-opening VLOOKUP actually has such an operation!
Excel Dynamic PivotTables, would you?
Three extended usages that make VLOOKUP even more powerful
This Excel universal formula easily KOs four major problems, it's that simple!
You really don't know how powerful the SUM function is!
Long press to identify the QR code↓ to enter the knowledge store
(Long press to identify the QR code)
Tips for returning students anytime
Since some old students bought courses 4 years ago, they have bought too many courses or they have forgotten where to listen to the courses for a long time, so the entrances of the courses purchased on each platform are specially organized into the following figure.
1. Search the WeChat public account " Learn Excel with Li Rui " and click " Purchased Courses " on the bottom menu to view the courses you have purchased on various platforms, so that everyone can find and resume courses at any time.
2. The bonus of course distribution and promotion is also transferred from this official account to everyone's WeChat wallet ( you can automatically receive money after following, enter your WeChat change, and have a transfer record in WeChat payment ), old students can enter the " knowledge shop " and click the bottom Click the button " Promotion to make money " or " My " - " Promotion Center " to query the detailed records of promotion rewards, and support active withdrawal .
In addition, there is also the contact information of the assistant. If you have any questions or learning needs, you can leave a message for feedback, and the assistant will reply to you within 24 hours.
Press the picture above to identify the QR code and view the details
Please recommend this official account to your friends :)
Let’s go here first today, more dry goods articles can be viewed with the helper below.
If you like this article
Welcome to click to watch , share and forward to the circle of friends
Dry goods tutorial · Information sharing
Welcome to scan the code ↓Add a little assistant to check in the circle of friends
Long press the picture to identify the QR code
Pay attention to the WeChat public account (ExcelLiRui), there are dry goods every day
Follow the top public account or set it as a star
Never worry about not receiving dry articles anymore
▼
After following, you can receive Excel dry goods tutorial every day
Please recommend this official account to your friends
↓↓↓Click "Read the original text" to enter the knowledge store
Comprehensive, professional and systematic improvement of Excel practical skills