EXCEL常用函数

1、vlookup函数。

我们在日常的工作中，经常会遇到两张不同的工作表，各包含一部分你所需要的信息，如果要获得完整的信息就需要将两张工作表连接起来，一般来说，是用工号这个字段进行匹配的，以前我们的approach中做过很多类似的操作。excel也可以通过函数来实现，操作上一点也不复杂。这个函数就是vlookup。

在看小电影之前，最好先阅读一下下面的参数概念，不管读得懂读不懂，至少要先了解一下。

先来了解一些vlookup的几个基本参数的概念。

vlookup(lookup_value,table_array,col_index_num,range_lookup)

lookup_value 为需要在数组第一列中查找的数值。lookup_value 可以为数值、引用或文本字符串。也就是说，需要查找的数值，必须放在工作表的第一列。

table_array 为需要在其中查找数据的数据表。（包含你需要的内容的数据源）可以使用对区域或区域名称的引用，例如数据库或数据清单。

col_index_num 为 table_array 中待返回的匹配值的列序号。col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1，函数 vlookup 返回错误值值 #value!

；如果 col_index_num 大于 table_array 的列数，函数 vlookup 返回错误值 #ref!。

range_lookup 为一逻辑值，指明函数 vlookup 返回时是精确匹配还是近似匹配。如果为 true 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 false，函数 vlookup 将返回精确匹配值。如果找不到，则返回错误值 #n/a。

（如果 range_lookup 为 true，则 table_array 的第一列中的数值必须按升序排列、…z、false、true；否则，函数 vlookup 不能返回正确的数值。如果 range_lookup 为 false，table_array 不必进行排序。通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。

）说明如果函数 vlookup 找不到 lookup_value，且 range_lookup 为 true，则使用小于等于 lookup_value 的最大值。

如果 lookup_value 小于 table_array 第一列中的最小数值，函数 vlookup 返回错误值 #n/a。

2、转换为5位工号的方法。

我们经常会用要求员工工号为五位，比如考勤报表啦，合同信息之类的东西，以前我们都是用approach或者lotus123来做，现在全面切换至excel，我们也要用excel来做啦！！

第一种方法：传统取字段法。

1、插入新列，新列的内容=工号列+100000

2、取新列的右边5个字，用right()函数，right(工号列+100000，5)

第二种方法：text(value,format_text)

这个函数将会把数字转换为指定格式的文本。使用格式为：text（工号，"00000"）。

第三种方法：excel合并字符法。

在excel中合并字符有一个非常简便的方法，就是用“&”符号，可以合并很多字符，合并的内容可以是自己输入的，也可以是引用单元格内容的。要注意的是，自己输入的内容要用半角的引号——"括起来。

由此一个引申的用处是：快速的输入email地址。

我们一般在做通讯录时，输入email地址信息时会有很多重复的"@huawei"的字样，如果每个名字后面都要输一次，真的是很麻烦；如果只输入拼音，没有后面的“@huawei”，用起来又很不麻烦。用合并字符的方法就可以很快搞定了！

做法是这样的：

一列输入姓名的拼音，一列输入一个“@”然后用copy的方式向下拖，再做一列huawei，然后在email地址列中输入这样的公式，回车，搞定！！！以后再多的地址，只要输入拼音，然后向下拖就可以全部搞定了！！如果只要大家看到email地址列，只要把a、b、c列隐藏起来就可以了。

3、单元格数据的有效性。

1、选定要限制其数据有效性范围的单元格。

2、在“数据”菜单上，单击“有效性”命令，再单击“设置”选项卡。

3、指定所需的数据有效性类型：

1. 有序列的数值。

若要就在框中定义序列，请键入序列值，并用逗号分隔。

若要使用已命名的单元格区域，请键入等号 (=随后键入区域的名称。

若要使用单元格引用，请在工作表上选择单元格，然后按 enter。

2. 数字有范围限制。

在“允许”框中，单击“整数”或“小数”。

在“数据”框中，单击所需的限制类型。例如，若要设值上限或下限，请单击“介于”。

输入允许的最大值、最小值或特定值。

3、通过“输入信息”和“出错警告”两个选项卡还能设置友好的提示界面。

4、常用函数-统计篇。

我们在日常工作中，常常要统计一些数据，如平均值、求和、最大值、最小值什么的，在excel中，有非常强大的统计功能，可以让您轻松实现对数据的处理。

简便的使用最常用的函数：

1、平均值、求和：

这个就不多说了，大家都用得很多，也非常熟练了。

**erage()

sum()2、最大值。

max(number1,number2,..

number1, number2, .是要从中找出最大值的 1 到 30 个数字参数。

要注意的是，函数 max()在求最大值时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。

3、最小值。

min(number1,number2,..

number1, number2,..是要从中找出最小值的 1 到 30 个数字参数。

要注意的是，函数 min()在求最大值时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。

4、条件求和：

sumif(range,criteria,sum_range)

range 为用于条件判断的单元格区域。

criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、"32" 或 "apples"。

sum_range 是需要求和的实际单元格。

比如在人员信息统计用，用这个函数就可以简单的统计出各二级部门的总人数了。

5、计数。count()

这个函数会返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 count 可以计算单元格区域或数字数组中数字字段的输入项个数。

什么意思呢？最常见的用法，我们要统计这次秘书大会战中各科上交了多少份试卷，那么就用count()函数，返回各科共有多少个成绩，也就是提交了多少份试卷。

要注意的是，函数 count 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。

还有一个用法，count(value1,value2,…valuen)

value1, value2, .为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。可以用来计算包含特定数字的单元值的个数。

6、条件计数：

countif(range,criteria)

range 为需要计算其中满足条件的单元格数目的单元格区域。

criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、"32" 或 "apples"。

比如说，我们要计算某列中">90"的单元格数，就可以使用这样的表达式。

countif(a1:a20,">90")

函数中只能对本列的数值做条件限定，不能引用其他列的内容。而且判断式的条件单一，不能是">80 and <90"这样形式的。

5、"$的妙用。

大家在通过拖动鼠标来填充单元格的时候，是否遇到过这样的烦脑：填充出来的单元格中并不是自己想要的数据。

往下看，只要我们知道了"$"符号的功能，这些烦脑就会一扫而空！

在一个工作表中，c4、c5中的数据分别是。如果在d4单元格中输入“=c4”，那么将d4向下拖动到d5时，d5中的内容就变成了50，里面的公式是“=c5”，将d4向右拖动到e4，e4中的内容是60，里面的公式变成了“=d4”。

现在在d4单元格中输入“=$c4”，将d4向右拖动到e4，e4中的公式还是“=$c4”，而向下拖动到d5时，d5中的公式就成了“=$c5”。将d4向右拖动到e4，e4中的公式还是“=$c4”，而向下拖动到d5时，d5中的公式就成了“=$c5”。向右拖动到e4，e4中的公式还是“=$c4”，而向下拖动到d5时，d5中的公式就成了“=$c5”。

如果在d4单元格中输入“=c$4”，那么将d4向右拖动到e4时，e4中的公式变为“=d$4”，而将d4向下拖动到d5时，d5中的公式还是“=c$4”。

如果在d4单元格中输入“=$c$4”，那么不论你将d4向哪个方向拖动，自动填充的公式都是“=$c$4”。

原来谁前面带上了“$”号，在进行拖动时谁就不变。如果都带上了“$”在拖动时两个位置都不能变。

EXCEL常用函数

Excel常用函数

Excel常用函数

Excel常用函数

其他用户还读了