==>《Excel数据透视表使用方法精要12点》

FENGJUN · 发表于 2004-6-22 16:28

总结了一下：姑称《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、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。

hxrmlc · 发表于 2004-6-22 16:31

谢谢你的帖子，我要好好看看

同时，顺便说一下，你英语很好，我也要向你学习

[此贴子已经被作者于2004-6-22 16:35:26编辑过]

丑陋裸男 · 发表于 2004-7-20 15:06

我们要牢记精要

camelwu · 发表于 2004-7-21 14:49

[em05]谢了，我正要学习它呢，来得很及时，同时也要谢谢楼主，承蒙推荐。

cello7788 · 发表于 2004-7-27 13:57

哪有数据透视表用法祥解？

kelchen · 发表于 2004-8-4 16:43

第一天有哪为大虾可以赐教？怎么按照（任意）时间周期进行分组？

来生缘 · 发表于 2004-8-4 17:21

还没弄懂数据透视表

fghgao · 发表于 2004-8-4 19:32

这些东西是从哪里得来的，

楼主继续贴啊！实在上不错！！正想学学数据透视表这方面的东西！

feima · 发表于 2004-8-4 20:48

我正需要按时间分组功能，真是及时雨！

wty2004 · 发表于 2004-9-10 18:48

是啊

		自动登录	找回密码
密码			免费注册

==>《Excel数据透视表使用方法精要12点》

评分