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

The original title: "which Excel master figured out this data collation skill, too useful!" (below) "

Small E before processing order data, need to merge the same order data for delivery, in order to save logistics costs.

As shown in the following figure, before that, I introduced three solutions: the function method, the plug-in method, and the PQ method.

Xiao E is already suffering from backache after packing hundreds of couriers.

Coincidentally, I saw a message from a partner backstage and wanted us to talk about metrics.

In line with the principle of meeting the needs of readers, today we will talk about how to use metrics to solve the above problem.

When calling the PP add-in, when it comes to measures, someone must ask, what is a measure?

Measure: as the name implies, it is a value, that is, it has only one result, which is usually calculated by an aggregate function.

So where is the measure in Excel?

It's just under the PowerPivot tab.

PS. PowerPivot is an add-on to Excel that can be used to manage millions of rows of data and perform powerful data analysis on that data.

If some of your buddies don't see the PowerPivot tab, it's because it's not called up.

If you have a developer tools tab, you just need to call it out in the COM add-in.

If there is no developer tools tab, we can call out the COM add-in dialog box in "File"-"options"-"add-ins" and through the "go" button.

Then also check "powerpivot" to load it.

At this point, I will default that everyone has called up the PP tab.

So next, let's take a look at the specific approach.

Specific steps due to the existence of the same information there are multiple order data, in order to simplify the difficulty of the problem, to facilitate your understanding, I am the same as before, first make a summary auxiliary table.

Then through this auxiliary table, the measure is written in the way of PivotTable.

Let's first take a look at the specific operation steps.

❶ selects the Auxiliary Table area-under the insert tab-Click PivotTable-click tables and regions.

Place it in the existing worksheet, check to add this data to the data model, and click the OK button.

PS. When the data model is checked, the data is automatically loaded into PowerPivot, so we can use the measurements in it.

At this point, you can see that the auxiliary table name just became the PivotTable area name.

Now, let's create a new measure ~

❷, under the PP tab, click measures-create a new measure.

The measure dialog box appears.

Measure name: quantity shipped.

Formula:

= CONCATENATEX ('region', 'region' [product name] & "*" & 'region' [total number], ",")

Click the [OK] button.

① drags the name and phone number into the line area.

② drags the shipping quantity metric into the value area.

At this point, the effect we want will be achieved!

Measure explanation before we wrote a simple measure, we solved our needs immediately.

The advantage of using PivotTable is that we have the flexibility to add or decrease external filter field conditions.

For example, if I only want to merge the products purchased with the same mobile phone number and the corresponding quantity, I just need to remove the name field from the line area.

The value of each summary in the PivotTable is a dataset.

So let's take a brief look at this measure.

= CONCATENATEX ('region', 'region' [product name] & "*" & 'region' [total number], ",")

The purpose of the CONCATENATEX function is to merge multiple texts together, similar to the TEXTJOIN function in Excel.

The CONCATENATEX function is structured as follows:

= CONCATENATEX (table, expression, delimiter)

Let's double-click the cell of Xiao Shuang's shipment quantity first.

At this point, a new worksheet will appear with datasets that filter Xiao Shuang and her mobile phone number.

And then execute our measurements.

= CONCATENATEX ('region', 'region' [product name] & "*" & 'region' [total number], ",") execute the expression first: the product name is combined with the total number:

'region' [product name] & "*" & 'region' [total number]

Finally, execute CONCATENATE to make use of separation to match and merge the data.

The merged result is the summary result of this cell.

If you don't want to use an auxiliary watch.

By the same token, we can add the data source to the data model by inserting the data source into the PivotTable report, and finally create the following measures.

The principle is basically the same as the above, interested partners can study it by themselves.

= CONCATENATEX (VALUES ('Table 1' [product name]), 'Table 1' [product name] & "*" & calculate ('Table 1' [quantity of goods]), ";")

Finally, this article explains the practice of complex merging similar items on PivotTable.

Check the data model when you insert the PivotTable, and the data is automatically loaded into PowerPivot (PP for short), and the measurement exists in PP.

To invoke the PP tab, simply check the COM add-on.

For the measure in the case, we mainly use the CONCATENATEX function, which is an iterative function that can perform an expression operation on each row in the data set, and then merge it into a value using a delimiter.

Compared with other practices, the advantage of using PivotTable is that we can flexibly control the filtering environment, once the filter field needs to be changed, we just need to drag and drop.

Other practices may need to be changed, which is troublesome.

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.