Excel常用工具分享

供应链计划部2015.5.29Excel常用工具分享二1大纲一数据透视表二两轴折线图三公式四函数五案例2数据透视表备注:使用数据透视表的前提:1.必须有表头,且标题不能为空不能有重复.2.整张工作表不能有合并单元格存在.数据透视表数据透视表备注:当原数据更改时,已做的数据透视不会变.两轴折线图两轴折线图图例两轴折线图两轴折线图步骤一—位置两轴折线图两轴折线图步骤二—成型两轴折线图两轴折线图步骤三—选择次坐标轴两轴折线图两轴折线图步骤四—更改图表类型两轴折线图两轴折线图步骤五—选择折线图两轴折线图两轴折线图步骤六—两轴折线图两轴折线图两轴折线图步骤七—美化,加数据公式EXCEL中最强大最有用的功能,也是有别与WORD或者PPT的所在,这个功能对于分析数据趋向来说,是一个无可取代的存在.公式一.单元格备注:每个单元格的行和列的交叉构成单元格的位置活动单元格公式二.单元格的引用备注:每个单元格的行和列的交叉构成单元格的位置引用格式对应的区域D7D列和第7得交叉处的单元格F1:F124F列中第1行到124行的单元格Q:QQ列全部单元格F:GF列到G列的全部单元格102:102第102行的全部单元格3:10第3行到10行的全部单元格C1:E5C列第1行到E列第5行的单元格公式二.单元格的引用备注:三种引用是对于单元格位置而言,可以省掉反复重写仅行号列标顺序改变的公式的时间相对引用绝对引用混合引用公式三.什么是公式备注:一个完整的公式中可以没有值或常量,但一定要有运算符运算符+单元格引用+值或常量+工作表函数=IF(F5>=3000,”大单”,”小单”)类型名称运算符功能算术加号+加减号-减及负数斜杠/除乘号*乘百分号%显示为百分比脱字符^乘方比较等号=等于大于号>大于小于号<小于大于等于号>=大于或等于小于等于号<=小于或等于不等于号<>不等于文本文本串联符&将两个或多个字符连接起来引用冒号:引用单元格区域逗号,合并多个单元格区域空格

