参加EH培训的心得和体会

sjz76meizi · 发表于 2010-5-7 18:04

在数据透视表中排序
1. 手动排序数据透视表：
a.使用工具栏按钮排序：a-z或z-a
b.使用菜单命令按钮：数据-排序-排序依据（升或降）-排序（标题）-确定
c.右键快捷菜单的顺序命令：鼠标右键-顺序-移至首端（向右）
d.利用鼠标快速排序：单击字段-将鼠标定位到边框（当鼠标出现小黑十字架时）用鼠标拖动字段到你想要的位置
2. 自动排序数据透视表：
a.双击字段名称设置自动排序：双击字段名称-高级-自动排序（升或降）-使用字段-确定
b.利用数据透视表工具栏自动排序：单击字段名称-数据透视表下拉列表-排序并列出前十个-自动排序（升或降）-使用字段-确定
c.根据列字段数据顺序对行字段进行排序：双击字段名称（列字段）-高级-自动排序（升或降）-使用字段（行字段）-确定
3. 按笔画顺序对数据透视表进行排序：
a.按笔画顺序纵向排序（页字段）
b.按笔画顺序横向排序（行字段）
4. 自定义顺序排序：
添加选项：工具-选项-自定义序列（输入序列或从单元格中导入注（用逗号或回车分开））-添加-确定
然后单击数据-排序-自动排序（升或降）-选项-自定义选项（添加内容）-确定
5. 利用数据透视表排序进行数据分析：
a.显示最大值前几名：双击字段名称-高级-自动排序（升或降）-使用字段（行字段）-自动显示前十项（打开）-显示（最大）（想要几输入几）-使用字段（行字段）-确定或者单击鼠标右键-字段设置-高级（同上）
b.显示最小几种：：双击字段名称-高级-自动排序（升或降）-使用字段（行字段）-自动显示前十项（打开）-显示（最小）（想要几输入几）-使用字段（行字段）-确定
c.如何显示%的数据（自动筛选）单击数据透视表右侧列字段最相邻的单元格-选中数据透视表的数据字段（列字段）-数据-筛选-自动筛选.打开筛选按钮-前十个-显示（最小）-百分比-确定
d.对整个透视表进行筛选-选中整个标题行-数据-（同上）利用筛选下拉按钮-自定义-大于（小于….）-（输入自己想要的数）-确定
工作中应用详见附件：我运用了高级筛选和自定义序列

[ 本帖最后由 sjz76meizi 于 2010-8-11 08:55 编辑 ]

livewire · 发表于 2010-5-7 18:13

请发到培训中心相关专题帖下！

Kimez12 · 发表于 2010-5-11 20:57

这次培训的新人水王有点多哦。

sjz76meizi · 发表于 2010-5-19 17:37

数据透视表的项目分组：

一：针对不同数据类型的字段进行项目组合：
1.日期型：
自动组合：方法一：鼠标右键—组及显示明细数据—组合—步长（需要组合的项）--确定
（做到这里发现一个难题，不会截图，从来没用过，网上搜索一下，恶补一下）

注意：如果只对月进行组合会把所有年中的月全部组合，所以一般情况下是对年和月同时选中进行组合
方法二：点击透视表工具栏中的工具栏选项按钮勾选组合和取消组合

取消组合：将组合字段拖回透视表字段列表（或单击工具栏中的取消组合按钮）
如何按旬来组合日期项目：
组合按钮—步长（日）--天数（10）--确定

2.组合数值型项目：
按等距步长组合：组合—步长（需要的数）--确定
按不等距步长组合：（手动组合）首先进行排序选中区域—组合
操作动画见附件：（题外话：不会在word里面插入动态图片）

按不等距步长进行自动组合：在数据源中添加辅助列，在这里新学了一个公式lookup,简单实用，以前我用的都是if函数，同一个结果两个不同的公式和大家分享一下（以前我用的【=IF(LEFT(Q2,1)="6","宅急送",IF(LEFT(Q2,4)="3110","顺丰",IF(LEFT(Q2,1)="E","邮局",IF(LEFT(Q2,3)="UGO","优购",IF(LEFT(Q2,2)="自送","自送")))))】现在学了lookup公式【=LOOKUP(Q1,{6,"3110","E","UGO","自送"},{"宅急送","顺丰","邮局","优购","自送"})】
不等距组合日期：a手动组合b在数据源中更改日期类型
3.组合文本型项目：唯一的方法是运用手动方式进行组合：（首先进行排序）选中区域—组合
取消组合：局部取消组合和全部取消组合
局部取消：选中单一组合—取消组合
全部取消组合：透视表字段列表中组合字段项消失

二：数据类型的不一致导致无法进行组合：
1. 空白单元格：修改数据源，重新做表
2. 数据类型不同：修改成统一的数据格式
3. 日期格式不同：转换数据类型将文本---数字，这里讲了一个type函数[=type(a1)],如果检测结果是1代表的是数字，如果检测结果是2代表文本（注：这里我们是用眼睛无法直接观察出来的）
4. 引用区域失效：检查数据源是否存在，用透视表向导检查数据源并重新指定，

aboo · 发表于 2010-5-19 20:49

来MARK一下，马上就要参加透视表初级班啦

ymh282 · 发表于 2010-5-19 21:05

在学数据透视表，但还没到楼主上面的知识，我收藏了，谢谢

sjz76meizi · 发表于 2010-6-11 16:24

在数据透视表中进行计算

1. 多汇总方式进行计算（11种）
单击鼠标右键---字段设置----汇总方式（求和，计数，最大值，最小值，平均值，乘积，数值计数等）
注：可以对同一字段进行多次汇总方式计算
2. 自定义显示方式（9种）
单击列字段，选择“字段设置”---选项---数据显示方式的下拉列表（普通，百分比，差异等）
三种常见的方式：
a差异---此显示方式需要选择基本字段和基本项，是一个定量的分析
b百分比----基准字段选择基本字段和基本项，然后是在基本项的基础上计算超额还是不足，是一个比率分析
c按某一字段汇总
如须对某一字段进行排名时，可按下列方法操作：数据透视表工具栏→公式→计算字段，会出现插入计算字段对话框，定义字段的名称，在公式一栏中填写“＝1”，这样新建了一个计算字段，将鼠标移到计算字段上，右键单击→字段设置→选项→数据显示方式中选“按某一字段设置”，下方选中字段，确定，即可在数据透视表中进行对某字段进行排名。
3. 添加计算字段和计算项
计算字段在数据透视表的下拉列表里选择公式---计算字段（是通过已有的字段进行数据计算来获得新的字段）
计算项是通过某字段项下，对不同类型的数据项进行计算获得的项
4. 常见问题分析
A为什么在计算字段中使用了match等函数却得不到计算结果？
因为在数据透视表中的计算字段中可以使用简单的函数运算（如sum,if等），但透视表的计算是在透视表缓存中进行的，因此不能使用函数对单元格进行应用或定义名称等
B为什么添加了由“单价”*“数量”得到的总金额新字段后，得到的合计数计算不正确
因为透视表总计结果总是先进行求和再进行其他运算，因此在计算总金额时是先将“单价”和“数量”先进行求和在进行乘积，因此计算结果不正确
C 为什么数据透视表不能进行行总计
因为透视表的行总计仅能计算一维表数据项形成的列字段数据，而对于二维表的多列字段形成的列字段数据则需要通过添加计算字段进行求和或其他方式来实现

sjz76meizi · 发表于 2010-6-16 17:58

VBA简介
VBA全称是Visual Basic for Application,它是Microaoft Visual Basic 的应用程序版本，它使Excel 形成了独立的编程环境
VBA的应用
1. 自动执行重复的操作
2. 进行复杂的数据分析对比
3. 生成报表和图表
4. 个性化用户界面
5. OFFICE组件的协同工作
6. EXCEL的二次开发
EXCEL对象模型
对象代表应用程序中的元素，EXECL中工有100多个对象，其中比较常用的有工作薄，工作表，单元格，图表和用户窗体等，全部对象以分层结构（或树根状结构）组织在一起构成对象模型。

注意：虽然单元格后面带s，但它并不是一个对象集合，它只代表工作表中的单个单元格对象。

注意的两点：
1在对象模型中，同一个对象可以出现在不同的地方，如数据透视表字段对象。
2集合的名字，一般是相应的对象名称后面加s，但不总是，如CalculateFields集合。

