电脑操作系列培训课程(函数与公式).ppt

电脑操作系列培训课程 2 目 录 前前 言言 1 1 常用函数与公式常用函数与公式 2 2 综合应用案例综合应用案例 3 3 结结 语语 4 4 15分钟 30分钟 60分钟 60分钟 3 导 读 前前 言言 1 1 常用函数与公式常用函数与公式 2 2 综合应用案例综合应用案例 3 3 结结 语语 4 4 4 前 言 1、课堂规则： 1）要求：“空杯心态” 不掉队、勤动手脑、记录要点、互帮互助、课后五多 （多实践、多思考、多总结、多探索、多研讨）。 2）规则：“养机场” “部落先知负责制” 5 前 言 2、课前提问： 1）关于EXCEL公式与函数，讲讲你知道的； 2）讲讲你想知道的。 6 前 言 3、函数与公式概述： 7 前 言 1）函数与公式学习的六个阶段 Add Your Text 高级阶段 中级阶段 第二道门槛 第一道门槛 初级阶段 揭开神秘面纱 数组公式与多维引用 嵌套函数与合并公式 查找与匹配函数 条件函数的运用 操作界面、简单函数 提升工作效率的工具 sum() 、today() 习惯使用F1、Baidu 知道、EXCELhome求 助 If() 的运用 vlookup() 的运用 数字小写变 大写、生成 日历 动态 转置 等 8 前 言 2）函数、公式的定义 公式：以等号开头，通过运算符按一定的顺序组合进行数 据运算处理的等式。 请判断以下哪些是公式? A、 =15*3+20*2 B、 =A1*3+A2*2 C、 =单价*数量 D、 =SUM(A1*3,A2*2) 答案：全是， 通常公式可以有含数值运算、含单元格引用运算、含名称运算 、含函数的运算四种形式。 9 前 言 如：=SUM(A1,A2,A3)表示A1,A2,A3这三个单元格数值 相加的结果。 以逗号分隔的3个参数 函数名 称 嵌套函数 =IF(B2=60,”合格”,”不合格”),”优秀”) 函数的结构如下图 函数：按照特定的算法执行计算并返回计算结果的预定义 的特殊公式。 10 导 读 综合应用案例综合应用案例 3 3 结结 语语 4 4 常用函数与公式常用函数与公式 2 2 前前 言言 1 1 11 常用函数与公式 常用函数与嵌套函数的举例（含部分工具栏、键操作说明与常用小技巧） 类型知识点说明备注 对应示例 号 键说明F5 快速选定工作表中所有带有公式的单元格，F5 键定位条件定位公式即可 略 键说明Ctr+BacksPace 快速返回选中区域，按Ctr+BacksPae（即退格 键） 略 键说明shift+F5查找，ctrl+F 略 键说明ctrl+:返回当前日期2010-5-14略 键说明ctrl+shift:返回当前时间17:17略 键说明ctrl+方向键:定位空格 略 12 常用函数与公式 类型知识点说明备注 对应示例 号 键说明 ctrl+shift+光 标 选中非空区域 略 键说明ctrl+shift+ 将光标放在表格中任一不为空的单元， 按下组合键后选中上下左右方形非空区 域 略 键说明 ctrl+shift+ent er 数组公式（动态转置）选定转置后区域 后插入数组函数transpose,选区域后同 时按下该组合键即可 改动源数据则转置后的数据 自动更改 略 键说明ctrl+D复制相临行 略 键说明*/?单个字符*多个字符 略 键说明F2 不用鼠标,点进光标所在单元格,或是在 地址栏输入单元格名,Enter再点F2 略 13 常用函数与公式 类型知识点说明备注 对应示例 号 小技巧批量输入 ctrl下选取单元格，输入内容，enter;或 是先复制，多个选中后再粘贴 小技巧多个数据个位变万位 在空格中输10000，复制，选中要调位数 的区域，选择性粘贴除（可同时选择数 值） 小技巧文本变数值 在一个单元格中输入1,复制,选中要变数 值的文本单元格,选择性粘贴乘以 小技巧表格要求无合并单元格、行列连续、有加列序号 如何显示0008,三种方 法，0008，改为文本 格式， =text(8,“0000“) 小技巧保护工作区 全选后在单元格格式里把上锁标记全去掉 ，然后选定目标区域后标记，再用工具栏 -选项-安全性，加密码保护 小技巧排序 三个以上的排序，先加辅助列 =A1文本：*/?,数： ）,按高级筛选（条件区：标题与条件全部选入 ） 小技巧 同时对一个工作 簿中多个表格进 行页面设置 在工作表的表名处选定多个表格或全部表格，再进 行设置，即对多个表格进行了设置。 小技巧自动保存文件 在EXCEL中如何使它象WORD一样的自动定时保存文件 ？ 点击“工具”“选项”“保存”设置自动保存 时间隔 小技巧定位 快速选定工作表中所有带有公式的单元格，F5键定 位条件定位公式即可 小技巧有效性 在几个人名间进行填写，有效性选序列，条件中人 名间用英文状态下的, 用工具选项预设序列 时也是用英文状态下的 “,“ 小技巧改写 键入文字会取代了原有文字，在状态栏内点击“改 写”（或直接按键盘上的insert） 15 常用函数与公式 类型知识点说明备注对应示例号 函数F4相对绝对单元格的转换锁定行列 函数Sqrt返回正平方根=SQRT(ABS(-9)例1 函数trim去除单元格内字符两边的空格 函数type字符类型,1数值，2文本 =type(E20) 函数text变换数字大小写=TEXT(A1,“DBNUM2“)例2 函数ABS绝对值=ABS(E20)例2 函数int取整=INT(E20)例2 16 常用函数与公式 类型知识点说明备注对应示例号 函数fixed =FIXED(2016.349,-1)表示四舍五入到小数点左 边第一位 =FIXED(2016.349,-1,true)表示返回的结果是真 实值。“2020”无千分号 2,020例3 函数count count计算非空单元格个数（不计算文本） ,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) 17 常用函数与公式 类型知识点说明备注对应示例号 函数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/E 38)当E38为0返回的的空格 函数frequency 以一列垂直数组返回某个区域中数据的频率分布 。 =FREQUENCY(数据区域,划分标准) 函数fixed =FIXED(要四舍五入的数字,取整到小数点右边第 几位) 计算入司年份=FIXED(TODAY()- A16)/365,1)选中要筛选单元格文本颜色的 列，插入名称定义A，并在公式处输入 =get.cell(24,A1)再插入一新列，在第一个单 元格内=A 向下拖即可列出该列单元格的文本 颜色参数，再做筛选 20 导 读 常用函数与公式常用函数与公式 2 2 结结 语语 4 4 综合应用案例综合应用案例 3 3 前前 言言 1 1 21 综合应用案例 1、某业务部门业务员绩效得分考核销售收入、毛利两项 指标，请你用系统导出的该部门4月销售商品明细、客户 对应业务员名单、业务员所辖客户指标表三张表格来核 算业务员绩效得分。（20分钟） 4月销售商 品明细 客户对应业 务员名单 业务员所辖 客户指标表 业务员绩效排序与分数 22 综合应用案例 2、请运用本课所学，在EXCEL中完成如下电子月历（20分钟）： 要 求： 1）随电脑内置日历变换； 2）当月1号位于A2； 3）当天日期格式自动显示如图； 4）有“#月份”的自动显示； 5）其他格式如图设置。 23 综合应用案例 3、请运用本课所学，在EXCEL中制出如下一张“部门 工作进度表”（30分钟）： 要 求： 1）日期随月份变换自行变换； 2）紫色条显示当前任务； 3）填入每一事项的完成率即可自动生“执行 进度”（已完成、进行中、超前、滞后）； 4）后面自动生成甘特图（项目管理图）。 24 综合应用案例 自动判断执行状态 表中所有日期自动更新 自动用桔条显示当前任务 甘特图中蓝色块表示当 日对应工作项已完成 甘特图中灰色块表示当日对应工 作项未完成 25 导 读 常用函数与公式常用函数与公式 2 2 综合应用案例综合应用案例 3 3 结结 语语 4 4 前前 言言 1 1 26 结 语 函数的学习内容很多，一般来说需要三天的时间才能完成常用知识 学习。限于课堂时间有限，仅将相关常识与最常见的少数函数给大家作 了简要介绍，在后面的时间里面，我们还将对应用范围较广的IF（）、 VLOO