Excel2010 OLE DB 利用SQL语句编制每天刷卡汇总数据透视表.pdfVIP

利用SQL 语句编制每天刷卡汇总数据透视表 图 20-48 展示了某实验室在2012 年3 月份每天进出实验室刷卡记录数据列表，该数据列 表保存在D 盘根目录下的“2012 年3 月实验室出入刷卡记录.xlsx”文件中。 图 20-48 刷卡记录数据列表 示例 20.7 编制每天刷卡汇总数据透视表 如果希望对图 20-48 所示的数据列表，查询每天实验室人员的刷卡情况，请参照以下步骤。 步骤1 新建一个Excel 工作簿，将其命名为“编制每天刷卡汇总数据透视表.xlsx”，打开 该工作簿，将Sheet1 工作表改名为“出入汇总”，然后删除其余的工作表。 步骤2 打开 D 盘根目录下的目标文件 “2012 年3 月实验室出入刷卡记录.xlsx”，弹出 【选择表格】对话框，如图 20-49 所示。 1 / 4 图 20-49 选择表格 步骤3 保持【选择表格】对话框的默认选择，单击【确定】按钮，在弹出的【导入数据】 对话框中选择【数据透视表】单选按钮，【数据的放置位置】选择【现有工作表】单选按钮， 单击“出入汇总”工作表中的 A1 单元格，再单击【属性】按钮打开【连接属性】对话框， 单击【定义】选项卡，如图 20-50 所示。 图 20-50 打开【连接属性】 步骤4 清空【命名文本】文本框中的内容，输入以下SQL 语句： SELECT A.工号,A.姓名,A.日期,A.刷卡时间,COUNT(B.刷卡时间) AS 打卡次序 FROM [刷卡记 录$]A INNER JOIN [刷卡记录$]B ON A.工号=B.工号 AND A.日期=B.日期AND A.刷卡时间=B.刷卡时间 GROUP BY A.工号,A.姓名,A.日期,A.刷卡时间 单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白的数据透 2 / 4 视表，如图 20-51 所示。 图 20-51 创建空白的数据透视表 思路解析：以工号、日期和刷卡时间作为关联条件，通过对同一天、同一工号下的不同 刷卡时间进行比较，利用聚合函数来统计符合条件的刷卡记录对比次数，从而获得同一 天、同一工号不同刷卡记录对应的打卡次序，实现每天刷卡汇总查询。 步骤5 在【数据透视表字段列表】中，将工号、姓名和日期字段移动至【行标签】区域 内，将“打卡次序”字段移动至【列标签】区域内，将“刷卡时间”字段移动至【∑ 数值】 区域内，并更改“打卡次序”字段的值汇总方式为“求和”，设置“数字格式”为时间格式， 最后对数据透视表进一步美化，最终完成的数据透视表如图 20-52 所示。 图 20-52 最终完成的数据透视表 示例结束。 本例利用SQL 联接语句结合聚合函数统计符合条件的数据记录，日常工作中有着非常广泛 的应用，例如生成排名等，但使用JOIN 联接，需要注意关联条件的设置，条件设置不当， 3 / 4 容易产生笛卡尔积，导致数据虚增。 本篇文章节选自 《Excel 2010 数据透视表应用大全》 ISBN ：9787115300232 人民邮 电出版社 4 / 4