模块8 表格的高级应用技巧教学课件 MS Office 2016高级应用.pptx

《模块8 表格的高级应用技巧教学课件 MS Office 2016高级应用.pptx》由会员分享，可在线阅读，更多相关《模块8 表格的高级应用技巧教学课件 MS Office 2016高级应用.pptx（75页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、模块8 表格的高级应用技巧CONTENT案例1制作问卷调查表案例2制作问卷调查表案例1制作问卷调查表4案例描述图 8-1 问卷调查表效果问卷调查表可以调查客户满意度、产品类别、访客来源等。使用问卷调查表可以很方便地设计各类问卷题型：是非题、单选题、多选题、填空题等。问卷调查表效果如图 8-1所示。5案例分析实现本案例首先要创建密码登录界面，之后才能进行各种控件的添加与定义操作。要完成本案例，需要进行如下操作。（1）创建密码登录界面。（2）定义控件事件。（3）创建 Excel 文件打开事件。（4）创建分组框及选项按钮控件。6案例实现1.创建密码登录界面（1）选择“文件”“新建”选项，进入“新建”

2、界面，单击“空白工作簿”按钮。（2）这样即可新建一个工作簿，单击“文件”选项卡，选择“另存为”选项，进入“另存为”界面，单击“浏览”按钮。7案例实现（3）弹出“另存为”对话框，设置好文件保存路径，修改“文件名”为“问卷调查表”，修改“保存类型”为“Excel 启用宏的工作簿（*.xlsm）”，单击“保存”按钮，即可保存宏表格，如图 8-2 所示。（4）切换至“开发工具”选项卡，在“代码”组中单击“Visual Basic”按钮，即可进入 VBA 的编程界面。n提示通常情况下，“开发工具”选项卡不显示在 Excel 2016 主界面中，用户需要通过设置“自定义功能区”使之显示在主界面中。8案例实

3、现图 8-2 设置保存参数9案例实现（5）在菜单栏中，选择“插入”“用户窗体”选项，如图 8-3 所示。（6）这样即可创建窗体界面，并在窗体上单击鼠标并拖曳，调整窗体的大小。（7）在属性窗口中，修改 Caption 参数值为“登录界面”，如图 8-4 所示。10案例实现图 8-3 选择“用户窗体”选项图 8-4 修改 Caption 参数值11案例实现（8）这样即可更改窗体界面的名称，并查看窗体界面效果。（9）在问卷调查表的 VBA 界面中，单击工具箱中的“标签”控件，如图 8-5 所示。（10）在窗体界面上，单击鼠标并拖曳，即可创建标签控件，如图 8-6 所示。12案例实现图 8-5 单击“

4、标签”控件图 8-6 创建标签控件13案例实现（11）在属性窗口中，设置其 Caption 属性值为“用户名：”，在属性窗口的 Font 文本框中，单击 按钮，如图 8-7 所示。（12）打开“字体”对话框，在“大小”下拉列表框中选择“四号”选项，单击“确定”按钮，如图 8-8 所示。14案例实现图 8-7 设置属性参数图 8-8 设置字体参数15案例实现（13）这样即可设置字体的大小，并在窗体中调整标签的大小和位置，如图 8-9 所示。（14）在窗体中，选择标签进行复制，设置其 Caption 属性值为“密码：”，并调整标签的大小。（15）在 VBA 编程界面的工具箱中，单击“文本框”控件，

5、在窗体界面上，单击鼠标并拖曳，即可创建多个文本框控件。（16）在 VBA 编程界面的工具箱中，单击“命令按钮”标签，在窗体中，按住鼠标左键并拖曳，即可绘制一个命令按钮标签，然后设置命令按钮控件的属性和字体参数，如图 8-10所示。16案例实现图 8-9 调整标签大小和位置图 8-10 命令按钮控件17案例实现2.定义控件事件（1）在用户界面窗体上，右击，在弹出的快捷菜单中选择“查看代码”选项，即可打开代码编辑窗口，并输入代码，如图 8-11 所示。图 8-11 输入代码 118案例实现（2）在 VBA 操作界面的左侧列表中，双击 ThisWorkbook 选项，打开代码编辑窗口，输入相应的代码

6、，如图 8-12 所示。图 8-12 输入代码 219案例实现3.创建 Excel 文件打开事件（1）选择“文件”“打开”选项，进入“打开”界面，选择“计算机”选项，单击“浏览”按钮，弹出“打开”对话框，选择需要打开的工作簿，单击“打开”按钮。（2）这样即可打开工作簿，并显示内容禁用信息，单击“启用内容”按钮，弹出“登录界面”对话框，在“用户名”和“密码”文本框中输入用户名和密码，单击“确定”按钮，如图 8-13 所示。图 8-13 输入用户名和密码20案例实现4.创建分组框及选项按钮控件（1）选择 A1:G1 单元格区域，在“开始”选项卡的“对齐方式”组中，单击“合并后居中”按钮，合并单元格

7、。（2）在单元格中输入文本“跑酷用品市场问卷调查表”，设置其字体格式为“方正大黑简体、18”，如图 8-14 所示。图 8-14 输入表头文本21案例实现（3）切换至“开发工具”选项卡，在“控件”组中，单击“插入”下拉按钮，展开下拉列表，单击“分组框”控件，如图 8-15 所示。（4）在工作表中，按住鼠标左键并拖曳，绘制一个分组框，修改其显示文字为“您的性别？”，并取消选中“网格线”复选框，隐藏网格线。（5）切换至“开发工具”选项卡，在“控件”组中，单击“插入”下拉按钮，展开下拉列表，单击“选项按钮”控件，如图 8-16 所示。22案例实现图 8-15 单击“分组框”控件图 8-16 单击“选

8、项按钮”控件23案例实现（6）在分组框中单击，插入“男”“女”两个选项按钮。（7）使用同样的方法，依次在工作表中创建分组框和选项按钮，完善问卷表，如图 8-17 所示。图 8-17 完善问卷表24必备知识宏是一个指令集，用来告诉 Excel 完成用户指定的动作。宏类似于计算机程序，但它是完全运行于 Excel 之中的，用户可以使用宏来完成枯燥的重复性工作。宏完成工作的速度比用户快得多。1.表格宏功能例如，可以创建一个宏，用来在工作表的每一行上输入一组日期，并使日期居中对齐，然后对此行应用边框格式。还可以创建一个宏，在“页面设置”对话框中指定打印设置并打印文档。25必备知识宏具有以下特点。18%

9、21%9%31%2341创建宏命令后，原本需要烦琐的操作步骤才能完成的设置，只需要按几个键或单击几个按钮，便可实现相同的操作，在简化了操作步骤的同时也节约了时间。减少错误发生率。简化操作步骤。具有重复性。26必备知识2.设置宏安全级别在创建带宏功能的工作簿后，用户需要对工作簿中的宏安全性进行设置，以防止黑客利用宏进行病毒的传播，为了预防计算机遭受宏病毒的侵害，Excel 中提供了可对宏的安全性进行设置的功能，其具体操作方法是：选择“文件”“选项”选项，打开“Excel 选项”对话框，在左侧列表中，选择“信任中心”选项，在右侧界面中，单击“信任中心设置”按钮，如图 8-18 所示，打开“信任中心

10、”对话框，在左侧列表中，选择“宏设置”选项，在右侧的“宏设置”选项区中，选中“禁用所有宏，并发出通知”单选按钮，单击“确定”按钮，即可设置宏安全性，如图 8-19 所示。27必备知识图 8-18 单击“信任中心设置”按钮图 8-19 设置宏安全性28必备知识3.使用宏完成重复动作宏本身具有很高的可重复性，因此对实际工作中重复率很高的工作有着非常高的使用价值。但由于宏只能影响其所在的工作簿，而当多个类似工作簿需要同样的操作过程时，则会变得非常不方便，这时可以将重复使用于多个类似工作簿中的宏功能设计制作成加载宏。29必备知识在设计加载宏时，需要单击“录制宏”按钮，打开“录制宏”对话框，如图 8-2

