Excel的使用方法与技巧PPT学习课件.ppt

ExcelExcel的基础方法与技巧的基础方法与技巧11 12 23 34 4ExcelExcel的基础知识输入与设置数据格式管理数据清单目录常用公式和函数2 第一节第一节 ExcelExcel的基础知识的基础知识 ExcelExcel的诞生的诞生 （）年Microsoft Excel问世，被公认为是功能最完整、技术最先进和使用最简便的电子表格软件。Excel可以创建和修改工作表、三维图表，以及分析管理数据 Excel2013、2010、2007和老一点的Excel2003较为多见。198519853一个工作簿最多可以有多少个工作表？一个工作簿最多可以有多少个工作表？255255个个 一张工作表一张工作表(2007)(2007)由多少行、多少列组成？由多少行、多少列组成？10485761048576行行 *1638416384列列 列标：列标：A,B.AA.AAA.XFDA,B.AA.AAA.XFD 行标：行标：1,2,3.10485761,2,3.1048576行行 最后一个单元格的地址是什么？最后一个单元格的地址是什么？XFD1048576XFD1048576 工作表默认对齐方式：数字？文本？日期及时间？工作表默认对齐方式：数字？文本？日期及时间？右对齐：日期和时间、数字右对齐：日期和时间、数字 左对齐：文本左对齐：文本 如何选择、复制多个工作表？如何选择、复制多个工作表？选择多个相邻的工作表：选择多个相邻的工作表：shiftshift 选择多个不相邻的工作表：选择多个不相邻的工作表：ctrlctrl基基础础知知识识考考考考你你4常用基础操作常用基础操作快捷键的使用设置工作表数目自定义快速访问工具栏、功能区5 CtrlCtrl的使用方式的使用方式Ctrl+Ctrl+方向键方向键Ctrl+Ctrl+Ctrl+VCtrl+VCtrl+CCtrl+CCtrl+XCtrl+XC Ctrltrl +EscEscCtrl+SCtrl+SCtrl+ZCtrl+ZCtrl+YCtrl+YCtrl+ACtrl+ACtrl+FCtrl+FCtrl+NCtrl+N移到目前资料区域的边缘显示隐藏粘贴复制剪切打开开始菜单保存当前操作的档撤销上一步的操作恢复选择所有项目查找或替换新增工作簿快快捷捷键键的的使使用用6Ctrl+DCtrl+DCtrl+RCtrl+RCtrl+;Ctrl+;Ctrl+9Ctrl+9Ctrl+0(Ctrl+0(零零)Ctrl+5Ctrl+5C Ctrl trl+U+UC Ctrl trl+I+IC Ctrl trl+W+WC Ctrl trl+B+BC Ctrl trl+F9+F9C Ctrl trl+F10 +F10 C Ctrl trl+G+GF2F2向下填满 向右填满输入日期隐藏列隐藏栏套用或移除删除线的格式下划线斜体关闭打开的窗口加粗将活页簿窗口最小化成图标将活页簿窗口最大化或还原定位文件重命名或单元格快快捷捷键键的的使使用用7ShiftShift的使用方式的使用方式ShiftShift +方向键方向键Shift+F11Shift+F11Shift+7Shift+7 ALTALT的使用方式的使用方式ALT+=ALT+=ALT+ALT+Alt+TabAlt+Tab将选取区扩大一个单元格新增工作表链接符号（&）插入自动求和公式显示自动完成列表切换到另一个窗口 快快捷捷键键的的使使用用8合合并并使用使用Ctrl+Shift+;Ctrl+Shift+;Ctrl+Shift+Ctrl+Shift+方向键方向键Ctrl+Shift+(Ctrl+Shift+(Ctrl+Shift+)Ctrl+Shift+)Ctrl+Alt+DeleteCtrl+Alt+DeleteAlt+Shift+Alt+Shift+Alt+Shift+Alt+Shift+Alt+Shift+TabAlt+Shift+TabShift+Ctrl+LShift+Ctrl+L输入时间将选取区扩大到活动单元格的同一栏或同一列的最后一个非空白单元格显示所选范围隐藏的列显示所选范围隐藏的行打开任务管理器将列或栏群组在一起取消列或栏的群组切换到上一个程序启用或取消筛选 快快捷捷键键的的使使用用9在【Office按钮】的下拉菜单中点击【Excel选项】，将【常用】面板的【新建工作簿时】选项组中的【包含的工作表数】进行修改即可。设设置置工工作作表表数数目目10快速访问工具栏是一个可自定义的工具栏，其中表示命令的按钮可以自行添加，另外还可以将快速访问工具栏移动到功能区下方显示。1、添加快速访问工具栏中的按钮 在快速访问工具栏上单击鼠标右键，在弹出的快捷菜单中选择【自定义快速访问工具栏】命令，打开【Excel选项】对话框。在【从下列位置选择命令】下拉列表框中选择【常用命令】选项，在其下方的列表框中选择需要添加的命令，单击【添加】按钮，右侧列表框中即可显示添加的命令，单击【确定】按钮。工具栏自自定定义义快快速速访访问问工工具具栏栏、功功能能区区112、移动快速访问工具栏的位置 在快速访问工具栏上单击鼠标右键，从弹出的快捷菜单中选择【在功能区下方显示快速访问工具栏】命令，快速访问工具栏显示在功能区的下方，并且变成和操作界面一样的宽度。自自定定义义快快速速访访问问工工具具栏栏、功功能能区区工具栏12 功能区中列出了完成某一任务所需的命令，并按其类别存储在各个选项卡中，为了增大屏幕的显示空间，可以将功能区最小化。功能区自自定定义义快快速速访访问问工工具具栏栏、功功能能区区131 12 23 34 4ExcelExcel的基础知识输入与设置数据格式管理数据清单目录常用公式和函数14第二节第二节 输入与设置数据格式输入与设置数据格式自动填充设置等差/比序列数据有效性条件格式15文本的输入文本的输入 单元格中输入文本时自动左对齐数字的输入数字的输入 数字自动右对齐。输入负数要在前面加一个负号日期和时间的输入日期和时间的输入 按 Ctrl+;键可以输入当前日期 按 Ctrl+Shift+;键可以输入当前时间公式的输入公式的输入 公式输入以“=”开头还记得快捷键吗上上一一节节知知识识再再考考考考默认对齐方式是什么第一步要做什么16自动填充自动填充 如果第一个单元格中输入的数据是数字，自动填充出来的所有数据都相同；按住CtrlCtrl键，用鼠标拖动填充柄，可以填充步长值为“1 1”的等差数列；如果需要输入的序列差值是2 2或2 2以上，要求先输入前两个数据，然后选中这两个单元格，再沿填充方向拖动鼠标进行填充。如果第一个单元格中输入的数据是日期、自定义序列中的内容或数字文本，用鼠标拖动填充柄，可以填充步长值为“1 1”的等差数列；按住CtrlCtrl键，自动填充出来的所有数据都相同。自自动动填填充充17自动填充序列自动填充序列初始选择初始选择扩展序列扩展序列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日1999，20002001，2002，2003Text1，textAText2，textA，Text3，textA产品1产品2，产品3自自动动填填充充18设置等差设置等差/比序列比序列 输入第一个值，选中要填充的区域，选择“编辑”“填充”“系列”选项，在弹出的“序列”对话框中分别修改“序列产生在”和“类型”及输入”步长值”即可。设设置置等等差差/比比序序列列19提高输入效率：提高输入效率：1.在多个工作表中输入相同数据 按住CtrlCtrl键，逐个单击要输入相同数据的工作表。选中要输入相同数据的单元格，输入数据。输入完毕，单击任意一个工作表的表标签结束操作。2.在一个工作表中同时对多个单元格输入相同内容 选定要输入相同内容的单元格区域，在活动单元格中输入数据。输入最后一个字符后，按下Ctrl+EnterCtrl+Enter键3.选择列表的使用 (1)在一个文字列表的下面右击一个单元格，在弹出的快捷菜单中选择“从下拉列表中选择”选项，或按Alt+Alt+键 (2)弹出一个输入列表，从中选择需要的项即可。输入小技巧204、在一个单元格内输入多个值：单击鼠标选定单元格，然后按住CtrlCtrl键再次单击鼠标选定此单元格 单元格周围将出现实线框，再输入数据，敲回车键，活动单元格就不会移动了。5、奇特的F4F4键：F4键可以重复前一次操作，在很多情况下起作用，比如在工作表内加入或删除一行，然后移动插入点并按下F4F4键以加入或删除另一行，根本不需要使用菜单。6、快速输入大写中文数字金额 将光标移至需要输入大写数字的单元格中，在单元格中输入相应的小写数字。右击该单元格，点击“设置单元格格式”，从弹出的“单元格格式”对话框中选择“数字”选项；然后从“特殊”列表框中选择“中文大写数字”选项。最后单击“确定”按钮即可.217、ExcelExcel中消除0值 (1)打开OfficeOffice按钮 ExcelExcel选项高级 此工作表的显示选项 (2)取消勾选“在具有零值的单元格中显示零值”项 (3)确定后，当前工作表中的值为0 0的单元格将全部显示成空白。8、快速得到合适列宽和行高 (1)如果各列中数据宽度一致的话，我们可以选中各列，然后把鼠标移至列标题的右边框，待鼠标变成双向箭头时拖动至合适的宽度，这样就可以得到统一列宽的各列了。(2)如果各列中数据宽度不一致的话，此时可以选中含有数据的各列，同样将鼠标移至其中一个列标题的右边框，当鼠标指针变成双向箭头时双击鼠标左键，可以立刻将各列的列宽设置为“最适合的列宽”。(3)用这个方法也可以得到“最适合的行高”。只是要双击行 标题的下边框。229、在打印时让每页都显示标题 (1)打开 页面布局 打印标题页面设置 (2)单击“工作表”选项卡，在“打印标题”栏中的“顶端标题行”项和“左侧标题列”项中设置好要显示的标题单元格，单击“确定”按钮。这样在打印长表格时，每页都会显示出标题的内容了。23设置单元格的数字格式、字体、边框、颜色设置单元格的数字格式、字体、边框、颜色设设置置单单元元格格的的格格式式24控制单元格输入文本长度控制单元格输入文本长度选中区域-数据-数据有效性-允许-文本长度可防止身份证/银行卡号/手机号码输入多一位或者少一位数功能可自行设置出错警告哦数数据据有有效效性性25设置序列设置序列选中区域-数据-数据有效性-序列-在“来源”输入（A,B,C,D)逗号要半角格式注意数数据据有有效效性性26禁止输入重复数据禁止输入重复数据选中区域-数据-数据有效性-允许（自定义)-公式填写=COUNTIF(A:A,A1)=1 =COUNTIF(A:A,A1)=1 或=COUNTIF(A:A,A1)2=COUNTIF(A:A,A1)“分类汇总”菜单 统计岗位的不同学历平均工资分分类类汇汇总总嵌套分类汇总嵌套分类汇总46再次选择“分类字段、汇总方式、汇总项”，并取消勾选“替换当前分类汇总”第二次分类汇总结果 注意：要取消此项勾选，否则之前分类将被覆盖分分类类汇汇总总嵌套分类汇总嵌套分类汇总47按位置合并计算数据按位置合并计算数据 按位置合并计算数据，是指在所有源区域中的数据被相同地排列以进行合并计算，也就是说从每一个源区域中合并计算的数值必须在被选定源区域的相同位置上。按分类合并计算数据按分类合并计算数据 通过分类来合并计算数据，是指当多重来源区域包含相似的数据却以不同的方式排列时，可依不同分类进行数据的合并计算。也就是说，当选定的表格具有不同的内容时，我们可以根据这些表格的分类来分别进行合并计算。合合并并计计算算48怎样快速获得公司总销售情况？合合并并计计算算按位置合并计算数据按位置合并计算数据49 选定合并结果数据的目的显示区域，点击数据合并计算，打开“合并计算”对话框合合并并计计算算按位置合并计算数据按位置合并计算数据50选择“求和”函数，在“引用位置”选择要引用的数据区域，点击“添加”依次将3组引用数据添加进去合合并并计计算算按位置合并计算数据按位置合并计算数据51合并后的数据当进行合并计算时，还当进行合并计算时，还可以引用不同工作簿中可以引用不同工作簿中的工作表区域，本例中的工作表区域，本例中3 3个分公司的工作表在同个分公司的工作表在同一工作薄中一工作薄中合合并并计计算算按位置合并计算数据按位置合并计算数据52季度整体工资情况，你所知道的合并方法有哪些？合合并并计计算算按分类合并计算数据按分类合并计算数据53 选定合并结果数据的目的显示区域，点击数据合并计算，打开“合并计算”对话框选择”求和”引用位置”依次添加3组数据要勾选这两项哦合合并并计计算算按分类合并计算数据按分类合并计算数据54如果希望数据源改变时，Excel自动更新合并的工作表，只需在“合并计算”对话框中选中“创建连至源数据的链接”复选框即可 合合并并计计算算按分类合并计算数据按分类合并计算数据55 数据透视表是一种交互，交叉制作的报表，一组具有多个字段的数据进行多立体的分析汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和比较大量数据。当数据规模比较大时，这种分析的意义就显得尤为突出。说人话一种快速分析数据的工具数数据据透透视视表表认识数据透视表认识数据透视表56数据透视表可以快速解决这些事情统计7月入职的招聘来源分布统计离职原因分布个数统计各部门平均工资统计各岗位学历分布情况数数据据透透视视表表57序号姓名中心/部门职务入职时间联系电话渠道1何国清华南加盟服务中心司机7月1日18818334568内部推荐2聂宇鸿川渝大区导师7月3日13488990528智联招聘3叶超锦研发中心发型设计师7月2日13560395131内部推荐4詹独伊研发中心初级设计师7月2日18627894027内部推荐5邢冰人资中心员工关系专员7月3日15876573968前程无忧6刘燕辉市场中心产品主管7月1日15915842064前程无忧7赵满礼华东大区区域经理7月6日智联招聘8刘舒平运营人力资源部人力资源专员7月7日前程无忧9郑嘉平人资中心企业文化专员7月6日13825736135前程无忧10刘付上娥销售渠道中心档案管理员7月6日13642713104赶集网11隆社香D事业部推广经理7月9日18670265889外部推荐12马莹莹华北大区导师7月6日其他13颜江华中大区导师7月2日58同城14邹婷华中大区区域经理7月10日内部推荐15李嘉妙华东大区导师7月10日18042843627内部推荐16叶亮川渝大区区域经理7月15日13558709272智联招聘17赵秋洁华南大区导师7月16日13432099893内部推荐18刘珊川渝大区区域经理7月16日15756232495前程无忧19林康秀销售管理部客服助理7月15日智联招聘20冯芳*供应中心维修组退货员7月13日人才市场21陈笑欢销售管理部仓导员7月13日内部推荐22沈阳华东大区仓导员7月16日13160185828智联招聘23徐井花华东大区区域经理7月15日内部推荐24杨香香华东大区区域经理7月16日内部推荐25朱建明华东大区区域经理15995765930前程无忧26王小君华中大区导师7月16日58同城27黄宇诗总务部前台文员7月22日前程无忧28杨文秀西南大区导师7月11日内部推荐29唐从禹华东大区仓导员7月20日1801397930458同城30陆淑娟南区加盟文员7月27日18978987743内部推荐31崔智静财务中心直营会计7月27日18027668856内部推荐32陈晓春销售管理部统计文员7月27日1356045587358同城33曾达丽华中大区区域经理7月20日15913363023内部推荐打开“插入”“数据透视表”，选择区域、透视表放置位置，确定数数据据透透视视表表统计统计7 7月入职的招聘来源分布月入职的招聘来源分布58根据需求，选择要添加到报表的字段，拖动到相应位置同理，可用来统计离职原因分布数数据据透透视视表表统计统计7 7月入职的招聘来源分布月入职的招聘来源分布59点击箭头，选择“值字段设置”，可设置汇总方式数数据据透透视视表表统计部门平均工资统计部门平均工资60行：职位列：性别、学历数值：姓名数数据据透透视视表表统计各岗位学历分布统计各岗位学历分布611 12 23 34 4ExcelExcel的基础知识输入与设置数据格式管理数据清单目录常用公式和函数62第四节第四节 常用公式和函数常用公式和函数运算符及优先级公式返回的错误值及原因常用函数函数VOOKUP函数COUNTIF63 优优先先等等级级逐逐步步降降低低1.-（负号）2.%（百分号）3.（指数符号）4.*（乘号）/（除号）5.+（加号）-（减号）6.&(连接两串字符）7.=有括号先做括号里有括号先做括号里相同级别按自左向右相同级别按自左向右不同级别按自上而下不同级别按自上而下运运算算符符及及优优先先级级64返回的错误值返回的错误值产生的原因产生的原因#!公式计算的结果太长，单元格列宽不够；或者单元格中存在无效的日期和时间#DIV/0！公式或函数中的除数为零，或为空白单元格#N/A 公式中无可用的数值或缺少函数参数#NAME?删除了公式中使用的名称，或使用了不存在的名称，以及包含不可识别的文本#NULL！使用了不正确的区域运算，或者引用单元格之间的交集为空格#NUM！在需要数字参数的函数中使用了无效的数值，或者公式计算结果的数字太大或太小，Excel无法表示#REF！单元格引用无效，删除或覆盖了由其他公式引用的单元格，或将移动单元格粘贴到其他公式引用的单元格中#VALUE!使用的参数或者运算对象的类型不正确，例如当公式中需要输入数字或逻辑值时输入了文本公公式式返返回回的的错错误误值值及及原原因因65函数名语法函数结构函数作用1、Left1、LEFT(text,num_chars)其中：其中：Text是包含要提取字符的文本字是包含要提取字符的文本字符串。符串。Num_chars 指定要由指定要由 LEFT 所提所提取的字符数。取的字符数。=LEFT(A1,2)从从A1A1最左边最左边向右取两位向右取两位2、Right2、RIGHT(text,num_chars)其中：其中：Text是包含要提取字符的文本字是包含要提取字符的文本字符串。符串。Num_chars指定希望指定希望 RIGHT 提提取的字符数。取的字符数。=RIGHT(A1,2)从从A1A1最右边最右边向左取两位向左取两位3、Mid3、MID(text,start_num,num_chars)其中：其中：Text是包含要提取字符的文本字是包含要提取字符的文本字符串。符串。Start_num 是文本中要提取的第是文本中要提取的第一个字符的位置。一个字符的位置。Num_chars 指定希望指定希望 MID 从文本中返回字符的个数。从文本中返回字符的个数。=MID(A1,2,3)从从A1A1左边第左边第2 2位开始取位开始取3 3位位4、&（连连接符号）接符号）XX&XX=A2&B2将将A2A2和和B2B2内内容合并在一容合并在一起起常常用用函函数数截取、合并类截取、合并类66函数名函数名函数结构函数结构函数作用函数作用FIND=FIND(龙龙,C6)在c6中查找龙，结果显示龙在c6中的位数，查不到显示#VALUE!VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)查找类查找类常常用用函函数数67函数名函数名语法语法函数结构函数结构函数作用函数作用IFIF(logical_test,value_if_true,value_if_false)=if(a20,1,0)当a20时，显示1，否则显示0IFREEOR第一个参数一般为一个函数，当这个函数运行是个错误值时（#N/A#DIV/0!#N/A#VALUE!#NAME#NULL!#NUM!#REF!），运行结果等于第二个参数=iferror(vookup(A1,A:B,2,0),0)如果VLOOKUP函数运行值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数运行的值。ANDAND(logical1,logical2,.)Logical1,logical2,.表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。=and(a30,b30,并且b30,b40,或者b40时，都显示true，否则显示false判断类判断类常常用用函函数数68函数名函数名函数结构函数结构函数作用函数作用1、COUNT=Count(a1:a10)计a1:a10中数字的个数2、COUNTA=Counta(a1:a10)计a1:a10中非空单元格的个数，（0也算）3、COUNTBLANK=Countblank(a1:a10)计a1:a10中空白单元格的个数常常用用函函数数计数类计数类69函函数数V VO OO OK KU UP P70参数参数简单说明简单说明输入数据类型输入数据类型lookup_value要查找的值要查找的值数值、引用或文本字符串数值、引用或文本字符串table_array要查找的区域要查找的区域数据表区域数据表区域col_index_num返回数据在查找区域的第返回数据在查找区域的第几列数几列数正整数正整数range_lookup精确匹配精确匹配/模糊匹配模糊匹配0 0或或1 1函函数数V VO OO OK KU UP P71选择区域里最好不要有合并的单元格（先“取消单元格合并”）如果单元格里有空格，有可能导不出来 注意重复的名字查找区域首列必须含有查找的内容（第一个参数）注意函函数数V VO OO OK KU UP P72我们公司有多少个女的呢？COUNTIF(range,criteria)区域 条件标准函函数数C CO OU UN NT TI IF F73 选择目标放置单元格，输入“=countif”，弹出对话框中输入区域和条件函函数数C CO OU UN NT TI IF F74人员名单有没有重复的人？函函数数C CO OU UN NT TI IF F75没有重复人员重复，此份名单中有2个函函数数C CO OU UN NT TI IF F7677