讲题EXCEL应用与数据分析课件

《讲题EXCEL应用与数据分析课件》由会员分享，可在线阅读，更多相关《讲题EXCEL应用与数据分析课件（21页珍藏版）》请在装配图网上搜索。

1、讲题EXCEL应用与数据分析EXCELEXCEL应用与数据分析应用与数据分析讲师：讲师：讲题EXCEL应用与数据分析目录v 公式函数必备知识 函数的使用技能和综合演练v 图表制作v 数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析一、制作单斜线及多斜线表头三、设置工作表背景及打印工作表背景五、定义名称六、设置单元格条件格式基本操作实用技巧七、数据有效性二、制作单斜线及多斜线表头四、数据安全性讲题EXCEL应用与数据分析常用技巧Ctrl+1 显示显示“单元格格式单元格格式”对话框对话框Alt+Enter 在单元格内换行在单元格内换行Ctrl+; 输入当前日期输入当前日期ctrl+shi

2、ft+: 可以快速输入当前时间可以快速输入当前时间ctrl+shift+ 可以快速输入上一个单元格内容可以快速输入上一个单元格内容Shift+F11 或或 Alt+Shift+F1 插入新工作表插入新工作表Shift+f5 显示查找替换对话框显示查找替换对话框Ctrl+Page Down 移动到工作簿中的下一张工作表移动到工作簿中的下一张工作表Ctrl+Page Up 移动到工作簿中的上一张工作表移动到工作簿中的上一张工作表Shift+Ctrl+Page Up 选择当前工作表和上一张工作表选择当前工作表和上一张工作表F4 重复上一次操作重复上一次操作讲题EXCEL应用与数据分析目录基本操作实用

3、技巧v 函数的使用技能和综合演练v 图表制作v 数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析函数定义Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式 。 “SUM(number1,number2,.)”按照函数的来源，Excel 函数可以分为内置函数和自定义函数。公式：公式：函数与公式既有区别又互相联系。它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。函数参数函数参数函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗

4、号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE 或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)，甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。函数的返回值：函数的返回值： 一个函数的返回值如同参数一样，可以是常量、逻辑值，数组，错误值，单元格引用的等类型。1.常量常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。2.逻辑值逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如 公式

5、“=IF(A3=0,“0,A2/A3)”3.数组数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel 中有常量和区域两类数组。SUM（A2：A10），sum（1，2；3，4）4.错误值错误值使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”，如果其中的参数是#NUM!，则返回数值“6”。5.单元格引用单元格引用 单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使

6、用同一个单元格的数据。单元格引用分为相对引用、绝对引用和混合引用三种类型。讲题EXCEL应用与数据分析数组及数组公式 数组概念 由文本、数值、日期、逻辑值、错误值等元素组成的集合 常量数组和内存数组 数组分类一维数组，行数组(垂直数组)，列数组(水平数组)1,2,3或“a”,”b”,”c”,false,true二维数组,多行多列数组0,”差”;60,”中”;80,”优”讲题EXCEL应用与数据分析数组公式 数组公式跟普通公式区别1、数组公式是同时对一组或几组数同时处理。2、数组公式是通过同时按下ctrl+shift+enter三键进行输入3、输入后在公式编辑栏可看到括号。讲题EXCEL应用与数

7、据分析函数的应用-逻辑函数 IF用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。语法：IF(logical_test，value_if_true，value_if_false)。AND所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。语法：AND(logical1,logical2, .)Logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。OR在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE； 所有参数逻辑值为FALSE,则返

8、回FALSE。语法：OR(logical1,logical2,.)Logical1,logical2,. 为需要进行检验的 1 到 30 个条件，分别为 TRUE 或 FALSE。讲题EXCEL应用与数据分析函数的应用-查找和引用函数 1、Index返回指定的行与列交叉处的单元格引用INDEX(reference,row_num,column_num,area_num)3 3、 matchmatch返回在指定方式下与指定数值匹配的数组中元素的相应位置MATCH(lookup_value,lookup_array,match_type)1、Lookup_value 为需要在数据表中查找的数值。2

