To create the PivotTable and get the correct results, you need to create a relationship between the two tables.Īfter you create the relationship, the PivotTable combines the data from the purchases table with the list of regions correctly, and the results look like this:Įxcel 2013 contains technology developed by Microsoft Research (MSR) for automatically detecting and fixing relationship problems like this one.Īutomatic detection checks new fields that you add to a workbook that contains a PivotTable. For example, you might have a table that lists each sales region, and another table that lists purchases for all regions. The problem is that the fields you have added to the PivotTable might be in the same workbook, but the tables that contain each column are not related. Have you ever created a PivotTable like this? You intended to create a breakdown of purchases by region, and so you dropped a purchase amount field into the Values area, and dropped a sales region field into the Column Labels area. While this flexibility is powerful, it also makes it easy to bring together data that is not related, leading to strange results. However, in Excel, you are free to import multiple tables and build your own connections between tables. PivotTables have traditionally been constructed using OLAP cubes and other complex data sources that already have rich connections between tables. Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 More.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |