数据透视表高级应用案例
利用数据透视表进行销售综合分析
示例 6-16 多角度的销售分析表和销售分析图
图 6-85 展示的“历史销售数据”工作表中记录了某公司一定时期内的销售及成本明细数据。
图6-85历史销售数据明细表对这样一个庞大而且经常增加记录的数据列表进行数据分析，首先需要创建动态的数据透视表，并通过对数据透视表的重新布局得到按“商品年份”“商店名称”和“季节名称”等不同角度的分类汇总分析表，再通过不同的数据透视表生成相应的数据透视图得到一系列的分析报表，具体请参照以下步骤。
步 骤 1新建一个 Excel 工作簿，将其命名为“多角度的销售分析表和销售分析图 .xlsx”，打开该工作簿，将 Sheet1 工作表改名为“销售分析”。
步 骤 2在【数据】选项卡中单击【现有连接】按钮，在弹出【现有连接】的对话框中单击【浏览更多】按钮，打开【选取数据源】对话框，选择要导入的目标文件的所在路径，双击“销售分析数据源 .xlsx”，打开【选择表格】对话框，如图 6-86 所示。
图6-86激活【选取数据源】对话框
步 骤 3保持【选择表格】对话框中对名称的默认选择，单击【确定】按钮，激活【导入数据】对话框，单击【数据透视表】选项按钮，指定【数据的放置位置】为现有工作表的“$A$1”，单击【确定】按钮生成一张空白的数据透视表，如图 6-87 所示。 图6-87生成空白的数据透视表步 骤 4向数据透视表中添加相关字段，并在数据透视表中插入计算字段“毛利”，如图 6-88所示。
计算公式为“毛利 = 销售金额 - 成本金额”
图6-88按商品年份汇总的数据透视表步 骤 5单击数据透视表中的任意一个单元格（如 A2），在【数据透视表工具】的【分析】选项卡中单击【数据透视图】按钮，在弹出的【插入图表】对话框中选择【折线图】选项卡中的“折线图”图表类型，单击【确定】按钮创建数据透视图，如图 6-89 所示。
图6-89按商品年份的收入及成本利润走势分析图步 骤 6对数据透视图进行格式美化后如图 6-90 所示。
图 6-90 美化数据透视图步 骤 7复制图 6-88 所示的数据透视表，对数据透视表重新布局，创建数据透视图，图表类型选择“饼图”，得到按不同季节的销售金额汇总表和销售占比图，如图 6-91 所示。
图6-91不同季节销售占比分析图步 骤 8再次复制图 6-88 所示的数据透视表，对数据透视表重新布局，创建数据透视图，图表类型选择“堆积柱形图”，得到按销售部门反映的收入及成本利润汇总表和不同门店的对比分析图，如图 6-92 所示。
图6-92门店销售分析图本例通过对同一个数据透视表的不同布局得到各种不同角度的销售分析汇总表，并通过创建数据透视图来进行销售走势、销售占比和门店对比等各种图表分析，完成图文并茂的多角度动态销售分析报表，如图 6-93 所示。
图6-93图文并茂的多角度动态销售分析报表
制作物料动态进销存模板ENJOY THE SUMMER
示例 6-17 根据多工作表数据统计进销存且支持自适应路径及文件名更改
图 6-94 展示了某企业的物料进销存模板，模板中包含了期初、入库、出库和进销存四张工作表，分别放置了期初、入库和出库数据，进销存表中则是按物料编码统计进销存信息的数据透视表。为了便于不同部门的人员查看数据，还需要具有文件所在路径及文件名更改时，不影响数据透视表的跨表提取数据的功能。
图6-94根据多工作表数据统计进销存且支持自适应路径及文件名更改本案例的关键解决思路：
利用 SQL 语句创建数据透视表实现从多工作表提取数据。
创建计算字段统计期末结存的数量和金额。
利用 VBA 使数据透视表支持自适应路径及文件名更改后的统计。
具体操作步骤如下。
步 骤 1打开“根据多工作表数据统计进销存且支持自适应路径及文件名更改”工作表，选中“进销存”工作表的 A1 单元格，在【数据】选项卡中单击【现有连接】按钮，在弹出的【现有连接】对话框中单击【浏览更多】按钮，在弹出的【选取数据源】对话框中选择文件所在位置（如桌面），选择目标文件，单击【打开】按钮，在弹出的【选择表格】对话框中选中“入库 $”数据源表，如图 6-95 所示。
图6-95选择创建进销存汇总表的数据
步 骤 2在【选择表格】对话框中单击【确定】按钮，在弹出的【导入数据】对话框中选中【数据透视表】单选按钮，单击【属性】按钮，在弹出的【连接属性】对话框中单击【定义】选项卡，清空命令文本中的内容并输入 SQL 代码，单击【确定】按钮，最后返回【导入数据】对话框，再次单击【确定】按钮，如图 6-96 所示。 图6-96输入 SQL 语句SQL 代码如下：select *, 数量 as 入库数量 ,0 as 出库数量 ,0 as 期初数量 , 金额 as 入库金额 ,0 as 出库金额 ,0 as 期初金额 from [ 入库 $] UNION ALL select *,0 as 入库数量 , 数量 as 出库数量 ,0 as 期初数量 ,0 as 入库金额 ,金额 as 出库金额 ,0 as 期初金额 from [ 出库 $] UNION ALLselect *,0 AS 入库数量 ,0 as 出库数量 , 数量 as 期初数量 ,0 as 入库金额 ,0 as 出库金额 , 金额 as 期初金额 from [ 期初 $]
提示：此 SQL 语句的含义如下。
先使用子查询语句 UNION ALL 将所有工作表的数据列表记录汇总。由于不同工作表下相同字段名代表的含义不同，如字段名“数量”在期初、入库和出库表中的数量分别代表期初数量、入库数量和出库数量，所以用 as 别名标识符对字段重命名为易于识别的名称。Excel 工作表在引用时需要将其包含在方括号内“[]”，同时需要在其工作表名称后面加上“$”符号，如 select * from [ 期初 $]
步 骤 3在创建的空白数据透视表中进行字段布局，如图 6-97 所示。
图6-97设置数据透视表的字段布局步 骤 4在数据透视表中插入【期末数量】和【期末金额】计算字段，按照期初、入库、出库和结存的显示顺序调整数据透视表的字段，并美化数据透视表，如图 6-98 所示。
期末数量 = 期初数量 + 入库数量 - 出库数量
期末金额 = 期初金额 + 入库金额 - 出库金额
图6-98美化后的进销存汇总表
步 骤 5为了使数据透视表支持自适应路径及文件名更改，添加 VBA 代码。单击【开发工具】选项卡下的【Visual Basic】按钮，在弹出的【Microsoft Visual Basic for Applications】对话框中，单击【插入】→【模块】命令，如图 6-99 所示。
图6-99在 VBE 界面添加模块
步 骤 6双击【模块 1】，在代码框中输入以下代码，如图 6-100 所示。
图6-100编辑模块中的 VBA 代码
Dim strCon As String, iPath As String ' 定义变量
Dim iT As Integer, jT As Integer, iFlag As String, iStr As String Dim sht As Worksheet
iPath = ThisWorkbook.FullName ' 获取本工作簿的完全路径
On Error Resume Next ' 防错语句，当执行代码遇到错误时继续运行后面的代码
For Each sht In ThisWorkbook.Worksheets ' 遍历工作簿中的每张工作表
iT = sht.PivotTables.Count ' 统计数据透视表的个数
If iT ＞ 0 Then
For jT = 1 To iT ' 遍历工作簿中的每张工作表
strCon = sht.PivotTables(jT).PivotCache.Connection ' 将数据透视表中缓存连接信息赋值给变量
strCon Select Case Left(strCon, 5) ' 利用 select case语句判断缓存连接信息中的数据连接方式是 ODBC 还是 OLEDB, 判断方法为从 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) ' 利 用 split 函数 , 分隔符分别取 iFlag 变量和 ';' 为分隔符取得数据源和路径在变量strCon 中截取文件路径信息 With sht.PivotTables(jT).PivotCache ' 替换据透视表缓存信息中的文件完全路径 .Connection = VBA.Replace(strCon, iStr, iPath) ' 利用 Connection 属性把连接属性里前面的文件夹路径设置成当前工作簿的路径 .CommandText = VBA.Replace(.CommandText, iStr, iPath) ' 利用 CommandText 属性修改 SQL 语句的文件路径为当前工作簿的文件路径 End With Next End If NextEnd Sub
步 骤 7双击【ThisWorkbook】，输入以下代码，如图 6-101 所示。
Private Sub Workbook_Open() Call SQL 自适应路径和文件名更改End Sub
图6-101编辑 ThisWorkbook 的 VBA 代码
提示：如果用户发现当输入 VBA 代码后，Excel 文件无法保存，请将文件另存为“Excel 启用宏的工作簿 (*.xlsm)”类型。
至此，实现了数据透视表根据多工作表数据统计进销存且支持自适应路径及文件名更改的需求。为了使 VBA 代码能够顺利执行，当开启文件时遇到“安全警告 部分活动内容已被禁用。单击此处了解详细信息”时，需要单击【启用内容】按钮。
END最新文章
-
“豫西中医界之泰斗乔保均”治疗疑难病60年经验赏析
9小时前 -
回忆、健忘和洒脱
9小时前 -
弘一法师：“真的不忍心告诉你，这个世界只是一个梦。你一辈子执
9小时前 -
心理学，准的让你惊叫
9小时前 -
人生，成大器者有四识，知识、见识、胆识、远识
10小时前 -
俞和：被遗忘的书法家，以古为师，创新在手！
10小时前 -
2024届新高考II卷语文真题答案及解析
10小时前 -
生前只是小人物，死后震惊史学界
10小时前
热门文章
-
每日一诵伤寒论第241条
1周前 -
老张老李侃门球之140篇
2天前 -
这个穴位可以治疗多种胃痛腹痛，还可以减肥
5天前 -
诊余杂记（师传经验）
4天前 -
为什么五点钟要起床答案让人吃惊！（现在知道还不晚）
1周前 -
治疗坐骨神经痛药酒５方
1周前 -
美丽中国-2870：中国最大的内陆河，塔里木河
2天前 -
二十四山开门放水作灶真诀开门放水作灶直诀——子山
2天前
有话要说...