Excel 函数

河南理工大学土木工程学院孙现军Excel函数与VBA初步0.概述时间学时内容第4周20概述（教学安排、要求）1.EXCEL函数1.1EXCEl函数基础知识1.2数学函数、统计函数第4周21.3逻辑函数1.4查找与引用函数第5周21.5财务函数1.6数据库函数第5周21.7工程函数1.8数组函数第6周22.EXCELVBA初步2.1EXCELVBA概述2.2宏第6周22.3VBE和VBA程序简介2.4控制工作薄、工作表、控制单元格第7周22.5EXCEL对话框操作2.6窗体与控件第7周22.7窗口、工具栏、自定义菜单操作2.8系统演示与介绍教学内容与安排0.概述教学参考书赵志东,人民邮电出版社,2010年11月(日)日花弘子.恒盛杰资讯译.中国青年出版社2007年02月.0.概述参考网站://://概述教学目标1、认知目标认知Excel水平提高，学习兴趣提高2、操作目标函数操作水平达到中高级水平；ExcelVBA入门；带动Excel基本操作提高。向Excel高手迈进0.概述EXCEL的作用“一招鲜，吃遍天”，只要熟练掌握EXCEL，无论在那里工作，都能助您职场成功；打破软件行业的“二八定律”，EXCEL的95%的功能我们还不曾见到，也不曾用过，这其中只需要再开发20%，就让我们受益终生。0.概述学习注意事项（1）循序渐进，EXCEL学习一般经历新手、初级用户、中级用户、高级用户和专家五个层次。新手要扫盲，借一本书加上机，熟悉EXCEL界面和基本操作；初级用户应掌握基本的图表操作，掌握简单的公式和函数；中级用户掌握各种菜单功能、熟练使用数据透视表、掌握20多个常用函数和函数嵌套使用；高级用户熟练掌握各种常用公式，能够自定义公式，掌握VBA运用；EXCEL专家具备高超的EXCEL操作能力，并且某一行业的知识和经验非常丰富。（2）掌握方法，解决应用问题最重要EXCEL的344个函数不可能全部记住，掌握常用的即可，其他的掌握运用方法，并可以像查字典一样去随时查询和运用；EXCELVBA编程的基本语句、命令也不需要都记住，能看懂别人的语句，会修改、移植就可以了；学会拿来主义，学以致用，解决问题最重要。（３）永远不要停息你探索的脚步很多深入的应用是挖掘和探索出来的，所以请明锐的去发现工作中的需求，并尝试探索用EXCEL去解决把；（４）多阅读，多运用市面上有很多EXCEL的书，找到一本适合自己的，并在实际运用中勤加练习。公共邮箱[e-mail]密码：hpu1234561.EXCEL公式和函数（1）Excel基础知识（2）绝对引用和相对引用（3）基本运算符（4）公式的输入方法（5）Excel函数分类1.1Excel函数基础知识Excel操作对象工作薄工作表单元格操作单元格小技巧技巧1：如何在多个单元格输入相同内容方法（1）选中你要输入的文字的区域（2）输入文字（3）Ctrl+Enter选中区域输入文字Ctrl+Enter操作技巧-学会运用强大的F4键F4键的强大功能之一——重复上一步操作建立工作表删除新建单元格重复的格式设置操作小技巧-快速输入平方、立方ALT+178快速输入平方ALT+179快速输入立方alt+41420快速输入“√”号alt+41409快速输入“×”号1.1Excel函数基础知识（2）绝对引用和相对引用B2——列和行相对引用$B2——列绝对引用，行相对引用B$2——列相对引用，行绝对引用$B$2——列和行绝对引用Excel的查找与引用的灵活应用对于减少重复数据的录入是大有裨益的。1、在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。引用的作用关于引用需要了解如下几种情况的含义：外部引用--不同工作簿中的单元格的引用称为外部引用。三维地址的引用：从另外一个工作簿文件的不同工作表中引用单元格。表示方式为：[工作簿.xls]工作表！单元格或区域远程引用--引用其它程序中的数据称为远程引用。引用的含义相对地址引用：公式中的相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格B2中的相对引用复制到单元格B3，将自动从=A1调整到=A2。当把一个含有单元格地址的公式复制到一个新的位置或用该公式填充一个范围时，公式中单元地址随着改变。行复制、列复制绝对引用和相对引用绝对引用和相对引用绝对地址引用：当把一个含有单元格地址的公式复制到一个新的位置或用该公式填充一个范围时，公式中单元地址保持不变。如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。绝对引用和相对引用混合地址引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式。绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从A2复制到B3，它将从=A$1调整到=B$1。1.1Excel函数基础知识（3）基本运算符算术运算符+——加法-——减法*——乘法/——除法^——乘方文本运算符&——文本（结合）逻辑运算符=——等号——大于号——小于号=——大于等于号=——小于等于号引用运算符，——引用不相邻单元格：——引用相邻单元格空格——引用交叉区域1.1Excel函数基础知识（3）基本运算符优先级运算种类1%（百分号）2^3*或/4+或-5&6比较运算符=、、、=、=、≠1.1Excel函数基础知识（4）公式的输入方法①选中需显示计算结果的单元格；②在单元格中输入“=”；③输入公式④按Enter键公式输入示例输入内容在编辑栏也可以显示，如是长串公式，在此一次输入选中E3单元格，先输入=再输入2+3公式输入示例选中E3单元格，先输入=，选中B3单元格，再输入+，再选中C3单元格，按Enter修改公式选中单元格，在编辑栏修改，改后按Enter双击单元格，在单元格修改，改完后按Enter1.1Excel函数基础知识（5）Excel函数分类类型涉及内容函数符号数学与三角包含求和函数和数学计算函数。求和、乘方等的四则运算，以及四舍五入、舍去数字等的零数处理及符号的变化等。SUM、ROUND、ROUNDUP、ROUNDDOWN、PRODUCT、INT、SIGN、ABS等统计求数学统计的函数，平均值、中值、众数、方差、标准差。AVERAGE、RANK、MEDIAN、MODE、VAR、STDEV等日期与时间计算日期和时间的函数。年月日的显示格式和日期数据序列之间的相互转换，也可求当前日期或时间的函数。DATE、TIME、TODAY、NOW、EOMONTH、EDATE等逻辑根据是否满足条件，进行不同处理的if函数，及在逻辑表述中被利用的函数。IF、AND、OR、NOT、TRUE、FALSE等查找与引用从表格或数组中提取制定行或列中的数值、推断出包含目标值的单元格的位置VLOOKUP、HLOOKUP、INDIRECT、ADDRESS、COLUMN、ROW等文本…………………………………………财务…………………………………………信息…………………………………………数据库…………………………………………工程…………………………………………外部…………………………………………Sum()和average()Sum()——求和语法：SUM(number1,number2,...)SUM(选定区域)average()——求平均值Count和CountaCount()：在计数时将把数字、空值、逻辑值、日期或文字代表的数计算进去，但错误值或其他无法转化成数字的文字则被忽略。但是，如果参数是一个数组或引用，那么只统计其中的数字，数组或引用的空单元格、逻辑值、文字或错误值都将被忽略。Counta():返回参数列表中非空值的单元格个数。利用它可以计算单元格区域或数组中包含数据的单元格个数。如果不需要统计逻辑值、文字或错误值，则使用COUNT（）1.2逻辑函数主要有if（）or（）and（）not（）等格式：if(逻辑表达式，value-if-true,value-if-false)功能：执行真假值判断，根据逻辑条件的真假值，返回不同的结果。最多可嵌套7层。IF()逻辑判断函数格式：SUMIF(range,criteria,Sum-range)作用：根据指定条件对若干单元格求和Range：用于条件判断的单元格区域Criteria：判断哪些单元格将被相加求和的条件，其形式可以为数字，表达式，或文本，如32，“32”，“32”,”apples”SUMIF（）Countif用途根据条件在另一个区域进行个数的统计。可以完成符合条件的统计计算，也可以进行重复值的查找。语法COUNTIF(range,criteria)所有参数的计算结果为TRUE时，返回TRUE；只要有一个参数的计算结果为FALSE，即返回FALSE。AND函数的一种常见用途就是扩大用于执行逻辑检验的其他函数的效用。例如，IF函数用于执行逻辑检验，它在检验的计算结果为TRUE时返回一个值，在检验的计算结果为FALSE时返回另一个值。通过将AND函数用作IF函数的logical_test参数，可以检验多个不同的条件，而不仅仅是一个条件。AND(logical1,[logical2],...)最多可包含255个条件。参数的计算结果必须是逻辑值（如TRUE或FALSE），而参数必须是包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域未包含逻辑值，则AND函数将返回错误值#VALUE!。AND()在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。语法OR(logical1,logical2,...)Logical1,logical2,...是1到255个需要进行测试的条件，测试结果可以为TRUE或FALSE。注解参数必须能计算为逻辑值，如TRUE或FALSE，或者为包含逻辑值的数组或引用。OR()对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT。语法NOT(logical)Logical可以计算出TRUE或FALSE的值或表达式。注解如果logical为FALSE，NOT返回Yes；如果logical为TRUE，NOT返回No。NOT()1.3查询函数CHOOSE函数用于从一系列元素中检索出一项，其格式为=CHOOSE（索引数，元素1，元素2，……，元素N）索引数是待查找项在列表中的位置，它必须是大于0且小于等于N的整数元素可以是数值、文本或单元格引用。若果index_num为1，函数choose返回value1；如果为2，函数choose返回value2，以此类推。如果index_num小于1或大于列表中最后一个值得序号，函数choose返回错误值#VALUE!.。如果index_num为小数，则在使用前将被截尾取整。CHOOSE()用于从一系列元素中检索出一项与查找值最相近的元素的序号。格式：MATCH（查找