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表格公式大全来源：郭志煌的日志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 公式和函数 典型案例—停车收费系统为方便停车费用的合计，可以制作一个停车收费系统。

该系统通过记录车位的不同，从而产生的费用的差异；以及通过记录停车与离开的时间，分别计算出累计的时间（如天数、小时和分钟），最后求出停车的总费用。

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中这样显示才漂亮！兰色说：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图表，你也试试小伙伴们好啊，今天老祝和大家一起学习一个图表制作技巧。

如下图所示，是某集团各分公司的销售业绩完成情况，其中的实际完成又分为上半年和下半年两个指标。

先来看看最终的图表效果：使用这种图表，不仅可以清楚的展示各个分公司的业绩完成情况，而且还能展示实际完成与计划的差异。

关键时候能提升一下biger哦。

接下来就看看如何实现这样的效果：（本教程使用Excel 2016制作）步骤1 调整数据源结构首先在每个分公司数据下面插入两个空行，然后将上半年和下半年的数据下移一行：步骤2 插入柱形图选中B1:D12单元格区域，按下图步骤插入柱形图。

步骤3 清除多余项目单击图表标题，按Delete键删除。

同样的方法，删除网格线和水平轴标签。

将图例项拖动到图表左上角位置。

此时的图表效果如下图所示。

步骤4 调整表格样式1、双击数据系列，将分类间距调整为5%2、选中垂直轴，按下图设置坐标轴选项。

实际操作时，可根据具体数值确定最小值和最大值以及间隔值。

3、然后按住Alt键不放拖动图表，使图表左上角与单元格对齐。

步骤5 调整表格样式1、为图表的数据系列和图表区设置填充颜色。

2、在图表顶部合并两行单元格，用来模拟图表标题和副标题。

3、在图表底部单元格输入分公司名称，用来模拟水平坐标轴。

4、最后将标题和底部单元格的底色设置成和图表区相同的颜色。

OK了：哦，有同学可能问了，左上角有个红色方块是啥情况？嗯嗯，就是插入了一个矩形而已，头上插花，你不觉得好看啊？光说不练假把式，你也试试吧~~ 图文制作：祝洪忠。

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中，成绩统计是较简单的一种数据库管理方法。

下面运用艺术字来制作表格的标题，并借助公式来统计学生的总成绩，以及通过设置不同的统计条件，来计算成绩的样本方差。

另外，还运用了分类汇总功能对工作表进行统计个数及平均分。

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、数据透视表前⾔：本⽂档中公式设置的基础是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 的公式和函数计算出每户本月的各项费用和总费用，并利用错误检查功能对工作表中的公式进行检查。

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所示。

姓名实验报告成绩评语：评分项目评分内容分值评分结果预习情况是否完成预习工作。

要求按实验指导书进行预习、并完成实验报告初稿、编写程序。

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))。