Excel数据透视表使用方法精要12点

QIANCHENG

总结了一下：姑称《Excel数据透视表使用方法精要12点》


1、Excel数据透视表能根据时间列和用户自定时间间隔对数据进行分组统计，如按年、季度、月、日、一周等，即你的数据源表中只需有一个日期字段就足够按照（任意）时间周期进行分组了；。
If you look at the Source Data worksheet, you'll notice that there is no Quarters column. The Quarters field was created from the Order Date
field by grouping the dates in the field, using the Group and Show Detail commands on the shortcut menu. Excel allows you to group dates in
several ways including by year, month, or day. You can also use seven-day groups to group by week.


2、通常，透视表项目的排列顺序是按升序排列或取决于数据在源数据表中的存放顺序；
Initially, the items in a field are either sorted in ascending order or displayed in the order received from the source database, depending
on the type of source data you use. For example, in the reports on the Order Amounts and Orders by Country worksheets, the names of the
salespersons are alphabetized.


3、对数据透视表项目进行排序后，即使你对其进行了布局调整或是刷新，排序顺序依然有效；
Once you use this command to sort a field, the field retains the sort order even if you rearrange or update the report.


4、可以对一个字段先进行过滤而后再排序；
you can display a field both filtered and sorted if you want.


5、内部行字段中的项目是可以重复出现的，而外部行字段项目则相反；
The report repeats items in an inner row field as necessary for each outer row field item.


6、通过双击透视表中汇总数据单元格，可以在一个新表中得到该汇总数据的明细数据，对其可以进行格式化、排序或过滤等等常规编辑处理；决不会影响透视表
和源数据表本身；
You can easily list the records from the source data that are summarized in a particular data cell, just by double-clicking the cell. Excel
creates a new worksheet like this one with a copy of the data. You can format, sort, and filter this detail data without affecting the
PivotTable report or the original source data.


7、以上第6点对源数据是外部数据库的情况尤其有用，因为这时不存在单独的直观的源数据表供你浏览查阅；
This feature is available for most types of source data, and is particularly useful with source data taken from external databases, where you
don't have a separate Excel source data worksheet to view.


8、透视表提供了多种自定义（计算）显示方式可以使用；
Excel provides several types of custom calculations for data fields. To see what's available, double-click the Percent of Order Total field
and look at the Show data as dropdown list.


9、如果源数据表中的数据字段存在空白或是其他非数值数据，透视表初始便以“计数”函数对其进行汇总（计算“计数项”）；
When the data in a field includes blanks and other values that aren't numbers, Excel initially uses Count to summarize the data.
You can easily change the fields to total the amounts instead of counting: double-click the field and click Sum under Summarize by.


10、透视表在进行TOP 10排序时会忽略被过滤掉的项目，因此在使用此功能时要特别注意；
If you had previously used the dropdown list for the Customer field to hide some customers, when you set the Top 10 AutoShow options, Excel
omits the hidden items from the calculations.


11、在一个透视表中一个（行）字段可以使用多个“分类汇总”函数；


12、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。

youfang

路过，学习下