Excel提高篇-函数

Excel提高篇--函数
主要内容
一、信息类 二、查找与引用 三、数学计算 四、统计计算 五、数组函数
2
函数
函数是构建Excel模式的核心工具。要能够将Excel发 挥到淋漓尽致，以充分利用Excel解决问题，恰当的利用 函数是不可或缺的。
3
一、信息类
IS() IS类函数是用来检验数值、字符或引用类型的工作表函数。
7
一、信息类
CELL() 格式： CELL(信息类型,引用)
功能：返回某一引用区域的左上角单元格的格式、位置或 内容等信息。
例如：取工作表的全名。 CELL("filename") 将返回工作簿名称和工作表名称： H:\Excel\[信息函数.xls]IS函数
8
二、查找与引用
ROW() 格式：ROW(引用)
功能：返回“引用”的行号。
如果“引用” 为一个单元格区域，则函数 ROW 将 “引用”的行号以垂直数组的形式返回。 例如：=ROW(B3) 返回：3
=ROW(2:6)
返回：{2;3;4;5;6} （数组）
=ROW(A2:A6) 返回：{2;3;4;5;6} （数组）
9
二、查找与引用
COLUMN() 格式： COLUMN(引用)
37
二、查找与引用
FIND() 与 SEARCH() 例如：查找包含数字2的单元格个数。 统计“查找包含数字2的单元格”的个数： {=COUNT(FIND(2,A1:A5))} 查找包含数字2的单元格： FIND(2,A1:A5)
涉及到区域，应该使用 数组！
38
二、查找与引用
CHOOSE() 格式：CHOOSE(索引值,数值,数值,„,数值)
18
二、查找与引用
VLOOKUP()， HLOOKUP() 例如：依据“奖励 表”的信息完成 “奖励”字段的填 写。 为了避免“0”的 出现，公式中添加 &""
=VLOOKUP(D3,奖励表!A$3:B$32,2,0)&""
19
二、查找与引用
VLOOKUP()， HLOOKUP() 例如：依据“奖励 表”的信息完成 “奖励”字段的填 写。
11
二、查找与引用
INDEX() 格式： INDEX(引用查找范围,行数,列数,区域)
（引用型）
INDEX(数组查找范围,行数,列数) （数组型）
功能：返回表或区域中的值或对值的引用。
12
二、查找与引用
VLOOKUP()， HLOOKUP() 格式：VLOOKUP(查找值，查找数组，指定列，选项)
10
二、查找与引用
OFFSET() 格式： OFFSET(引用,行距,列距,行数,列数)
功能：以指定的引用为参照系，通过给定偏移量得到新的 引用。
返回的引用可以为一个单元格或单元格区域。并可 以指定返回的行数或列数。 例如：在A1中输入公式 =SUM(OFFSET(A2,1,2,5,3)) 得到的结果是区域$C$3:$E$7 的和。
=IF(ISNA(MATCH(E3,C2:C167,0)),"未选","已选")
24
二、查找与引用
FIND() 与 SEARCH() 格式：FIND(查找字符串,原始字符串,起始位置)
功能：返回从“起始位置”开始“查找字符串”在“原始 字符串”中的位置。
如果“起始位置”缺省，那么从 1 开始。 例如：
50
三、数学计算
RANDBETWEEN() 再例如，设计一个算术考试 自动判分表。 考虑到随机函数是每次运 算都改变，因此我们需要有一 个过度。 我们可以在L列选10个单 元格，分别产生[10，49]区间 的随机整数。 =RANDBETWEEN(10,49)
51
三、数学计算
RANDBETWEEN() 再例如，设计一个算术考试 自动判分表。 选中L列的10个随机整数， 单击“复制”命令。 选择B3:B12区域，打开 “选择性粘贴”对话框，选中 其中的“数值按钮”。单击 “确定”按钮关闭对话框。 D3:D12区域、 D13:D22 区域的操作类似B3:B12区域。
功能：返回一个[M,N]的均匀分布的随机整数。
注意：如果该函数不可 用，则应该通过 “工具”菜单下 的“加载宏”命 令，选中“分析 工具库。
47
三、数学计算
RANDBETWEEN() 例如，产生一个由大写英文字母组成的四位随机密 码。 大写英文字母的ASCII码是 65~90，因此我们可 以通过RANDBETWEEN()生成 65~90 的随机数，然后 使用CHAR函数将其转换为相应的字符。 =CHAR(RANDBETWEEN(65,90)) J W A G
=FIND("E","Excel应用技术")
=FIND("e","Excel应用技术") =FIND("技","Excel应用技术")
1 4
8
25
二、查找与引用
FIND() 与 SEARCH() 格式： SEARCH (查找字符串,原始字符串,起始位置)
功能：返回从“起始位置”开始“查找字符串”在“原始 字符串”中的位置。
如果“起始位置”缺省，那么从 1 开始。 例如：
= SEARCH("E","Excel应用技术")
= SEARCH("e","Excel应用技术") = SEARCH("技","Excel应用技术")
1 1
8
26
二、查找与引用
FIND() 与 SEARCH() SEARCH 支持通配符，而 FIND 不行； FIND 可以区分大小写，而 SEARCH 不行； 如果“查找字符串”没有出现在“原始字 符串”中，则返回“#VALUE!”
功能：返回“数值”序列中的第“索引值”的值。
例如： =CHOOSE(2,"ABC", "XYZ", "ASD") 返回：XYZ
39
二、查找与引用
CHOOSE() 例如：利用CHOOSE函数生成工资条。
40
二、查找与引用
CHOOSE() 例如：利用CHOOSE函数生成工资条。
快速填充！
=CHOOSE(MOD(ROW(工资表!A1),3)+1,"",工资表!A$1,OFFSET(工资表!A$1,(ROW(工资表!A1)+1)/3,0))
4
一、信息类
IS() 例如： 计算 A 列数据和 B 数据的 MOD
向下填充
=MOD(A2,B2)
5
一、信息类
IS() 例如： 计算 A 列数据和 B 数据的 MOD
出错了！
6
一、信息类
IS() 例如： 计算 A 列数据和 B 数据的 MOD
=IF(ISERROR(MOD(A2,B2)),"数据错误！",MOD(A2,B2))
“积分”项的 计算类似 “奖励”
=VLOOKUP(D3,奖励表!A$3:C$32,3)
20
二、查找与引用
MATCH() 格式：MATCH(查找值,查找范围,查找类型) 功能：返回在指定方式下与“查找值”在“查找范围”中 的匹配位置。 “查找类型”可以取0，-1，1 其中 0 表示查找第一个完全相同的值； -1 表示查找第一个大于或等于的值， “查找范围”必须降序排列； 1 表示查找第一个小于或等于的值， “查找范围”必须升序排列。 如果“查找值”没有出现在“查找范围”中，则 返回“#N/A”。