取两个单元格区域的交集优先级运算符1冒号2空格3逗号4百分号5脱字符6乘号或除号7加号或减号8文本运算符9比较运算符公式函数一.什么是函数备注:1.参数与参数之间使用半角逗号进行分隔.当以数组为参数时,各列的数值用逗号隔开,各行的数值用分号隔开.2.手工输入函数公式时需确定当前的输入状态为半角状态.函数就是预先定义好的特殊公式,通过使用一些称之为参数的特定数值来完成特定的计算,分析等处理数据的任务.IF(logical-test,value-if-true,value-if-false)函数名参数1参数2参数3嵌套函数:IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)函数二.函数类型备注:函数类型说明函数符号或其他加载宏和自动化函数创建用户自定义函数VisualBasicforApplications多维数据集函数从AmalysisServices中提取OLAP数据.CUBEKPIMEMBER等数据库函数分析数据清单中的数值是否符合特定条件DDAVERAGE,DCOUNT,DCOUNTA等日期和时间函数在公式中分析和处理日期值和时间值.DATE,DAY,EDATE等工程函数工程分析,在不同的数字系统(十进制,八进制等)间进行数值转换等BESSELK等财务函数一般财务计算ACCRINT等信息函数确定单元格中数据的类型等INFO,CELL等逻辑函数进行真假判断IF,AND,OR等查找和引用函数查找特定数值VLOOKUP,CHOOSE,AREAS等数学和三角函数处理简单的计算ABS,ACOS,ACOSH等统计函数对数据区域进行统计分析AVEDEV等文本函数在公式中处理文字串ASC,BAHTTEXT,CHAR等函数三.EXCEL返回的错误类型备注:当公式错误时,原因不仅仅只是上述内容,建议当公式错误时,查回所用函数的格式,一般找到原因.错误提示错误原因#######列不够宽或使用了负的日期或负的时间#VALUE!使用的参数或操作数类型错误,比如错输入文本,编辑数组却按了[ENTER]键等#DIV/O!数字被0除,如=5/0,使用空白单元格或包含0的单元格引用做除数#NAME?EXCEL未识别公式中的文本,例如函数名拼写错,输入文本时没用双引号等.#N/A数值对函数或公式不可用,使用VLOOKUP,HLOOKUP,MATCH工作函数来定位值前没排序.#REF!单元格引用无效,如引用的单元格被删除函数344?500?EXCEL中有多少个函数?函数四.常用函数—数学和三角函数备注:1.按条件对指定单元格求和.2.第一参数和第三参数只能是单元格,区域引用,不能是数组.3.第二参数如果用了等于某条件.可以使用通配符:问号(?)和星号(*),问号匹配任意单个字符,星号匹配任意一串字符.SUMIF格式:SUMIF(range,critaria,sum-range)函数四.常用函数—数学和三角函数备注:1.返回某一单元格区域中所有数字之和.2.如果参数为错误值或为不能转换为数字的文本,将会导致错误.3.如果还有更多条件,可同时罗列,用星号连接,按[Ctrl+Shift+Enetr]组合键后,显示结果.SUM格式:SUM(number1,number2,…)函数四.常用函数—数学和三角函数备注:返回数字的绝对值ABS格式:ABS(number)函数四.常用函数—逻辑函数备注:1.根据指定的条件返回不同的结果.2.他用嵌套IF时,括号一定要匹配,有时会因为漏掉右括号而生产错误.IF格式:IF(Logical-test,value-true,value-if-false)函数四.常用函数—逻辑函数备注:1.判断指定的多个条件是否全部成立.2.参数最多可以有255个.3.当所列的条件全部满足时,返回TURE,只要有一个条件不满足,则返回FALSE.AND格式:AND(Logical1,logical2,…)函数四.常用函数—逻辑函数备注:1.当指定的任一条件为真时,即返回真2.参数必须能计算为逻辑值,或者为包含逻辑值的数组或引用.3.如果参数包含直接输入的非逻辑值,如文本,空白,空单元格等,将产生错误值#VALUE!.OR格式:OR(logical1,logical2,…)函数四.常用函数—文本函数备注:1.返回文本字符串中的字符个数2.不支持单元格区域引用,否则返回错误值.3.直接引用字符串时,要用半角双引号将字符串括起来.4.始终将每个字符(不管是双字节还是单字节)按1计长度,比如单字节”a”和双字节”啊”的长度都为1.LEN格式:LEN(text)函数四.常用函数—文本函数备注:1.替换字符串中的部分字符(根据字符数).2.第一参数是要替换期部分字符的文本.3.第二参数是待替换字符的起始位置.4.第三参数是被替换字符串的长度,如果忽略,相当于输入新字符串.5.第四参数是替换后的新字符串.REPLACE格式:REPLACE(old-text,start-num,num-chars,new-text)函数四.常用函数—文本函数备注:1.查找字符串起始位置(不区分大小写)2.第一参数表示要查找的文本或文本所在的单元格,如果直接输入文本,需加双引号,可用通配符.3.第二参数包含要查找的文本或文本所在的单元格.4.第三参数指定从哪个字节开始搜索字符,如果省略,则假设其值为1.SEARCH格式:SEARCH(find-text,within-text,start-num)函数四.常用函数—文本函数LEFT格式:LEFT(text,num-chars)备注:1.返回文本值中最左边的字符.2.当第一参数是直接引用输入字符串时,需用半角的双引号将字符串括起来.3.第二参数必须大于或等于零.4.第二参数大于文本长度时,返回全部文本,省略时则假设值为1.函数四.常用函数—文本函数MID格式:MID(text,start-num,num-chars)备注:1.从文本字符串的指定位置起返回特定个数的字符.2.第一参数表示要提取字符的文本字符串,也可以是单元格引用.3.第二参数表示字符串中要提取字符的起始位置,如果大于文本长度,返回空文本.4.第三参数表示MID从文本中返回字符的个数,如果是负数,则返回错误值.函数四.常用函数—文本函数MIDB格式:MIDB(text,start-num,num-bytes)备注:1.从文本字符串的指定位置起返回特定个数的字符.2.用法与MID相似,但,MID以字符为单位,而MIDB以字节为单位.3.半角字符为1个字节,汉字是2个字节.函数四.常用函数—文本函数FIND格式:FIND(find-text,within-text,start-num)备注:1.查找字符串起始位置(区分大小写).2.不允许使用通配符.3.第三参数如果大于所查找的字符的位置时,返回错误值.4.一般情况下忽略第三参数,假设其值为1.函数四.常用函数—文本函数TEXT格式:TEXT(value,format-text)备注:1.设置数字格式并将其转换为文本.2.第二参数不能包含”*”.3.其结果将不再作为数字参与计算.函数四.常用函数—统计函数AVERAGEA格式:AVERAGEA(value1,value2,…)备注:1.计算参数列表中数值的平均值.2.逻辑值和直接输入到参数列表中代表数字的文本纳入计算.3.参数中的TURE作为1计算,FALSE作为0计算,数组,引用参数,空文本作为0计算.4.当按某条件计算平均值时,需按[ctrl+shift+enter].

函数四.常用函数—统计函数AVERAGEIFS格式:AVERAVEIFS(average-range,criteria-rangel,criteria1,criteria-range2,criteria2,…)备注:1.返回满足多重条件的所有单元格的平均值.2.第一参数为实际求和区域.3.第二参数为包含条件的区域.4.第三参数则是第一个条件,在其之后可继续设置条件,可设1-127个条件.函数四.常用函数—统计函数COUNT格式:COUNT(value1,value2,…)备注:1.返回参数列表中的数字个数.2.数字参数,日期参数或代表数字的文本参数,逻辑值都会被计算.3.如果参数是错误值,不能转换为数字的文本,空单元格,将会被忽略.

函数四.常用函数—统计函数COUNTIF格式:COUNTIF(range,criteria)备注:1.计算区域中满足给定条件的单元格的个数.2.第二参数中可以使用通配符.函数四.常用函数—统计函数MAX格式:MAX(number1,number2,…)备注:1.返回一组值中的最大值.2.如果参数包含数字,返回0.

函数四.常用函数—统计函数MIN格式:MIN(value1,value2,…)备注:1.返回参数中的最小值.2.如果参数中不含数字,返回0.

函数四.常用函数—统计函数COUNTIFS格式:COUNTIFS(criteria-range1,criteria1,[criteria-range2,criteria2]…)备注:1.计算区域内符合多个条件的单元格的数量.2.本函数仅适用于Excel2007和Excel2010.3.支持通配符为作条件.4.条件可以使用比较运算符,如>,<,>=…函数四.常用函数—日期和时间函数NOW格式:NOW()备注:1.返回当前的日期和时间.2.该函数没有参数,但必须要有括号(),不论括号中输入什么参数,都返回错误值.3.返回值和该函数所在单元格的格式有关,常规格式下序列号,自定义格式下显示日期和时间格式.

函数四.常用函数—日期和时间函数TODAY格式:TODAY()备注:1.返回当前日期.2.该函数没有参数,但必须要有括号(),不论括号中输入什么参数,都返回错误值.3.TODAY返回的日期可以进行数字运算.4.使用此函数时,必须检查控制面板的日期设置是否准确,否则运算结果可能出错.函数四.常用函数—日期和时间函数MONTH格式:MONTH(serial-number)备注:1.返回某日期对应的月份.2.不论日期以何种日期格式显示,都可以返回值.3.如果引用的单元格是非日期的形式,则返回错误值.

函数四.常用函数—日期和时间函数DAY格式:DAY(serial-number)备注:1.返回某日期对应当月的天数.2.不论日期以何种日期格式显示,都可以返回值.3.如果引用的单元格是非日期的形式,则返回错误值.4.只用于标准日期格式的计算,如果使用”2008,9,25”或”2008\9\25”格式,则返回错误值.函数四.常用函数—日期和时间函数WEEKDAY格式:WEEKDAY(serial-number,return-type)备注:1.返回某日为星期几.2.第一参数如果是日期以外的文本,则返回错误值.3.第二参数确定返回类型的数字,具体类型见上右图.

第二参数返回值1或省略数字1(星期日)到数字7(星期六)2数字1(星期一)到数字7(星期日)3数字1(星期一)到数字6(星期日)函