Excel表格制作技巧

Excel#格#制作技巧 Excel制作技巧集锦 Excel进阶技巧(一) 无可见边线表格的制作方法 对Excel工作簿中的表格线看腻了吗,别着急，Excel的这些表格线并非向人们想象的那样是必需的，它也是可以去掉的，我们也可以使整个Excel工作簿变成―白纸‖，具体步骤为: 1.执行―工具‖菜单中的―选项‖命令，打开―选项‖对话框。 2.单击―视图‖选项卡。 3.清除―网格线‖选项。 4.单击―确定‖按钮，关闭―选项‖对话框。 快速输入大写中文数字的简便方法 Excel具有将用户输入的小写数字转换为大写数字的功能(如它可自动将123.45转换为―壹佰贰拾叁点肆伍‖)，这就极大的方便了用户对表格的处理工作。实现这一功能的具体步骤为: 1.将光标移至需要输入大写数字的单元格中。 2.利用数字小键盘在单元格中输入相应的小写数字(如123.45)。 3.右击该单元格，并从弹出的快捷菜单中执行―设置单元格格式‖命令。 4.从弹出的―单元格格式‖对话框中选择―数字‖选项卡。 5.从―分类‖列表框中选择―特殊‖选项;从―类别‖列表框中选择―中文大写数字‖选项。 6.单击―确定‖按钮，用户输入的123.45就会自动变为―壹佰贰拾叁点肆伍‖，效果非常不错。 在公式和结果之间进行切换的技巧 一般来说，当我们在某个单元格中输入一些计算公式之后，Excel只会采用数据显示方式，也就是说它会直接将计算结果显示出来，我们反而无法原始的计算公式。广大用户若拟查看原始的计算公式，只需单击―Ctrl-`"键(后撇号，键盘上浪线符~的小写方式)，Excel就会在计算公式和最终计算结果之间进行切换。不过此功能仅对当前活动工作簿有效，用户若拟将所有工作簿都设置为只显示公式，则应采用如下方法: 1.执行―工具‖菜单的―选项‖命令，打开―选项‖对话框。 2.击―视图‖选项卡。 3.选―窗口选项‖栏中的―公式‖选项。 4.单击―确定‖按钮，关闭―选项‖对话框。 Excel具有自动填充功能，它可帮助用户快速实现诸如―第1栏‖、―第2栏‖„―第10栏‖之类的数据填充工作，具体步骤为: 1.在A1、B1单元格中分别输入―第1栏‖、―第2栏‖字样。 2.用鼠标将A1:B1单元格定义为块。 3.为它们设置适当的字体、字号及对其方式(如居中、右对齐)等内容。 4.将鼠标移至B1单元格的右下角，当其变成十字形时，拖动鼠标向右移动，直至J1栏为止。 5.放开鼠标，则A1-J1栏就会出现诸如―第1栏‖、―第2栏‖„―第10栏‖的栏号，且它们的格式、排列位置都完全相同，从而满足了用户为表格添加栏号的要求。当然，我们也可采用同样的办法在Excel表格中自动设置F1、F2或第1行、第2行之类的行号，操作十分方便。 让Excel自动打开用户工作簿的技巧 XLSTART目录是Excel的专用启动目录，Excel每次启动时都会检查该目录，如果有文件 就自动执行(或打开)该目录中的所有文件，否则就直接启动并创建一个新的工作簿供用户使用。利用这一功能，我们只需为常用的工作簿在XLSTART目录中建立一个快捷方式即可达到让Excel在启动时自动打开这些工作簿的目的。如某用户每次启动Excel时都要处理C:\MyDocuments目录中―奖金‖工作簿，则可使用鼠标将C:\MyDocuments目录中的―奖金.XLS"文件右拖到ProgramFiles\MicrosoftOffice\Office\XLSTART目录中，然后在弹出的快捷菜单上选择―在当前位置创建快捷方式‖命令，为―奖金‖工作簿在XLSTART目录中创建一个快捷方式，此后每次启动时，Excel都会自动打开―奖金‖工作簿供用户使用，非常方便。 更改Excel系统启动目录的技巧 上面提到，我们只需将有关文件保存到XLSTART目录中，Excel启动时就会自动运行该文件，不过经常对系统目录进行操作可能会对系统安全造成的影响(XLSTART目录是一个系统目录，一般情况下我们不应将过多的文件存放在该目录中)。别着急，除了专用启动目录之外，Excel还提供了替补启动目录功能，即允许用户将任意一个目录指定为Excel的替补启动目录，然后在启动时自动打开该目录中的文件，从而增强了我们日常操作的灵活性。为Excel指定替补启动目录的步骤为: 1.在硬盘的合适位置建立一个常用Excel工作簿保存目录(如在―C:\MyDocuments" 下再建立一个"Excel"目录)，将常用的Excel工作簿文件都保存到该目录中。 2.启动Excel。 3.执行―工具‖菜单的―选项‖命令，打开―选项‖对话框。 4.单击―常规‖选项卡。 5.在―替补启动目录‖对话框中输入常用Excel工作簿所在目录(如C:\MyDocuments\Excel)。 6.单击―确定‖按钮。 此后Excel就会以―C:\MyDocuments\Excel"目录作为替补启动目录，它在每次启动时都会自动打开该目录中的所有工作簿，方便了用户的使用(―替补启动目录‖与XLSTART目录并不冲突，当我们设置了―替补启动目录‖之后，Excel将同时打开XLSTART目录和―替补启动目录‖中的所有文件，二者共同发挥作用)。 在多个工作表与Word等其它Office组件一样，Excel97也具有在工作簿中插入超级链接的功能，我们可以利用此功能将有关Internet网址、磁盘地址、甚至同一张Excel工作簿的不同的单元格链接起来，此后就可以直接利用这些链接进行调用，从而极大地方便了用户的使用。在Excel97中插入超级链接的步骤为: 1.将光标移到需要插入超级链接的位置。 2.执行―插入‖菜单中的―超级链接‖命令，打开―插入超级链 接‖对话框。 3.在―链接到文件或URL"对话框中指定需要链接的文件位置或Internet网址(当用户需要链接同一个工作簿中的不同单元格时，此位置可空出不填)。 4.在―文件中有名称的位置‖对话框中指定需要链接文件的具体位置(如Word文档的某个书签、Excel工作簿的某个单元格等)。 5.单击―确定‖按钮，关闭―插入超级链接‖对话框。 这样，我们就实现了在Excel工作簿中插入超级链接的目的，此后用户只需单击该超级链接按钮，系统即会自动根据超级链接所致显得除可对已有的样式进行修改及自定义所需的样 式之外，Excel还允许我们将某个工作簿所包含的样式拷贝到其它工作簿中使用，以进一步扩大样式的使用范围，具体步骤为: 1.打开包含有需要复制样式的源工作簿及目标工作簿。 2.执行―格式‖菜单的―样式‖命令，打开―样式‖对话框。 3.单击―合并‖按钮，打开―合并样式‖对话框。 4.在―合并样式来源‖框中选择包含有要复制样式的源工作簿并单击―确定‖按钮，源工作簿中所包含的一切样式就会拷贝到目标工作簿中(对于同名的样式，系统将会要求用户选择是否覆盖)，然 后我们就可以在目标工作簿中直接加以使用了，从而免去了重复定义之苦。 利用粘贴函数功能简化函数的调用速度 Excel一共向用户提供了日期、统计、财务、文本、查找、信息、逻辑等10多类，总计达数百种函数，不熟悉的用户可能很难使用这些优秀功能，没关系，―粘贴函数‖功能可在我们需要使用各种函数时提供很大的帮助。―粘贴函数‖功能是Excel为了解决部分用户不了解各种函数的功能及用法、而专门设置的一种一步一步指导用户正确使用各种函数的向导功能，广大用户可在它的指导下方便地使用各种自己不熟悉的函数，并可在使用的过程中同时进行学习、实践，效果非常好。在Excel中使用―粘贴函数‖功能插入各种函数的步骤为: 1.在Excel中将光标移至希望插入有关函数的单元格中。 2.执行Excel"插入‖菜单的―函数‖命令(或单击快捷工具栏上的―粘贴函数‖按钮)，打开―粘贴函数‖对话框。 3.若Office向导没有出现，可单击―粘贴函数‖对话框中的―Office向导‖按钮，激活Office向导，以便在适当的时候获取它的帮助。 4.在参考―粘贴函数‖对话框对各个函数简介的前提下，从―函数分类‖列表框中选择欲插入函数的分类、从―函数名‖列表框中选择所需插入的函数。 5.单击―确定‖按钮。 6.此时，系统就会打开一个用于指导用户插入相应函数的对话框，我们可在它的指导下为所需插入的函数指定各种。 7.单击―确定‖按钮。 在工具栏上添加一个计算器图标的技巧 1.执行Excel"工具‖菜单的―自定义‖命令，系统弹出―自定义‖对话框。 2.在―自定义‖对话框中选择―命令‖选项卡。 3.从―类别‖列表框中选择―工具‖选项;从―命令‖列表框中选择一个计算器样式的―自定义‖命令(注意，该列表框中有几个不同的―自定义‖命令按钮，.我们应选择那个在―说明‖按钮中显示为―计算器‖的按钮，不要搞错)。 4.用鼠标将该命令拖到Excel快捷工具栏的合适位置。 5.此时Excel的快捷工具栏上就会出现一个计算器样式的―自定义‖命令按钮，用鼠标右击该按钮，打开鼠标右键菜单。 6.执行快捷菜单中的―命名‖命令，将其改名为―计算器‖。 7.用户如果需要，可以采用类似方法为Excel的菜单栏也添加一个―计算器‖命令菜单。 8.单击―关闭‖按钮，关闭―自定义‖对话框。 统计符合某个条件的数据的简便方法 利用COUNTIF函数可轻易地实现对符合某一条件的数据进行统计的目的，具体步骤为: 1.将光标移至某一空白单元格中。 2.在单元格中输入―=COUNTIF(单元格1:单元格2，条件)‖。 3.按下回车键，Excel就会自动统计出从单元格1到单元格2中所有符合条件的数据个数，并将最后结果显示出来，使用非常方便。 其它操作技巧集锦 ALT系列快捷键 ALT，F11可以调出VBA编辑器 alt+enter拆分同一单元格ALT+0178=? ALT+0179=? ALT+0188=? ALT+0177=? ALT+0137=? 操作方法是按住ALT键，直接在数字小键盘(注意，这是很多人作不出来的原因)上连续输入数字，松开ALT键就出现字符了。 当然，你输入其他的数字也可以出来相应的字符。例如ALT，128是欧元符号―?‖ 输入自动求和公式时，不用鼠标点按工具栏上的按钮， 只需按―Alt+=‖即可。 ALT+;选定可见单元格 CTRL系列快捷键 按住ctrl+shift拖动单元格=复制+插入单元格+粘贴 ctrl+9隐藏一行单元格 ctrl+0隐藏一列单元格 Ctrl+Shift+9取消隐藏行 Ctrl+Shift+0取消隐藏列 快速输入今天的日期:ctrl+; 快速输入当前的时间:ctrl+shift+; 选定从工作表开始到结尾的方法:选中开始单元格(如A21)，同时按下ctrl+shift+end. 用Ctrl+右键头>和Ctrl+下箭头快速选定区域:如果一张表的第一行和第一列没有空格，一次即可，有几个空格按几次方向键! 删除单元格/行/列:选中单元格，ctrl+―-‖(减号) 插入单元格/行/列:选中单元格，ctrl+shift+―+‖(加号) ctrl+1快速打开格式对话框。 "<ctrl>+<~>可以将当前工作表中的公式显示出来,再按<ctrl>+<~>则恢复." "注意:有时在中文输入状态下会有所冲突,将输入法转换为英文即可." Ctel+Tab可在程序与程序，表簿与表簿之间转换打开。 使用ctrl+PageDown和ctrl+PageUp可以在工作表之间向前向后切换 按住ctrl键不放，按向左或向右键就可以直接到本工作表的最后一行或第一行 快速到达A1:CTRL+Home 快速到达行首:CTRL+(向左小箭头) 快速到达行尾:CTRL+(向右小箭头) 快速到达列首:CTRL+(向上小箭头) 快速到达列尾:CTRL+(向下小箭头) 按Ctrl+上下左右箭头可快速移动到数据清单的顶端、底部、最左边及最右边。 按Ctrl+Shift+上下左右箭头可快速选定数据清单的各个部份。 最小化窗口:ctrl+f9 最大化和还原窗口:ctrl+f10 删除选定单元格区域的边框:ctrl+shift+_ 如果你用的是带滚轮的鼠标，那么先按住―Ctrl‖键，再拨动滚轮吧～可以快速改变显示比例哟～ ctrl+―*‖=Activecell.CurrentRegion.Select 用CTRL+TAB键可以快速地在几个打开的工作簿之间切换 SHIFT系列快捷键 按住shift拖动单元格=剪切+插入单元格+粘贴 用SHIFT+SPACER，可以将自选图形变成文本框，外形不变。 选中一个单元格，按shift+F2键，可以插入批注。 功能键系列快捷键 F2用来修改单元格建议用以下方法来选择区域 ?当区域较小时，可直接用鼠标拖动选择 ?区域稍大，但在屏幕范围内，用鼠标点击左上角单元格，按住Shift键点击右下角单元格 ?区域较大，直接在名称框输入范围，如:在名称框输入A3:C7来选择黄色区域，输入3:7来选择黄色区域所在的行 ?常用区域，选择后直接在名称框输入名称，再使用时从名称框直接选择名称 "?不连续区域选择:按住Ctrl键用鼠标选择或在名称框输入各区域名称，区域名称间用逗号分隔如:A3:B7,A10:D17" 要点:1. 按不同需求灵活使用选择区域的方法，使工作更快捷 工具—选项使用技巧 工具-选项-重新计算——显示值为准——真正的四舍五入 每次输入公式时，EXCEL默认的格式需要以等号，加号或减号开头，但是如果在工具-选项-1-2-3的帮助-选择LOTUS1-2-3的公式后，则不用先输入等号，加号或减号，EXCEL会自动计算出结果。例如: 未转换前，输入公式的格式为:=2+5*3 转换后，输入公式的格式为:2+5*3 "工具-选项-视图中,在""窗口选项""下去掉""零值""后,余额为0就不显示了。" 工具,选项,视图,网格线前面的对勾去掉可以不显示网格线 隐藏工作表 通过【格式】|【工作表】|【隐藏】可以将活动工作表隐藏，但此时在【格式】|【工作表】里又出现了【取消隐藏】命令。为了让 【取消隐藏】命令不可用，可以选择【工具】|【保护】|【保护工作薄】，这样就可以让工作表隐藏得更好了。 借用自动更正来作快速录入。 方法是:工具—?自动更正—?在〖替换〗对话框中写上简单的，例如:―1‖字，—?在〖替换为〗对话框中写上:―快来参加Excel接龙游戏～‖—?添加—?确定，设定完成了。 以后在任何单元格只要输入―1‖就马上变成:―快来参加Excel接龙游戏～‖了。 用完后别忘了把它删除，不然就无法输入―1‖字了。 锁定单元格 如果想在一个单元格里输入不同的数值，以测试目标单元格在不同数值下的结果，比如:A2的公式为,A1*5。我们可以锁定A1，输入不同数值，按回车后，活动单元格不会下移，还是A1。 1.锁定单元格:用鼠标选定A1，按住Ctrl，用鼠标再点一次A1。就可以锁定了。 2.也可以用同样的方法在几个特定单元格间移动:选定一个单元格(比如A1)，按Ctrl，选中别的单元格(如:A3，C5等)，按Enter就可以在这几个单元格中来回移动 隐藏单元格内容有这样一种方法 1。设置单元格格式为―;;;‖(三个分号) 2。设置单元格格式-〉保护，把隐藏钩上。 3。保护工作表 这样无论在单元格里还是在编辑框里都看不见单元格内容了。 将存为文本的数字转换成数字 1.一次选中一个单元格 在―工具‖菜单上，单击―选项‖，再单击―错误检查‖选项卡。 请确保选中了―允许后台错误检查‖和―数字以文本形式存储‖框。 选中任何在左上角有绿色错误指示符的单元格。 在单元格旁边，单击出现的按钮，再单击―转换为数字‖。 2.一次选中整个区域 在空白单元格中，输入数字1。 选中单元格，并在―编辑‖菜单上，单击―复制‖。 选取需转换的存储为文本的数字范围。 在―编辑‖菜单上，单击―选择性粘贴‖。 在―运算‖下，单击―乘‖。 单击―确定‖。 删除在第一步中输入的单元格的内容。 注意一些财务程序显示负值时在该值右边带有负号(–)。要将此 文本字符串转换为数值，必须返回除最右边字符(即负号)以外的所有文本字符串字符，然后乘以–1。例如，如果单元格A2中的值为―156–‖，下面的公式将文本转换为值–156。 数据公式 "156-=LEFT(A2,LEN(A2)-1)*-1" 巧用Excel状态栏: 右键点击状态栏，可以选择均值，计数，计数值(只计包含数值的单元格)，最大值，最小值或求和。当你在工作表中选定单元格后，在状态栏就会出现相应的提示。 行列转置 选中需要转置的区域，[复制]，[选择性粘贴]，[转置]，行列即可转置。 "Ctrl+D和Ctrl+""(引号)的区别。" A1=251/4用Ctrl+D 1次，A2=251/4(拷贝内容和格式) 2次，A2=251/4 3次，A2=251/4 "A1=251/4用Ctrl+""" 1次，A2=25.25(格式有区别) 2次，A2=25.2525.25(不是数字) "3次，A2=25.2525.2525.25(跟REPT(A1,3)的结果一样)" "选中一行内连续的多个单元格，Ctrl+D可以一次向下拷贝，但Ctrl+"" 不能。" [数据]-[有效性] "1.在[设置]中设置数据的范围,在[出错警告]中,可以设置当输入数据错误时的提示信息." 2(文本输入时，避免在字符串前后无意输入多余的空格 假如在B列输入，选中B列，菜单中， 数据?有效性?设置?自定义?公式，输入公式:=len(B1)=len(TRIM(B1))，确定。 如何使用照相机 你可以在菜单栏上右单击右键，选择自定义，然后在菜单,工具,摄影，将照相机拖动到工具栏，可以用他来拍你所想要拍的东西。 调整整个工作表列宽或行高 要调整整个工作表列宽或行高，点击左上角顶格，拖动某列或某行线，即可改变整个工作表的列宽或行高，形成全部一样的列宽或行高。 让不同类型数据用不同颜色显示 方法1 在工资表中，如果想让大于等于2000元的工资总额以―红色‖显示，大于等于1500元的工资总额以―蓝色‖显示，低于1000元的工 资总额以―棕色‖显示，其它以―黑色‖显示，我们可以这样设置。 1.打开―工资表‖工作簿，选中―工资总额‖所在列，执行―格式?条件格式‖命令，打开―条件格式‖对话框。单击第二个方框右侧的下拉按钮，选中―大于或等于‖选项，在后面的方框中输入数值―2000‖。单击―格式‖按钮，打开―单元格格式‖对话框，将―字体‖的―颜色‖设置为―红色‖。 2.按―添加‖按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为―蓝色‖;小于1000，字体设置为―棕色‖)。 3.设置完成后，按下―确定‖按钮。 方法2 在单元格属性中自定义数据类型:[红色][>0];[蓝色][<0]，这样单元格的值为正数就显示红色，是负数就显示蓝色 单元格输入技巧 1(输入下标: 例:CO2 选中2菜单栏选―格式/单元格‖,特殊效果(左下),点击下标,确定～～ 2(选中任意块(连续或不连续)，在任一个单元格中输入要输内容，然后按下―Ctrl+ENTER‖组合键，即可将此内容复制到选中的多个连续或不连续的单元格中。 3(在自动填充时不使数据递增的办法: 按住[ctrl]键的同时拖动填充柄，这样可使自动填充变为复制了。 4(在输入长数字时，比如帐号。可在输入的数字前先加上英文状态的单引号。 "5.如果到excel作比较复杂的表的话。可以先在WORD里画好以后，复制，粘贴," 6.当你想复制公式到整列(至文件尾)可选中有公式的单元格，待出现黑色填充柄时，双击即可，只要相邻列没有空格，多少条记录也会一下子到底，很快噢～ 7.插入根号的简单方法 ―插入‖---―对象‖---―新建‖---―microsoft公式3.0‖ "8.如果你要在引用中使用单字节双引号(""),比如你要实现A1的显示为(""ILoveYou!""),则在A1中输入 =""""""""&""ILoveYou!" "&"""""""",即4个双引号在一起可以显示一个双引号." "=""""""ILoveYou""""" "更清楚" 9.要复制大量公式如几千上万行，选择第二行窗口冻结，将最后一行提至第二行，将公式拖至第二行，OK～ 10.如果你想给所有的数字都加上100的话: 首先在一个单元格中输入100，再复制;选定你要加的数，再来个―选择性粘贴‖，选择里面的―相加‖; "11(选中要向右填充的单元格，按住shift,选中要向右填充的目标单元格，松开shift，再按ctrl+R,可将此单元格的值向右填充。" 12(在原数基础将其变为万元，只需在一格中输入1000，复制，再选定转换范围，右击―选择性粘贴‖-数值(复选―除‖)即可～ 13(想要在多个单元格输入同一数据，先选中所需填写的单元格，然后用键盘输入，最后用Ctrl+Enter即可 14.如果在sheet1中制作了一个表头，在sheet2中也想用的话。先选中表头选复制，然后在sheet2中选粘贴，然后选择―保留原列宽‖，就可以了。 15.同时实现单元格的取数及链接: #REF! 16.如果你的工作薄中的所有的工作表都具有相同框架，可以直接把所有的工作表都选中，然后在其中一个工作表中作输入，就可以完成多个工作表中相同数据的填充了。 快速、完整复制工作表 "先打开两工作簿，一要复制，另一被复制，在要复制的工作簿中，右击左下角需复制工作表的名称，点―移动或复制工作表(M)""在工作簿栏中点需要复制到的工作簿，在下一栏中选复制到工作簿的位置，在‖建立副本―前打钩，按确定即可。" 剔除表里所有空格的最快方法 用查找取代，查找里按一下空格键，取代里什么也不输，然后点击全部取代。感觉很好 "excel查找替换中的*和?是通配符,分别可以替代多个字符和单个字符,但要查找替换*和?自身怎么实现呢?有一个绝窍:在*和?前加 ~即可使它们失去通配符的作用,也就可以查找替换它自身了" 标签取色 "用OFFICEXP的,在EXCEL的表标签""sheet1""等上点右键，可以选择标签颜色，以便快速区分。" 快速选择工作表 在Excel中，要在一个有很多个工作表的工作簿中快速的选择某一个工作表，只需将鼠标指针移到工作薄左下角的工作表滚动轴(SheetTabs)，然后点击右键，在快捷菜单中选择你想 要的工作表。 将单元格复制成图片: 1选择区域 2按住SHIFT不放，点击编辑/复制图片3按自己的要求在弹出的对话框里选择 4确定 双击格式刷，可反复使用。在工作表内单元格以外的地方单击右键，选择自定义可以方便地添加工具、菜单项及命令。