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

Excel实战技巧精粹公式与函数申明本教程主要针对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种方法进入单元格编辑状态选中公式所在单元格，并按下<F2>键。双击公式所在单元格。选中公式所在单元格，单击列标上方的编辑栏。使用<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种常量数值常量，如：=〔5+8〕*3。日期常量，如：=DATEDIF〔“2012-1-1”,NOW〔〕，“m”〕。文本常量，如：=“国际”&“复合”。逻辑值常量，如：=VLOOKUP〔“张三丰”，A:B，2，FALSE〕。错误值常量，如：=COUNTIF〔A:A，#DIV/0!〕注意：

逻辑值与文本型数字在运算中会被自动转换为数值！1.4单元格引用1.4.1A1引用样式和R1C1引用样式A1引用样式在默认情况下，Excel使用A1引用样式。即使用字母A~XFD表示列标，使用数字1~1048576表示行号。R1C1引用样式单击【Office】按钮，【Excel选项】，【公式】选项卡，勾选“R1C1引用样式”，如以下图所示：1.4单元格引用1.4.2相对引用、绝对引用和混合引用相对引用附属单元格与引用单元格的相对位置不变。=A1绝对引用公式所引用的单元格绝对位置不变。=＄A＄1混合引用所引用单元格的行或列方向之一的绝对位置不变，而另一方向位置发生变化。示例1.5函数1.5.1函数的概念函数的结构=IF（A1>0,”正数”,IF（A1<0,”负数”,”零”））函数名参数1参数2参数3注意：

当使用函数作为另一个函数的参数时，成为函数的嵌套！1.5函数可选参数与必需参数函数名称参数位置及名称省略参数后的默认情况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函数常用函数的分类文本函数信息函数逻辑函数查找和引用函数日期和时间函数统计函数数学和三角函数数据库函数财务函数工程函数多维数据集函数根据函数的功能和应用领域，内置函数可分为11个类别根据来源的不同，Excel函数可分为内4类：内置函数扩展函数自定义函数宏表函数1.5函数函数的输入和编辑1.5函数使用公式的常见问题错误值类型含义######当列宽不够显示数字，或者使用了负的日期、时间时，出现错误#VALUE当使用的参数或操作数类型错误时，出现错误#DIV/0！当数字被零（0）除时，出现错误#NAME？当未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误表1-6常见错误值及其含义1.6名称1.6.1名称的概念为什么要使用名称增强公式的可读性。方便公式的统一修改。代替需要重复使用的公式，以简化公式。代替单元格区域存储常量数据。解决数据有效性和条件格式中无法使用常量数组、交叉引用、跨表引用等问题。1.6名称名称命名的限制不能以数字开头。不能以R、C、r、c作为名称名。不能使用除下划线、点号、和反斜线以外的其它符号。字符不能超过255个。字母不区分大小写。1.6名称定义名称的方法方法一：使用名称管理器定义名称方法一：使用名称框快速创立名称1.6名称定义名称的对象1使用合并区域引用和交叉引用2使用常量3使用常量数组4使用函数与公式示例２.文本处理２.1合并与比较文本２.2大小写、全半角转换２.3字符与编码转换２.4提取与查找字符２.5替换与清理字符２.1合并与比较文本区分文本型数字与数值文本值默认左对齐数值默认右对齐逻辑值与错误值默认居中日期与时间默认右对齐数据默认对齐方式文本形式数字与数值导致查询出错示例２.1合并与比较文本合并文本使用文本合并运算符“&”使用CONCATENATE函数使用PHONETIC函数示例２.1合并与比较文本文本值的大小比较示例使用比较运算符“=”使用EXACT函数注意：用比较运算符比照文本时具有以下特性区分全角与半角字符。区分文本型数字与数值，文本始终大于数值。不区分字母大小写。２.2大小写、半全角转换大小字母转换LOWER函数：将所有字母转换为小写字母。UPPER函数：将所有字母转换为大写字母。PROPER函数：将所有单词转换为首字母大写的格式。示例２.2大小写、半全角转换全角半角字符转换〔ASC、WIDECHAR〕全角字符：又称双音节字符，占用2个标准字符位置。半角字符：又称单音节字符，占用1个标准字符位置。所有的汉字都是全角字符示例２.3字符与编码转换字符与编码转换函数〔CHAR、CODE〕ASCⅡ字符集B2312字符集BIG5字符集GB18030字符集Unicode字符集A〔65〕、a〔97〕=CHAR〔64+COLUMN〔A1〕〕=CHAR〔96+COLUMN〔A1〕〕２.4提取与查找字符常用提取字符函数LEFT函数：从左端提取制定数量字符。RIGHT函数：从右端提取制定数量字符。MID函数：从中间提取制定数量字符。示例提取身份证信息。分离中英文对照。２.5替换与清理字符常用替换字符函数SUBSTITUTE函数：将目标文本中的制定字符串替换为新的字符串。SUBSTITUDE〔text,old_text,new_text,instance_num〕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函数RAND函数：产生0到1之间的随机数；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年月日函数

使用TODAY和NOW快速生成当前日期和时间使用<Ctrl+；>和<Ctrl+Shift+；>组合键快速输入当前日期和时间，与使用函数生成的日期与时间有何异同？使用DATE函数生成指定日期=DATE〔year,month,day〕示例使用YEAR、MONTH和DAY提取年份、月份和日期4.2认识DATEDIF函数功能描述：计算两个日期之间的天数、月数和年数语法：DATEDIF〔start_date,end_date,unit〕Unit参数含义如下：Unit代码函数返回值“y”时间段中的年数“m”时间段中的月数“d”时间段中的天数“md”时间段中天数的差。忽略日期中的年和月“ym”时间段中月数的差。忽略日期中的年和日“yd”时间段中天数的差。忽略日期中的年示例4.3星期相关函数WEEKDAY函数：返回指定日期的星期值WEEKNUM函数：返回指定日期属于全年的第几周语法：WEEKDAY〔serial_number,[return_type]〕return_type说明：数字1：1至7对应星期天到数星期六〔1可省略〕数字2：1至7代表星期一到星期天〔中国人的习惯〕数字3：0至6代表星期一到星期天示例4.4时间的计算TIME函数：用于按指定数字生成具体时间HOUR函数：用于提取时间系列值中的小时数MINUTE函数：用于提取时间系列值中的分钟数SECOND函数：用于提取时间系列值中的秒数5.查找与引用5.1根本的查找函数5.2常用的定位函数5.3行号和列号函数5.4特殊的查找函数5.5认识OFFSET函数5.1根本的查找函数VLOOKUP函数：搜索查找范围中的首列中满足条件的数据VLOOKUP〔lookup_value,table_array,col_index_num,[rang_lookup]〕HLOOKUP函数：搜索查找范围中的首行中满足条件的数据HLOOKUP〔lookup_value,table_array,row_index_num,[rang_lookup]〕示例注意：rang_lookup参数为0或FALSE代表精确查找。rang_lookup参数1或TRUE代表模糊匹配查找。查找值与查找范围的数据类型相匹配。5.2常用的定位函数MATCH函数：确定查找值在查找范围中的位置序号MATCH〔lookup_value,lookup_array,[match_type]〕示例判断数据是否被重复录入5.3行号和列号函数ROW函数：返回单元格引用的行号COLUMN函数：返回单元格引用的列号示例1.如何隔行设置背景颜色？2.与VLOOKUP函数共同设置数据查询。5.4特殊的查找函数LOOKUP函数：在查找范围中查找用户指定的查找值LOOKUP〔lookup_value,lookup_v