數(shù)據(jù)庫系統(tǒng)工程師：Excel數(shù)據(jù)透視表使用方法精要

1、Excel數(shù)據(jù)透視表能根據(jù)時間列和用戶自定時間間隔對數(shù)據(jù)進行分組統(tǒng)計，如按年、季度、月、日、一周等，即你的數(shù)據(jù)源表中只需有一個日期字段就足夠按照（任意）時間周期進行分組了；
　　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、通常，透視表項目的排列順序是按升序排列或取決于數(shù)據(jù)在源數(shù)據(jù)表中的存放順序；
　　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、對數(shù)據(jù)透視表項目進行排序后，即使你對其進行了布局調整或是刷新，排序順序依然有效；
　　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、內部行字段中的項目是可以重復出現(xiàn)的，而外部行字段項目則相反；
　　The report repeats items in an inner row field as necessary for each outer row field item.

6、通過雙擊透視表中匯總數(shù)據(jù)單元格，可以在一個新表中得到該匯總數(shù)據(jù)的明細數(shù)據(jù)，對其可以進行格式化、排序或過濾等等常規(guī)編輯處理；
決不會影響透視表和源數(shù)據(jù)表本身；
　　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點對源數(shù)據(jù)是外部數(shù)據(jù)庫的情況尤其有用，因為這時不存在單獨的直觀的源數(shù)據(jù)表供你瀏覽查閱；
　　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、如果源數(shù)據(jù)表中的數(shù)據(jù)字段存在空白或是其他非數(shù)值數(shù)據(jù)，透視表初始便以“計數(shù)”函數(shù)對其進行匯總（計算“計數(shù)項”）；
　　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.

更多軟考資料請訪問：考試吧軟件水平考試欄目

希望與更多網友交流，請進入考試吧軟件水平考試論壇