EXCEL实战技巧(公式与函数)课件.ppt

1、Excel实战技巧精粹公式与函数申 明l本教程主要针对Excel中、高级用户，内容主要涉及EXCEL公式与函数的基本使用方法及技巧。l本教程对应使用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公式的组成要素序 号1234公 式=15*3+20*2=A1*3+A2*2=SUM（A1*3，A2*2）=单价*数量说 明包含常量运算的公式包含单元格引用的公式包

2、含函数的公式包含名称的公式表1-1 公式的组成要素1.1认识公式1.1.3公式的输入、编辑与删除3种方法进入单元格编辑状态a)选中公式所在单元格，并按下键。b)双击公式所在单元格。c)选中公式所在单元格，单击列标上方的编辑栏。使用键与键可清除单元格中的内容。1.1认识公式1.1.4公式的复制与填充5种方法实现公式的复制与填充方法一：拖拽填充。方法二：双击填充柄。方法三：快捷键填充。选中需要填充的区域，按。方法四：选择性粘贴。方法五：多单元格同时输入。示例示例1.2公式中的运算符1.2.1认识运算符符 号说 明实 例-算术运算符：负号=8*-5 结果为-40%算术运算符：百分号=60*5%结果为

3、3算术运算符：乘幂=103 结果为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：B5 A4：D9）引用交叉区域，联合运算符：逗号=if（3+2=5，true，false）分隔参数表1-2 公式中的运算符

4、1.2公式中的运算符1.2.2数据比较的原则数据类型示 例文本姓名、性别、住址、职务、职称数值0、1、2、3、4、5、6逻辑值TRUE、FALSE错误值#VALUE！、#DIV/0!、#NAME！、#N/A、#REF！、#NULL！注意：数字与数值是两个不同的概念！示例示例表1-3 EXCEL中的数据类型1.2公式中的运算符1.2.3运算符的优先顺序序 号符 号说 明1：_（空格）,引用运算符：冒号、单个空格和逗号2-算术运算符：负号3%算术运算符：百分号4算术运算符：乘幂5*和/算术运算符：乘和除6+和-算术运算符：加和减7&文本运算符：连接文本8=,=,比较运算符：比较两个值表1-4 运算

5、符的优先级优先级次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.1 A1引用样式和R1C1引用样式a)A1引用样式引用样式 在默认情况下，Excel使用A1引用样式。即使用字母AXFD表示列标，使用数字11048576表示行

6、号。a)R1C1引用样式引用样式 单击【Office】按钮，【Excel选项】，【公式】选项卡，勾选“R1C1引 用样式”，如下图所示：1.4单元格引用1.4.2 相对引用、绝对引用和混合引用相对引用相对引用 从属单元格与引用单元格的相对位置不变。=A1绝对引用绝对引用 公式所引用的单元格绝对位置不变。=A1混合引用混合引用 所引用单元格的行或列方向之一的绝对位置不变，而另一方向位置发生变化。示例示例1.5函数1.5.1函数的概念1.5.2函数的结构=IF（A10,”正数正数”,IF（A10,”负数负数”,”零零”）函数名参数1参数2参数3注意：当使用函数作为另一个函数的参数时，成为函数的嵌套

7、！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】默

8、认为替换所有符合第二个参数的字符表1-5 常用函数省略可选参数情况1.5函数1.5.4常用函数的分类文本函数信息函数逻辑函数查找和引用函数日期和时间函数统计函数数学和三角函数数据库函数财务函数工程函数多维数据集函数根据函数的功能和应用领域，内置函数可分为根据函数的功能和应用领域，内置函数可分为11个类别个类别根据来源的不同，根据来源的不同，Excel函数可分为内函数可分为内4类：类：p内置函数内置函数p扩展函数扩展函数p自定义函数自定义函数p宏表函数宏表函数1.5函数1.5.5函数的输入和编辑1.5函数1.5.6使用公式的常见问题错误值类型含 义#当列宽不够显示数字，或者使用了负的日期、时间时

9、，出现错误#VALUE当使用的参数或操作数类型错误时，出现错误#DIV/0！当数字被零（0）除时，出现错误#NAME？当未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误表1-6 常见错误值及其含义1.6名称1.6.1名称的概念1.6.2为什么要使用名称a)增强公式的可读性。b)方便公式的统一修改。c)代替需要重复使用的公式，以简化公式。d)代替单元格区域存储常量数据

10、。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.

11、1区分文本型数字与数值文本值默认左对齐文本值默认左对齐数值默认右对齐数值默认右对齐逻辑值与错误值默认居中逻辑值与错误值默认居中日期与时间默认右对齐日期与时间默认右对齐数据默认对齐方式文本形式数字与数值导致查询出错示例示例.1合并与比较文本2.1.2合并文本a)使用文本合并运算符“&”b)使用CONCATENATE函数c)使用PHONETIC函数示例示例.1合并与比较文本2.1.3文本值的大小比较示例示例a)使用比较运算符“=”b)使用EXACT函数注意：用比较运算符对比文本时具有以下特性I.区分全角与半角字符。II.区分文本型数字与数值，文本始终大于数值。III.不区分字母大小写。.2大小写、

