《讲题EXCEL应用与数据分析课件》由会员分享，可在线阅读，更多相关《讲题EXCEL应用与数据分析课件（21页珍藏版）》请在金锄头文库上搜索。
1、EXCELEXCEL应用与数据分析应用与数据分析讲师：讲师：讲题EXCEL应用与数据分析目录v 基本操作实用技巧基本操作实用技巧基本操作实用技巧基本操作实用技巧v 公式函数必备知识 函数的使用技能和综合演练v 图表制作v 数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析一、制作单斜线及多斜线表头三、设置工作表背景及打印工作表背景五、定义名称六、设置单元格条件格式基本操作实用技巧七、数据有效性二、制作单斜线及多斜线表头四、数据安全性讲题EXCEL应用与数据分析常用技巧Ctrl+1显示显示“单元格格式单元格格式”对话框对话框Alt+Enter在单元格内换行在单元格内换行Ctrl+;输入当
2、前日期输入当前日期ctrl+shift+:可以快速输入当前时间可以快速输入当前时间ctrl+shift+可以快速输入上一个单元格内容可以快速输入上一个单元格内容Shift+F11或或Alt+Shift+F1插入新工作表插入新工作表Shift+f5显示查找替换对话框显示查找替换对话框Ctrl+PageDown移动到工作簿中的下一张工作表移动到工作簿中的下一张工作表Ctrl+PageUp移动到工作簿中的上一张工作表移动到工作簿中的上一张工作表Shift+Ctrl+PageUp选择当前工作表和上一张工作表选择当前工作表和上一张工作表F4重复上一次操作重复上一次操作讲题EXCEL应用与数据分析目录v
3、基本操作实用技巧v 公式函数必备知识公式函数必备知识公式函数必备知识公式函数必备知识 函数的使用技能和综合演练v 图表制作v 数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析函数定义Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。“SUM(number1,number2,.)”按照函数的来源，Excel函数可以分为内置函数和自定义函数。公式：公式：函数与公式既有区别又互相联系。它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。函数参数函数参数函数右边括号中的
4、部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)，甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。函数的返回值：函数的返回值：一个函数的返回值如同参数一样，可以是常量、逻辑值，数组，错误值，单元格引用的等类型。1.常量常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。2.逻辑值逻辑值是比较特殊的
5、一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如公式“=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、
7、输入后在公式编辑栏可看到括号。讲题EXCEL应用与数据分析函数的应用-逻辑函数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；所有参数逻辑值
8、为FALSE,则返回FALSE。语法：OR(logical1,logical2,.)Logical1,logical2,.为需要进行检验的1到30个条件，分别为TRUE或FALSE。讲题EXCEL应用与数据分析函数的应用-查找和引用函数1、Index返回指定的行与列交叉处的单元格引用INDEX(reference,row_num,column_num,area_num)3 3、 match match返回在指定方式下与指定数值匹配的数组中元素的相应位置MATCH(lookup_value,lookup_array,match_type)1、Lookup_value为需要在数据表中查找的数值。2
9、、Lookup_array可能包含所要查找的数值的连续单元格区域。Lookup_array应为数组或数组引用。3、Match_type为数字-1、0或1。指明如何查找。2 2、 offset offset以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。 OFFSET(reference,rows,cols,height,width)4 4、 VLOOKUP VLOOKUP 用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(lookup_value，table_arr
10、ay，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返回从 start_num
11、开始首次找到特定字符或文本字符串的位置上特定字符的编号。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,.)COUNT
14、(value1,value2,.)2 2、COUNTIFCOUNTIF计算区域中满足给定条件的单元格的个数。COUNTIF(range,criteriaCOUNTIF(range,criteria) )3、Sum返回某一单元格区域中所有数字之和。SUM(number1,number2, .)SUM(number1,number2, .)4 4、SumifSumif根据指定条件对若干单元格求根据指定条件对若干单元格求和。和。SUMIF(range,criteria,sum_raSUMIF(range,criteria,sum_rangenge) )讲题EXCEL应用与数据分析函数的应用-统计函数
15、5 5、SumproductSumproduct在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT(array1,array2,arrSUMPRODUCT(array1,array2,array3, .)ay3, .)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个最小值的数组或数字型数个最小值的数组或数字型数据区域。据区域。K为返
17、回的数据在数组或数据区域里的位置（从小为返回的数据在数组或数据区域里的位置（从小到大）。到大）。注意：对数组中的数据并列最小。可能出现第注意：对数组中的数据并列最小。可能出现第K小是小是某个数，某个数，K+1小也是某个数。小也是某个数。讲题EXCEL应用与数据分析其他常用函数row()column()choose()small()讲题EXCEL应用与数据分析目录v 基本操作实用技巧v 公式函数必备知识 函数的使用技能和综合演练v 图表制作图表制作图表制作图表制作v 数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析图表制作技巧认识图表认识图表常见图表的制作技巧常见图表的制作技巧特殊图表
18、制作方案特殊图表制作方案交互式动态图表制作技巧交互式动态图表制作技巧讲题EXCEL应用与数据分析目录v 基本操作实用技巧v 公式函数必备知识 函数的使用技能和综合演练v 图表制作v 数据透视分析数据透视分析数据透视分析数据透视分析 设置不同比较方案讲题EXCEL应用与数据分析数据透视分析功能功能数据透视表可实现组织与分析多维数据透视表可实现组织与分析多维数据。数据。通过向导创建一个交互式表格来自通过向导创建一个交互式表格来自动提取、组织和汇总数据。然后，动提取、组织和汇总数据。然后，可以使用该报表分析数据并进行比可以使用该报表分析数据并进行比较、检测样式和关系并分析趋势。较、检测样式和关系并分析趋势。 创建数据透视表创建数据透视表 构建数据透视表构建数据透视表 不同的比较方案不同的比较方案给报表添加数据给报表添加数据给报表添加页排序字段给报表添加页排序字段按金额排序费用类别按金额排序费用类别 讲题EXCEL应用与数据分析谢 谢！讲题EXCEL应用与数据分析