第二部分
如何引用数据透视表
1. 使用名称引用对象-----数据透视表名称
Sheets("Sheet1").PivotTables("PvtOnSheet1").RowFields(1).Name                               2、使用索引引用对象----数据透视表的索引号
Debug.Print Sheets("Sheet1").PivotTables(1).Name
3、使用<range对象>.Pivottable
Sheets("Sheet1").Cells(1, "A").PivotTable.Name
Sheets("Sheet1").Range("N1").PivotTable.Name
Sheets("Sheet1").Cells(11, 14).PivotTable.Name
4、利用对象变量对保留数据透视表对象的引用
Dim pvt as pivottable
Set Pvt = Sheets("Sheet1").PivotTables(1)
Pvt.Name
Pvt.Parent.Name
调试方法：工具栏---调试---逐语句（或F8）语句变成黄色，等待执行
思考题：从引用数据透视表对象的角度来考虑，为什么在同一个工作表中的多个数据透视表不能同名，而在同一个工作薄中可以存在同名的数据透视表
第三部分
利用VBA创建数据透视表
1. 录制宏
工具-------宏------------录制新宏，录制完成后按停止录制。
2、查看代码：
工具-------宏-------Visual Basic编辑器
3、  通过透视表向导来创建，有一个固定模式
Sub CreatPivotTable1()
Dim myPvt As PivotTable
Sheets("数据源").Range("A1").Select
Set myPvt = ActiveSheet.PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=Sheets("数据源").Range("A1:F1044"))
myPvt.AddFields RowFields:="部门", ColumnFields:="月"
myPvt.AddDataField Field:=myPvt.PivotFields("发生额"), Caption:="月发生额", Function:=xlSum

4、创建数据透视表的两个方法：
1）使用PivotTableWizard方法创建数据透视表
2）利用Pivotcache对象的Createpivottable方法创建数据透视表。

5、数据透视表缓存：
数据透视表的数据缓存是计算机内存中的一个指定区域用于存储报表数据。
共享数据缓存（基于同一个单元格区域或数据连接的两个或多个数据透视表之间自动共享缓存）
可以实现同时刷新。
优势：提高性能的同时减小了工作簿的大小。

下列情况不能共享数据缓存：
1）不希望所有数据透视表中都显示计算字段和计算项
2）不希望在所有数据透视表中按同方式分组字段。
3）不希望同时刷新所有数据透视表。

数据透视表缓存与数据透视表对象的关系
1）每个数据透视表都有且只有一个对应的数据透视表缓存（一对一）
2）多个数据透视表可以共用一个数据透视表缓存（多对一）
3）数据透视表和数据透视表缓存的对应关系是可以改变的。
数据透视表缓存对象即Pivotcache对象。
显示数据透视表缓存的使用情况：利用Pivotcache对象的Createpivottable方法创建数据透视表。

取消数据透视表之间数据缓存共享
1）使用数据透视表和数据透视图向导创建一个新数据透视表，使该新数据透视表与另一报表基于同一单元格区域但不共享数据缓存。
2）通过临时重新定义数据区域以强制 Excel 取消数据缓存共享，可以取消基于该单元格区域的数据透视表之间的数据缓存共享。
3）通过为工作簿中的每个数据透视表创建唯一的数据连接，可以取消基于同一数据连接的两个或多个数据透视表之间的数据缓存共享。
6、灵活指定数据字段位置
在数据透视表中，数据字段除了出现在数据项区域外，也可以出现在行字段或者列字段区域中。
创建数据透视表时利用虚拟数据字段可以指定数据字段位置。（Data）
注意：在中文版Excel中虚拟数据字段可以使用“Data”或者“数据”，但在英文或其他版中只能使用“Data”。

7、快速修改数据字段的默认汇总方式
如果数据源中某些字段中存在空白单元格或者文本数值时，将该字段添加到数据项区域后，其默认汇总方式
为“计数”，如何让其汇总方式都变更为求和呢：
解决方法：利用代码循环遍历数据透视表中的全部数据字段，快速修改全部数据字段的汇总方式为“求和”。

课程总结
数据透视表对象模型
如何引用数据透视表
什么是数据透视表缓存
利用VBA创建数据透视表的两种方法
灵活指定数据字段位置
快速修改数据字段的默认汇总方式
参考内容《Excel数据透视表大全》第15章

lhhqyt · 发表于 2010-6-23 15:57

总结的很全面，对学习数据透视表很有帮助，谢谢了！

sjz76meizi · 发表于 2010-8-11 08:52

初识VBA的一点心得：

一：VBA发展史二：VBA的用途与作品展示三：Visual Basic编辑器简介

