Create an intelligent pivot table in Excel PDF

Preview

Summary

Download Create an intelligent pivot table in Excel PDF

Description

8/3/2019

Create an intelligent pivot table in Excel | XLSTAT English  Login (/customer/en/login?b_id=9283&return_to=%2Fcustomer%2Fen%2Fportal%2Farticles%2F2062252-create-an-intelligent-pivot-table-in-excel%3Fb_id%3D9283)  

XLSTAT Support Center 

 Home (https://help.xlstat.com) / Tutorials (https://help.xlstat.com/?b_id=9283) / Describing data (/customer/en/portal/topics/824595-describing-data/articles?b_id=9283) / Create an intelligent pivot ta...

Create an intelligent pivot table in Excel

2017-10-20

This tutorial shows how to use the XLSTAT intelligent pivot table (https://www.xlstat.com/en/solutions/features/pivot-table) feature, which has substantial advantages compared to the classic Excel pivot tables.

Dataset to create an intelligent pivot table An Excel sheet (zipped le) with both the data and the results can be downloaded by clicking here (https://help.xlstat.com/customer/portal/kb_article_attachments/59949/original.xls?1437066374). The data were collected during the 1994 Census by the American Census Bureau (http://www.census.gov (http://www.census.gov)). This dataset has been used several times by statisticians to evaluate the predictive performance of new algorithms. Each record contains 8 descriptors about an individual, like age, occupation, education, sex, etc. The number of records has been limited to 8000. The weight variable (allowing each individual to represent a certain percentage of the population) is not used in the example below.

Goal of this tutorial The goal is here to quickly build a pivot table and a contribution chart that will help the user to understand which factors and combination of factors most inuence the fact that an individual has a revenue greater or lower than 50k$ (the corresponding variable is in column J). XLSTATenables to quickly and easily do this.

Generating an intelligent pivot table Once XLSTAT is open, select the XLSTAT / Describing data/ Pivot command, or click on the corresponding button of the Describing datamenu (see below).

https://help.xlstat.com/customer/en/portal/articles/2062252-create-an-intelligent-pivot-table-in-excel?b_id=9283

1/5

8/3/2019

Create an intelligent pivot table in Excel | XLSTAT

Once you have clicked on the button, thePivot dialog box appears. Select the data on the Excel sheet. As the rst row corresponds to the labels, and as the next rows correspond to data, it is possible to use the quickest selection mode of XLSTAT: select directly columns by clicking on the corresponding letters. Select the Labels included option as the rst row corresponds to the name of the variables. Note that the explanatory and response variables can be either qualitative or quantitative variables. As the variable to explain is a qualitative variable, select qualitative for the type of variable. Then select the target modality to be used in the pivot table. In our case, we focus on the ">50K" case.

Then click on Ok so that XLSTAT-Pivot can start the computations. Pivot algorithm is based on classiaction trees and the CHAID algorithm. The next dialog box displays the options for creating the optimal pivot tables. Select the variables which you want to use in the pivot tables. The contributions of the variables to the model are displayed next to the variable name (the higher the contribution, the more information it brings to explain the variability of the explanatory variable). Once you are satised with the selection (in this example we did not change anything to the default options), click on Continue.



Interpreting an intelligent pivot table A new sheet is displayed with a histogram of the contributions of the variables, and a dynamic pivot table.

https://help.xlstat.com/customer/en/portal/articles/2062252-create-an-intelligent-pivot-table-in-excel?b_id=9283

2/5

8/3/2019

Create an intelligent pivot table in Excel | XLSTAT

The chart conrms that the variables that have the highest eect on the revenue are the Marital status followed by the number of years of education. The dynamic pivot table can display up to 4 values for each combination of categories: Target average: Percentage of the cases where the target category of the response variable is present in the case of a qualitative variable; average of the target variable calculated on the sub-population corresponding to the combination in the case of continuous variable; Target size: Count of the occurrences of the target category for the response variable in the case of qualitative variable; Population size %: Percentage of the overall population corresponding to the combination; Population size: Population size corresponding to the combination. The pivot table is as follows: 

We should now analyze the dynamic pivot table, to identify the combinations that most inuence the fact that the people earn more than 50k$. Note that once you have a pivot table, it might be interesting to do a correspondence analysis o to see how the categories of the various explanatory variables are related to each other. To build the input table, keep only the "Target size" values.

 Getting Started XLSTAT rst steps and statistical learning resources

https://help.xlstat.com/customer/en/portal/articles/2062252-create-an-intelligent-pivot-table-in-excel?b_id=9283

3/5

8/3/2019

Create an intelligent pivot table in Excel | XLSTAT (https://help.xlstat.com/?b_id=9202)

 Tutorials The place where you will master all of XLSTAT features (https://help.xlstat.com/?b_id=9283)

 Ideas Any feature or option you’d like us to develop? (http://ideas.xlstat.com/)

 Videos For those of you who prefer video help (https://www.youtube.com/channel/UCHvhQQxGqVepBRELIUeGZew)

Contact Us (/customer/portal/emails/new?b_id=9283) (/customer/portal/emails/new?b_id=9283) (/customer/portal/emails/new?b_id=9283)

(/customer/portal/emails/new?b_id=9283)Email Sales (https://www.xlstat.com/en/contact) Contact our technical support team: [email protected]

https://help.xlstat.com/customer/en/portal/articles/2062252-create-an-intelligent-pivot-table-in-excel?b_id=9283

4/5

8/3/2019

Create an intelligent pivot table in Excel | XLSTAT

The most complete statistical add-in for Microsoft Excel.

 (https://www.linkedin.com/groups/XLSTAT2160205?   gid=2160205&mostPopular=&trk=tyah&trkInfo=tarId%3A1415606346733%2Ctas%3Axlst    1-1) (https://plus.google.com/+xlstat/posts) (https://www.youtube.com/channel/UCHvhQQxGqVepBRELIUeGZew) (http://funkystats.tumblr.com/) (https://www.facebook.com/Xlstat) (https://twitter.com/XLSTAT)

Solutions XLSTAT BASE XLSTAT PREMIUM XLSTAT BIOMED XLSTAT MARKETING XLSTAT ECOLOGY XLSTAT QUALITY XLSTAT SENSORY XLSTAT FORECAST XLSTAT PSY

Links HOME PRODUCTS PRICES ORDER NEWS TRAINING & CONSULTANCY

Copyright © 2017 Addinsoft. All Rights Reserved Customer service software (http://www.Desk.com) powered by Desk.com

https://help.xlstat.com/customer/en/portal/articles/2062252-create-an-intelligent-pivot-table-in-excel?b_id=9283

5/5...