MSOffice函数基础知识(2015

1、一、 函数基本知识介绍 函数（function）表示每个输入值对应唯一输出值的一种对应关系，在Excel中，函数是以等号“=”开始的，以特定的函数名引导并以特定语法写成的表示执行某个特定运算过程的一个语句。比如：在A1格中输入了一串语句“=B2+100”，当在B2格中输入一个数25时，A1会返回一个唯一的值：125 。“=B2+100”就是一个简单的函数，“=”是函数的引导符，表明接下来的式子是一个函数，而不是别的东西；“B2”是一个参数，参数有很多种，可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用，这里的“B2”就是一个单元格引用，表示

2、引用B2格里的数值；“+”是可执行的运算，可执行运算可以是加减乘除等基本运算，也可以是一些MS定义的较复杂运算，这些较复杂的运算通常都有一个函数名；“100”是另一个参数。整个函数式表示要对单元格B2和常数100执行加法运算，然后将结果在A1格中显示出来。 1 单元格及区域范围如上图: 单元格: B2，E2 区域范围:A6:D11, B2:D2,F10:H13 2通配符“? “表示可以替代任意的一个字符或者是任意的一个数字,一个？只能替代一个字符。例如：厂内在线领料单 63A1 ,63B1,63D1, 我们可以用 63?1 来表示。“* “ 表示可以替代任意个字符或者数字，一个*可以替代任意多

3、个字符。 上面例子我们也用 63*1，来表示，如果存在含有AB的字符串，那么“ AB* ”表示以AB开头的字符串，“*”表示以AB结尾的字符串，“*AB*”表示包含AB的字符串。3&连接运算符，它可以将若干个字符连接起来。例如:A&B =AB 670 ，“-“, 连接起来 670&”-“&=670-4 引用(1)相对引用相对引用是指函数所在的单元格与函数中引用的单元格之间的相对位置，就是说单元格的引用与函数的位置有关。例如:在单符格A5中输入公式“=SUM(A2:A4)” ，此公式是求和数，将单元格A5公式复制到单元格B6后，所显示的结果是公式“=SUM(B3:B5)”执行后的结果随着公式的位

4、置的改变，在公式中后引用的单元格也发生了改变，由“A2:A4”变成了“B3:B5”，(2) 绝对引用绝对引用是指被引用的单元格与公式所在的单元格的位置是绝对的。即不管公式被复制到什么位置，公式中所引用的单元格的位置不变。单元格的绝对引用是在行号和列标的前面加“”符号，“=SUM($A$2:$A$4)” 。(3) 混合引用混合引用是一种介于相对引用和绝对引用之间的引用，即在引用单元格的行和列之中一个是相对的，,一个是绝对的。比如上面例子的加总公式“=SUM(A$2:A$4)”，表示其列值是相对引用，即随着公式的不同位置而发生改变，而行值是绝对引用，位置不发生变化，当我们把A5复制到B6时，公式会

5、变成“=SUM(B$2:B$4)”二、 EXCEL函数的应用1VLOOKUP 语法 (lookup_value, table_array,col_index_num,range_lookup)Lookup_value 为需要在数组第一列中查找的数值。Table_array 为需要在其中查找数据的数据表。Col_index_num 表中待返回的匹配值的列序号.Range_lookup 为一逻辑值, 指明函数 VLOOKUP 返回时是精确匹配还是近似匹配,如果 range_lookup 为 TRUE或省略,则返回匹配值;找不到匹配值,则返回小于Range_lookup 的最大值如果Range_lo

6、okup 为FALSE,将返回精确匹配值,找不到,则返回错误值#N/A 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!。2TRIM 语法 TRIM(text) 除了单词之间的

7、单个空格外，清除文本中所有的空格。在从其它应用程序中获取带有不规则空格的文本时，可以使用函数 TRIM=TRIM（“ARE YOU OK? ”）= ARE YOU OK?Text为您所要移除多余空白的文字数据3 IF 语法 IF (logical_test, value_if_true, value_if_false)如果您指定的情况结果为 TRUE，则传回一个值，若结果为 FALSE，则传回另一个值。Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如，A10=100 就是一个逻辑表达式，如果单符格 A10 中的值等于 100，表达式即为 TRUE，否则

8、为 FALSEValue_if_true logical_test 为 TRUE 时返回的值。例如，如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE，则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 value_if_true 为空，则本参数返回 0（零）。Value_if_false logical_test 为 FALSE 时返回的值。例如，如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE，则 IF 函数将显示文本“超出预算”AB实际费用预算费用1150090025009003500

9、925公式（结果）=IF(A2B2,Over Budget,OK)判断第 1 行是否超出预算 (Over Budget)=IF(A3B3,Over Budget,OK)判断第 2 行是否超出预算 (OK)4Left 语法 (text, num_chars)从一个文本字符串的第一个字符开始返回指定的字符Text 是包含要提取字符的文本字符串。Num_chars 指定要由 LEFT 所提取的字符数。 Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 LEFT 返回所有文本。 如果省略 num_chars，则假定其为 1。A1Apple公式（结果）=LEFT(A1

10、,4)第一个字符串中的前四个字符 (Appl)=LEFT(A1)第二个字符串中的第一个字符 (A)5Right 语法:RIGHT(text,num_chars)从一个文本字符串的最后一个字符开始返回指定个数的字符 Text 是包含要提取字符的文本字符串。Num_chars 指定希望 RIGHT 提取的字符数。说明 Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。 如果忽略 num_chars，则假定其为 1。 A1Apple公式（结果）=Right(A1,4)第一个字符串中的倒数四个字符 (pple)=Right(A1)第二个字符

11、串中的倒数第一个字符 (e)6Mid 语法：MID(text,start_num,num_chars) Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars指定希望 MID 从文本中返回字符的个数。A1apple公式说明（结果）=MID(A1,2,3)=ppl(从第2个字符开始,取右侧的3个字符)=MID(A1,3,5)=ple(从第3个字符开始,取右侧的5个字符,但文本中从第3个字符开始到文本结束,只有3个字符,返回值便只有3个字符)=MID(A1,20,5)因为要提取的第1

12、个字符的位置大于字符串的长度，所以返回空文本 ()7LEN 和 FIND LEN 语法：LEN(text)返回文本字符串中的字符个数（空格也算在内）。A11Apple公式（结果）=len(A1)=5（字符串的长度为5 个字符 ）FIND 语法：FIND(find_text,within_text,start_num)返回要查找的文本在被查找的文本中第一次出现的位置find_text 是要查找文本，可以是字符或字符串，也可以是包含字符或字符串的单元位引用，或计算结果为字符或字符串的计算式within_text 是被查找的文本，可以是字符或字符串，也可以是包含字符或字符串的单元位引用，或计算结果为

13、字符或字符串的计算式start_num 是开始查找的位置，如果缺省此段，则默认为是从被查找文本的第1位查起A11Apple公式（结果）=FIND(“L”,A1,2)=3（字符L在A1格中出现的位置是第4位,但从第2位开始查找,则是第3位）=FIND(“PP”,A1)=2(字符串PP在A1格中出现的位置是第2位,缺省了开始查找位置的字段,默认为从A1格的第1位开始查找,结果是第2位)三、数据透视表的应用:数据透视表本质上是一个由数据库生成的动态汇总报告。数据库可以存在于一个工作表(以表的形式)或一个外部的数据文件中。数据透视表可以将众多行列中的数据转换成一个有意义的数据报告。例如，数据透视表可以

14、创建频率分布和几种不同数据量度的交叉表。使用数据透视表有一个小小的欠缺，它和基于公式的汇总报告不同，当改变源数据的信息时，数据透视表不会自动更新。不过该缺陷不会造成很严重的问题，只要单击一次“刷新”按钮，即可对最新的数据进行更新。来源资料用于数据透视表进行分析的来源资料有其自身的特点要求，它必须是近似于关系数据库中的二维表的一组数据。二维表是由纵、横两个坐标交叉形成的网格，横向的叫“行”，纵向的叫“列”。每一“行”里的数据称为一个“记录”，每一“列”则称为一个“字段”或一个“属性”，而表中的第一“行”通常称为“字段名”或“属性名”。“行”与“列”交叉的格子中的数据，称为“值”。各列之间的“值”

15、可以是不同的格式，但同一列下的所有“值”必须是同一格式。这样的一个具有“字段”、“记录”和“值”的表，表示了一种关系。比如：表示财务部在职职员的二维表：“工号”、“姓名”等处在二维表的第一行，就是各字段的字段名，或属性名，它表明了所在列的数据的共同属性。比如：“姓名”列，就表明了在这一列下的所有值都是姓名，而姓名都是由文字组成的，这就要求“姓名”这一列所有的值的格式都必须是文本格式；“加班工时”列，工时都是数字表示的，这就要求“加班工时”列所有的值都必须是数值型的，如果写了“12.5小时”，就是一个错误的数值。同一列下必须都是相同格式的数据，这一点非常重要，违反了这个规则，数据透视表是不能得出

16、正确的计算结果的。比如：要对“加班工时”进行加总，“100”+“12.5小时” ,等于什么呢？一个数字和一堆文字相加，人是算不出来的，计算机也是算不出来的。工号姓名上级部门加班工时备注陈志彬GM0财务部张协理000财务中心李仁珍GMC12成本课陈松GMC16成本课杨治钢GMC115成本课彭日玲GMD17出纳课沈艳 GMD19出纳课肖风英GMD21出纳课张桂花GMD22出纳课黄家超GMA225账务课傅凯英GMA205账务课朱燕瓊GMA113账务课谭敏GMA16账务课朱婕儿GMA15账务课邓曼 GMA9账务课赵伟丽GMA85账务课龚芳GMA125账务课林敏龙GMA115账务课许秀玲GMA16账务课二维表还必须有字段名，也就是说：表的第一行必须每一列都有列名。如果只有“值”，没有“名”，那这些“值”都是些什么东西呢？只是些毫无意义的数据罢了。计算机又怎么去对这些毫无意义的数据进行计算呢？具体操作：1 打开EXCEL表，选定你要分析的数据范围，即要分析的二维表范围2 点击数据-数据透视表和数据透视图3.在弹出的透视表