app教程网 学习教程 Excel表格的25招必学秘技(exlc表格技巧)

Excel表格的25招必学秘技(exlc表格技巧)

Excel表格的25招必学秘技, Excel电子表格的25个妙招在网上流传甚广,堪称Excel圣经的操作技巧,使许多用户意识到Excel的强大。作者还仔细阅读了全文,并将功能用红色处理,以方便用户学习。

exlc表格技巧

学习Excel表格秘密的25个技巧;

也许你已经在Excel中完成了数百张财务报表,也许你已经使用Excel函数实现了数千次复杂的操作,也许你认为Excel仅此而已,甚至毫无新意。

然而,平日里我们重复了无数次的得心应手的使用方法,只是Excel所有技巧的百分之一。本期话题从Excel中一些鲜为人知的技巧入手,领略Excel的独特风格。

首先,让不同类型的数据以不同的颜色显示

在工资表中,如果你想让2000元以上的工资总额用红色显示,1500元以上的工资总额用蓝色显示,1000元以下的工资总额用棕色显示,其他的用黑色显示,我们可以这样设置。

1.打开工资单工作簿,选择薪金总额所在的列,并执行“设置条件格式”命令以打开“条件格式”对话框。单击第二个框右侧的下拉按钮,选择大于或等于选项,并在后面的框中输入值2000。单击格式按钮,

打开“单元格格式”对话框,并将字体颜色设置为红色。

2.按下添加按钮,并设置其他条件(大于等于1500,字体设置为蓝色;小于1000,字体设置为棕色)。

3.设置完成后,按下确定按钮。

看看工资单。工资总额数据是按照你的要求用不同颜色显示的吗?

其次,建立一个类别下拉列表来填写项目。

我们经常需要将企业名称输入到表格中。为了保持名称的一致性,我们使用数据有效性函数构建一个类别下拉列表来填写项目。

1.在Sheet2中,根据类别(如工业企业、商业企业、个体企业等)将企业名称输入不同的栏中。)建立企业名称数据库。

2.选择A列(工业企业名称所在的列),在名称列中输入工业企业的字符,然后按enter键确认。

模仿上述操作,将B列和C列分别命名为商业企业和个人企业。

3.切换到Sheet1,选择需要输入企业类别的列(例如C列),执行“数据有效性”命令打开“数据有效性”对话框。在“设置”选项卡中,单击“允许”右侧的下拉按钮,选择“序列”选项,然后输入工业企业、商业企业,

单个企业序列(每个元素由英文逗号分隔),并确认退出。

选择需要输入企业名称的列(如D列),然后打开“数据有效性”对话框。选择序列选项后,在来源框中输入公式=间接(C1),并确认退出。

4.选择C列中的任意单元格(如C4),单击右侧的下拉按钮,并选择相应的企业类别填写该单元格。然后选择与该单元格对应的D列单元格(如D4),并单击下拉按钮。

您可以从相应类别的企业名称列表中选择所需的企业名称并将其填写在此单元格中。

提示:以后打印报表时,如果不需要打印企业类别列,可以选择该列,单击鼠标右键,选择隐藏选项并隐藏该列。

第三,建立一个新的常用文档菜单

在菜单栏上新建一个常用文档菜单,并在其中添加常用的工作簿文档,方便随时调用。

1.在工具栏的空白处单击鼠标右键,选择“自定义”选项打开“自定义”对话框。在命令选项卡中,选择类别下的新菜单项,然后将命令下的新菜单拖到菜单栏。

按下“更改选择”按钮,并在弹出式菜单的“命名”框中输入名称(如“常用文稿”)。

2.选择类别下的一个项目(如“插入选项”),选择右侧命令下的一个项目(如“超链接选项”),将其拖到“新建”菜单(常用文档)中,并像上面的操作一样命名(如“工资单”)以建立第一个工作簿文档列表的名称。

重复上述操作并添加更多文档列表名称。