11、0 所示，在打开的对话框中需要根据提示进行操作，设置好宏的名称、快捷键及宏内容，当宏录制完成 后，则 需 要 停 止 宏 录 制，并 将 宏 文 件 设 置 为“Excel 加载宏”的保存类型，完成加载宏的保存操作。图 8-20“录制宏”对话框30必备知识在“Excel 选项”对话框中，在左侧的列表中，选择“加载项”选项，并单击右侧界面中的“转到”按钮，将弹出“加载宏”对话框，单击“浏览”按钮，即可根据提示一步一步地进行操作，将保存好的加载宏添加进去，再进行加载宏的使用，完成重复的动作。在完成加载宏的录制和保存操作后，由于 Excel的加载宏是一个特殊的 Excel 文件，因此需要经过添加才能

12、将一个加载宏加载到当前 Excel 文件中，然后才可以使用。31必备知识4.表格的各类控件控件是添加在窗体上的一些图形对象，具有显示或输入数据、执行特定操作、方便用户进行窗体阅读等功能。通常包含命令按钮、文本框、单选按钮、列表框等，这些控件主要是为用户提供选项、命令按钮、执行预置的宏或一些脚本的。在 Excel 2016 中有两种类型的控件：ActiveX 控件和表单控件。ActiveX 控件比较常用，多与 VBA 与 Web 脚本一起工作，而表单控件与早期的 Excel 5.0 之后的版本兼容，只能在 XML 宏工作表中使用。32必备知识在 Excel 2016 中，与控件相关的操作命令按钮

13、均放在“开发工具”选项卡的“控件”组中，如图 8-21 所示。常见的 ActiveX 控件包括按钮、组合框、复选框、数值调节按钮、列表框、单选按钮、分组框、标签等；常见的表单控件有按钮、组合框、复选框、数值调节按钮、列表框等，如图 8-22 所示。33必备知识图 8-21“控件”组图 8-22 常见控件34必备知识（1）ActiveX 控件。通常 ActiveX 控件是复选框、按钮等类型，可以在 VBA 中为控件编写宏，也可以在 Microsoft 脚本编辑器中编写脚本。ActiveX 控件通常用在联机窗体，需要使用控件进行多项控制时。（2）表单控件。表单控件也就是“窗体”控件工具栏，表单控件

14、允许用户控制程序图形界面上的对象，如文本框、复选框、滚动条、命令按钮等，其具有显示或输入数据、执行特定操作、方便用户进行窗体阅读等功能。例如，为用户提供选项时添加的列表框，为用户提供选择时添加的按钮等。35训练任务在素材文件夹中打开“临时工工资发放表”工作簿，然后进行控件添加与代码输入。具体编辑要求如下。（1）在“开发工具”选项卡的“控件”组中，单击“设计模式”按钮，开启设计模式。（1）在“开发工具”选项卡的“控件”组中，单击“设计模式”按钮，开启设计模式。（2）在“插入”列表框中，选择“表单控件”选项区中的“按钮（窗体控件）”控件，在工作簿中添加“筛选”和“恢复”按钮控件，并对添加的按钮控件

15、进行宏设置。最终 Excel 电子表格如图 8-23 所示。36必备知识图 8-23 临时工工资发放表案例2分析销售计划表38案例描述图 8-24 销售计划表效果销售计划表是用来登记公司产品销售情况的预算分析表格，其内容包含产品名称和各月的销售记录。销售计划表效果如图 8-24 所示。39案例分析实现本案例需要使用分析工具分析数据。要完成本案例，需要进行如下操作。（1）为随机抽样而生成随机数。（2）使用单变量求解解析数据。（3）单变量数据的模拟运算法。（4）采用方差分析获取稳定值。（5）使用检验工具校验平均差。（6）妙用抽样工具直接随机抽。（7）对完成后的抽样进行排位。（8）分析数据创建方案。

16、（9）显示与生成方案总结报告。40案例实现（1）启动 Excel 2016，单击“文件”选项卡，选择“打开”选项，进入“打开”界面，选择“计算机”选项，单击“浏览”按钮，打开“打开”对话框，选择“素材 模块 8 销售计划表.xlsx”工作簿，单击“打开”按钮，即可打开工作簿。1.为随机抽样而生成随机数41案例实现（2）在“数据”选项卡的“分析”组中，单击“数据分析”按钮，弹出“数据分析”对话框，在“分析工具”下拉列表框中，选择“随机数发生器”选项，单击“确定”按钮，如图 8-25 所示。（3）弹出“随机数发生器”对话框，修改“变量个数”和“随机数个数”分别为“2”和“4”，在“分布”下拉列表框

17、中，选择“均匀”选项，单击“输出区域”右侧的按钮，如图 8-26 所示。42案例实现图 8-25 选择“随机数发生器”选项图 8-26“随机数发生器”对话框43案例实现图 8-27 生成随机数结果（4）弹出“随机数发生器”对话框，在工作表中，按住鼠标左键并拖曳，选择 A18:B21单元格区域。（5）按 Enter 键，返回到“随机数发生器”对话框，单击“确定”按钮，即可为随机抽样生成随机数，并为生成的随机数添加边框效果，如图 8-27 所示。44案例实现2.使用单变量求解解析数据（1）在工作表中选择 E10 单元格，输入公式符号“=”，在 E8 单元格上单击，完成单元格的引用；输入公式符号“*

18、”，在 E9 单元格上单击，完成单元格的引用。（2）按 Enter 键，即可使用公式计算数据，并显示出计算结果。（3）在单元格 E8 中输入产量 4100，在 E9 单元格中输入手机 1 产品直接材料成本“1200”，这样即可得到手机 1 产品的直接材料成本，如图 8-28 所示。（4）选择 E10 单元格，在“数据”选项卡的“预测”组中，单击“模拟分析”下拉按钮，展开下拉列表，选择“单变量求解”选项，如图 8-29 所示。45案例实现图 8-28 计算手机产品的直接材料成本图 8-29 选择“单变量求解”选项46案例实现（5）弹出“单变量求解”对话框，在“目标值”文本框中输入“4500000

19、”，在“可变单元格”右侧单击相应的按钮，在工作表中选择 E8 单元格作为可变单元格，单击“确定”按钮，如图 8-30 所示。（6）弹出“单变量求解状态”对话框，即可开始进行单变量求解，稍后将显示求解结果，并单击“确定”按钮。（7）返回到工作表中，完成数据的单变量求解操作，并查看求解的结果，如图 8-31所示。n技巧在默认情况下，“单变量求解”命令在它执行 100 次求解，且与指定目标值的差在 0.001 之内时停止计算。如果不需要这么高的精度，可以选择“选项”命令，单击“重新计算”，修改“最多次数”和“最大误差”值。47案例实现图 8-30“单变量求解”对话框图 8-31 查看求解结果48案例

20、实现3.单变量数据的模拟运算法（1）选择 E13 单元格，输入公式“=INT（370000/E9）”，按 Enter 键，即可使用公式计算数据，并显示出计算结果。（2）选择 D13:E16 单元格区域，在“数据”选项卡的“预测”组中，单击“模拟分析”下拉按钮，展开下拉列表，选择“模拟运算表”选项。49案例实现（3）弹出“模拟运算表”对话框，在“输入引用列的单元格”文本框中引用 E9 单元格，单击“确定”按钮，如图 8-32 所示。（4）返回到工作表中，完成数据的单变量模拟运算操作，并查看模拟运算结果，如图 8-33 所示。n技巧在使用模拟运算表求解金额时，除了可以使用单变量求解外，还可以使用双

21、变量求解。在“模拟运算表”对话框中，在“输入引用行的单元格”和“输入引用列的单元格”文本框中输入变量值，单击“确定”按钮即可。50案例实现图 8-32 引用 E9 单元格图 8-33 查看模拟运算结果51案例实现（1）在“数据”选项卡的“分析”组中，单击“数据分析”按钮，弹出“数据分析”对话框，在左侧列表中，选择“方差分析：单因素方差分析”选项，单击“确定”按钮。4.采用方差分析获取稳定值86%（2）弹出“方差分析：单因素方差分析”对话框，单击“输入区域”右侧的按钮，弹出“方差分析：单因素方差分析”对话框，在工作表中，选择 B3:G6 单元格区域。52案例实现（3）返回到“方差分析：单因素方差

22、分析”对话框，选中“输出区域”单选按钮，并引用 I1 单元格，单击“确定”按钮，如图 8-34 所示。（4）这样即可采用方差分析的方法获取稳定数据值，并查看工作表中的数据分析结果，如图 8-35 所示。53案例实现图 8-34“方差分析：单因素方差分析”对话框图 8-35 查看数据分析结果54案例实现5.使用检验工具校验平均差（1）在“数据”选项卡的“分析”组中，单击“数据分析”按钮，弹出“数据分析”对话框，在左侧列表中，选择“t-检验：平均值的成对二样本分析”选项，单击“确定”按钮。（2）弹出“t-检验：平均值的成对二样本分析”对话框，在“输入”选项区中，选择变量区域，选中“输出区域”单选按

23、钮，并引用输出区域，单击“确定”按钮，如图 8-36所示。（3）这样即可采用检验工具校验 3 月份和 4 月份的平均差数据，并查看工作表中的数据分析结果，如图 8-37 所示。55案例实现图 8-36“t-检验：平均值的成对二样本分析”对话框图 8-37 校验平均差数据56案例实现6.妙用抽样工具直接随机抽（1）在“数据”选项卡的“分析”组中，单击“数据分析”按钮，弹出“数据分析”对话框，在左侧列表中，选择“抽样”选项，单击“确定”按钮。（2）弹出“抽样”对话框，在“输入区域”列表框中引用输入区域，选中“随机”单选按钮，并修改“样本数”为“4”，选中“输出区域”单选按钮，并引用输出区域，然后单

24、击“确定”按钮，如图 8-38 所示。（3）这样即可采用抽样工具随机抽取数据，并查看工作表中的数据分析结果，如图 8-39所示。57案例实现图 8-38“抽样”对话框图 8-39 查看抽样数据58案例实现7.对完成后的抽样进行排位（1）在“数据”选项卡的“分析”组中，单击“数据分析”按钮，弹出“数据分析”对话框，在左侧列表中，选择“排位与百分比排位”选项，单击“确定”按钮。（2）弹出“排位与百分比排位”对话框，在“输入区域”列表框中引用输入区域，选中“输出区域”单选按钮，并引用输出区域，单击“确定”按钮，如图 8-40 所示。（3）这样即可对随机抽样的数据进行排位，并查看工作表中的数据分析结果

25、，如图 8-41 所示。59案例实现图 8-40“排位与百分比排位”对话框图 8-41 对抽样数据进行排位60案例实现8.分析数据，创建方案（1）切换至“产销预算”工作表，在“数据”选项卡的“预测”组中，单击“模拟分析”下拉按钮，展开下拉列表，选择“方案管理器”选项，弹出“方案管理器”对话框，单击“添加”按钮，如图 8-42 所示。（2）弹出“编辑方案”对话框，在“方案名”文本框中输入名称“最低利润”，在“可变单元格”文本框中引用 I8:I11 单元格区域，单击“确定”按钮，如图 8-43 所示。61案例实现图 8-42 单击“添加”按钮图 8-43 设置方案名和单元格62案例实现（3）弹出“

26、方案变量值”对话框，在各变量值文本框中依次输入数值“800”“500”“1000”和“1200”，然后单击“确定”按钮，如图 8-44 所示。（4）返回到“方案管理器”对话框，完成第一个方案的添加。（5）使用同样的方法，再次添加“最高利润”和“目标利润”方案，然后设置“最高利润”的方案变量值分别为“950”“750”“1300”和“1600”；“目标利润”的方案变量值分别为“1210”“1350”“1650”和“1700”。图 8-44 设置方案变量值63案例实现（1）在“数据”选项卡的“预测”组中，单击“模拟分析”下拉按钮，展开下拉列表，选择“方案管理器”选项，弹出“方案管理器”对话框，在“

27、方案”列表框中，选择“最低利润”方案，单击“显示”按钮。（2）这样即可显示方案的结果，如图 8-45 所示。9.显示方案与生成方案总结报告64案例实现图 8-45 查看“最低利润”方案结果65案例实现（3）使用同样的方法，在“方案”列表框中，依次选择“最高利润”方案和“目标利润”方案，在工作表中依次显示“最高利润”和“目标利润”方案结果。（4）在“方案管理器”对话框中，单击“摘要”按钮，打开“方案摘要”对话框，在“结果单元格”文本框中引用单元格，单击“确定”按钮，如图 8-46 所示。（5）这样即可新建一个工作表，并在新建的工作表中生成方案总结报告，如图 8-47所示。66案例实现图 8-46

28、 引用单元格图 8-47 生成方案总结报告67必备知识1.模拟分析（2）双变量模拟运算表。（1）单变量模拟运算表。68必备知识2.显示“数据分析”工具默认情况下，Excel 中将不显示“数据分析”工具，需要通过设置“Excel 选项”对话框才能显示。其具体的操作方法是：在“Excel 选项”对话框的左侧列表中选择“加载项”选项，并单击其右侧界面中的“转到”按钮，如图 8-48 所示，打开“加载项”对话框，选中“分析工具库”复选框，如图 8-49 所示，单击“确定”按钮，即可在“数据”选项卡的“分析”组中显示“数据分析”按钮。69必备知识图 8-48“Excel 选项”对话框图 8-49“加载项

29、”对话框70必备知识3.复杂数据分析如果需要开展复杂的统计或工程分析，则可使用分析工具库以节省操作步骤和时间。为每项分析提供数据和参数，此工具将使用适当的统计或工程宏函数来计算，并将结果显示在输出表格中。除了输出表格，某些工具还生成图表。71必备知识而其余的工作表中则显示清空格式的表格。要对其余的工作表执行数据分析，需要使用分析工具分别对每个工作表重新进行计算。这些数据分析函数一次只能在一个工作表上使用。当用户在分组的工作表上执行数据分析时，结果将显示在第一个工作表中，72必备知识分析工具库包含方差分析、相关系数、描述统计、指数平滑、F-检验、双样本方差、直方图、移动平均、随机数发生器、回归和

30、百分比排位等工具，选择不同的工具，可以进行不同的数据分析。73训练任务在 素 材 文 件 夹 中 打 开 一 个 名 为“实 验 数 据 表”的 工 作 簿，然 后 使用“数 据 分 析”功 能 分 析 数 据。具 体 制 作 要 求 如 下。（1）使 用“随 机 抽 取 器”命 令 随 机 抽 样 而 生 成 随 机 数。（2）使 用“抽 样”命 令 随 机 抽 样 数 据。（3）使 用“排 位 与 百 分 比 排 位”命 令 对 完 成 后 的 抽 样 数 据 进 行排 位 操 作。最 终 E x c e l 表 格 效 果 如 图 8-5 0 所 示。74训练任务图 8-50 数据分析最终效果演示完毕 谢谢观看