Shulou(Shulou.com)11/24 Report--

Small logistics companies will have consignment business, generally there will be designated routes for transportation and distribution within a specified period of time. Some merchants may ship the goods by consignment and put the goods in the logistics company in advance, and the company will deliver the goods to the destination.

Some of the consignment prices are calculated according to volume, different routes, different volumes will have different prices.

Since the same route can be shipped by merging a car, there is a pricing method based on the combined volume.

As shown below: now, we need to calculate the customer's quotation.

To accomplish this effect, you only need to write a function formula, which involves the use of three functions. Click me to get "35 commonly used function descriptions"

Next, let's take a look.

1. Sumif function to summarize and sum the same route. Because the specific quotation is a summary of the same route, and then inquire. So before using the query function to query, we need to use the sumif function to sum the volume according to the condition (the same route).

The formula is as follows:

= SUMIF ($Bath2VOG / BLY3 / B2 / B2 / 2 / 2 / 2 / 3 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 3 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 3 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 /

Syntax rules for sumif functions:

= sumif (conditional region, conditional region, summation region)

The corresponding is:

Conditional area: $B$2:$B$33, city column

Conditions: B2, Jiangmen and Foshan

Summation region: $C$2:$C$33, volume column.

After the summary, we need to query the volume to match the interval.

2. Match function to match the corresponding position. Because the parameter table is a two-dimensional table, that is, we need to cross-match. Here, we first use the match function to match the position of the corresponding volume interval (0mem5, 10, 20, 50).

The formula is as follows:

= MATCH ($H$1:$L$1, 1)

The match function is mainly used to match positions.

= MATCH (lookup value, lookup range, lookup type)

In the third parameter, there are three ways to find the type:

0 exact match

1 less than

-1 is greater than

Corresponding formula:

Lookup value: value after sumif summary

Look for: $H$1:$L$1, a list of different volumes

Lookup type: 1 is less than. For example, in the range of 0-5, the volume value 2 returns the position of the column 0, so here you want to write less than 1.

The value + 1 obtained by the match function corresponds to the third parameter in the vlookup function-- the number of columns to be returned as a result.

So let's use the vlookup function to query the final result.

3. Vlookup function, which returns a value that meets the condition. Use the vlookup function to query the price under each route.

The formula is as follows:

= VLOOKUP (B2Jing $G$2:$L$13, MATCH (SUMIF) + 1,0)

= VLOOKUP (the value to be found, the area to be found, the column in which the result to be returned is in the search area, exact match or approximate match)

Value to look for: B2, Jiangmen Foshan

Find area: $G$2:$L$13 parameter table

The result to be returned is in the column of the lookup area: the number of columns obtained according to the match function + 1. Because the number of columns starts from finding the column, you need to add 1.

Exact match or approximate match: 0 exact match.

Now, the corresponding offer has to be made.

4. To sum up, the explanation of this paper is to compile the formula of cross approximate matching according to the conditional total value.

The ❶ sumif function first summarizes the values of the same route

According to the results obtained by sumif function, ❷ uses match function to match the relative positions of different volume numbers (0, 5, 10, 20, 50).

❸ returns the final result with the vlookup function, and the result from the match function + 1 is the number of columns of the third parameter of vlookup.

The whole process is not difficult, the key here is, after the disassembly of the requirements, the use of functional tools to achieve the desired results step by step.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.