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

The original title: "this function is too unnatural!" It took me an hour to write the function, and it took 10 minutes to click with the mouse. "

Hi, guys, how are you?

The new year is about to start again, and all kinds of statements and statistics are coming one after another!

Do you feel helpless? Do you feel flustered?

Don't hurry! Don't hurry! Don't hurry!

When it comes to merging, summarizing, and querying data, we have to mention an unnatural function, which is:

Merge calculation!

Merge calculation!

Merge calculation!

Say the important things three times! )

What?

This merging calculation can also be called an inverse function?

What a surprise!

Then come and have a look with me!

Where is the general usage merge calculation?

It's in the data tab. Oh!

Although it looks inconspicuous, it can play a big role!

Needless to say, let's first take a look at one of its routine operations.

As shown in the figure below, there are two statistical tables recording sales in the first and second half of 2021, respectively. (in order to make it easy for you to understand, the examples given in this article are relatively simple. After reading the article, you can try more complex data by yourself.)

Now, I want to put their sales together.

The specific operations are as follows:

▋ Step01 calls up the "merge calculation" dialog box, first select the starting cell that needs to store the results, such as [G2] cell, and click "merge calculation" under the "data" tab.

The following dialog box appears:

In the ▋ Step02 add area, select the first area [A2:B9] in the [reference location], and then click "add".

Then select the second area [D2:E9] and click "add".

In this way, we have added both areas.

The [function] defaults to [summation]. In the figure, you can also see that the function here can choose not only summation, but also counting, averaging and other ways, and you can use it flexibly according to your needs.

In [label location], check [first row] and [leftmost column].

Finally, click "OK". As shown below:

The merged [G2] cell has no content, so you can fill in the desired content [name].

It's pretty simple!

Find usage to find reference data, we usually use functions such as Vlookup.

But you know what? In fact, [combined calculation] is also possible.

Let's take a look at how merge computing solves the problem of one-to-one lookup references.

PS. The lookup data function here, the lookup value must be unique in the data source!

As shown in the picture below, we want to find the sales of Zhang San and Wang Wu.

The steps are as follows:

▋ Step01 select the target range first select the [D1:E3] cell range, and then follow the above method to call up the [merge calculation] dialog box.

The ▋ Step02 add area adds the data region ([A1:B8]) in the [reference location], and then check [first row] and [leftmost column] in the [label location].

Finally, click "OK", as shown below:

In this way, we will find out all the data we are looking for.

Doesn't it feel a little interesting!

Summation usage carries on the conditional sum to the data, we generally use the Sumif function, or the PivotTable.

But have you ever heard that [merge calculation] can also be summed up?

As shown in the picture below, we want to find out the total sales volume of Zhang San and Wang Wu.

The procedure of this method is the same as that of [02 find usage].

First select the [D1:E3] area and bring up the [merge calculation] dialog box.

Add the location that needs to be [referenced] ([A1:B11]), and check [first row] and [leftmost column].

Finally, click [OK], and the final result will come out!

How's it going?

Is it easy to complete the summary statistics again!

Moving picture reference:

Fuzzy statistics for fuzzy statistics, we usually use functions, combined with wildcards (asterisks * or question marks)? To implement data summarization

I guess you don't know that wildcards can also be used in merge calculations.

PS.

The wildcard asterisk (*) represents any number of characters.

The wildcard question mark (?) represents any character.

As shown in the following figure: we want to summarize the sales of all the people who start with the surname Zhang and those who start with the surname Wang and have two characters.

The steps here are the same as above.

First select the [D1:E3] area and bring up the [merge calculation] dialog box.

Add the location that needs to be [referenced] ([A1:B11]), and check [first row] and [leftmost column].

Finally, click "OK", as shown below:

The result of Zhang * is that the number of Zhang San in line 2 is 10 and the number of Zhang Sanfeng in line 9 is 80, which adds up to 90.

Wang? The result is: Wang Wu in line 4 is 30, so 30 is returned, and Wang Xiaowu in line 11 is three characters, so it is not counted.

Take it easy!

Moving picture reference:

Multi-table summary for multi-table merging, if there are not many tables, it is very convenient to use the [merge calculation] function.

As shown in the figure below, there are two tables that need to be merged now.

This method step is the same as [01 normal usage].

First select the starting cell of the storage area of the result table, such as [A1] cell in the summary table.

Then call up the merge calculation dialog box, add the data in [Table 1] and [Table 2], and check [first row] and [leftmost column] in [label location].

Finally, click "OK", as shown below:

The summary is complete.

This method seems to be no different from [01 normal usage].

Then let's use it in an advanced way on this basis.

As shown in the figure below, we just want to summarize the sales volume of Zhang San, Wang Wu and Zhao Liu in Table 1 and Table 2. What should we do?

In fact, the operation methods and procedures are basically the same!

First, select the areas that need to be summarized ([A1:B4]), bring up the [merge calculation] dialog box, add the regions of two tables in turn, and check [first row] and [leftmost column].

Finally, click "OK" as shown below:

In this way, multi-meter summarization can be easily done!

And it is a summary of the designated personnel.

The disadvantage of multi-column field summary merge calculation is that it can only use the first column as the text line field to be summarized, and the other columns as the value field of the summary.

If we have two or more fields that want to be displayed in row fields, we can't just use merge calculation.

For example, in the following picture, we want to count the total sales of each product name in the first half and the second half of the year.

Follow the above method.

First select the [I2] cell, call up the [merge calculation] dialog box, and then add the data of the two regions.

Finally, click "OK", as shown below:

The result is not accurate!

[specification] one column defaults to summation. But because this column is text, it cannot be summed and is displayed as a blank cell.

What are we going to do?

There is a saying like this: there are always more methods than difficulties!

We just need to merge two or more fields into one field, and then summarize it!

As shown in the following figure, add a column to the original data area and integrate the original product name and specifications into one column

Then, you can follow the above method to merge.

First select the [K2] cell, and then add these two fields.

Then click "OK" to complete the merger! Look at the effect picture:

Finally, you can add a title to the blank cell, and then beautify it, and you can finish the work!

At the end of today, we have learned six functions of merge Computing, including:

❶ aggregates multiple regions

❷ search

❸ summation

❹ fuzzy statistics

❺ multi-table unconditional summary and specified condition summary

❻ multi-column field sink

Isn't it interesting to have so many ways to play a small function that was originally inconspicuous?

This very rebellious [merge calculation] function, that is, without any advanced functions, do not spend a long time cost of learning, and do not have to search everywhere, you can achieve commonly used search, summation, summary statistics and other functions.

You can do whatever you want to aggregate!

Learning this will certainly help you to show your skills in your work.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart, Editor: Xiaoyin, Zhu Lan

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.