Hevitra ato Anatiny
Famolavolana ny olana
Pivot tables are one of the most amazing tools in Excel. But so far, unfortunately, none of the versions of Excel can do such a simple and necessary thing on the fly as building a summary for several initial data ranges located, for example, on different sheets or in different tables:
Before we start, let’s clarify a couple of points. A priori, I believe that the following conditions are met in our data:
- Tables can have any number of rows with any data, but they must have the same header.
- There should be no extra data on the sheets with source tables. One sheet – one table. To control, I advise you to use a keyboard shortcut Ctrl+Tapitra, which moves you to the last used cell in the worksheet. Ideally, this should be the last cell in the data table. If when you click on Ctrl+Tapitra any empty cell to the right or below the table is highlighted – delete these empty columns to the right or rows below the table after the table and save the file.
Method 1: Build tables for a pivot using Power Query
Starting from the 2010 version for Excel, there is a free Power Query add-in that can collect and transform any data and then give it as a source for building a pivot table. Solving our problem with the help of this add-in is not difficult at all.
First, let’s create a new empty file in Excel – assembly will take place in it and then a pivot table will be created in it.
Avy eo eo amin'ny tabilao Data (raha manana Excel 2016 na aoriana) na eo amin'ny tabilao Fanontaniana momba ny herinaratra (if you have Excel 2010-2013) select the command Create Query – From File – Excel (Get Data — From file — Excel) and specify the source file with the tables to be collected:
In the window that appears, select any sheet (it doesn’t matter which one) and press the button below Change (Hanova):
The Power Query Query Editor window should open on top of Excel. On the right side of the window on the panel Mangataka paramètre delete all automatically created steps except the first – Source (Source):
Now we see a general list of all sheets. If in addition to data sheets there are some other side sheets in the file, then at this step our task is to select only those sheets from which information needs to be loaded, excluding all the others using the filter in the table header:
Delete all columns except column Databy right-clicking a column heading and selecting Fafao ny tsanganana hafa (Remove other columns):
You can then expand the contents of the collected tables by clicking on the double arrow at the top of the column (checkbox Ampiasao ny anaran'ny tsanganana tany am-boalohany ho toy ny prefix you can turn it off):
If you did everything correctly, then at this point you should see the contents of all tables collected one below the other:
It remains to raise the first row to the table header with the button Ampiasao ho lohateniny ny andalana voalohany (Ampiasao ho lohateniny ny laharana voalohany) vakizoro Home (Trano) and remove duplicate table headers from the data using a filter:
Save everything done with the command Akatona ary asio - Akatona ary ampidiro ao… (Manakatona & Ento - Akatona & Ento mankany…) vakizoro Home (Trano), and in the window that opens, select the option Fifandraisana ihany (Fifandraisana Ihany):
Everything. It remains only to build a summary. To do this, go to the tab Ampidiro - PivotTable (Ampidiro - Table Pivot), choose the option Mampiasà loharano angona ivelany (Mampiasà loharano angona ivelany)and then by clicking the button Select connection, our request. Further creation and configuration of the pivot occurs in a completely standard way by dragging the fields we need into the rows, columns and values area:
If the source data changes in the future or a few more store sheets are added, then it will be enough to update the query and our summary using the command Havaozy daholo vakizoro Data (Angona - Havaozy daholo).
Method 2. We unite tables with the UNION SQL command in a macro
Another solution to our problem is represented by this macro, which creates a data set (cache) for the pivot table using the command FIRAISAN-KINA SQL query language. This command combines tables from all specified in the array SheetNames sheets of the book into a single data table. That is, instead of physically copying and pasting ranges from different sheets to one, we do the same in the computer’s RAM. Then the macro adds a new sheet with the given name (variable ResultSheetName) and creates a full-fledged (!) summary on it based on the collected cache.
To use a macro, use the Visual Basic button on the tab Developer (mpamorona) na hitsin-dàlana fitendry Alt+F11. Then we insert a new empty module through the menu Ampidiro - Module ary kopia ity code manaraka ity any:
Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'sheet name where the resulting pivot will be displayed ResultSheetName = "Pivot" 'an array of sheet names with source tables SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'we form a cache for tables from sheets from SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1)) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS.Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString ) End With 're-create the sheet to display the resulting pivot table On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivot. Name = ResultSheetName 'display the generated cache summary on this sheet Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Select End With End Sub The finished macro can then be run with a keyboard shortcut Alt+F8 or the Macros button on the tab Developer (Developer - Macros).
Ny tsy fahampian'ity fomba ity:
- The data is not updated because the cache has no connection to the source tables. If you change the source data, you must run the macro again and build the summary again.
- When changing the number of sheets, it is necessary to edit the macro code (array SheetNames).
But in the end we get a real full-fledged pivot table, built on several ranges from different sheets:
Voilà!
Fanamarihana ara-teknika: if you get an error like “Provider not registered” when running the macro, then most likely you have a 64-bit version of Excel or an incomplete version of Office is installed (no Access). To fix the situation, replace the fragment in the macro code:
Provider=Microsoft.Jet.OLEDB.4.0;
ho:
Provider=Microsoft.ACE.OLEDB.12.0;
And download and install the free data processing engine from Access from the Microsoft website – Microsoft Access Database Engine 2010 Redistributable
Method 3: Consolidate PivotTable Wizard from Old Versions of Excel
This method is a little outdated, but still worth mentioning. Formally speaking, in all versions up to and including 2003, there was an option in the PivotTable Wizard to “build a pivot for several consolidation ranges”. However, a report constructed in this way, unfortunately, will only be a pitiful semblance of a real full-fledged summary and does not support many of the “chips” of conventional pivot tables:
In such a pivot, there are no column headings in the field list, there is no flexible structure setting, the set of functions used is limited, and, in general, all this is not very similar to a pivot table. Perhaps that is why, starting in 2007, Microsoft removed this function from the standard dialog when creating pivot table reports. Now this feature is only available through a custom button PivotTable Wizard(Pivot Table Wizard), which, if desired, can be added to the Quick Access Toolbar via File – Options – Customize Quick Access Toolbar – All Commands (File — Options — Customize Quick Access Toolbar — All Commands):
After clicking on the added button, you need to select the appropriate option at the first step of the wizard:
And then in the next window, select each range in turn and add it to the general list:
But, again, this is not a full-fledged summary, so don’t expect too much from it. I can recommend this option only in very simple cases.
- Mamorona tatitra miaraka amin'ny PivotTables
- Mametraha kajy ao amin'ny PivotTables
- What are macros, how to use them, where to copy VBA code, etc.
- Data collection from multiple sheets to one (PLEX add-on)