How to use Excel to calculate ladder price?
Candy fruit 2020-11-26 12:48:17
Today is also a practical problem , About the beauty of fruit , Let me help you count how much money you've made ?

Old tool people online ……

Requirement specification : Recently, there are activities in the fruit shop , Buy the same fruit at one time , The unit price is different under different weight , Like buying mangoes ,5 Jin ( Include ) following 6 element / Per Jin ,5 To 10 Jin , The unit price is 5.6, The more you buy , The lower the unit price !

Now we need to get the corresponding price according to the weight the customer purchased , Calculate the amount !

 How to use EXCEL Calculate the ladder price ?

The difficulty of this requirement , You need to find the name of the fruit , Also need to match according to the interval ! Many students , Know the exact match , direct VLOOKUP+MATCH that will do , But this is an interval and I don't know how to deal with it !

The point is how to find the corresponding fruit 、 Unit price under the corresponding weight , In fact, it's relatively simple ! Let's first write the formula according to the original setting , Then read and optimize !

▼ Please read me , I'm just a humble formula ……


 How to use EXCEL Calculate the ladder price ?

The core of this is still MATCH function ,MATCH The function has three matching patterns depending on the third parameter , The most used is exact matching , It's a perfect match , Generally, the third parameter is 0 Or just write a comma

Although it has little to do with this formula , But the frequency is high , Let's talk about !

MATCH Exactly match - The third parameter 0 Or shorthand ( Just write commas )

Return the search value , In the search area For the first time Position of appearance ! If you can't find it, you will report an error

 How to use EXCEL Calculate the ladder price ?

This is the most common usage ! This time the demand obviously does not need this kind of precise match !

MATCH Ascending search pattern

Here we have a look at the official explanation of the third parameter :

 How to use EXCEL Calculate the ladder price ?

In this case, we use 1 Or omit ! Let's read about it

Two main points :

1、 The second parameter must be in ascending order , This case fully meets the requirements

2、 Returns the maximum value less than or equal to the lookup value .

For the second point , Let's add , For example, we look for 10, stay {1,3,5,10,20} In interval

First find a ratio less than or equal to 10 Of Yes {1,3,5,10} , The maximum value of this is 10, So back 10 Corresponding position .

Convenient interpretation , Let's paste the formula again :

 How to use EXCEL Calculate the ladder price ?

Why +2,MATCH Matching is from G At the beginning of the column , That is, if it can match to the minimum, it is 1

And we VLOOKUP It's from F Column names start with , first 5 The price corresponds to 2, So we should add 1 Do you ?

It's not true , There is also a pit here , Namely 5 Questions below yuan , It's not reflected in the table , It's not listed , The first one on the list should be 0,IFERROR Appearance , That is to deal with this problem ! The correct interval setting should be ! In itself 100 I don't think about it ! exceed 100 Jin will have problems !

 How to use EXCEL Calculate the ladder price ?

If you think about fault tolerance , We should put online 100 Think about it, too , such as 100 More than one kilogram 1 element / Jin

▼ Please read me , I'm just a humble formula ……


So we don't have to think about fault tolerance !

 How to use EXCEL Calculate the ladder price ?

If you're a little bit smart , Mastered MATCH+1 Medium +1 Or not !

▼ Please read me , I'm just a humble formula ……


 How to use EXCEL Calculate the ladder price ?

MATCH Returns the position of the second parameter that satisfies the condition for the first time , We just need to move forward one , So the first thing that satisfies the condition is 2,VLOOKUP The search itself is No 1 Column , So the unit price you need is from 2 Column start , Dynamic Fit !

as for MATCH For the third time -1, It should be clear from the instructions ! If it's disorder, do you know how to play ?

There are both unit price and quantity , As for how much money was made in the end , Miss said that she would …… Tool person offline ……!
本文为[Candy fruit]所创,转载请带上原文链接,感谢