What will you do if you need to create a pivot table and your data is on different sheets? With Excel 2013 installed, you have an easy way to do this. There is a technology called Data Model and it uses data relationships the way a database does.
In this tutorial, I will show you everything for creating a PivotTable in Excel 2013 from data in multiple sheets using the Data Model.
Video clip
If you want to follow the tutorial using your own Excel file, you can do so. Or download the zip file for this tutorial, which contains a sample book Pivot Consolidate.xlsx.
Data exploration
There are three worksheets in this book: customer information, order information, and payment information.
Click on the sheet Customer Info and make sure it contains order numbers and the name and status of the customers.
Customer Info sheet
Click on the sheet Order Info and see that it contains the order numbers, as well as the fields for the month, the items ordered, and whether those products are organic.
Order Info sheetGo to the Payment Info tab and verify that it contains the order numbers, the dollar amount of each sale, the method of payment, and how a new or existing customer places an order.
Payment Info sheetBy connecting all of these sheets in the PivotTable task pane, we can select data from each sheet. Since order numbers exist on all three sheets, they will become connection points. This is what the database calls primary key. Please note: it is not necessary to have a primary key, but it reduces the chance of error.
Creating named tables
Before creating a pivot table, let's create a table from each sheet.
Click back into Customer Table, then click anywhere within the data area. Go to the tab Insert in the ribbon bar and click the Table.
Convert the data in the worksheet by choosing Insert> TableThe Create Table dialog box correctly identifies the table area. The checkbox at the bottom should also identify that the first row of the table is for headers. (If not, choose this option.)
The Create Table dialog should correctly guess the data areaClick on OK and you now have a table with alternating strokes and filter buttons. You can click inside to deselect it if you want to see it better (just don't click outside the table). The ribbon panel also displays a tab Design for the table. On the left side of the tape in the box Table Name the temporary name Table1 is displayed. Delete this and name it Customer_Info(use an underscore instead of a space). Click on Enter.
Apply name to each tableRepeat these steps for the Order Info and Payment Info sheets. Name the tables Order_Info and Payment_Info.
We are now ready to insert the PivotTable.
Inserting a PivotTable
Make sure the cursor is somewhere in the table on the Payment Info sheet. Go back to the tab Insert ribbon and click the icon PivotTable(this is the very first icon).
Use the cursor inside one of the tables to select Insert> PivotTableIn the dialog box that appears, you need to correctly define the table and select so that the PivotTable goes to a new worksheet. Check the box at the bottom Add this data to the Data Model... Click on OK.
Adding data to the Data Model is what makes the connections workYou will now have a PivotTable in a new sheet, the taskbar will appear on the right side of the screen, and the Analyze tab will appear on the ribbon.
The taskbar displays the table and fields of only the active sheet, so click ALL to see all the tables you've created. But before we can use them, we have to link them to each other, which means creating a relationship. Click the button Relationships on the ribbon panel.
Setting up table relationships
Clicking this button displays the Manage Relationships dialog box. Click the button New and the Create Relationship dialog box appears. We will create two relationships using the Order # field as a connector.
From the drop-down lists, select Payment_Info for the table, and next to it select Order # in the Column dropdown. On the second line, select Customer_Info from the Related Table dropdown and next to it select Order # from the Related Column dropdown list.
There are three tables, so create two relationshipsThis means that the Payment_Info and Customer_Info tables are related to each other by the same order number.
Click the button OK and we will see these relationships listed in the Manage Relationships window.
Repeat this process to create a relationship between Payment_Info and Order_Info, also using the Order # field. Now the Manage Relationships window looks like this:
The Manage Relationships dialog displays the relationships you are creatingNote that there is no need to create a relationship between the Order_Info and Customer_Info tables, as they are automatically joined through the Payment_Info table.
Click the button Close at the bottom of the window. Now we can finally drag the fields into the PivotTable.
Inserting fields into PivotTable
In the ALL section of the taskbar, click the small arrows to expand the three tables to see their fields. Drag the fields in the PivotTable area as follows:
- State and Month into lines
- Product in columns
- $ Sale into values
- Status in filters
Now you can use and modify it like any other PivotTable.
Conclusion
Using new function Object Data Model in Excel 2013, you can select pink boxes from multiple sheets to create a single PivotTable. Keep in mind that the rows of each table must be related to each other in some way. You have a better chance of success when the tables share a common field with unique values.
If you are looking for good ways submission of its data, Envato Market has a good choice as well as to transform Excel data to web formats and vice versa.
This article will show you how to quickly combine data from two Excel tables when key columns no exact matches. For example, when the unique identifier from the first table is the first five characters of the identifier from the second table. All the solutions proposed in this article have been tested by me in Excel 2013, 2010 and 2007.
So, there are two Excel sheets that need to be combined for further data analysis. Suppose one table contains the prices (the Price column) and descriptions of the goods (the Beer column) that you are selling, and the second reflects the data on the availability of goods in the warehouse (the In stock column). If you or your colleagues compiled both tables in the catalog, then both must contain at least one key column with unique identifiers of goods. Product description or price may change, but the unique identifier always remains the same.
Difficulties begin when you get some tables from the manufacturer or from other departments of the company. The matter may become even more complicated if it is suddenly introduced new format unique identifiers or the smallest SKU changes. And you are faced with the task of uniting in Excel new and the old data table. One way or another, a situation arises when in key columns there is only a partial match of records, for example, “ 12345 " and " 12345-new_suffix“. It's clear to you that this is the same SKU, but the computer is not so quick-witted! This not an exact match makes it impossible to use regular Excel formulas to combine data from two tables.
And what is really bad - the correspondences can be completely fuzzy, and “ Some company"In one table can become" CJSC "Some Company""In another table, and" New company(formerly Some Company)" and " Old Company”Will also turn out to be a record about the same company. You know this, but how can Excel explain it?
Comment: The solutions described in this article are universal. You can adapt them for future use with any standard formulas such as VLOOKUP(VLOOKUP), SEARCH(MATCH), GPR(HLOOKUP) and so on.
Select the appropriate example to jump directly to the solution you want:
A key column in one of the tables contains additional characters
Consider two tables. The columns of the first table contain the SKU, the name of the beer (Beer) and its price (Price). The second table contains the SKU and the number of bottles in stock (In stock). Instead of beer, there can be any product, and the number of columns in real life there could be much more.
Create an auxiliary column in the table with additional characters. You can add it to the end of the table, but it is best to insert it next to the right after the key column so that it is visible.
The key in the table in our example is the column A with the SKU data, and you need to extract the first 5 characters from it. Let's add a helper column and name it SKU helper:
Ready! We now have exact match key columns - column SKU helper in the main table and column SKU in the table where the search will be performed.
Now using the function VLOOKUP(VLOOKUP) we get the desired result:
Other formulas
- Extract the first NS characters to the right: for example, 6 characters to the right from “DSFH-164900”. The formula will look like this:
RIGHT (A2, 6)
= RIGHT (A2,6) - Skip the first NS characters, extract the following Y characters: for example, you need to extract “0123” from the record “PREFIX_0123_SUFF”. Here we need to skip the first 8 characters and extract the next 4 characters. The formula will look like this:
PSTR (A2; 8; 4)
= MID (A2,8,4) - Extract all characters up to the delimiter, the length of the resulting sequence can be different. For example, you need to extract “123456” and “0123” from the entries “123456-suffix” and “0123-suffix”, respectively. The formula will look like this:
LEFT (A2; FIND ("-"; A2) -1)
= LEFT (A2, FIND ("-", A2) -1)
In short, you can use such Excel functions, how LEVSIMV(LEFT), RIGHT(RIGHT), PSTR(MID), FIND(FIND) to retrieve any portions of the composite index. If you have any difficulties with this, we will do our best to help you.
Data from the key column in the first table is split into two or more columns in the second table
Suppose the table being searched contains a column with identifiers. The cells of this column contain records of the form XXXX-YYYY, where XXXX Is the code designation of a group of goods ( mobile phones, TVs, camcorders, cameras), and YYYY- This is the product code within the group. The main table consists of two columns: one contains the codes of the product groups (Group), the second contains the codes of the products (ID). We cannot simply discard the codes of product groups, since the same product code can be repeated in different groups.
In a cell C2 we write down the following formula:
CONCATENATE (A2; "-"; B2)
= CONCATENATE (A2, "-", B2)
Here A2- this is the address of the cell containing the group code; symbol " – ”Is a separator; B2 Is the address of the cell containing the product code. Let's copy the formula to the rest of the lines.
Now it's easy to combine data from our two tables. We will match the column Full ID first table with column ID second table. If a match is found, the entries from the columns Description and Price the second table will be added to the first table.
The data in the key columns does not match
Here's an example: You are the owner of a small store, receiving goods from one or more suppliers. Each of them has its own nomenclature, which differs from yours. As a result, there are situations when your record “Case-Ip4S-01” matches the record “SPK-A1403” in Excel file received from the supplier. Such discrepancies arise at random and there is no general rule to automatically convert “SPK-A1403” to “Case-Ip4S-01”.
Bad news: The data contained in these two Excel spreadsheets, will have to be processed manually so that it will be possible to combine them in the future.
Good news: You only have to do this once, and the resulting auxiliary table can be saved for future use. Then you can combine these tables automatically and thus save a lot of time.
1. Create an auxiliary search table.
Create a new Excel sheet and call it SKU converter... Copy the entire column Our.SKU from the sheet Store to a new sheet, remove duplicates and leave only unique values in it.
Add a column next to Supp.SKU and manually looking for matches between the column values Our.SKU and Supp.SKU(the descriptions from the column will help us with this. Description). This is a boring job, let the thought that it only have to be done once pleases you :-).
As a result, we have the following table:
2. Update the main table with data from the lookup table.
Insert a new column into the main table (Store sheet) Supp.SKU.
Further, using the function VLOOKUP(VLOOKUP) compare sheets Store and SKU converter Our.SKU, and for updated data - the column Supp.SKU.
Column Supp.SKU filled with original manufacturer codes.
Comment: If the column Supp.SKU there are empty cells, then you need to take all the codes SKU corresponding to these empty cells, add them to the table SKU converter and find the corresponding code from the supplier table. Then we repeat step 2.
3. We transfer data from the lookup table to the main table
Our main table has a key column with an exact match to the elements of the lookup table, so now this task will not be difficult.
Using the function VLOOKUP(VLOOKUP) merge sheet data Store with sheet data Wholesale Supplier 1 using the column to find matches Supp.SKU.
Here is an example of the updated data in a column Wholesale Price:
It's simple, isn't it? Ask your questions in the comments to the article, I will try to answer as soon as possible.
Pivot tables are useful for analyzing data and creating reports with them. And if it's relational data (that is, data that is stored in separate tables, but can be combined due to shared values), you can create a pivot table like this in just a few minutes:
How is this pivot table different? Note that the list of fields on the right does not display a single table, but a whole set of tables. Each of these tables contains fields that can be combined into one pivot table to get different slices of the data. No manual formatting and data preparation required. Immediately after importing data, you can create a pivot table based on related tables.
To combine multiple tables in a PivotTable Field List:
Below is the procedure for importing multiple tables from a database SQL data Server.
Please note: the list of fields contains several tables. These are all the tables you selected during import. Each table can be expanded and collapsed to view its fields. Since the tables are linked, you can create a pivot table by dragging fields from any table to the area VALUES, LINE or COLUMNS.
Importing tables from other sources
Besides SQL Server, you can import tables from a number of other relational databases.
Relational databases are not the only data source that supports multiple tables in a PivotTable Field List. You can use tables in your workbook, or you can import data feeds and then integrate them with other data tables in your workbook. For all this unrelated data to work together, you need to add each table to the data model, and then create relationships between them using the corresponding field values.
Using the Data Model to Create a New PivotTable
You may have created relationships between tables in the Data Model and are now ready to use that data for analysis. The following describes how to create a new PivotTable or PivotChart using a data model in a workbook.
Very often, when generating reports (analytics) based on large amounts of data, you have to summarize information from several sheets of the Excel workbook into one table.
Ideally, to collect and analyze information from different Excel sheets, it is desirable to make a pivot table that will take data from several sources (book sheets).
This table can be done in two different ways.
Let's consider the first way.
Create a pivot table from different sheets using standard features and tools.
Step one.
You need to add a PivotTable and Chart Wizard button to the toolbar.
To do this, right-click on the ribbon (toolbar) and select the "Customize Ribbon" item from the drop-down menu
or enter the tab
File => Options => Customize Ribbon.
Next, in the ribbon settings, from the drop-down list under the "Select commands" label, select the "All commands" item. The window below will display all the possible commands that can be placed on the panel, in alphabetical order.
From the list, select "Pivot Tables and Charts Wizard"
In the right window, using the "Create group" button, create new group tools. For the group, you can choose a name convenient for you. For example, "Own group". You can choose on which tab the group will be created. In my example, I selected the Home tab.
When the group is created, select it with the cursor, highlight the "PivotTable and Chart Wizard" in the left window and click the "Add >>" button.
Then click "OK".
The PivotTable and Chart Wizard is now on the main tab of the toolbar.
Step two. Building a pivot table from multiple data sources.
Second way.
Creating a table that takes data from multiple sheets using a query generated in the Power Query add-in.
This method is to use a Power Query add-in query.
Create a Power Query to flatten multiple pages of a workbook into a single table.
Step one.
You need to create two queries, each of which will take information from a separate table.
Step two.
To do this, in the Power Query tab, you need to click the "From Table" button and specify in the window that appears, the range - the data source. Then click "OK".
Step three.
When the second query is created, you need to click on the "Merge Queries" button in the Power Query tab and configure the appearance of the resulting general table in the window that appears.
The data source for a pivot table is a list of data, where typically each column acts as a field in the pivot table. But what if a table that only looks like a pivot table came to you (it is formatted and looks like it, but it is impossible to use the tools for working with pivot tables). And you need to turn it into a list of data, i.e. perform the reverse operation. In this post, you will learn how to convert a pivot table with two variables to a list of data.
The figure shows the principle that I described. Those. the range A2: E5 is the original pivot table that is converted to a list of data (range H2: J14). The second table represents the same dataset, only in a different perspective. Each value in the original PivotTable appears as a string, consisting of a row field item, a column field, and their corresponding value. This display of data is useful when you need to sort and manipulate data in other ways.
In order to implement the ability to create such a list, we will use the pivot table tools. Let's add a button PivotTable Wizard to the quick access panel, which is not available to us on the tape, but remained as a rudiment from more early versions Excel.
Go to the tab File -> Options... In the dialog box that appears OptionsExcel, in the tab Panel quick access in the left margin, find the item PivotTable and Chart Wizard and add it to the right one. Click OK.
You now have a new icon on the Quick Access Toolbar.
Click on this tab to run PivotTable Wizard.
In the first step of the wizard, you must select the data source type of the pivot table. Installing the switch In multiple ranges of consolidation and click Further.
In step 2a, specify how you want to create the page margins. Put the switch Create Page Fields -> Next.
At step 2b, in the field Range select the range containing the data and click Add. In our case, this will be the location of the original pivot table A1: E4.
At the third step, you need to decide where you want to place the pivot table, and click the button Ready.
Excel will create a pivot table with data. On the left side of the screen, you will see the area List of PivotTable Fields. Remove all items from the row and column fields. I wrote in more detail about it in the previous article.
You will end up with a small one-cell pivot table that contains the sum of all the values in the original table.
Double click on this cell. Excel will create a new sheet that will contain a table with a list of values.
The headings of this table represent general information you probably want to make them more informative.
