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

Original title: "Sum of multiple worksheets, are you still pressing Shift?" With this method, the table can be updated automatically! "

For most companies, the summary of data is an indispensable part of their daily work.

Among them, the summary and statistics of multiple table data is the last step in data statistics.

There are many small partners in the collection are repetitive operations, inefficient work.

This is not, as shown in the picture below, is a summary of regional performance.

The monthly performance needs to be added up and reflected in the summary table.

The data structure of each month is exactly the same as that of the summary table. The details of the January and February worksheets are shown below:

Do most of the kids like to use the following two ways when collecting?

Method 1:

First select the [B2] cell in the [summary table], then enter an equal sign (=), then click the [B2] cell in the January worksheet, then enter a plus sign (+), and then click the [B2] cell in the February worksheet. Enter. As shown below:

Finally, drag the fill formula downward using the fill handle in the lower right corner of the cell to get the result of [B3:B5] below.

Method 2:

First select the [B2] cell in the [summary table], and then enter an equal sign (=), then enter the SUM function, click on the [January] worksheet, and hold down the SHIFT key, then click the [B2] cell in the [February] worksheet, and finally enter.

The advantage of the above two methods is that the operation is very simple.

The disadvantage is that when there are new months, you need to repeat the operation, not once and for all.

How can you set the formula only once to make the table update automatically?

Come on, let's take a look at how to achieve this magical function.

1. Magic auxiliary table first select the [February] worksheet, and then click the plus sign (+) next to it to create a new blank worksheet. As shown below:

Double-click the worksheet tab and rename the worksheet to: December.

Then use the SUM function to sum according to [method 2] above.

First select the [B2] cell in the [summary table], and then enter an equal sign (=), then enter the SUM function, click on the [January] worksheet, and hold down the SHIFT key, then click the [B2] cell in the [December] worksheet, and finally enter.

Then, hide the [December] worksheet.

If there is a new worksheet in the future, its contents will be counted automatically.

For example, let's add a worksheet to see if it really becomes automatic statistics.

Select the [February] worksheet, and then click the plus sign (+) next to it.

Rename the new worksheet to March, and enter:

Let's look at the data in the summary table:

The newly added worksheet data is automatically counted.

Perfect solution to automation problems.

In addition, wait until December, and then unhide the original hidden [December] worksheet, and then enter the contents, the formula in the summary table does not need to be modified, the data will be automatically updated.

How's it going?

This multi-table automatic summation method is not quite magical!

The following moving picture is for reference!

2. The method of multi-table summation above in knowledge expansion can only be applied to the exact position of the table structure of each month.

If the structure of the table is not exactly the same every month, it will not apply.

For example, there are only Beijing and Shanghai in January and Nanjing and Tianjin in February.

At this time, we can use the method of Power Query multi-table merging combined with PivotTable.

It can also be implemented using only functional methods.

Because most partners may not be able to use the Power Query feature. So we share the functional approach here to solve this problem.

The formula is as follows:

= SUM (SUMIF (INDIRECT (ROW ($1 INDIRECT) & "Yue! avut A"), A2Jing INDIRECT (ROW ($1RV 12) & "Yue! BJV B"), 0) Formula parsing:

This formula consists of three parts:

① SUMIF (INDIRECT ($1 ROW) & "Moon! a Vol A"), A2 Magi INDIRECT (ROW ($1 V 12) & "Yue! B")

② IFERROR (①, 0)

③ SUM (②)

The method of using this function can be achieved in one step.

Of course, there is still some difficulty.

PS: in the lower version, you need to press three keys [Ctrl+Shift+Enter] to end the formula.

In addition, there is a method of using auxiliary region + function, which can also be realized. I would also like to introduce you here. As shown below:

Among them: the blue area part of the manual input, the yellow area we use the equal sign (=) to refer to the contents of each worksheet starting from the [A2] cell.

The general idea is to create an auxiliary range in the summary table and then reference the data from each worksheet.

Finally, you can use the SUMIF function to sum the auxiliary region.

The formula is as follows:

= SUMIF (EchroHMagazine A2GRG I) looks for the contents of the [A2] cell in the [ERV H] column, and sums up the row data corresponding to the [FRV I] column.

In addition: if there is a new worksheet later, you can set the auxiliary area and the range of the formula for January-December in advance.

Maybe some friends have such questions? Can't I just copy the data from the following month worksheet and paste it into this auxiliary area?

A very good question! But if the monthly data changes later, do you have to copy it again, twice or three times? If you use the equal sign (=) link here, it won't be so troublesome!

3. At the end of the day, we share a magical multi-table summation method. It is realized by creating a new auxiliary table.

This method is simple and practical, but only if the table structure is exactly the same. It is very useful for the summary of financial statement templates, tax statement templates, personnel, administration and other standard templates!

In addition, we also extend how to use functions to solve the problem when the contents of the rows of the table structure are not exactly the same. The method of extending the functions in knowledge can be used to solve the situation that the table structure is exactly the same and the table structure is different.

Children can learn more, try more and think more in their daily work, and there will always be new discoveries.

But the tips in Excel are much more than what I've introduced today.

If you Get Excel thinking, you can quickly handle a large amount of data 👇, even with only one shortcut key.

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

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.