MSOffice函数基础知识(2015)

1/9一、函数基本知识介绍函数（function）表示每个输入值对应唯一输出值的一种对应关系，在Excel中，函数是以等号“=”开始的，以特定的函数名引导并以特定语法写成的表示执行某个特定运算过程的一个语句。比如：在A1格中输入了一串语句“=B2+100”，当在B2格中输入一个数25时，A1会返回一个唯一的值：125。“=B2+100”就是一个简单的函数，“=”是函数的引导符，表明接下来的式子是一个函数，而不是别的东西；“B2”是一个参数，参数有很多种，可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用，这里的“B2”就是一个单元格引用，表示引用B2格里的数值；“+”是可执行的运算，可执行运算可以是加减乘除等基本运算，也可以是一些MS定义的较复杂运算，这些较复杂的运算通常都有一个函数名；“100”是另一个参数。整个函数式表示要对单元格B2和常数100执行加法运算，然后将结果在A1格中显示出来。1．单元格及区域范围如上图:单元格:B2，E2区域范围:A6:D11,B2:D2,F10:H132．通配符“?“表示可以替代任意的一个字符或者是任意的一个数字,一个？只能替代一个字符。例如：厂内在线领料单63A1,63B1,63D1,我们可以用63?1来表示。2/9“*“表示可以替代任意个字符或者数字，一个*可以替代任意多个字符。上面例子我们也用63*1，来表示，如果存在含有AB的字符串，那么“AB*”表示以AB开头的字符串，“*ＡＢ”表示以AB结尾的字符串，“*AB*”表示包含AB的字符串。3．&连接运算符，它可以将若干个字符连接起来。例如:A&B=AB670，“-“,0505010001连接起来670&”-“&0505010001=670-05050100014．引用(1)相对引用相对引用是指函数所在的单元格与函数中引用的单元格之间的相对位置，就是说单元格的引用与函数的位置有关。例如:在单符格A5中输入公式“=SUM(A2:A4)”，此公式是求和数，将单元格A5公式复制到单元格B6后，所显示的结果是公式“=SUM(B3:B5)”执行后的结果随着公式的位置的改变，在公式中后引用的单元格也发生了改变，由“A2:A4”变成了“B3:B5”，(2)绝对引用绝对引用是指被引用的单元格与公式所在的单元格的位置是绝对的。即不管公式被复制到什么位置，公式中所引用的单元格的位置不变。单元格的绝对引用是在行号和列标的前面加“＄”符号，“=SUM($A$2:$A$4)”。(3)混合引用混合引用是一种介于相对引用和绝对引用之间的引用，即在引用单元格的行和列之中一个是相对的，,一个是绝对的。比如上面例子的加总公式“=SUM(A$2:A$4)”，表示其列值是相对引用，即随着公式的不同位置而发生改变，而行值是绝对引用，位置不发生变化，当我们把A5复制到B6时，公式会变成“=SUM(B$2:B$4)”二、EXCEL函数的应用1．VLOOKUP语法(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在数组第一列中查找的数值。Table_array为需要在其中查找数据的数据表。Col_index_num表中待返回的匹配值的列序号.Range_lookup为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配,如果range_lookup为TRUE或省略,则返回匹配值;找不到匹配值,则返回小3/9于Range_lookup的最大值如果Range_lookup为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!。2．TRIM语法TRIM(text)除了单词之间的单个空格外，清除文本中所有的空格。在从其它应用程序中获取带有不规则空格的文本时，可以使用函数TRIM=TRIM（“AREYOUOK?”）=AREYOUOK?Text为您所要移除多余空白的文字数据3．IF语法IF(logical_test,value_if_true,value_if_false)如果您指定的情况结果为TRUE，则传回一个值，若结果为FALSE，则传回另一个值。Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。例如，A10=100就是一个逻辑表达式，如果单符格A10中的值等于100，表达式即为TRUE，否则为FALSEValue_if_truelogical_test为TRUE时返回的值。例如，如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE，则IF函数将显示文本“预算内”。如果logical_test为TRUE而value_if_true为空，则本参数返回0（零）。Value_if_falselogical_test为FALSE时返回的值。例如，如果本参数为文本字符串“超出预算”而且logical_test参数值为FALSE，则IF函数将显示文本“超出预算”AB实际费用预算费用1150090025009004/93500925公式（结果）=IF(A2B2,OverBudget,OK)判断第1行是否超出预算(OverBudget)=IF(A3B3,OverBudget,OK)判断第2行是否超出预算(OK)4．Left语法(text,num_chars)从一个文本字符串的第一个字符开始返回指定的字符Text是包含要提取字符的文本字符串。Num_chars指定要由LEFT所提取的字符数。Num_chars必须大于或等于0。如果num_chars大于文本长度，则LEFT返回所有文本。如果省略num_chars，则假定其为1。A1Apple公式（结果）=LEFT(A1,4)第一个字符串中的前四个字符(Appl)=LEFT(A1)第二个字符串中的第一个字符(A)5．Right语法:RIGHT(text,num_chars)从一个文本字符串的最后一个字符开始返回指定个数的字符Text是包含要提取字符的文本字符串。Num_chars指定希望RIGHT提取的字符数。说明Num_chars必须大于或等于0。如果num_chars大于文本长度，则RIGHT返回所有文本。如果忽略num_chars，则假定其为1。A1Apple公式（结果）=Right(A1,4)第一个字符串中的倒数四个字符(pple)5/9=Right(A1)第二个字符串中的倒数第一个字符(e)6．Mid语法：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个字符的位置大于字符串的长度，所以返回空文本()7．LEN和FINDLEN语法：LEN(text)返回文本字符串中的字符个数（空格也算在内）。A11Apple公式（结果）=len(A1)=5（字符串的长度为5个字符）FIND语法：FIND(find_text,within_text,[start_num])返回要查找的文本在被查找的文本中第一次出现的位置find_text是要查找文本，可以是字符或字符串，也可以是包含字符或字符串的单元位引用，或计算结果为字符或字符串的计算式within_text是被查找的文本，可以是字符或字符串，也可以是包含字符或字符串的单元位引用，或计算结果为字符或字符串的计算式start_num是开始查找的位置，如果缺省此段，则默认为是从被查找文本的第1位查起6/9A11Apple公式（结果）=FIND(“L”,A1,2)=3（字符L在A1格中出现的位置是第4位,但从第2位开始查找,则是第3位）=FIND(“PP”,A1)=2(字符串PP在A1格中出现的位置是第2位,缺省了开始查找位置的字段,默认为从A1格的第1位开始查找,结果是第2位)三、数据透视表的应用:数据透视表本质上是一个由数据库生成的动态汇总报告。数据库可以存在于一个工作表(以表的形式)或一个外部的数据文件中。数据透视表可以将众多行列中的数据转换成一个有意义的数据报告。例如，数据透视表可以创建频率分布和几种不同数据量度的交叉表。使用数据透视表有一个小小的欠缺，它和基于公式的汇总报告不同，当改变源数据的信息时，数据透视表不会自动更新。不过该缺陷不会造成很严重的问题，只要单击一次“刷新”按钮，即可对最新的数据进行更新。来源资料用于数据透视表进行分析的来源资料有其自身的特点要求，它必须是近似于关系数据库中的二维表的一组数据。二维表是由纵、横两个坐标交叉形成的网格，横向的叫“行”，纵向的叫“列”。每一“行”里的数据称为一个“记录”，每一“列”则称为一个“字段”或一个“属性”，而表中的第一“行”通常称为“字段名”或“属性名”。“行”与“列”交叉的格子中的数据，称为“值”。各列之间的“值”可以是不同的格式，但同一列下的所有“值”必须是同一格式。这样的一个具有“字段”、“记录”和“值”的表，表示了一种关系。比如：表示财务部在职职员的二维表：“工号”、“姓名”等处在二维表的第一行，就是各字段的字段名，或属性名，它表明了所在列的数据的共同属性。比如：“姓名”列，就表明了在这一列下的所有值都是姓名，而姓名都是由文字组成的，这就要求“姓名”这一列所有的值的格式都必须是文本格式；“加班工时”列，工时都是数字表示的，这就要求“加班工时”列所有的值都必须是数值型的，如果写了“12.5小时”，就是一个错误的数值。同一列下必须都是相同格式的数据，这一点非常重要，违反了这个规则，数据透视表是不能得出正确的计算结果的。比如：要对“加班工时”进行加总，“100”+“12.5小时”,等于什么呢？一个数字和一堆文字相加，人是算不出来的，计算机也是算不出来的。7/9工号姓名上级部门加班工时备注005619陈志彬000001GM0财务部000001张协理000001000财务中心022364李仁珍025591GMC12成本课021187陈松025591GMC16成本课025591杨治钢005619GMC11．5成本课029075彭日玲006371GMD17出纳课012630沈艳006371GMD19出纳课007993肖风英006371GMD21出纳课006371张桂花005619GMD22出纳课027291黄家超004403GMA22．5账务课028657傅凯英004403GMA20．5账务课024219朱燕瓊004403GMA11．3账务课028718谭敏004403GMA16账务课028815朱婕儿004403GMA15账务课018777邓曼004403GMA9账务课008586赵伟丽004403GMA8．5账务课029002龚芳004403GMA12．5账务课004403林敏龙005619GMA11．5账务课022363许秀玲004403GMA16账务课二维表还必须有字段名，也就是说：表的第一行必须每一列都有列名。如果只有“值”，没有“名”，那这些“值”都是些什么东西呢？只是些毫无意义的数据罢了。计算机又怎么去对这些毫无意义的数据进行计算呢