Excel常用公式

1、常常用用公公式式序序号号作作用用1从文本中提取数字2公公式式“=LOOKUP(9E+307,-MID(A3,MIN(FIND(0;1;2;3;4;5;6;7;8;9,A3&1234567890),ROW(INDIRECT(1:&LEN(A3)”常常用用公公式式备备注注不限位数常常用用公公式式序号函数功能说明1MIDMID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定2MIDBMID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定3MIN返回参数列表中的最小值4FINDFind函数用来对原始数据中某个字符串进行定位，以确定其位置。Find函数进行定位时，

2、总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。5&连接字符串，即两串字符串连在一起6-文本转换为数字(负负得正)7ROW表示当前单元格所在行的序号8INDERECT返回由文本字符串所指定的引用9VLOOKUP 在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。 这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列。 假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列。10COUNTIFcountif函数

3、的功能就是用来计算个数，表示计算区域中满足给定条件的单元格的个数11ROUND四舍五入函数12SUBSTITUT(substitut)文本字符串中替换指定的文本参数实例MID(text，start_num，num_chars)如果a1=电子计算机，则公式“=MID(A1，3，2)”返回“计算”，=MIDB(A1，3，2)返回“子”。MIDB(text，start_num，num_bytes)MIN(number1,number2,.)例如:A列 0.1 -1 在A4输入公式:=MIN(A1:A3)结果:-1 2 FIND(find_text,within_text,start_num)A2=“

4、广东省东莞市东城区A3=“黑龙江省哈尔滨市 对含有不同地方的数据，利用“Find”函数，非常简单地确定“省”出现的位置。详细解释:公式“=FIND(省,A2)”中，“省”表示要查找的文本为“省”，（实际使用中，也可以很长的一串字符）。要找查找的对象是A2单元格的内容“广东省东莞市东城区”，因为没有指定起始位置，所以系统从第一位开始。返回的“3”，表示“省“字在第三位。而公式“=FIND(省,A3)”中，“黑龙江省哈尔滨市”则返回4。与Find类似，Search函数也有相同的功能。它们的区别是，Find区分大小写，而Search不分大小写（当被查找的文本为英文时）。A2 & B2 或 a

5、 & bc 例如要将A、B、C几列中的数值或文本合并到D列中表示，则在D列单元格中输入公式为：=A&B&C-MID(text,start_num,num_chars)ROW( )选择C2单元格，输入“=ROW（B4）”，按回车会显示“4”。INDIRECT(&$S27&!AC20)INDIRECT(&$S27&!AC20) 的意思是：引用以单元格S27的内容为工作表名称的AC20单元格的内容。工作表名称是由数字、括号等特殊字符命名时工作表名称要用单引号括起来，所以要在$S27前后再串接一个单引号。VLOOKUP（查找值，区域，列序号，逻辑

6、值）假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价： A B1 农产品名称 单价2 小麦 0.563 水稻 0.484 玉米 0.395 花生 0.51100 大豆 0.45Sheet2为销售清单，每次填写的清单内容不尽相同：要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。设下表为Sheet2： A B C D1 农产品名称 数量 单价 金额2 水稻 1000 0.48 4803 玉米 2000 0.39 780 在D2单元格里输入公式：=C2*B2；在C2单元格里输入公式：=VLOOKUP（A2，Sheet1!A2:B100，2

7、，FALSE）。如用语言来表述，就是：在Sheet1表A2:B100区域的第一列查找Sheet2表单元格A2的值，查到后，返回这一行第2列的值。 这样，当Sheet2表A2单元格里输入的名称改变后，C2里的单价就会自动跟着变化。当然，如Sheet1中的单价值发生变化，Sheet2中相应的数值也会跟着变化。其他单元格的公式，可采用填充的办法写入。COUNTIF(range,criteria)=COUNTIF(A6:A35,*河*)=COUNTIF(D6:D35,30)=COUNTIF(D6:D35,&D7)ROUND(number,num_digits)ROUND(2.15, 1) 将

8、2.15 四舍五入到一个小数位(2.2) =ROUND(2.149, 1) 将 2.149 四舍五入到一个小数位 (2.1) =ROUND(-1.475, 2) 将 -1.475 四舍五入到两小数位 (-1.48) =ROUND(21.5, -1) 将21.5 四舍五入到小数点左侧一位 (20) SUBSTITUTE(text,old_text,new_text,instance_num)A4=2011年第一季度，执行公式SUBSTITUTE(A4,1,2,2)后，用“2”代替示例中第二次出现的“1”变成2012年第一季度A3=2008年第一季度，执行公式SUBSTITUTE(A3,一,二,1

9、)后，用“二”代替示例中第一次出现的“一”，变成2008年第二季度备注Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推；Num_chars指定希望MID从文本中返回字符的个数Num_bytes指定希望MIDB从文本中按字节返回字符的个数。Find_text 是要查找的文本。Within_text 是包含要查找文本的文本。Start_num 指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。字符串或文本连接时需要加双引号还有13种方法

10、公式解析：“=ROW（B4）”表示所引用的B4单元格所在行的序号，B4在第4行，所以显示4$符号表示绝对引用，单元格名称的行号或列号前加一个$，就可以将行号或列号锁定起来，公式在上下或左右拖动填充时，保持行号或列号不发生变化。“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。“区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP返回错误值#VALUE!；如果大于区域的列

11、数，函数VLOOKUP返回错误值#REF!。“逻辑值”：为TRUE或FALSE。它指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。如果“查找值”为文本时，“逻辑值”一般应为FALSE 。另外：如果“查找值”小于“区域”第一列中的最小值，函数VLOOKUP返回错误值#N/A。如果函数VLOOKUP找不到“查找值” 且“逻辑值”为FALSE，函数VLOOKUP返回错误值#N/A。其中: Rang

12、e参数：是为需要计算其中满足条件的单元格数目的单元格区域， Criteria参数：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 COUNTIF函数是支持通配符的，在COUNTIF函数中可以引用通配符。 其中通配符?号代表单个字符，*号代表多个字符。Number 需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。 Text是需要替换其中字符的文本，

13、或是含有文本的单元格引用；Old_text是需要替换的旧文本；New_text用于替换old_text 的文本；Instance_num 为一数值，用来指定以new_text 替换第几次出现的old_text；如果指定了instance_num，则只有满足要求的old_text被替换；否则将用new_text 替换Text 中出现的所有old_text。1、引用B2=预先输入的内容!B2很明显，该公式引用了工作表“预先输入的内容”中B2单元格的内容。本工作表B2单元格的值与预先输入的内容!B2的值相同。2、地址“A1”、“B2”分别表示一个单元格的地址，“预先输入的内容!B2”也表示一个单元格

14、地址，是一个指定工作表的单元格地址。如果预先知道单元格地址，在公式中使用该地址可以引用它的值。3、文本天安门广场是一个地址，把“天安门广场”写在纸片上，写在手心，它是一串文本，根据这个文本，向导可以把你带到天安门广场。文本和地址是两回事，不容易把它说清楚，但聪明的你可能已经心领神会了，OK。4、转换有了文本，需要向导才能到达，也一定能到达指定的地址。工作表中单元格地址也一样,“A1”与A1是两回事，“Sheet2!F7”与Sheet2!F7是两回事，知道地址文本，你要引用这个单元格的值，需要一个向导，它就是INDIRECT()函数。要点之一： INDIRECT()函数的第一个参数为一个文本，一

15、个表示单元格地的文本。要点之二： Excel对单元格有两种引用样式，一种为A1引用样式，一种为R1C1引用样式。当使用A1引用样式时，INDIRECT()函数第二个参数须指定为TRUE或省略它。当使用R1C1引用样式时，INDIRECT()的第二个参数须指定为FALSE。 INDIECT(F5)，与INDIRECT(F5,TRUE)，与INDIRECT(R5C6,FALSE)返回同一个单元格的引用。1、引用B2=预先输入的内容!B2很明显，该公式引用了工作表“预先输入的内容”中B2单元格的内容。本工作表B2单元格的值与预先输入的内容!B2的值相同。2、地址“A1”、“B2”分别表示一个单元格的地址，“预先输入的内容!B2”也表示一个单元格地址，是一个指定工作表的单元格地址。如果预先知道单元格地址，在公式中使用该地址可以引用它的值。3、文本天安门广场是一个地址，把“天安门广场”写在纸片上，写在手心，它是一串文本，根据这个文本，向导可以把你带到天安门广场。文本和地址是两回事，不容易把它说清楚，但聪明的你可能已经心领神会了，OK。4、转换有了文本，需要向导才能到达，也一定能到达指定的地址。工作表中单元格地址也一样,“A1”与A1是两回事，“Sheet2!F7”与Sheet2!F7是两回事，知道地址文本，你要引用这个单元格的值，需要一个