Excel公式和函数 典型案例—任务完成情况公示表
Excel公式和函数 典型案例-教师考核评分表
Excel 公式和函数 典型案例－教师考核评分表目前，各大高校都会定期或者不定期的对学校老师的综合素质进行考核，一方面可以对教师起到一定的监督作用，另一方面可以促进学校内部管理机制的改革，从而有助于提高学校整体的教学水平。
本例将学习“教师考核评分表”的制作方法，并利用SUM 和SUMPRODUCT 函数对符合条件的教师人数进行统计。
1．练习要点● 使用SUM 函数 ● 判断评分等级 ● 使用条件格式 ● 使用数组公式● 使用SUMPRODUCT 函数 2．操作步骤：（1）合并A1至I1单元格区域，输入标题文字，并设置其字体格式。
然后，单击【边框】下拉按钮，选择“双底框线”选项。
再在A2至I2单元格区域中，分别输入各字段名称，如图11-31所示。
图11-31 设置标题格式提 示 设置标题文字【字体】为“仿宋_GB2312”；【字号】为18，并单击【加粗】按钮。
设置字段名【字体】为“仿宋_GB2312”；【字号】为14。
（2）在A3至G20单元格区域中，分别输入相应的数据，并设置A2至I2单元格区域的【填充颜色】为“橙色”。
如图11-32所示。
选择效果显示效果显示图11-32 输入数据和设置单元格格式提示在“教师考核评分表”中，设置单元格中的中文数据【字体】为“微软雅黑”，西文数据的【字体】为Times New Roman 。
（3）选择H3单元格，并插入SUM函数，在弹出的【函数参数】对话框中，设置参数Number1为D3:G3，单击【确定】按钮，即可计算出第一位教师所得总分，如图11-33所示。
图11-33 计算教师总分技 巧 另外，用户也可以选择H3单元格，单击【函数库】组中的【自动求和】下拉按钮，执行【求和】命令，计算教师所得总分。
提 示 使用相同的方法，或者使用向下自动填充的功能，计算其他教师所得总分。
（4）选择I3单元格，并插入IF 函数，在【函数参数】对话框中，设置参数Logical_test 为“H3>=34”；参数Valu e_if_true 为“优秀”；参数Value_if_fals e 为“IF(H3>=30,"良好",IF(H3>=24,"合格","不合格"))”，如图11-34所示。
EXCEL表格公示大全
EXCEL表格公示大全Excel表格公式大全来源：郭志煌的日志1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和；2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数；3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名；4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分；7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分；8、分数段人数统计：（1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格；（2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；（7）=COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；说明：COUNTIF函数也可计算某一区域男、女生人数。
Excel公式和函数 典型案例—停车收费系统
Excel 公式和函数 典型案例—停车收费系统为方便停车费用的合计，可以制作一个停车收费系统。
该系统通过记录车位的不同，从而产生的费用的差异；以及通过记录停车与离开的时间，分别计算出累计的时间（如天数、小时和分钟），最后求出停车的总费用。
1．练习要点：●设置单元格格式 ● 应用公式 2．操作步骤：（1）新建一张空白工作表，在A1单元格中，输入“停车收费系统”文字，并合并A1至I1单元格区域。
然后，在【字体】组中，设置该字体的格式，如图3-26所示。
图3-26 设置字体格式 技 巧 右击标题文字，在弹出的【浮动工具栏】中，也可以设置【字体】为“方正姚体”；【字号】为22。
技 巧 单击【对齐方式】组中的【启动器对话框】按钮，在弹出的对话框中，启用【合并单元格】复选框，也可以合并单元格。
（2）在A2至I3单元格区域中，分别输入字段名称。
然后，选择A2至A3单元格，单击【对齐方式】组中的【合并后居中】按钮，如图3-27所示。
图3-27 合并单元格（3）运用相同的方法，合并B2和B3单元格、C2和C3单元格、H2和H3单元格以及I2和I3单元格。
（4）合并D2至G2单元格区域。
然后，分别选择G3和H3单元格，单击【对齐方式】组中的【自动换行】按钮，如图3-28所示。
单击设置效果合并单元格单击单击图3-28 自动换行技巧单击【对齐方式】组中的【启动器对话框】按钮，在弹出的对话框中，启用【自动换行】复选框，也可以使单元格自动换行。
（5）输入’001数据，然后将鼠标置于该单元格的填充柄上，向下拖动即可填充该单元格区域，如图3-29所示。
图3-29 自动填充 提 示 输入的’001数据为类文本型数据，该数据输入的单引号为英文输入法下的单引号。
（6）选择B4单元格，输入日期2008-3-18，并输入一个空格，再输入时间，即可完成日期与时间的混合输入，如图3-30所示。
图3-30 输入日期和时间（7）运用相同的方法，在B4至C13单元格区域中，输入相应的日期和时间。
“完成率”在excel中这样显示才漂亮！
“完成率”在excel中这样显示才漂亮！兰色说：exce中如果不会使用函数，很难想象表格处理工作是怎么进行的。
学会下面37个excel函数，工作效率提升十倍都是保守的说法。
1、ABS函数函数名称：ABS主要功能：求出相应数字的绝对值。
使用格式：ABS(number)参数说明：number代表需要求绝对值的数值或引用的单元格。
应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数(如100)还是负数(如-100)，B2中均显示出正数(如100)。
特别提醒：如果number参数不是数值，而是一些字符(如A等)，则B2中返回错误值“#VALUE!”。
2、AND函数函数名称：AND主要功能：返回逻辑值：如果所有参数值均为逻辑“真(TRUE)”，则返回逻辑“真(TRUE)”，反之返回逻辑“假(FALSE)”。
使用格式：AND(logical1,logical2, ...)参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。
应用举例：在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。
如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。
特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。
3、AVERAGE函数函数名称：AVERAGE主要功能：求出所有参数的算术平均值。
使用格式：AVERAGE(number1,number2,……)参数说明：number1,number2,……：需要求平均值的数值或引用单元格(区域)，参数不超过30个。
应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。
展示计划和完成量的Excel图表，你也试试
展示计划和完成量的Excel图表，你也试试小伙伴们好啊，今天老祝和大家一起学习一个图表制作技巧。
如下图所示，是某集团各分公司的销售业绩完成情况，其中的实际完成又分为上半年和下半年两个指标。
先来看看最终的图表效果：使用这种图表，不仅可以清楚的展示各个分公司的业绩完成情况，而且还能展示实际完成与计划的差异。
关键时候能提升一下biger哦。
接下来就看看如何实现这样的效果：（本教程使用Excel 2016制作）步骤1 调整数据源结构首先在每个分公司数据下面插入两个空行，然后将上半年和下半年的数据下移一行：步骤2 插入柱形图选中B1:D12单元格区域，按下图步骤插入柱形图。
步骤3 清除多余项目单击图表标题，按Delete键删除。
同样的方法，删除网格线和水平轴标签。
将图例项拖动到图表左上角位置。
此时的图表效果如下图所示。
步骤4 调整表格样式1、双击数据系列，将分类间距调整为5%2、选中垂直轴，按下图设置坐标轴选项。
实际操作时，可根据具体数值确定最小值和最大值以及间隔值。
3、然后按住Alt键不放拖动图表，使图表左上角与单元格对齐。
步骤5 调整表格样式1、为图表的数据系列和图表区设置填充颜色。
2、在图表顶部合并两行单元格，用来模拟图表标题和副标题。
3、在图表底部单元格输入分公司名称，用来模拟水平坐标轴。
4、最后将标题和底部单元格的底色设置成和图表区相同的颜色。
OK了：哦，有同学可能问了，左上角有个红色方块是啥情况？嗯嗯，就是插入了一个矩形而已，头上插花，你不觉得好看啊？光说不练假把式，你也试试吧~~ 图文制作：祝洪忠。
Excel公式和函数 典型案例—业务报表
Excel 公式和函数 典型案例—业务报表业务报表通常以一个月或三个月作统一汇总，从而判断出当前业务部门和销售部门发展的阶段。
本例运用相关函数来制作一个“业务报表”表格，实现查找功能。
然后，运用Excel 的图表功能来分析业务报表中的数据，让用户更清晰明了的观察出图表中数据的变化情况。
本例共分两部分，其内容如下： 第一部分 创建业务报表表格1．练习要点● 函数的应用● 数据有效性的应用 2．操作步骤：（1）在一张空白的工作表中，重命名Sheet1工作表为“业务报表”名称。
然后，在该工作表中创建一个“业务报表”表格，如图14-62所示。
图14-62 创建表格提示 选择A1至E1单元格区域，合并该区域。
然后，设置区域中的文字的【字体】为“微软雅黑”；【字号】为20。
提 示 选择A2至E22单元格区域，设置【字体】为“新细明体”；【字号】为12。
其中，“新细明体”字体可以从Internet 上下载。
（2）选择A2至E2单元格区域，单击【字体】组中的【填充颜色】下拉按钮，在【标准色】栏中，选择“浅绿”色块，如图14-63所示。
创建图14-63 设置填充颜色（3）选择A5至E6单元格区域，设置【填充颜色】为“橙色，强调文字颜色6，淡色80%”。
选择A9至E10单元格，设置【填充颜色】为“水绿色，强调文字颜色5，淡色60%”，如图14-64所示。
图14-64 设置填充颜色（4）运用相同的方法，分别设置该表格中的其他单元格的填充颜色。
（5）在“业务报表”表格的右侧创建一个“统计计算”表格，如图14-65所示。
图14-65 创建表格（6）选择H3单元格，输入“=MA X(IF($D$3:$D$22=G3,$E$3:$E$22))”公式，并按下Ctrl+Shift+Enter键，则该公式转变成数组公式，如图14-66所示。
图14-66 输入数组公式（7）选择H3单元格，将鼠标置于该单元格的填充柄上，向下拖动至H8单元格，即可复制公式，效果如图14-67所示。
Excel公式和函数 典型案例—自考成绩统计
Excel公式和函数典型案例—自考成绩统计在Excel中，成绩统计是较简单的一种数据库管理方法。
下面运用艺术字来制作表格的标题，并借助公式来统计学生的总成绩，以及通过设置不同的统计条件，来计算成绩的样本方差。
另外，还运用了分类汇总功能对工作表进行统计个数及平均分。
1．练习要点●插入艺术字●应用公式●排序●分类汇总2．操作步骤：（1）新建一张空白工作表，选择【插入】选项卡，单击【文本】组中的【艺术字】下拉按钮，选择“填充-无,轮廓-强调文字颜色6,发光强调文字颜色6”，效果如图14-21所示。
图14-21 插入艺术字（2）更改艺术字文字为“自考成绩统计”，并在【字体】组中，设置字体格式，如图14-22所示。
图14-22 设置艺术字字体格式提示在【字体】组中，设置【字体】为“华文新魏”；【字体颜色】为“红色”。
选择设置最终效果（3）在A4至G26单元格中，分别运用设置单元格对齐方式、填充颜色和添加边框等知识，创建如图14-23所示的成绩表格。
图14-23 创建表格提 示 选择A5至G5单元格区域，单击【字体】组中的【填充颜色】下拉按钮，选择“水绿色，强调文字颜色5，淡色60%”色块，即可设置单元格的填充颜色。
提 示 选择A5至G26单元格区域，单击【字体】组中的【边框】下拉按钮，执行【所有框线】命令，即可为单元格添加边框。
（4）在A28至G36单元格区域中，分别创建用于统计结果的表格，以及统计条件表格，如图14-24所示。
14-24 创建统计表格（5）选择G6单元格，并选择【公式】选项卡，在【函数库】组中单击【自动求和】下拉按钮，执行【求和】命令，如图14-25所示。
创建表格创建表格条件表格输入选择图14-25 自动求和（6）按Enter键后，即可在G6单元格中显示求和结果。
然后，将鼠标置于该单元格的填充柄上，向下拖动至G26单元格，如图14-26所示。
拖动图14-26 复制公式（7）选择D28单元格，单击【函数库】组中的【插入函数】按钮，即可弹出【插入函数】对话框。
EXCEL公式—SUM、IF、COUNT、VLOOKUP、数据透视表
EXCEL公式—SUM、IF、COUNT、VLOOKUP、数据透视表前⾔：本⽂档中公式设置的基础是OFFICE07及以上版本，WPS或者OFFICE03⼤同⼩异，可能位置不⼀样，请⾃⾏百度解决版本及位置差异⼀、SUM函数SUM函数的语法是：=SUM(number1，number2，……)，意思是求number1～numberN的和(⼀) 其中number可以是数字，也可以是数组1、数字的情况是：在单元格内输⼊：=SUM(1，2，3) 然后点击Enter键结果就是：62、数组的情况是：按下表情况来讲，26的公式组成为：=SUM(B2:B5)42的公式组成为：=SUM(C2:C5)68的公式组成为：=SUM(B2:B5,C2:C5)=SUM(B2:C5) 或者=SUM(B6:C6)如果要看到公式编制的具体情况，请双击上⼀个图表就能看到(⼆) SUM函数可以⾃⼰⼿动编制，也可以⽤EXCEL菜单直接⽣成1、⼿动编制就是⾃⼰⼿⼯输⼊，选中需要编制公式的单元格，直接输⼊公式2、EXCEL菜单直接⽣成就是利⽤EXCEL菜单栏预先设置的公式直接⽣成⾃⼰需要的公式在“开始”菜单右上⾓有图标选中需要求和的数字区域，然后点击该图标，这种⽅法的缺点是有时候会找不到求和的单元格在哪⾥；也可以先选中要⽣成求和数值的单元格，然后点击该图标，然后再选择需要求和的数字区域；即可⾃动⽣成求和数据。
⼆、VLOOKUPVLOOKUP函数的表达公式为:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）关于精确匹配或近似匹配，⽬前⼯作中遇到的所有的情况都⽤精确匹配来作为参数，实际表达为FALSE或者0 所以实际⼯作中VLOOKUP函数简化为：=vlookup(查找值,查找范围,查找列数,0)（最后⼀个必须是0，不可以是其他数字）通俗的讲，根据查找值参数，在查找范围的第⼀列搜索查找值，找到该值后，则返回值为：以第⼀列为准，往后推数查找列数值的这⼀列所对应的值。
Excel数据管理与图表分析 典型案例-房水电管理系统
Excel 数据管理与图表分析 典型案例－房水电管理系统房水电管理系统可以帮助物业公司计算每户的各种费用，同时可以使业主知道房屋缴费情况和每月水电费用的开支情况。
本例运用Excel 的公式和函数计算出每户本月的各项费用和总费用，并利用错误检查功能对工作表中的公式进行检查。
1．练习要点● 设置单元格格式● 公式和函数的应用● 审核公式的应用2．操作步骤：（1）新建一张空白工作表，在A1单元格中输入“房水电管理系统”文字，选择A1至K1单元格区域，单击【对齐方式】组中【合并后居中】，即可对单元格进行合并，如图2-94所示。
图2-94 合并单元格提 示在【字体】组中，设置【字体】为“黑体”；【字号】为14；并单击【加粗】按钮。
（2）在A2至K2单元格区域中，输入字段名信息。
然后，合并C2至E2单元设置选择格区域和G2至I2单元格区域，如图2-95所示。
效果选择图2-95 合并单元格（3）在A3至K3单元格区域中，输入字段名信息。
然后，合并A2至A3单元格区域、合并B2至B3单元格区域和K2至K3单元格区域，并设置字体格式，如图2-96所示。
效果合并设置图2-96 设置字体格式（4）在A4单元格中输入数据“’0101”，向下拖动填充柄至A13单元格，即可得到门牌号。
（5）在B4至D4单元格区域中，输入房租和电费信息，如图2-97所示。
输入数据图2-97 输入数据信息（6）在E4单元格中，输入“=D4-C4”公式，向下拖动填充柄至E13单元格，即可得到用电量，如图2-98所示。
输入结果图2-98 计算用电量（7）在F4单元格中，输入“=E4*$F$2”公式，向下拖动柄充柄至F13单元格，即可得到电费，如图2-99所示。
输入结果图2-99 计算电费（8）在G4和H4单元格区域中，输入水费信息。
然后，在I4单元格中，输入“=H4-G4”公式，向下拖动填充柄至I13单元格，即可得到用水量，如图2-100所示。
输入结果图2-100 计算用水量（9）在J4单元格中，输入“=I4*$J$2”公式，向下拖动柄充柄至J13单元格，即可得到水费，如图2-101所示。
实验九Excel公式和函数的使用
姓名实验报告成绩评语：评分项目评分内容分值评分结果预习情况是否完成预习工作。
要求按实验指导书进行预习、并完成实验报告初稿、编写程序。
20实验方案实验方案是否可行。
要求目的、任务明确、原理清楚、步骤可操作、对实验过程数据有记录方案、对可能的错误有预备方案。
30实验过程与结果实验结果是否正确。
要求完成实验过程中的数据记录、记录要完整和准确、内容要详细，实验结果要与预期的正确结果一致。
35实验结论实验结论是否正确。
要求根据实验原理对实验过程中的数据、实验结果进行分析，得出实验结论。
15总分100指导教师（签名）年月日说明：指导教师评分后，实验报告交院（系）办公室保存。
实验九Excel公式和函数的使用一、实验目的1．掌握Excel公式的应用。
2．掌握Exc1e常用函数的应用。
3．掌握三大地址的区别与应用。
二、实验设备和软件（实验条件）1、硬件设备：Pentium Ⅳ以上微型计算机。
2、软件：Windows XP、Excel 2003或以上版本。
三、实验内容任务1：算术公式和逻辑函数的使用任务2：常用函数的使用任务3：三大地址的使用任务4：计算周岁任务5：混合地址计算任务6：计算九九乘法表任务7：学生成绩表绩点的计算和奖学金等级的判定四、实验方案和步骤任务一：①计算补贴。
②计算年龄。
③计算应发工资。
④计算所得税。
⑤计算实发工资。
任务二：①将Sheet4工作表改名为“公式与函数”。
②用函数统计出“工资表”工作表中职工的平均年龄、所得税总额和最高实发工资。
③实现函数运算。
④用日期公式填入有规律变化的日期数据。
⑤输入电话号码升位表。
任务三：①工作表重命名。
②混合地址计算。
任务四：①计算周岁。
任务五：①计算税后本息合计。
任务六：①计算九九乘法表。
②任务七：③计算总评成绩。
④计算各学生的课程“绩点”。
⑤计算各学生的“平均绩点”，并判断其应获得的“奖学金等级”。
五、实验过程与结果任务一：实验过程：①在H3单元格中输入公式：=IF(G3>2000,G3*12%,IF(G3>1500,G3*8%,95))。
Excel公式和函数 典型案例—学生公寓用品采购情况表
Excel 公式和函数 典型案例—学生公寓用品采购情况表采购情况表能够体现要购买的物品名称、数量、单价，以及需要花费的金额等内容，从而使物品的采购情况更加清晰明白。
本例我们通过Excel 的条件格式，表格样式以及查找和引用函数等功能，来制作一个“学生公寓用品采购情况表”。
1．练习要点：● 设置填充格式 ● 添加边框 ● 使用条件格式 ● 应用表格样式● 使用HLOOKUP 函数 ● 使用MATCH 函数 2．操作步骤：（1）新建Excel 工作簿，在Sheet1工作表中，分别输入表头文字、字段名以及相关的数据，并设置其【对齐方式】均为“居中”，如图6-33所示。
图6-33 输入数据提 示 合并A1至F1单元格区域，设置其【字体】为“方正姚体”；【字号】为16。
然后，设置其他单元格区域的【字号】为12。
（2）选择A3至F3单元格区域，单击【数字】组中的【数字格式】下拉按钮，选择【货币】选项。
然后，设置A1单元格的【填充颜色】为“橙色”；A2至F2单元格区域为“茶色，背景2”，如图6-34所示。
图6-34 设置数字格式和填充颜色 提 示 选择A1至F3单元格区域，单击【边框】下拉按钮，选择【所有框线】选项。
（3）在A5至E13单元格区域中，输入相应的信息，并分别设置字体格式。
然后，选择B8至E13单元格区域，设置其【数字格式】为“百分比”，如图6-35所示。
输入单击选择效果显示图6-35 设置数字格式提示在设置单元格的数字格式时，需要通过单击【增加小数位数】按钮，来调整数据的显示效果。
（4）选择A5至E13单元格区域，单击【边框】下拉按钮，依次选择【所有框线】和【粗匣框线】选项。
然后，在A6单元格中，绘制一条“直线”形状，并设置其格式，如图6-36所示。
图6-36 添加边框和斜线表头提示选择A6单元格，将光标置于“数量”和“名称”文字之间，按Alt键和按Enter键进行换行，并对其位置进行调整。
提示选择A5单元格，设置其【填充颜色】为“橙色”。
Excel公式和函数 典型案例—射击比赛成绩统计
Excel公式和函数典型案例—射击比赛成绩统计射击是用枪支对准目标打靶的竞技项目，国际比赛有男女个人项目，也有团体项目。
目前，射击被当作是一种娱乐活动。
本例将对各运动员在射击比赛中的成绩进行记录，然后利用统计函数和数组公式，对各成绩段的频率进行统计。
1．练习要点● 自动求和 ● 自动计算排名● 统计不同成绩段人数 ● 数组公式 ● 冻结窗格 2．操作步骤：（1）合并B1至J1单元格区域，输入标题文字，并设置其字体格式。
然后，在B2至J2单元格中，输入各字段名称，并设置其【字体】为“方正姚体”；【字号】为12，如图8-83所示。
图8-83 输入标题和字段名称提 示设置标题文字的【字体】为“宋体”；【字号】为18。
（2）在B3至H41单元格区域中，输入各选手的姓名，以及各比赛项目的成绩，如图8-84所示。
图8-84 输入数据（3）选择I3单元格，并选择【公式】选项卡，单击【自动求和】按钮，选择C3至H3单元格区域，并按Enter 键，如图8-85所示。
图8-85 自动求和效果显示设置效果显示单击输入提示 拖动I3单元格右下角的填充柄，将公式复制到I4至I41单元格区域。
（4）选择J3单元格，在【编辑栏】中输入“=RANK(I3,$I$3:$I$62)”公式，并按Enter 键。
然后，将该公式复制到J4至J41单元格区域，如图8-86所示。
图8-86 计算名次（5）右击第2行的行号，执行【行高】命令，在弹出的【行高】对话框中，设置【行高】为19.5，如图8-87所示。
图8-87 调整行高（6）选择A3单元格，并选择【视图】选项卡，单击【窗口】组中的【冻结窗格】下拉按钮，执行【冻结拆分窗格】命令，如图8-88所示。
图8-88 冻结窗格 提 示 冻结窗格的目的在于滚动工作表的其余部分时，可以保持标题和字段名永远处于可见的状态。
（7）设置B1至J2的填充颜色为“橙色”。
然后，选择B3至J41单元格区域，单击【边效果显示输入设置效果显示选择执行框】下拉按钮，分别执行【所有框线】和【粗匣框线】命令，如图8-89所示。
Excel公式和函数 典型案例—学生成绩统计
Excel公式和函数典型案例—学生成绩统计在学校的教学活动中，通过统计分析学生的考试成绩，能够了解学校的教学情况，为改进以后的教学工作提供良好的依据。
利用Excel统计学生成绩，既能够节省时间和精力，同时又可以有效的避免因计算失误而发生的错误。
1．练习要点：●使用条件格式●自动求和●计算学生名次●COUNTA函数●COUNTIF函数●计算及格率和优秀率2．操作步骤：（1）在Sheet1工作表中，分别输入表头文字和各字段名，并设置其字体格式。
然后，选择A2至G2单元格区域，应用【数据和模型】栏中的“输出”单元格样式，如图7-61所示。
图7-61 应用单元格样式选择A1单元格，设置其【字体】为“华文行楷”；【字号】为20；【填提示充颜色】为“橄榄绿，强调文字颜色3，深色25％”。
（2）选择A3单元格，输入“’04011301”，并向下拖动其填充柄，填充至A33单元格区域。
然后，再输入其他的数据，如图7-62所示。
图7-62 输入学号及其他信息提示在输入“学号”时，需要先输入一个英文状态下的“’”单引号，表示以文本状态输入数字。
（3）选择F3单元格，单击【函数库】组中的【自动求和】下拉按钮，执行【求和】命令，并选择C3至E3单元格区域，即可计算出该学生的总分，如选择应用输入信息图7-63所示。
图7-63 自动求和提 示向下拖动F3单元格的填充柄，将公式复制到F4至F33单元格区域中。
（4）选择G3单元格，并插入RANK 函数，在【函数参数】对话框中，设置参数Number 为F3；参数Ref 为F3:F33，如图7-64所示。
图7-64 计算学生名次 技 巧 另外，也可以选择G3单元格，在【编辑栏】中输入“=RANK(F3,F3:F33)”公式，并按Enter 键。
（5）选择G3单元格，将其公式修改为“=RANK(F3,$F$3:$F$33)”。
然后，向下拖动其填充柄，将该公式复制到G4至G33单元格区域，如图7-65所示。
Excel在学校中的应用32-月考成绩对比图——交互式图表（三）
Excel在学校中的应用32-月考成绩对比图——交互式图表（三）4.10 月考成绩对比图——交互式图表（三）案例背景每逢考试结束，考试成绩登统完毕后，一般情况下学校要组织召开学生家长会，班主任在家长会上首先向学生家长通报本班考试的整体情况后，各科任课教师还要分别介绍各门课程的学习和考试情况，而学生家长除了关注班级整体情况外，应该更关心自己的孩子的每门课程考试成绩以及自己孩子的成绩在班级中的位置，细心地家长可能还会关心自己的孩子每门课程近期的学习状况和发展趋势。
在3.7节中介绍了通过利用数据的“自动筛选”隐藏行数据的特性与图表源数据联动的特性，通过筛选不同的学生实现图表的交互功能，3.8节通过使用Excel“窗体”来选择代表不同的课程考试成绩的列，切换图表中的“源数据”从而实现图表的交互功能。
本案例以2004年天津市某重点中学理科实验班部分学生高三阶段按时间顺序排列的四次月考的语文、数学、英语和物理四门课程考试成绩为例，在3.7节和3.8节的基础上进一步介绍“月考成绩对比图”交互式图表的制作与操作方法。
关键技术点要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识指定名称，粘贴名称列表，自定义筛选●图表应用图表的编辑，自动更新图表标题柱形图●函数应用 CHOOSE函数，AVERAGE函数，MAX函数，MIN 函数，ROUND函数，OFFSET函数●综合应用定义含有动态区域的名称，EXCEL“窗体”的使用，交互式图表最终效果展示4.10.1创建“月考成绩表”Step 1创建工作簿、重名工作表创建工作簿“月考成绩对比图.xls”，然后将工作表重命名为“月考”，并删除多余工作表。
Step 2输入班级考试成绩①在单元格区域A1:Q24输入学生姓名和各次月考各门课程原始成绩。
②在单元格区域A25:A27分别输入“平均分”，“最高分”和“最低分”。
③在单元格B25输入公式：“=ROUND(AVERAGE(B2:B24),1)”，然后按键确认。
Office 2016 办公软件高级应用第5章 Excel 公式和函数的应用
判断是否有一个条件满足
5.2.7 逻辑和文本函数
34
4．使用 LEFT 函数从文本左侧开始截取字符
LEFT 函数用于从一个文本字 符串的第一个字符开始截取指定 个数的字符，其语法结构为 LEFT(text,[num_chars])。其中， text 为必需参数，表示字符串； [num_chars] 为可选参数，表示 要截取的字符个数。
SUBSTITUTE 函数用于对指定的字符串进行替换，如果字符串中有多个相同的字符时， 则可以指定替换第几次出现的字符，类似 Excel 中的查找和替换功能，其语法结构为： SUBSTITUTE(text,old_text,new_text,[instance_nu])。
替 换 字 符
5.2.8 财务函数
2．追踪单元格的引用情况 追踪引用单元格
9
追踪从属单元格
5.1.4 审核公式
10
3．公式错误检查
操作方法：选中返回错误值的单元格，单击【公式】/【公式审核】组中的“错误检查”按钮 ， 打开“错误检查”对话框，在其中将显示出错的单元格以及出错的原因。
5.1.4 审核公式
11
4．查看公式求值过程
在检查公式时，如果公式较复杂，计算步骤较多，可通过 Excel?2016 提供的“公式求值”功 能，按公式的计算顺序逐步查看公式的计算过程，以便快速查看出到底是公式的哪步计算出错。
28
2．使用 HLOOKUP 函数在数组或区域中按列查找数据
HLOOKUP 函数用于在表格或数组的首行查找指定的数值，并返回表格或数组中指定行的同一列的 数值，其语法结构为 HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])。
Excel公式和函数 典型案例—奖金计算表
Excel公式和函数典型案例—奖金计算表良好的制度体系，是每个企业健康发展的重要保障，将员工的出勤率和奖金挂钩，可以进一步激发员工的积极性。
本例制作的奖金计算表，兼有考勤和计算奖金金额两种功能。
它是利用统计函数，通过对员工出勤情况的记录，自动得出员工的奖金金额。
1．练习要点● 插入特殊符号 ● 数据有效性 ● 使用时间函数● 使用COUNTIF 函数 ● 设置单元格格式 2．操作步骤：（1）在Sheet1工作表中，合并A1至AM1单元格区域，输入标题文字，并设置其【字体】为“微软雅黑”；【字号】为16，如图7-88所示。
图7-88 设置标题字体格式（2）在A2单元格中，输入“月份：”文字。
然后，选择B2单元格，在【编辑栏】中输入“=MONTH(TODAY())&"月"”公式，即可提取当月的月份，如图7-89所示。
图7-89 提取月份提 示 选择A2至B2单元格区域，设置其【字体】为“微软雅黑”；【字号】为12。
（3）合并A3至A4单元格区域，输入“姓名”文字，并在A5至A21单元格区域中输入员工姓名。
然后，合并B3至AF3单元格区域，输入“日期”文字，并在B4至AF4单元格区域中，输入数字1～31，如图7-90所示。
提 示 分别在B4和C4单元格中输入数字1和2，然后，选择该单元格区域，向右拖动填充至AF4单元格。
设置输入效果显示设置效果显示图7-90 合并单元格并输入信息（4）选择B 列至AF 列，右击任意一列的列标，执行【列宽】命令。
在弹出的对话框中，设置【列宽】为3.5，如图7-91所示。
图7-91 设置列宽技 巧 另外，也可以拖动任意两列之间的边界线，当显示为“宽度3.50（33象素）”时释放鼠标。
（5）分别合并AG3至AG4、AH3至AH4、AI3至AI4、AJ3至AJ4、AK3至AK4、AL3至AL4、AM3至AM4单元格区域，输入相应的字段名称，并单击【自动换行】按钮，如图7-92所示。
Excel公式和函数 典型案例-佳远运输公司货物运输表
Excel 公式和函数 典型案例－佳远运输公司货物运输表货物运输表主要包含一些卡车和货物的基本信息，利用Excel 中的数学和三角函数，可以在货物运输表中计算货物运输的次数和运输货物的数量，为运输公司的成本比较及在选择运输车量中带来很大的便捷。
1．练习要点● 设置单元格格式 ● TRUNC 函数 ● CEILING 函数 2．操作步骤：（1）新建一张空白工作表，在A1单元格中输入“佳远运输公司货物运输表”文字，并在A2至F2单元格区域中，分别输入各字段名。
然后，合并A1至F1单元格区域，如图12-33所示。
图12-33 创建表格提 示在A1至F1单元格区域中，设置对齐方式为【合并后居中】，即可合并单元格。
（2）在A3至D10单元格区域中，输入卡车的基本信息，在A11和A12单元格中，分别输入“送一次货物的数量”和“送完这批货物要跑几次”文字。
然后，合并单元格A11至E11和单元格A12至E12区域，如图12-34所示。
图12-34 输入基本信息（3）在E3单元格中，输入“=B3*C3*D3”公式，求出A 车车箱的体积。
复制此公式到该列的其他单元格中，如图12-35所示。
创建表格基本信息合并单元格图12-35 计算体积（4）在A17至D19单元格区域中，输入货物信息，在D19单元格中，输入“=A19*B19*C19”公式，即可得到货物的体积，如图12-36所示。
图12-36 货物信息表提 示计算卡车的最大容量，需要用到货物的体积。
（5）在F3单元格中，插入TRUNC 函数。
然后，在【参数设置】对话框中，设置参数Number 为E3/0.02268；参数Num_digits 为0，即可得到A 车装货物的最大容量，如图12-37所示。
图12-37 计算最大容量提 示A 车的载货量为E3，货物的体积为0.02268，所以参数Number （最大容量）为E3/0.02268。
因为装的货物不会有小数，所以设置参数Num_digits 为0技 巧选择F3单元格，把鼠标放到填充柄上向下拖动，即可得到其他车装货物的容量。
Excel公式和函数 典型案例—二月份日程表
Excel公式和函数典型案例—二月份日程表日程表是一种工作或日常安排表，可以帮助人们查看每日的行程，以免遗漏重要事件。
本例主要运用逻辑函数来判断该年为闰年还是平年，从而判断出二月份是否有29号，最终帮助用户制作一个“二月份日程表”表格。
1．练习要点：● 逻辑函数的应用 ● 拆分窗口 ● 插入批注 2．操作步骤：（1）新建一张空白工作表，在B3单元格中，输入“二月份日程表”文字。
然后，合并B3至F3单元格，并设置其字体格式，如图5-54所示。
图5-54 设置标题字体格式提 示 选择B3至F3单元格区域，在【对齐方式】组中，单击【合并后居中】按钮。
提 示 选择B3单元格，在【字体】组中，设置【字体】为“隶书”；【字号】为20；并单击【加粗】按钮。
（2）在B6单元格中，输入日期“2月1日”。
然后，选择该单元格，将鼠标置于其填充柄上，向下拖动至B33单元格，如图5-55所示。
图5-55 输入日期（3）选择C6单元格，输入“=IF(MOD(B6,2),"9:00 AM","3:00 PM")”公式。
然后，复制该公式至C33单元格，如图5-56所示。
图5-56 复制公式（4）在D6至F33单元格区域中，输入相应的字段信息，如图5-57所示。
效果设置拖动复制公式输入图5-57 输入字段信息（5）选择B34单元格，输入“=IF(OR(AND(MOD(F4,4)=0,MOD(F4,100)<>0),MOD(F4,400)=0),"2月29日","")”公式，即可计算出该年份是否有2月29日，如图5-58所示。
图5-58 输入公式提示 当F4单元格为空时，系统以默认数字0参与计算，由于0能被4整除，故返回日期为“2月29日”。
提 示 若F4单元格中的数字为闰年（如2008），则在B34单元格中显示日期；若为平年（如2007），则B34单元格中显示为空信息。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel公式和函数典型案例—任务完成情况公示表
为了调查某县07年劳动和社会保障局任务完成情况，可以在Excel工作表中，使用COUNTIF函数来统计完成任务的村镇个数。
下面运用插入形状，以及简单的公式进行制作“任务完成情况公示表”表格。
1．练习要点
● 使用形状 ● 使用公式 2．操作步骤：
（1）新建一张空白工作表，在B1单元格中，输入“雪竹县劳动和社会保障局07年任务完成情况公示表”文字。
然后，设置该文字的字体格式，并单击【对齐方式】组中的【合并后居中】按钮，如图12-23所示。
图12-23 设置字体格式
提
示 在【字体】组中，设置【字号】为18；并单击【加粗】按钮。
（2）选择【插入】选项卡，单击【插图】组中的【形状】下拉按钮，选择【箭头总汇】栏中的“左右箭头”项。
然后，绘制该形状，如图12-24所示。
图12-24 绘制“左右箭头”形状
提 示 选择“左右箭头”项后，鼠标此时将变为实心“+”十字形状，从左向右拖动即可
绘制该形状。
（3）选择所绘制的“左右箭头”形状，设置【形状填充】为“无填充颜色”。
然后，单击【形状效果】下拉按钮，在【发光】级联菜单中，选择【发光变体】栏中的“强调文字颜色3，5pt 发光”项，如图12-25所示。
单击 设置
选择
绘制形状
图12-25 设置“左右箭头”形状格式
提
示 选择“左右箭头”形状，并选择【格式】选项卡，单击【形状样式】组中的【形
状填充】下拉按钮，执行【无填充颜色】命令，即可设置形状的填充颜色为无。
（4）运用Excel 中的自动换行和合并单元格等功能，完成如图12-26所示的表格的创建。
图12-26 创建表格
（5）选择E7单元格，输入“=ROUNDDOWN((D7/C7),2)”公式，计算曹马村“下岗失业人员再就业人数（人）”任务完成量。
然后，复制该公式至此列的其他单元格中，如图12-27所示。
图12-27 计算下岗失业人员再就业任务完成情况
提 示 将鼠标置于E7单元格的填充柄上，向下拖动至E29单元格，即可复制公式。
（6）选择H7单元格，输入“=ROUNDDOWN((G7/F7),2)”公式。
然后，复制此公式至该列的其他单元格中，如图12-28所示。
执行
选择
最终效果
创建
表格
输入
输入
图12-28 计算“企业养老保险新增参保人数”完成情况
（7）选择K7单元格，输入“=ROUNDDOWN((J7/I7),2)”公式。
然后，复制此公式至该列的其他单元格中，如图12-29所示。
输入
图12-29 计算“基本医疗保险新增参保人数（人）”完成情况
（8）选择N7单元格，输入“=ROUNDDOWN((M7/L7),2)”公式。
然后，复制此公式至该列的其他单元格，如图12-30所示。
输入
图12-30 计算“工伤保险新增参保人数（人）”完成情况
（9）选择E30单元格，输入“=COUNTIF(E7:E29,"<1")”公式，即可求出“下岗失业人员再就业人数（人）”中没有完成任务的村镇个数，如图12-31所示。
输入
图12-31 计算没有完成目标的个数
（10）选择H30、K30和N30单元格，分别输入“=COUNTIF(H7:H29,"<1")”、“=COUNTIF(K7:K29,"<1")”和“=COUNTIF(N7:N29,"<1")”公式，求出其他部门未完成目标的村镇个数，如图12-32所示。
图12-32 计算没有完成目标的村镇个数
（11）单击Office按钮，执行【打印】|【打印预览】命令，即可查看工作表的制作效果。