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

Excel实战技巧精粹,公式与函数,申明,本教程主要针对Excel中、高级用户，内容主要涉及EXCEL公式与函数的基本使用方法及技巧。本教程对应使用office2007版本李宗尧2013年6月,Content,目录,1.公式和函数基础,1.1认识公式1.2公式中的运算符1.3公式中的常量1.4单元格引用1.5函数1.6名称,1.1认识公式,1.1.1公式的概念1.1.2公式的组成要素,表1-1公式的组成要素,1.1认识公式,1.1.3公式的输入、编辑与删除,3种方法进入单元格编辑状态选中公式所在单元格，并按下键。双击公式所在单元格。选中公式所在单元格，单击列标上方的编辑栏。使用键与键可清除单元格中的内容。,1.1认识公式,1.1.4公式的复制与填充,5种方法实现公式的复制与填充方法一：拖拽填充。方法二：双击填充柄。方法三：快捷键填充。选中需要填充的区域，按。方法四：选择性粘贴。方法五：多单元格同时输入。,示例,1.2公式中的运算符,1.2.1认识运算符,表1-2公式中的运算符,1.2公式中的运算符,1.2.2数据比较的原则,注意：数字与数值是两个不同的概念！,示例,表1-3EXCEL中的数据类型,1.2公式中的运算符,1.2.3运算符的优先顺序,表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引用样式。即使用字母AXFD表示列标，使用数字11048576表示行号。R1C1引用样式单击【Office】按钮，【Excel选项】，【公式】选项卡，勾选“R1C1引用样式”，如下图所示：,1.4单元格引用,1.4.2相对引用、绝对引用和混合引用,相对引用从属单元格与引用单元格的相对位置不变。=A1绝对引用公式所引用的单元格绝对位置不变。=A1混合引用所引用单元格的行或列方向之一的绝对位置不变，而另一方向位置发生变化。,示例,1.5函数,1.5.1函数的概念1.5.2函数的结构,注意：当使用函数作为另一个函数的参数时，成为函数的嵌套！,1.5函数,1.5.3可选参数与必需参数,表1-5常用函数省略可选参数情况,1.5函数,1.5.4常用函数的分类,根据函数的功能和应用领域，内置函数可分为11个类别,根据来源的不同，Excel函数可分为内4类：,内置函数,扩展函数,自定义函数,宏表函数,1.5函数,1.5.5函数的输入和编辑,1.5函数,1.5.6使用公式的常见问题,表1-6常见错误值及其含义,1.6名称,1.6.1名称的概念1.6.2为什么要使用名称,增强公式的可读性。方便公式的统一修改。代替需要重复使用的公式，以简化公式。代替单元格区域存储常量数据。解决数据有效性和条件格式中无法使用常量数组、交叉引用、跨表引用等问题。,1.6名称,1.6.3名称命名的限制,不能以数字开头。不能以R、C、r、c作为名称名。不能使用除下划线、点号、和反斜线以外的其它符号。字符不能超过255个。字母不区分大小写。,1.6名称,1.6.4定义名称的方法,方法一：使用名称管理器定义名称,方法一：使用名称框快速创建名称,1.6名称,1.6.5定义名称的对象,示例,.文本处理,.1合并与比较文本.2大小写、全半角转换.3字符与编码转换.4提取与查找字符.5替换与清理字符,.1合并与比较文本,2.1.1区分文本型数字与数值,文本值默认左对齐数值默认右对齐逻辑值与错误值默认居中日期与时间默认右对齐,数据默认对齐方式,文本形式数字与数值导致查询出错,示例,.1合并与比较文本,2.1.2合并文本,使用文本合并运算符“&”使用CONCATENATE函数使用PHONETIC函数,示例,.1合并与比较文本,2.1.3文本值的大小比较,示例,使用比较运算符“=”使用EXACT函数,注意：用比较运算符对比文本时具有以下特性区分全角与半角字符。区分文本型数字与数值，文本始终大于数值。不区分字母大小写。,.2大小写、半全角转换,2.2.1大小字母转换,LOWER函数：将所有字母转换为小写字母。UPPER函数：将所有字母转换为大写字母。PROPER函数：将所有单词转换为首字母大写的格式。,示例,.2大小写、半全角转换,2.2.2全角半角字符转换（ASC、WIDECHAR）,全角字符：又称双音节字符，占用2个标准字符位置。半角字符：又称单音节字符，占用1个标准字符位置。所有的汉字都是全角字符,示例,.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常用提取字符函数,LEFT函数：从左端提取制定数量字符。RIGHT函数：从右端提取制定数量字符。MID函数：从中间提取制定数量字符。,示例,提取身份证信息。分离中英文对照。,.5替换与清理字符,2.5.1常用替换字符函数,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数值取舍函数,常用取舍函数汇总,3.3四舍五入示例,取舍函数应用示例,3.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。,常用日期函数如下,4.1年月日函数,4.1.1使用TODAY和NOW快速生成当前日期和时间,使用和组合键快速输入当前日期和时间，与使用函数生成的日期与时间有何异同？,4.1.2使用DATE函数生成指定日期,=DATE（year,month,day）,示例,4.1.3使用YEAR、MONTH和DAY提取年份、月份和日期,4.2认识DATEDIF函数,功能描述：计算两个日期之间的天数、月数和年数语法：DATEDIF（start_date,end_date,unit）Unit参数含义如下：,示例,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_vector,result_vector）,示例,5.5认识OFFSET函数,OFFSET函数：以指定的引用为参照，最终通过给定的偏移量得到一个新的单元格引用或单元格区域引用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：按指定条件（多个）对区域内数值个数进行统计Countifs(criteria_range1,criteria1),示例,6.3条件求和类函数,SUMIF：按指定条件（一个）对区域内数值进行求和sumif（range,criteria,sum_range）SUMIFS：按指定条件（一个）对区域内数值进行求和sumifs（Countifs(criteria_range1,criteria1）,示例