3.在常用文档菜单中选择一个菜单项(如工资),单击鼠标右键,在弹出的快捷菜单中选择“分配超链接”的“打开”选项,打开“分配超链接”对话框。通过按搜索范围右侧的下拉按钮,找到相应的工作簿(如salary。xls等。)文件夹。

并选择工作簿文档。

重复上述操作,并将菜单项与其对应的工作簿文档进行超链接。

4.将来需要在常用文档菜单中打开工作簿文档时,只需展开常用文档菜单并单击相应的选项。

提示:虽然我们将超链接选项拖到了常用文档菜单中,但它并不影响“插入”菜单中的超链接菜单项和常用工具栏上的“插入超链接”按钮的功能。

第四,制作专业符号工具条

在编辑专业表格时,我们经常需要输入一些特殊的专业符号。为了方便输入,我们可以制作自己的专业符号工具栏。

1.执行工具宏录制新宏命令,打开录制新宏对话框,输入宏名?比如付豪1号?并将宏保存在个人宏工作簿中,然后确认开始录制。选择“录制宏”工具栏上的“相对引用”按钮,然后在单元格中输入所需的特殊符号。

然后单击宏录制工具栏上的停止按钮,完成宏录制。

模仿上述操作,逐一记录其他特殊符号的输入宏。

2.打开自定义对话框,单击工具栏选项卡中的新建按钮,将弹出新建工具栏对话框。输入名称professional symbol,然后工作区中会出现一个工具栏。

切换到“命令”选项卡,选择类别下的宏,并将命令下的自定义按钮项拖到专业符号栏(根据特殊符号的数量拖动按钮)。

3.选择一个自定义按钮,并以第二个秘密技能的第一点命名。

4.右键单击一个命名按钮,在弹出的快捷菜单中选择“指定宏”选项,打开“指定宏”对话框,选择相应的宏(如付豪1),确认退出。

重复此步骤,将按钮与相应的宏链接起来。

5.关闭自定义对话框。将来,您可以像使用普通工具栏一样,使用专业符号工具栏在单元格中快速输入专业符号。

5.使用视窗管理器保存多个打印页面。

有些工作表经常需要打印不同的区域,因此请使用视窗管理器。

1.打开需要打印的工作表,用鼠标拖动不需要打印的行(或列),选中它们并单击鼠标右键。在随后出现的快捷菜单中,选择“隐藏”选项以隐藏不需要打印的行(或列)。

2、执行视图视面管理器命令,打开视面管理器对话框,单击添加按钮,弹出添加视面对话框,输入一个名称(如上报表)后,单击确定按钮。

3、将隐藏的行(或列)显示出来,并重复上述操作,添加好其它的打印视面。

4、以后需要打印某种表格时,打开视面管理器,选中需要打印的表格名称,单击显示按钮,工作表即刻按事先设定好的界面显示出来,简单设置、排版一下,按下工具栏上的打印按钮,一切就OK了。

六、让数据按需排序

如果你要将员工按其所在的部门进行排序,这些部门名称既的有关信息不是按拼音顺序,也不是按笔画顺序,怎么办?可采用自定义序列来排序。

1、执行格式选项命令,打开选项对话框,进入自定义序列标签中,在输入序列下面的方框中输入部门排序的序列(如机关,车队,一车间,二车间,三车间等),单击添加和确定按钮退出。

2、选中部门列中任意一个单元格,执行数据排序命令,打开排序对话框,单击选项按钮,弹出排序选项对话框,按其中的下拉按钮,选中刚才自定义的序列,按两次确定按钮返回,所有数据就按要求进行了排序。

七、把数据彻底隐藏起来

工作表部分单元格中的内容不想让浏览者查阅,只好将它隐藏起来了。

1、选中需要隐藏内容的单元格(区域),执行格式单元格命令,打开单元格格式对话框,在数字标签的分类下面选中自定义选项,然后在右边类型下面的方框中输入;(三个英文状态下的分号)。

2、再切换到保护标签下,选中其中的隐藏选项,按确定按钮退出。

3、执行工具保护保护工作表命令,打开保护工作表对话框,设置好密码后,确定返回。