9、、Lookup_array 可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。3、Match_type 为数字 -1、0 或 1。指明如何查找。 2 2、 offsetoffset以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。 OFFSET(reference,rows,cols,height,width)4 4、 VLOOKUP VLOOKUP 用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(lookup_value，tab

10、le_array，col_index_num，range_lookup) 讲题EXCEL应用与数据分析函数的应用-文本处理函数1、SUBSTITUTE在文本字符串中用 new_text 替代 old_text。SUBSTITUTE(text,old_text,new_text,instance_num)2、FIND用于查找其他文本字符串 (within_text) 内的文本字符串 (find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。 FIND(find_text,within_text,start_num)3、SEARCH返回从 star

11、t_num 开始首次找到特定字符或文本字符串的位置上特定字符的编号。SEARCH(find_text,within_text,start_num)4、TRIM除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数 TRIM。TRIM(text) 讲题EXCEL应用与数据分析函数的应用-文本处理函数5、LEN返回文本字符串中的字符数。LEN(text) 6、RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。RIGHT(text,num_chars)7 7、LEFTLEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符。基

12、于所指定的字符数返回文本字符串中的第一个或前几个字符。 LEFT(text,num_chars) 8 8、REPTREPT按照给定的次数重复显示文本。可以通过函数 REPT 来不断地重复显示某一文本字符串，对单元格进行填充。REPT(text,number_times) 讲题EXCEL应用与数据分析函数的应用-文本处理函数9、MIDMID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MID(text,start_num,num_chars)10、MOD 返回两数相除的余数。结果的正负号与除数相同。MOD(number,divisor)11、CONCATENATE将几个文本

13、字符串合并为一个文本字符串。CONCATENATE (text1,text2,.)也可以用 &（和号）运算符代替函数 CONCATENATE 实现文本项的合并)12、REPLACEREPLACE 使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。 REPLACE(old_text,start_num,num_chars,new_text) 讲题EXCEL应用与数据分析函数的应用统计函数1 1、CountCount返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。COUNT(value1,value2,

14、.)COUNT(value1,value2,.)2 2、COUNTIFCOUNTIF计算区域中满足给定条件的单元格的个数。COUNTIF(range,criteria)COUNTIF(range,criteria)3、Sum返回某一单元格区域中所有数字之和。SUM(number1,number2, .)SUM(number1,number2, .)4 4、SumifSumif根据指定条件对若干单元格求根据指定条件对若干单元格求和。和。SUMIF(range,criteria,sum_raSUMIF(range,criteria,sum_range)nge)讲题EXCEL应用与数据分析函数的应用

15、-统计函数5 5、SumproductSumproduct在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT(array1,array2,arSUMPRODUCT(array1,array2,array3, .)ray3, .)7 7、DSUM(database,field,criteria)DSUM(database,field,criteria)返回列表或数据库的列中满足指定条件的数字返回列表或数据库的列中满足指定条件的数字之和。之和。DSUM(database,field,criteria)Database 构成列表或数据库的单元格区域。数据构成列表或数据库

16、的单元格区域。数据库是包含一组相关数据的列表，其中包含相关库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。信息的行为记录，而包含数据的列为字段。Field 指定函数所使用的数据列。指定函数所使用的数据列。Criteria 为一组包含给定条件的单元格区域。为一组包含给定条件的单元格区域。6 6、smallsmall返回数据集中第返回数据集中第 k 个最小值。使用此函数可以返回个最小值。使用此函数可以返回数据集中特定位置上的数值。数据集中特定位置上的数值。SMALL(array,k)Array 为需要找到第为需要找到第 k 个最小值的数组或数字型个最小值的数组或数字型

17、数据区域。数据区域。K 为返回的数据在数组或数据区域里的位置（从为返回的数据在数组或数据区域里的位置（从小到大）。小到大）。注意：对数组中的数据并列最小。可能出现第注意：对数组中的数据并列最小。可能出现第K小小是某个数，是某个数，K+1小也是某个数。小也是某个数。 讲题EXCEL应用与数据分析其他常用函数 row() column() choose() small()讲题EXCEL应用与数据分析目录基本操作实用技巧v 公式函数必备知识 函数的使用技能和综合演练v v 数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析图表制作技巧认识图表认识图表常见图表的制作技巧常见图表的制作技巧特殊图

18、表制作方案特殊图表制作方案交互式动态图表制作技巧交互式动态图表制作技巧讲题EXCEL应用与数据分析目录基本操作实用技巧v 公式函数必备知识 函数的使用技能和综合演练v 图表制作 设置不同比较方案讲题EXCEL应用与数据分析数据透视分析功能功能数据透视表可实现组织与分析多维数据透视表可实现组织与分析多维数据。数据。通过向导创建一个交互式表格来自通过向导创建一个交互式表格来自动提取、组织和汇总数据。然后，动提取、组织和汇总数据。然后，可以使用该报表分析数据并进行比可以使用该报表分析数据并进行比较、检测样式和关系并分析趋势。较、检测样式和关系并分析趋势。 创建数据透视表创建数据透视表 构建数据透视表构建数据透视表 不同的比较方案不同的比较方案 给报表添加数据给报表添加数据 给报表添加页排序字段给报表添加页排序字段 按金额排序费用类别按金额排序费用类别 讲题EXCEL应用与数据分析谢 谢！