Excel 技能培训-初级篇ppt课件

1、Microsoft Excel-2-内部文档-2-大纲-3-内部文档-3-我们要学什么-4-内部文档-4-基础知识操作技巧常用函数数据透视表现实问题转换报表图表数据管理数据分析要学的内容以后讲我们要学什么 1985 1985年年Microsoft ExcelMicrosoft Excel问世，被公认为是功能最完整、技术最先进和使用最简便的电问世，被公认为是功能最完整、技术最先进和使用最简便的电子表格软件。子表格软件。 Excel Excel可以创建和修改工作表、图表，以及分析管理数据。可以创建和修改工作表、图表，以及分析管理数据。分析分析-5-内部文档-5-Excel基础知识-6-内部文档-6

2、-Excel界面-7-内部文档-7-Excel基本介绍 一个EXCEL文件是由“文件-工作簿-工作表-行、列(X,Y)-单元格构成的。 工作簿：就是处理和存储数据的文件； 一个工作簿可以创建256个工作表； 一个工作表包括65536行,256列； 一个工作表包括65536*256个单元格 EXCEL是一个容量非常大的数据表格！l注：注：EXCEL2019EXCEL2019在数据容量上超过了在数据容量上超过了EXCEL2019EXCEL2019lEXCEL2019EXCEL2019一个工作表包括一个工作表包括10485761048576行，行，1638416384列；列；lEXCEL2019EX

3、CEL2019一个工作表包括一个工作表包括1048576 1048576 * * 16384 16384个单元格个单元格-8-内部文档-8-Excel常用快捷键单元格复制： “Ctrl + C”单元格粘贴： “Ctrl + V”单元格剪切： “Ctrl + X” 撤销最后一次操作：“Ctrl + Z”复制上一个单元格：“Ctrl + ”向下填充：“Ctrl + D”向右填充：“Ctrl + R” 获得帮助信息： “F1”编辑当前单元格： “F2”重复上一步操作： “F4”随机数：randbetween（*，*）-9-内部文档-9-Excel常用技巧-10-内部文档-10-Excel常用技巧-1

4、1-内部文档-11-EXCEL数据输入技巧-1数据的定义：数据是数值、文字、日期、时间、公式和符号的总称。数据的分类：数据分为字符数据和数值数据。数据输入常用方法：1、非数值数字的输入：数字前加单引号或设置单元格属性为“文本”如：身份证号码输入2、分数的输入：“=+ 0 + 分子 + / + 分母” 如：直接输1/3，EXCEL默认为1月3日，应该写成01/33、当前日期的输入：“Ctrl + ；”(分号)4、当前时间的输入：“Ctrl + ：”(冒号)5、批量录入相同数据：“ Ctrl + Enter”先选择需要批量输入相同数据的区域，在输入数据，使用快捷点Ctrl+Enter6、在单元格中

5、换行：“Alt + Enter”留意留意“；/ -”均为英文均为英文状态下的符号状态下的符号-12-内部文档-12-EXCEL数据输入技巧-27、快速输入大写中文数字 将光标移至需要输入大写数字的单元格中。在单元格中输入相应的小写数字。右击该单元格，点击“设置单元格格式”，从弹出的“单元格格式对话框中选择“数字选项；然后从“特殊列表框中选择“中文大写数字选项。最后单击“确定按钮即可.8、快速隐藏 在打印工作表时，我们有时需要把某些行或者列隐藏起来，可是用菜单命令或调整行号(列标)分界线的方法比较麻烦,有一个简单方法：在英文状态下，按“Ctrl+9或“Ctrl+0组合键，就可以快速隐藏光标所在的

6、行或列。 9、快速选定单元格在Excel中，如果要选定已选定单元格周围的单元格，可以按上下方向键进行移动，可为了节省时间，让操作的速度更高。-13-内部文档-13-快速移动到活动单元格在拖动鼠标的同时，按住“Ctrl键选择不连续区域在拖动鼠标的同时，按住“Shift键可以选中两点之间区域“Ctrl + Shift + 8” 或“Ctrl + * ”可以选中当前单元个周围连续的矩形区域“Ctrl + Home” 将光标移动到第一个单元格“Ctrl + End ”将光标移动到表格最后一个单元格“Ctrl + 向右方向键可将光标移动到当前区域最右列(其他方向箭头依此类推)“Ctrl + Shift

7、+ 向下箭头选择当前单元格至当前区域最后一行的内容-14-内部文档-14-查找功能常用技巧 1.模糊查找 按“CTRL+F ”进入查找，在查找里直接输查找内容为模糊查找。 2.精确查找 在查找里点开选项，将“单元格匹配的勾打上为精确查找 3.值查找 在查找里点开选项，“查找范围选择值为“值查找。（如果单元格里是公式那必须用值查找）-15-内部文档-15-自动填充-1自动填充，就是根据已输入的数据，按照系统规定的规则或用户指定的规律，自动将数据填充到选定的区域。 如果单元格中是文本、数据填充柄起复制的作用如果单元格中是日期填充柄起到填充序列的作用-16-内部文档-16-自动填充-2初始选择初始选

8、择扩展序列扩展序列1，2，34，5，69：0010：00，11：00，12：00MonTue，Wed，Thu星期一星期二，星期三，星期四JanFeb，Mar，Apr一月，四月七月，十月，一月1月1日，3月1日5月1日，7月1日，9月1日2019，20002019，2019，2019Text1，textAText2，textA ，Text3，textA产品1产品2，产品3-17-内部文档-17-自动填充-3要填充比较复杂的数据时，可以试试右键来拖动填充柄选中单元格-编辑-填充-序列-18-内部文档-18-数据分列的妙用 1用用ExcelExcel输入数据时，如果在一行内输入内容过长，可以采取快速

9、分列方输入数据时，如果在一行内输入内容过长，可以采取快速分列方法。具体操作如下法。具体操作如下: : 在选中单元格中输入数据或文字在选中单元格中输入数据或文字, ,然后选定所要分割的数据区域然后选定所要分割的数据区域; ; 单击菜单栏【数据】单击菜单栏【数据】 - -【分列】【分列】 ; ; 在弹出的对话框中选择【固定宽度】在弹出的对话框中选择【固定宽度】, ,点击点击“下一步下一步” ;” ; 在【数据预览】中在【数据预览】中, ,按住分列线按住分列线, ,移动至指定位置移动至指定位置, ,点击点击“下一步下一步” ;” ; 选择【列数据格式】选择【列数据格式】, ,点击点击“完成完成”.”

10、.电话号码电话号码交易时间1389227734020191001 08:21:501874032275020191001 08:38:501377291420820191001 08:39:481372079442020191001 08:49:071348829488620191001 09:08:391829125964920191001 09:10:221870022235120191001 09:42:59电话号码电话号码交易日期交易日期交易时间13892277340201910018:21:5018740322750201910018:38:501377291420820191001

11、8:39:4813720794420201910018:49:0713488294886201910019:08:3918291259649201910019:10:2218700222351201910019:42:59-19-内部文档-19-数据分列的妙用 2批量转换日期格式批量转换日期格式 以前在以前在ExcelExcel中输入职工出生时间时，为了简单都输入成中输入职工出生时间时，为了简单都输入成“yymmdd“yymmdd方式，但上级部门一律要求输入成方式，但上级部门一律要求输入成“yyyy-mm-“yyyy-mm-dddd格式，那么一千多名职工出生时间肯定不能每个手工转化。格式，那么

12、一千多名职工出生时间肯定不能每个手工转化。 最快速的方法是：先选定要转化的区域。点击【数据】最快速的方法是：先选定要转化的区域。点击【数据】【分列】，出现【文本分列向导】对话框。勾选【分列】，出现【文本分列向导】对话框。勾选“固定宽度固定宽度”，连续两次点击【下一步】按钮，在步骤三对话框的【列数据格式】连续两次点击【下一步】按钮，在步骤三对话框的【列数据格式】中，选择中，选择“日期日期”，并选定，并选定“YMD“YMD方式，按下【完成】按钮，方式，按下【完成】按钮，以前的文本即转化成了需要的日期了。以前的文本即转化成了需要的日期了。-20-内部文档-20-选择性粘贴 1选择要行列互换的数据区域

13、，单击【复制】按钮或按下【Ctrl+C】键复制所选区域。选择行列互换后数据所在区域的第一个单元格区域左上角）。单击【编辑】菜单上的【选择性粘贴】在对话框中选择【转置】复选框，单击【确定】。-21-内部文档-21-选择性粘贴 2对单元格进行同增同减操作 在Excel中，如果要对某一单元格或某一区域中的每个单元格中的数值进行同加、同减、同乘或同除操作时，可以使用“选择性粘贴功能轻松实现。 例如：让选定区域中的每个单元格都同乘以2，首先在选定区域外的某个单元格中输入2，选择这个单元格并复制 ;然后选择要进行同乘操作的单元格区域，单击“编辑菜单，选择“选择性粘贴命令，在“运算栏中选择“乘单选项，单击“

14、确定按钮.结果显示在了每个单元格中 。 使用这个功能还可以对单元格进行自身操作，例如可以进行单元格的自身相加、相减、相乘以及相除 。-22-内部文档-22-定位在在ExcelExcel中，经常要选定空白单元格，逐个选定比较麻烦，中，经常要选定空白单元格，逐个选定比较麻烦，如果使用下面的方法就方便多了：翻开如果使用下面的方法就方便多了：翻开“编辑编辑定位定位”，在在“定位窗口中，按下定位窗口中，按下“定位条件按钮；选择定位条件按钮；选择“定位定位条件中的条件中的“空值空值”，再按，再按“确定确定”，空白单元格即被全，空白单元格即被全部选定。如果要选定只含数据的单元格，在上面方法的部选定。如果要选

15、定只含数据的单元格，在上面方法的“定位条件窗口中，选择定位条件窗口中，选择“常量常量”，再点，再点“确定确定”，含，含有数据的单元格全部选定。有数据的单元格全部选定。 快速选定快速选定“空白和空白和“数据单元格数据单元格-23-内部文档单元格的引用技巧 相对引用 单元格地址会随公式所在单元格的位置变更而改变。如：“A1 + B1” 绝对引用 单元格地址将不随公式所在单元格的位置变化而改变。如：“$A$1 + $B$1” 混合引用 只给行号或列号加“$”符号。如：A$1，$B2,复制后部分地址改变。-24-内部文档显示标题技巧 1在打印时让每页都显示标题在Excel中制作一个长表格时，默认打印时

16、，并不是每页都会显示出顶端标题行和左侧标题列，它只会在第一页上显示，那如何能让表格打印出来时，每页都带有标题栏呢？可以这样设置：翻开“文件菜单，选择“页面设置选项，弹出“页面设置对话框，单击“工作表选项卡，在“打印标题栏中的“顶端标题行项和“左侧标题列项中设置好要显示的标题单元格，单击“确定按钮。这样在打印长表格时，每页都会显示出标题的内容了。 -25-内部文档显示标题技巧 2编辑时让每页都显示标题在编辑状态时向后翻页会看不到第一行的标题行，在操作数据时就会很不方便。这个问题很简单就可以解决，方法如下： 选中标题所在行的下面一行单元格，翻开“窗口菜单，选择“冻结窗格选项，上面一行就被固定了，无

17、论怎么向下翻页，它都始终在第一行不会移动。-26-内部文档-26-使用Excel公式与函数-27-内部文档-27-公式和函数什么是函数Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是SUM(number1,number2 ,.)”。其中“SUM称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。什么是公式函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“

18、=SUM(E1:H1)*A1+26为例，它要以等号“=”开场，其内部可以包括函数、援用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1则是对单元格A1的引用(使用其中存储的数据)，“26则是常量，“*”和“+”则是算术运算符。-28-内部文档常用函数SUM SUM 用途：返回某一单元格区域中所有数字之和。 语法：SUM(number1，number2，.)。 参数：Number1，number2，.为1到N个需要求和的数值。 用法举例：-29-内部文档常用函数SUMIF SUMIF 用途：对满足单个条件的单元格求和。 语法：SUMIF (range，criteria，sum_ra

19、nge) 参数： Range为用于条件判断的单元格区域 Criteria是由数字、逻辑表达式等组成的判定条件 Sum_range为需要求和的单元格、区域或引用 用法举例：-30-内部文档常用函数DSUM DSUM 用途：求满足给定多个条件的数据库中记录的字段列数据的和。 语法：DSUM ( database , field , criteria ) 参数： Database表示构成列表或数据库的单元格区域。是引用的数据。 Field 指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。可以用双引号括住的列标签，或是表示该列在列表中位置的数值。是数值型数据。 Criteria 为一组包

20、含给定条件的单元格区域。区域包括列标签及列标签下满足某个条件的单元格。 用法举例：-31-内部文档常用函数MAX 和 MIN MAX 用途：返回数据集中的最大数值。 语法：MAX(number1，number2，.) 参数：Number1，number2，.是需要找出最大数值的1至N个数值。 MIN 用途：返回数据集中的最大数值。 语法：MIN(number1，number2，.) 参数：Number1，number2，.是需要找出最小数值的1至N个数值。 用法举例：-32-内部文档常用函数COUNT 和 COUNTIF COUNT 用途：返回包含数字以及包含参数列表中的数字的单元格的个数。

21、语法: COUNT(value1,value2,.) 参数：Value1, value2, . 为包含或引用各种类型数据的参数1 到 N个），但 只有数字类型的数据才被计算。 COUNTIF 用途：统计某一区域中符合条件的单元格数目。 语法：COUNTIF (range，criteria) 参数： range为需要统计的符合条件的单元格数目的区域； Criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本。其中数字可以直接写入，表达式和文本必须加引号。 用法举例：-33-内部文档常用函数ROUND/ROUNDUP/ROUNDDOWNROUND用途：返回某个数字按指定位数取整后的数

22、字。 语法：ROUND (number , num_digits) 参数：Number为需要进行四舍五入的数字。Num_digits为指定的位数，按此位数进行四舍五入。 ROUNDUP：不做四舍五入的向上取整 如算通话分钟数ROUNDDOWN：不做四舍五入的向下取整用法举例：ROUND(“2.1547”,3) = 2.155ROUNDUP (“2.1547”,3) = 2.155ROUNDDOWN (“2.1547”,3) = 2.154-34-内部文档常用函数LEFT 和 RIGHT LEFT 用途：基于所指定的字符数返回文本字符串中的左边第一个或前几个字符 语法：LEFT (text ,

23、num_chars) 参数： Text是包含要提取字符的文本字符串。 Num_chars指定要由 LEFT 所提取的字符数。 RIGHT：为取右 用法举例： LEFT (“ad246g”,3) = ad2 RIGHT (“ad246g”,3) = 46g-35-内部文档常用函数MID MID 由用户自行指定开始的位置和字符的长度。由用户自行指定开始的位置和字符的长度 语法： MID(text,start_num,num_chars) 参数： Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。 Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的

24、start_num 为 1，以此类推。 Num_chars 指定希望 MID 从文本中返回字符的个数。 用法举例： 假设A1身份证号码提取生日的公式是“=MID(A1,7,4)&年&MID(A1,11,2)&月&MID(A1,13,2)&日”，返回的是“2019年12月31日”。-36-内部文档常用函数RANK RANK 用途：根据指定条件对若干单元格、区域或引用求和。 语法：RANK (number , ref , order) 参数： Number：为需要找到排位的数字。 Ref：为数字列表数组或对数字列表的引

25、用。 Order：为一数字，指明排位的方式。假设 order 为 0零或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表; 假设 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 用法举例：-37-内部文档常用函数IF IF 用途：根据指定条件对若干单元格、区域或引用求和。 语法：IF (logical_test , value_if_true , value_if_false) 参数： Logical_test：表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如，A10=100 就是一个

26、逻辑表达式，如果单元格 A10 中的值等于 100，表达式即为 TRUE，否则为 FALSE。 Value_if_true：logical_test 为 TRUE 时返回的值。 Value_if_false：logical_test 为 FALSE 时返回的值。 用法举例：-38-内部文档常用函数VLOOKUPuVLOOKUPu用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。u语法： VLOOKUP查找值，区域，列序号，逻辑值）u参数：u“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。 u“区域”：数组所在的区域，如“B

27、2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。 u“列序号”：即希望区域数组中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；u“逻辑值”：为TRUE/1或FALSE/0。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值的最大数值；假设“逻辑值为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值u用法举例：-39-内部文档-39-Excel数据排序、筛选与分类汇总-40-内部文档-40-Excel数据排序、筛选与分类汇总-41-内部文档排序是根据数据清单中某

28、个字段的值排列各行记录的顺序。如果要求对一列数据对数据清单排序，在常用工具栏中单击【升序】【降序】 按钮即可。如果要示对多列的数据对数据清单排序，操作步骤如下：1、选中【数据】菜单中的【排序】菜单项，弹出【排序】对话框。数据排序-42-内部文档2、在弹出【排序】对话框中，根据数据清单中有无“标题行”，在“当前数据清单设置区中选择“有标题行或“无标题行选项3、在“主要关键字”、“次要关键字”、“第三关键字中选择相关要求4、单击“确定”，此时表中的数据按照要求进行排序数据排序-43-内部文档数据筛选是将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。Excel提供“自动筛选和“高级筛选前者进行

29、简单件的筛选，后者进行复杂的条件筛选。使用自动筛选的方法：1、选中数据清单的标题行，单击【数据】菜单中的【挑选】菜单项的【自动筛选】，在选中数据的每个字段名的右侧都会出现下拉箭头。数据筛选数据筛选-44-内部文档2、如果要显示分数前几名考生，可单击一拉箭头，点击“前10个”，系统会弹出【选前10个】对话框，用户可根据需求进行设置，最后按“确定”。3、如果要进行某个不等式条件或某个区间进行筛选，右可单击一拉箭头，点击“自定义”，系统会弹出【自定义】对话框，用户可根据需求进行设置，最后按“确定”。数据筛选-45-内部文档如果筛选条件很多，可以考虑使用“高级筛选”，运用“高级筛选步骤 如下：1、在与

30、数据清单不相连的区域设置条件，将有“与关系的条件放在同一行，有“或关系的条件放在不同的行注： “与表示两个条件同时成立，“或表示两个条件中有一个成立）2、单击数据清单中的任一单元格，单击【数据】菜单中的【挑选】菜单项的【高级筛选】命令，弹出【高级筛选】对话框。数据的高级筛选-46-内部文档3、在【高级筛选】对话框中选择数据区域和条件区域，4、可以选择两种放置筛选结果的方式，一种是“在原有区域显示筛选结果另一种是“将当前筛选结果复制到其他位置”，单击【确定】。数据的高级筛选-47-内部文档分类汇总之前要先对数据进行排序选中数据，单击【数据】菜单中的【分类汇总】菜单项，出现【分类汇总】对话框在【分类汇总】对话框中设置：分类字段，汇总方式，汇总项。各项设置后单击【确定】按钮，完成分类汇总分类汇总-48-内部文档-48-Excel数据条件格式-49-内部文档条件格式的使用技巧什么是条件格式对选定区域中符合指定条件的数据以指定格式显示如何设置条件格式格式 - 条件格式使用条件格式显示单元格的数据例：将表格中小于60的数字以红色显示目的：突出显示满足某个条件数