经过这样的设置以后,上述单元格中的内容不再显示出来,就是使用Excel的透明功能也不能让其现形。

提示:在保护标签下,请不要清除锁定前面复选框中的号,这样可以防止别人删除你隐藏起来的数据。

八、让中、英文输入法智能化地出现

在编辑表格时,有的单元格中要输入英文,有的单元格中要输入中文,反复切换输入法实在不方便,何不设置一下,让输入法智能化地调整呢?

选中需要输入中文的单元格区域,执行数据有效性命令,打开数据有效性对话框,切换到输入法模式标签下,按模式右侧的下拉按钮,选中打开选项后,确定退出。

以后当选中需要输入中文的单元格区域中任意一个单元格时,中文输入法(输入法列表中的第1个中文输入法)自动打开,当选中其它单元格时,中文输入法自动关闭。

九、让自动更正输入统一的文本

你是不是经常为输入某些固定的文本,如《电脑报》 而烦恼呢?那就往下看吧。

1、执行工具自动更正命令,打开自动更正对话框。

2、在替换下面的方框中输入pch(也可以是其他字符,pcw用小写),在替换为下面的方框中输入《》 ,再单击添加和确定按钮。

3、以后如果需要输入上述文本时,只要输入pch字符?此时可以不考虑pch的大小写?然后确认一下就成了。

十、在Excel中自定义函数

Excel函数虽然丰富,但并不能满足我们的所有需要。我们可以自定义一个函数,来完成一些特定的运算。下面,我们就来自定义一个计算梯形面积的函数:

1、执行工具宏Visual Basic编辑器菜单命令(或按Alt+F11快捷键),打开Visual Basic编辑窗口。

2、在窗口中,执行插入模块菜单命令,插入一个新的模块模块1。

3、在右边的代码窗口中输入以下代码:

Function V(a,b,h)V=h*(a+b)/2End Function

4、关闭窗口,自定义函数完成。

以后可以像使用内置函数一样使用自定义函数。

提示:用上面方法自定义的函数通常只能在相应的工作簿中使用。

十一、表头下面衬张图片

为工作表添加的背景,是衬在整个工作表下面的,能不能只衬在表头下面呢?

1、执行格式工作表背景命令,打开工作表背景对话框,选中需要作为背景的图片后,按下插入按钮,将图片衬于整个工作表下面。

2、在按住Ctrl键的同时,用鼠标在不需要衬图片的单元格(区域)中拖拉,同时选中这些单元格(区域)。

3、按格式工具栏上的填充颜色右侧的下拉按钮,在随后出现的调色板中,选中白色。经过这样的设置以后,留下的单元格下面衬上了图片,而上述选中的单元格(区域)下面就没有衬图片了(其实,是图片被白色遮盖了)。

提示:衬在单元格下面的图片是不支持打印的。

十二、用连字符来合并文本

如果我们想将多列的内容合并到一列中,不需要利用函数,一个小小的连字符就能将它搞定(此处假定将B、C、D列合并到一列中)。

1、在D列后面插入两个空列(E、F列),然后在D1单元格中输入公式:=B1C1D1。

2、再次选中D1单元格,用填充柄将上述公式复制到D列下面的单元格中,B、C、D列的内容即被合并到E列对应的单元格中。

3、选中E列,执行复制操作,然后选中F列,执行编辑选择性粘贴命令,打开选择性粘贴对话框,选中其中的数值选项,按下确定按钮,E列的内容(不是公式)即被复制到F列中。

4、将B、C、D、E列删除,完成合并工作。

提示:完成第1、2步的操作,合并效果已经实现,但此时如果删除B、C、D列,公式会出现错误。故须进行第3步操作,将公式转换为不变的值。

十三、生成绩条

常有朋友问如何打印成绩条这样的问题,有不少人采取录制宏或VBA的方法来实现,这对于初学者来说有一定难度。出于此种考虑,我在这里给出一种用函数实现的简便方法。

此处假定学生成绩保存在Sheet1工作表的A1至G64单元格区域中,其中第1行为标题,第2行为学科名称。

