EXCEL2007实战技巧(公式与函数)

申明本教程主要针对Excel中、高级用户，内容主要涉及EXCEL公式与函数的基本使用方法及技巧。本教程对应使用office2007版本李宗尧2013年6月Content目录1公式和函数基础2文本处理3数学计算4日期和时间计算5查找与引用6统计与求和1.公式和函数基础1.1认识公式1.2公式中的运算符1.3公式中的常量1.4单元格引用1.5函数1.6名称1.1认识公式1.1.1公式的概念1.1.2公式的组成要素序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=SUM（A1*3，A2*2）包含函数的公式4=单价*数量包含名称的公式表1-1公式的组成要素1.1认识公式1.1.3公式的输入、编辑与删除3种方法进入单元格编辑状态a)选中公式所在单元格，并按下F2键。b)双击公式所在单元格。c)选中公式所在单元格，单击列标上方的编辑栏。使用Del键与Backspace键可清除单元格中的内容。1.1认识公式1.1.4公式的复制与填充5种方法实现公式的复制与填充方法一：拖拽填充。方法二：双击填充柄。方法三：快捷键填充。选中需要填充的区域，按Ctrl+D。方法四：选择性粘贴。方法五：多单元格同时输入。示例1.2公式中的运算符1.2.1认识运算符符号说明实例-算术运算符：负号=8*-5结果为-40%算术运算符：百分号=60*5%结果为3^算术运算符：乘幂=10^3结果为1000*和/算术运算符：乘和除=3*2/4结果为1.5+和-算术运算符：加和减=3+2结果为5=，,=,=比较运算符：等于，不等于，大于，小于，大于等于和小于等于，=（A1=A2）判断A1与A2相等=（B1“ABC”）判断B1不等于ABC=（C1=5）判断C1大于等于5&文本运算符：连接文本=“CP”&“IC”返回“CPIC”:区域运算符：冒号=SUM（A1：A10）引用A1至A10区域_(空格)交叉运算符：单个空格=SUM（A1：B5A4：D9）引用交叉区域，联合运算符：逗号=if（3+2=5，true，false）分隔参数表1-2公式中的运算符1.2公式中的运算符1.2.2数据比较的原则数据类型示例文本姓名、性别、住址、职务、职称……数值0、1、2、3、4、5、6……逻辑值TRUE、FALSE错误值#VALUE！、#DIV/0!、#NAME！、#N/A、#REF！、#NULL！注意：数字与数值是两个不同的概念！示例表1-3EXCEL中的数据类型1.2公式中的运算符1.2.3运算符的优先顺序序号符号说明1：_（空格）,引用运算符：冒号、单个空格和逗号2-算术运算符：负号3%算术运算符：百分号4^算术运算符：乘幂5*和/算术运算符：乘和除6+和-算术运算符：加和减7&文本运算符：连接文本8=,,,=,=,比较运算符：比较两个值表1-4运算符的优先级优先级次1.3公式中的常量1.3.1常量参数公式中的5种常量a)数值常量，如：=（5+8）*3。b)日期常量，如：=DATEDIF（“2012-1-1”,NOW（），“m”）。c)文本常量，如：=“国际”&“复合”。d)逻辑值常量，如：=VLOOKUP（“张三丰”，A:B，2，FALSE）。e)错误值常量，如：=COUNTIF（A:A，#DIV/0!）注意：逻辑值与文本型数字在运算中会被自动转换为数值！1.4单元格引用1.4.1A1引用样式和R1C1引用样式a)A1引用样式在默认情况下，Excel使用A1引用样式。即使用字母A~XFD表示列标，使用数字1~1048576表示行号。a)R1C1引用样式单击【Office】按钮，【Excel选项】，【公式】选项卡，勾选“R1C1引用样式”，如下图所示：1.4单元格引用1.4.2相对引用、绝对引用和混合引用①相对引用从属单元格与引用单元格的相对位置不变。=A1②绝对引用公式所引用的单元格绝对位置不变。=＄A＄1③混合引用所引用单元格的行或列方向之一的绝对位置不变，而另一方向位置发生变化。示例1.5函数1.5.1函数的概念1.5.2函数的结构=IF（A10,”正数”,IF（A10,”负数”,”零”））函数名参数1参数2参数3注意：当使用函数作为另一个函数的参数时，成为函数的嵌套！1.5函数1.5.3可选参数与必需参数函数名称参数位置及名称省略参数后的默认情况IF第3个参数【value_if_false】默认为FALSELOOKUP第3个参数【result_vector】默认为数组语法MATCH第3个参数【match_type】默认为1VLOOKUP第4个参数【range_lookup】默认为TRUEHLOOKUP第4个参数【range_lookup】默认为TRUEFIND第3个参数【start_num】默认为1LEFT第2个参数【num_chars】默认为1RIGHT第2个参数【num_chars】默认为1SUBSTITUTE第4个参数【instance_num】默认为替换所有符合第二个参数的字符SUNIF第3个参数【sum_range】默认对第一个参数range进行求和表1-5常用函数省略可选参数情况1.5函数1.5.4常用函数的分类文本函数信息函数逻辑函数查找和引用函数日期和时间函数统计函数数学和三角函数数据库函数财务函数工程函数多维数据集函数根据函数的功能和应用领域，内置函数可分为11个类别根据来源的不同，Excel函数可分为内4类：内置函数扩展函数自定义函数宏表函数1.5函数1.5.5函数的输入和编辑1.5函数1.5.6使用公式的常见问题错误值类型含义######当列宽不够显示数字，或者使用了负的日期、时间时，出现错误#VALUE当使用的参数或操作数类型错误时，出现错误#DIV/0！当数字被零（0）除时，出现错误#NAME？当未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误表1-6常见错误值及其含义1.6名称1.6.1名称的概念1.6.2为什么要使用名称a)增强公式的可读性。b)方便公式的统一修改。c)代替需要重复使用的公式，以简化公式。d)代替单元格区域存储常量数据。e)解决数据有效性和条件格式中无法使用常量数组、交叉引用、跨表引用等问题。1.6名称1.6.3名称命名的限制a)不能以数字开头。b)不能以R、C、r、c作为名称名。c)不能使用除下划线、点号、和反斜线以外的其它符号。d)字符不能超过255个。e)字母不区分大小写。1.6名称1.6.4定义名称的方法方法一：使用名称管理器定义名称方法一：使用名称框快速创建名称1.6名称1.6.5定义名称的对象1使用合并区域引用和交叉引用2使用常量3使用常量数组4使用函数与公式示例２.文本处理２.1合并与比较文本２.2大小写、全半角转换２.3字符与编码转换２.4提取与查找字符２.5替换与清理字符２.1合并与比较文本2.1.1区分文本型数字与数值文本值默认左对齐数值默认右对齐逻辑值与错误值默认居中日期与时间默认右对齐数据默认对齐方式文本形式数字与数值导致查询出错示例２.1合并与比较文本2.1.2合并文本a)使用文本合并运算符“&”b)使用CONCATENATE函数c)使用PHONETIC函数示例２.1合并与比较文本2.1.3文本值的大小比较示例a)使用比较运算符“=”b)使用EXACT函数注意：用比较运算符对比文本时具有以下特性I.区分全角与半角字符。II.区分文本型数字与数值，文本始终大于数值。III.不区分字母大小写。２.2大小写、半全角转换2.2.1大小字母转换a)LOWER函数：将所有字母转换为小写字母。b)UPPER函数：将所有字母转换为大写字母。c)PROPER函数：将所有单词转换为首字母大写的格式。示例２.2大小写、半全角转换2.2.2全角半角字符转换（ASC、WIDECHAR）a)全角字符：又称双音节字符，占用2个标准字符位置。b)半角字符：又称单音节字符，占用1个标准字符位置。c)所有的汉字都是全角字符示例２.3字符与编码转换2.3.1字符与编码转换函数（CHAR、CODE）ASCⅡ字符集B2312字符集BIG5字符集GB18030字符集Unicode字符集A（65）、a（97）=CHAR（64+COLUMN（A1））=CHAR（96+COLUMN（A1））２.4提取与查找字符2.4.1常用提取字符函数a)LEFT函数：从左端提取制定数量字符。b)RIGHT函数：从右端提取制定数量字符。c)MID函数：从中间提取制定数量字符。示例I.提取身份证信息。II.分离中英文对照。２.5替换与清理字符2.5.1常用替换字符函数a)SUBSTITUTE函数：将目标文本中的制定字符串替换为新的字符串。SUBSTITUDE（text,old_text,new_text,instance_num）a)REPLACE函数：根据起始位置和文本字符数来替换为指定的新字符串。REPLACE(old_text,start_num,num_chars,new_text)示例1.如何将英文句子首字母转换为大写？2.如何清除字符串中间的空格？３.数学计算3.1取余函数3.2数值取舍函数3.3四舍五入函数3.4随机函数3.1取余函数使用MOD函数判断奇偶性语法：MOD（number，divisor），返回两数相除的余数。例：MOD（69,7），返回结果为7。示例3.2数值取舍函数常用取舍函数汇总函数名称功能描述INT取整函数，将数字向下舍入为最接近的整数TRUNC将数字直接截尾取整，与数值符号无关ROUND将数字四舍五入到指定位数ROUNDUP将数朝远离0的方向舍入，即向上舍入ROUNDDOWN将数朝0的方向舍入，即向下舍去CELLING将数字向上舍入为最接近的整数，或最接近的指定基数的整数倍FLOOR将数字向下舍入为最接近的整数，或最接近的指定基数的整数倍EVEN将数字向上舍入为最接近的偶型整数ODD将数字向上舍入为最接近的奇型整数3.3四舍五入示例取舍函数应用示例函数名称示例INT=INT（-28.2）=-29TRUNC=TRUNC（-28.2）=-28ROUND=ROUND（1256.78，-2）=1300ROUNDUP=ROUNDUP（23.416,1）=23.5ROUNDDOWN=ROUNDDOWN（23.416,1）=23.4CELLING=CELLING（123.456，0.25）=123.50FLOOR=FLOOR（123.456，0.25）=123.25EVEN=EVEN（123.456）=124ODD=ODD（123.456）=1253.4随机函数RAND函数与RANDBETWEEN函数a)RAND函数：产生0到1之间的随机数；b)RANDBETWEEN函数：依据给定上限和下限产生随机整数。抽取试题安排座位生成密码生成模拟测试数据随机函数用途4.日期和时间计算4.1年月日函数4.2认识DATEDIF函数4.3星期相关函数4.4时间的计算4.1年月日函数日期数据是一种数值的特殊表现形式，数值1代表1900-1-1。日期是一个整数值，时间则是小数部分，数据0.5对应时间序数12:00:00。常用日期函数如下函数名称功能描述TODAY、NOW用于生产当前日期和时间DATE根据指定的年份、月份和日期数返回具体的日期值YEAR、MONTH、DAY用于从日期数据中提取年份、月份和日期数YEARFRAC用于计算两个日期序列相差的年数，结果以实数显示EDATE根据指定月份数返回指定日期之前或之后的日期值EOMONTH从任意一个日期返回指定月份数之前或之后的月末日期WORKDAY根据源日期，按指定工作日天数返回之前或之后的日期NETWORKDAYS计算两个日期之间的工作日天数4.1年月日函数4.1.1使用TO