"Perspective" as a verb, meaning rotation. If you view data as an object, the pivot table allows you to rotate the data to summarize it from different angles or points of view. Pivot Tables make it easy to move fields, swap field locations, and set up specific groups for creating projects.
If you give a strange object to identify you, you may look at it from different angles to get the answer. Working with pivot Tables is similar to studying a strange object. At this point, the object is your own data. Pivot Tables require multiple trials, so rotate and control the pivot table until you're satisfied. You'll be amazed at the results.
A pivot table is a summary report that allows users to quickly view various forms of data based on different classifications, different summaries, and more. To put it simply, it's a quick sort of aggregated data that's powerful in processing data!
1. Pivot Table Introduction
1-1. What is a
pivot table
Pivot Tableis an interactive way to quickly summarize large amounts of data. Use
Pivot TableYou can drill down into numerical data and answer some unexpected data questions.
Pivot Tableis designed specifically for the following purposes:
1) query large amounts of data in a variety of user-friendly ways.
2) Summarize and aggregate the numeric data, collect the data by classification and sub-classification, and create custom calculations and formulas.
3) Expand or collapse the data level to focus on the results, and view the details of the summary data for the area of interest.
4) Move rows to columns or move columns to rows (or "perspectives") to see different summaries of the source data.
5) filter, sort, group, and conditionally format the most useful and focused subset of data so you can focus on the information you need.
6) Provide concise, attractive and annotated online reports or printed reports.
1-2. Considerations for making
Pivot Tables :
Create with worksheet data
Pivot Table, these worksheet data must be a list of data. The so-called list of data is the top behavior field name (caption) of the worksheet data area, subsequent behavior data (records), and each column contains only a data region of one type of data. The data region of this structure is the equivalent of a database saved in a worksheet.
First, the Top behavior field name (title) of the data region.
Second, avoid having blank rows and empty columns in the list. Here, the so-called blank line, which means that there is no data in the columns of a row, if some columns of a row have no data, but the other columns have data, then the row is not a blank row. The same is true for empty columns.
Third, the columns contain only one type of data.
Four, avoid merging cells in the list of data.
Five, avoid entering a space at the beginning and end of the cell.
To avoid creating multiple lists in a worksheet, it is best to use only one list per sheet.
VII, the worksheet's list should have at least one empty column and one blank row between the other data to facilitate detection and selection of the list.
In the production
Pivot TableBefore, you should check the data area according to the above 7 points, if you do not meet the above requirements, you need to organize the worksheet data to standardize. (Not the standard example does not provide the table, as to how to deal with, presumably everyone should be more familiar with me, hehe)
2. Create pivot Table
Click any cell in the data source (benefit: By default, this ensures that
Pivot TableAbility to capture the range of data sources), insert-table-
Pivot Table, select the location of the pivot table, which is usually done by default on the new worksheet.
3. Move and delete pivot Tables
3-1. Move
pivot Table
When you need to move the
Pivot Table, the menu bar displays the
Pivot TableTools to automatically add options and Design tabs. On the Options tab, in the Actions group, select the mobile
Pivot Table”。
3-2. Delete
a PivotTable report
When you need to delete the
Pivot Table, on the Options tab, in the Actions group, click the arrow below Select, and then click the entire
Pivot Table, press the DELETE key to remove.
4. Add fields to the report
Pivot TableThe four regions in the field list are:
1) Report filtering-Adding a field to the report filter area allows the field to be included in the
Pivot TableFilter area so that its unique data items are filtered.
2) Column Labels-Add a field to the column label area to
Pivot TableThe top shows a unique value from the field.
3) Row Label-Add a field to the row label area to follow
Pivot TableThe entire area on the left shows a unique value from the field.
4) Value---Add a field "value" area, you can make the field contained in the
Pivot TableValue area and uses the value in the field to specify the calculation.
Here are the
Pivot TableTo add a field to an animated presentation in a report:
5. Change the field name
The final
Pivot TableEach field in the column and filter area inherits its name from the title of the source data, and the fields in the data section are given a name such as "Sum: XX". For example, in the actual operation, we may use "total" instead of the default name, directly click the field to enter a new name, if the input is
Pivot Table, the name will fail, and you can add a space at the beginning of the named field. Add a space, this change is just a decoration, the user will not notice the space in front of the name, but Excel thinks there are no spaces and there are spaces are different.
Excel2010 Pivot Table 1
