1、 第 1 页 共 8 页 教育培训部彭玉龙提供 0571-87225927Excel 中常用公式的介绍序言：Office 办公软件主要由四大块组成，主要处理文字的Word、处理一般数据的 Excel、处理幻灯片的 PowerPoint、处理复杂数据的 Access。由于我们使用的 Word 的机会比较多，往往以为 Word的功能比 Excel 强。实际上，Office 办公软件四大块中以 Excel 的功能最为强大，对经常需要作一些数据分析的来说，只有掌握一些公式，就可以大幅度提高效率。Excel 中的公式（也称其为函数）可以划为分文本函数、逻辑函数、数学函数、财务函数、日期时间函数、信息函数
2、等数类。这里只介绍其中四种。一、文本函数文本函数就是把单元格中的内容作为文本处理，进行诸如计算字节数、拆分、连接、查找特定字符等，共 27 个公式。我们只介绍其中常用的 10 个公式。1、字符连接函数 concatenate（a,b,c,d）将不同单元格中的字符连接成起来，括号中的变量最多不能超过30 个，式中 a,b,c,d 表示不同的单元格，非单元格内容应当加引号。例如：a1=1997，b1=12，c1=21，要求得到的结果是：1997-12-21则可以写成：concatenate（a1, “-“,b1, “-“,c1）公式中的引号必须是西文标准，不能用中文双字节的引号。下面的公式一样。如
3、果觉得这个公式的单词难记，可以用“&”符号，效果一样。上例可以简单地写成：a1& “-“&b1&“-“&c12、从左边开始截取指定数量的字节 left(a,b)式中 a 表示所指定的单元格，b 表示需要截取的字节数。例如：a1=330102732412，要求得到的结果是前四位数字：3301，则可以写成：left(a1,4)4、从右边开始截取指定数量的字节 right(a,b)第 2 页 共 8 页 教育培训部彭玉龙提供 0571-87225927式中 a 表示所指定的单元格，b 表示需要截取的字节数。例如：a1=330102732412，要求得到的结果是最后四位数字：2412，则可以写成：ri
4、ght(a1,4)5、从中间指定位置开始截取指定数量的字节 mid(a,b,c)式中 a 表示所指定的单元格，b 表示开始位置，c 表示截取字节数。例如：a1=330102732412，要求得到的结果是中间四位数字：7324，则可以写成：mid(a1,7,4)6、计算单元格中字符串的字符数量 len(a)式中 a 表示所指定的单元格。例如：a1=330102732402，通过len 函数，可以计算出有多少字符，len(a1)，得到结果是：127、删除文本中多余的空格 trim(a)除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，用此函数特方便，其奇
5、妙的地方在于删除多余空格，而将单词之间按西文规则保留一个空格。例如：a1= I Love You ，要求得到的结果是：I Love You则可以写成 trim(a1)8、替换文本中的某个字符 replace(a,b,c,d)式中 a 表示单元格中的旧文本，b 表示需要替换的开始位置，c表示被替换的字节数，d 表示替换进去的新字符。例如：a1=中国人民保险公司，要求得到的结果是：中国生命保险公司，则可以写成：replace(a1,3,2, “生命“)9、按指定次数重复某字符 rept(a,b)式中 a 表示单元格中的文本或指定某个字符，b 表示重复次数。例如：a1=ab，要求得到结果是：abab
6、ab，则公式可以写成rept(a1,3)10、从指定位置开始查找某个指定字符在文本中首次出现的具体位置 search(a,b,c)式中 a 表示需要查找的指定字符，b 表示单元格中的文本，c 表示开始查找的位置。例如：a1=abcdefcci，要求知道第五个字母后第 3 页 共 8 页 教育培训部彭玉龙提供 0571-87225927“c“出现的具体位置，则可以写成：search(“c“,a1,5)，得到的结果是：7虽然上述十个公式看上去并没有什么特殊功效，但如果把公式相互组合起来，公式中嵌公式，效果就完全不一样。二、逻辑函数逻辑函数一共有 6 个，都比较容易理解，用法也简单。其中FALSE、
7、TRUE 两个函数不大使用，我们只介绍另外 4 个函数。但要正确灵活地使用好逻辑函数，关键在于对所需要处理的工作的理解。1、对一事进行条件判断，符合与不符合条件分别如何处理的函数 IF（a,b,c,）式中 a 表示条件，可以是单项条件，也可以是复合条件（与其它函数同时使用） ；b 表示符合 a 这个条件时如何处理，可以是赋一个值，也可以用公式计算某个值；c 表示不符合条件时如何处理，同样可以是赋值或公式计算。IF 函数可以层层嵌套，但最多不能超过 7 层。例 a1=男，a2=女，希望在 b 列得到结果是 b1=60，b2=55，则公式可以写成 IF（a1=“男“,60,55）2、两个以上条件同
8、时需要满足的函数 AND（a,b,c）式中 a、b、c 分别表示需要具备的条件。注意，AND（）返回值是 true 或 false（真或假） ，而不是具体的文本或数值。3、两个以上条件中至少满足一个的函数 OR（a,b,c）式中 a、b、c 分别表示需要具备的条件。与 AND（）函数一样，返回值是 true 或 false（真或假） ，而不是具体的文本或数值。上述两个公式分别需要根据返回值的真假再进行进一步的计算。4、对给出的条件取反的函数 NOT（a）式中 a 表示给出的条件，当 a 与条件相符时函数值为 false，不符时为 true。逻辑函数看似简单，实际上挺复杂的，特别是当有多项条件辨
9、别选择的时候，顺序设计好坏，对能否简化公式有很大影响。在实际工第 4 页 共 8 页 教育培训部彭玉龙提供 0571-87225927作中，财务人员做个人所得税扣除，就非常需要逻辑函数；业务人员做年金保险方案，也非常需要逻辑函数；其他在统计、分析数据时也经常要用到这类函数。三、数学函数数学函数是 excel 中数量最大的部分，在三角、概率统计与分布上特别多，大约有 171 个公式，下面仅简单介绍其中常用的 14 个。1、取绝对值的函数 ABS（a）式中 a 表示单元格中的数值。例：a1=-2，希望得到绝对值 2，则公式就是 ABS（a1）2、计算自然数的 n 次幂 EXP（a）例如在单元格中输
10、入=EXP，计算结果就是 e 的平方，7.3890563、计算阶乘的函数 FACT（a）例如在单元格中输入=FACT，计算结果就是 4！，等于 244、计算自然数为底的对数 LN（a）例如在单元格中输入= LN，计算结果就是 05、计算以指定数值为底的对数 LOG（a,b）式中 a 表示用于计算对数的正实数，b 表示底数，必须符合大于0，不等于 1 的条件，否则会出错。例：a1=8，b1=2，希望计算以 2 为底的 8 的对数，则公式是LOG（a1, b1） ，计算结果为 36、取整数的函数 INT（a）对一个数或一个计算结果只保留整数。例：a1=7，b1=3，希望只取 a1/b1 的整数部分
11、，则可以把公式写成 INT（a1/b1） ，计算结果为 2注意，INT（）只是把结果往最接近的整数上靠，所以结果为负值时可能与你的希望不一样，如上例中 b1=-3 时，计算结果为-3，如果希望得到是-2，则应当用另外的公式，TRUNC（a1/b1）7、保留小数位数的函数 ROUND（a,b）式中 a 表示某个数值，b 表示希望保留的小数位数。例：a1=7，b1=3，希望对计算结果保留 2 位小数，则公式可以写第 5 页 共 8 页 教育培训部彭玉龙提供 0571-87225927成 ROUND（a1/b1，2） ，计算结果是 2.33。如果此时点击增加小数位数的按钮，只是在 2.33 后面加
12、0 而已。所以，为什么我们有时候用公式计算后，最后的汇总数值总是与实际相差一两分钱，就是因为没有对每个计算结果进行四舍五入的缘故。8、取两数相除后的余数 MOD（a,b）式中 a 表示被除数，b 表示非零的除数。例：a1=5，希望得到被 2 除了以后的余数，则完全可以把公式写成 MOD（a1，2） ，计算结果为 1利用这个公式，我们可以轻松地根据身份证号码辨别出性别。9、指数函数 POWER（a,b）式中 a 表示底数，b 表示指数。例：a1=1.025，b1=3，希望得到 1.025 的 3 次方，则公式可以写成 POWER（a1,b1） ，计算结果是 1.07689也可以用更简单的方式表示
13、，a1b1，计算结果相同。10、辨别一个数的正负 SIGN（a）该公式返回该数值的正负，返回值 1 表示正数，0 表示处于正负之间的 0，-1 表示负数。例：a1=-4，若使用公式 SIGN（a1） ，得到的计算结果是-111、单项条件求和 SUMIF（a,b,c）式中 a 表示用于条件判断的单元格区域，b 表示具体条件，c 表示需要求和的数据区域例：从 a1 到 a70 为萧山余杭等单位名称（有可能重复） ，从 b1到 b70 为数据，现在要求把同一单位的数据相加。则公式可以写成（先加萧山）SUMIF（a1:a70, “萧山“,b1:b70）12、单项条件统计 COUNTIF（a,b）式中
14、a 表示用于统计的原始数据（文本、数值均可） ，b 表示统计口径（或者是条件）如上例，要统计萧山有几条数据，则公式可以写成COUNTIF（a1:a70, “萧山“）13、多条件求和 SUM（if（a,if（b,c,d） ，e） ）第 6 页 共 8 页 教育培训部彭玉龙提供 0571-87225927上式表示两个条件求和，再多的条件无非增加 if 而已。式中 a表示第一个条件，符合时处理下一个 if，不符合时直接处理 e。b 表示第二个条件，符合时处理 c，不符合时处理 d。因为是求和，所以c 只需给出特定区域就可以了。14、多条件统计 COUNT（if（a,if（b,c,d） ，e） ）上式
15、表示两个条件的统计，再多的条件无非增加 if 而已。式中a 表示第一个条件，符合时处理下一个 if，不符合时直接处理 e。b表示第二个条件，符合时处理 c，不符合时处理 d。因为是统计，所以 c 只需给出特定区域或直接赋给一个任意值就可以了，而不符合任何一个条件时，应当赋给一个空值，故式中 d、e 最好用“，中间没有空格。注意：上述两公式在输入完毕后，必须按住 ctrl 和 shift 键，再按回车键，否则将得不到正确结果。在本质上，通过两个甚至多个if，只是把符合条件的数据采集到一个集合中，sum（count）只是对该集合中的元素相加（统计元素个数）四、时间日期函数时间日期函数共有 20 个
16、，如果单元格格式已设置成日期格式，则使用日期函数可以直接得到年份、月份、日期等数据，对计算经过时间、年龄很有好处。这里只介绍其中 4 个。1、截取年度的函数 YEAR（a）式中 a 表示某个日期。例：a1=2003-9-10，要求得到 2003，则把公式写成 YEAR（a1）注意，这里不能用 left（a1,4）函数，因为在 excel 中实际上是用一个数值保存的，所以实际会得到 3787 这样一个数。2、截取月份的函数 MONTH（a）同上例，与得到 9，则把公式写成 MONTH（a1）同样不能 mid（a1，6，1）函数，否则会得到一个空值，因为该日期实际上是 37874 这样一个数，第
17、6 位起是空的。3、截取日期的函数 DAY（a）同上例，要得到 10，则把公式写成 DAY（a1）第 7 页 共 8 页 教育培训部彭玉龙提供 0571-87225927同样不能用 right（a1,2） ，否则会得到 74 这样的数。理由同上。4、把数字以日期形式表示的函数 DATE（a,b,c）式中 a、b、c 分别表示一些数，公式能自动表示成 yyyy-mm-dd的形式。例：a1=2003，b1=37，c1=54，使用公式 DATE（a1,b1,c1）后，得到的结果是 2006-2-23 这样的日期形式。公式能自动把月份以 12为限，每超过一个 12 就往年度上加 1，把日期部分自动以该
18、月最大天数为限，超过部分往月份上加。闰年也会自动处理。五、其它常用的定位、查找函数1、定位函数 MATCH（a,b,c）式中 a 表示需要查找的对象，b 表示查找区域，c 以 1，0，-1 表示（分别代表升序排列、不按序排列、降序排列） 。当 b 为指定行时，返回的是选定区域内第几列，当 b 为指定列时，返回的选定区域内第几行。例：a1 至 a12 是 516 的自然数序列，若 b1=7，与想知道 7 在a 列是第几行，可以把公式写成 MATCH（a1:a12,b1,0） ，则返回的结果是 3，表示 7 这个数在 a 列是第三行。2、检索函数 INDEX（a,b,c）式中 a 表示检索区域，b
19、 表示行号，c 表示列号。返回的是在指定行、列中的具体数据。例：a1=50，a2=55，a3=60，b1=0.045，b2=0.056，b3=0.078，c1=0.065，c2=0.077，c3=0.083，在这样一个三行三列的数据表中，要求返回指定 2 行 3 列的数据，则公式应写成 INDEX（a1:c3,2,3）,返回的结果是 0.077表面上知道了行列号已经不需要用公式去检索，但实际上当你配合 match（）函数时，所带来的便利是非常大的。在直销业务中，经常要对被保险人计算交费标准或保险金额，根据被保险人性别、年龄等条件，可以迅速从费率表中找到系数，再进行计算，可以做到一气第 8 页 共 8 页 教育培训部彭玉龙提供 0571-87225927呵成，直接得到最终结果。3、查找函数 VLOOKUP（a,b,c,d）式中 a 表示查找的依据，可以是数值、文本，b 表示查找区域，c 表示所在的列号，d 为一逻辑值（TRUE 或 FALSE，忽略时默认是TRUE） ， 为 FALSE 时返回的是精确匹配，如果为 TRUE 或忽略，则返回近似匹配值，即最接近要求是一个值。注意：使用该函数最好先进行升序排列，以免得到错误结果。以上共 35 个函数公式，如能熟练运用，相互嵌套，已近 excel 高手也。