输入公式:=IF(MOD(ROW(),3)=0,IF(0MOD?ROW(),3(=1,sheet1!A,INDEX(sheet1!$A:$G,INT(((ROW()+4)/3)+1),COLUMN())))。

2、再次选中A1单元格,用填充柄将上述公式复制到B1至G1单元格中;然后,再同时选中A1至G1单元格区域,用填充柄将上述公式复制到A2至G185单元格中。

至此,成绩条基本成型,下面简单修饰一下。

3、调整好行高和列宽后,同时选中A1至G2单元格区域(第1位学生的成绩条区域),按格式工具栏边框右侧的下拉按钮,在随后出现的边框列表中,选中所有框线选项,为选中的区域添加边框(如果不需要边框,

可以不进行此步及下面的操作)。

4、同时选中A1至G3单元格区域,点击常用工具栏上的格式刷按钮,然后按住鼠标左键,自A4拖拉至G186单元格区域,为所有的成绩条添加边框。

按打印按钮,即可将成绩条打印出来。

十四、Excel帮你选函数

在用函数处理数据时,常常不知道使用什么函数比较合适。Excel的搜索函数功能可以帮你缩小范围,挑选出合适的函数。

执行插入函数命令,打开插入函数对话框,在搜索函数下面的方框中输入要求(如计数),然后单击转到按钮,系统即刻将与计数有关的函数挑选出来,并显示在选择函数下面的列表框中。再结合查看相关的帮助文件,

即可快速确定所需要的函数。

十五、同时查看不同工作表中多个单元格内的数据

有时,我们编辑某个工作表(Sheet1)时,需要查看其它工作表中(Sheet2、Sheet3)某个单元格的内容,可以利用Excel的监视窗口功能来实现。

执行视图工具栏监视窗口命令,打开监视窗口,单击其中的添加监视按钮,展开添加监视点对话框,用鼠标选中需要查看的单元格后,再单击添加按钮。重复前述操作,添加其它监视点。

以后,无论在哪个工作表中,只要打开监视窗口,即可查看所有被监视点单元格内的数据和相关信息。

十六、为单元格快速画边框

在Excel 2002以前的版本中,为单元格区域添加边框的操作比较麻烦,Excel 2002对此功能进行了全新的拓展。

单击格式工具栏上边框右侧的下拉按钮,在随后弹出的下拉列表中,选绘图边框选项,或者执行视图工具栏边框命令,展开边框工具栏。

单击工具栏最左侧的下拉按钮,选中一种边框样式,然后在需要添加边框的单元格区域中拖拉,即可为相应的单元格区域快速画上边框。

提示:如果画错了边框,没关系,选中工具栏上的擦除边框按钮,然后在错误的边框上拖拉一下,就可以清除掉错误的边框。如果需要画出不同颜色的边框,可以先按工具栏右侧的线条颜色按钮,

在随后弹出的调色板中选中需要的颜色后,再画边框即可。这一功能还可以在单元格中画上对角的斜线。

十七、控制特定单元格输入文本的长度

你能想象当你在该输入四位数的单元格中却填入了一个两位数,或者在该输入文字的单元格中你却输入了数字的时候,Excel就能自动判断、即时分析并弹出警告,那该多好啊!要实现这一功能,对Excel来说,

也并不难。

例如我们将光标定位到一个登记年份的单元格中,为了输入的统一和计算的方便,我们希望年份都用一个四位数来表示。所以,我们可以单击数据菜单的有效性选项。在设置卡片有效性条件的允许下拉菜单中选择文本长度。

然后在数据下拉菜单中选择等于,且长度为4。同时,我们再来到出错警告卡片中,将输入无效数据时显示的出错警告设为停止,并在标题和错误信息栏中分别填入输入文本非法!和请输入四位数年份。字样。

很显然,当如果有人在该单元格中输入的不是一个四位数时,Excel就会弹出示的警告对话框,告诉你出错原因,并直到你输入了正确样式的数值后方可继续录入。神奇吧?其实,在Excel的数据有效性判断中,

还有许多特殊类型的数据格式可选,比如文本类型啊,序列大小啊,时间远近啊,如你有兴趣,何不自作主张,自己设计一种检测标准,让你的Excel展示出与众不同的光彩呢。

十八、成组填充多张表格的固定单元格

我们知道每次打开Excel,软件总是默认打开多张工作表。由此就可看出Excel除了拥有强大的单张表格的处理能力,更适合在多张相互关联的表格中协调工作。要协调关联,当然首先就需要同步输入。因此,

在很多情况下,都会需要同时在多张表格的相同单元格中输入同样的内容。

那么如何对表格进行成组编辑呢?首先我们单击第一个工作表的标签名Sheet1,然后按住Shift键,单击最后一张表格的标签名Sheet3(如果我们想关联的表格不在一起,可以按住Ctrl键进行点选)。

此时,我们看到Excel的标题栏上的名称出现了工作组字样,我们就可以进行对工作组的编辑工作了。在需要一次输入多张表格内容的单元格中随便写点什么,我们发现,工作组中所有表格的同一位置都显示出相应内容了。

但是,仅仅同步输入是远远不够的。比如,我们需要将多张表格中相同位置的数据统一改变格式该怎么办呢?首先,我们得改变第一张表格的数据格式,再单击编辑菜单的填充选项,然后在其子菜单中选择至同组工作表。这时,

Excel会弹出填充成组工作表的对话框,在这里我们选择格式一项,点确定后,同组中所有表格该位置的数据格式都改变了。

十九、改变文本的大小写

在Excel中,为表格处理和数据运算提供最强大支持的不是公式,也不是数据库,而是函数。不要以为Excel中的函数只是针对数字,其实只要是写进表格中的内容,Excel都有对它编辑的特殊函数。

例如改变文本的大小写。

在Excel 2002中,至少提供了三种有关文本大小写转换的函数。它们分别是:=UPPER(源数据格),将文本全部转换为大写;=LOWER(源数据格),

将文本全部转换成小写;=PROPER(源数据格),将文本转换成适当的大小写,如让每个单词的首字母为大写等。例如,我们在一张表格的A1单元格中输入小写的Excel,

然后在目标单元格中输入=UPPER(A1),回车后得到的结果将会是Excel。同样,如果我们在A3单元格中输入mr.weiwei,然后我们在目标单元格中输入=PROPER(A3),

那么我们得到的结果就将是Mr.Weiwei了。

二十、提取字符串中的特定字符

除了直接输入外,从已存在的单元格内容中提取特定字符输入,绝对是一种省时又省事的方法,特别是对一些样式雷同的信息更是如此,比如员工名单、籍贯等信息。

如果我们想快速从A4单元格中提取称谓的话,最好使用=RIGHT(源数据格,提取的字符数)函数,它表示从A4单元格最右侧的字符开始提取2个字符输入到此位置。当然,如果你想提取姓名的话,

则要使用=LEFT(源数据格,提取的字符数)函数了。还有一种情况,我们不从左右两端开始,而是直接从数据中间提取几个字符。比如我们要想从A5单元格中提取武汉两个字时,

就只须在目标单元格中输入=MID(A5,4,2)就可以了。意思是:在A5单元格中提取第4个字符后的两个字符,也就是第4和第5两个字。

二十一、把基数词转换成序数词

将英文的基数词转换成序数词是一个比较复杂的问题。因为它没有一个十分固定的模式:大多数的数字在变成序数词都是使用的th后缀,但大凡是以1、2、3结尾的数字却分别是以st、nd和rd结尾的。而且,

11、12、13这3个数字又不一样,它们却仍然是以th结尾的。因此,实现起来似乎很复杂。其实,只要我们理清思路,找准函数,只须编写一个公式,就可轻松转换了。

请看:=A2IF(OR(VALUE(RIGHT(A2,2))={11,12,13}),th,IF(OR(VALUE(RIGHT(A2))={1,2,3,},CHOOSE(RIGHT(A2),st,nd,rd),th))

提示:该公式尽管一长串,不过含义却很明确:如果数字是以11、12、13结尾的,则加上th后缀;如果第1原则无效,则检查最后一个数字,

以1结尾使用st、以2结尾使用nd、以3结尾使用rd;如果第1、2原则都无效,那么就用th。因此,基数词和序数词的转换实现得如此轻松和快捷。

二十二、用特殊符号补齐位数

和财务打过交道的人都知道,在账面填充时有一种约定俗成的安全填写法,那就是将金额中的空位补齐,或者在款项数据的前面加上$之类的符号。其实,在Excel中也有类似的输入方法,那就是REPT函数。

它的基本格式是=REPT(特殊符号,填充位数)。

比如,我们要在中A2单元格里的数字结尾处用#号填充至16位,就只须将公式改为=(A2REPT(#,16-LEN(A2)))即可;如果我们要将A3单元格中的数字从左侧用#号填充至16位,

就要改为=REPT(#,16-LEN(A3)))A3;另外,如果我们想用#号将A4中的数值从两侧填充,

则需要改为=REPT(#,8-LEN(A4)/2)A4REPT(#)8-LEN(A4)/2);如果你还嫌不够专业,要在A5单元格数字的顶头加上$符号的话,

那就改为:=(TEXT(A5,$#,##0.00(REPT(#,16-LEN(TEXT(A5,$#,##0.00)))),一定能满足你的要求。

二十三、创建文本直方图

除了重复输入之外,REPT函数另一项衍生应用就是可以直接在工作表中创建由纯文本组成的直方图。它的原理也很简单,就是利用特殊符号的智能重复,按照指定单元格中的计算结果表现出长短不一的比较效果。

比如我们首先制作一张年度收支平衡表,然后将E列作为直方图中预算内月份的显示区,将G列则作为直方图中超预算的显示区。然后根据表中已有结果D列的数值,用Wingdings字体的N字符表现出来。

具体步骤如下:

在E3单元格中写入公式=IF(D30,REPT(n,ROUND(D3*100,0)),),也拖动填充柄至G14。我们看到,一个没有动用Excel图表功能的纯文本直方图已展现眼前,方便直观,简单明了。

二十四、计算单元格中的总字数

有时候,我们可能对某个单元格中字符的数量感兴趣,需要计算单元格中的总字数。要解决这个问题,除了利用到SUBSTITUTE函数的虚拟计算外,还要动用TRIM函数来删除空格。

比如现在A1单元格中输入有how many words?字样,那么我们就可以用如下的表达式来帮忙:

=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),))+1)

该式的含义是先用SUBSTITUTE函数创建一个新字符串,并且利用TRIM函数删除其中字符间的空格,然后计算此字符串和原字符串的数位差,从而得出空格的数量,最后将空格数+1,

就得出单元格中字符的数量了。

二十五、关于欧元的转换

这是Excel 2002中的新工具。如果你在安装Excel 2002时选择的是默认方式,那么很可能不能在工具菜单中找到它。不过,我们可以先选择工具菜单中的加载宏,然后在弹出窗口中勾选欧元工具选项,

确定后Excel 2002就会自行安装了。

完成后我们再次打开工具菜单,单击欧元转换,一个独立的专门用于欧元和欧盟成员国货币转换的窗口就出现了。与Excel的其他函数窗口一样,我们可以通过鼠标设置货币转换的源区域和目标区域,

然后再选择转换前后的不同币种即可。所示的就是100欧元分别转换成欧盟成员国其他货币的比价一览表。当然,为了使欧元的显示更显专业,我们还可以点击Excel工具栏上的欧元按钮,

这样所有转换后的货币数值都是欧元的样式了。

以上内容便是Excel表格的25招必学秘技,用户要特别注意其中的函数,是以全英文符号书写,只要带有一个中文字符,整条命令就不再可用。

Excel表格的25招必学秘技,以上就是本文为您收集整理的Excel表格的25招必学秘技最新内容,希望能帮到您!更多相关内容欢迎关注。

本文来自网络,不代表本站立场,转载请注明出处:https: