行政管理-用Excel表进行采购成本分析-关键知识点讲解1
关键知识点讲解1
(1).MIN函数
MIN函数是常用的数学函数，它返回一组值中的最小值。
函数语法
MIN(number1,number2,...)
number1, number2,...：是要从中找出最小值的 1 到 30 个数字参数。
函数说明
可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。
如果参数是数组或引用，那么函数MIN仅使用其中的数字，空白单元格、逻辑值、文本或错误值将被忽略。如果逻辑值和文本字符串不能忽略，则可使用MINA函数。
如果参数中不含数字，函数MIN则返回0。
函数简单示例
在Excel中输入以下公式，观察出现的结果。
|
公 式 |
说明（结果） |
|
=MIN(10,7,9,6,3) |
返回5个正数中的最小值3（3） |
|
=MIN(-10,-9,-7,-6,-3) |
返回5个负数中的最小值-10（-10） |
(2).MATCH函数
MATCH函数是常用的查找函数之一，它返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。
函数语法
MATCH(lookup_value,lookup_array,match_type)
lookup_value：为需要在数据表中查找的数值。它可以为数值(数字、文本或逻辑值)或者对数字、文本或逻辑值的单元格引用。
lookup_array：可能包含所要查找的数值的连续单元格区域。lookup_array应为数组或数组引用。
match_type：为数字-1、0或1。match_type指明Microsoft Excel如何在lookup_array中查找lookup_value。
如果match_type为1，函数MATCH则查找小于或等于lookup_value的最大数值，此时lookup_array必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。
如果match_type为0，函数MATCH则查找等于lookup_value的第1个数值。lookup_array可以按任何顺序排列。
如果match_type为-1，函数MATCH则查找大于或等于lookup_value 的最小数值，此时lookup_array必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。
如果省略match_type，则假设其为1。
函数说明
函数MATCH返回lookup_array中目标值的位置，而不是数值本身。例如MATCH("b",{"a", "b","c"},0)返回2，即“b”在数组{"a","b","c"}中的相应位置。
查找文本值时，函数MATCH不区分大小写字母。
如果函数MATCH查找不成功，则返回错误值#N/A。
如果match_type为0且lookup_value为文本，lookup_value可以包含通配符星号 (*) 和问号(?)，星号可以匹配任何字符序列，问号可以匹配单个字符。
函数简单示例
在Excel中输入以下公式，观察出现的结果。
|
公 式 |
说明（结果） |
|
=MATCH(41,{41,35,56},0) |
41在数组中的位置（1） |
|
=MATCH(41,{41,35,56},1) |
小于或等于41，即35在数组中的位置（2） |
|
=MATCH(40,{41,35,56},-1) |
大于40的最小数值，即41在数组中的位置（1） |
|
=MATCH(57,{41,35,56},-1) |
数组中没有大于或等于57的数值，查找不成功，返回错误值（#N/A） |
(3).INDEX函数
INDEX函数也是常用的查找函数之一，它返回表或者区域中的值或对值的引用。INDEX函数有两种形式：数组形式和引用形式。
函数语法
A. 数组形式的函数语法
INDEX(array,row_num,column_num)
array：一个单元格区域或数组常量。
如果数组中只包含一行或一列，则可不使用相应的row_num或column_num参数。
如果数组中包含多个行和列，但只使用了row_num或column_num，INDEX将返回数组中整行或整列的数组。
row_num：用于选择要从中返回值的数组中的行。如果省略row_num，则需要使用column_ num。
column_num：用于选择要从中返回值的数组中的列。如果省略column_num，则需要使用row_num。
函数说明
如果同时使用了row_num和column_num参数，INDEX将返回row_num和column_num交叉处单元格中的值。
如果将row_num或column_num设置为0(零)，INDEX将分别返回整列或整行的值数组。要将返回的值用做数组，则要在行的水平单元格区域和列的垂直单元格区域以数组公式的形式输入INDEX函数。
row_num和column_num必须指向数组中的某一个单元格，否则INDEX函数返回错误值#REF!。
函数简单示例
在Excel中输入以下公式，观察出现的结果。
|
公 式 |
说明（结果） |
|
=INDEX({1,2,2;3,4,5},1,2) |
返回数组中第1行第2列的值（2） |
|
=INDEX({"A","B","C";1,2,3},0,0) |
返回数组中第1行第1列的数值（A） |
B. 引用形式的函数语法
INDEX(reference,row_num,column_num,area_num)
reference：对一个或多个单元格区域的引用。
如果要对引用输入一个非连续区域，则应使用括号将该引用括起来。
如果引用中的每个区域都只包含一行或一列，则可不使用相应的row_num或column_num参数。例如对于单行引用，可以使用INDEX(reference,,column_num)。
row_num：要从中返回引用的引用中的行编号。
column_num：要从中返回引用的引用中的列编号。
area_num：用于选择要从中返回row_num和column_num的交叉点的引用区域。选择或输入的第1个区域的编号是1，第2个区域的编号是2，依次类推。如果省略area_num，INDEX函数将使用区域 1。
函数说明
在reference和area_num选择了特定的区域后，row_num和column_num将选择一个特定的单元格：row_num 1是该区域中的第1行，column_num 1是该区域中的第1列，依次类推。INDEX函数返回的引用将是row_num和column_num的交叉点。
如果将row_num或column_num设置为0(零)，INDEX将分别返回整列或整行的引用。
row_num、column_num和area_num必须指向引用中的某一个单元格，否则INDEX函数返回错误值#REF!。如果省略了row_num和column_num，INDEX函数将返回由area_num指定的引用区域。
INDEX函数的结果是一个引用，在用于其他的公式时其解释也是如此。根据使用的公式，INDEX函数的返回值可以用做引用或值。
函数简单示例
在Excel中输入以下内容，然后用INDEX函数进行运算，观察出现的结果。
|
公 式 |
说明（结果） |
|
=INDEX(B2:D4,1,2) |
返回B2:D4单元格区域中第1行第2列的值（3） |
|
=INDEX((B2:C4,D2:D4),2,1,2) |
返回第2个单元格区域中第2行第1列的值（B） |
本例公式说明
B16单元格中的“最低采购成本”公式为：
=MIN(F2:F13)
函数返回F2:F13单元格区域中的最小值，即取得“最低采购成本”值。
E16单元格中的“采购批次”公式为：
=INDEX(A2:A13,MATCH(B16,F2:F13,0))
公式中的第2个参数使用了MATCH函数，其各个参数值指定函数在F2:F13单元格区域查询B16单元格中的“最低采购成本”，返回查找到的“总成本”的相对行号。
MATCH和INDEX函数经常一起使用来查找，INDEX函数利用MATCH函数查找到的相对行号，返回A2:A13单元格区域的相应值。
H16单元格中的“采购量”公式为：
=INDEX(B2:B13,MATCH(B16,F2:F13,0))
公式中的第2个参数MATCH(B16,F2:F13,0)在F2:F13单元格区域查询B16单元格中的“最低采购成本”，返回查找到的“总成本”的相对行号，然后用此值作为INDEX函数的第2个参数，返回B2:B13单元格区域的相应值。