EXCEL中常见函数应用与条件功能

1.函数：LARGE与SMALL。

LARGE

返回数据集里第 k 个最大值。使用此函数可以根据相对标准来选择数值。

语法 

LARGE(array,k)

Array：为需要从中选择第 k 个最大值的数组或数据区域。

K：为返回值在数组或数据单元格区域里的位置（从大到小排）。

说明 

1、如果数组为空，函数 LARGE 返回错误值 #NUM!。

2、如果 k < 0 或 k 大于数据点的数目，函数 LARGE 返回错误值#NUM!。 

3、如果区域中数据点的数目为 n，则函数 LARGE(array,1) 返回最大值，函数 LARGE(array,n)返回最小值。

SMALL

返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。

语法

SMALL(array,k)

Array：为需要找到第 k 个最小值的数组或数字型数据区域。

K：为返回的数据在数组或数据区域里的位置（从小到大）。

说明 

1、如果 array 为空，函数 SMALL 返回错误值 #NUM!。

2、如果 k < 0 或 k 超过了数据点数，函数 SMALL 返回错误值 #NUM!。

3、如果 n 为数组中的数据点数，则 SMALL(array,1) 等于最小数值，SMALL(array,n)等于最大数值。

实际应用的实例：求学生成绩中的前3名的成绩和后3名成绩。

文档说明：

A1：A100存放100位学生的成绩；在B1：B3中录入1，2，3；在C1中设定公式=LARGE(A1:A100,B1)（第一名的成绩），然后填充到C2和C3格；在D1中设定公式=SMALL(A1:A100,B1)（倒数第一名的成绩），然后填充到D2和D3格。

 

变通与扩展：

1、当寻找到成绩后可用Match函数寻找成绩所在的位置。

2、当找到位置后还可以用OFFSET偏移，找到其它相应的内容，如姓名。

2.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

假如,你要在表1A列里填个姓名,B列就自动查找表2里的这个姓名对应的身份证号(表2,是人事资料),可以这样.

 

 表1 表2

 A B A B C

姓名 身份证号 姓名 性别 身份证号

张三 123456789 张三 X 123456789

 

在表1的B2输入 

 

=VLookUP(A2,表2!A1:F100,3,0)

'在表的A1:F100范转内查找第一个姓名为张三的,再返回第三列的值."0"是精确查找,SORRY!还有其他参数请自己查帮助吧,这里只我怎么用而已.^-^

 

LOOKUP  

从单行或单列区域或者从一个数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）返回值。LOOKUP函数具有两种语法形式：

 

（向量  LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。

当要查询的值列表较大或者值可能会随时间而改变时，使用该向量形式。

 

数组  LOOKUP的数组形式在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值。

当要查询的值列表较小或者值在一段时间内保持不变时，使用该数组形式。

 

 

 注释  对于详细的测试或七种以上条件的测试，还可以使用 LOOKUP 函数作为 IF 函数的替代函数。请参阅数组形式的示例。）

 

1. 向量形式

 

向量是只含一行或一列的区域。LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹配的值时，请使用LOOKUP 函数的这种形式。LOOKUP 的另一种形式自动在第一行或第一列中查找。

 

LOOKUP(lookup_value,lookup_vector,result_vector)

 

Lookup_value  LOOKUP 在第一个向量中搜索的值。Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。

 

Lookup_vector  只包含一行或一列的区域。lookup_vector中的值可以是文本、数字或逻辑值。

 

 要点  lookup_vector 中的值必须以升序顺序放置：...,-2, -1, 0, 1, 2, ...；A-Z；FALSE,TRUE。否则，LOOKUP 可能无法提供正确的值。大写文本和小写文本是等同的。

 

Result_vector  只包含一行或一列的区域。它必须与 lookup_vector大小相同。

 

评论

 

如果 LOOKUP 找不到 lookup_value，则它与 lookup_vector 中小于或等于 lookup_value的最大值匹配。

如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会提供 #N/A错误值。

示例

 

如果将示例复制到一个空白工作表中，示例将更易于理解。

 

 方法

 

创建一个空白工作簿或工作表。

在“帮助”主题中选择示例。请不要选择行或列标题。

 

 

从“帮助”中选择示例

按 Ctrl+C。

在工作表中，选择单元格 A1，然后按 Ctrl+V。

要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或者在“工具”菜单上，指向“公式审核”，然后单击“公式审核模式”。

  A            B

1 频率         颜色

2  4.14         红色

3  4.19         橙色

4  5.17         黄色

5  5.77         绿色

6  6.39         蓝色

公式 说明（结果）

=LOOKUP(4.19,A2:A6,B2:B6) 在列 A 中查找 4.19，然后返回列 B 中同一行内的值（橙色）

=LOOKUP(5.00,A2:A6,B2:B6) 在列 A 中查找 5.00，与接近它的最小值 (4.19) 匹配，然后返回列B 中同一行内的值（橙色）

=LOOKUP(7.66,A2:A6,B2:B6) 在列 A 中查找 7.66，与接近它的最小值 (6.39) 匹配，然后返回列B 中同一行内的值（蓝色）

=LOOKUP(0,A2:A6,B2:B6) 在列 A 中查找 0，并返回错误，因为 0 小于 lookup_vectorA2:A7 中的最小值 (#N/A)

 

 

 数组形式

 

LOOKUP 的数组形式在数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。当要匹配的值位于数组的第一行或第一列中时，使用LOOKUP 的这种形式。当要指定列或行的位置时，使用 LOOKUP 的另一种形式。

 

提示

 

一般而言，最好使用 HLOOKUP 或 VLOOKUP 函数而不是 LOOKUP 的数组形式。LOOKUP的这种形式是为了与其他电子表格程序兼容而提供的。

 

LOOKUP(lookup_value,array)

 

Lookup_value  LOOKUP 在数组中搜索的值。Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。

 

如果 LOOKUP 找不到 lookup_value，它会使用数组中小于或等于 lookup_value 的最大值。

 

如果 lookup_value 小于第一行或第一列中的最小值（取决于数组维度），LOOKUP 会返回 #N/A 错误值。

 

数组  包含要与 lookup_value 进行比较的文本、数字或逻辑值的单元格区域。

 

LOOKUP 的数组形式与 HLOOKUP 和 VLOOKUP 函数非常相似。区别在于：HLOOKUP 在第一行中搜索lookup_value，VLOOKUP 在第一列中搜索，而 LOOKUP 根据数组维度进行搜索。

 

如果数组包含宽度比高度大的区域（列数多于行数），LOOKUP 会在第一行中搜索 lookup_value。

 

如果数组是正方的或者高度大于宽度（行数多于列数），则 LOOKUP 在第一列中进行搜索。

 

使用 HLOOKUP 和 VLOOKUP，可以通过索引以向下或遍历的方式搜索，但是 LOOKUP始终选择行或列中的最后一个值。

 

 要点  数组中的值必须以升序顺序放置：...,-2, -1, 0, 1, 2, ...；A-Z；FALSE, TRUE。否则，LOOKUP无法提供正确的值。大写文本和小写文本是等同的。

 

示例 1

如果将示例复制到一个空白工作表中，示例将更易于理解。

 

 方法

 

创建一个空白工作簿或工作表。

在“帮助”主题中选择示例。请不要选择行或列标题。

 

 

从“帮助”中选择示例

按 Ctrl+C。

在工作表中，选择单元格 A1，然后按 Ctrl+V。

要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或者在“工具”菜单上，指向“公式审核”，然后单击“公式审核模式”。

  

1

 A B

公式 说明（结果）

=LOOKUP("C",{"a","b","c","d";1,2,3,4})在数组的第一行中查找“C”，查找小于或等于它（“c”）的最大值，然后返回最后一行中同一列内的值 (3)

=LOOKUP("bump",{"a",1;"b",2;"c",3})在数组的第一行中查找“bump”，查找小于或等于它（“b”）的最大值，然后返回最后一列中同一行内的值 (2)

 

 

示例 2

如果将示例复制到一个空白工作表中，示例将更易于理解。

 

 方法

 

创建一个空白工作簿或工作表。

在“帮助”主题中选择示例。请不要选择行或列标题。

 

 

从“帮助”中选择示例

按 Ctrl+C。

在工作表中，选择单元格 A1，然后按 Ctrl+V。

要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或者在“工具”菜单上，指向“公式审核”，然后单击“公式审核模式”。

下面的示例使用一个数字数组为测试分数指定字母等级。

 

   A

1 分数

2  45

3  90

4  78

公式 说明（结果）

=LOOKUP(A2,{0,60,70,80,90},{"F","D","C","B","A"}) 在数组的第一行中的 A2 (45) 中查找值，查找小于或等于它 (0) 的最大值，然后返回数组最后一行中同一列内的值 (F)

=LOOKUP(A3,{0,60,70,80,90},{"F","D","C","B","A"}) 在数组的第一行中的 A3 (90) 中查找值，查找小于或等于它 (90) 的最大值，然后返回数组最后一行中同一列内的值 (A)

=LOOKUP(A4,{0,60,70,80,90},{"F","D","C","B","A"}) 在数组的第一行中的 A4 (78) 中查找值，查找小于或等于它 (80) 的最大值，然后返回数组最后一行中同一列内的值 (C)

=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})在数组的第一行中的 A2 (45) 中查找值，查找小于或等于它 (60) 的最大值，然后返回数组最后一行中同一列内的值 (F)

