2017年Excel培训基础教程(二、公式).pptx

2017年Excel培训基础教程 二 信息技术部 1公式与函数 在单元格建立公式相对参照位址与绝对参照位址常用公式介绍不需要公式也能得知计算结果 1 1在单元格建立公式 当我们需要将工作表中的数字数据做加 减 乘 除 等运算时 可以把计算的动作交给Excel的公式去做 省去自行运算的工夫 而且当数据有变动时 公式计算的结果还会立即更新 1 1 1公式的表示法 Excel的公式和一般数学公式差不多 数学公式的表示法为 若将这个公式改用Excel表示 则变成要在A3单元格中输入 意思是Excel会将Al单元格的值十A2单元格的值 然后把结果显示在A3单元格中 1 1 2输入公式 输入公式必须以等号 起首 例如 A1 A2 这样Excel才知道我们输入的是公式 而不是一般的文字数据 现在我们就来练习建立公式 我们已在其中输入了两个学生的成绩 我们打算在E2单元格存放 王书桓的各科总分 也就是要将 王书桓 的英文 生物 理化分数加总起来 放到E2单元格中 因此将E2单元格的公式设计为 B2十C2 D2 请选定要输入公式的E2单元格 并将指针移到数据编辑列中输入等号 函数方块 在此输入 接着输入 之后的公式 请在单元格B2上单击 Excel便会将B2输入到数据编辑列中 此时B2被虚线框包围住 B2自动输入到公式中 再输入 然后选取C2单元格 继续输入 选取D2单元格 如此公式的内容便输入完成了 最后按下数据编辑列上的翰入钮或按下键 公式计算的结果马上显示在E2单元格中 数据编辑列会显示公式 单元格显示公式计算的结果 你也可以直接在E2储存格中 以键盘直接输入 B2十C2十D2 再按下Enter键来输入公式 1 1 3自动更新结果 公式的计算结果会随着单元格内容的变动而自动更新 以上例来说 假设当公式建好以后 才发现 王书桓 的英文成绩打错了 应该是 90 分才对 当我们将单元格B2的值改成 90 E2单元格中的计算结果立即从220更新为225 自动更新计算结果了 1 2相对参照位址与绝对参照位址 1 2 1相对地址 单元格绝对地址 相对固定的单元格 公式中会运用到的地址有相对引用地址与绝对参照地址2种类型 相对引用地址的表示法例如 Bl C4 绝对参照地址的表示法 则须在单元格地址前面加上 符号 例如 B l C 4 1 2 2实例说明 底下我们以实例为你说明相对引用地址与绝对参照地址的使用方式 先选取D2单元格 在其中输入公式 B2十C2 并计算出结果 根据前面的说明 这是相对引用地址 以下我们要在D3单元格输入绝对参照地址的公式 B 3 C 2 选取D3单元格 然后在数据编辑列中输入 B3 可以直接在资料编辑列中输入 B 3 切换相对引用与绝对参照地址的快速键 键可循序切换单元格地址的参照类型 每按一次键 参照地址的类型就会改变 其切换结果如右 接着输入 C3 再按下F4键将C3变成 C 3 最后按下键 公式就建立完成了 D3的公式內容 D2及D3的公式分别是由相对地址与绝对地址组成 但两者的计算结果却一样 到底它们差别在哪里呢 选定D2 D3单元格 拉曳填满控点到下一栏 将公式复制到E2 E3储存格 计算结果不同了 相对地址公式 D2的公式 B2 C2 使用了相对位址 表示要计算D2往左找两个储存格 B2 C2 的总和 因此当公式复制到E2单元格后 便改成从E2往左找两个储存格相加 结果就变成C2和D2相加的结果 绝对地址公式 D3的公式 B 3十 C 3 使用了绝对位址 因此不管公式复制到哪里 Excel都是找出B3和C3的值来相加 所以D3和E3的结果都是一样的 1 2 3混合参照 如果在公式中同时使用相对引用与绝对参照 这种情形称为混合参照 例如 这种公式在复制后 绝对参照的部份 如 Bl的 B 不会变动 而相对引用的部份则会随情况做调整 绝对参照 相对参照 绝对参照 相对参照 请依照下列步骤将E3单元格中的公式改成混合参照公式 B3 C3 双按E3单元格 将插入点移至 之后 接着按两次F4键 让 B 3变成 B3 将插入点移至 之后 按3次F4键将 C 3变成C3 最后按下enter键 公式便输入完成 接着选定F3 分别拉曳填满控点至F3及E4 E4单元格 F3单元格 B4单元格为0 C4单元格也为0 B3维持1215 D3为2180 1 3常用公式介绍 1 3 1函数的格式 函数是Excel根据各种需要 预先设计好的运算公式 可让你节省自行设计公式的时间 底下我们就来看看如何运用Excel的函数 每个函数都包含三个部份 函数名 自变量和小括号 函数名 自变量 1 3 2使用函数方块输入函数函数也是公式的一种 所以输入函数时 也必须以等号 起首 1 3 3常用函数公式 1 文本连接 2 逻辑函数 if and or not 3 常用统计函数 Max Min large Small Count counta countblank Countif countifs 4 常用数学函数 Sum Average Sumif Sumifs Product Sumproduct rand Int round 5 日期和时间函数 day month year hour minute second Today Now Workday networkdays 6 其他常用函数 Vlookup Index 将多列或多内容合并 1 文本连接 如 将邮箱名和邮箱后缀 合并为邮箱全称公式为 A2 B2注 字符串内容需要加英文双引号 条件函数if逻辑与函数and 逻辑或函数or 逻辑非函数not 返回值为true falseIf and 符合条件返回值 不符合条件返回值 If or 符合条件返回值 不符合条件返回值 If not 符合条件返回值 不符合条件返回值 注意 返回值若为字符串 则需要加英文双引号 2 条件与逻辑函数 if and or not Max Min取最大最小 Large Small取所选区域中第K个最大值 最小值 Countif countifs为满足一个或多个条件的单元格数量 countif 选定区域 条件 countifs 选定区域 条件 选定区域 条件 3 常用统计函数 Max Min large Small Count counta countblank Countif countifs Sum求和 Sumif条件求和 Average平均数Product连乘 SumProduct对应列相乘再求和Rand随机数 Int取整 Round四舍五入Rand b a around number k 4 常用数学函数 Sum Average Sumif Sumifs Product Sumproduct rand Int round 变化的日期和时间工作日日期加减 5 日期和时间函数 day month year hour minute second Today Now workday networkday Vlookup lookup value table array col index num range lookup vlookup 需要比较值 被比较区域 返回被比较区域的第几列 行 精确查找 模糊查找 取矩阵交叉项 Index 区域 行 列 7 其他常用函数 Vlookup Index Vlookup时一般用绝对地址 若按下此处 可取得函数的进阶说明 TIP 函数方块列示窗只会显示最近用过的10个函数 若在函数方块列示窗中找不到想要的函数 可选取其他函数项目开启插入函数交谈窗来寻找欲使用的函数 稍后说明 这里会描述此函数的功能 再来就是要设定函数的自变量 先按下第一个自变量栏Number1右侧的折迭钮 将函数自变量交谈窗收起来 再从工作表中选取B4 B6当作自变量 按 下自变量栏右侧的展开钮 再度将函数自变量交谈窗展开 选取B4 B6当作自变量 TIP 除了从工作表中选取单元格来设定自变量 你也可以直接在自变量栏中输入自变量 省下折迭 展开函数引 数交谈窗的痲烦 选取的范围会被虚线框围住 函数自变量交谈窗目前被折迭起来 这里会显示计算的结果 按下确定钮 函数的计算结果就会显示在B8单元格内 刚才输入的函数及公式 计算结果 1 3 4用自动显示的函数列表输入函数若已经知道要使用哪一个函数 或是函数的名称很长 我们还有更方便的输入方法 请直接在单元格内输入 再输入函数的第1个字母 例如 S 单元格下方就会列出S开头的函数 如果还没出现要用的函数 再继续输入第2个字母 例如 U 出现要用的函数后 用鼠标双按函数就会自动输入单元格了 移动箭头键到函数上 会显示函数的说明 双按函数可自动输入单元格 TIP 若一直没有出现函数列表 请切换到文件页次再按下选项钮 然后切换到公式页次 确认已勾选公式自动完成选项 1 3 5利用 自动求和 钮快速输入函数 在开始页次编辑区有一个自动加按钮 可让我们快速输入函数 例如当我们选取B8单元格 并按下钮时 便会自动插入SUM函数 且连自变量都自动帮我们设定好了 只要按下enter键 就可以算出班费的总支出了 会自勤选取好函数引数 你也可以自行重新选取其他范围 这里会有函数的输入格式提示 除了加总功能之外 还提供数种常用的函数供我们选择使用 只要按下钮旁边的下拉钮 即可选择要进行的计算 可用来做这些运算 若选此项 会开启插入函敷交谈窗 1 3 6开启 插入函数 交谈窗输入需要的函数 插入函数交谈窗是Excel函数的大本营 当你在函数方块列示窗中找不到需要的函数时 就可从这里来输入函数 现在我们要练习透过插入函数交谈窗来输入函数 列出面包店各家门市的营业额排名 请选取C4单元格 然后按下数据编辑列上的插入函数钮 你会发现数据编辑列自动输入等号 并且开殷插入函数交谈窗 可从这里选择函数的类别 如财务 统计 文字 日期及时间 等 列出Excel所提供的函数 按下此处可显示目前所选取函数的使用说明 函数的功能叙述 1 4不需输入公式也能得知计算结果 这一节要介绍一个超实用的自动计算功能 让你不需输入任何公式或函数的情况下 也能快速得到运算结果 其可计算的项目包括求和 平均值 最大