EXCEL数据分析与主要函数课件

1、EXCEL函数 与数据分析Excel函数与数据处理第1页，共85页。EXCEL函数 与数据分析第2页，共85页。3数据分析是业务发展的推动力随着公司的快速发展，对管理人员的数据分析能力提出了更高的要求，提高数据分析能力是提高管理能力和水平的重要内容。当前企业数据当中的大部分都属于非结构化数据，比如独立报表、零散数据、自由文本等，致使企业不能充分利用。另一方面，企业数据量非常大，而其中真正有价值的信息却很少，因此管理人员就要从大量的数据中经过深层分析，获得有利于企业运营的信息 ，供领导决策。为贯彻落实“科学发展观”思想，深入开展“管理提升月”活动，提高管理人员的数据挖掘和数据分析能力，特进行本次

2、交流，共同探讨利用最常用的办公软件来管理和分析数据，提升管理水平 ，增强企业核心竞争力。第3页，共85页。4总 目 录公式与函数常用函数用法应用举例第4页，共85页。5一、公式与函数公式的特性公式的输入公式中的运算符公式中的数据类型公式的复制和移动公式的调整函数格式内置函数第5页，共85页。6公式的特性公式的基本特性：公式的输入是以“ =”开始，公式的计算结果显示在单元格中，公式本身显示在编辑栏中。(工具选项菜单)如：=1+2+6 （数值计算） =A1+B2 （引用单元格地址） 第6页，共85页。7公式的输入=IF(and(A1A2,B1B2),100*A1/A2, 100*B1/B2) （函

3、数计算）=“ABC”&”XYZ” (字符计算，结果:ABCXYZ) =25+count(A1:C4) (混和计算)公式中的自变量变化，则计算结果会自动调整第7页，共85页。8公式中的运算符算术运算符：+ - * / %字符运算符：&比较运算符：= = 逻辑运算符：and or not 以函数形式出现优先级顺序：算术运算符字符运算符比较运算符逻辑函数符 (使用括号可确定运算顺序) 第8页，共85页。9公式中的数据类型输入公式要注意公式中可以包括：数值和字符、单元格地址、区域、区域名字、函数等不要随意包含空格公式中的字符要用半角引号括起来公式中运算符两边的数据类型要相同 如：=“ab”+25 出错

4、 #VALUE第9页，共85页。10公式的复制和移动复制移动或公式时，公式会作相对调整公式的复制：使用填充柄菜单编辑复制/粘贴 或复制/选择性粘贴 第10页，共85页。11公式的调整相对地址 在公式复制时将自动调整绝对地址 在公式复制时不变。例如：C3单元的公式=$B$1+$B$2复制到D5中，D5单元的公式=$B$1+$B$2混合地址 在公式复制时绝对地址不变，相对地址按规则调整。第11页，共85页。12函数格式函数是Excel附带的预定义或内置公式函数的格式：函数名(参数1，参数2,.)函数中的参数可以是：数值、字符、逻辑值、表达式、单元格地址、区域、区域名字等没有参数的函数，括号不能省略

5、 例如: PI( )，RAND(), NOW( )第12页，共85页。13Excel内置函数数学和三角函数统计函数文本函数日期与时间函数逻辑函数财务函数数据库工作表函数工程函数信息函数查找与引用函数第13页，共85页。14二、常用函数用法 重点介绍50个常用函数的功能、格式、参数和用法，包括：数学函数（ABS、MOD、INT、ROUND、ROUNDDOWN、 ROUDUP、RAND、SQRT 、 SUBTOTAL ）三角函数（SIN、COS、PI）统计函数（AVERAGE、COUNT、MAX、MIN、SUM、RANK、 LARGE 、 FREQUENCY）文本函数（TEXT、MID、LEFT、

6、RIGHT、TRIM、VALUE、LEN 、 CONTAENATE ）日期函数（NOW、DATE、DAY、MONTH、TODAY、WEEKDAY、DATEIF）条件函数（IF、SUMIF、COUNTIF）逻辑函数（OR、AND）查找函数（COLUMN、 INDEX、MATCH、 VLOOKUP ）财务函数（ PMT、 PV、NPV、IRR）数据库函数(DCOUND )其他函数（ ISBLANK 、ISERROR）第14页，共85页。15数学函数ABS主要功能：求出相应数字的绝对值。 使用格式：ABS(number) 参数说明：number代表需要求绝对值的数值或引用的单元格。 应用举例：如果在

7、B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。 特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。 第15页，共85页。16数学函数MOD主要功能：求出两数相除的余数。 使用格式：MOD(number,divisor) 参数说明：number代表被除数；divisor代表除数。 应用举例：输入公式：=MOD(13,4)，确认后显示出结果“1”。 特别提醒：如果divisor参数为零，则显示错误值“#DIV/0!”；MOD函数可以借用函数INT来表示：上述

8、公式可以修改为：=13-4*INT(13/4)。 第16页，共85页。17数学函数INT主要功能：将数值向下取整为最接近的整数。 使用格式：INT(number) 参数说明：number表示需要取整的数值或包含数值的引用单元格。 应用举例：输入公式：=INT(18.89)，确认后显示出18。 特别提醒：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为-19。第17页，共85页。18数学函数 ROUND将数字“12.3456”按照指定的位数进行四舍五入，可以在D3单元格中输入以下公式：“=ROUND(B3,C3)“第18页，共85页。19数学函数 ROUNDDOW

9、N向下舍入函数。例如：出租车的计费标准是：起步价为5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。输入不同的公里数，然后计算其费用。可以在C3单元格中输入以下公式：=IF(B3=80,A,IF(C2=60,B,C),IF(D2=80,A,IF(D2=60,B,C),IF(E2=80,A,IF(E2=60,B,C)，然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。 第69页，共85页。70第70页，共85页。71利用函数进行等级评定(续)也可以在F2单元格中输入：=IF(C2=80,A,IF(C2=60,

10、B,C)&IF(D2=80,A,IF(D2=60,B,C)&IF(E2=80,A,IF(E2=60,B,C)，然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。 第71页，共85页。72第72页，共85页。73统计学生考试成绩 第73页，共85页。74选中f23:f26单元格，拖动填充句柄向右填充公式至h26单元格，松开鼠标，各学科的统计数据就出来了。至于各科分数段人数的统计，那得先选中f28:f35单元格，在编辑栏中输入公式：=FREQUENCY(F$3:F$22,$C$28:$C$35)。然后按下“Ctrl+Shift+Enter”

11、快捷键，可以看到在公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在K列中的那些数字，就是我们统计各分数段时的分数分界点。现在再选中f28:f35单元格，拖动其填充句柄向右至h列，那么，其它学科的分数段人数也立即显示在我们眼前了。 第74页，共85页。75自动录入性别在d3单元格中输入“IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女,男),IF(MOD(MID(C3,15,1),2)=0,女,男)”。回车后即可在单元格获得该职工的性别，而后只要把公式复制到D3、D4等单元格，即可得到其他职工的性别。 第75页，共85页。76根据身份

12、证号提取出生日期在单元格中输入公式“=IF(LEN(C3)=15,CONCATENATE(19,MID(C3,7,2),年,MID(C3,9,2),月,MID(C3,11,2),日),CONCATENATE(MID(C3,7,4),年,MID(C3,11,2),月,MID(C3,13,2),日)”。 第76页，共85页。77年龄统计 第77页，共85页。78位次阈值统计 假设C2:C21区域存放着学生的考试成绩，首先在D列选取空白单元格D3，在其中输入公式“=PERCENTILE(C2:C21,0.67)”。其中D2作为输入百分点变量的单元格，如果你在其中输入0.33，公式就可以返回名次达到前

13、1/3所需要的成绩。第78页，共85页。79让Excel按人打出工资条 新建一Excel文件，在sheet1中存放工资表的原始数据，假设有N列。第一行是工资项目，从第二行开始是每个人的工资。在sheet2中我们来设置工资条。根据实际情况，工资条由三行构成，一行对应工资项目，一行对应一个人的工资数据，然后是一个空行用来方便切割。这样三行构成一个工资条。工资项目处在行号除以3余数为1的行上；空行处在行号能整除3的行上。以上两行不难设置，关键是工资数据行，牵扯到sheet1与 sheet2中数据的对应，经分析不难看出“sheet2中的数据行=INT(sheet1中的数据行+4)/3)”。第79页，共

14、85页。80这样我们在sheet2的A1单元格中输入公式“=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT(ROW()+4)/3),COLUMN()”。确认后选择A1单元格，把鼠标放在A1单元格的右下角，鼠标变成“+”时，向右拖动鼠标自动填充至N列，这样工资条中的第一行就出来了。选定A1：N1，把鼠标放在N1单元格的右下角，鼠标再次变成“+”时，向下拖动鼠标自动填充到数据的最后一行，工资条就全部制作完成了。该公式运用IF函数，对MOD函数所取的引用行号与3的余数进行判断。如果余数为0，则产生一个空行；如

15、果余数为1，则固定取sheet1中第一行的内容；否则运用INDEX函数和INT函数来取Sheet1对应行上的数。最后来设置一下格式，选定A1：N2设上表格线，空行不设。然后选定A1：N3，拖动N3的填充柄向下自动填充，这样有数据的有表格线，没有数据的没有表格线。最后调整一下页边距，千万别把一个工资条打在两页上。 第80页，共85页。81Word表格计算用法： 点击“插入”“域”“公式”，弹出“公式”对话框。 可从“粘贴函数”下拉列表框中选择相应的函数如SUM、AVERAGE、COUNT、MAX等，也可直接输入函数名。 函数参数为ABOVE（数据在单元格之上）、LEFT（左）、RIGHT（右）中

16、其一。 还可以在“数字格式” 下拉列表框中选择相应的数字格式。举例：=sum(above) 计算单元格之上本列单元格数据的总和。提醒：当原数据发生变化时，结果单元格内的数据不会随之发生变化，需要右击单元格选择“更新域”；公式中可以包含运算符，如=sum(above)/count(above)第81页，共85页。82下次交流内容一、利用EXCEL函数进行统计分析： RANK(排名)、 FREQUENCY(频数)、 MODE(众数)、 MEDIAN(中位数)、 CORREL(相关系数)、 VAR(样本方差)、 VARP(总体方差)、 FTEST(F检验值)、 CRITBINOM(质量检验)、 TRIMMEAN(内部平