Excel中常用公式的介绍[1].doc

Excel中常用公式的介绍序言：Office办公软件主要由四大块组成，主要处理文字的Word、处理一般数据的Excel、处理幻灯片的PowerPoint、处理复杂数据的Access。由于我们使用的Word的机会比较多，往往以为Word的功能比Excel强。实际上，Office办公软件四大块中以Excel的功能最为强大，对经常需要作一些数据分析的来说，只有掌握一些公式，就可以大幅度提高效率。Excel中的公式（也称其为函数）可以划为分文本函数、逻辑函数、数学函数、财务函数、日期时间函数、信息函数等数类。这里只介绍其中四种。一、文本函数文本函数就是把单元格中的内容作为文本处理，进行诸如计算字节数、拆分、连接、查找特定字符等，共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）公式中的引号必须是西文标准，不能用中文双字节的引号。下面的公式一样。如果觉得这个公式的单词难记，可以用“&”符号，效果一样。上例可以简单地写成：a1& -&b1&-&c12、从左边开始截取指定数量的字节 left(a,b)式中a表示所指定的单元格，b表示需要截取的字节数。例如：a1=330102732412，要求得到的结果是前四位数字：3301，则可以写成：left(a1,4)4、从右边开始截取指定数量的字节 right(a,b)式中a表示所指定的单元格，b表示需要截取的字节数。例如：a1=330102732412，要求得到的结果是最后四位数字：2412，则可以写成：right(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)除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，用此函数特方便，其奇妙的地方在于删除多余空格，而将单词之间按西文规则保留一个空格。例如：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，要求得到结果是：ababab，则公式可以写成rept(a1,3)10、从指定位置开始查找某个指定字符在文本中首次出现的具体位置 search(a,b,c)式中a表示需要查找的指定字符，b表示单元格中的文本，c表示开始查找的位置。例如：a1=abcdefcci，要求知道第五个字母后c出现的具体位置，则可以写成：search(c,a1,5)，得到的结果是：7虽然上述十个公式看上去并没有什么特殊功效，但如果把公式相互组合起来，公式中嵌公式，效果就完全不一样。二、逻辑函数逻辑函数一共有6个，都比较容易理解，用法也简单。其中FALSE、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、两个以上条件同时需要满足的函数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。逻辑函数看似简单，实际上挺复杂的，特别是当有多项条件辨别选择的时候，顺序设计好坏，对能否简化公式有很大影响。在实际工作中，财务人员做个人所得税扣除，就非常需要逻辑函数；业务人员做年金保险方案，也非常需要逻辑函数；其他在统计、分析数据时也经常要用到这类函数。三、数学函数数学函数是excel中数量最大的部分，在三角、概率统计与分布上特别多，大约有171个公式，下面仅简单介绍其中常用的14个。1、取绝对值的函数 ABS（a）式中a表示单元格中的数值。例：a1=-2，希望得到绝对值2，则公式就是ABS（a1）2、计算自然数的n次幂EXP（a）例如在单元格中输入=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的整数部分，则可以把公式写成INT（a1/b1），计算结果为2注意，INT（）只是把结果往最接近的整数上靠，所以结果为负值时可能与你的希望不一样，如上例中b1=-3时，计算结果为-3，如果希望得到是-2，则应当用另外的公式，TRUNC（a1/b1）7、保留小数位数的函数 ROUND（a,b）式中a表示某个数值，b表示希望保留的小数位数。例：a1=7，b1=3，希望对计算结果保留2位小数，则公式可以写成ROUND（a1/b1，2），计算结果是2.33。如果此时点击增加小数位数的按钮，只是在2.33后面加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也可以用更简单的方式表示，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）式中a表示用于统计的原始数据（文本、数值均可），b表示统计口径（或者是条件）如上例，要统计萧山有几条数据，则公式可以写成COUNTIF（a1:a70, 萧山）13、多条件求和 SUM（if（a,if（b,c,d），e）上式表示两个条件求和，再多的条件无非增加if而已。式中a表示第一个条件，符合时处理下一个if，不符合时直接处理e。b表示第二个条件，符合时处理c，不符合时处理d。因为是求和，所以c只需给出特定区域就可以了。14、多条件统计 COUNT（if（a,if（b,c,d），e）上式表示两个条件的统计，再多的条件无非增加if而已。式中a表示第一个条件，符合时处理下一个if，不符合时直接处理e。b表示第二个条件，符合时处理c，不符合时处理d。因为是统计，所以c只需给出特定区域或直接赋给一个任意值就可以了，而不符合任何一个条件时，应当赋给一个空值，故式中d、e最好用，中间没有空格。注意：上述两公式在输入完毕后，必须按住ctrl和shift键，再按回车键，否则将得不到正确结果。在本质上，通过两个甚至多个if，只是把符合条件的数据采集到一个集合中，sum（count）只是对该集合中的元素相加（统计元素个数）四、时间日期函数时间日期函数共有20个，如果单元格格式已设置成日期格式，则使用日期函数可以直接得到年份、月份、日期等数据，对计算经过时间、年龄很有好处。这里只介绍其中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这样一个数，第6位起是空的。3、截取日期的函数 DAY（a）同上例，要得到10，则把公式写成DAY（a1）同样不能用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，把日期部分自动以该月最大天数为限，超过部分往月份上加。闰年也会自动处理。五、其它常用的定位、查找函数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表示行号，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（）函数时，所带来的便利是非常大的。在直销业务中，经常要对被保险人计算交费标准或保险金额，根据被保险人性别、年龄等条件，可以迅