excel技能培训PPT课件

,EXCEL操作培训,主讲人：朱勇,2018.07,1,02:42,.,Part,01,2,02:42,.,Excel用户水平的5个层次,新手,刚刚开始接触,掌握常用操作功能,初级,熟练使用常用功能+函数+数据透视,中级,数组公式+VBA简单编程,高级,EXCEL技能+专业知识+行业经验,专家,3,02:42,.,Excel2007基础知识,01,4,02:42,.,Excel基础知识工作界面,5,02:42,.,Excel基础知识电子表格操作,（1）工作簿（Workbooks）：电子表格软件Excel生成的一个文档文件,工作簿文件的默认扩展名：.xlsx（未启用宏），启用宏的Excel工作簿文件扩展名：.xlsm默认情况下，可包含三个工作表-03的扩展名：.xls07、10、13的扩展名：.xlsx（建议使用07以上版本）,工作表的标签为Sheet1、Sheet2等，标签可设置颜色工作表的有关操作(1)插入(2)删除(3)重命名(4)移动或复制(5)保护工作表(6)隐藏工作表,（2）工作表（Spreadsheet）：,6,02:42,.,Excel基础知识电子表格操作,(3)单元格：,1、单元格的访问方式：（单元格的地址）A1方式：列号行号，如B3R1C1方式：R行C列，如R3C3,2、列数、行数列标：A,BAAZZ,AAAXFD，共有16384列行号：1,2,3.1048576行最后一个单元格的地址：XFD10485762003版65536行*65536列,7,02:42,.,Excel基础知识电子表格操作,单元格区域的表示方法,1矩形区域的种表示：A2:D5A5:D2D5:A2D2:A52单行、单列、若干行、若干列区域:6:6H:H3:5D:F3两区域公共部分：(交)(B4:K6D1:F8)(空格)4多区域：(并)A1:B5,K8,9:9,H:J(逗号),8,02:42,.,Excel基础知识,5不同工作表单元格的表示:(!)例：=SUM(Sheet1!A2:B4,Sheet2!F5)6不同工作簿的单元格的表示：(工作簿文件名工作表！单元格),单元格区域的表示方法,9,02:42,.,Excel基础知识,设置密码打开权限修改权限设置方式保存/另存，选择路径-工具-常规选项,保存有密码的工作表,10,02:42,.,Part,02,11,02:42,.,Excel常用功能及数据操作,主要知识点,单元格格式设置单元格条件格式查找替换功能（通配符的使用）自动填充选择性粘贴数据有效性排序及筛选（高级筛选）分列,12,02:42,.,Excel常用功能及数据操作,单元格格式设置,Excel单元格有多种格式，包括：“常规、数值、货币、日期、时间、百分比、分数、科学计数、文本、特殊、自定义”等11种格式。,选定需要定义格式的单元格，打开Excel菜单：“开始”-“数字”；或者直接点击右键，在下来菜单里面有个“设置单元格格式”,13,02:42,.,Excel常用功能及数据操作,单元格格式设置,不同的数据类型在单元格内默认的对齐方式不同，数值型右对齐，文本型左对齐。,其中，“常规”和“文本”两种格式没有格式选项，其他格式都有不同的选项。例如“数值”格式有小数位数、是否选用千分位分隔符、负数的显示方式等选项。,14,02:42,.,Excel常用功能及数据操作,单元格格式设置,1900年1月1日以前的日期怎么输？,日期格式:可以把日期系列数显示为定义的日期格式。日期系列数是从1开始的整数，数字1表示1900年1月1日，2表示1900年1月2日等等。,15,02:42,.,Excel常用功能及数据操作,单元格格式设置,对齐字体边框填充保护：只有保护工作表（在“审阅”选项上的“更改”组中，单击“保护工作表”按钮）后，锁定单元格或隐藏公式才有效条件格式,16,02:42,.,Excel常用功能及数据操作,单元格条件格式,“条件格式”工具是将符合一定的条件的单元格的格式（边框、底色、文本格式、文本颜色等）改变为用户定制的形式。条件格式常用于突出显示某些单元格的内容，或提醒数据录入使用者数据的异常。,17,02:42,.,Excel常用功能及数据操作,单元格条件格式,标记表中克林霉素2.0,具体做法如下：我们选中G2：G20，然后点击“条件格式”的“新建规则”,在弹出的对话框中的“选择规则类型”里面选择“使用公式确定要设置的单元格”。,18,02:42,.,Excel常用功能及数据操作,单元格条件格式,在公式栏中输入=$G32，然后点击“格式”。这里我们要注意对G1的相对引用与绝对引用，如果这里写成$G$3或者G$3格式都不会正常。因为这里的公式时相当于我们所选的G3：G20而言的，填写一个其实就把公式填写了所有单元格。,19,02:42,.,Excel常用功能及数据操作,查找替换功能（通配符的使用）,查找:选择“开始”选项卡中的“编辑”组中的“查找与选择”中的“查找”或Ctrl+F替换定位：F5,通配符？*,20,02:42,.,Excel常用功能及数据操作,填充序列（实例）,填充柄的作用数据和公式的复制（注意右下角的十字架）产生自定义序列(操作：选项-高级-自定义序列)产生等差或等比序列选定单元格，直接用鼠标左键拖动填充柄（右键拖动产生快捷菜单）,自动填充功能自定义序列,21,02:42,.,Excel常用功能及数据操作,选择性粘贴,通过选择性粘贴达到特定目的：直接使用复制粘贴功能复制数据是按原格式进行复制。除此之外可以使用选择性粘贴来达到待定目的，实现格式、公式的复制，复制进行计算等等。,无格式粘贴Excel数据,通过数据复制进行数据运算,22,02:42,.,Excel常用功能及数据操作,编辑数据（实例）,通过选择性粘贴达到特定目的：直接使用复制粘贴功能复制数据是按原格式进行复制。除此之外可以使用选择性粘贴来达到待定目的，实现格式、公式的复制，复制进行计算等等。,通过数据复制进行行列转置,23,02:42,.,Excel常用功能及数据操作,数据有效性,数据有效性的设置是让制定单元格所输入的数据满足一定的要求。可以有效地防止在单元格中输入无效的数据。,1，设置数据有效性2，设置鼠标指向时显示提示信息3，自定义数据输错后的警告提示信息,设置数据有效性为满足特定的值区域,24,02:42,.,Excel常用功能及数据操作,数据有效性,有些单元格中需要输入的数据有几个固定值可选，此时不必手动输入，而通过数据有效性功能来设置可选择序列，从而实现选择输入。,设置数据有效性为可选择序列,25,02:42,.,Excel常用功能及数据操作,数据的排序,普通排序原则：“升序”-从小到大数字：从负到正文本：ASCII码的顺序逻辑值：FALSE，=，（不等于）运算结果为TRUE或FALSE,35,02:42,.,Excel常用函数,公式中对数据源的引用,相对引用:=SUM(A3:B5)绝对引用:=SUM($A$3:$B$5)混合引用:=SUM($A3:B$5),引用当前工作表之外的单元格,引用其他工作簿的数据源,36,02:42,.,Excel常用函数,常用函数介绍,逻辑函数If、and、or、not文本函数&与CONCATENATE：将两个或多个文本字符串合并为一个字符串left、right、mid、lenexact、replace、trim、find、searchtext,日期和时间函数yearmonthdaydatenow与today,统计类函数Sum、count、average、max、min、int、mod、round、stedvSumif、sumifs、countif、countifs、averageif、averageifs,37,02:42,.,Excel常用函数,（一）逻辑函数,（1）IF函数,功能：用于根据指定条件来判断其真（TRUE）、假（FALSE），从而返回其相对应的内容，最常用的函数之一。,语法：IF(logical_text,value_if_true,value_if_fulse),38,02:42,.,Excel常用函数,（一）逻辑函数,（2）adn函数,功能：And函数用来检验一组数据是否同时都满足条件。注意同时满足。and函数一般和if等逻辑函数结合起来使用。,语法：and(logical1,logical2,.)，其中Logical1,logical2为判断条件,39,02:42,.,Excel常用函数,（一）逻辑函数,（3）or函数,功能：OR函数只有在所有参数都是FALSE时才返回FALSE，参数中只要有值为TRUE，那么返回值就为TRUE。,语法：OR(logical1,logical2,.)，其中参数logical的值为TRUE或FALSE。,40,02:42,.,Excel常用函数,（二）文本函数,（1）“&”及CONCATENATE函数,功能：EXCEL中字符串连接或合并函数。,语法：concatenate(text1,text2,.）Text1，Text2可以是文本或者数值最多为255项，但是项与项之间必须用逗号隔开,41,02:42,.,Excel常用函数,（二）文本函数,（2）LEFT函数,功能：用于返回文本字符串中的第一个字符或前几个字符。,语法：LEFT(text,num_chars),42,02:42,.,Excel常用函数,（二）文本函数,（3）RIGHT函数,功能：用于根据所指定的字符数返回文本字符串中最后一个或多个字符。,语法：RIGHT(text,num_chars),43,02:42,.,Excel常用函数,（二）文本函数,（4）MID函数,功能：用于返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。,语法：MID(text,star_num,num_chars),num_chars：要返回的字符数。如果num_chars超过或等于文本的字符数（包括start处的字符），将返回字符串中从start_num到字符串结束的所有字符。,44,02:42,.,Excel常用函数,（二）文本函数,（5）TEXT函数,功能：用于将数值转换为按指定数字格式表示的文本。,语法：TEXT(value,format_text),45,02:42,.,Excel常用函数,（三）日期函数,（1）DATE函数,功能：DATE函数的基本构型是DATE（year,month,day），其作用是将提取的数字变为日期格式进行显示。,语法：date（year，month，day），这三个参数分别对应年月日。,46,02:42,.,Excel常用函数,（三）日期函数,（2）DATEDIF函数,功能：用于计算两个日期之间的年数、月数和天数。,语法：DATEDIF(date1,date2,code),47,02:42,.,Excel常用函数,（三）日期函数,（2）DATEDIF函数,功能：用于计算两个日期之间的年数、月数和天数。,语法：DATEDIF(date1,date2,code),=DATEDIF(A1,TODAY(),Y)计算年数差=DATEDIF(A1,TODAY(),M)计算月数差=DATEDIF(A1,TODAY(),D)计算天数差Y时间段中的整年数。M时间段中的整月数。D时间段中的天数。MDstart_date与end_date日期中天数的差。忽略日期中的月和年。YMstart_date与end_date日期中月数的差。忽略日期中的日和年。YDstart_date与end_date日期中天数的差。忽略日期中的年。,48,02:42,.,Excel常用函数,（四）查找函数,（1）VLOOKUP函数,功能：用于在表格或数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。,语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),HLOOKUP函数的应用：按行查找,49,02:42,.,Excel常用函数,（五）数学和三角函数,（1）ROUND函数,功能：用来对数值进行四舍五入。是EXCEL使用频率较高函数之一,语法：ROUND(number,num_digits)number表示需要进行四舍五入的数值或单元格内容。num_digits表示需要取多少位的参数。num_digits0时，表示取小数点后对应位数的四舍五入数值。num_digits=0时，表示则将数字四舍五入到最接近的整数。num_digits0时，表示对小数点左侧前几位进行四舍五入。,50,02:42,.,Excel常用函数,（五）数学和三角函数,（2）INT函数,功能：将数字向下舍入到最接近的整数。,语法：INT(number）其中number是需要进行向下舍入取整的实数。,51,如=int(3.5)，结果为3=int(-6.2),结果为-7,02:42,.,Excel常用函数,（六）统计类函数,（1）sum函数,功能：主要作用是计算数字之和，返回某一单元格区域中数字、逻辑值及数字的文本表达式之和。,语法：SUM(number1,number2,)number1(必选)：表示要求和的第1个数字，可以是直接输入的数字、单元格引用或数组。number2,(可选),52,02:42,.,Excel常用函数,（六）统计类函数,（2）SUMIF函数,功能：用于按照指定条件对若干单元格、区域或引用求和,语法：SAMIF(range，criteria,sam_range),53,02:42,.,Excel常用函数,（六）统计类函数,（3）SUMIFS函数,功能：对某一区域内满足多重条件的单元格求和。（07新增函数）,语法：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,),54,02:42,.,Excel常用函数,（六）统计类函数,（4）MAX函数,功能：用于返回数据集中的最大数值。,语法：MAX(num1,num2,),55,02:42,.,Excel常用函数,（六）统计类函数,（5）Min函数,功能：用于返回数据集中的最小数值。,语法：Min(num1,num2,),56,02:42,.,Excel常用函数,（六）统计类函数,（6）AVERAGE函数,功能：Average是英文平均的意思,那么在Excel中,它也是用来计算指定区域中平均值的函数.下面就来分享一下Average函数的用法.,语法：average(number1,number2,.),可以与round函数一起使用,57,02:42,.,Excel常用函数,（六）统计类函数,（7）AVERAGEIF函数,功能：用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。,语法：AVERAGEIF(range,criteria,average_range),可以与round函数一起使用,58,02:42,.,Excel常用函数,（六）统计类函数,（8）AVERAGEIFS函数,功能：用于返回某个区域内满足多重条件的所有单元格的平均值（算术平均值）。（07新增函数）,语法：AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2),59,02:42,.,Excel常用函数,（七）数据分析工具,点击顶栏的“数据”选项卡，观察左上角是否有“数据分析“这个功能模块（看下图），如果没有，需要先进行添加。,60,02:42,.,Excel常用函数,（七）数据分析工具,首先，单击左上角的office图表，点击”excel选项”，在弹框中，选择“加载项”，在下方的“管理”选项中，选择”excel加载项“，点击转到,在弹出的”加载宏“，界面里，勾选”分析工具库“，点击确定即可。,61,02:42,.,Excel常用函数,（七）数据分析工具,数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类：数据集中属性的指标均值Average中位数Median众数Mode数据的分散属性的指标极差Range方差Variance标准差StandardDeviation标准误StandardError数据频数的分布形态指标偏度Skewness峰度Kurtosis,62,02:42,.,Excel常用函数,（八）函数我不会用，怎么办,63,02:42,.,Excel2007数据透视表及图表,04,64,02:42,.,Excel数据透视表及图表,数据透视表,运用数据透视表可以对属性数据进行统计分析。,数据透视表是Excel中的功能强大的工具。运用数据透视表可以对大量数据进行快速汇总求和、计数、平均等，创建相应的多维统计表格。,源数据中工作表第一行各列的名称数据中不能有合并单元格每列只有一种数据类型,数据列表格式要求：,65,02:42,.,Excel数据透视表及图表,数据透视表,选择：菜单栏-插入-数据透视图。在弹出的“创建数据透视表及数据透视图”对话框使用“选择一个表或区域”选项，点击表/区域右边的选择框。,实际应用中，所要分析的对象通常具有多个不同的属性，例如，公司、客户、分类、地区、物料等。在数据统计表中，这些数据称为“属性数据”。,66,02:42,.,Excel数据透视表及图表,数据透视表,选择好数据源后，点击确认。这样我们就能看到制作出来的数据透视图了，现在