Data presentation skills-Pivot table (1)

A basic article (Concepts of pivot tables) 

03

      Some people say that a pivot table is a small database. Some people say that a pivot table can perform filtering, sorting, and category aggregation operations in sequence and generate a summary table, it is a concrete manifestation of the powerful data processing capabilities of Excel. Some people say that the pivot table is a program from the original creden to the registration books and the preparation of reports ......    These are true. The Pivot table does have these functions. To be precise, the pivot table is used to sum up information points from the Excel Data list, relational database files, or special fields in OLAP multi-dimensional data sets.Analysis tool. It is an interactive report that can quickly classify and summarize a large amount of data and select different elements in pages, rows, and columns at any time, in order to quickly view the different statistical results of the source data, you can also display and print the detailed data of the region you are interested in.

  The Pivot Table combines the advantages of data analysis, such as data sorting, filtering, and Classification summary, to easily adjust the method of Classification summary and flexibly display data features in multiple ways. A "PivotTable" can be used to convert various types of reports by moving the field positions with the mouse. In addition, the pivot table is one of the methods to solve the speed bottleneck of the function formula. Therefore, this tool is one of the most common Excel data analysis tools with the most comprehensive functions.

  2. Techniques for pivot tables   1. Sorting skills      A , Manually sort the PivotTableYou can manually sort the pivot table by using the following methods:
        1. Use toolbar buttons              
        2. Use the menu command to sort. (Select any cell in the sorting column --- data --- sort)
        3. Use the right-click shortcut menu to sort ordered commands.
        4. Use the mouse to quickly sort (select the cells to be sorted by dragging ).

  The disadvantage of manual sorting is that when the data source of the pivot table is changed, the pivot table needs to be refreshed. If you refresh the pivot table here, all the manual sorting results will not exist, you need to perform the operation again when you try again.

   B. Automatic Sorting of pivot tablesTo automatically sort data tables, use the following methods:
    1.Double-click the field name and use the "PivotTable field" function to sort the fields. Double-click the self-segment name --- "pivot table field" --- "advanced" to select based on their respective sorting requirements to achieve sorting from the surface.
   2. Use the PivotTable toolbar for automatic sorting, and select the PivotTable --- sort and list the top 10 items in the PivotTable toolbar.
   3. Sort row fields by column Field Data Order.
  

   C. sort by strokes

   We Chinese prefer to sort tables by pen. So let's see how to sort the table with strokes. The order of sorting by strokes is: the order in which the number of strokes is equal to the number of strokes, and the order in which the number of strokes is equal to the number of strokes. Match the font at the beginningStructure, first the left and right structures, then the upper and lower structures, and then the overall structure.
   1. Vertical sorting by stroke order. Choose "data"> "-sort"> "options"> "strokes" from the toolbar"
     2. Horizontal sorting by stroke order. The method is the same as above.

   D, Custom sorting
   Custom sorting mainly involves adding custom sequence. The method is: "tool" --- "option" --- "Custom sequence" --- enter the sequence you want to define --- "add ". The custom sequence is "data" --- "sort" --- "ascending/descending" --- Select the sequence you just created in "Custom sort order" --- "OK ".

    E. Others

    Another sorting function of the pivot table is to select the sequence of numbers you need and perform simple data analysis. For example, you can select the three most visited sites from a series of network connections, for example, you can select the site that contributes the first 15% of the traffic from thousands of connections, these can be achieved through the pivot table. Of course, in actual work, more Combinations need to be performed by individuals. For example, a new Pivot table can be formed based on the results of the pivot table. For example, the pivot table can be sorted in advance, use some of the results to form a custom sequence, and then sort it in the next step. It should be noted that, whether it is a pivot table or a custom sorting data column, there are requirements on the number of fields. The number of custom sequences in excel2003 should not exceed 1000, the number of Pivot table fields cannot exceed 30 thousand (I have not tested the number of fields). When I process more than data records at work, the pivot table cannot meet the requirements, an error is reported when you drag related fields ).

   2. project grouping skills

 Call of a field combination

  There are two methods to call field combination. One is to select a cell in the combination field and right-click the cell to call it. The other is to call it through the PivotTable toolbar. Note that different field types are displayed in different ways. For example, a combination selection dialog box is displayed for a date field, a numeric text field directly forms a new combination field such as data group 1 and Data Group 2 in the PivotTable.

 B. Project combination for different types of fields

  1) Date Field

  For date fields, Excel provides an automatic date project combination dialog box. You can perform related operations as needed. In the automatic date grouping dialog box, Excel provides a variety of options, such as step size: Wonderful/minute/hour/day/month/quarter/year. When only day is selected, you can also set other operations based on the number of days in the dialog box. For example, if we want to set the number of days in weeks, the number of days is set to 7. If we want to set the number of days as the date group unit, the number of days is set to 10. Note the following when you perform a pivot table on a date field: 1) You can select multiple step sizes to group data at the same time. In Excel, step size settings are sometimes not convenient to use, for example, if you want to calculate the data for the first or second months of two years from year 09 to year 10, the selected month cannot meet your requirements. Excel combines the data for the same period of two years by default, in this case, you need to select a month/year for grouping. 2) if there are some differences between the date formats of the grouping field, the date format must be adjusted in a unified manner or the date fields of different formats must be grouped separately.

 

 

  2) value/Text Field

  The Automatic combination of numeric fields is similar to that of date fields, and the expression is the same, but the step selection of the numeric field grouping dialog box is not as complicated as that of date fields, in addition, the processing steps are all same distance. If you process the value fields of the same distance, for example, the income group we often use in the survey is the same distance (less than 2000 Yuan, 2000--5000, 5000--10000, more than 10000 ), such grouping of non-same-distance fields can only be performed manually by grouping. Manual grouping often uses sorting and adding secondary columns to increase the grouping efficiency. For text fields, you can only group projects by combining them manually. The operations are the same as those for numeric date fields.

  CCancel a project group

  All things in the world are a cycle, and they end at the beginning. Sometimes, when the pivot table is used up and needs to be edited again, the Ungroup function is used. the Ungroup field of the Excel receipt Pivot table must appear in the pivot table to be canceled, sometimes a new field variable is generated when you add a group. The new field will appear in the Field List of the PivotTable. You can cancel the field combination by calling the reverse operation of the field combination, restore the original appearance of a PivotTable.

  FAQ on field combination failure

  Common Causes of field combination failure in a PivotTable are as follows: 1) when a group field contains blank cells, the simplest solution is to right-click a blank field and choose hide. 2) the Data Types of grouping fields are different. there are usually two solutions to this problem. One is to modify the field attribute so that the format is changed to the same; the second is to convert other groups by means of manual grouping; 3) address change or missing data source <prompt that the PivotTable is invalid>, the first way to solve this problem is to find its data source and then perform the relevant Pivot Table operations (a common way to find a pivot source is to use the reverse operation of the pivot table wizard, find the real data source ).

  

   3. PivotTable (graph) and PPT

   The results of our data presentation are often put in PPT. If you are still having to switch back and forth between EXCEL and PPT during your presentation, if you do not know how to put the Excel Pivot table in the PPT, you are out. Of course, it is not as easy as simply copying a pivot table (graph) into a PPT. The general steps are as follows:

 

 

Major defect: the data source needs to be transferred and transferred together with the demo environment of the PPT, and the related settings need to be re-configured every time the computer is changed

3.Other data display tools

   What should you do if you want to make your data display brighter? It is said that "image and text look" is the same thing. At this time, you can use a data perspective to implement your thoughts. Of course, in addition to the data perspective, there are no other methods for starting your chart and data diagram 1. The answer is yes. Of course, the premise is that you have processed the raw data with a PivotTable, and then imported the results into the crystal xcelsius (Chinese name: Crystal easy table) for related operations, which may make your PPT brighter, dynamic Display, alarm settings, dynamic combination of multiple graphics, etc. Today, due to limited time, I will not introduce it. I just post a very simple figure:

PS: Most of the content in this article is organized from the excelhome Forum. For more information about the crystal easy table, see this blog link.