Excel2010 OLE DB 利用SQL语句编制每天刷卡汇总数据透视表
SQL在Excel中的应用方法
SQL在Excel中的应用方法iamlaosng 文Excel中使用SQL的主要目的是连接数据库（或Excel工作表）导入数据或者对这些数据进行统计汇总，要达到这个目的，需要好好学习SQL语句的使用。
本文主要说明在Excel中如何使用SQL，至于SQL语句本身就不多作介绍了。
一、简单的查询1、建立查询数据选项卡一现有连接一浏览更多或者按快捷键Alt+D+D+D选择要查询的Excel文件和文件中的的工作表，就可以将相应工作表的数据取过来。
表现形式可以是表，也可以是数据透视表等。
2、SQL查询语句如果是挑选部分列数据，就需要用SQL语句（取所有数据也可以用SQL语句）。
• 建立查询时，选择工作表后不要点击确定”按钮，而是先点击属性”按钮，弹出窗口中选择定义' 选项卡，在命令文本框中输入SQL查询语句（原来的工作表名称，表示所有数据，可以认为是取所有数据的SQL的一种特殊写法）：Select字段列表from [工作表名$]--其中字段列表就是需要选择的字段，数据源用工作表名称加“再用中括号括起来，例如:select prov_ name, city_ name, xs_mc, xs_code from [Sheet1$]select * from [Sheet1$] --取所有数据*偶然发现，字段名不能用no,估计是保留字，如需要，用中括号括起来，例如: select [ no],prov_ name,city_ name, xs_mc, xs_code from [Sheet1$]字段名中含有特殊字符的也要用中括号括起来，如/ ?空格等Excel查询没有伪表概念，对于表达式的计算直接用select既可，例如Select 23+45 --返回68Select date() --返回当前日期3、修改查询语句* 方法：点击右键一弹出菜单一表格一编辑查询通过修改SQL语句可以变更所取的数据，也可以将建立查询时的简单SQL语句改成复杂的SQL语句。
Excel中的数据透视表使用数据透视表报表数据处理
Excel中的数据透视表使用数据透视表报表数据处理数据透视表是Excel中非常有用的功能，它可以帮助我们以多种方式对数据进行汇总和分析。
无论是处理大量数据还是对数据进行快速分析，数据透视表都可以提供便捷的解决方案。
本文将介绍如何使用数据透视表在Excel中进行数据处理和报表生成。
一、什么是数据透视表？数据透视表是Excel中的一项功能，它可以根据所选取的数据范围自动创建一个交互式报表，通过拖动和放置字段，可以快速对数据进行汇总和分析。
数据透视表可以将数据按照不同的方式分类和汇总，同时还可以通过数据透视图的样式和格式进行自定义设置。
二、数据透视表的使用步骤1. 准备数据：首先，需要准备好需要进行分析和汇总的数据。
确保数据的格式是正确的，并且数据字段是清晰明确的。
2. 选择数据透视表功能：在Excel中，选择需要创建数据透视表的数据范围，然后点击菜单栏中的“数据”选项卡，进入“数据工具”区域，找到“数据透视表”按钮并点击。
3. 设置数据透视表：在弹出的“创建数据透视表”的对话框中，选择需要分析的数据范围，并确定放置数据透视表的位置。
然后，在对话框中选择需要作为行字段、列字段和值字段的数据字段，并可选择需要进行汇总和筛选的数据。
4. 定制数据透视表：Excel会自动根据所选择的字段和选项创建数据透视表。
你可以根据需要进行调整和更改。
例如，你可以拖动字段到不同的区域，或者更改数据透视表的样式和格式。
5. 汇总和分析数据：完成数据透视表的创建和定制后，Excel会自动对数据进行汇总和分析。
你可以根据需要展开或折叠数据，选择不同的过滤条件，或者使用各种内置的计算公式进行更深入的数据分析。
6. 更新数据透视表：如果原始数据发生变化，可以通过右键点击数据透视表并选择“刷新”来更新数据透视表，以反映最新的数据变化。
三、数据透视表的应用场景1. 数据汇总：数据透视表可以帮助我们将大量的数据按指定的条件进行分类和汇总。
学习使用Excel进行数据汇总和数据透视表
学习使用Excel进行数据汇总和数据透视表第一章：Excel数据汇总的基本操作在进行数据汇总之前，需要先将原始数据导入Excel中。
使用Excel可以迅速进行数据的整理、汇总和分析，方便用户快速的获取有效的信息。
下面将介绍Excel数据汇总的基本操作。
1.1 插入数据表首先，在Excel中创建一个新的工作表，将需要汇总的数据逐行录入该表中。
确保每一行代表一条数据记录，每一列代表一个字段。
1.2 使用筛选和排序功能在Excel的数据汇总过程中，筛选和排序功能是非常常用的。
可以通过筛选功能选择特定的数据进行汇总，也可以通过排序功能按照一定的规则对数据进行排序，便于后续的分析和汇总。
1.3 使用公式进行数据计算Excel提供了丰富的内置函数，可以使用这些函数对数据进行计算和汇总。
常用的函数包括SUM、AVERAGE、COUNT等，可以通过这些函数对数据进行求和、平均值、计数等操作。
第二章：Excel数据透视表的构建与分析数据透视表是Excel中非常有用的功能，可以帮助用户更快速地理解和分析数据。
下面将详细介绍Excel数据透视表的构建与分析。
2.1 构建数据透视表在Excel中，选择需要进行数据透视分析的数据范围，然后点击数据选项卡中的“透视表”按钮，选择“创建透视表”选项。
按照向导的步骤选择需要汇总和分析的字段，即可生成数据透视表。
2.2 数据透视表的布局和格式设置在生成数据透视表后，可根据需要进行布局和格式的调整。
比如可以调整行标签、列标签、数值等的位置，设置数值格式、合并单元格等来使数据透视表更加清晰和美观。
2.3 对数据透视表进行分析数据透视表的最大优势是可以对大量的数据进行快速分析。
可以通过拖拽字段到对应的区域进行汇总、计算和筛选操作，生成需要的分析结果。
用户可以根据实际需求灵活地调整数据透视表的分析配置，以获取需要的信息。
第三章： Excel数据汇总和数据透视表的应用实例为了更好地理解和应用Excel数据汇总和数据透视表功能，下面将介绍两个具体的应用实例。
Excel2010 OLE DB 导入数据关联列表创建数据透视表
导入数据关联列表创建数据透视表运用导入外部数据结合“编辑OLE DB”查询中的SQL语句技术，可以轻而易举地汇总关联数据列表的所有记录。
汇总数据列表的所有记录和与之关联的另一个数据列表的部分记录图12-45展示了某公司2011年员工领取物品记录数据列表和该公司的部门员工资料数据列表。
此数据列表保存在D盘根目录下的“2011年物品领取记录.xlsx”文件中。
图12-45 部门-员工数据列表和物品领取数据列表如果希望统计不同部门不同员工的物品领取情况，请参照以下步骤。
步骤1 打开D盘根目录下的“2011年物品领取记录.xlsx”文件，单击“汇总”工作表标签，在【数据】选项卡中单击【现有连接】按钮，弹出【现有连接】对话框，单击【浏览更多】按钮，打开【选取数据源】对话框，如图12-46所示。
图12-46 选取数据源步骤2 打开D盘根目录下的目标文件“2011年物品领取记录.xlsx”，弹出【选择表格】对话框，如图12-47所示。
双击鼠标图12-47 选择表格步骤3 保持【选择表格】对话框的默认选择，单击【确定】按钮，在弹出的【导入数据】对话框中选择【数据透视表】单选按钮，【数据的放置位置】选择【现有工作表】单选按钮，然后单击“汇总”工作表中的A3单元格，再单击【属性】按钮打开【连接属性】对话框，单击【定义】选项卡，如图12-48所示。
图12-48 打开【连接属性】步骤4 清空【命名文本】文本框中的内容，输入以下SQL语句：SELECT A.部门,A.员工,B.日期,B.领取物品,B.单位,B.数量 FROM [部门－员工$]A LEFT JOIN [物品领取$]B ON A.员工=B.员工也可以使用以下SQL语句：SELECT A.日期,A.领取物品,A.单位,A.数量,B.部门,B.员工 FROM [物品领取$]A RIGHT JOIN [部门－员工$]B ON A.员工=B.员工单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白的数据透视表，如图12-49所示。
Excel中使用数据透视表的方法
Excel中使用数据透视表的方法数据透视表是Excel中一种非常强大的数据分析工具，它可以帮助我们快速地对大量数据进行汇总和分析。
在本文中，我们将介绍如何使用数据透视表来处理和分析数据。
一、创建数据透视表1. 打开Excel并导入需要分析的数据。
2. 选中数据区域，点击“插入”选项卡中的“数据透视表”按钮。
3. 在弹出的对话框中，选择要将数据透视表放置的位置，并点击“确定”按钮。
4. 在数据透视表字段列表中，将需要分析的字段拖放到相应的区域，如行区域、列区域和值区域。
二、设置数据透视表字段1. 在行区域中，可以放置用于分类的字段，比如日期、地区等。
2. 在列区域中，可以放置用于分组的字段，比如产品、部门等。
3. 在值区域中，可以放置用于计算的字段，比如销售额、利润等。
4. 可以对值区域中的字段进行汇总方式的设置，比如求和、平均值、计数等。
三、筛选数据1. 在数据透视表上方的筛选器中，可以对字段进行筛选，只显示符合条件的数据。
2. 可以使用多个筛选器进行多个条件的组合筛选。
四、排序数据1. 在数据透视表中，可以对行区域和列区域中的字段进行排序，按升序或降序排列。
2. 可以对值区域中的字段进行排序，按数值大小或字母顺序排列。
五、更改数据透视表布局1. 可以通过拖放字段到不同的区域来更改数据透视表的布局。
2. 可以在行区域和列区域中添加多个字段，实现多级分类和分组。
六、刷新数据1. 当原始数据发生变化时，数据透视表不会自动更新，需要手动刷新数据。
2. 可以点击数据透视表上方的“刷新”按钮来更新数据。
七、使用数据透视图进行数据分析1. 可以使用数据透视表来分析数据的总体情况，比如销售额的总和、平均值等。
2. 可以使用数据透视表来比较不同分类和分组的数据，找出最大值、最小值等。
3. 可以使用数据透视表来进行数据的透视和透视图的生成，快速生成各种报表和图表。
八、数据透视表的高级功能1. 可以使用数据透视表来创建计算字段，实现更复杂的数据分析。
Excel数据透视表讲解
Excel数据透视表讲解Excel数据透视表详细教程一、创建数据透视表1.打开Excel表格，选择需要创建数据透视表的数据源，然后单击“插入”选项卡中的“数据透视表”按钮。
2.在弹出的“创建数据透视表”对话框中，选择放置数据透视表的位置，可以是新工作表或现有工作表，然后单击“确定”按钮。
3.在数据透视表字段列表中，将需要分析的字段拖动到行标签、列标签和值字段区域中。
4.完成后，数据透视表会自动根据拖放字段和数据源中的数据进行汇总、计算和显示相应的统计信息。
二、移动数据透视表1.选中已创建的数据透视表，然后单击“选项”选项卡中的“移动或复制工作表”按钮。
2.在弹出的“移动或复制工作表”对话框中，选择要移动到的目标位置，可以是新工作表或现有工作表，然后单击“确定”按钮。
3.此时，数据透视表将被移动到新的位置，并且列宽会自动调整以适应新的工作表大小。
三、删除数据透视表1.选中需要删除的数据透视表，然后单击“选项”选项卡中的“删除工作表”按钮。
2.在弹出的提示框中，确认要删除的数据透视表，然后单击“确定”按钮。
3.此时，选中的数据透视表将被彻底删除，无法恢复。
四、刷新数据透视表1.选中数据透视表，然后单击“选项”选项卡中的“刷新”按钮。
2.在弹出的提示框中，选择需要更新的数据源范围，然后单击“确定”按钮。
3.Excel将自动刷新数据透视表中的数据，以反映最新数据源中的信息。
五、数值排序与文本筛选1.选中数据透视表中的某个数值字段，然后单击“选项”选项卡中的“排序”按钮，可以根据需要选择升序或降序排序方式。
2.若要对某个文本字段进行筛选，可以单击该字段旁边的下拉箭头，然后选择需要的筛选条件。
3.可以同时对多个字段进行排序和筛选操作，以满足不同的分析需求。
excel中数据透视表的汇总方式
excel中数据透视表的汇总方式Excel 中数据透视表的汇总方式在日常的数据处理和分析工作中，Excel 是我们常用的工具之一，而其中的数据透视表功能更是强大而实用。
数据透视表能够快速地对大量数据进行汇总、分析和展示，帮助我们从复杂的数据中提取有价值的信息。
在数据透视表中，汇总方式的选择对于准确呈现数据结果至关重要。
接下来，让我们一起深入了解一下 Excel 中数据透视表常见的汇总方式。
一、求和汇总求和是数据透视表中最常用的汇总方式之一。
当我们需要计算某一列数据的总和时，选择求和汇总就能够快速得到结果。
例如，我们有一份销售数据表格，其中包含了不同产品在不同地区的销售额。
通过创建数据透视表，并将“销售额”字段设置为求和汇总，我们可以轻松得到每个地区的总销售额，以及每种产品的销售总额。
在实际应用中，如果数据中存在空值，求和汇总会自动忽略这些空值，只对有数值的单元格进行求和计算。
二、计数汇总计数汇总用于计算某一列中非空单元格的数量。
比如说，我们有一份员工信息表格，包含了员工的姓名、部门、职位等信息。
如果我们想知道每个部门有多少名员工，就可以将“部门”字段设置为计数汇总。
需要注意的是，计数汇总只计算非空单元格的数量，对于数值型数据，即使数值相同，也会被分别计算。
三、平均值汇总平均值汇总用于计算某一列数据的平均值。
以学生的考试成绩为例，我们有不同学科的成绩数据。
通过将“成绩”字段设置为平均值汇总，能够得到每个学科的平均成绩。
在使用平均值汇总时，同样会自动忽略空值。
四、最大值汇总最大值汇总可以帮助我们快速找出某一列数据中的最大值。
假设我们有一份产品质量检测数据，包含了不同批次产品的检测值。
通过最大值汇总，能够一眼看出每个批次中的最高检测值。
五、最小值汇总与最大值汇总相对应的是最小值汇总，用于找出某一列数据中的最小值。
例如在库存管理中，通过对库存数量进行最小值汇总，可以了解到每种商品的最低库存水平。
六、乘积汇总乘积汇总相对较少使用，但在某些特定场景下非常有用。
Excel2010 OLE DB 导入单张数据列表创建数据透视表
导入单张数据列表创建数据透视表运用导入外部数据的功能，指定数据源数据列表所在位置后，可以生成动态的数据透视表。
“外部数据源”是相对当前Excel工作簿而言，除了各种类型的文本文件或数据库文件Excel 工作簿也可以作为“外部数据”供导入。
导入单张数据列表中的所有记录图12-1展示了某超市的销售数据列表，此数据列表保存在D盘根目录下“2012年销售电子记录.xlsx”文件中。
图12-1 销售电子记录数据列表如果希望对图12-1所示数据列表进行汇总分析，查看不同月份下所有商品的销售情况，请参照以下步骤。
步骤1 双击打开“2012年销售电子记录.xlsx”文件，单击“商品汇总”工作表标签，在【数据】选项卡中单击【现有连接】按钮，弹出【现有连接】对话框，单击【浏览更多】按钮，打开【选取数据源】对话框，如图12-2所示。
图 12-2 选取数据源步骤2 打开D 盘根目录中的目标文件“2012年销售电子记录.xlsx ”，弹出【选择表格】对话框，单击【名称】中的【数据源$】，如图 12-3所示。
图 12-3 选择表格步骤3 单击【选择表格】对话框中的【确定】按钮，在弹出的【导入数据】对话框中选择【数据透视表】单选按钮，【数据的放置位置】选择【现有工作表】单选按钮，然后单击“商品汇总”工作表中的A1单元格，最后单击【确定】按钮创建一张空白的数据透视表，双击鼠标如图12-4所示。
图12-4 创建一张空白的数据透视表步骤4 在【数据透视表字段列表】对话框中，将“销售日期”字段移动至【列标签】区域并在数据透视表中按【步长】为【月】进行分组组合，“商品名称”字段移动至【行标签】区域，将“数量”字段移动至【∑数值】区域，最后对数据透视表进行美化，完成后的数据透视表如图12-5所示。
图12-5 完成后的数据透视表步骤5 单击数据透视表中的任意单元格（如A1），在【数据透视表工具】的【选项】选项卡中单击【刷新】按钮的下拉按钮，在弹出的下拉列表中选择【连接属性】命令，在弹出的【连接属性】对话框中的【刷新控件】中勾选【打开文件时刷新数据】的复选框，最后单击【确定】按钮关闭对话框，如图12-6所示。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
利用SQL语句编制每天刷卡汇总数据透视表
图20-48展示了某实验室在2012年3月份每天进出实验室刷卡记录数据列表，该数据列表保存在D盘根目录下的“2012年3月实验室出入刷卡记录.xlsx”文件中。
图20-48 刷卡记录数据列表
如果希望对图20-48所示的数据列表，查询每天实验室人员的刷卡情况，请参照以下步骤。
步骤1 新建一个Excel工作簿，将其命名为“编制每天刷卡汇总数据透视表.xlsx”，打开该工作簿，将Sheet1工作表改名为“出入汇总”，然后删除其余的工作表。
步骤2 打开D盘根目录下的目标文件“2012年3月实验室出入刷卡记录.xlsx”，弹出【选择表格】对话框，如图20-49所示。
图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.刷卡时间
单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白的数据透
视表，如图20-51所示。
图20-51 创建空白的数据透视表
思路解析：以工号、日期和刷卡时间作为关联条件，通过对同一天、同一工号下的不同刷卡时间进行比较，利用聚合函数来统计符合条件的刷卡记录对比次数，从而获得同一天、同一工号不同刷卡记录对应的打卡次序，实现每天刷卡汇总查询。
步骤5 在【数据透视表字段列表】中，将工号、姓名和日期字段移动至【行标签】区域内，将“打卡次序”字段移动至【列标签】区域内，将“刷卡时间”字段移动至【∑数值】区域内，并更改“打卡次序”字段的值汇总方式为“求和”，设置“数字格式”为时间格式，最后对数据透视表进一步美化，最终完成的数据透视表如图20-52所示。
图20-52 最终完成的数据透视表
本例利用SQL联接语句结合聚合函数统计符合条件的数据记录，日常工作中有着非常广泛的应用，例如生成排名等，但使用JOIN联接，需要注意关联条件的设置，条件设置不当，
容易产生笛卡尔积，导致数据虚增。
本篇文章节选自《Excel 2010数据透视表应用大全》ISBN：9787115300232 人民邮电出版社。