Let’s give an example of calculating KPI at work:
There are sales tasks and performance of each employee, and the completion rate is calculated. The current KPI calculation is based on the task completion rate. There are KPI calculation rules on the right
This can be quickly solved through the fuzzy search of the VLOOKUP function.
❶First, we process the standards for calculating KPIs and add column H. This is the minimum completion rate.
This data has two characteristics. The first one is that each data in column H is the lowest value of each level in column G:
X<30%, then the minimum completion rate is 0
30%<=X<60%, the lowest value of this level is 30%
By analogy, write the lowest value of each level in column H
The second characteristic is: the data in column H must be arranged in ascending order, that is, from 0→30%→60% all the way to 120%
❷Then we can use the VLOOKUP function formula to find it directly
We enter the formula in cell E2:
=VLOOKUP(D2,H:I,2,1)
[The last parameter of VLOOKUP is 1, fuzzy search]
The fuzzy search principle about this formula is:
Will find the value closest to the <= search value in the search column, and then return the corresponding result column
Let us give an example to illustrate
For example, Lu Bu's completion rate is 84%, then Excel will find the closest value to <=84% in column H, which is the 60% level, and the result is 0.8 corresponding to 60%
For another example, Da Qiao's completion rate is 18%, then the closest value in column H <= 18% is 0, and the resulting KPI is also 0
Do you understand the fuzzy calculation of this VLOOKUP function?
Advanced improvements, improved accuracy!
All the above conditional standards are less than or equal to, so it is easy to write the minimum completion rate
If our standard condition is just the less than sign, then how to write the minimum completion rate?
For example, 30%<X<=60% is the minimum completion rate, if you fill in 30%. If there is a person in the table whose completion rate is exactly 30%, then it will get the value of this level, 0.4. Then according to the new standard, X<=30% should be 0
In this case, we need to introduce a precision value, such as 10^-5 power [can be adjusted according to the actual situation]
In other words, 30%<X<=60%, we fill in 30%+10^-5
This precision is added to each subsequent one to form a new standard, and then we can use VLOOKUP fuzzy search to use it normally.
Have you learned this skill today? Try it yourself, welcome to leave a message for discussion~
Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/Excel-zhong-vlookup-han-shu-gong-shi-ji-suan-xiao-shou-gong-zuo-ti-cheng-ye-ji-kpi.html
支付宝扫一扫 
评论列表（196条）
测试