搜索
?2006Hisuntech©2012HisuntechExcel技能分享2016年7月Contents目录01020304Excel的一些基本常识与编辑技巧一些常用的快捷键数据分析与主要函数Excel的一些高级应用介绍©2016Hisuntech-2-来看看你属于哪个Excel级别？Excel新人掌握Excel的基本操作方法和常用功能，如输入数据、查找替换、设置单元格格式、排序、筛选和保存工作簿等初级用户经常在工作中使用Excel，如绘制一些表格，画一张图表，使用简单的函数，知道Excel比较强大，但不知道怎么用中级用户①熟练使用各个Excel菜单；②熟练使用数据透视表；③至少掌握20个常用函数及函数的嵌套运用。高级用户①熟练运用数组公式；②能够利用VBA编写不太复杂的自定义函数或过程。专家具有高级用户的Excel技能，能结合自己的行业开发不同的Excel应用的高手才能称之为专家©2016Hisuntech-3-Excel的一些限制Excel2003及以前版本65536行,256列Excel2007及后续版本1048576行,16384列最多输入行列数公式最多嵌套层数函数的嵌套不超过64层。最多可创建的工作表数Excel2003工作簿最多可以有255张工作表，2007版本以上工作簿中的工作表个数：没有具体数字的限制，只受可用内存的限制。©2016Hisuntech-4-Excel的一些编辑技巧（1）如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法：先输入“0”，然后输入空格，再输入分数“1/5”分数的输入序列的输入如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“'”（西文单引号），然后输入“001”日期的输入如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。©2016Hisuntech-5-Excel的一些编辑技巧（2）如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，右击“设置单元格格式”菜单，选中“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。填充条纹多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。©2016Hisuntech-6-Excel的一些编辑技巧（3）选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。不连续单元格填充同一数据在单元格中显示公式如果工作表中的数据多数是由公式生成的，想要快速知道每个单元格中的公式形式，以便编辑修改，可以这样做：用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏下的“公式”项有效，单击“确定”按钮。这时每个单元格中的分工就显示出来了。如果想恢复公式计算结果的显示，就再设置“窗口选项”栏下的“公式”项失效即可。Excel2013直接在“公式”选项卡下面，点击“显示公式”©2016Hisuntech-7-Excel的一些编辑技巧（4）在执行查找操作之前，可以将查找区域确定在某个单元格区域、整个工作表（可选定此工作表内的任意一个单元格）或者工作簿里的多个工作表范围内。在输入查找内容时，可以使用星号（*）作为通配符，以方便查找操作。星号（*）代表一个或多个字符。需要注意的问题是，既然星号（*）作为通配符使用，那么如何查找星号（*）呢？只要在这两个字符前加上波浪号(～)就可以了。快速查找在一个单元格内输入多个值我们有时需要在某个单元格内连续输入多个数值，以查看引用此单元格的其他单元格的效果。但每次输入一个值后按回车键，活动单元格均默认下移一个单元格，非常不便。其实可以采用以下方法:单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格，此时，单元格周围将出现实线框，再输入数据，敲回车键就不会移动了。©2016Hisuntech-8-Excel的一些编辑技巧（5）在工作中，发现一些通过文本文件或其它财务软件的数据导入Excel中后居然是以文本形式存在的(数字默认是右对齐，而文本是左对齐的)，即使是重新设置单元格格式为数字也无济于事。有一个办法可以快速地将这些文件转变回数字:在空白的单元格中填人数字1，然后选中这个单元格，执行“复制”命令，然后再选中所要转换的范围，选择“选择性粘贴”中的“乘”，你就会发现它们都变为数字了巧变文本为数字绘制斜线表头右击“设置单元格格式”命令，弹出“设置单元格格式”窗口，选择“对齐”标签，将垂直对齐的方式选择为“靠上”，将“文本控制”下面的“自动换行”复选框选中()，再选择“边框”标签，按下“外边框”按钮，使表头外框有线，接着再按下面的“斜线”按钮，为此单元格添加一格。Contents目录01020304Excel的一些基本常识与编辑技巧一些常用的快捷键数据分析与主要函数Excel的一些高级应用介绍©2016Hisuntech-10-常用的快捷键（1）快速编辑单元格：F2单元格内快速换行：Alt+Enter重复前一次操作：F4或者CTRL+Y移动到工作簿中下一个工作表：CTRL+PAGEDOWN用当前输入项填充选定的单元格区域：CTRL+ENTER隐藏行：CTRL+9，隐藏列：CTRL+0（零）选定当前单元格周围的区域：CTRL+SHIFT+*（星号）将选定区域扩展到工作表的最后一个使用的单元格：CTRL+SHIFT+ENDCTRL+SHIFT+↓或↑列的单元格快速选取将选定区域扩展到工作表的最后一个使用的单元格：CTRL+SHIFT+END©2016Hisuntech-11-常用的快捷键（2）快速进行单元格之间的切换用鼠标圈定一定区域后，按Tab键可使目标单元格向后移，按“Shift+Tab”可向前移。这样就可以在键盘上连续输入一组数据而不需用鼠标，从而提高输入速度。提示:事实上，采用此方法最大的好处是，在一行的最后一个单元格，继续按Tab，则可以转到下一行开始的单元格;在选定区域最后一行的最后一个单元格继续按Tab则会回到选定区域第一行第一个单元格。同样用Enter可以按列输入数据快速填充空白单元格选中你需要填充的区域，打开定位（Ctrl+G），选中空值，在任意的一个空格写上数据，按Ctrl+shift，填充完成。快速格式化单元格如果想要快速访问Excel中的单元格格式对话框，以更改诸如字样、对齐方式或边框等，请先选择需要格式化的单元格然后按下“Ctrl+1”组合键，就可以打开“单元格格式对话框”相对引用与绝对引用引用公式选择区域时，绝对引用可以固定所选择的区域，而相对引用可以随着活动单元格的移动而相对调整引用位置。要设置绝对引用的快捷键：F4Contents目录01020304Excel的一些基本常识与编辑技巧一些常用的快捷键数据分析与主要函数Excel的一些高级应用介绍©2016Hisuntech-13-分类汇总•分类汇总对同类数据进行汇总计算。注意：①使用之前先对数据列表按类别排序。②只能对连续区域使用”组合”命令，不能对多重选定区域使用组合命令。③注意汇总数据显示位置的设置。©2016Hisuntech-14-合并计算•合并计算认识合并计算：注意：①使用按类别合并的功能时，数据源列表必须包含行或列标题。②处理方式不仅仅是SUM,还有其他选择(计数、平均、乘积……)③注意结果表区域需要与合并计算的区域分开©2016Hisuntech-15-数据透视表•数据透视表概述•快捷、强大的数据分析方法，可以解答您关于数据的种种问题。©2016Hisuntech-16-模拟分析•Excel附带了三种假设分析工具：方案、数据表和单变量求解•假设分析能为我们做什么？•1）单变量求解：已知公式y=(6x+16)/50-60,y=102时，x=?，诸如此类已知函数结果，求自变量的问题•2）数据表（单变量）：已知y=(6x+16)/50-60，分析x,变量在不同数值下，y值如何变化•3）数据表（双变量）：已知z=(6x+y)/50-60，分析x,y两个变量在不同数值下，z值如何变化•4）方案：假设您具有三个预算方案：最坏情况、一般情况、最好情况。您可以使用方案管理器在同一工作表中创建这三个方案，然后在各方案间切换。对于每个方案，您可以指定更改单元格以及用于该方案的值。当您在各方案之间切换时，结果单元格会发生变化以反映变化的不同单元格值。•快捷、强大的数据分析方法，可以解答您关于数据的种种问题。©2016Hisuntech-17-常用函数类型数学和三角函数（MOD、SIN、PI）统计函数（AVERAGE、COUNT、MAX）文本函数（TEXT、MID、LEFT）日期与时间函数（NOW、DATE、DAY）逻辑函数（OR、AND）查找函数（COLUMN、VLOOKUP、MATCH）数据库工作表函数(DCOUND)条件函数（IF、SUMIF、COUNTIF）……©2016Hisuntech-18-常用函数-ROUND•将数字“12.3456”按照指定的位数进行四舍五入，可以在D3单元格中输入以下公式：“=ROUND(B3,C3)“©2016Hisuntech-19-常用函数-RANK.AVG或RANK.EQ•RANK.AVG返回一列数字的数字排位：数字的排位是其大小与列表中其他值的比值；如果多个值具有相同的排位，则将返回平均排位。•RANK.EQ返回一列数字的数字排位。其大小与列表中其他值相关；如果多个值具有相同的排位，则返回该组值的最高排位。如果要对列表进行排序，则数字排位可作为其位置。©2016Hisuntech-20-常用函数-FREQUENCY•FREQUENCY计算数值在某个区域内的出现频率，然后返回一个垂直数组。例如，使用函数FREQUENCY可以在分数区域内计算测验分数的个数。由于FREQUENCY返回一个数组，所以它必须以数组公式的形式输入。©2016Hisuntech-21-常用文本函数-TEXT•TEXT根据指定的数值格式将相应的数字转换为文本形式。©2016Hisuntech-22-常用文本函数-MID•MID从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：MID(text,start_num,num_chars)参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。应用举例：假定A47单元格中保存了“我喜欢四方坪”的字符串，我们在C47单元格中输入公式：=MID(A47,4,3)，确认后即显示出“四方坪”的字符。特别提醒：公式中各参数间，要用英文状态下的逗号“,”隔开。类似的函数还有:LEFT(A38,3)RIGHT(A38,3)©2016Hisuntech-23-常用文本函数-TRIM•TRIM除了单词之间的单个空格之外，移除文本中的所有空格。对于从另一个可能含有不规则间距的应用程序收到的文本，可以使用TRIM。©2016Hisuntech-24-常用文本函数-CONCATENATE[kɑnˈkæt(ə)ˌneɪt]•主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。使用格式：CONCATENATE(Text1，Text……)参数说明：Text1、Text2……为需要连接的字符文本或引用的单元格。应用举例：在c39单元格中输入公式：=CONCATENATE(A14,@,B14,.com)，确认后，即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体，显示在C14单元格