12、半全角转换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 A（6565）、）、a a（9797

13、）=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(ol

14、d_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数值取舍函数常用取舍函数汇总常用取舍函数汇总函数名称INTTRUNCROUNDROUNDUPROUNDDOWNCELLINGFLOOREVENODD功能描述取整函数，将数字向下舍入为最接近的整数将数字直接截尾取整，与数值符号

15、无关将数字四舍五入到指定位数将数朝远离0的方向舍入，即向上舍入将数朝0的方向舍入，即向下舍去将数字向上舍入为最接近的整数，或最接近的指定基数的整数倍将数字向下舍入为最接近的整数，或最接近的指定基数的整数倍将数字向上舍入为最接近的偶型整数将数字向上舍入为最接近的奇型整数3.3四舍五入示例取舍函数应用示例取舍函数应用示例函数名称INTTRUNCROUNDROUNDUPROUNDDOWNCELLINGFLOOREVENODD示 例=INT（-28.2）=-29=TRUNC（-28.2）=-28=ROUND（1256.78，-2）=1300=ROUNDUP（23.416,1）=23.5=ROUNDDO

16、WN（23.416,1）=23.4=CELLING（123.456，0.25）=123.50=FLOOR（123.456，0.25）=123.25=EVEN（123.456）=124=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。日期是一个整数值，时间时间则是小

17、数部分，数据0.5对应时间序数12:00:00。常用日期函数如下常用日期函数如下函数名称TODAY、NOWDATEYEAR、MONTH、DAYYEARFRACEDATEEOMONTHWORKDAYNETWORKDAYS功能描述用于生产当前日期和时间根据指定的年份、月份和日期数返回具体的日期值用于从日期数据中提取年份、月份和日期数用于计算两个日期序列相差的年数，结果以实数显示根据指定月份数返回指定日期之前或之后的日期值从任意一个日期返回指定月份数之前或之后的月末日期根据源日期，按指定工作日天数返回之前或之后的日期计算两个日期之间的工作日天数4.1年月日函数 4.1.1使用TODAY和NOW快速生

18、成当前日期和时间使用和组合键快速输入当前日期和时间，与使用函数生成的日期与时间有何异同？4.1.2使用DATE函数生成指定日期=DATE（year,month,day）示例示例4.1.3使用YEAR、MONTH和DAY提取年份、月份和日期4.2认识DATEDIF函数功能描述：计算两个日期之间的天数、月数和年数语法：DATEDIF（start_date,end_date,unit）Unit参数含义如下：Unit代码“y”“m”“d”“md”“ym”“yd”函数返回值时间段中的年数时间段中的月数时间段中的天数时间段中天数的差。忽略日期中的年和月时间段中月数的差。忽略日期中的年和日时间段中天数的差。

19、忽略日期中的年示例示例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常用

20、的定位函数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）示例示例注意：I.rang_lookup参数为0或FALSE代表精确查找。II.rang_lookup参数1或TRUE代表模糊匹配查找。III.查找值与查找

21、范围的数据类型相匹配。5.2常用的定位函数MATCH函数：确定查找值在查找范围中的位置序号MATCH（lookup_value,lookup_array,match_type）示例示例判断数据是否被重复录入5.3行号和列号函数ROW函数：返回单元格引用的行号COLUMN函数：返回单元格引用的列号示例示例1.如何隔行设置背景颜色？2.与VLOOKUP函数共同设置数据查询。5.4特殊的查找函数LOOKUP函数：在查找范围中查找用户指定的查找值LOOKUP（lookup_value,lookup_vector,result_vector）示例示例5.5 认识OFFSET函数OFFSET函数：以指定的

22、引用为参照，最终通过给定的偏移量得到一个新的单元格引用或单元格区域引用OFFSET（reference,rows,cols,height,width）如何设置动态下拉菜单？示例示例6.统计与求和 6.1基本的计数函数6.2条件统计类函数6.3条件求和类函数6.4极值应用函数6.5排名应用6.1基本的计数函数COUNT：对数值进行统计，忽略错误值、逻辑值及文本值COUNTA：统计非空单元格的个数COUNTBLANK：统计数据表中的空单元格个数示例示例6.2条件统计类函数COUNTIF：按指定条件（一个）对区域内数值个数进行统计Countif（range,criteria）COUNTIFS：按指定

23、条件（多个）对区域内数值个数进行统计Countifs(criteria_range1,criteria1)示例示例6.3条件求和类函数SUMIF：按指定条件（一个）对区域内数值进行求和sumif（range,criteria,sum_range）SUMIFS：按指定条件（一个）对区域内数值进行求和sumifs（Countifs(criteria_range1,criteria1）示例示例6.4极值应用函数MAX：取得数据最大值MIN：取得数据最小值LARGE：返回指定的第N大的值（LARGE（array_k）SMALL：返回指定的第N小的值（LARGE（array_k）MEDIAN：针对给定的3个值取中间值示例示例6.5排名应用示例示例RANK：返回给定值在数值区域中的排位序号RANK(number,ref)Thank you 祝愿大家今后处理数据能游刃有余Tel:68157856QQ:68842855E-mail: