下载文档原格式
3.5 综合案例3.5.1案例分析本节通过建立一个工资表的Excel电子表格，使读者进一步掌握电子表格的输入技巧，如何查看数据量大的表格，了解数据透视表的使用。
设计要求本电子表格共有四个工作表，其中，一个工作表为9月工资表，如图3.88所示；一个工作表为9月水电读数；一个工作表为应发工资统计图，如图3.89所示；一个工作表为数据透视表，如图3.90所示。
图3.88 9月工资表图3.89 应发工资统计图图3.90 数据透视表3.5.2设计步骤打开电子表格文件“工资表原始”，执行以下操作。
1．工作表管理（1）修改工作表的名称具体要求将sheet1工作表改名为“9月工资表”操作步骤在“Sheet1”标签上双击，“Sheet1”处于反白状态，输入新的工作表名称：“9月工资表”，按回车键确认。
（2）删除工作表具体要求删除Sheet2工作表操作步骤在“Sheet2”工作表标签上单击鼠标右键，选择快捷菜单中的“删除”命令。
（3）复制工作表具体要求将“水电表原始”工作簿的“9月水电读数”工作表复制到本工作簿文件中。
步骤1 打开“水电表原始”工作簿，切换至“9月水电读数”工作表，选择“编辑”|“移动或复制工作表”命令，打开“移动或复制工作簿”对话框。
步骤2 在“移动或复制工作簿”对话框中，如图3.91所示。
在“工作簿”下拉列表中选择“工资表原始”，在“下列选定工作表之前”选择“移至最后”，选中“建立副本”复选框。
单击“确定”按钮后，在本工作簿的最后，新增了一个“9月水电读数”工作表。
图3.91“移动或复制”工作表对话框图 3.92录入数据的快捷菜单图3.93 从列表中选择数据2．编辑“工资表”数据单击“工资表”标签，切换到“工资表”工作表。
（1）插入行具体要求在第11行前插入一行：林致远，1974-2-8，长沙，财会部，科级，1430。
操作步骤选中11行（姓名张志峰）的任一单元格作为活动单元格，选择“插入”|“行”命令，则11行的前面插入了空行。
利用Excel建立分期偿还贷款明细分析模型一、案例的提出2006年12月1日,甲从中国银行申请住房商业贷款15万元,贷款年限15年,采用等额本息付款方式按月偿还贷款,其间中国银行利率变化了四次:2006年利率为3.5127%,2007年利率为6.156%,2008年利率为6.9977%,2009年利率为4.4254%(注:前三年的利率在基准利率上打九折,2009年的利率在基准利率上打七折)。
问:1.每年的月偿还额为多少元?2.至2009年底,甲共偿还多少贷款?其中本金多少?利息多少?如果这时想提前偿还贷款,还需向银行支付多少元?二、模型的建立由于知道现值(150 000元),利率(变动利率)和期限(15年),求每年支付的金额(年金),可以用时间价值函数中的年金函数(PMT)、年金中的本金函数(PPMT)和年金中的利息函数(IPMT)来分别计算月偿还额、月偿还额中的本金和利息,最后再用Excel中的常用计算函数求出累计偿还额、累计偿还本金和利息,以及剩余贷款金额。
(一)建立分期偿还贷款分析模型表创建一个新的工作簿,将其命名为“贷款偿还分析表”,在工作簿中选择一工作表,并将该工作表重命名为“分期偿还贷款明细分析表”,在此工作表上建立“分期偿还贷款分析模型”,如表1:各项目的公式设置如下:总付款期数:C8=C6*C7每期偿还金额:C9==ABS(PMT(C5/C7,C8,C4))该模型建立之后,每期偿还金额与贷款金额、贷款年利率、贷款年限、每年还款期数等因素之间建立了动态链接,可以通过直接输入数据的方式改变贷款金额、贷款年利率、贷款年限、每年还款期数中的任意一个或几个因素的值,来观察每期偿还金额的变化,选择一种当前能力所及的固定偿还金额进行贷款。
“蒋文”是把贷款金额和利率变化的多种结果综合显示到一张表上,便于决策,而本文讨论的重点不是决策,而是决策后需要了解的相关信息。
为了便于贷款额的明细分析,文中只显示一种结果,表1显示的结果就是案例中2006年的月偿还额 1 073.26元。
excel综合案例Excel是一款功能强大的电子表格软件，被广泛应用于各个领域。
它不仅可以进行数据的录入和计算，还可以进行数据分析和可视化展示。
在日常工作和学习中，我们经常会遇到需要用到Excel的情况。
下面我将通过一个综合案例，来展示Excel的一些常见功能和应用。
假设我们是一家电商公司的销售经理，需要对公司的销售数据进行分析和报告。
我们手头有一份包含了产品销售额、销售数量和销售日期的数据表格。
首先，我们需要计算每个产品的总销售额和总销售数量，以便了解产品的销售情况。
在Excel中，我们可以使用SUM函数来计算某一列的总和。
我们可以在一个空白单元格中输入“=SUM(数据范围)”来计算数据范围内的总和。
通过使用SUM函数，我们可以很方便地计算出每个产品的总销售额和总销售数量。
接下来，我们可以使用Excel的数据透视表功能对销售数据进行更深入的分析。
数据透视表可以帮助我们对大量的数据进行快速的汇总和分析。
我们可以根据需要选择要汇总的字段，并设置汇总方式，如求和、计数、平均值等。
通过数据透视表，我们可以轻松地生成销售额按产品和日期的汇总报表，以及销售数量按产品和日期的汇总报表。
除了数据透视表，Excel还提供了丰富的图表功能，可以将数据以图表的形式进行可视化展示。
通过图表，我们可以更直观地了解销售数据的趋势和变化。
在Excel中，我们可以选择合适的图表类型，如柱状图、折线图、饼图等，并根据需要进行图表的格式设置和样式调整。
通过图表，我们可以清晰地看到销售额和销售数量的变化趋势，以及产品之间的销售对比情况。
除了基本的计算和分析功能，Excel还提供了一些高级功能，如条件格式、数据验证和目标求解等。
条件格式可以帮助我们根据特定的条件对数据进行格式化显示，以便更好地突出重点和发现异常。
数据验证可以帮助我们设置数据输入的规则和限制，以确保数据的准确性和完整性。
目标求解可以帮助我们根据特定的目标和约束条件，寻找最优的解决方案。
10个Excel商务图表实战案例，教你⽤特殊符号打造⾼端图表！通常我们要制作图表，都是在Excel中通过数据来⽣成图表。
但是，今天所要给⼤家介绍的制作图表⽅法并⾮如此，它是利⽤⼀些特殊符号，然后通过函数完成对图表的制作。
这⾥我们会通过⼗个案例，详细的教⼤家制作⽅法！01条形图公式：=REPT('|',B2)REPT函数能够按照定义的次数重复显⽰定义的内容。
此时我们能够看到单元格中已经被竖线“|”填充满了，我们只需要更换⼀下字体颜⾊，条形图就算是完成了。
如果你不喜欢默认竖线这种效果，可以将字体换成「Playbill」就变成实⼼的了。
02漏⽃图公式：=REPT('|',B2/10)漏⽃图表在制作上是与条形图差不多的，只是多了⼀个居中对齐。
在公式上我加了⼀个“/10”是因为默认情况下数据条太长影响效果，除以⼗会缩短⼀点。
03带负数的柱形图正数公式：=IF(B2>0,REPT('|',B2),'')负数公式：=IF(B2<0,REPT('|',ABS(B2)),'')ABS求绝对值函数，可将负数返回正数。
字体「Playbill」（当然你如果喜欢默认竖条风格就不⽤改了）。
负数这边需要右对齐。
04⽢特图公式：=REPT(' ',B2-MIN($B$2:$B$6))&REPT('|',(C2-B2))REPT上⾯我们已经说过，能够按照定义的次数重复显⽰定义的内容。
这⾥的B2是计划开始⽇，减去MIN($B$2:$B$6)，MIN函数是返回的最⼩值，这⾥指的是返回“计划开始⽇”该列中最⼩的⼀个⽇期。
这样就能获取时间差数，⽽这个差数会显⽰' '空格。
&连接符，连接后⾯的条形图。
05旋风对⽐图公式：=REPT('|',B2/5)=REPT('|',C2/5)字体「Playbill」，然后修改⼀下颜⾊。
Excel事件⽰例（⼀）本节介绍两个事件⽰例，帮助⼤家学习理解事件的具体应⽤，代码会尽量分析的详细些帮助理解。
1、选中某个值时，⽤颜⾊标注同样的值。
⽰例⼀的效果如下，假如每周的值班如图，当在B1:C7单元格中选中⼀个值时，同样的值都会标注颜⾊。
具体代码如下，后⾯会逐段分析。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Range('b1:c7').Interior.ColorIndex = xlNoneIf Application.Intersect(Target, Range('b1:c7')) Is Nothing ThenExit SubEnd IfIf Target.Count > 1 ThenSet Target = Target.cells(1)End IfDim rng As RangeFor Each rng In Range('b1:c7')If rng.Value = Target.Value Thenrng.Interior.ColorIndex = 34End IfNextEnd Sub代码的整体结构是Worksheet_SelectionChange事件。
当单元格选择发⽣改变时，即触发事件将选中单元格传递到target参数。
Range('b1:c7').Interior.ColorIndex = xlNone⾸先每次事件触发时都将B1:C7单元格的区域的设置重新设置为⽆⾊。
If Application.Intersect(Target, Range('b1:c7')) Is Nothing ThenExit SubEnd If这段⽤于限制只有B1:C7的单元格区域选择改变时会触发SelectionChange事件。
【编号】001【题目】设计及填充游客信息表【部门】旅行社导游部1.设计及填充游客信息表【案例】某旅行社导游小牛每次出团都要填写这样的旅客的信息表，表中包含了游客的各种基本信息。
随着旅行社办公自动化程度的提高，小牛选择了用Excel软件来作为制作表格的工具。
在旅游旺季，随着旅行团人数的增多，填写表格过程中产生的重复工作越来越多，使小牛苦不堪言。
为了减少工作量提高工作效率，小牛决定通过使用Excel中的函数来解决这一问题。
通过对函数的学习和应用，表格中很多数据不再因为旅行团人数的变动而从新填写，大大减轻了小牛的工作量。
并且，小牛通过使用条件格式和数学统计方法，使旅行团中游客的信息更加明了直观，能准确了解游客的个体差异，并及时提供更加人性化的导游服务。
【评析】Excel电子表格系统不仅能完成普通的表格处理，还具有强大的数据计算能力。
Excel2003作为Microsoft Office系列软件中最为优秀的电子表格处理软件，允许使用公式及函数对数值进行计算。
公式是对数据进行分析计算的等式，使用公式可以对工作表中的数值进行加法、减法、乘法、除法等算术计算；在Excel中海提供了一批预定义的公式，称为函数。
公式或函数中引用的单元格中的数据修改后，公式的计算结果也会自动更新，这大大减少了表格设计人员的工作量，提高了工作效率。
Excel综合案例导入：在前面的课程中，我们已经对Excel的基本知识点进行了系统的讲解，包括Excel表格格式的设置、数据的计算（函数和公式）方法、数据分析（排序、筛选和分类汇总）和数据的可视化表现（图表的应用）。
本次课我们将通过一个综合的案例来加深同学们对这些基本应用的了解，这次课我们应用的案例来自某旅行社的旅客信息表，我们将通过函数的应用来减少表格统计的工作量，并通过一些特别的设置来增加表格的信息量，这也是我们这节课的重点。
在这张表格中，包含了很多的内容。
现在请大家先大致浏览一下在这张表格中包含的数据信息，并且思考一下，假设你自己是填写这张表格的工作人员，你将采用什么方法去填写，以减少工作量？（思考三十秒钟）在表格中，旅客的编号可以采用拖动填充柄的方式通过填写一个步长为1的等差数列来实现，旅客的姓名和身份证号也必须采用手动的方式填写。
3.5 综合案例3.5.1案例分析本节通过建立一个工资表的Excel电子表格，使读者进一步掌握电子表格的输入技巧，如何查看数据量大的表格，了解数据透视表的使用。
设计要求本电子表格共有四个工作表，其中，一个工作表为9月工资表，如图3.88所示；一个工作表为9月水电读数；一个工作表为应发工资统计图，如图3.89所示；一个工作表为数据透视表，如图3.90所示。
图3.88 9月工资表图3.89 应发工资统计图图3.90 数据透视表3.5.2设计步骤打开电子表格文件“工资表原始”，执行以下操作。
1．工作表管理（1）修改工作表的名称具体要求将sheet1工作表改名为“9月工资表”操作步骤在“Sheet1”标签上双击，“Sheet1”处于反白状态，输入新的工作表名称：“9月工资表”，按回车键确认。
（2）删除工作表具体要求删除Sheet2工作表操作步骤在“Sheet2”工作表标签上单击鼠标右键，选择快捷菜单中的“删除”命令。
（3）复制工作表具体要求将“水电表原始”工作簿的“9月水电读数”工作表复制到本工作簿文件中。
步骤1 打开“水电表原始”工作簿，切换至“9月水电读数”工作表，选择“编辑”|“移动或复制工作表”命令，打开“移动或复制工作簿”对话框。
步骤2 在“移动或复制工作簿”对话框中，如图3.91所示。
在“工作簿”下拉列表中选择“工资表原始”，在“下列选定工作表之前”选择“移至最后”，选中“建立副本”复选框。
单击“确定”按钮后，在本工作簿的最后，新增了一个“9月水电读数”工作表。
图3.91“移动或复制”工作表对话框图3.92录入数据的快捷菜单图3.93 从列表中选择数据2．编辑“工资表”数据单击“工资表”标签，切换到“工资表”工作表。
（1）插入行具体要求在第11行前插入一行：林致远，1974-2-8，长沙，财会部，科级，1430。
操作步骤选中11行（姓名张志峰）的任一单元格作为活动单元格，选择“插入”|“行”命令，则11行的前面插入了空行。
在空行中输入数据：林致远，1974-2-8，长沙，财会部，科级，1430。
技巧输入数据在输入分公司、部门、职务等级这几列的数据时，选中单元格后，单击鼠标右键，在快捷菜单中选择“从下拉列表中选择”，如图3.91所示。
单元格的下面出现列表，显示出上面的行中曾输入的数据，如图3.92所示。
用户可直接从列表中选择需要输入的数据。
（2）填充等差序列具体要求在A3至A32单元格中填充工号：1到30。
操作步骤工号1-30是等差序列，可采取下列方式填充。
➢在A3单元格输入1。
将鼠标放在A3单元格的填充柄上，按住Ctrl键，鼠标指针变为带有加号的实心十字架。
向下拖曳鼠标，填充到A32单元格。
➢在A3单元格输入1，在A4单元格输入2。
选中A3到A4单元格，鼠标指向A4单元格的填充柄，向下拖曳鼠标，填充到A32单元格。
➢在A3单元格输入1，选择“编辑”|“填充”|“序列”命令，打开“序列”对话框。
如图3.94所示。
在“序列产生在”选择“列”单选按钮，在“类型”选择“等差序列”单选按钮，在“步长值”文本框中输入1，在“终止值”文本框中输入30。
图3.94 “序列”对话框图3.95“数据有效性”对话框的“输入信息”选项卡（3）在多个单元格中输入相同的数据具体要求在H3至H32单元格中输入相同的生活补贴：220。
操作步骤在多个单元格中输入相同的数据，可采取下列方式：➢在H3单元格输入220，鼠标指向H3单元格的填充柄，向下拖曳鼠标，填充到H32单元格。
➢选中H3到H32单元格，输入数据220，再按Ctrl+Enter键。
（4）设置数据有效性具体要求在G3到G32单元格设置数据有效性：1000到4000。
步骤1 选中G3到G32单元格区域，选择“数据”|“有效性”命令，打开“数据有效性”对话框。
步骤2 在“数据有效性”对话框中，选择“设置”选项卡，在“允许”下拉列表中选择“小数”，在“数据”下拉列表中选择“介于”，在“最小值”文本框中输入“1000”，在“最大值”文本框中输入“4000”。
步骤3 选择“输入信息”选项卡，如图 3.95所示，在“输入信息”下拉列表中输入“1000-4000”。
步骤4 选择“出错警告”选项卡，在“错误信息”下拉列表中输入“基本工资最低1000，最高4000”。
当选中基本工资列的单元格为活动单元格时，下面出现标签显示设置的输入信息。
若输入的数据小于1000或大于4000，将会打开出错警告窗口，显示所设置的出错信息。
3．自定义序列具体要求将9月工资表的B3到B32单元格中的数据导入为自定义序列，在“9月水电读数”的A2到A31单元格中填充此序列。
输入数据时，用户可以填充“星期一、星期二、星期三……”或“甲、乙、丙……”等序列，这些都是系统预先设置的序列。
通过“工具”|“选项”，用户可根据自己的需要来定义序列。
步骤1 选择“工具”|“选项”命令，打开“选项”对话框。
步骤2 在“选项”对话框中，选择“自定义序列”选项卡，如图3.96所示。
单击“从单元格中导入序列”文本框右边的按钮，选定单元格区域B3到B32，单击“导入”按钮。
各位员工的姓名“陈源、邓小玲……”被定义为一个序列。
图3.96 导入自定义序列步骤3 切换到“9月水电读数”工作表，在A2单元格中输入“陈源”，鼠标指向单元格的填充柄，向下拖曳到第31行，将其他各位员工的名字将被填充出来。
4．编辑公式首先，通过公式数计算第一位职工的岗位津贴、应发工资、水费、电费、个人所得税、扣款合计和实发工资，再将公式填充到其他行。
（1）计算岗位津贴具体要求根据职务等级计算岗位津贴：厅级职务津贴为3000；处级职务津贴为2000；科级职务津贴为1000；办事员职务津贴为500。
操作步骤选中I3单元格，输入公式=IF(F3="厅级",3000, IF(F3="处级",2000,IF(F3="科级",1000, 500)))（2）计算应发工资具体要求计算应发工资，应发工资为基本工资、岗位津贴和生活补贴的和。
操作步骤选中J3单元格，输入公式=G3+H3+I3或=SUM(G3:I3)（3）计算水费具体要求计算水费，水费为用水度数与水费单价的乘积。
操作步骤选中K3单元格，输入公式=9月水电读数!B2*9月水电读数!F$2 由于输入公式所在的工作表是“9月工资表”，需要引用的是“9月水电读数”工作表的单元格，须在单元格名称前加上表名和！。
另外，向下填充公式时，所引用的水费单价F 2单元格的名称不应变化，须在2前要加上$,表示对行号的绝对引用。
（4）计算电费具体要求计算电费，电费为用电度数与电费单价的乘积。
操作步骤选中L3单元格，输入公式=9月水电读数!C2*水电表读数!F$3（5）计算个人所得税具体要求根据应发工资计算个人所得税，1000元以下不扣税，1000～2000元之间扣税5%，2000以上扣税10%。
操作步骤选中M3单元格，输入公式=IF（J3<1000,0,IF(J3<2000,(J3-1000)*0.05,1000*0.05+(J3-2000)*0.1))（6）计算扣款合计具体要求计算扣款合计，扣款合计为水费、电费和个人所得税的和。
操作步骤选中N3单元格，输入公式=K3+L3+M3或=SUM(K3:M3)（7）计算实发工资具体要求计算实发工资，实发工资为应发工资减去扣款合计。
操作步骤选中O3单元格，在编辑栏中输入公式=J3-N3（8）填充公式具体要求将公式填充到其他行。
操作步骤选中I3到O3单元格区域，鼠标指向选定单元格区域右下角的填充柄，填充到第32行，释放鼠标。
5．设置表格格式（1）设置单元格的数字格式具体要求设置C3到C32单元格的格式为“年月日”，J3到N32单元格保留小数点后一位。
步骤1 选中C3到C32单元格，单击鼠标右键，在快捷菜单中选择“设置单元格格式”命令，打开“单元格格式”对话框。
选择“数字”选项卡，如图3.97所示。
在“分类”列表框中选择“日期”，在“类型”列表框中选择“2001年3月14日”。
步骤2 选中J3到N32单元格，同样，打开“单元格格式”对话框。
选择“数字”选项卡，如图3.98所示，在“分类”的列表框中选择“数字”, 在“小数位数”的数值框中输入“1”。
图3.97 “单元格格式”对话框的“数字”选项卡图3.98 “单元格格式”对话框的“数字”选项卡（2）格式化单元格具体要求合并A1到O1单元格，设置单元格对齐方式为居中，字体为华文彩云，字号为24，字形加粗，字体颜色为红色。
步骤1 选中A1到O1单元格，单击“格式”工具栏的“合并及居中”按钮。
步骤2 在“格式”工具栏的“字体”下拉列表中选择“华文彩云”，“字号”下拉列表中选择24，单击“加粗”按钮，单击“字体颜色”按钮右边的小三角形，在其菜单中选择“红色”。
（3）设置单元格填充颜色具体要求设置C3到F32及K3到N32的填充颜色为浅绿色，G3到J32及O3到O32单元格的填充颜色为浅黄色操作步骤选中C3到F32单元格，按住CTRL键，再选中K3到N32单元格，单击“格式”工具栏的“填充颜色”按钮右边的小三角形，在下拉菜单中选择“浅绿色”。
选中G3到J32单元格，按住CTRL键不动，再选中O3到O32单元格，在“填充颜色”按钮的下拉菜单中选择“浅黄色”。
（4）复制格式具体要求选中A2到O2单元格，设置居中对齐，字形加粗，填充颜色为蓝色，字体颜色为白色。
并将此格式复制到A3到B32单元格。
步骤1 选中A2到O2单元格，单击“格式”工具栏的“居中”按钮，“加粗”按钮，单击“填充颜色”按钮右边的小三角形，在其下拉菜单中选择“深蓝色”。
单击“字体颜色”按钮右边的小三角形，在其下拉菜单中选择“白色”。
步骤2 选中A2单元格，选择“编辑”|“复制”命令。
步骤3 选中A3到B32单元格，选择“编辑”|“选择性粘贴”命令，打开“选择性粘贴”对话框。
如图3.99所示，选中“粘贴”下的“格式”单选按钮。
复制格式后，A2到B32单元格的格式为与A2单元格的格式一致，而数据并无变化。
图3.99 “选择性粘贴”对话框图3.100“单元格格式”对话框的“边框”选项卡（5）设置边框具体要求设置表格的外边框为粗线，内边框为细线。
操作步骤选中A2到O32单元格区域，选择“格式”|“单元格”命令，打开“单元格格式”对话框。
选择“边框”选项卡，如图3.100所示。
在“线条样式”中选择一种粗线，单击“预置”的“外边框”按钮；在“线条样式”中选择一种细线，单击“预置”的“内边框”按钮。
（6）设置列宽具体要求设置表格各列为最适合的列宽。
操作步骤鼠标指向A列的列号处，鼠标指针变为。