=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})在数组的第一行中的 A3 (90) 中查找值，查找小于或等于它 (90) 的最大值，然后返回最后一行中同一列内的值 (A-)

=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})在数组的第一行中的 A4 (78) 中查找值，查找小于或等于它 (80) 的最大值，然后返回最后一行中同一列内的值 (C+)

注意:VLookUP要求查找的目标必须在查找范围的第一列(那么这个例子表2的A列就是要查的姓名的列,如果B列才是姓名,那就要改成B1:F100了),返回值也是以设定范围内第一列为1,所以本例的身份证号是3列.还得注意的就是要返回的内容必须在查找范围的右边,按本例来说,就是身份证号不能在姓名的前面了,这函数是有点局限性,^O^,但我还是觉得不错的.

　 

 

一、条件求和。

 

　　1、单条件求和：统计C1公司施工的工程总建筑面积，并将结果放在E18单元格中，我们只要在E18单元格中输入公式“=SUMIF（D2：D17,"C1公司",E2：E17）”即完成这一统计。

 

　　友情提醒：如果对EXCEL的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。

 

　　2、多条件求和：统计C2公司施工的质量等级为“合格”的工程总建筑面积，并将结果放在E19单元格中，我们用“条件求和”功能来实现：

 

　　①选“工具→向导→条件求和”命令（若没有此命令选项，可以用“加载宏”的方式来增加这一命令选项），在弹出的对话框中，按右下带“―”号的按钮（此时对话框变成类似工具条的窗口形式浮于桌面上），用鼠标选定D1：I17区域，并按窗口右边带红色箭头的按钮（恢复对话框状态）。

 

　　②按“下一步”，在弹出的对话框中，按“求和列”右边的下拉按钮选中“建筑面积”项，再分别按“条件列、运算符、比较值”右边的下拉按钮，依次选中“施工单位”、“=”（默认）、“C2公司”选项，最后按“添加条件”按钮。重复前述操作，将“条件列、运算符、比较值”设置为“质量等级”、“=”、“合格”，并按“添加条件”按钮。

 

　　③两次点击“下一步”，在弹出的对话框中，按右下带“―”号的按钮，用鼠标选定E19单元格，并按窗口右边带红色箭头的按钮。

 

　　④按“完成”按钮，此时符合条件的汇总结果将自动、准确地显示在 E19单元格中。

 

