Office 高级应用之Excel公式与函数

AOA
高级办公自动化
逻辑函数
IF AND OR
AOA
高级办公自动化
IF函数
IF（logical_test，value_if_true， value_if_false） • logical_test：逻辑条件 • value_if_true：当值为“真”时的返回值 • value_if_false：当值为“假”时的返回值 注意：在IF函数中三个参数中可以 value_if_true或value_if_false，但不能同 时省略 例：2（2）、10（1）
• number1、number2、……：从中找 出最大值或最小值的1～30个数字参 数
AOA
高级办公自动化
COUNT函数
功能：用于返回数字参数的个数，即统计单 元格区域中含有数值类型的单元格个数 格式：COUNT(value1，value2，……) • value1，value2，……表示包含或引用各 类型数据的参数 注意： 1、函数可以最多附带上1～30个参数 2、只有数字类型的数据才能被统计
AOA
高级办公自动化
查找和引用函数
• VLOOKUP函数
• 功能：从一个数组或表格的最左列中查找含有特定值 的字段，并返回表格数组当前行中其他列的值。 V 表 示垂直方向。
• HLOOKUP函数
AOA
高级办公自动化
VLOOKUP函数
功能：从一个数组或表格的最左列中查找含有特定 值的字段，再返回同一列中某一指定单元格中的值 格式：
AOA
高级办公自动化
REPLACE函数
功能：将某几位的文字以新的字符串替换。其替 换功能与SUBSTITUTE函数大致类似 格式：REPLACE（old_text，start_num，
num_chars，new_text）
• old_text：原始的文本数据 • start_num：设置要从old_text的第几个字符位置开始替换 • num_chars：设置共有多少字符要被替换
它可以产生一个以上的结果数组公式的创建数组公式数组公式aoa高级办公自动化数组公式创建数组公式创建3按下shiftctrlentershiftctrlenter102等aoa高级办公自动化数据筛选数据筛选数据筛选是一种用于查找数据的快速方法筛选将数据列表中所有不满足条件的记录暂时隐藏起来只显示满足条件的数据行以供用户浏览和分析
COUNTIF(D2:D5,”男”) 功能：统计D2:D5区域中男性人数； COUNTIF（C2:C12, “<60”) 功能：统计C2:C12单元格区域中成绩不及格的 人数 见练习题1（4）
AOA
高级办公自动化
RANK
返回一个数字在数字列表中的排位。 格式：RANK (number，ref，order) Number：为需要找到排位的数字。 Ref ：为数字列表数组或对数字列表的引用。 Order：为０（零）或省略，降序；不为零， 升序。 例：见题1（2）
本格式的，请给参数加上英文状态下的双引号 使用“&”符连接也能达到相同的效果
例：18（2）
AOA
高级办公自动化
SUBSTITUTE函数
功能：实现替换文本字符串中的某个特定字符串
格式：SUBSTITUTE（text，old_text，
new_text，instance_num）
• • • • text：原始内容或是单元格地址 old_text：要被替换的字符串 new_text：替换old_text的新字符串 如果字符串中含有多组相同的old_text时，可以使用参数instance_num来 指定要被替换的字符串是文本字符串中的第几组。如果没有指定 instance_num的值，默认的情况下，文本中的每一组old_text都会被替换 为new_text。
注意：该函数只对参数的数值求平均数， 如区域引用中包含了非数值的数据，则 AVERAGE不把它包含在内。 1(3)
AOA
高级办公自动化
MAX、MIN
MAX函数：求参数列表中对应数字的最大 值; MIN函数：求参数列表中对应数字的最小 值; 格式：MAX（number1，number2，……） MIN（number1，number2，……）
• new_text：要用来替换的新字符串
例 4（2）、8（1）
AOA
高级办公自动化
信息函数
• IsText 检测一个值是否为文本
AOA
高级办公自动化
日期和时间函数
• • • • • • • •
Year(date) 4（1） Month(date) Day(date) Now() 4（1） Hour Minute Second …
HLOOKUP（lookup_value，table_array， row_index_num，range_lookup）
• look_value：要在表格第一行中搜索的值
• table_array：要搜索的数据表格、数组或数据库 • row_index_num：要返回的值位于table_array列中第几行
AOA
高级办公自动化
CONCATENATE函数
功能：将多个字符文本或单元格中的数据连接在 一起，显示在一个单元格中
格式：CONCATENATE(text1，text2，……)
• text1，text2，……：需要连接的字符文本或引用的 单元格
注意：1、该函数最多可以附带30个参数
2、如果其中的参数不是引用的单元格，且为文
高级办公自动化
数据库函数
数据库分析函数
• DAVERAGE函数：计算列表或数据库的列中满足指定 条件的数值的平均值 • DMAX函数：返回列表或数据库的列中满足条件的最大 值 • DPRODUCT函数：返回列表或数据库中满足指定条件 的记录字段（列）中数值的乘积
• DSUM函数是用来返回列表或数据库中满足指定条件的 记录字段（列）中的数字之和
AOA
高级办公自动化
EXACT函数
功能：用来比较两个文本字符串是否相同。如果 两个字符串相同，则返回“TRUE”，反之，则返 回“FALSE” 格式：EXACT（text1，text2） • text1 和 text2：两个要比较的文本字符串 注意：1、在字符串中如果有多余的空格，会被视 为不同 2、EXACT函数在判别字符串的时候，会 区分英文的大小写，但不考虑格式设置的差异
Microsoft Office——Excel2003
公式——函数
j
AOA
高级办公自动化
公式——函数
函数是一些预先定义的内置公式，例如：
1、SUM 求和
2、AVERAGE
3、MAX 4、MIN
求平均值
求最大值 求最小值
5、COUNTIF：统计给定区域内满足特定条件的个数 6、RANK 排位 7、IF 8、COUNT 9、ROUND
• 数据库信息函数：直接获取数据库中的信息 • 数据库分析函数：分析数据库的数据信息
数据库函数格式为：
函数名称（database，field，criteria）
• database：构成数据清单或数据库的单元格区域 • field：指定函数所使用的数据列
• criteria：一组包含给定条件的单元格区域
AOA
高级办公自动化
类似函数： 1、COUNTA函数：返回参数组中非空值的数目， 即计算数组或单元格区域中数据项的个数； 2、COUNTBLANK函数：计算某个单元格区域中 空白单元格的数目； 3、COUNTIF函数：计算区域中满足给定条件的 单元格的个数。
AOA
高级办公自动化
例子
•
range_lookup：一个逻辑值，如果其值为“TRUE”或被省略，则返回部分符 合的数值；如果该值为“FALSE”时，函数只会查找完全符合的数值，如果找 不到，则返回错误值“＃N/A”。
例 9（ 1）
AOA
高级办公自动化
数据库函数
数据库函数是用于对存储在数据清单或数据库中 的数据进行分析，判断其是否符合特定的条件。 根据各自函数所具有的功能不同，可分为两大类：
AOA
高级办公自动化
单元格引用
(3) 混合引用
形式：$A1、A$1 特点：复制时，不加$的行（列）号发生变化， 加$的行（列）号保持不变。
AOA
高级办公自动化
数学函数
SUM SUMIF Round
AOA
高级办公M（number1， number2，……） 功能：返回指定参数所对应的数值之和
AOA
高级办公自动化
函数输入
1、直接输入函数
AOA
高级办公自动化
函数输入
2、使用“公式选项板”输入函数
AOA
高级办公自动化
函数输入
3、利用“粘贴函数”功能
“插入”—“函数”
AOA
高级办公自动化
函数形式
函数
函数的形式：函数名（参数1，参数2，……） 参数：常量、单元格引用、区域、其他函数 例子：=sum(a1:d1) ； =average(a1:a4，b5)
见题1（3）、2（2）
AOA
高级办公自动化
文本函数
• Exact：比较两个文本字符串是否相同 • Concatenate：将多个字符文或单元格中的数据连接在一 起
• Substitute：将字符串中的部分字符串以新 字符串替代 • Replace：将某几位的文字以新的字符串替 换。 • MID/LEFT/RIGHT
• 例5（2）
AOA
高级办公自动化
财务函数
•
PMT 计算贷款按年、按月的偿还金额 PMT(rate,nper,pv,fv,type)
rate:贷款利率 nper:总贷款期限 pv:贷款开始计算时已经入账的款项或一系列未来付款 当前值的累积和（贷款金额） fv:未来值或在最后一次付款后希望得到的现金金额 type：逻辑值，用以指定付款时间是在期初还是期末， 1表示期初，0表示期末，默认为0
AOA
高级办公自动化
统计函数
• • • • •
Average Max/Min COUNT COUNTIF RANK
AOA
高级办公自动化
AVERAGE函数
功能：返回指定参数所对应数值的算术平 均数 格式：AVERAGE（number1， number2，……）
• number1，number2等是指定所要进行求平均值的参数
AOA
高级办公自动化
数据库函数
数据库信息函数
•
DCOUNT函数 ：返回列表或数据库中满足指定条件的记录字段（列） 中包含数值的单元格的个数
• DGET函数：从列表或数据库的列中提取符合指定条件的单个值 • DCOUNTA函数：返回列表或数据库中满足指定条件的记录字段（列） 中非空单元格的个数
AOA
AOA
高级办公自动化
• 计算：属性值超过 160,000 的佣金的和 (63,000)
另：见题2（4）
AOA
高级办公自动化
• 计算性别是“男”的英语总分 • 等效于：SUMIF(B2:B5, “男”,C2:C5)
AOA
高级办公自动化
ROUND函数
格式： ROUND （number，nun_digits） 功能：按指定的位数对数值进行四舍五入
VLOOKUP（lookup_value，talbe_array， col_index_num，range_lookup）
• look_value：要在数组中搜索的数据，它可以是数值、引用地址或文字字符 串 • table_array：要搜索的数据表格、数组或数据库
• col_index_num：一个数字，代表要返回的值位于table_array中的第几列。
例如：SUM(A1:A3)、SUM(B2:B4,C5)、 SUM(23,45,88 )
AOA
高级办公自动化
SUMIF函数
SUMIF函数：用于计算符合指定条件的单 元格区域内的数值进行求和 格式为：SUMIF（range，criteria，
sum_range）
• range 是条件判断的单元格区域； • criteria 是指定条件表达式； • sum_range 是需要计算的数值所在的单元格区 域
AOA
高级办公自动化
单元格引用
在公式或函数中使用单元格称为单元格的引用。 (1) 相对引用 形式： A1、A1:D4 特点：把公式复制到别处时，被引用的单元格地 址将相应变化。如何变化？
AOA
高级办公自动化
单元格引用
(2) 绝对引用 形式：$A$1、$A$1:$D$4
特点：把公式复制到别处时，被引用的单元格地 址保持不变。
• range_lookup：一个逻辑值，如果其值为“TRUE”或被省略，则返回部分符 合的数值；如果该值为“FALSE”时，函数只会查找完全符合的数值，如果找 不到，则返回错误值“＃N/A”。
例 2（ 1）
AOA
高级办公自动化
HLOOKUP函数
功能：可以用来查询表格的第一行的数据
格式：