作者:乔山办公网日期:
返回目录:excel表格制作
数组,数据分析,都是自己的思路来写的
是EXCEL自带的数据透视表不能满百足你的要求吗?说实话,有时我也有这种感觉,也想自己用VBA编写功能更强大的数据透视表代码,但实践下来有如下感觉,希望对你有用:
1、代码量太大,如果不是很广普的功能,实现起来总有得不偿失感觉。度
2、VBA代码还是不够强大,不能很好的实现设置参数保存,当文件关闭后再打开,原来透视表的个项设置参数不能被加载,也就不能自动重新计算了,需要重新设置数据透视表。当然可以把这些参数保存在数据表中,但这样做很不安全,我放版弃了这种方式。
3、数据透视表可以定义成一类,但类对象也不能被保存,所以给该功能 的实现带来瞪大的难权度。这个与第2个问题应该算是同一个问题吧。因为类对象可以保存的话,那第2个问题也就不存在了。
以下代码为:当工作簿打开时,自动替换透视表中源路径为当前工作簿路径
Private Sub Workbook_Open()
Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As String
'定义变量e69da5e887aa7a686964616f335
On Error Resume Next
strCon = ActiveSheet.PivotTables(1).PivotCache.Connection
'将当前活动数据透视表中缓存连接信息赋值给变量strCon
Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符
Case "ODBC;" '用于判断缓存连接信息中的数据连接方式,如果是ODBC方式
iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag
Case "OLEDB" '用于判断缓存连接信息中的数据连接方式,如果是OLEDB方式
iFlag = "Source=" '将"Source=" 赋值给变量iFlag
Case Else
Exit Sub
End Select
iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息
iPath = ActiveWorkbook.FullName '获取当前活动工作簿的完全路径
With ActiveSheet.PivotTables(1).PivotCache '替换数据透视表中缓存信息中的文件完全路径
.Connection = VBA.Replace(strCon, iStr, iPath)
.CommandText = VBA.Replace(.CommandText, iStr, iPath)
End With
End Sub
首先,选中原始数据区域,点击【插入】-【数据透视表】。
如果刚才已经选中了数据区域,在【数据透视表向导】中最上面的选择区域框可以看到选中的单知元格道区域,如果刚才没有选中,也可以在此选取。
数据透视表提供了在新工作表创建和在当前工作表创建的选择,如果数据透视表较大,内容很多,建议在新工作表中生成透视表。
根据实际情况选择完上述两个设置后,点击【确定】按钮。
这时,在excel中就出现了一个新回工作表。左面是空白的透视表区域,右边是数据透视表字段列表,可以拖动和设置。
数据透视表列表字段处显示的字段名称是原始数据区域的抬头,可以拖动到下面的四个框中。
把【姓名】字段拖到行标签位置,【答数量】拖动到【数值】位置。这时,可以看到左方的数据透视表已经按照不同的人名将数量进行了汇总。
行标签处可以插入多个,但是前后顺序要根据自己的需求来选择。比如说,我们要按人员和名称分别看汇总数,可以将【名称】字段拖到【姓名】字段下面,结果如下图所示:
选中数据透视表上任何一个单元格,菜单栏上将激活【选项】和【设计】两个选项卡,可以对报表布局、样式等进行更多设置。