How to bore sheets with a paper clip. How to combine sheets in Excel

On one of the pages of this site, a small article was published about how to save sheets of the active Workbook Excel in separate files. It is time to consider the opposite question about how to collect information from different files in general and how to combine multiple files into one book in particular. Make it can be easily and fast, but about it a little later.

Simultaneous user work in one book

In some cases, it is necessary to collect or process information simultaneously to several users. There are different methods that allow you to not transmit the spreadsheet sequentially on the chain from one user to another, and it is possible to work in it simultaneously or in parallel. One such methods is to provide general access to the document when users can lead simultaneously with the data of this document.

Another way to speed up the process of collecting or processing data is the division of the working book into separate sheets with their subsequent merging after custom processing. About how you can divide the workingbook on individual sheets and save these sheets with separate files, we have already told. Now let's stay in more detail on how to combine them back.

Combining sheets of different working books in one

Combining different files to one or certain sheets in one book has a variety of solutions. This is a standard option with copying and inserting sheets to the desired book, and the use of macros, and the installation of additional programs specializing in combining files such as MergeExcel. Each of these methods has its advantages and their cons. For our part, I want to offer another way to solve the problem of combining sheets from different books in one. Without dealing with the program code of macros and without installing additional programs, you can quickly expand Excel with new features using the addition called add-in.

The add-in to combine various files into one is based on a VBA macro, but it is advantageous from it convenience in use. The add-in is easily connected And it starts with one click of the button displayed directly to the main menu, after which the dialog box appears. Next, all intuitively clearly, files are selected, the sheets of these files are selected, the additional settings of the merge are selected and the "Start" button is pressed.

macro (add-in) To combine multiple Excel files in one book

Add-in allows you to:

1. One mouse click on the macro dialog box directly from the Excel toolbar;

2. Select files to merge, as well as edit a list of selected files;

3. Combate all sheets of selected files in one workbook;

4. Combine into the workingbook only non-empty sheets of selected files;

5. Collected in the final book sheets with the specified name (you can use a mask with specials. Coincidence symbols);

6. Collect one book sheets of selected files with a specific number (index) or number of numbers;

7. Collect sheets with a certain value in a specified range of cells;

8. Set additional parameters for the union, such as:

a) assigning the names of the names of the united files;

b) removal from the book in which the data is associated, its own sheets, which were in this book initially;

c) replacement of formulas values \u200b\u200b(results of calculations).

If necessary, the names of the sheets of the formed working book can be quickly changed by add-in automatic renaming sheets.

Combining the ranges of values \u200b\u200bfrom different sheets of different working books on a separate sheet

It is also possible to overtake all the workbooks specified in the dialog box, select only the necessary sheets in them, copy the ranges defined by the user and paste them on a separate work sheet of the active book. In this case, you can choose a method of placing data on a sheet with the results, the ranges of values \u200b\u200bcan be located one by one as horizontally and vertically. Thus, it is possible

If the data you want to analyze are presented on several sheets or in several books, they can be combined on one sheet using the Consolidation command. For example, if there is a separate sheet of expenses for each regional offices, using consolidation can be created on the basis of this data corporate spending sheet. Such a sheet may contain the final and average data on sales, current levels of stocks and the most popular products throughout the organization.

The type of consolidation should be chosen based on how united sheets look like. If the data on sheets are uniformly (the names of the rows and columns may differ at the same time), use the arrangement consolidation. If on sheets for the corresponding categories, the same lines and columns are used (data can be located differently), use consolidation by category.

Consolidation by location

Consolidation by category

Environmentsand the other - Average

Consolidation by location

To consolidate the location, the data range on each of the source sheets must have a list format without empty rows and columns.

Consolidation by category

To consolidate the category, the data range on each of the source sheets should have a list format without empty rows and columns. In addition, the categories must be identified the same. For example, if one of the columns is called Environmentsand the other - AverageConsolidation does not sum up these columns.

Hi, habraloudi!
I read on Habré about stitching books and understood it - it's not one of the topic. The soul was embarrassed: the proposed method of the classic firmware gives a high-quality book, but the costs of the strength and time invested in this labor feat will be aspilated. Plus, a considerable snarling is needed - no one can make a more or less high-quality book from the first time. What is just one of the block of block - you really think that at least once in life you can do it exactly for a block from 200 sheets with the help of a stationery knife? And if you want to do not the only book a year, and at least 2-3 per week? We would make methods simpler, and preferably no less effective. Share!

Method 1.
With a volume of up to 40 sheets (and this is 80 pages!) We sew a simple swivel stapler in the middle of the sheets, making the usual notebook (like a student). To do this, we buy a swivel stapler, calculated on the bracket of great depth. Its working part is able to rotate 90 degrees, and the bracket of great depths (not width, namely, the depths) can easily break up to 40 sheets. For a few seconds we have a neatly stitched book.

Method 2 (practically no limitation of the volume of the book)
Print a book on A4 format paper or less. Take the stationery hole punch, and selecting 20-25 sheets from the stack of 20-25 sheets, we make holes in them. It is very important here that holes are on all sheets at the same distance, both from the edge of the sheet and on top-bottom. To do this, have a hole punch with a built-in alignment line. It is worth such a hole in exactly as much as without a ruler, but will allow your future book to look pretty neatly. Thus obtained sheets with holes, we simply insert into a pre-acquired folder-folder. All the variety of such folders is reduced to such types: folders on the sliders, on the ropes, on brackets. We choose the folder on the brackets, paying attention to the following:
:: The size of the bracket should be slightly more than you need to insert all sheets. Sheets should not fit in bulk! After inserting the page should be free to turn over.
:: Staples should be disconnected as much as possible.
:: When the brackets are connected, there should not be the slightest gap between them, otherwise the sheet does not fall, but will be cling when turning, which is very annoying.
:: It is advisable that the brackets are disconnected by the hands - on the gap, or with the help of the tongue at the bottom and at the top of the folder. Do not buy the folder where the brackets are bred by a bulky mechanism - it will not be convenient to use, the feeling of "books" will disappear.
:: folder cover It is advisable to choose soft. Its size should be greater than those placed there. The best cover is made of plastic.
Just insert sheets into such a folder - and the book is ready. And you do not need to think that this is a primitive: I met such books issued by foreign publishing houses (though, there are indefinite brackets). At first I thought that I was just laughing. AN no - Zabugorsky inhabitants perceive such "books" as books. Well, for them and McDonalds - restaurant.

Method 3.
Buy a notebook for records of suitable size and volume (they are up to 200 sheets, it is 400 pages), on a plastic or on a metal spring, must with clean sheets (no "in a cell" or "striped"). Carefully remove the spring (without tools without any). Print on the resulting sheets a book. Insert back. Very neatly crimp the spring with your fingers, pushing evenly for each "tooth". Do not overcover and do not remember the teeth (otherwise the book will have an untidy look), sheets and so will not fall. Book of decent quality is ready.

Method 4.
Print a book. With the help of a stationery hole, as in Method No. 2, we make holes. But now we make a row of 4 holes - 2 higher, 3 lower. Do not forget to prepare the bottom and upper cover in the same way. In the economic store we buy a device for planting rivets or lurexes. Through the resulting holes using ripples or lures, connect pages and covers. If Cardboard Covers Or Polokarton are needed using a ruler to clip the cover along the disclosure line to the first disclosure of the book. If the cover of plastic - it is necessary to compare half the depth of plastic along this line - it will open along this line (it may not work with a neat groove from the first time). Of course, such a book will not be disclosed "to the root itself" - this must be considered when printing content. It turns out a very comfortable and beautiful book. With a certain skill, you can make a cover from a single piece of material - then the "root" will not be visible outside.

Method 5.
We buy a binding machine for a plastic spring (such a "spring" is little similar to the spring). There is a car from $ 30, and it is not more difficult to use it than a toaster. With the help of plastic springs, you can sew up to 500 sheets. There are similar machines for stitching on a metal spring, but they are more expensive for them for them, and no more than 130 sheets will serve you. Received books are very convenient to use. The correct name of such machines "Binder for binding to a plastic (metal) spring". When buying, pay attention to the following: The housing and handles must be metallic; Knives should be disconnected separately - the more, the better; there must be adjustment of the departure from the edge; Choose a typewriter designed for the maximum number of pages being stitched, and at the maximum simultaneously perforated pages - do not save here; All knives should move synchronously and without the slightest hooker; The rest, including the manufacturer - does not have much importance for an individual user.

Method 6.
We will do real books. "Real" are two species: sewn and glued. Stitched are the highest-quality, but also the most difficult in manufacture, and therefore - not in the subject of this article. Gledged - the most common, look at your bookpin: if the pages of the book in the root area, under the cover are connected by a semillimeter layer of solid glue - it is it that. Here are the books, and professional quality, and we will do without any problems at home. To do this, you will need to buy a thermocryptic machine for $ 50 and thermocons. The machine melts the originally solid thermoclay. After printing and trimming, the sheet block is inserted into the inside of the machine, and it is crimped. On the finished block of books manually glued the cover. That's all. With this method, you can sew up to 700 sheets (depending on the thickness of the paper).

Method 7.
Binding with a metal channel (metable) promises high quality, moments and low cost of binding with a thickness of up to 300-600 sheets A4 80g / sq. M at home. The apparatus at the price of about $ 200 compresses the block of metal screw along the entire block. According to reviews - very reliably. An interesting feature - a bracket can be opened and reused, up to 10-20 times.

Note:
All these methods (other than metable) I tried myself. I have a lot of books stitched in this way. It's simple, quickly, and really accessible to everyone. Good luck!

Tags: binding, books

Author Lady Bet. asked a question in the section Other languages \u200b\u200band technologies

how to combine all pages of the book in Excel in one? You need to make the sample and got the best answer

Answer from ўля Pavlova [Guru]
To combine the sheets of the book makes sense if these sheets contain the source data entered on the same structure on each sheet. This can be made copying values \u200b\u200bfrom the sheet to the overall sheet.
If the sheets contain formulas, and even with a variety of references, then copying will destroy all the work. References must be relative.
Remember that Excel's value is just in formulas automatically calculated.
And if you work only with the data albeit in tabular form, you can easily do the Word tables, there are tables to add visible rows similar to the structure from other tables with copying or transferring through a buffer, but no calculated formulas there.
The sample in Excel can be done from several sheets, in the cycle, moving from the sheet to the sheet in order of sheets, or on the list of names of the sheets. Just need to organize a cycle.

Answer from 2 response[guru]

Hey! Here is a selection of topics with answers to your question: how to combine all the pages of the book in Excel in one? You need to make a sample

Answer from Striped Giraffe Alik.[guru]
What for? What is the sample? Some things can be done "Through sheets"!

Answer from Vitaly Lapin[guru]
in Ixel there are consolidated tables, good thing

Answer from Eergei Rakovets - 1C Franchising[active]
vUVU

Answer from Ўzer honest[guru]
Well, naturally, no sheets do not "merge". Is that copying all the information from them to one. Weird question. How does the program know how to arrange information for a hypothetical "union"? Yes, and never had the need for a similar one. You describe what you need - it will be more clear. And 100 and more sheets can easily be described in some situations with one formula. Well, not - another Visual Basic remains.

Answer from Irina Korchagin[newcomer]
No need to combine pages

Suppose you have several sales reports in different regions. If you want to perform calculations and build graphs to the entire data volume, you need to collect all the data on one consolidated sheet. Switching between multiple books, infinite copy of hundreds of rows or writing VBA can take a long time.

With the add-in "Combine Sheets", you can collect data on one consolidated sheet in seconds:

• Collect sheet data from different books on one sheet
• Collect sheet data with the same name and combine the name of the tab
• Combine sheet data with an identical structure under one heading
• Save formatting in a summary sheet of result

Add "Combine Sheets" in Excel 2019, 2016, 2013, 2010

Suitable for: Microsoft Excel 2019 - 2010, Desktop Office 365 (32-bit and 64-bit).

How to work with add-on:

How to combine data from several sheets in one to one in 3 steps

With the add-in "Combine Sheets" you can collect and combine the data of several sheets - and from different books - in one main sheet in just 3 hours:

1. Click the "Combine Sheet" button on the XLTools panel\u003e Select the type of operation:

• Combine sheet data with the same name on one sheet

2. Mark the sheets that have merged well. The data tree displays all sheets in all open books.

3. Click "Combine"\u003e Finish! All data is copied to one main consolidated sheet.

How to combine multiple sheet data on one step-sheet

Suppose you have a series of sheets, and each of them contains sales report on a specific category of products. The add-in will help you copy all these individual reports and combine the data into one consolidated sheet.

1. Click the "Combine Sheets"\u003e button\u003e Select "Combine multiple sheet data on one sheet."
2. 
   
3. Click the "Combine"\u003e button, all data from the selected sheets are assembled on one main sheet in a new book.

How to combine data from sheets with the same name on one step sheet

Suppose you have a number of books, and each of them contains a report on regional sales. Each report is divided into sheets with data on specific products - thus, tabs in regional reports have the same names. The add-in will help you copy data on all reports to one consolidated sheet.

1. Click the "Combine Sheets"\u003e button\u003e Select "Combine these sheets with the same name on one sheet."
2. Check the "Tables with headlines" flag if so.
   Tip: So, the data will be combined under a single heading. This is convenient if the structure of the sheets that you merge are unlikely, for example, if the reports are created by one template. If the headers do not match, each data range will be added with its heading.
3. Choose sheets for combining by setting the appropriate checkboxes in the data tree.
   Tip: Instead of an alternate union of sheets with the same name by groups (one name group after another), you can choose them all at once. The add-in will automatically collect data on the same names of the tabs and will result in the appropriate individual sheets of a consolidated book.
4. Click the "Combine"\u003e button, all of the selected sheets with the same name are collected in the new summary book.

How the data is copied to the consolidated sheet

Data combining in essence means extracting and copying data from several source sheets to a new sheet.

• Data is copied completely - the entire range to the last used cell on the source sheet.
• Copy ranges are added sequentially, one range under the last string of the previous range.
• An add-in XLTools "Combine Sheets" preserves the formatting of cells and tables, links to cells, functions and formulas, combined cells, etc.
• The source data is not subject to change.

How to combine several sheets in one book

You can combine several sheets in one book using the XLTools add-in organizer of books. It helps to copy and manage multiple sheets at the same time.