一：VBA发展史
1，60年代中期出现了BASIC编程语言
2,1991年微软发布了Visual Basic®for Windows®
3,1993年，VBA首次应用于商业软件Microsoft Excel 5/Project 4
注：Excel 5之前的版本中，只能使用XLM宏（MS Excel 4.0宏）简称宏表，宏表的名称为(Macrol1,Macrol2….)
4、VBA可以集成到其他商用软件中：如AutoCAD、WordPerfect等。
★什么是VBA？
VBA的全称是Visual Basic®for Application，它是以Visual Basic为发展基础的语言。在Office软件中，VBA应用程序能够在Word、Access等之间进行交互式应用，加强了应用程序间的互动。
VBA是VB的应用程序版本，可以理解为“寄生在Office办公软件中的VB”，可以看作是VB语言的一个子集。
VBA使Office形成了独立的编程环境
★ VBA与Visual Basic
1、VB用于开发Windows应用程序，其代码最终被编译为可执行程序。而VBA是用于控制已有应用程序的自动化操作，其代码为解释。
2、VB拥有独立的开发环境，而VBA必须集成在已有的应用程序中。
二：VBA应用场景
1. 自动执行重复的操作
2. 进行复杂的数据分析对比
3. 生成漂亮诱人的报表和图表
4. 个性化用户界面
5. Office组件的协同工作
6. Excel二次开发（这需要一定的编程水平）
三：Visual Basic编辑器
（一）什么是Visual Basic编辑器？
1、Visual Basic编辑器（Editor）通常简称为VBE
2、VBE的用途：
（1）输入和修改代码
（2）运行和调试代码
3、如何打开VBE窗口？（老师讲了两种）
（1）EXCEL菜单：工具——宏——Visual Basic编辑器
（2）快捷键<Alt+F11>
（3）VBE窗口已经处于打开状态时，用 Alt+Tab 可以切换到VBE窗口。
（如果从VBE窗口直接返回Excel窗口鼠标单击工具栏上面最左侧的Excel图标就可以了）
除了老师讲的两种我自己还发现6种进入VBE窗口的方法：
☆.鼠标右键单击工作表名---查看代码
☆.鼠标右键单击工具栏最左侧excel图标---查看代码
☆单击工具栏中的工具按钮-----自定义---勾选visual basic---调出宏工具栏-----单击宏工具栏中的visual basic编辑器
☆. 点击控件工具箱---查看代码
☆.单击工具栏窗口的工具菜单----宏-----（输入“宏”名）----创建
☆.插入一个控件---右键---查看代码
4.VBE窗口的三大组成部分：菜单，工具栏和功能窗口
5. VBE功能窗口介绍
1）如何显示相关功能窗口
VBE窗口的“试图”菜单中代码窗口，对象窗口，对象编辑器，立即窗口，本地窗口。监视窗口，工程资源管理器，属性窗口….各种不同窗口
视频中主要讲解的“代码窗口”和“工程资源管理器”
2）工程资源管理器
快捷键（CTRL+R）
我们所有的工作表和模块等对象等都会在此以分层结构显示，其中ThisWorkbook代表的是工作簿对象；sheet1(sheet1)等代码工作表对象，其中括号里是我们前面真正的工作表的名称，而前面的sheet1是代码中可以引用的名称；如果添加了模块，那么也会在此显示。
如果有多个工作簿对象时，可以点击前面的“+”“－”把其折叠或打开。
3)代码窗口
快捷键（F7）
在输入代码前先做一下如下设置：
工具---选项-----代码设置（勾选：自动语法检测，自动缩进，自动列出成员，自动显示快速信息，自动显示数据提示）好处是：
A：输入名称后，回车确定，系统会自动添加起结束语句。如(sub  end sub)
B:编写代码时具有自动完成功能。如输入一个vba后面加上“.”（英文半角的句号）时后面会出现属性的提示，选中想要的属性后，按Tab键可自动输入该属性，输完一个属性后，加上空格，后面会出现参数的列表，输入后，加“逗号”（也是英文半角状态）就会出现第二个参数，结束时，按Enter即可，并且可以看到，系统会自动调整大小写。所以在输入代码时，我们不必刻意去输入大小写，只要输入的正确的，它会自动调整这些代码的大小写。
C：自动缩进
自动缩进可以使我们的代码结构清晰，便于理解和纠错
D:注释语句
注释语句以英文状态下的“’”开头来注明，所有的注释语句都以绿色显示
4）VBA编程工具
①VBE窗口鼠标滚屏工具-FreeWheel v2.4
官方主页http://www.geocities.com/siticonballey/2060/freewheel.html
下载链接http://www.geocities.com/siliconvalley/2060/freewheel.zip
②代码美化工具-SmartIndent v3.5(自动添加代码的自动缩进)
官方主页http://www.oaltd.co.uk/indenter/indentfmn.htm
下载链接http://www.oaltd.co.uk/dlcount/dlcount.asp?file=indentervba.exe
A:选择区域的自动缩进
B:模块区域的自动缩进
C:整个工程项目的自动缩进
D:取消自动缩进
E:缩进调整

[ 本帖最后由 sjz76meizi 于 2010-8-11 08:56 编辑 ]

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

参加EH培训的心得和体会

评分

透视表组合、分组相关知识

数据透视表与VBA