生成[0,N)的均匀分布的随机整数:
INT(RAND()*N)
生成[M,N)的均匀分布的随机整数: INT(RAND()*(N-M)+M) 生成[M,N]的均匀分布的随机整数: INT(RAND()*(N-M+1)+M)
46
三、数学计算
RANDBETWEEN() 格式：RANDBETWEEN(数值M,数值N)
HLOOKUP(查找值，查找数组，指定行，选项)
功能：返回“查找数组”中第一列（行）与“查找值”相 同的“指定列（行）”的值。
“选项”缺省、1、TRUE，模糊查找，要求有序；
0、FALSE，精确查找，支持无序。 如果“查找值”没有出现在“查找数组”中，则 返回“#N/A”。
13
二、查找与引用
VLOOKUP()， HLOOKUP() 例如：依据“奖励 表”的信息完成 “奖励”字段的填 写。
14
二、查找与引用
VLOOKUP()， HLOOKUP() 例如：依据“奖励 表”的信息完成 “奖励”字段的填 写。
向下填充 至G12
=VLOOKUP(D3,奖励表!A$3:B$8,2,0)
15
二、查找与引用
VLOOKUP()， HLOOKUP() 例如：依据“奖励 表”的信息完成 “奖励”字段的填 写。
算170！）。 如果输入的 N 不是整数，则截尾取整 。
44
三、数学计算
RAND() 格式：RAND()
功能：返回一个大于等于0,小于1的均匀分布的随机数。
注意：每当编辑工作表时便会重新计算一个新的数值。
45
三、数学计算
RAND() 格式：RAND()
功能：返回一个大于等于0,小于1的均匀分布的随机数。
32
二、查找与引用
FIND() 与 SEARCH() 再例如，从表中将“规格型号”中的中文名称过滤出来。
33
二、查找与引用
FIND() 与 SEARCH() 再例如，从表中将“规格型号”中的中文名称过滤出来。