《办公自动化应用技术教学资料》第4章

,办公自动化应用技术,项目四应用表格处理软件Excel,在本项目中，通过4个工作任务具体分析及操作，向读者展示Excel的强大功能。实用项目的处理可以在财务、行政、金融、经济、统计和审计等众多领域组织、计算和分析各种类型的数据，能够制作各种复杂的图表和财务统计表，进而大大提高了数据处理的效率。,目录,制作客户信息表,制作客户信息表,Excel 2013简介 Excel 2013工作界面工作簿文档窗口Excel 2013的基本操作14.设置字体格式15. Excel的数据类型,张梦瑶是某公司的后勤职员，接到主管任务，她需要为公司制作客户信息表。要求显示公司、姓名、 联系方式等基本信息，并对表格进行设置，使表格信息看起来更清晰。,制作客户信息表,任务一：制作客户信息表,由于Excel本身就是一个制表软件，它具有强大的数据分析处理功能。同时，用户易于操作，制表清晰、明了。因此，张梦瑶决定使用Excel来完成此任务。,任务一：制作客户信息表,任务实施,制作客户信息表,（1）从开始菜单启动Excel 2013软件，如下左图所示。（2）系统会自动新建一个空白的工作簿，如下右图所示。,（3）手动输入如下内容，如下左图所示。（4）选中A1至H1单元格，如下右图所示。,（5）用鼠标右键单击选定区域，右击，在弹出的快捷菜单中选择“设置单元格格式”选项，如下左图所示。（6）系统弹出“设置单元格格式”对话框，如下右图所示。,（7）切换到“对齐”选项卡，如下左图所示。（8）选择“水平对齐方式”为居中，如下右图所示。,（9）选择“垂直对齐方式”为居中。如下左图所示。（10）勾选“合并单元格”复选框，如下右图所示。,（11）单击“确定”按钮完成设置，返回工作表中，单元格的效果如下左图所示。（12）依次合并所需单元格，效果如下右图所示。,（13）选定标题所在单元格，设置标题“客户信息表”字体为“黑体”，如下左图所示。（14）设置“字号”为“14”号。如下右图所示。,（15）加粗标题字体，如下左图所示。（16）按此方法设置A2、A9和A14单元格字体为黑体，12号，加粗，设置后效果如下右图所示。,同时按Ctrl键，分别选中A2、A9、A14单元格，一次进行设置。,（17）设置其余字体为宋体、字号为10号，如下左图所示。（18）选择标题所在行，单击“开始单元格格式行高”选项，如下右图所示。,（19）弹出设置行高对话框，输入行高为“60”，如下左图所示。（20）单击“确定”按钮，返回工作簿，设置行高后效果如下右图所示。,（21）设置其余行行高为25，设置后效果如下左图所示。（22）选定整个表格内容，如下右图所示。,（23）右击，在打开的快捷菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框，切换到“边框”选项卡，如下左图所示。（24）设置边框效果如下右图所示。然后单击“确定”按钮完成设置。,（25）单击快速访问工具栏“保存”按钮，如下左图完成对制作好的表格的保存。的（26）系统弹出“另存为”对话框，选择文件保存路径，输入文件的保存名称为“客户信息表”，单击“保存”按钮，完成对文件的保存，如下右图所示。,（27）表格制作出来的最终效果如下图所示。,管理与分析客户加盟统计表,管理与分析客户加盟统计表,单元格引用输入公式函数行、列和单元格的操作条件格式,张小光是北京某网络中心的职员，他的工作任务是每天需要浏览新闻动态，阅读城市信息，整理资料，及时更新网络内容。,赵康是某公司的职员，接到上级领导任务，他需要制作客户加盟统计表。要求使用Excel公式进行客户加盟管理与分析，分出加盟客户的等级。,管理与分析客户加盟统计表,任务二：管理与分析客户加盟统计表,由于Excel提供了大量的公式以及函数，用户可根据需要选择合适的公式，计算出结果，方便实用。同时，计算出的结果利于管理分析数据。因此，赵康决定使用Excel公式来完成此任务。,任务二：管理与分析客户加盟统计表,任务实施,管理与分析客户加盟统计表,（1）新建一个工作表，双击Sheet1工作表标签，将其改名为“客户加盟表”，按Enter键确认，如下左图所示。（2）单击单元格A1，然后输入“客户加盟统计表”，按Enter键确认。（3）在区域A2:F2中输入各列标题，如下右图所示。,（4）选择区域A1:F1，然后单击功能区中的“开始对齐方式合并后居中合并后居中”选项，将所选单元格合并并将文字居中对齐，如下左图所示。（5）选择区域A2:F2，然后单击功能区中的“开始对齐方式居中”图标，将各列文字居中对齐，如下右图所示。,（6）将单元格A1:F1中的文字的字体设置为“华文楷体”，字号设置为“20”。然后将区域A2:F2中的文字加粗，如下左图所示。（7）选择区域A3:A19，然后输入公式“=”-“&(ROW()-2)”，如下右图所示。,（8）按Ctrl+Enter组合键，在区域A3:A19中输入带有前缀“JM”的编号，如下左图所示。这里的“JM”表示“加盟”二字的拼音首字母。（9）在区域B3:D19中输入加盟客户的其他信息，如下右图所示。,（10）输入联系电话。由于电话区号的第一位有可能是0，为了能够正确输入0，因此需要在输入联系电话前为输入区域设置文本格式。选择区域E3:E19，在功能区中的“开始字数字格式文本”选项。输入好的联系电话如下图所示。,如果是以分隔符将区号与电话号码分开，那么就无须设置单元格为文本格式，而直接输入电话号码即可。,（1）单击单元格E4，然后单击功能区中的“审阅批注新建批注”图标，进入批注编辑状态，如下左图所示。（2）在批注框里输入要说明的内容，例如输入“家庭电话”，如下右图所示。,（3）单击除批注框以外的其他区域，确认输入。这样就为单元格E4添加了一个批注。此时，单元格E4右上角将会显示小三角标记，如下左图所示，这就是批注标记。当鼠标指向该单元格时，将会自动显示批注，当鼠标移开该单元格时，批注所会自动隐藏。（4）按照相同的方法，为其他几个单元格添加批注，如下右图所示。,如果需要修改批注中的说明文字，先选择包含批注的单元格，然后单击功能区中“审阅批注编辑批注”图标，此时将显示批注框，然后修改其中的文字即可。也可以在批注框的边框上单击鼠标右键，在弹出的快捷菜单中选择“编辑批注”选项。,（1）选择区域A3:F19，然后单击功能区中的“开始样式条件格式新建规则”选项，打开“新建格式规则”对话框。（2）在“选择规则类型”列表中选择“使用公式确定要设置格式的单元格”，然后在“为符合此公式的值设置格式”文本框中输入公式“=MOD(ROW(),2)=1”，如下图所示。,公式说明：在公式“=MOD(ROW(),2)=1”中，ROW()返回当前行的行号，使用MOD(ROW(),2)判断当前行能够被2整除。不能被2整除的奇数行将返回1，而最后用MOD(ROW(),2的结果与1比较，奇数行将返回TRUE。,（3）由于条件格式只对返回为TRUE的设置格式，因此，最终将为所有奇数行设置底纹效果。单击“格式”按钮，打开“设置单元格格式”对话框，在“填充”选项卡中选择如下图所示的颜色。,（4）单击“确定”按钮，返回“新建格式规则”对话框。再次单击“确定”按钮，将看到所选区域中奇数行自动添加了灰色底纹，如下图所示。,（1）打开客户加盟表，单击单元格F3，然后输入公式“=CHOOSE(MATCH(D3,30000,20000,15000,10000,8000,7000,5000,2000,0),”A“,”B“,”C“,”D“,”E“,”F“,”E“,”G“)”，按Enter键，得到第一个客户的加盟等级，如下图所示。,公式说明：在上面的公式中，首先使用MATCH函数确定加盟费在数组30000,20000,15000,10000,8000,5000,2000中出现的位置编号，然后根据出现的位置编号，使用CHOOSE函数提取对应位置上的等级字母。,（2）双击单元格F3右下角的填充柄，将此公式向下复制，直到单元格F19，如下图所示。,（1）单击单元格H2，输入“各级客户统计”，然后将单元格H2与I2合并后居中。（2）在区域H3:H8中输入客户等级标记，如下图所示。,（3）单击单元格I3，然后输入以下公式“=COUNTIF(F3:F19,”A“)”。按Enter键，统计出A级客户的数量，如下图所示。,（4）在单元格I4:I8中分别输入公式“=COUNTIF(F3:F19,”A“)”，只不过需要将公式中的“A”改为“B”、“C”、“D”等不同级别的字母，即可统计出其他客户的加盟等级，如下图所示。,拓展提高：,随着企业ERP、MIS以及其他智能化管理系统的广泛使用，相当多的商务数据保存于数据库和其他电子文档内。而将这些电子文档直接导入到Excel工作表中，将避免重复录入的麻烦。导入数据的方法如下。（1）打开工作簿，切换至待导入数据的工作表。（2）单击“数据获取外部数据”选项组中选择一种导入文件方式，并按提示完成即可。,制作损益分析表,制作损益分析表,认识图表创建、设计图表修改图表布局数据透视表5. 数据透视图,张小光是北京某网络中心的职员，他的工作任务是每天需要浏览新闻动态，阅读城市信息，整理资料，及时更新网络内容。,杨张帅是某公司的职员，接到主管任务，他南要制作损益分析表。要求通过图表分析出上半年公司损益情况。,任务三：制作损益分析表,制作损益分析表,由于企业经营的主要目标之一是追求利润，损益分析表是衡量企业在某一段时间内获利能力的财务报表，其目的在于衡量企业的动态绩效。同时Excel拥有的图表分析功能，可以使用户快速查看报表情况，因此，张帅决定使用图表来完成此任务。,任务三：制作损益分析表,任务实施,制作损益分析表,（1）建立损益表，如下左图所示。（2）将标题重命名为“损益表”，如下右图所示。,（3）选定“A4”单元格， 如下左图所示。（4）将“一、主营业务收入”更改为“主营业务收入”，如下右图所示。,（5）选定“A5”单元格， 如下左图所示。（6）将“减：主营业处成本”改为“主营业务成本”如下右图所示。,（7）选定单元格区域D5数据区域，如下左图所示。（8）单击“插入图表”按钮，如下右图所示。,（9）弹出“插入图表”对话框，如下左图所示。（10）在这里使用“柱形图-三维柱形图”对损益分析表中的主营业务收入、主营业务成本、主营业务税金及附加进行分析，如下右图所示。,（11）单击选中工作表中的图表，如下左图所示。（12）单击“页面布局页面设置”按钮，如下右图所示。,（13）弹出“页面设置”对话框，单击“图表”标签，切换至“图表”选项卡。在“打印质量”选项区中选择“草稿品质”复选框，此项是确认打印的颜色体系。如下左图所示。（14）单击“打印预览”按钮，弹出“预览”窗口，如下右图所示，进行相关设置。,（15）设置完成后，最终制作出来的图表效果如下图所示。,制作企业日常费用表,制作企业日常费用表,数据排序筛选分类汇总合并计算,张小光是北京某网络中心的职员，他的工作任务是每天需要浏览新闻动态，阅读城市信息，整理资料，及时更新网络内容。,杨孙阳是某公司秘书处职员，接到秘收书长任务，他需要制作一份公司日常费用表。要求显示管理分析公司日常费用。,任务四：制作企业日常费用表,制作企业日常费用表,由于Excel为用户提供了强大的数据排序、筛选、汇总和合并计算功能，利用这些功能可以方便地从数据表中分析出公司日常费用数据信息，便于总结数据。因此，孙阳决定使用Excel排序、筛选、汇总来完成此任务。,任务四：制作企业日常费用表,任务实施,制作企业日常费用表,（1）新建工作簿文件，输入表格标题和列标志，并将其格式化，如下左图所示。（2）使用自动填充功能输入“序列”数据，并将其设置为“0000”样式，然后将涉及货币的“金额”列设置为“￥700.00”格式，最后输入除“所属部门”和“费用类别”列的相关数据。如下右图所示。,在单元格输入0001时，请0001，可以显示出“0001”，否则将显示“1”。,（3）单击选定“时间”列的所有单元格，如下左图所示。单击“数据数据工具数据验证数据验证”选项，打开“数据验证”对话框。（4）在“允许”下拉列表中选择“日期”选项，从“数据”下拉表中选择“介于”，在“开始日期”文本框中输入“2005-1-1”，在“结束日期”文本框中输入“2005-1-31”，如下右图所示，单击“确定”按钮返回工作表。,（5）在B3单元格中输入“2004-1-3”，系统会自动弹出“输入值非法”提示框，如下图所示。,（6）选中“所属部门”列，单击“数据数据工具数据验证数据验证”选项，在打开的“数据验证”对话框，从“允许”下拉列表中选择“序列”，如下左图所示。（7）在“来源”文本框中输入“研发部,销售部,企划部,秘书处,行政部”，如下右图所示。,（8）单击“确定”按钮确认对单元格数据有效性所作的设置，返回工作表中，如下左图所示。（9）选定单元格时，右侧会出现下拉列表按钮，单击下拉列表按钮，然后在设置的序列中选择符合员工的部门即可，如下右图所示。,（10）将所有员工隶属的部门在下拉列表选择填写，按照步骤5和步骤6所述的方法对“费用类别”列进行相同的设置，最后效果如下图所示。,（1）这里对“费用类别”进行排序。单击任意单元格，单击“数据排序和筛选排序”按钮，如下左图所示。（2）弹出“排序”对话框，在对话框中单击“主要关键字”旁的下拉三角形按钮，在弹出的下拉列表中用户可以选择“费用类别”列，然后在其右侧单击选中“降序”单选项，如下右图所示。,（3）击“确定”按钮，关闭“排序”对话框，返回工作表中，此时的费用表排序后效果如下图所示。,对数据清单排序（1）打开“排序”对话框后，在“排序”对话框设置“主要关键字”为“所属部门”项，设置“次要关键字”为“费用类别”项。设置“第三关键字”为“金额”项，然后均选中“降序”单选项，其余保持系统默认设置，如下左图所示。（2）单击“确定”按钮，关闭“排序”对话框，返回工作表中，此时工作表中的数据便会按照“所属部门”排序后再按“费用类别”进行排序，再在此基础上按照“金额”的多少进行排序，最后排序结果如下右图所示。,这里的所属部门排序是按第一个字的拼音字母降序排的，例如，研发部的“研”的第一个字母y排在销售部“销”的x后面，因此研发部排在销售部的前面。,（1）选定单元格D3F21区域，然后单击“数据排序和筛选高级”按钮，如下左图所示。（2）弹出“高级筛选”对话框，在对话框中的“方式”文本框中单击“在原有区域显示筛选结果”选项，在“列表区域”文本框中系统默认整个工作表，如下右图所示。,（3）然后单击“确定”按钮关闭“高级筛选”对话框，返回工作表中，此时可以看到筛选结果，如下图所示。,如果需要撤销“高级筛选”，只需选择“数据排序和筛选筛选清除”图标即可。,（1）单击选定数据区域任意单元格，单击“数据排序和筛选排序”图标，如下左图所示。（2）弹出“排序”对话框，在“主要关键字”下拉列表中选择“费用类别”项，按“升序”排序，如下右图所示。,（3）单击“确