　　友情提醒：上述操作实际上是输入了一个数组公式，我们也可以先在E19单元格中直接输入公式：=SUM(IF(D2:D17="C2公司",IF(I2:I17="合格",E2:E17)))，然后在按住Ctrl+Shift键（非常关键！！！）的同时按下Enter键，也可以达到上述多条件求和之目的。

 

　　二、条件计数。

 

　　统计质量等级为“合格”工程的数目，并将结果存放在I18单元格中，在I18单元格中输入公式：=COUNTIF(I2:I17,"合格")，当按下确定按钮后，统计结果――数字5即自动在I18单元格中显示出来。

 

　　三、条件格式。

 

　　将工程造价在500万元（含500万元）以上的工程造价数值以红颜色显示出来：

 

　　①选中F2至F17单元格；

 

　　②用“格式→条件格式”命令，打开“条件格式”对话框（如图 2）；

 

 

 

　　③按第二个方框旁的下拉按钮，选中“大于或等于”选项，再在后面的方框中输入数字500；

 

　　④按上述对话框中的“格式”按钮，打开“单元格格式”对话框（如图3），在“文字”卡片下，按“颜色”旁的下拉按钮，将文字颜色设置成红色后，按“确定”按钮关闭“单元格格式”对话框，回到“条件格式”对话框中；

 

 

 

　　⑤按“确定”按钮。

 

　　此时造价大于500万元的工程，其造价数值将以红色显示在单元格中。

 

　　友情提醒：继续按上述“条件格式”对话框中的“添加”按钮，可以设置多级“条件”，如“工程造价大于500万元以红色显示，大于1000万元以蓝色显示”等（可以对一个单元格设置三个条件）。

 

　　四、条件函数。

 

　　我们在对竣工工程观感质量进行评定后，当观感得分率超过85%，该工程质量等级可以评定为“优良”。

 

　　①在I2单元格中输入公式：=IF(H2>=85,"优良",IF(H2>0,"合格","未竣工"))；

 

　　②选中I2单元格，将鼠标移至该单元格右下角成细十字线时（我们称之为“填充柄”），按住左键向下拖拉，将上公式复制到I列下面的单元格中；

 

　　③以后当工程竣工后，我们将该工程的观感得分率填入H列内相应的单元格中，则质量等级自动显示在相应工程的I列单元格中（若没有填观感得分率，则I列相应单元格中显示“未竣工”字样）。

 

五、条件查找。

 

　　当工程基本情况表中登记的工程非常之多时，如果我们用普通浏览的方式查找某个具体的工程是非常困难的，此时我们可以用“查找”功能来快速定位：

 

　　① 用“编辑查找”命令，打开“查找”对话框

　　②在“查找内容”下面的方框中输入该工程的关键词；

 

　　③对“搜索方式、搜索范围”作适当设置后，连续按“查找下一个”按钮，符合模糊条件的单元格依次被选中，即可快速查找到某个具体的工程。

 

　　友情提醒：在“查找内容”下面的方框中输入的内容不一定非得与某一单元格数值完成相同的文本，如查找“C1公司”的某个工程，只要输入“C1”即可以进行模糊查找了。

 

　　六、条件筛选。

 

　　如果要打印C1公司施工的工程基本情况一览表，我们可以用“自动筛选”功能来实现：

 

　　①选中表格中的任一单元格，用“数据→筛选→自动筛选”命令，启动“自动筛选”功能（列标题旁出现一个下拉按钮，如图5）；

 

　　②按“施工单位”旁的下拉按钮，选中C1公司，则表格中只显示C1公司所施工的工程；

 

③接上打印机，装上打印纸，按“打印”按钮，即可打印出C1公司施工的工程基本情况一览表　　

④打印结束后，重复第①的操作关闭“自动筛选”功能，则全部工程又重新显示在表格中。

 

　　友情提醒：此处的“条件筛选”可以进行“多级”筛选，如再上述筛选的基础上，再按“进度”旁的下拉按钮，选中“在建”选项，则表格中只剩下“C1公司”施工的所有“在建”工程。