Excel常用技巧

Excel常用内容2目录5.图表3.数据有效性4.条件格式1.基础知识2.函数3一、Excel基础知识4•工作簿与工作表工作簿是Excel使用的文件架构，我们可以将它想象成是一个工作夹，在这个工作夹里面有许多工作纸，这些工作纸就是工作表。“工作夹”工作表5Excel工作界面公式栏列号名字框行号当前单元格工作表标签工作表工作簿6常用快捷键(1)复制、粘贴、剪切选中区域复制选定区域CTRL+C粘贴选定区域CTRL+V剪切选定区域CTRL+X撤消最后一次操作CTRL+Z复制上行单元格CTRL+D复制左侧单元格CTRL+R重复上一步操作CTRL+Y(2)打印显示“打印”对话框CTRL+P显示“打印”预览CTRL+F27二、Excel函数81、什么是函数？什么是公式？公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*2)包含函数的公式91、函数分类2、用好EXCEl自带的帮助，活用函数的关键是要具备清晰的程序逻辑思维。2、EXCEL公式分类10•3、EXCEL公式表达式=（B4+25）/SUM（D5:F5）公式特定引导符号区域范围引用单元格地址数值型常量Excel函数运算操作符号11符号类型符号举例算术运算+；-；*；/；^=6^2=3+2*5比较运算;;=;；=；=；=52=false文本运算&=天龙&八部区域运算:=Sum(A1:A3)交叉运算_(空格)=sum(A1:B5A4:D9)=sum(A4:B5)联合运算，（逗号）=rank(A1,(A1:A10，C1:C10))4、EXCEL公式运算符号12•5、EXCEL公式的相对引用、绝对引用和混合引用1、相对引用：复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1当将公式复制到C2单元格时变为：=A2+B2当将公式复制到D1单元格时变为：=B1+C12、绝对引用：复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1当将公式复制到C2单元格时仍为：=$A$1+$B$1当将公式复制到D1单元格时仍为：=$A$1+$B$13、混合引用：复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1当将公式复制到C2单元格时变为：=$A2+B$1当将公式复制到D1单元格时变为：=$A1+C$1不同的引用在拖动公式时具有不同的结果。引用的转换快捷键：F413•6、EXCEL公式中的跨工作表引用1、引用其他工作表的数据=工作表名称！目标单元格例：=sheet2!A12、引用已打开的工作簿的数据=[工作簿名称]工作表名称！目标单元格例：=[Book2.xls]sheet1!$A$13、引用未打开的工作簿的数据=‘文件路径[工作簿名称]工作表名称’！目标单元格例：=‘D:\Excel案例[Book2.xls]sheet1’!$A$1注：将最好先打开工作簿，再编写跨表引用公式14类型函数用途类型函数用途数值Sum求和函数文本len计算文本长度Average平均值函数left文本截取（左）Max/Min最大/最小函数right文本截取（右）sumproduct数组求和函数mid文本截取（指定）SUMIF条件求和CONCATENATE文本合并Sumifs多条件求和text文本化统计Count单元格统计函数value数值化CountA时间DAY日期countblank查找与引用COLUMN列号比较RANK排名ROW行号LARGE第K大的值LOKKUP查找SMALL第K小的值VLOKKUP列查找逻辑IF判断函数HLOOKUP行查找AND且函数MATCH匹配OR或函数INDEX引用IFERROR错误判断offset偏移以上函数基本能满足一般工作需求。7、EXCEL常用函数15注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数身份证号地区出生日期性别=left(a1,6)=mid(a1,7,6)=right(a1,1)3201027810014923201027810012员工代码社保号姓名=left(a1,10)=mid(a1,11,3)0002337084张三0002337084张三0000656246李世民0000656246李世民为什么是3而不是2？为什么不用Right函数？7.1----文本处理函数（提取字符Left、Right、Mid）16日期年月日日期=Year(a1)=Month(a1)=Day(a1)=DATE(A1,B1,C1)2009-6-1620096162009-6-16（1）取出当前系统时间/日期信息，无参数。NOW()、TODAY()。（2）取得日期/时间的部分字段值（年份、月份、日数或小时），无参数。YEAR()、MONTH()、DAY()、HOUR()7.2----日期与时间函数117（3）计算两个日期之间的天数、月数或年数之差。Datedif(start_date，end_date，unit)7.2----日期与时间函数2unit代码函数返回值开始日期结束日期公式显示结果y时间段中的整年数2000-6-152009-6-16=datedif(A1,B1,y)9m时间段中的整月数2008-6-152009-6-16=datedif(A2,B2,m)12d时间段中的天数2008-6-152009-6-16=datedif(A3,B3,d)366md忽略日期中的年和月，计算天数差2008-3-172009-6-16=datedif(A4,B4,md)30ym忽略日期中的年和日，计算月数差2008-6-172009-6-16=datedif(A5,B5,ym)11yd忽略日期中的年，计算天数差2008-6-172009-6-16=datedif(A6,B6,yd)364当单位代码为YM时,计算结果是两个日期间隔的月份数，不计相差年数。如忽略年份后，开始日期必须大于结束日期，否则公式计算错误。18数值Round--四舍五入Roundup--向上取整Rounddown--向下取整公式结果公式结果公式结果123.456=round(A2,0)123=roundup(A3,0)124=rounddown(A3,0)123123.456=round(A3,2)123.46=roundup(A3,2)123.46=rounddown(A3,2)123.451234.56=round(A4,-2)1200=roundup(A3,-2)1300=rounddown(A3,-2)1200区别7.3----数字计算函数（取整函数Round、Roundup、Rounddown）197.3----数字计算函数（取整函数Int、Trunc）数值公式结果说明备注3.2=Int(a1)3返回不大于3.2的最大整数3-3.2=Int(a1)-4返回不大于-3.2的最大整数-43.2=Trunc(a1)3返回截去小数部分后的整数3-3.2=Trunc(a1)3返回截去小数部分后的整数-3=ROUNDDOWN(a1,0)20语法：SUM(number1，number2，...)。参数：Number1，number2，...为1到30（07以上版本254）个需要求和的数值、区域或引用。巧用：格式相同的多个工作表的同位置单元格求和。若Sheet1!A1=1、Sheet1!A1=2、Sheet1!A1=37.4----数字计算函数----sum217.4--统计函数求数据集的满足不同要求的数值的函数：1、最大值MAX2、最小值MIN3、平均值AVERAGE4、排名RANK227.5查找引用函数查询与引用函数可用来在数据数组或表格中查找特定数值，或查找某一单元格的引用。常用函数VLOOKUP、SUMIF、COUNTIF、COLUMN、ROW语法用途=VLOOKUP（需查找值，范围，相对列，false）=SUMIF(条件区域，条件，值区域）根据指定条件对若干单元格、区域或引用求和=COUNTIF(条件区域，条件）统计某一区域中符合条件的单元格数目=COLUMN(目标列/单元格)=ROW(目标行/单元格)237.6逻辑函数IFIF用途：按指定的条件计算满足条件选项A列B列C列D列=If(C1=22,全勤,C1)序号姓名出勤备注1张三20202李四23全勤3王五21214贾六22全勤241、IF函数的嵌套：IF(A11,SUM(B1:G1),)在这个公式中，sum函数是2级函数，它是if函数的参数，(Excel2007允许64层的嵌套，2003只能7层)=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN()),))第1个IF的条件参数当条件为真时返回该值当条件为假时进入下一个IF第2个IF的条件参数当条件为真时进入Index函数当条件为假时返回空值3、解读如下：=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN()),))2、复杂案例7.6逻辑函数IF251、目的：按规律切割工资条2、分析数据规律，确定公式组合结果的第一行：永远是原数据中的第一行。结果的第二行：结果第2行对应原数据第2行，结果第5行对应原数据第3行，第8对应第4，第11对应第5。结果的第三行：永远为空。这里面第二个最难算，找找看它们之间有什么规律。例:工资打印切割公式=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN()),))7.6函数编写逻辑思路268、Excel公式的常见错误信息错误信息原因#####！列宽不够显示，或使用了负的日期，和时间。#DIV/0!被零除错误，将数字除以零(0)或除以不含数值的单元格#N/A在函数或公式中没有可用数值时#NAME?在公式中出现excel不能识别的文本时#NULL!计算为空值时，无法得出计算结果#NUM!当函数或公式中某个数字有问题时#REF!当单元格引用无效时，如被删除、超出单元格位置#VALUE!参数或数值类型错误公式查错时的要诀：1、读懂公式2、看看公式在拖动过程中的引用方式是否正确3、当嵌套太多太复杂时，把公式分层粘到单元格中公式查错工具：1、键盘上的F92、公式选项卡中的27提升工作效率熟练掌握灵活运用数学函数逻辑函数统计函数查询函数SUMROUNDROUNDUPROUNDDOWNAverageMaxMinRankVLOOKUPCOUNTIFSUMIFIF28三、Excel数据有效性29数据有效性130数据有效性231数据有效性3停止不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。警告可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。信息可选择是否输入数据，点击“确定”可以直接输入数据，点击“取消”可以取消输入。32四、Excel条件格式33例：业务员业绩标记Excel2010版本可通过“条件格式-项目选取规则标示出数据范围内，排行在最前面或最后面几项的数据。倘若我们想了解每一季的业绩中，业绩位于前3名或位于倒数3名的业务员，就可以利用此规则来查询。请选取要查询的范围，然后按下条件格式选项钮，执行『项目选取规则／值最大的10项』命令。（先查询第1季的前3名）条件格式1341、在此输入3，表示要查询前3名的数字2、选择要标示的格式4、结果：第1季的前3名标记成了粉红色3、按确定条件格式235可以用同样的方法查出2～4