Dynamic cross Table

See also: Cross Table, , TCD, Pivot

A dynamic cross table (in English pivot counts ) is a functionality of some Tableur S which makes it possible to generate a Synthèse of a rough data table. That makes it possible to create workings of tables by choosing different the wanted fields, in X-coordinates or ordinates.

Home base is a beach of cells where each line corresponds to a recording (table top in the image). In the case of the example given in the image it is the beach " A1: E16". The first line of the beach is consisted of the titles of the fields (heading of the column S in fatty).

It is with these titles of fields, whether one selects or not, that the dynamic cross table is arranged (table of bottom). One makes slip the fields either into a column or in a line of the model. There exists an order of the fields in the lines and the columns.

A field to make slip will constitute the data of the cross table.

In the example on the image, the fields " fournisseur" and " prix" were put on line, the fields " Genre" and " taille" in column, in this order respectively and the field " Quantité" was put to constitute the data.
Nota: The sub-totals of the lines and the columns were decontaminated for more legibility of the table being used as example.

The table is known as Croisé because one puts data at the intersection (the " croisement") between fields laid out in line and column.

It is Dynamique, because one can bring changes to the table of the raw data, it is enough to ask " rafraîchir" so that the cross table is updated. One can also want to republish the position of the various fields, which will regenerate the table.

pivot counts has powerful dated summarization tool in Microsoft Excel, OpenOffice.org Calc, and other electronic Spreadsheet programs. Among other functions, it edge automatically leaves, count, and total dated stored in has spreadsheet and create has second table displaying the summarized dated. Pivot tables are useful to quickly create Crosstab S. To use sets up and exchanges the summary' S structure by dragging-and-dropping fields graphically. This " rotation" however pivoting off the summary table gives the concept its name. The name PivotTable has Trademark off Microsoft Corporation.

History

According to Bill Jelen, author off the book Pivot Table Dated Crunching , the concept that led to today' S pivot counts cam from Lotus Development Corporation with has revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salted. Salted realized that spreadsheets cuts patterns off dated. By designing has tool that could recognize thesis patterns, one could quickly build advanced dated models.

In has 2004 survey At MrExcel.com, fewer than 42% off Excel users make uses off the powerful features in pivot tables.

In OpenOffice.org Calc, the DataPilot provides similar functionality with drag-and-drop column fields inside the pivot counts.

With pivot counts edge Be graphically represented in has Pivot chart.

Explanation off has pivot counts

For typical dated entry and storage, dated is usually flat . Flat means that it consists off only columns and rows, such ace in the below example:

While there has batch off information stored in such dated, it is very difficult to gather the information you want out off it. With pivot edge help you quickly summarize the flat counts dated, giving it depth , and get the information you want. The use off has pivot counts is extremely broad and depend one the situation. The first question to ask is, " what amndt I looking for? ". In the example young stag, let' S ask " Does How many Units did we salt in each Region for every Ship Date? ":

With pivot counts usually consists off row , column , and dated (gold fact) fields. In this box, the row is Area , the column is Ship Dates , and the dated we would like to see is Units . Thesis fields were dragged onto the pivot counts from has list off available fields. Pivot tables also allow several kinds off aggregations including: Sum, Average, Standard Deviation, total Count, etc In this box, we wanted to see the number off units shipped, so we used has Sum aggregation.

How has pivot counts works

Using the example above, it will find all distinct records for Area . In this box, they are: North , South , East , West . Furthermore, it will find all distinct records for Ship Dates . Standard Based one the aggregation, Sum, it will summarize the fact, and display them in has multidimensional chart. In the example above, the first did not date is 66. This number was obtained by finding all records where both Region was East and Ship Dates was 1/31/2005 , and adding the Units off that collection off records together to get has final result.

Other programs

Although pivot tables are supported by Microsoft Excel and OpenOffice.org Calc, nap users may find to their off implementation it is lacking due to the generic design off thesis applications. Summon companies such ace NumberGo, Quantrix, and nap other spreadsheet software packages cuts filled this gap to simplify the uses pivot tables off.

Refer

Alexander, Michael and Bill Jelen: Pivot Table Dated Crunching (THAT, ISBN 0-7897-3435-4) June 2005
Call for Help (TV Show), Episode 236, originally aired 8/19/2005
U.S. & Trademark Office

External links

Online tutorials

has series off tutorials by John F. To release LLC
Introduction to pivot tables by Microsoft Business Solutions
Summary Tables

Books one pivot tables

Pivot Table Dated Crunching (Business Solutions) (ISBN 0-7897-3435-4)
has Supplements Guide to PivotTables: In Visual Approach (ISBN 1-59059-432-0) (in-depth review At slashdot.org)

--->

Integration of the dynamic cross tables in the principal spreadsheves

Under MS Excel

Under Excel, the dynamic cross table is generated thanks to an assistant called starting from small Données/Rapport of cross table dynamic.

Under OpenOffice.org

OpenOffice prefers to call dynamic cross table Pilote of data . One reaches the assistant by the menu Données / Pilote of data .

Thanks to a computer programming language

The dynamic cross tables can be implemented directly via a language of programming, like in the case of the language Oberon or via language of Macro using the objects of a spreadsheet.

Macro in VBA

It is possible to record macro in Visual BASIC for Applications in Excel during the generation of a dynamic cross table. The generated code can be published and modified to control the formation of the table.

It is necessary to start by selecting a beach of cells:
Range (" A1: E16"). Select

Then the table should be created. The associated object is called PivotTables:
ActiveWorkbook.PivotCaches.Add (SourceType: =xlDatabase, SourceData: = " Feuil1! R1C1: R16C5").CreatePivotTable TableDestination: =" " , TableName: = " Cross table dynamique2" , DefaultVersion: =xlPivotTableVersion10

One then gives a destination to this dynamic cross table:
ActiveSheet.PivotTableWizard TableDestination: =ActiveSheet.Cells (3, 1)
ActiveSheet.Cells (3, 1). Select

This PivotTables object is a container of the PivotFields objects, representing the added fields, which one can choose the properties:

.orientation (arguments: =xlRowField xlColumnField or xlHidden), to choose if the field is put in line, in column or not used,
.position (=1, 2, 3…), to choose the position of the field in the line or the column,
. Subtotals = Array (False, False, False, False, False,…), to mask the various sub-totals,

There exists finally a method .AddDataField making it possible to add a certain field (PivotFields) as data to the PivotTables object. One gives two arguments separated by a comma: the " Name of the champ" and xlSum,

Software allowing to handle the dynamic cross tables

office automation Continuations:

Microsoft Office (in the Excel module)
Open Office (in the module OOo Calc)

Software specialized

numberGo
Quantrix To model

See too

Data-processing decisional

Random links: