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

1、Excel數(shù)據(jù)透視表能根據(jù)時(shí)間列和用戶自定時(shí)間間隔對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)，如按年、季度、月、日、一周等，即你的數(shù)據(jù)源表中只需有一個(gè)日期字段就足夠按照（任意）時(shí)間周期進(jìn)行分組了；
　　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、通常，透視表項(xiàng)目的排列順序是按升序排列或取決于數(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、對(duì)數(shù)據(jù)透視表項(xiàng)目進(jìn)行排序后，即使你對(duì)其進(jìn)行了布局調(diào)整或是刷新，排序順序依然有效；
　　Once you use this command to sort a field, the field retains the sort order even if you rearrange or update the report.

4、可以對(duì)一個(gè)字段先進(jìn)行過濾而后再排序；
　　you can display a field both filtered and sorted if you want.

5、內(nèi)部行字段中的項(xiàng)目是可以重復(fù)出現(xiàn)的，而外部行字段項(xiàng)目則相反；
　　The report repeats items in an inner row field as necessary for each outer row field item.

6、通過雙擊透視表中匯總數(shù)據(jù)單元格，可以在一個(gè)新表中得到該匯總數(shù)據(jù)的明細(xì)數(shù)據(jù)，對(duì)其可以進(jìn)行格式化、排序或過濾等等常規(guī)編輯處理；
決不會(huì)影響透視表和源數(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點(diǎn)對(duì)源數(shù)據(jù)是外部數(shù)據(jù)庫(kù)的情況尤其有用，因?yàn)檫@時(shí)不存在單獨(dú)的直觀的源數(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、透視表提供了多種自定義（計(jì)算）顯示方式可以使用；
　　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ù)，透視表初始便以“計(jì)數(shù)”函數(shù)對(duì)其進(jìn)行匯總（計(jì)算“計(jì)數(shù)項(xiàng)”）；
　　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、透視表在進(jìn)行TOP 10排序時(shí)會(huì)忽略被過濾掉的項(xiàng)目，因此在使用此功能時(shí)要特別注意；
　　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.

更多軟考資料請(qǐng)?jiān)L問：考試吧軟件水平考試欄目

希望與更多網(wǎng)友交流，請(qǐng)進(jìn)入考試吧軟件水平考試論壇