EXCEL_函数介绍.ppt

microsoft excel 2003 功能介绍,ctrl & shift 的应用 excel 函数 替换 选择性粘贴 数据透视表,计算只不过是excel的很小一部分功能！,提示：,ctrl + / / / 移动到表格中的最上面/最下面/最左面/最右面 或者移动到下一个数据区域的最上面/最下面/最左面/最右面 shift + / / / 快速选择一个表格区域 ctrl + pgup / pgdn 快速移动到左边/右边的工作表 ctrl + home 快速移动到单元格a1 或者冻结窗格的左上角 ctrl + end 快速移动到当前活动工作表的右下角 或者空白工作表的单元格a1 ctrl + shift + / / / 快速选者所选单元格的整行/增列 ctrl + shift + pgup / pgdn 快速选者所选单元格对应的左边/右边工作表的单元格,利用ctrl & shift 快速移动和选择单元格,逻辑 if and or not 查找 vlookup,计算 sum sumif countif 文本处理 left right mid len find concatenate,excel 函数的应用,当你要计算连续12张单元表中所有单元格 b4的和时，你或许会点击每一张单元表的b4 来取和,公式1: “=sheet1!b4+sheet2!b4+sheet12!b4”,公式2: “=sum(sheet1:sheet12!b4)”,或者, 你可以利用 shift键: 输入 “ =sum( “ 单击第一张单元表”sheet1” 按住 shift 键 单击最后一张单元表“sheet12” 点击单元格 (e.g. b4) 所需求和的区域 输入 “ ) ”,特别注意当你移动单元表的顺序时，结果有可能会变化。,sum 不同工作表之间的求和,什么时候使用: 求同一列/行中具有相同名字单元格的的和, 华北地区2010-1-1到2010-1-29的总金额 =sumif (b2:b30,b2,e2:e30),语法 sumif(range, criteria, sum_range),tips: 使用绝对地址符号 ($) 来固定单元格或者表格区域，这样复制公式时不会出错 确保需要符合的条件与求和类别区域中所用的名字相同e.g., 有没有空格, 但英文字母的大小写不敏感 ( a & a 被认为是一样的),利用sumif求多个有相同定义单元格的和,什么时候使用 除了数据透视表外计算个数的快速方法,语法： countif(range, criteria),e.g., =countif(d20:d30,a) 得出产品a在2010-1-1到2010-1-29期间销售的个数,tips: 可以用counta()来统计区域中非空的单元格数目 可用count()来统计区域中数字形式的单元格数目,countif: 快速得出符合某条件的单元格个数,left: 返回单元格中最左边指定数目的字符 right:返回单元格中最右边指定数目的字符 mid:返回单元格中指定文字开始到指定数目的字符 len: 返回单元格字符串的长度 语法 left(text, num_chars) right(text, num_chars) mid(text, start_num, num_chars) len(text) e.g., =left(“141r-111shkb”,4) 141r =right(“141r-111shkb”,7) 111shkb =mid(“141r-111shkb”,6,3) 111 =mid(“141r-111shkb”,4,5) r-111 =len(“141r-111shkb”) 12,什么时候使用: 用来分割指定位置的字符串 len: 通常和别的函数一起使用 tips: 使用来find()灵活制定分割字符窜的地方,文本处理left, right, mid, len,在指定字符窜中找出目标字符窜，并返回目标字符窜在指定字符窜中开始的位置(从左向右查找) 语法 find(find_text, within_text, start_num) e.g., =find(“-”,“141r-111shkb”) 5 =find(“-”,“gpm5500-4500sgkb”) 8 =find(“#”, “abc # 123 # n”,6) 11 =find(“-”,“141r-111uskb”,6) #value!,什么时候使用: 通常与left, right, mid等函数一起使用来去出那些分割点不是固定的字符窜 e.g. =left(b1,find(“-”,b1)-1) =副经理 其中 b1=副经理-丙,find: 提取字符窜非常好的函数,tips: 如何没有找到目标字符窜系统返回 # 使用start_num如果指定字符窜中有两个或两个以上目标字符窜，而且需要返回的位置是第二个,把多个字符窜相加到一个 语法： concatenate(text1, text2,) 最多30个 e.g., =concatenate(“item”,” “,“number”) item number =concatenate(“141r”,“-”,“111”) 141r-111 =concatenate(“=1”,“+”,“1”) =1+1,什么时候使用: 需要两个字符窜相加来表达意思时, e.g., 一个单元格保存姓，一个单元格保存名， 可以相加得出姓名 tips: 符号&具有相同功能 e.g. = “浙江”&“杭州” = 浙江杭州,concatenate: excel中的字符窜相加,可同时对数值和文字进行判断 语法 if(logical_text, value_if_true, value_if_false) e.g. 找出金额大于5000定义为完成目标额度 =if(e25000,“完成“,“没完成“),什么时候使用: 需要根据情况判定输出不同的值 tips: 最多可用7层嵌套，excel 会自动显示每个if的范围,可选,if: 条件测试函数,and: 返回true如果所有条件为真，否则返回false or: 返回false如果所有条件为否， 否则返回true not: 返回判定的相反值 语法： and(logical1, logical2, ) or(logical1, logical2, ) not(logical) e.g., =and(“4/15/2002”=”4/1/2002”,”4/15/2002” true =or(“usa”=“chn”,”usa”=“sha”) = false,什么时候使用: 通常与if语句作为逻辑判断部分同时使用,and, or, not,您可以使用 vlookup 函数搜索某个单元格区域 （区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）的第一列，然后返回该区域相同行上任何单元格中的值。 语法： vlookup(lookup_value, table_array, col_index_num, range_lookup),tips: lookup_value 必须在所寻表格的最左边一列出现，否则会返回 #name? 如果寻表格中第一列中有两个相同的值，只返回第一个与之相匹配的对应数据 输入0或者false来限制查询的精确度，否则有可能得到错误的数据 用 hlookup来查找那些横向排列的表格. 如果 range_lookup 为 true 或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于 lookup_value 的最大值 ，同时的必须按，升序排列 table_array 第一列中的值；否则，vlookup 可能无法返回正确的值。,可选,需要寻找的值,寻找的表格,返回值在表格中处于第几列,输入0或false如果需要寻找得值要完全与条件值相同,vlookup,vlookup: 示例,示例1：非完全匹配,对成绩进行分类， d10=vlookup(c10,$f$6:$h11，3) c10=49, 在查找表格中第一列查找时， f6=0为小于49的最大值，故返回查找表格第3列中与f6同一行中的 h6, 为等级 f,示例2：完全匹配 根据学生的城市查找所在的省份， d11=vlookup(c11,$f$6:$g$10,2,false) 由于在查找表格第一列中找不到香港，所以返回#n/a,数据透视表,假设您已编辑了一个大的 数据列表（例如公司每种产品的销售额）。您现在可以从这些数据中提取一些有意义的信息并找到下列问题的答案： 按区域划分，每种产品的总销售额是多少？ 业绩最好的销售人员是谁？,创建数据透视表,数据-数据透视表和数据透视图,数据透视表向导-下一步,选择数据源区域-下一步,选择生成数据透视表位置：,点击完成,创建数据透视表,将相应的数据项托至数据透视表格区域便得到右边的统计表格，见下图：,将统计金额排序,双击此区域来获得排序窗口 接下来点击高级,选择排序的数据,选择升序或者降序,数据透视表：排序,各自分区域按升序排序,将统计金额排序,双击此区域来获得排序窗口 接下来点击高级,选择排序的数据,打开显示功能 选择需要显示的条件和个数,数据透视表：前/后几项显示,结果只输出按区域排序的前两名的,当需要对表格中填的数据限制在一个数列或数值时，数据有效性可以防止填入不符合的数据格式,1. 在同一工作表中输入区域列表,2. 选择需要对所填数据限制的单元格区域 3. 点击 数据- 有效性,4. 选择序列 5. 选定区域列表-