Pivot table
Pivot Tables (English pivot [ pɪvət ], often in the German French [ pi.vo ] pronounced pivot, hinge, key figure, axis, also pivot table reports or pivottable reports ) provide a way to data in a table in represent various types and evaluate without having to change the source data or table ( s) here. A pivot table is due to the aggregation used in the data fields of the output data in a condensed, summarized form Represents the is indeed connected with loss of information, but therein lies the benefit of a pivot table. It is a tool to reduce large amounts of data to manageable sizes and perform simple evaluations. Pivot tables can be used for the propagation of data only for the query, not. The entries of a pivot table are either read-only or a change does not affect the underlying original data.
The term pivot for the concept was first proposed in 1991 by the company Brio Technology, San Francisco - United States used - in its product called Pivot Data.
Data sources
Data sources for pivot tables can create lists and databases from spreadsheets, but also tables from external database applications, such as within an Office package, be. Modern spreadsheets have to a special feature that allows a lot of similar data sets can be combined into groups and as a result provides a pivot table. In the popular office suites, a wizard ( Pivot Table Wizard or Data Pilot) helps in the creation of pivot tables.
Structure
A pivot table consists of several sections, each of which any fields ( column headings ) of the original data it can hold. Typically the required fields when creating the pivot table selected from a list drawn with the mouse in the desired range.
Structure
The distribution of the fields on the row and column fields determines the structure of the pivot table. Changing this division or order no more or less data is displayed, but this represented only in another form. Moving a row or column to another location field is referred to as the pivoting of this field. For example, swapping row and column fields yields (preserving their order ) a transposed table.
By double clicking on a cell in a pivot table groups are one and the hidden ( "drill - down" and " roll-up " ) to represent more or less detail. If the cell is to a data field, after the double-click all of the individual data sets from the original data that went into the calculation of this cell, shown on a separate worksheet.
After which fields are grouped and which fields are displayed, the user can freely choose and change interactively after creating the pivot table, without having to repeat the entire creation. When grouped according to two or more fields the pivot table can be displayed as a crosstab, which increases their clarity.
Example of " Group by field X " means that you, summarizing all the records from the original table, which have exactly the same content in the X column, each a single record in the result table. Thus, the result table contains all the different items that appear in the X column, but no entry more than once.
Are there mostly different values ( eg measured values with many significant digits ) in a column of data to be analyzed, a grouping by this column by the pivot table does not make sense. In this case you must define suitable classes themselves (eg, intervals) and as a new column to add the output data before. An exception are columns with dates that can be grouped in various programs directly in the pivot table according to days, months, quarters, etc..
Field types
The following areas can be distinguished:
Databases
The principle of pivoting is also based on the views in MOLAP databases. These non-rational databases are multidimensional. For data analysis two dimensions are in the view plane, the other dimensions are as shown in the Pivot view aggregated in the background.
Comparison with SQL
Pivot tables make it possible to carry out in a spreadsheet analyzes, which can be achieved in the database query language SQL with a group-by clause. With the usual features available in spreadsheets for the calculation of cell contents, such groupings are very limited or not possible.
The different types of fields in a pivot table correspond to specific parts of a SQL query:
- Row and column fields from pivot tables correspond to fields in the group-by clause.
- Data fields correspond to expressions in the select part of the SQL command. These expressions necessarily contain aggregation functions, such as the sum function.
- Page fields correspond to primitive conditions in the Having or Where clause of the SQL command.
Not all opportunities that SQL or the spreadsheet itself offer, are supported by pivot tables. For example, available in Microsoft Excel XP only some predefined aggregation functions are available; Median and 95 - % quantile missing about.
Database query programs are generally more flexible than pivot tables, that is, with SQL can be answered more questions. However, the query programs typically offer fewer opportunities for attractive presentation of results and are less comfortable to use. Many programs can not impersonate records crosstab and special formatting are crafted ( that is, without assistants and Controls) or not possible.
Special
For performance reasons, pivot tables in general are not automatically updated when any change of the output data, as is common with other functions in spreadsheets. The update must start it manually by the user.
The evaluation is carried out according to data types, it is for example the number and the text 12:03 '12 .03 ' or date 12:03 evaluated separately, which can be easily overlooked and leads to errors when the data base is not maintained consistently.
In some Office packages can ( German: Pivot chart ) pivot tables graphically interactive pivot chart represent.