EXCEL实操训练、常用函数使用.pdf

EXCEL实操训练录四、五、六、七、八、认识EXCEL与内容保护数据的输入数据管理绘制图表排版与打印数据透视表百分位值函数一、认识EXCEL与内容保护 工作表 单元格 冻结窗口 保护文件 保护工作菌 保护工作表 保护单元格工作表（Worksheet）:由单元格构成的表格，是工作簿的重要组成部分，又称电子（报）表格。工作簿是工作表的集合。一个工作簿可包含多个工作表。系统默认一个工作簿包含三个 工作表名称为：Sheetl,Sheet2,Sheet3o 一个工作 簿最多有255张互相独立的工 作表Sheet。叨 文件9 编指口 视图。茹入9 格式（Q）IMd）豹得Q）窗口&）帮勖比：二 d二 J J 个0 7 3 /二息 3B13 N1ABCJD E F攵治经济学成绩单GLHI-23学号姓名平时1平时平时2俅绩平均平时占 30%期天成绩:考试 期末占 70%评绩 总成4990101张小立869389.526.98660.287.156990102王娜908587.526.38257.483.7990103李丽霞253027.58.3402836.37390104赵强928789.526.99163.790.68990105张雨819085.525.79566.592.29990106存海688174.522.48660.282.6_10 11 空990107王慧文83918726.1906389.1990108鬻国强75858024.08458.882.8S90109马明明889390.527.29365.192.313990110刘伟928990.527.28861.688.8i A 殳 4)!人寓;网睇/碎收治*说堂里科彷缥同泉/为便于对单兀格的引用,系统赋予每个单元格一个地址:列标+行号。如：F6表示的是第F列第6行交叉点处的单元格；A1:D4表示的是A1到D4单元格之间的巨型区域。列号行号冻结工作表当一个工作表数据超过一屏幕时，滚动会使表头移出当前屏幕，而不利于观看。有 时，我们希望数据上下移动时，表头不动，左右移动时，表题列不动。菜单窗口 I拆分（【窗口】一冻结窗格）若要取消拆分：执行【窗口】/【取消拆分】命令取消冻结，可选择【窗口】T【撤消窗口冻结】。蛆织 if建文件夹国 Microsoft Excel收藏夫桌面一最近访问的位置E6F153J-(VUC9文档面板修改视图文音*8旨频片档乐23 4年11月以后HR工作 奈良类型快挂方式修改日期:2014/11/19 23:12 大小：199 KB匈 Excel函数实用大全【实例讲解】修改日期 2015/2/3 13:55 大小：965 XBsalebnarketing修改日期:2013/12/15 23:52学/类型：快捷方式大小：1.01 KBb刘让安2015年度目标计划表xlsx修沙日期 2014/10/23 22:47学P类再：快捷方式日常投送项区=人力资源部。）修的厂生成黄份文件）大j文件共孽打开权限击码）：1文件名3）：|取1函我实用大全实例讲解】修改权限密码通）厂保存类空（T）|Exe“97-2003工作簿 L 建议只读 取消作者：Administratorr保存缁略图隐i藏文件夹|标记添加府记工具3 目存 I 取消角函数 总数用函数2.保护工作簿工作簿的保护有两个方面：第一是保护工作簿，防止他人非 法访问；第二是禁止他人对工作簿或对工作簿窗口的非法操作。工具B）I数据 窗口也）帮即出）711 llflft 4100%fM息检索.Alt+Cli ck 二m y=；：-j-：,d错误检查也）.一 一加一共享工作区）共享工作海色）.一电话7里科N611U裸机价（元）1367CD92S2019GH3951860S10881730保护9 1联机协作3）公式审核自定义选项（Q）.力注M&撤消工作表保护化）.一视图 开发工具1-工仔工回二批注3、对工作表的保护如果不允许对工作簿中工作表进行移动.删除.插入、隐藏、取消隐藏.重命名或禁止对工 作簿窗口的移动.缩放、隐藏、取消隐藏或关闭，操作步骤如下：【工具】/保护/保护工作表如果选勾复选框，工作表将不能移动.删除.插 入等键入密码，若要取消这种保护，可以选择【工具】一保护一撤消工作簿保护。密码回选)9：*1允许此工作表的所有用户进行(A):y选定锁定单元格选定未锁定单元格 设置单元格格式 设置列格式 设置行格式 插入列 插入行 插入超链接册1除列 删除行 排序 使用自动筛选 使用数据透视表编辑对象确定取消4、保护单元格首先使工作表处于非保护状态（撤消工作表保护）选定需要锁定或取消锁定的单元格区域；【格式】一单元格/保护标签注意：清除“锁定”复选框并单击“确定按钮。重新对工作表实施保护后，本操作中取消 锁定的单元格就是可以进行修改的单元格，其余单元格为保护单元格。二.数据的输入 输入文本 输入数值 输入日期和时间 快速填充数据1、输入文本 默认单元格内左对齐 每个单元格最多包含32000个字符。文本包含任何字母、中文字符、数字和键盘符号组合。当数字作为文本输入时，数字前输入单引号表示输入的数字作为文本处理。如1234存储数据：Enter键或Tab键;上、下、左、右的光标键；或在其它单元格单击鼠标，都可以存储当前单元格中输入的数据。取消输入：若在未存储之前，取消输入的数据，按Esc键2、输入数字正数：直接输入负数：带括号或使用号，如：15或（15）均表示负数。小数：工具-选项编辑对齐方式：默认右对齐选项0图表颜色国联 II保存错误检查拼写检查安全性视图重新计算.编辑常规1-2-3的帮助自定义序列设置E 单元格内部直接编辑电）0扩展数据区域格式及公式0 单元格拖放功能Q）0 允许自动百分比输入（1）0 覆盖单元格内容前提出警告（A）0 显示粘贴选项按钮）Ml按Enter键后移动皿）21显不插入选项按钮国）方向：向丁 y-口自樱S数点 J正数，设置小数位数管器鲁黑器篙;复制和排序9负数，设置尾数o的个数提供动画反馈但）v-（Z记忆式键入）I确定1取消输纯分数：应在分数前加“0和一个空格，要避免把 输入的数字当作日期，如：0 3/4。提两点.（-）当要输入并显示多于11位的数字时，系统自动以科学记数法（如7.89E+08）表示，而 编辑栏中仍将显示输入的内容；无论显示的数字的位数如何，Excel单元格中只保留15位数字 精度。若超过15位的数字，则多余数字位将转换为0（低位）。rosof!:Excel 一 学生成绩工作簿.xls（二）当输入一个超过列宽的较长数字时，Excel会自动采用科如果单元格中 给出#标记，说明列宽已不足以显示数据（扩大列宽后数据会重新完 整地显示），但系统记忆”了该单元格的全部内容，当选中该单元格后，在 编辑栏中会显示完整内容。3、输入日期和时间日期和时间视为数字处理，右对齐。输入时，必须严格按照规则，当输入的不是合法的日 期时系统自动认为是文本，被当做字符串来处理，就左对齐了，如：2011.3.4。1）输入日期：使用分隔符/或-1996/10/1,1996-10-1,01-OCT-96J/OCT/963/4,3-4:则显示“3月4日2）输入时间:hh:mm:ss19:15,7:15空格PM,19时15分，下午7时15分（12小时制：AM或PM,PM或P表示下午，AM或A表示上午。如“8:20PM）3）日期和时间的组合输入既要输入日期又要输入时间，则用空格分隔开如:1996-10-01 19:15,96年10月1日19时15分。输入当天日期：ctrl+;组合 输入当前时间：ctrl+:组合4.填充在Excel中，利用数据的填充可以将大量数据迅速地输入到工作表中去.对于有 一定规律的数据和公式，Excel提供快速序列填充，主要包括等比序列、等差序 歹！k日期序列、自定义序列等，对于不同的序列，填充方法稍有不同。2007年9月8日2007年9月9日2007年 9 月 10S 2007年9月11日2007年9月12日2 3 42 4 813 5 7 9 11月 月月月月月月月月月月 一二三四五六七八九十十十13151719212364128256512102420482007年9月1日 2007车9月2日 2007年9月3日 2007年9月4日 2007车9月5日 2007车9月6日 2007年9月7日(1)填充柄自动填充适用于填充相同文本序列、等比序列、等差序列、日期序列甚至是 自定义序列，但必须告诉Excel在开始时如何去做。步骤如下：在第一个单元格区域中输入数据；选择填好数据的单元格或区域，将光标移至区域的右下角填充柄处；鼠标左键拖动填充柄至最后一个单元格松开鼠标即可；思考：若要填充相同的内容?0(2)在不连续单元格中输入相同内容A按住Ctrl键，选定所有需要输入相同数据的单元格在最后选定的单元格中输入数据，按Ctrl+Enter键5、单元格的修饰:(1)格式/单元格单元格格式回区I厂数学司 对齐II字体II边椎II图案育 分类()：.示例1月数字选项卡数值：设置小数位数 货币：设置货币样式 日期：日期的表示形式 百分比：0.2)20%科学计数法：0.145)1.45E-01用 数专比记义 E计期间分数学本殊定 运会曰时百分科文特自小数位数)：6口使用千位分隔符C)包)负数3):数值格式用于一般数字的表示。货市和会计格式则提供货面值计算的 专用格式。确定 I 取消 设置对齐方式默认情况下，Excel根据单元格中数据的类型，自动调整数据的 对齐方式。用户也可利用“单元格格式的对齐”选项卡进行设置。文本对齐方式：水平.垂直 文本控制：合并单元格 文本方向：旋转度设置字体字体选项卡：字体、字形、字号、下划线.颜色框线的设置:使用格式工具栏上的边框各按钮,或执行格式/单元格”命令，可以为表 格设置线条和边框线。单元格格式田区 1厂就绪12三例：将小于60分的用红色倾斜显示，大于90的绿色倾斜加粗条件格式可以包括字体样式、下划线、颜色和册除 线。确定清除取消(3)格式的复制使用格式刷：首先选中要复制格式的单元格；然后单击工具栏上的“格式刷”按钮，最后在要复制到的单元格上单击。选择性粘贴复制后，单击鼠标右键选择“选择性粘贴”选择性格贴团区1粘贴。隆割 1C有效性验证3)。公式CF)O 边植除外3)。数值(V)O列宽圆)。格式(X)O 公式和数字格式保)O批注。C值和数字格式(U)运算。无)。乘)。加(JD)。除d)。减)口 跳过空单元CB)口转置但)粘贴链接CD 确定 1 取消(4)单元格的插入与修改、移动与复制、清除与删除：插入：选中要插入的单元格或区域；选择【插入】一单元格，弹出“插入”对话框在提供的四种选择中选择所需的一种，再单击确定按钮。修改单元格内容：若在原数据上略加修改且数据较长时，可通过双击单元格来进行修改，此时状态栏 显示为“编辑”。也可单击要修改的单元格，重新录入新数据，状态栏显示为“就 绪O删除与清除:清除：将单元格或区域的内容清除，使之成为空白区域。删除：将单元格或区域的整个内容（包括内容和单元格本身）去掉，然后它的右邻（或下邻）单元格产生移动来顶替当前空间。数据的清除包括四个方面：数据的格式批注.内容及全部。我们可以只清除其中 一个方面，如只清除数据的内容，这时格式仍然保留。【例】清除单元格B3,B4,B5的全部内容。操作步骤如下：选定区域B3:B5;选择【编辑】一清除一全部说明：在清除子菜单中：全部：清除所有信息；格式：只清除格式，内容和批注不变内容：只清除内容，格式和批注不变；批注：只清除批注（在单元格的右上角有红色小三角的表示有批注）。若只需要清除数据的内容，保留数据的格式、批注等，还可以用以下简单的方法。方法一:选定B3:B5,按Del键。方法二：选定B3:B5,指针放在单元格区域内并用快捷菜单中的清除内容项/清除单元格：Backspace键或De忤建单元格还在，清除内容、格式、批注/删除单元格：单元格不存在，将选定的单元格从工作表中删除右键快捷菜单删除 或编辑菜单-删除(5)插入批注批注就是单元格的说明、备注。选择【插入】菜单的批注”命令,就会出现一个文本框，直接输入内容 即可。此时，在当前单元格的右上角有一 个红色三角形，当鼠标经过它时，批 注会自动显不。三、数据管理1、数据清单2、数据排序3、数据筛选4.分类汇总1、数据清单Excel工作表中单元格构成的矩形区域，又称数据列表。记录：数据清单中的每一行（第一行称为表头），每一行对应数据库中的一个 记录。字段：每一列称为字段。列标是数据库中的字段名称数据清单中不允许有空行或空列；不能有数据完全相同的两行记录。_|口”1AcE别1234567891011考号 姓名 2050031周雨 205004曹克强 205002夏微206001206002206003205001206005205004年龄 出生日期 笔试 机试 总分 平均分19 1982-8-7 40 65 105 52.5191982-8-1806814874211980-3-12677013768.5201981-12-34545904521 1980-12-3118 1983-10-120 1981-4-120 1981-9-118 1982-9-10674580874590703075501571151101629578.557.5558147.5论考过过考过过考考过考 士 F T KI und wnd KI k KI und Ki 经申通通彳通通未申通申三I Sh已已t!/She et 2/She et 3/lr2、数据排序(1)简单排序例如：如何对“学生成绩统计表.xls”中的学生按“计算机基础”成绩降序排列？(2)复杂数据排序例如：对“学生成绩统计表.xls中的学生按下列方式排列：首先按“计算机基础”成绩降序排，如果“计算机基础”成绩相等，则按“高等 数学”成绩降序排，如果高等数学”成绩也相等，则按“大学英语”成绩降序 排。WPS表格-曷文件（E）编辑（巳视图（或插入格式（8工具（D数据（0 窗口（立）帮助（to二 C,白卷T宋体 12因W 二繁gI B/U匡塞排序 团区I主要关键字1蓟整蕤y O 升序-降序也）次要关键字高等数学 5 o升序&）降序如第三关键字大学英语 y O升序Q）降序）我的数据区域Q）有标题行）Q 无标题行过）选项）.确定n1取消C主成绩统计 性别1 排序琳筛选（日分类汇总（由 有效性（口分列合并计算（由蛆及分级显示B数据透视表（已导入外部数据（8 OV!攵3、数据筛选当用户只对数据列表中一部分数据感兴趣时，可以使用数据筛选功能。数 据筛选将不感兴趣的记录暂时隐藏起来，只显示感兴趣的数据。数据筛选分为两种：自动筛选高级筛选步骤如下：在数据清单以外的位置建立条件区域数据筛选一自动/高级筛选“命令WPS表格学生成筌统计表.川5打:具 数据 窗口（贴 帮助 网络办公空间感排序M筛选 分类汇总（由二有效性词自动筛选（E）Ci统计分列合并计算（由 组及分级显示高级谛选（虫圜数据透视表（B）导入外部数据（8攵学 计算机基础8078 7H(1)1动筛选全吾B.显示所有彳亍前;。个：显示由以据项或百分比指定的上限或下限范围内的行。自定义：使用当前列中的两个条件值，或者其他比较运算符，而不是“与运算符（默认运算符）空白：只显示此列中含有空白单元格的数据行。非空白：只显示此列中含有数值的数据行。例1：将学生成绩统计表.xls中的计算机基础不及格的学生筛选出来？大学英i昔E1高等数学EL计算机基础升序排列 降序排列（前10个）（自定义）3656658590(2)高级筛选 能实现更复杂的筛选功能例1：将“学生成绩统计表.Xis”中的计算机基础不及格或者英语 不及格的学生筛选出来？例2:将“学生成绩统计表.xls”中的计算机基础不及格或者英语 不及格的男生筛选出来？ks cj 1 1.xl s一|口|x|123A 考号 10012310097101023212281010232123210102321234101023212354561010252208710102522088B C姓名 班级卢金成金融学O1 朱凯武计算机Ol 刘江平通信8 周弓星 通_信。工吴预平计算机O1 苏丹 计尊机O1蕈箔料计算机O1U3另6 8 5 9 0 3 27 6 6 7 8 8 7FG)H X_I一高等数学英语息、分|.858524665762097082217280967564 225896性别英语既级90；男16r选择不重复录取浩|SKeet2!$AS1：$H$8|SKeet2!$AS1O：$C$13|方式介在原有区域显示筛选结果gC格乖选结果复制到其他位置(Q)据区域OJ：件区城:计算机O11.建立条件区域，同一行上的条件关系为逻旷 2,打开高级筛选 不同行之间的关系从 对话框或确定4.单击“确定按钮4、分类汇总定义：是对数据清单按某个字段进行分类，将字段值相同的连续记录作为一类,进行求和、平均、计数等汇总运算。例如：求“学生成绩统计表.Xis“中男生和女生的英语平均成绩注意：在分类汇总之前，首先一定要对分类字段进行排序，否则分类汇总就没 有意义了。先要对数据清单按分类的关键字进行排序，再执行数据”的分类汇总“命令。例419在成绩表中，按班号字段分类汇总成绩”的 平均分（即统计出各个班的平均分）5、输入有效数据选定若干个单元格选“数据|有效性”命令数据有效性设置 I输入信息I出错警告I输入法模式有效性条件全部清除C)确定取消、绘制表用图表来描述电子表格中的数据是Excel的主要功能之一。Excel 能够将电子表格中的数据转换成各种类型的统计图表，更直观地揭 示数据之间的关系，反映数据的变化规律和发展趋势，使我们能一 目了然地进行数据分析。常用的图表类型有柱形图、条形图、饼图、折线图、XY散点图等（-）创建图表1.使用快捷键创建选定区域3按下F11键，自动创建2.使用向导（如图1.2.3.4）选择数据3插入菜单3图表类型少确定数据系列玲设 置图表选项3确定图表位置图表向导-4步囊之1-图表类型自定义类型I标准类型I 弱表类型子图表类型（IMII图形线图登 条折膏XY图图图图图积环达面泡面回雷曲气直BB!。比较相交于类别轴上的数按下不放可查看示例9|取消|上一步（）|下一步完成源数据数据区域系列100000图表向导-4步衰之3-图表选项匚:梃题二;|坐标轴I网格线|图例|数据标志数据表|图表标题CD:数据区系列产分系数类厂列厂值厂图表向导一 4步衰之4-图表位置将图表:1000009000080000r 作为新工作表插入:与作为其中的对象插入(Q):|图表2回回回取消 I 上一步 11 下一步 I 完成 如果选择“作为新工作表插入”单选按钮，会在该工作簿中创建一个图表工作表;如果选择”作为其中的对象“单选按钮，将创建一个嵌入图表。（二）认识图表I数值轴标题 万元100000产品月销售统计80000绘图区600004000020000图表标题销售额单价硬盘 CPU-1图例口内存-主板 合计整个图表名称分类轴标题数据系列（三）编辑图表元A选定图表项A选定绘图区A选定数据序列18001600140012001000800600400200工资表工资表伍娑蔚麦宕烙秦义笛迟德凯魏绊么曲倪蒂曾梧缭划售计划产产线 企销设企生生前五、排版与打印一页面设置文件（日编辑视图（口插入布1.页面J0匕劭S口嘀新建（由本机上的模板（打开（Q）Ctrl+O从在线文档中打开（份关闭关闭所有文档（D保存 Ctrl+S保存所有文档（日另存为（川 F12另存到在线文档（心页面设置（U）打印区域（D d打印预览（口 刍 打印（E）.Ctrl+P因 输出为PDF格式2.页边距页面设置QB页面 页边距：页眉/页脚工作表打印 一|打印预览世）|选项9.|左CD：页脚3|1 3 zzd居中方式I-水平居中 I-垂直居中9确定取消3.页眉和页脚4.工作表页面页边距页眉/页脚工作表确空 取消二、打印文档打印的方法与Word基本相同,下面对打印对话框不同点加以说明：选定区域：只打印工作表中选定的单元格区域。非相邻的选定区域 打印在独立页上。此项优于在“页面设置”对话框中设定的打印区域。打EP名称如I:态型置注 状类位备nskjt 500空闲HP DekTet 500LFT1:二Ir打ej到文件（1）TSSl力全部（A：i广页 由0:|一3 至二|一三JEPr 选定区域 r 整个工作簿6选定工作表QDJ分数打 印份数 ：E方E厂逐份打印预览世|确定 取消选定工作表：打印所有当前选定工作表的所有区域，按选定的页数打印。如果没有定义打印页数，则打印整个工作表。整个工作簿：打印当前工作簿中所有的工作表。也可直接单击R常用1一打印，将根据先前在“页面设置对 话框中指定的选项，直接打印当前活动工作表。打印预览与打印选择“文件菜单中的打印预览命令，或者单击“常用”工具栏中的“打 印预览“按钮进入打印预览方式。1.打印预览“下一页/上一页“按钮：当打印的工作表多于一页时，单击下一页按钮，可 以显示下一页；单击上一页按钮，可以显示上一页。缩放按钮：使工作表在放大视图和全页视图之间切换。打印按钮：可以打开打印”对话框。设置”按钮：可以打开“页面设置”对话框。页边距”按钮：可以调整页边距.页眉和页脚。“分页预览/普通视图”按钮：可以在分页预览视图和普通视图之间切换。“关闭按钮：可以返回到工作表的编辑窗口中。六、数据透视表 L数据透视表的建立-1）活动单元格在数据区域一 数据数据透视表和图表报告-2）下一步一下一步，注意 核实选定区域”是否正确-3）显示“3步骤之3”,如右图58-4）单击“版式按钮-5）如图“版式”设置对话框-将主分类字段拖入到行，次分 类字段拖到列，汇总字段拖到 数据区域。-注意：双击数据区域中的汇总 字段可在此设置汇总方式-6）确定一完成592.数据透视表的修改如图：右击汇总字段名，可隐藏”和进行“字段设置”123隐臧字段设置B I 6 D E F G职称34性别 V-工程师丁人 技术吊助工 总计5 6 7 8 91 OI男区和项；工 求和国:求和项:求和项:工费 求和项:奖金 求和项:收入450 2393.9设置单元格黑式G）通置报青格m CD裁霆透现由QD叫323456789011111111122求和项:工资的求知 求和项:奖金的求和 求和项：收入 的求和时向导!更翱数籍。）选定妗）值表923曲-A奖金性别 收入期及分5果然收）公式电金字段设置a）表地方919253768.915110767394507.980190032991451666539461066395280118257067.914531713929462142845g.95数据透视表工具栏60下图字段设置”对话框，可设 置汇总方式拖动”数据透视表“工具栏中的 字段名到透视表数据区，可添加 汇总字段61七.百分位值3.7804233.3992063.2682543.4194442.5273813.7833333.5967593.3968253.4417992.5357143.7939813.6230163.5726853.5856482.7712964.0342183.8255133.6820353.6732063.1362854.1344714.1235193.8240744.0841533.240374.2574074.2153.8553174.1037043.3759264.294634.2481484.2344444.3514813.40624350分位位置475分位位置5.550分位值4.033.833.683.673.1475分位值4.204.173.844.093.31 50分位位置=1+0.5*(71)75分位位置=1+0.75*(71)50分位值=(C4C3)*0+C4 75分位值=(C6C5)*0.5+C5八.EXCEL函数应用Excel的主要功能不在于它能显示、存储数据，更重要的是对数据的计算能力，它允许使用公式和函数对数据进行计算。(1)公式是对工作表中的数据进行分析与计算的等式。它以二开始，由常量、单元 格地址和函数组成。选定单元格输入=输入包括要计算的单元格相应的运算，回车键或者在编辑栏中4按钮即可。(2)函数是预先定义好的公式，由函数名、括号及括号内的参数组成。多个参数之间用 分隔。函数的分类：财务函数、日期与时间函数、数学与三角函数、统计 函数、查找与引用函数、数据库函数、文本函数、逻辑函数和信息函数等九类。2、单元格的引用相对引用：指当把一个含有单元格地址的公式复制到一个新的位置或者用一个公式填入一个区域时，公式中的单元格地址会随着改变。举例：将E16的求和结果复制到E17,E18,E19等.选定E16,在其中输入二C16-D16,回车确定输入使用编辑栏中复制命令选定E17到E19处，用编辑栏中粘贴命令，结果自动得出。目文件(E)编辑视图(或插入格式(9工具(!)数据(0窗口(迎)帮助(H)网络办公空伯而嘎2 宋体 12O学生成装统计表.=：L 目今b z u=z=r71b/U=L 粘贝占（Ctrl+V）-绝对引用：指在把公式复制或填入到新位置时，使其中的单元格地址保持不变。需在行号和列标前加上“$。例：在F2中输入公式=$C$2+$D$2+$E$2F2二$C$2+$D$2+$E$2ABCDE1姓名数学英语讨算机总康绩29801001李明567865199、39801002王强8085901999801004938775T C C4J 4419959801005魏清73628519969801006吴小庆756480:二设置绝对地址 需在行号和列 号前加符号$混合引用：是指在一个单元格地址中，既有绝对地址引用又有相对地址引用。例：在F2中输入公式=C2+$D$2+$E2F2 3=C2+$D$2+$E2ABCDEf 2T1姓名数学英语讨算机总最绩2|9801001李明567865199139801002王强808590sdl49801004丁小十938775246 I59801005魏清1736285236 I69801006吴小庆756480N；返回的 错误值产生的原因#!公式计算的结果太长，单兀格容纳不卜。可增加单兀 格列宽解决除数为。fflsr/A公式中无可用的数值或缺少函数参数於AME删除了公式中使用的名称或使用了不存在的名称及名 称拼写错误4ULL使用了不止确的区域运算符或不止确的单兀格引用4UM在需要数字参数的函数中使用了不能接受的参数，或 公式计算结果的数字太大或太小，Excel无法表示EF当单兀格引用无效时#VALUE需要数字或逻辑值时，输入了文本单结 构嵌套结构I国名称=SUM(A1O,B5:B1O,5O,37)-I至谷募之司用也令分F鬲畲土用造令造-=IF(AVERAGE(F2:F5)5O,SUM(G2:G5),O)函数参数常用符号或表示方法 函数公式中的文本必须用半角引号，如：东南汽车；而非直接输入东南汽 车或“东南汽车 连接符：&如：东南&汽车的值为东南汽车 空值/空格的表示法：空值：空格：”“相关数学符号:V （不等于）；=（大于等于）；V=（小于等于）单元格引用表示法:A2;$A$2;$A2;A$2;A2:B7;1:1;1:5;F:F;A:N函数列表教学模块函数名称课堂讲解函数（20个）VALUE、IF、SUMIF、COUNTIF.COUNTA、OFFSET.COLUMN.ROW.MOD.VLOOKUP.MATCH.LEFT.RIGHT.MID.REPLACE.SUBSTITUTE.LEN.REPT.INDEX.PERCENTRANK课后自学函数（25个）RANK、QUARTILE、SIGN、SUM、AVERAGE.SUBTOTAU AND.OR.ABS.ROUND.COUNTBLANK.INT.TRUNC.EXACT.ISEVEN.ISERROR.CONCATENATE.YEAR/MONTH/DAY.DATE.TODAY.WEEKDAY.EDATE.FREQUENCYVALUE 用途：将表示数字的文字串转换成数字。语法：VALUE(text)。参数：Text为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是 Excel可以识别的任意常数、日期或时间格式。如果Text不属于上述格式，则 VALUE函数返回错误值#丫人1_1!。Vallie演示车型销量（文本型态）销量（用v&l ue函数进行转换）富利卡200=VALUE(B2)得利卡300=VALUE(B3)菱绅400=VALUE(B4)戈蓝2000=VALUE(B5)总计=SUM(B2:B5)=SUM(C2:C5)车型车肖量（文本型态）车肖量（用value函数进行转换）富不If卡2 002 00得利卡 3 003 00菱绅400400戈诙2 0002 000总计O/2 900/V通过va 1 u e函数转换就可以 计算了型态的数字不可计算If:逻辑函数 功能：IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。它的 应用很广泛，可以使用函数IF对数值和公式进行条件检测。语法格式：IF（条件,A.B）。其中条件”表示计算结果为TRUE或FALSE的任意值或表达式，若 为常量，视为True。条件参数可使用任何比较运算符。条件返回的结果为真的话，则 执行A,否则执行B。IF函数仅可以嵌套七层,超过将会出错。IF函数的简单案例一性别为男者,称谓为XX先生;性别为女者,称谓为XX女士捱名性别称谓公式张三另张三先生=IF（B2=肘A2 那先L女士）李四女李四女士案例二:依下述税率表，利用if函数计算个人所得税（课后练习）坂救全月A貂被所得领（已打球起征点）范围逑并上球敬.1不越过500元的2000-2SD002越过50。元至28。元的部分2500-400010%253越过208 元至5000元的部分4000-700015%1254越迂S08元至20口0口元的部分7OOO-22DOO20%3755越迂208口/t 40000兀的部分22000-4200025%13756越过4000。60000元的部分42000-6200030%33757越过6000口/t 80000元的部分62000-820003眸63753it 80000 00000部分82000-10200040%103759越过1 0000。元的部分102000-4眸1 5375注意：因IF仅可嵌套七层，所以无法计算月薪超过102000者个人所得税假如A3为存放税前工资的单元格,个人所得税计算公式如下：=IF(A382000f(A3-2000)*40%-10375fIF(A362000f(A3-2000)*35%-6375fIF(A342000f(A3-2000)*30%-3375fIF(A322000f(A3-2000)*25%-1375fIF(A37000f(A3-2000)*20%-375fIF(A34000f(A3-2000)*15%-125fIF(A32500f(A3-2000)*10%-25fIF(A32000f(A3-2000)*5%,。)Sumif:条件求和函数用途：根据指定条件对若干单元格、区域或引用求和。语法：SUMIF（条件区域，条件，需求和的区域）参数：条件是由数字.逻辑表达式等组成的判定条件。Sumif案例第Q工班X资组李宇5000，送务组F东,出开口12000忍西已生克W济目8200X资组号/晶j晶j4000X资生弓长怡宁3000，送务组石智勇5000评壮组7200请统计人资组同仁的工资总额:Sumif（A:Af 人资组”，C:C）Countif:条件计数函数。用途：计算区域中满足给定条件的单元格的个数。语法：COUNTIF（统计区域，条件）参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。条件”为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。Countif 案例ABCDEFGHI到职编号单位代码师员单位名称职务代码职务名称到职日期性别工作年限061964B316师服务保证课108课长1996-6-3男12.2104489B322员零件开发课231零件供应仓管员2000-9-20男7.9130230d222师开发四课H7资深工程师2003-4-15男5.3150192D32C员乘用车初检线212车检技术员2005-4-8男3.4160350C131师涂装课121助理工程师2006-7-3男2.1170183C432员熔接二课226焊接技术员2008-7-1男0.1061971B322师零件开发课H5高级工程师1996-6-3女12.2以上述样表为据，完成以下案例：例1：依“师员”字段，统计师员级的人数countif(C:Cf 师)；countif(C:Cf 员)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(1:1,*=3);countif(1:1,=3)-countif(I：L =5)例3:检测到职编号是否有重复值countif(A:A,A2)Counta用途：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。语法：COUNTA（单元格区域1,单元格区域2）说明：参数的个数为130个。Counta的思考案例例1：如果Al=6.28、A2=3.74,其余单元格为空，则公式=COUNTA(A1:A7)的计算结果等于?。例2:统计C列的非空白单元格的表示方法为：？；若整张 EXCEL表的每个单元格都有数据，前述函数公式的结果为？Offset 用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格 或单元格区域（并不返回值）。语法：OFFSET（作为偏移量参照系的即用位置，上下偏移的行数，左右偏移的列数,height,widthD。参数：作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用；上（下）偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边；。代表没有上（下）偏移。左（右）偏移的列数:正数代表在起始引用右边;负数代表在起始引用的左边；。代表没有左（右）偏移。Height:是要返回的期用区域的总行数（必须为正数）Width:是要返回的期用区域的总列数（必须为正数）。Offset的思考题ABC16820312763599385124903452656680787103377815018215问题一：OFFSET(B12141)定位到哪几个单元格？问题二：以A1单元格为参照，要定位到红线区域如何写函数：=OFFSET(Alf6,l,2f2)Coiinta.off set的综合应用案例要求：当记录增加时，公式自动统计总销量(假设销量存放600数值的单元格为C3):3900车型月份钠量IDE1600ZD巨2300IDe n6200PS21500MG3200LS4700LS5400=SUM(OFFSET(C3,0,0,COUNTA(C:C)-L1)或二 SUM(OFFSET(C2,LO,COUNTA(C:C)-L1)注：以上公式只能正确计算不间断的连续数据，如果表格中销量的数据有空白单元格，那么动态名称的引用位置 将发生错误COLUMN 用途：返回给定引用的列标。语法：COLUMN（单元格引用）。参数：”单元格引用“为需要得到其列标的单元格，如果省略，则假定函数 COLUMN是对所在单元格的引用。实例:公式“=COLUMN(A3)”返回 1,COLUMN(D5)返回?。Row用途：返回给定引用的行号。语法：ROW（单元格引用）。“单元格引用为需要得到其行号的单元格或单元格区域。实例:利用row建立序号：61964_B3 1 6pm月艮生-2104489E322口3130230d222pm4150192D32Cm5160350Cl 3 1i/m6二 70 ZL 83_C432g706197 1自322pm8061988D 1 72pm心9062006D 1 6Nvm1 O62020_E2 1 5pm1 1夕62037