excel常用函数使用技巧分享 (NXPowerLite)

Excel常用函数办公软件使用技巧分享之——CV市场研究部—2—数据共享、程序开发简单动画、协同办公提高效率、潜力无限图表演示、统计分析辅助计算、报表分析制作表格、表单应用Excel能做什么，还能做什么？……—3—数据分析排序筛选列表数据透视表假设分析高级分析数据操作图表与图形录入与导入常规编辑格式化高级编辑自定义打印图表的构成图表美化高级图表交互式图表公式与函数公式与基础函数应用函数嵌套数组公式自定义函数宏与VBA运行录制宏VBA基础VBA对象窗体与控件类模块—4—Excel常用函数使用第二章Excel基本操作技巧第一章第三章Excel图表制作案例—5—Excel基本操作技巧快速输入数据限定输入内容格式设置数据处理Excel基本操作技巧第一章Excel基本操作技巧—6—快速输入数据自动填充已知：单元格内输入一月二月，鼠标拖拉后，可自动生成三月、四月。演示：如何实现输入体系课，拖拉鼠标后自动生成所有课室？单元格设置法演示：如何实现输入1，显示男，输入2，显示女？多单元格输入演示：Ctrl+Enter/Ctrl+D，同时输入相同数据。第一章—7—限定输入内容-数据有效性有没有遇到过这样的情况，当您让别人给您填写一个资料时，老是不能按您的要求填写，更有甚者，会把您的表格乱改一通，比如您让别人填写个人简历，其中性别一栏共有以下内容：男、帅哥、男性、man、GG女、靓女、超女、女性、MM、PLMM晕，让我如何统计分析？报怨别人不按你的要求填写报表！我觉得：不要抱怨，是您给了别人这个自由！第一章—8—格式设置清零方法一：工具—选项—视图—零值方法二：查找和替换单元格文字换行快捷键组合：Alt+Enter合适列宽行高鼠标移至行/列标右边框，双击。第一章—9—数据处理选择性粘贴—转置演示：如何将竖向排序色数据重新横向排列。按行排序演示：如何使选定区域按行数据顺序排序。高级筛选演示：如何实现多条件筛选。第一章—10—其他Excel操作，键盘有话说－快捷键用法学会授权，Excel做得比您更快更好人非圣贤，孰能无错－出错信息详解分组显示使您的数据层次更加清晰快速输入系统日期：Ctrl+；快速输入系统时间：Ctrl+Shift+；第一章—11—函数公式结构第二章Excel常用函数案例=（B4+25）/SUM（D5:F5）Excel函数单元格地址引用区域常量(数值型)运算符公式符号函数公式的标点符号必须在英文状态下输入，字符应用双引号引起。Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式公式是由用户自行设计对工作表进行计算和处理的公式—12—常用运算符第二章类型运算符功能示例算术运算符+加A1+B1-减A1-B1*乘A1*B1/除A1/B1%百分比A1%^乘方A1^2比较运算符=等于A1=B1大于A1B1小于A1B1=大于等于A1=B1=小于等于A1=B1不等于A1B1文本运算符&文本值连接“单价”&”50元”返回值：单价50元引用运算符:区域运算符A1:B1(A1,A2,B1,B2)=(A1:B2),合并多个引用区域(A1:B1,C1:D1)—13—相对引用、绝对引用、混合引用第二章引用类型切换键F4，符号$列号行号B1相对引用：公式所在单元格的位置改变，引用单元格的位置也随之改变。如B1绝对引用：引用单元格的位置始终固定不变。如$B$1混合引用：引用单元格未使用$符号固定的行号或列号，随公式所在单元格的位置改变。如$B1或B$1部门目标销量占总销量目标比率营业一课200020%=C2/$B$6营业二课300030%绝对引用营业三课350035%营业四课150015%合计10000100%月份1月2月3月4月工作日22202123员工A20201922员工B19202121员工C21191817出勤率1月2月3月4月员工A91%100%90%96%员工B86%100%100%91%员工C95%95%86%74%=B5/B$1混合引用季度1季度2季度3季度销量200400500行业总量300060007000市占率6.7%6.7%7.1%=B2/B3相对引用—14—第二章常用函数类型类型名称语法用途日期时间YEARYEAR(单元格)选定日期序列号或日期格式单元格中的年份DATEDIFDATEDIF(开始日期,结束日期,”Y”)两个日期之间的年数文本TEXTTEXT(单元格,指定格式)将单元格数值转换为按指定数字格式表示文本MIDMID(单元格,开始位置,提取字符数量)在单元格中按指定开始位置提取指定数量的字符统计AVERAGEAVERAGE(单元格1,单元格2,…)选定单元格为数字类型的数据的加权平均数COUNTCOUNT(单元格1,单元格2,…)选定单元格为数字类型数据的个数COUNTIFCOUNTIF(单元格区域,条件)选定单元格区域内满足条件的单元格个数MAXMAX(单元格1,单元格2,…)选定单元格中数值最大的值逻辑IFIF(参数条件,结果1,结果2)满足参数条件,返回结果1,否则返回结果2ANDAND(参数1,参数2,…)过程中所有参数都需满足OROR(参数1,参数2,…)过程中只需满足一个参数即可数学SUMSUM(单元格1,单元格2,…)选定单元格内所有数据之和SUMIFSUMIF(条件区域,条件,取值区域)符合条件的条件区域所对应的取值区域内数据之和ROUNDROUND(单元格,保留位数)对选定单元格进行四舍五入查找引用VLOOKUPVLOOKUP(单元格,查找区域,查找区域第N列,FALSE)查找区域首列符合指定单元格的查找区域第N列数值HLOOKUPHLOOKUP(单元格,查找区域,查找区域第N行,FALSE)查找区域首行符合指定单元格的查找区域第N行数值—15—第二章入职日期入职年入职月入职日至2011-12-31工龄2004/03/2020043207年A2=YEAR(A2)=MONTH(A2)=DAY(A2)=DATEDIF(A2,2011-12-31,Y)取A2值年份取A2值月份取A2值日号计算入职至2011年12月31日的工龄M计算月数；D计算天数YM忽略年日,计算两日期相差的月数YD忽略年,计算两日期相差的天数MD忽略年月,计算两日期相差的天数今天日期工作30个月后日期2013/10/242016/04/24=today()=EDATE(E10,30)E10在E10日期上加30个月的日期函数应用示例-日期时间函数—16—第二章函数应用示例-逻辑函数（IF、AND、OR）考核处罚考核等级优秀员工901A502D800B优600D701C900A优A7B7D7=IF(A7=90,A,IF(A7=80,B,IF(A7=70,C,D)))=IF(AND(OR(D7=A,D7=B),B7=0),优,)90分—A，80分—B，70分—C，其他分—D考核等级为A或B,且处罚为0的为优秀员工—17—第二章函数应用示例-逻辑函数（IF）逻辑函数（IF函数特殊情况）体系计划仓库采购财务总务设计品质技术模具成型营业001006008009010011012002003004005007=IF(A1=体系,1,0)+IF(A1=品证,2,0)+IF(A1=技术,3,0)+IF(A1=模具,4,0)+IF(A1=成型,5,0)+IF(A1=计划,6,0)+IF(A1=营业,7,0)+IF(A1=仓库,8,0)+IF(A1=采购,9,0)+IF(A1=财务,10,0)+IF(A1=总务,11,0)+IF(A1=设计,12,0)IF函数最多嵌套七层IF(条件,A,IF(条件,B,IF(条件,C,IF(条件,D,IF(条件,E,IF(条件,F,IF(条件,G,H)))))))七层以上可使用公式IF(条件,A,0)+IF(条件,B,0)+IF(条件,C,0)+IF(条件,D,0)……—18—思考：结合以上所列举的日期函数和逻辑IF函数，对柳汽所生产的车辆进行超期判断监控，判断标准如下所示，那公式该怎么写呢？超期判断填写（截止今天）：3个月内、3-6个月、6-12个月、12个月以上第二章综合应用案例思考排产日期超期判断2013/10/93个月内2013/5/73-6个月2012/9/112个月以上2012/12/196-12个月A5=IF(EDATE(A5,3)TODAY(),3个月内,IF(EDATE(A5,6)TODAY(),3-6个月,IF(EDATE(A5,12)TODAY(),6-12个月,12个月以上)))排产日期超期判断2013/10/9？2013/5/72012/9/12012/12/19—19—第二章函数应用示例-文本函数工号姓名身份证号码出生年月日出生年月日格式转换130张三310110199010251253199010251990-10-25C2=MID(C2,7,8)=TEXT(E2,0000-00-00)取C2值第7位开始的8个字符出生年月日按要求转换格式年份日期199025=LEFT(E2,4)=RIGHT(E2,2)取E2值的左边4个字符取E2值的右边4个字符名称字数字节数M起始位置M7C起始位置霸龙M7C5733A2=LEN(A2)=LENB(A2)=FIND(M,A2,1)=SEARCH(M7C,A2,1)返回字数返回字节数搜索单一字符支持通配符搜索—20—东风完整车型代码有效代码公式DFL4251A2-T08A-F03-11ABDFL4251A2-T08A-F03-11=LEFT(A2,LEN(A2)-2)DFL5251GJBA1-K09G-000-020JDFL5251GJBA1-K09G-000-02=LEFT(A3,LENB(A3)-2)DFL3310A13-K37-300-010$SHZ82A？东风完整车型代码有效代码公式DFL4251A2-T08A-F03-11ABDFL4251A2-T08A-F03-11=LEFT(A2,LEN(A2)-2)DFL5251GJBA1-K09G-000-020JDFL5251GJBA1-K09G-000-02=LEFT(A3,LENB(A3)-2)DFL3310A13-K37-300-010$SHZ82ADFL3310A13-K37-300-01第二章综合应用案例思考思考：结合以上所列举的文本函数，需要对东风完整车型代码进行处理，要求保留红色字体字母，剔除黑色字体字母，该如何填写公式呢？前两个代码可以通过LEFT、LEN(B)综合进行代码截取，但第3个车型代码由于代码类型无法批量进行处理，应该如何进行公式填写？需要运用什么函数？通过LEFT函数，以及FIND的嵌入使用=LEFT(A4,FIND(-,A4,FIND(-,A4,FIND(-,A4,1)+1)+1)+2)—21—第二章函数应用示例-数学函数营业部姓名营业额总营业额一课占营业额%一课A200010400510049%一课B1500E2F2二课C2500=SUM(C2:C7)=SUMIF($A$2:$A$7,F1,$C$2:$C$7)=ROUND(F2/E2,2)一课D1600对C2:C7值求和对A2:A7值为F1的对应C2:C7值求和保留2位小数四舍五入二课E1000二课F1800A2:A7C2:C7ROUNDDOWN向下取整ROUNDUP向上取整F1原数字round取整向下取整截尾取整1.11111.7211-1.1-1-2-1-1.7-2-2-1B5C5D5=ROUND(A5,0)=INT(A5)=TRUNC(A5,0)原数字立方公式平方根公式464=POWER(A2,3)2=POWER(A2,1/2)9729=A3^(3)3=A3^(1/2)—22—第二章函数应用示例-统计函数部门姓名工号性别年龄6月营业额(万元)营业一课A2男352000B3女301500营业二课C6女332500D10男281600营业三课E35女401000F50男191800A2:A7C2:C7D2: