电脑操作系列培训课程函数与公式课件

电脑操作系列培训课程电脑操作系列培训课程 2 2目目 录录15分钟30分钟60分钟60分钟3 3导 读4 4前前 言言1、课堂规则：1）要求：“空杯心态”不掉队、勤动手脑、记录要点、互帮互助、课后五多 （多实践、多思考、多总结、多探索、多研讨）。2）规则：“养机场”“部落先知负责制”5 5前前 言言2、课前提问：1）关于EXCEL公式与函数，讲讲你知道的；2）讲讲你想知道的。6 6前前 言言3、函数与公式概述：7 7前 言1 1）函数与公式学习的六个阶段）函数与公式学习的六个阶段高级阶段高级阶段中级阶段中级阶段第二道门槛第二道门槛第一道门槛第一道门槛初级阶段初级阶段揭开神秘面纱揭开神秘面纱数组公式与多维引用嵌套函数与合并公式查找与匹配函数条件函数的运用操作界面、简单函数提升工作效率的工具sum()、today()习惯使用F1、Baidu知道、EXCELhome求助 If()的运用 vlookup()的运用数字小写变大写、生成日历动态转置等8 8前 言2）函数、公式的定义公式：以等号开头，通过运算符按一定的顺序组合进行数公式：以等号开头，通过运算符按一定的顺序组合进行数据运算处理的等式。据运算处理的等式。请判断以下哪些是公式?A、=15*3+20*2 B、=A1*3+A2*2 C、=单价*数量 D、=SUM(A1*3,A2*2)答案：全是，通常公式可以有含数值运算、含单元格引用运算、含名称运算、含函数的运算四种形式。9 9前 言如：=SUM(A1,A2,A3)表示A1,A2,A3这三个单元格数值相加的结果。以逗号分隔的以逗号分隔的3 3个参数个参数函数名函数名称称嵌套函数嵌套函数=IF(B2=60,=IF(B2=60,”合格合格”,”不合格不合格”),),”优秀优秀”)函数的结构如下图函数：按照特定的算法执行计算并返回计算结果的预定义的特殊公式。1010导 读1111常用函数与公式常用函数与嵌套函数的举例（含部分工具栏、键操作说明与常用小技巧）类型类型知识点知识点说明说明备注备注对应示例对应示例号号键说明F5快速选定工作表中所有带有公式的单元格，F5键定位条件定位公式即可略键说明Ctr+BacksPace快速返回选中区域，按Ctr+BacksPae（即退格键）略键说明shift+F5查找，ctrl+F略键说明ctrl+:返回当前日期2010-5-14略键说明ctrl+shift:返回当前时间17:17略键说明ctrl+方向键:定位空格略1212常用函数与公式类型类型知识点知识点说明说明备注备注对应示例对应示例号号键说明ctrl+shift+光标选中非空区域略键说明ctrl+shift+将光标放在表格中任一不为空的单元，按下组合键后选中上下左右方形非空区域略键说明ctrl+shift+enter数组公式（动态转置）选定转置后区域后插入数组函数transpose,选区域后同时按下该组合键即可改动源数据则转置后的数据自动更改略键说明ctrl+D复制相临行略键说明*/?单个字符*多个字符略键说明F2不用鼠标,点进光标所在单元格,或是在地址栏输入单元格名,Enter再点F2略1313常用函数与公式类型类型知识点知识点说明说明备注备注对应示例对应示例号号小技巧批量输入ctrl下选取单元格，输入内容，enter;或是先复制，多个选中后再粘贴小技巧多个数据个位变万位在空格中输10000，复制，选中要调位数的区域，选择性粘贴除（可同时选择数值）小技巧文本变数值在一个单元格中输入1,复制,选中要变数值的文本单元格,选择性粘贴乘以小技巧表格要求无合并单元格、行列连续、有加列序号如何显示0008,三种方法，0008，改为文本格式，=text(8,0000)小技巧保护工作区全选后在单元格格式里把上锁标记全去掉，然后选定目标区域后标记，再用工具栏-选项-安全性，加密码保护小技巧排序三个以上的排序，先加辅助列=A1&/&B1&/&C1&/D1,再把该列排序即是按排序1414常用函数与公式类型类型知识点知识点说明说明备注备注对应示例号对应示例号小技巧高级筛选选把数据区标题行复制到条件区，输入条件（同行：与and，同列不同行：or;文本：*/?,数：）,按高级筛选（条件区：标题与条件全部选入）小技巧同时对一个工作簿中多个表格进行页面设置在工作表的表名处选定多个表格或全部表格，再进行设置，即对多个表格进行了设置。小技巧自动保存文件在EXCEL中如何使它象WORD一样的自动定时保存文件？点击“工具”“选项”“保存”设置自动保存时间隔小技巧定位快速选定工作表中所有带有公式的单元格，F5键定位条件定位公式即可小技巧有效性在几个人名间进行填写，有效性选序列，条件中人名间用英文状态下的,用工具选项预设序列时也是用英文状态下的,小技巧改写键入文字会取代了原有文字，在状态栏内点击“改写”（或直接按键盘上的insert）1515常用函数与公式类型知识点说明备注对应示例号函数F4相对绝对单元格的转换锁定行列函数Sqrt返回正平方根=SQRT(ABS(-9)例1函数trim去除单元格内字符两边的空格函数type字符类型,1数值，2文本 =type(E20)函数text变换数字大小写=TEXT(A1,DBNUM2)例2函数ABS绝对值=ABS(E20)例2函数int取整=INT(E20)例21616常用函数与公式类型知识点说明备注对应示例号函数fixed=FIXED(2016.349,-1)表示四舍五入到小数点左边第一位=FIXED(2016.349,-1,true)表示返回的结果是真实值。“2020”无千分号2,020例3函数countcount计算非空单元格个数（不计算文本）,counta计算文本=COUNT(D21:D29)与=COUNTA(D21:D29)例4函数sumif/countif条件筛选求和/计数=SUMIF(D22:D30,30,E22:E30)指区域D22:D30中，选取单元格值大于30的单元，计算其对应的E列的单元格值之和。例4函数len、mid、left、right=len(A1),=mid(A1,第几位，取几位)去掉单元格中的后一位字符=LEFT(A1,LEN(A1)-1)例5函数日期函数=date(年，月，日),=tody()当前日期，now()当前时间，当前年月的头一天=DATE(YEAR(NOW(),MONTH(NOW(),1)=WEEKDAY(B2)显示星期几(结果直接在单元格式里设置成日期星期几)=edate(A1,1)显示日期的后一个月的日期例6函数CONCATENATE字段组合函数=CONCATENATE(MID(20点08分53秒,1,2),:,MID(20点08分53秒,4,2),:,MID(20点08分53秒,7,2)1717常用函数与公式类型知识点说明备注对应示例号函数if=IF(A3=80,优秀,IF(A3=60,及格,IF(A360,不及格)例7函数rank=rank(A2,范围)返回排名号例8函数ISERROR用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。=D38/E38,当E38是0时返回的是#DIV/0!=IF(ISERROR(D38/E38),D38/E38)当E38为0返回的的空格函数frequency以一列垂直数组返回某个区域中数据的频率分布。=FREQUENCY(数据区域,划分标准)函数fixed=FIXED(要四舍五入的数字,取整到小数点右边第几位)计算入司年份=FIXED(TODAY()-A16)/365,1)&年例9函数vlookup查找函数=VLOOKUP(A1,Sheet1!A:B,2,0)例10函数INDEX自动搜索符合条件的数值=INDEX($E$19:$E$35,MATCH(COLUMN()-2,$AG$19:$AG$35,0),1)例111818常用函数与公式类型知识点说明备注对应示例号函数身份证号码身份证号码：1、第17位是代表性别，双为女，奇数为男mod(A1,B)表示A1被B除后的余数。=IF(MOD(MID(4310021,17,1),2)=1,男,女)例12函数身份证号码2、7-12位数段表示生日，判断年龄=YEAR(TODAY()-YEAR(TEXT(MID(4310021,7,8),#-00-00)函数upper与lower字母变大小写=UPPER(Excel函数)=LOWER(Excel函数)函数char显示函数=CHAR(ROW(A1),row(A1)表示1，也就是char(1)返回一个对应的符号，1-128会返回不同的符号，可以在做表格之前先把他们全放在一列，直接取用，免得在插入菜单中一个个的选择、点击。见右示例，拖动生成人员A-人员Z序列。横向生成用=人员&CHAR(COLUMN(A1)+64)；纵向生成装Column(A1)换成ROW(A1)人员B例131919常用函数与公式类型知识点说明备注对应示例号提升生成文本首字母返回指定文本的第一个字的拼音首字母=LOOKUP(CODE(张虹),45217+0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264,A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,W,X,Y,Z)提升transposer数组公式（动态转置）选定转置后区域后插入数组函数transpose,选区域后同时按下该组合键ctrl+shift+enter即可改动源数据则转置后的数据自动更改提升筛选单元格格式筛选单元格格式;选中要筛选单元格文本颜色的列，插入名称定义A，并在公式处输入=get.cell(24,A1)再插入一新列，在第一个单元格内=A 向下拖即可列出该列单元格的文本颜色参数，再做筛选2020导 读2121综合应用案例1 1、某业务部门业务员绩效得分考核销售收入、毛利两项、某业务部门业务员绩效得分考核销售收入、毛利两项指标，请你用系统导出的该部门指标，请你用系统导出的该部门4 4月销售商品明细、客户月销售商品明细、客户对应业务员名单、业务员所辖客户指标表三张表格来核对应业务员名单、业务员所辖客户指标表三张表格来核算业务员绩效得分。（算业务员绩效得分。（2020分钟）分钟）4月销售商品明细客户对应业务员名单业务员所辖客户指标表业务员绩效排序与分数2222综合应用案例2、请运用本课所学，在EXCEL中完成如下电子月历（20分钟）：要 求：1）随电脑内置日历变换；2）当月1号位于A2；3）当天日期格式自动显示如图；4）有“#月份”的自动显示；5）其他格式如图设置。2323综合应用案例3 3、请运用本课所学，在、请运用本课所学，在EXCELEXCEL中制出如下一张中制出如下一张“部门部门工作进度表工作进度表”（3030分钟）：分钟）：要要 求：求：1 1）日期随月份变换自行变换；）日期随月份变换自行变换；2 2）紫色条显示当前任务；）紫色条显示当前任务；3 3）填入每一事项的完成率即可自动生）填入每一事项的完成率即可自动生“执行执行 进度进度”（已完成、进行中、超前、滞后）；（已完成、进行中、超前、滞后）；4 4）后面自动生成甘特图（项目管理图）。）后面自动生成甘特图（项目管理图）。2424综合应用案例自动判断执行状态表中所有日期自动更新自动用桔条显示当前任务甘特图中蓝色块表示当日对应工作项已完成甘特图中灰色块表示当日对应工作项未完成2525导 读2626结 语 函数的学习内容很多，一般来说需要三天的时间才能完成常用知识函数的学习内容很多，一般来说需要三天的时间才能完成常用知识学习。限于课堂时间有限，仅将相关常识与最常见的少数函数给大家作学习。限于课堂时间有限，仅将相关常识与最常见的少数函数给大家作了简要介绍，在后面的时间里面，我们还将对应用范围较广的了简要介绍，在后面的时间里面，我们还将对应用范围较广的IFIF（）、（）、VLOOKUPVLOOKUP（）、（）、INDEXINDEX（）、（）、MATCHMATCH（）等函数与嵌套函数、组公式、简单（）等函数与嵌套函数、组公式、简单的宏等进行更深一层次的介绍，并会向大家解说如何在实际工作中应用的宏等进行更深一层次的介绍，并会向大家解说如何在实际工作中应用函数、公式提高工作效率、进行项目管理，相信能给大家的学习和工作函数、公式提高工作效率、进行项目管理，相信能给大家的学习和工作带来帮助。带来帮助。课堂上的学习的有限的，要想尽快成为课堂上的学习的有限的，要想尽快成为EXCELEXCEL应用中、高手，还需要应用中、高手，还需要大家勤于思考、提问、求解和总结，周围的同事、帮助文件、大家勤于思考、提问、求解和总结，周围的同事、帮助文件、BaiduBaidu知道、知道、网上其他学习网站和学习文档都将是大家最博识的老师。网上其他学习网站和学习文档都将是大家最博识的老师。本节课程到此结束，谢谢收看！