excel技能培训ppt.ppt

EXCEL操作培训主讲人：朱勇2018.071*Part011EXCEL的基础知识2常用功能及数据操作3EXCEL的常用函数4数据透视表及图表2*Excel用户水平的5个层次新手刚刚开始接触掌握常用操作功能初级熟练使用常用功能+函数+数据透视中级数组公式+VBA简单编程高级EXCEL技能+专业知识+行业经验专家3*Excel2007基础知识014*Excel基础知识工作界面5*Excel基础知识电子表格操作（1）工作簿（Workbooks）：电子表格软件Excel生成的一个文档文件工作簿文件的默认扩展名：.xlsx（未启用宏），启用宏的Excel工作簿文件扩展名：.xlsm默认情况下，可包含三个工作表-03的扩展名：.xls 07、10、13的扩展名：.xlsx（建议使用07以上版本）工作表的标签为Sheet1、Sheet2等，标签可设置颜色工作表的有关操作(1)插入 (2)删除(3)重命名(4)移动或复制 (5)保护工作表(6)隐藏工作表（2）工作表（Spreadsheet）：6*Excel基础知识电子表格操作(3)单元格：1、单元格的访问方式：（单元格的地址）A1方式：列号行号，如B3 R1C1方式：R行C列，如R3C32、列数、行数列标：A,BAAZZ,AAAXFD，共有16384列行号：1,2,3.1048576行最后一个单元格的地址：XFD10485762003版 65536行*65536列7*Excel基础知识电子表格操作单元格区域的表示方法1矩形区域 的种表示：A2:D5 A5:D2 D5:A2 D2:A5 2单行、单列、若干行、若干列区域:6:6 H:H 3:5 D:F 3两区域公共部分：(交)(B4:K6D1:F8)(空格)4多区域：(并)A1:B5,K8,9:9,H:J (逗号)8*Excel基础知识5不同工作表单元格的表示:(!)例：=SUM(Sheet1!A2:B4,Sheet2!F5)6不同工作簿的单元格的表示：(工作簿文件名工作表！单元格)单元格区域的表示方法9*Excel基础知识设置密码打开权限修改权限设置方式保存/另存，选择路径-工具-常规选项保存有密码的工作表10*Part021EXCEL的基础知识2常用功能及数据操作3EXCEL的常用函数4数据透视表及图表11*Excel常用功能及数据操作主要知识点单元格格式设置单元格条件格式查找替换功能（通配符的使用）自动填充选择性粘贴数据有效性排序及筛选（高级筛选）分列12*Excel常用功能及数据操作单元格格式设置Excel单元格有多种格式，包括：“常规、数值、货币、日期、时间、百分比、分数、科学计数、文本、特殊、自定义”等11种格式。选定需要定义格式的单元格，打开Excel菜单：“开始”-“数字”；或者直接点击右键，在下来菜单里面有个“设置单元格格式”13*Excel常用功能及数据操作单元格格式设置不同的数据类型在单元格内默认的对齐方式不同，数值型右对齐，文本型左对齐。其中，“常规”和“文本”两种格式没有格式选项，其他格式都有不同的选项。例如“数值”格式有小数位数、是否选用千分位分隔符、负数的显示方式等选项。14*Excel常用功能及数据操作单元格格式设置1900年1月1日以前的日期怎么输？日期格式:可以把日期系列数显示为定义的日期格式。日期系列数是从1开始的整数，数字1表示1900年1月1日，2表示1900年1月2日等等。15*Excel常用功能及数据操作单元格格式设置对齐字体边框填充保护：只有保护工作表（在“审阅”选项上的“更改”组中，单击“保护工作表”按钮）后，锁定单元格或隐藏公式才有效条件格式 16*Excel常用功能及数据操作单元格条件格式“条件格式”工具是将符合一定的条件的单元格的格式（边框、底色、文本格式、文本颜色等）改变为用户定制的形式。条件格式常用于突出显示某些单元格的内容，或提醒数据录入使用者数据的异常。17*Excel常用功能及数据操作单元格条件格式标记表中克林霉素2.0,具体做法如下：我们选中G2：G20，然后点击“条件格式”的“新建规则”,在弹出的对话框中的“选择规则类型”里面选择“使用公式确定要设置的单元格”。18*Excel常用功能及数据操作单元格条件格式在公式栏中输入=$G32，然后点击“格式”。这里我们要注意对G1的相对引用与绝对引用，如果这里写成$G$3或者G$3格式都不会正常。因为这里的公式时相当于我们所选的G3：G20而言的，填写一个其实就把公式填写了所有单元格。19*Excel常用功能及数据操作查找替换功能（通配符的使用）查找:选择“开始”选项卡中的“编辑”组中的“查找与选择”中的“查找”或Ctrl+F替换定位：F5通配符？*20*Excel常用功能及数据操作填充序列（实例）填充柄的作用数据和公式的复制（注意右下角的十字架）产生自定义序列(操作：选项-高级-自定义序列)产生等差或等比序列选定单元格，直接用鼠标左键拖动填充柄（右键拖动产生快捷菜单）自动填充功能自定义序列21*Excel常用功能及数据操作选择性粘贴通过选择性粘贴达到特定目的：直接使用复制粘贴功能复制数据是按原格式进行复制。除此之外可以使用选择性粘贴来达到待定目的，实现格式、公式的复制，复制进行计算等等。无格式粘贴Excel数据通过数据复制进行数据运算22*Excel常用功能及数据操作编辑数据（实例）通过选择性粘贴达到特定目的：直接使用复制粘贴功能复制数据是按原格式进行复制。除此之外可以使用选择性粘贴来达到待定目的，实现格式、公式的复制，复制进行计算等等。通过数据复制进行行列转置23*Excel常用功能及数据操作数据有效性数据有效性的设置是让制定单元格所输入的数据满足一定的要求。可以有效地防止在单元格中输入无效的数据。1，设置数据有效性2，设置鼠标指向时显示提示 信息3，自定义数据输错后的警告 提示信息设置数据有效性为满足特定的值区域24*Excel常用功能及数据操作数据有效性有些单元格中需要输入的数据有几个固定值可选，此时不必手动输入，而通过数据有效性功能来设置可选择序列，从而实现选择输入。设置数据有效性为可选择序列25*Excel常用功能及数据操作数据的排序普通排序原则：“升序”-从小到大数字：从负到正文本：ASCII码的顺序逻辑值：FALSE，=，=，（不等于）运算结果为TRUE或FALSE35*Excel常用函数公式中对数据源的引用相对引用:=SUM(A3:B5)绝对引用:=SUM($A$3:$B$5)混合引用:=SUM($A3:B$5)引用当前工作表之外的单元格引用其他工作簿的数据源36*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、averageifs37*Excel常用函数（一）逻辑函数（1）IF函数功能：用于根据指定条件来判断其真（TRUE）、假（FALSE），从而返回其相对应的内容，最常用的函数之一。语法：IF(logical_text,value_if_true,value_if_fulse)38*Excel常用函数（一）逻辑函数（2）adn函数功能：And函数用来检验一组数据是否同时都满足条件。注意同时满足。and函数一般和if等逻辑函数结合起来使用。语法：and(logical1,logical2,.)，其中Logical1,logical2为判断条件39*Excel常用函数（一）逻辑函数（3）or函数功能：OR函数只有在所有参数都是FALSE时才返回FALSE，参数中只要有值为TRUE，那么返回值就为TRUE。语法：OR(logical1,logical2,.)，其中参数logical的值为TRUE或FALSE。40*Excel常用函数（二）文本函数（1）“&”及CONCATENATE函数功能：EXCEL中字符串连接或合并函数。语法：concatenate(text1,text2,.）Text1，Text2可以是文本或者数值最多为255项，但是项与项之间必须用逗号隔开41*Excel常用函数（二）文本函数（2）LEFT函数功能：用于返回文本字符串中的第一个字符或前几个字符。语法：LEFT(text,num_chars)42*Excel常用函数（二）文本函数（3）RIGHT函数功能：用于根据所指定的字符数返回文本字符串中最后一个或多个字符。语法：RIGHT(text,num_chars)43*Excel常用函数（二）文本函数（4）MID函数功能：用于返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(text,star_num,num_chars)num_chars：要返回的字符数。如果num_chars超过或等于文本的字符数（包括start处的字符），将返回字符串中从start_num到字符串结束的所有字符。44*Excel常用函数（二）文本函数（5）TEXT函数功能：用于将数值转换为按指定数字格式表示的文本。语法：TEXT(value,format_text)45*Excel常用函数（三）日期函数（1）DATE函数功能：DATE函数的基本构型是DATE（year,month,day），其作用是将提取的数字变为日期格式进行显示。语法：date（year，month，day），这三个参数分别对应年月日。46*Excel常用函数（三）日期函数（2）DATEDIF函数功能：用于计算两个日期之间的年数、月数和天数。语法：DATEDIF(date1,date2,code)47*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*Excel常用函数（四）查找函数（1）VLOOKUP函数功能：用于在表格或数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)HLOOKUP函数的应用：按行查找49*Excel常用函数（五）数学和三角函数（1）ROUND函数功能：用来对数值进行四舍五入。是EXCEL使用频率较高函数之一语法：ROUND(number,num_digits)number表示需要进行四舍五入的数值或单元格内容。num_digits表示需要取多少位的参数。num_digits0时，表示取小数点后对应位数的四舍五入数值。num_digits=0时，表示则将数字四舍五入到最接近的整数。num_digits0时，表示对小数点左侧前几位进行四舍五入。50*Excel常用函数（五）数学和三角函数（2）INT函数功能：将数字向下舍入到最接近的整数。语法：INT(number）其中number是需要进行向下舍入取整的实数。51如=int(3.5)，结果为3=int(-6.2),结果为-7*Excel常用函数（六）统计类函数（1）sum函数功能：主要作用是计算数字之和，返回某一单元格区域中数字、逻辑值及数字的文本表达式之和。语法：SUM(number1,number2,)number1(必选)：表示要求和的第1个数字，可以是直接输入的数字、单元格引用或数组。number2,(可选)52*Excel常用函数（六）统计类函数（2）SUMIF函数功能：用于按照指定条件对若干单元格、区域或引用求和语法：SAMIF(range，criteria,sam_range)53*Excel常用函数（六）统计类函数（3）SUMIFS函数功能：对某一区域内满足多重条件的单元格求和。（07新增函数）语法：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,)54*Excel常用函数（六）统计类函数（4）MAX函数功能：用于返回数据集中的最大数值。语法：MAX(num1,num2,)55*Excel常用函数（六）统计类函数（5）Min函数功能：用于返回数据集中的最小数值。语法：Min(num1,num2,)56*Excel常用函数（六）统计类函数（6）AVERAGE函数功能：Average是英文平均的意思,那么在Excel中,它也是用来计算指定区域中平均值的函数.下面就来分享一下Average函数的用法.语法：average(number1,number2,.)可以与round函数一起使用57*Excel常用函数（六）统计类函数（7）AVERAGEIF函数功能：用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。语法：AVERAGEIF(range,criteria,average_range)可以与round函数一起使用58*Excel常用函数（六）统计类函数（8）AVERAGEIFS函数功能：用于返回某个区域内满足多重条件的所有单元格的平均值（算术平均值）。（07新增函数）语法：AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)59*Excel常用函数（七）数据分析工具点击顶栏的“数据”选项卡，观察左上角是否有“数据分析“这个功能模块（看下图），如果没有，需要先进行添加。60*Excel常用函数（七）数据分析工具首先，单击左上角的office图表，点击”excel选项”，在弹框中，选择“加载项”，在下方的“管理”选项中，选择”excel加载项“，点击转到,在弹出的”加载宏“，界面里，勾选”分析工具库“，点击确定即可。61*Excel常用函数（七）数据分析工具数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类：n数据集中属性的指标l均值Averagel中位数Medianl众数Moden数据的分散属性的指标l极差Rangel方差Variancel标准差StandardDeviationl标准误StandardErrorn数据频数的分布形态指标l偏度Skewnessl峰度Kurtosis62*Excel常用函数（八）函数我不会用，怎么办63*Excel2007数据透视表及图表0464*Excel数据透视表及图表数据透视表 运用数据透视表可以对属性数据进行统计分析。运用数据透视表可以对属性数据进行统计分析。数据透视表是Excel中的功能强大的工具。运用数据透视表可以对大量数据进行快速汇总求和、计数、平均等，创建相应的多维统计表格。n 源数据中工作表第一行各列的名称n 数据中不能有合并单元格n 每列只有一种数据类型数据列表格式要求：65*Excel数据透视表及图表数据透视表选择：菜单栏选择：菜单栏-插入插入-数据透视图。在弹出的数据透视图。在弹出的“创建数据透视表及数据透视图创建数据透视表及数据透视图”对话框使用对话框使用“选择一个表或区域选择一个表或区域”选项，点击表选项，点击表/区域右边的选择框。区域右边的选择框。实际应用中，所要分析的对象通常具有多个不同的属性，例如，公司、客户、分类、地区、物料等。在数据统计表中，这些数据称为“属性数据”。66*Excel数据透视表及图表数据透视表选择好数据源后，点击确认。这样我们就能看到制作出来的数据透视图了，现在我们开始使用它进行数据统计和分析呈现。勾选右侧数据透视表字段列表内分类、销量、收入等字段。67*Excel数据透视表及图表数据透视表将“地区”拖至指定区域，同时将“收入”“销售毛利”拖至数据选项区域，可根据需要进行汇总、计数、平均值等列示。输出结果后，再在空白区域进行复制粘贴值，这样就可以后续加工处理68*Excel数据透视表及图表图表的构成69列举7个大类；每个大类又细分为多种图表类型。柱形图条形图折线图饼图XY散点图面积图圆环图*Excel数据透视表及图表柱形图网络线所在区域为绘图区图表标题垂直（值）轴标签（即刻度值）垂直轴图例水平（类别）轴标签网格线不同颜色的柱形表示不同的数据系列。数据点。70出口内销总计-5,00010,00015,00020,00025,00030,00035,00040,00045,00034,9037,57542,4777,4131,4848,896分地区销售统计求和项:收入合计求和项:销售毛利柱形图是将各个数据点绘制成不同高度的柱形，以显示各种数据的大小，比较直观。*Excel数据透视表及图表设置图表格式71设置图表格式包括设置各个图表元素的格式，例如图表区、绘图区、数据系列、坐标轴、标题、数据标签和图例等。*操作步骤：（1）右键单击图表或者图表元素，弹出选择菜单，如图所示。（2）选择具体命令设置。（3）例如：单击，弹出“设置坐标轴格式”对话框，如图所示。（4）具体设置后，单击“关闭”按钮，图表中的坐标元素根据设置的情况相应改变。Excel数据透视表及图表条形图72和柱形图非常类似，不同点是条形图的分类轴和数据轴与柱形图正好相反。01002003004005001月2月3月4月5月6月7月8月9月10月11月12月月销售额分布图月销售额分布图销额（万元）*Excel数据透视表及图表折线图73折线图可以用来表示数据的连续性，便于观测数据的趋势效果。Axis Title首次第3个月第6个月第9个月第12个月 第18个月 第24个月 第36个月 第48个月0.01.02.03.04.05.06.07.03.93.96.03.93.92.93.94.04.05.23.5PH(3.0-5.0)PH(3.0-5.0)上限下限*Excel数据透视表及图表饼图74饼图对于显示各组成部分之间的大小比例关系非常有用。但只能显示一个数据系列。降糖类药物4%氟苯尼考6%克林霉素37%培南33%其他类2%心血管类药物18%收入分类统计*75THE END谢 谢!*