Tabloya Pivot di nav gelek rêzikên daneyê de

Formulasyona pirsgirêkê

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+Dawî, 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+Dawî 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.

Piştre li ser tabê Jimare (heke we Excel 2016 an derengtir heye) an li ser tabê Pirsa Hêzê (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 Gûherrandinî (Weşandin):

The Power Query Query Editor window should open on top of Excel. On the right side of the window on the panel Daxwaza Parametreyên delete all automatically created steps except the first – Kanî (Kanî):

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 Jimareby right-clicking a column heading and selecting Stûnên din jêbirin (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 Navê stûna orîjînal wekî pêşgir bikar bînin 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 Rêza yekem wekî sernav bikar bînin (Use first row as headers) tab Xane (Xane) and remove duplicate table headers from the data using a filter:

Save everything done with the command Girtin û barkirin - Girtin û barkirin li… (Girtî û Barkirin - Girtin û Barkirin…) tab Xane (Xane), and in the window that opens, select the option Tenê girêdan (Tenê Têkilî):

Everything. It remains only to build a summary. To do this, go to the tab Têxe - PivotTable (Têxe - Tabloya Pivot), vebijêrkê hilbijêre Use external data source (Use external data source)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 Hemî nûve bikin tab Jimare (Daneyên - Hemî nûve bikin).

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 YEKÎTÎ 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 pêşvebirinê (pêşdebir) an jî kurteya klavyeyê alt+F11. Then we insert a new empty module through the menu Têxe - Modul û koda jêrîn li wir kopî bikin:

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 pêşvebirinê (Pêşvebir - Macro).

Cons of this approach:

• 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à!

Têbînî teknîkî: 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;  

ber:

	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.

• Afirandina raporan bi PivotTables
• Di PivotTables de hesaban saz bikin
• What are macros, how to use them, where to copy VBA code, etc.
• Data collection from multiple sheets to one (PLEX add-on)