excel常用内置公式使用说明

E XCEL常用内置公式使用说明

一、查找类（重点是vlookup其他2个可简单了解）

1.VLOOKUP

作用：从大量无序的数据列中通过键值查找指定信息引用到目标位置，是最常用的公式之一。

语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

L ookup_value 为需要在表格数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）第一列中查找的数值。Lookup_value 可以为数值或引用。

若lookup_value 小于table_array 第一列中的最小值，VLOOKUP 返回错误值

#N/A。

Table_array 为两列或多列数据。使用对区域或区域名称的引用。table_array 第一列中的值是由lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。

Col_index_num 为table_array 中待返回的匹配值的列序号。Col_index_num 为1 时，返回table_array 第一列中的数值；col_index_num 为2，返回table_array 第二列中的数值，以此类推。如果col_index_num ：

小于1，VLOOKUP 返回错误值#VALUE!。

大于table_array 的列数，VLOOKUP 返回错误值#REF!。

Range_lookup 为逻辑值，指定希望VLOOKUP 查找精确的匹配值还是近似匹配值：如果为TRUE 或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值。

table_array 第一列中的值必须以升序排序；否则VLOOKUP 可能无法返回正确的值。

有关详细信息，请参阅排序数据。

如果为FALSE，VLOOKUP 将只寻找精确匹配值。在此情况下，table_array 第一列的值不需要排序。如果table_array 第一列中有两个或多个值与lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。

使用说明和例子：

a.第一个参数lookup_value，一般情况下只要将光标放在其后的输入框中，然后选择一个待查找单元格就行了，不必选择整列，只要完成一个单元格的公式输入，excel将依据相对引用的原则自动填充公式，所以不需要选择整列。

b.在多个列需要vlookup时，Table_array选中整个源数据区，再混合使用绝对引用和相对引用，就可一次性完成多个列的一次性vlookup。关于绝对引用和相对引用可以参考excel帮助。

c. Range_lookup 一般选0，以避免源数据未排序或者格式不匹配引起的返回错误。

2.H LOOKUP

类似vlookup只是查找的方向不同，vlookup是纵向查找，Hlookup是横向查找，参数的类型和使用方法都和Vlookup类似。可以参考excel帮助。

3.LOOKUP

LOOKUP 函数具有两种语法形式：向量形式和数组形式。向量形式:在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。数组形式:在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值，一般而言，最好使用HLOOKUP 或VLOOKUP 函数而不是LOOKUP，使用HLOOKUP 和VLOOKUP，可以通过索引以向下或遍历的方式搜索，但是LOOKUP 始终选择行或列中的最后一个值，或者第二行（列）的值。

二、文本合并拆分以及格式化

1.MID和MIDB

作用：从单元格中指定位置处，指定长度提取字符串，有时候我们只需要一个单元格中的一部分（有时需要字母，有时需要数字串，有时需要汉字）那么可以

使用该函数。

语法：

MID(text,start_num,num_chars)

MIDB(text,start_num,num_bytes)---该函数针对纯双字节文本如日文，汉文。

Text 是包含要提取字符的文本字符串。

Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的

start_num 为1，以此类推。

Num_chars 指定希望MID 从文本中返回字符的个数。

Num_bytes 指定希望MIDB 从文本中返回字符的个数（按字节）。

注解：

如果start_num 大于文本长度，则MID 返回空文本("")。

如果start_num 小于文本长度，但start_num 加上num_chars 超过了文本

的长度，则MID 只返回至多直到文本末尾的字符。

如果start_num 小于1，则MID 返回错误值#VALUE!。

如果num_chars 是负数，则MID 返回错误值#VALUE!。

如果num_bytes 是负数，则MIDB 返回错误值#VALUE!。

使用说明和例子：

上述例子中只需要汉字信息那么，就在B2位置处输入以上公式，下拉后就可以得到汉字字符串。

2.文本合并操作符-&

只要在目标单元格中输入等号，然后将两个或者多个引用位置的单元格用“&”

符号连接起来就可以达到文本合并的目的。

3.文本格式化-TEXT函数

作用：将文本或者数值转化成指定格式，格式用格式化字符串控制。具体格式化字符串分为日期、时间、数值、货币等类别。

语法：TEXT(value,format_text)

Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。

Format_text 是作为用引号括起的文本字符串的数字格式。通过单击“设置单元格格式”对话框中的“数字”选项卡的“类别”框中的“数字”、“日期”、“时间”、“货币”

或“自定义”并查看显示的格式，可以查看不同的数字格式。

注解：

Format_text 不能包含星号(*)。

使用命令设置单元格格式（在“开始”选项卡上的“数字”组中，单击“数字”旁边的箭头，然后单击“数字”）仅更改格式，而不会更改值。使用函数TEXT 会将数值转换为带格式的文本，而其结果将不再作为数字参与计算。

例子和说明：