Excel操作技巧及函数高效应用(学员版)

Excel操作技巧及函数高效应用1Excel是办公自动化中非常重要的一款软件，它不仅仅能够快捷方便地处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算。函数作为Excel处理数据的一个最重要手段，功能是十分强大的，在生活和工作实践中可以有多种应用，您甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。你平时都用到Excel的哪些功能？你是否经常进行大量的数据整理？你是否经常对数据进行图表化处理，分析数据趋势？你是否可以将学到的Excel函数灵活运用到实际工作中？。。。。。。目录基础操作-实用小技巧函数应用-常用几类函数操作数据透视表-数据整理的有效工具1基础操作快速填充定义名称数据有效性条件格式壹基础操作-实用小技巧2快速填充基础操作1壹3方法二：鼠标双击方法一：鼠标左键拖动方法五：运用快速填充“求和小计”ALT+=方法四：CTRL+D或CTRL+R延伸：自定义填充设置方法三：快捷键：CTRL+ENTER定义名称基础操作2壹4EXCEL使用中遇到的部分问题实现数据区域的动态化函数中某变量重复出现编辑函数麻烦函数嵌套固定次数函数公式太长定义名称基础操作2壹5可读性与可理解性宏应用基础突破嵌套限制代替辅助区域简化公式便于修改定义名称【定义名称】定义名称，就是给数值、字符串或单元格(组)起的名字。我们可以将一个或多个数值或单元格定义名称，利用名称可以实现很多功能，比如名称可以带入公式计算，名称可以快速的定位到你想找到的数据行。定义名称也是EXCEL高级宏应用的基础定义名称基础操作2壹6利用名称栏直接填写1利用定义名称选项2鼠标右击选择3根据所选内容创建4创建名称方法数据有效性基础操作3壹7日期格式不标准数字乱七八糟输入重复内容名称不统一不按规定统计只能输入日期格式只能输入小数或整数不允许重复输入只能从下拉选项选择以固定值开始或结尾工作中，你是否经常统计一些信息？把表格发给别人，收回的表格中的数据千奇百怪。收上来的数据我想要的数据数据有效性基础操作3壹1.下拉式选择输入2.只能输入日期3.只能输入数字8数据有效性基础操作3壹自定义限制内容公式限制单元格以“A”开头=LEFT(A1,1)=A限制单元格以“公司”结尾=RIGHT(A1,2)=公司限制单元格第三个字符为“中”=COUNTIF(A1,??中*)限制输入重复数据=COUNTIF($A:$A,A1)=1限制单元格只能输入数字=ISNUMBER(A1)限制单元格只能输入文本=ISTEXT(A1)限制单元格只能输入日期=YEAR(A1)=YEAR(NOW())9条件格式基础操作4壹对于一定区域的单元格，按照既定的规则，将符合条件的单元格的格式自动显示。102问题清单中已经解决完的问题1快要到期的应收账款、应付账款5最佳结果的特别显示4数值大小的对比显示3超过现金、库存的安全范围条件格式基础操作4壹对于一定区域的单元格，按照既定的规则，将符合条件的单元格的格式自动显示。11条件格式基础操作4壹12手动设置格式1.有可能选错区域2.重复设置3.条件变了，还得手动修改条件格式1.预设格式2.不同条件，不同格式3.系统检索、自动修改小结基础操作壹131.左键拖动2.双击3.CTRL+回车4.ALT+=5.CTRL+R6.CTRL+D快速填充1.名称栏2.定义名称选项3.右击命名4.选中内容创建表义名称1.序列2.日期3.小数4.自定义数据有效性1.强调格式2.底纹显示条件格式1.空值定位2.常量定位3.公式定位定位条件函数应用函数基础逻辑函数IS类判断函数统计函数贰文本函数日期函数数学函数查找引用函数函数应用-常用几类函数操作14函数基础函数应用1运算符标点函数字符单元格引用常量Excel函数【函数】Excel函数：是预先内置在excel软件中用于计算、分析等数据处理的功能模块=LEFT(A6,FIND(省,A6)-1)=IF(ISODD(MID(B5,9,9)),男,女)逻辑函数、IS类函数、统计函数、文本函数日期函数、数学函数、查询和引用函数15贰函数基础函数应用贰161算数运算符：+、-、*、/、%、^1比较运算符：=、、=、、=、2文本运算符：&3运算优先级^、%、*和/、+和-、&4运算符运算符函数基础函数应用贰171行数组1列数组2一维数组3多维数组4数组数组方向区分维度划分函数基础函数应用贰181相对引用绝对引用混合引用填充引用公式时，公式中的单元格是变动的填充引用公式时，公式中的单元格是固定的填充引用公式时，公式中的单元格是行变动或列变动逻辑函数函数应用贰192AND函数1OR函数2NOT函数3IF函数4逻辑函数逻辑函数函数应用贰202逻辑值TRUE和FALSE转换成1和0的各种方法(等值数学运算)逻辑值数值公式位置TRUE1=--A1“--”在前FALSE0=A2*1“*”前后均可TRUE1=A3/1“/”在后TRUE1=A4-0“-”在后FALSE0=A5+0“+”前后均可FALSE0=N(A6)置于函数N中逻辑函数函数应用贰212总结规律：AND可以用*来代替；OR可以用+来代替。函数返回值函数返回值=AND(1,1,0)FALSE=AND(1,1,1)TRUE=OR(0,1,1)TRUE=OR(0,1,1)TRUE=NOT(1)FALSE=NOT(0)TRUEAND、OR、NOT函数：执行逻辑判断，返回TRUE或FALSE。AND（表达式）、OR（表达式）、NOT（表达式）逻辑函数函数应用贰222IF函数：执行真假值判断，根据逻辑计算的真假值，返回不同结果。=IF(条件，TRUE，FALSE）逻辑表达式条件不成立时返回的值条件成立时返回的值逻辑函数函数应用贰2323如果判断标准有汉字内容，则在汉字前后加上英文状态下的双引号2如果函数IF的参数包含数组，则在执行语句时，数组中的每一个元素都将计算1OFFICE2003IF函数可以嵌套七层（07,64层）4一般来说，超过三层的嵌套，就不适合用IF函数了IS类判断函数函数应用贰243IS类判断函数：用来检验数值或引用类型的九个工作表函数，概括为IS类函数。可以检验数值的类型并根据参数取值返回TRUE或FALSE。序号函数功能1ISBLANK(value)值为空白单元格2ISERR(value)值为任意错误值（除去#N/A）3ISERROR(value)值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）4ISLOGICAL(value)值为逻辑值5ISNA(value)值为错误值#N/A（值不存在）6ISNONTEXT(value)值为不是文本的任意项（注意此函数在值为空白单元格时返回TRUE）7ISNUMBER(value)值为数字8ISREF(value)值为引用9ISTEXT(value)值为文本统计函数函数应用贰254MIN/MAX函数1COUNT函数2COUNTA函数3COUNTIF函数4统计函数统计函数函数应用贰264MIN/MAX函数：找出系列数据中的最小/大值。MIN(number1,number2,...)MAX(number1,number2,...)MIN(数据区域或名称)MAX(数据区域或名称)最多30个数字通常做法统计函数函数应用贰274COUNT、COUNTA、COUNTIF函数COUNT求某区域数字单元格个数，非数字单元格不统计。求某区域非空单元格个数。求空单元格个数的是COUNTBLANK可以用在动态数据表中。求一定区域中，符合条件的单元格个数。可以使用通配符；英文字母不区分大小写。COUNTACOUNTIF文本函数函数应用贰285LEFT/RIGHT/MID函数1LEN&LENB函数2FIND函数3SEARCH函数4文本函数文本函数函数应用贰295LEFTRIGHTMIDFINDSEARCH从字符串的左侧、右侧、中间截取指定长度的字符。LENLENB计算字符串的字符或字节数。查找字符在字符串中的位置。返回从开始首次找到特定字符或文本字符串的位置上特定字符的编号组合使用日期函数函数应用贰306YEAR/MONTH/DAY函数1DATE函数2EDATE/EMONTH函数3DATEDIF函数4日期函数日期函数函数应用贰316在excel中，把日期数据视为一种数值的特殊表现形式。(可以说一个日期一定对应一个数字，但一个数字不一定对应一个日期)。数字1400001000029584652958466转为日期1900-1-12009-7-61927-5-189999-12-31##############日期函数函数应用贰326提取数据DAY(表达式)提取日期组合数据MONTH(表达式)提取月份YEAR(表达式)提取年份DATE(YYYY,MM,DD)将三个数字组合为日期函数yearmonthdaydateeomonth日期年月日合并日期月份天数2005-8-72005872005-8-7312011-5-62011562011-5-6311985-12-15198512151985-12-15311999-4-2519994251999-4-2530日期函数函数应用贰336非标准日期格式如何转化为标准日期格式1432Excel中的标准日期格式是用“/”或“-”连接显示的数值型字段。但我们工作中有不同的使用习惯，如写成：20131122、2013.11.22，也不是真正的日期。遇到非标准日期，如何转化为标准日期呢？YYYYMMDD型非标准日期格式YYYY.MM.DD型非标准日期格式YYYY-MM-DD型非标准日期格式什么格式都有日期函数函数应用贰346银行对账单的处理日期函数函数应用贰356EDATE、EMONTH、DATEDIF函数EDATE计算指定月数之前或之后的几个月的日期。计算指定日期之前或之后的几个月的特定月份的月底的日。计算两个日期之间的年数、月数和天数。EMONTHDATEDIF数学函数函数应用贰367ROUND函数1RAND&RANDBETWEEN函数2SUMIF函数3SUMIFS函数4数学函数SUMPRODUCT函数5数学函数函数应用贰377如果num_digits大于0，则四舍五入到指定的小数位。如果num_digits等于0，则四舍五入到最接近的整数。如果num_digits小于0，则在小数点左侧进行四舍五入。ROUND函数：返回某个数字按指定位数保留小数后的数字=ROUND(number,num_digits)数学函数函数应用贰387ROUNDUP函数：远离零值，向上舍入数字。ROUNDDOWM函数：靠近零值，向下（绝对值减小的方向）舍入数字。数字位数ROUND取数ROUNDUP取数ROUNDDOWN取数4.999055489.455189.589.589.4-673.976-1-670-680-670870.342-2900900800数学函数函数应用贰397SUMIFSUMPRODUCT单条件求和。SUMIFS多条件求和，不可以对查找区域进行函数处理。多条件求和，可以对查找区域进行函数处理。=SUMIF(查找区域，关键字，求和区域)=SUMIFS(求和区域，查找区域，关键字，查找区域，关键字，…)=SUMPRODUCT(条件1*条件2*…*求和区域)查找引用函数函数应用贰408LOOKUP函数1VLOOKUP函数2HLOOKUP函数3C函数4查找函数查找引用函数函数应用贰418LOOKUP函数：在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。查找区域须升序排列。查找引用函数函数应用贰428VLOOKUP函数：在指定表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。=VLOOKUP(查找值，查找区域，返回位置（列），查找方式)查找引用函数函数应用贰438ROW：返回单元格或区域的行号COLUMN：返回单元格或区域的列号ROWS：计算行数COLUMNS：计算列数ROW、COLUMN、ROWS