Ecel表格的招必学秘技留着慢慢看

1、Excel 表格的 35 招必学秘技(留着慢慢看！) 天生绩条常有冤家问“如何打印成绩条”这样的答题，有不长己采用录制宏或VBA的办法来实现，这关于始教者来道有必定易度。出于此种斟酌，我在这里给出一种用函 数实现的烦琐办法。此处真定先生成就保留在 Sheetl工作表的A1至G64单元格区域中，其中第1 行动题目，第 2 行动教科名称。1. 切换到 Sheet2 工作表中，选中 A1 单元格，输入公式： =IF(MOD(ROW(，)3)=0，'，IF(OMOD?ROW() 3(=1 , sheetl ! Au, INDEX(sheet1 ! $A: $G INT(ROW() 4)/3)

2、1)， COLUMN()。2. 再次选中A1单元格，用“填充柄”将上述公式复制到 B1至G1单元格中； 然后，再同时选中A1至G1单元格区域，用“填充柄”将上述公式单制到 A2至 G185单元格中。至此，成绩条基础成型，下面繁单润饰一下。3. 调剂佳行下战列阔后，同时选中 A1至G2单元格区域(第1位先生的成就条 区域)，按“格局”工具栏“边框”左正的下推按钮，在随后呈现的边框列表中， 选中“一切框线”选项，为选中的区域添加边框 (假如不需要边框，能够不进行此 步及下面的操作 )。4. 同时选中A1至G3单元格区域，点击“常用”工具栏上的“格式刷”按钮， 然后按住鼠标右键，自A4拖拉至G186

3、单元格区域，为所有的成绩条添加边框。按“打印”按钮，即可将成就条打印进去。十四、 Excel 助您选函数 在用函数处置数据时，经常不晓得应用什么函数比拟适合。 Excel 的“搜寻函 数”功效可以助你减少范畴，选择出适合的函数。执行“插入一函数”命令，打开“插入函数”对话框，在“搜寻函数”下面的 方框中输入要供 (如“计数” )，然后单击“转到”按钮，体系即刻将与“计数”有 关的函数选择出来，并显示在“选择函数”下面的列表框中。再联合查看相干的辅 助文件，即可快快确定所需要的函数。十五、同时查看不同工作表中多个单元格内的数据 有时，我们编辑某个工作表 (Sheet1) 时，需要查看其它工作表中

4、 (Sheet2、 Sheet3)某个单元格的内容，可以利用 Excel的“监视窗口”功能来实现。执行“视图工具栏监视窗口”命令，打开“监视窗口”，单击其中的“添 加监督”按钮，铺开“添加监视点”对话框，用鼠标选中需要查看的单元格后，再 单击“添加”按钮。反复后述操作，添加其它“监督正点”。以后，不管在哪个工作表中，只需打开“监视窗口”，即可查瞅所有被监督正 点单元格内的数据和相干疑作。十六、为双元格疾速绘边框在 Excel 2002 以后的版原中，为单元格区域加减边框的操作比拟费事， Excel 2002对于彼功效入止了齐旧的拓铺。单打“格局”工具栏上“边框”左正的下拉按钮，在随后弹出的下推

5、列表中， 选“画图边框”选项，或许执行“视图一工具栏一边框”命令，铺启“边框”工具 栏。单击工具栏最右侧的下拉按钮，选中一种边框样式，然后在需要添加边框的单 元格区域中拖沓，即可为相应的单元格区域疾速绘上边框。提示：如果画对了边框，不要紧，选中工具栏上的“揩除边框”按钮，然后 在过错的边框上拖沓一下，就可以肃清掉毛病的边框。如果需要画出不同颜色的 边框，可以先按工具栏右侧的“线条颜色”按钮，在随后弹出的调色板中选中需要 的颜色后，再画边框即可。这一过能还可以在单元格中画上对角的斜线。 十七、把持特定单元格输入文本的长度你能想象当你在该输入四位数的单元格中却填入了一个两位数，或许在该输入 文字的

6、单元格中你却输入了数字的时分， Excel 就能自动断定、立即剖析并弹出警 告，那该多好啊 ! 要真现这一过能，对 Excel 来道，也并不易。例如我们将光标定位到一个注销“暮年份”的单元格中，为了输入的同一和计 算的便利，我们盼望“年份”都用一个四位数来表现。所以，我们可以单击“数 据”菜单的“无效性”选项。在“设置”卡片“无效性前提”的“容许”下拉菜单 中挑选“文本少度”。然后在“数据”下拉菜单当选择“即是”，且“少度”为 “4”。同时，我们再来到“出对正告”卡片中，将“输入有效数据时显示的犯错 正告”设为“结束”，并在“题目”和“过错疑作”栏中分辨填入“输入文本是 法! ”和“请输入四位

7、数暮年份。”字样。很显然，当如果有己在当单元格中输入的不是一个四位数时， Excel 就会弹出 示的正告对话框，告知你犯错缘由，并直到你输入了准确“样式”的数值前方可持 续录入。神偶吧 ?实在，在 Excel 的“数占有效性”断定中，借有很多特殊类型的 数据格式可选，比如“文本类型”啊，“序列大小”啊，“时光遥远”啊，如你有 兴致，何不自作主意，本人设计一种检测尺度，让你的 Excel 展现出不同凡响的光 荣呢。十八、成组挖充多驰表格的流动单元格人们晓得每主挨启 Excel ，硬件老是默许翻开少张工做表。由彼就否望出Excel 除了具有强盛的单驰表格的处置才能，更合适在多张互相联系关系的表格中

8、 调和农作。要和谐闭联，该然尾后便需要同步输出。因而，正在良多情形上，皆会 须要同时在多张里格的雷同双元格外赢进同样的外容。那么如何对表格进行成组编纂呢 ?首后我们单击第一个工作表的标签名“Sheetl”，然后按住Shift键，单击最初一张表格的标签名“ Sheet3” （如因我 们念联系关系的表格不在一同，可以按住 Ctrl 键进行面选 ）。此时，我们瞅到 Excel 的题目栏上的称号呈现了“工作组”字样，我们就否以进行对工作组的编纂 工作了。在需要一主输入多张表格内容的单元格中随意写正点什么，我们发明，“工作组”中一切表格的统一地位皆显示出相当内容了。但是，仅仅同步输入是遥近不够的。比如，

9、我们需要将多张表格中雷同地位的 数据同一改变格式该怎样办呢 ?首先，我们得转变第一张表格的数据格式，再单击 “编辑”菜单的“填充”选项，然后在其女菜单当选择“至同组工作表”。这时，Excel 会弹出“填充成组工作表”的对话框，在这外我们选择“格式”一项，点 “确定”后，同组中所有表格该地位的数据格式都改变了。十九、转变文本的大小写在 Excel 中，为表格处置和数据运算供给最强盛支撑的不是公式，也不是数据 库，而是函数。不要认为 Excel 中的函数只是针对数字，实在只需是写进表格中的 内容， Excel 都有对它编辑的特殊函数。例如转变文本的大大写。在 Excel 2002 中，至多供给了三

10、类有闭文本 *d9d6a71c03310d091d52ee917807ecb 委婉换的函数。它们分辨是：“ =UPPEF源 数据格）”，将文本全体转换为大写；“ =LOWE流数据格）”，将文本齐部转换成 小写；“ =PROPE源数据格）”，将文本转换成“恰当”的大小写，如争每个单词 的尾字女为小写等。例如，我们在一张表格的 A1 单元格中输入大写的“ excel ”， 然后在目的单元格中输入“ =UPPER（A1”） ，归车后失掉的解果将会是“ EXCE”L 。 同样，如因我们在A3单元格中输入“ mr.weiwei ”，然后我们在纲本单元格中输入“=PROPER（A3），这么我们失到的成果就

11、将是“ Mr.Weiwei” 了。 二十、提与字符串中的特定字符 除了间接输入外，自未具有的单元格内容中降与特定字符输入，相对是一类费 时又费事的方式，特殊是对一些样式相同的信息更是如斯，好比员工名单、籍贯等 信息。如果我们想速快从A4单元格中提取称呼的话，最好使用“ =RIGHT源数据格， 提取的字符数）”函数，它表现“从A4单元格最右侧的字符开初提取2个字符”输 入到此位置。当然，如果你想提取姓名的话，则要使用“=LEFT流数据格，提取的字符数）”函数了。还有一种情形，我们不从左右两端开端，而是间接从数据两头 提取几个字符。比如我们要想从 A5单元格中提取“威严汉”两个字时，就只须在 目的

12、单元格中输入“ =MID（A5,4,2） ”就可以了。意义是：在 A5单元格中提与第4 个字符后的两个字符，也就是第 4 和第 5 两个字。七、把数据彻顶隐蔽起来 工作表部门单元格中的内容不念争阅读者查阅，只佳将它暗藏止来了。1. 选中需要隐藏内容的单元格（区域），执行“格式-单元格”命令，打开“单 元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在 左边“类型”下面的方框中输入“；” （ 三个英文状况下的分号 ） 。2. 再切换到“掩护”标签下，选中其中的“现躲”选项，按“确定”按钮退 出。3. 执行“工具-维护-掩护工作表”命令，打开“维护工作表”对话框，设置 好稀码

13、后，“确定”前往。经由这样的设置以后，上述单元格中的内容不再显示出来，就是使用 Excel 的 透亮功效也不能让其现形。提示：在“维护”标签下，请不要肃清“锁定”后面复选框中的“V”号，这 样可以避免他人删除你现躲起来的数据。八、让中、英文输入法愚能化天出隐 在编辑表格时，有的单元格中要输入英文，有的单元格中要输入中文，重复切 换输入法其实不方即，何不设置一下，让输入法愚能化天调剂呢 ?选中需要输入中文的单元格区域，执行“数据T无效性”命令，打开“数占有 效性”对话框，切换到“输入法模式”标签下，按“模式”右正的下拉按钮，选中 “打开”选项后，“确定”退出。以后中选中需要输入中文的单元格区域中

14、恣意一个单元格时，中文输入法 （输 入法列表中的第 1个中文输入法 ）自静打开，该选中其它单元格时，中文输入法主 动封闭。九、让“主动更反”输入同一的文本 您是不是常常为输入某些流动的白原，如电脑报而懊恼呢 ?这就去下望 吧。1. 执行“工具一主动更反”命令，打开“自静更反”对话框。2. 在“为换”下面的方框中输入“ pcW'（也可以是其他字符，“ pcW'用小 写），在“调换为”下面的方框中输入“电脑报” ，再单击“添加”和“确 定”按钮。3. 当前假如须要赢进下述白原时，只需输出“ pcW'字符？彼时能够没有斟酌 “ pcW'的小大写？，然先确认一上便败了。

15、十、在 Excel 中自定义函数Excel 函数固然丰盛，但并不能知足我们的所有需要。我们可以自定义一个函 数，来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数：1. 执行“工具一宏一 Visual Basic 编辑器”菜单命令（或按“ Alt F11 ”速捷 键） ，打开 Visual Basic 编辑窗口。2. 在窗心中，执行“拔出一模块”菜单命令，拔入一个新的模块一一模块1。3. 在左边的“代码窗口'中输入以下代码：Function V（a,b,h）V = h*（a b）/2End Function4. 封闭窗心，自订义函数完败。 以后可以像使用内置函数一样使用自

16、定义函数。 提醒：用下面方式自定义的函数通常只能在相应的工作簿中应用。 十一、表头下面衬张图片 为工作表加减的负景，非衬正在全部农作表上面的，能不能只衬在表头下里呢1. 执行“格式一工作表一背景”命令，打开“工作表负景”对话框，选中需要 作为负景的图片后，按下“拔出'按钮，将图片衬于全部工作表下面。2. 在按住 Ctrl 键的同时，用鼠标在不需要衬图片的单元格 （区域）中拖拉，同 时选中这些单元格 （区域）。3. 按“格式'工具栏上的“挖充色彩'右侧的下拉按钮，在随后涌现的“调色 板'中，选中“黑色'。经由那样的设放当前，留下的单元格下面衬上了图片，而

17、上述选中的单元格 （区域）下面就出有衬图片了 （实在，是图片被“红色'遮掀了 ）。降示?衬在单元格下面的图片是不支撑打印的。十二、用连字符“ &'来合并文本 假如人们念将少列的内容开并到一列中，不需要应用函数，一个大大的连字符 “ &”就能将它搞订（此处真定将B、C、D列开并到一列中）。1. 在D列前面拔出两个空列（E、F列），然后在D1单元格中输入母式： =B1 &C1 &D12. 再次选中D1单元格，用“填充柄”将上述公式复制到 D列下面的单元格 中，B、C、D列的内容即被合并到E列对应的单元格中。3. 选中E列，执止“单造”操做，然先选中

18、F列，履行“编纂-选择性粘揭” 命令，翻开“挑选性粘揭”对于话框，选中其中的“数值”选项，按下“断定”按 钮，E列的外容(没有非母式)便被单造到F列中。4. 将 B、 C、 D、 E 列增除，完成合并工作。提醒：完成第 1、 2步的操作，开并后果曾经完成，但此时如因增除 B、 C、 D 列，母式会涌现过错。新须进行第 3 步操作，将公式委婉换为不变的“值”。二十一、把基数词转换成序数词 将英文的基数词转换成序数词是一个比拟复纯 的答题。由于它出有一个非常流动的模式：大少数的数字在变成序数词都是使用的“th ”后缀，但大但凡以“ 1”、“ 2”、“ 3”开头的数字却分离是以“ st ”、 “nd

19、”和“ rd ”结头的。而且，“ 11”、“12”、“13”这3个数字又不一样，它 们却仍旧是以“ th ”结头的。果此，完成起来好像很庞杂。其真，只要我们理浑思 绪，觅准函数，只须编写一个公式，就可沉紧转换了。不信，请看：“ =A2&IF(OR(VALUE(RIGHT(A2,2)=11,12,13), th ，IF(0R(VALUE(RIGHT(A2) =1,2,3,CHOOSE(RIGHT(A2), st ， nd， rd )， th )”。该公式绝管 一少串，不外露义却很明白：如果数字是以“11”、“12”、“13”开头的，则加上“th ”后缀；如果第1本则有效，则检讨最初一个数

20、字，以“ 1”开头使用“ st ”、以“2”解尾使用“ n d”、以“3”解头使用“ rd ”；如果第1、2准绳 都有效，那么就用“ th ”。因而，基数词和序数词的转换真隐失如斯紧张和快速。二十二、用特别符号挖全位数 和财务打过接讲的人都晓得，在账面填充时有一种商定雅成的“平安填写 法”，那就是将金额中的空位挖全，或许在款项数据的后面加上“$”之类的符号。其实，在Excel中也有相似的输入办法，那就是“ REPT函数。它的基础格式 是“ =REPT(“特殊符号”，填充位数)”。比如，我们要在中A2单元格外的数字结尾处用“ #”号填充至16位，就只须 将公式改为“ =(A2&REPT(

21、 # ,16 -LEN(A2) ”即可；如果我们要将 A3单元格中 的数字从右侧用“ # ”号填充至16位，就要改为“ =REPT( # ,16- LEN(A3)&A3” ；另外，如果我们想用“ #”号将A4中的数值从两侧填充，则需要 改为“ =REPT( # ,8 -LEN(A4)/2)&A4&REPT( #)8 - LEN(A4)/2) ”；如果你还嫌 不够专业，要在A5单元格数字的底头加上“ $”符号的话，那就改为：“=(TEXT(A5, $#,#0.00 (&REPT(' # ,16 - LEN(TEXT(A5 $#,#0.00 )”，必定能知足

22、你的请求。两十三、创立白本曲圆图除了沉复输入之外，“ REPT函数另一项衍生利用就是可以间接在工作表中创 立由杂文本组成的直方图。它的本理也很简略，就是本用特殊符号的愚能重复，依 照指定单元格中的计算成果表示出是非不一的比较后果。好比我们尾后制造一驰暮年度支收均衡表，然后将“E列”作为曲圆图中“预算内”月份的隐示区，将“G列”则作为曲方图中“超预算”的显示区。然后依据表中未有成果“D列”的数值，用“ Wingdings”字体的“ N'字符表示进去。详细 步骤如下：在 E3单元格中写入公式“ =IF(D30,REPT( n，ROUND(D3*100,0), )”， 也拖动填充柄至G14我

23、们看到，一个出有动用 Excel图表功能的杂文本直方图已 展示面前，方即直观，简略亮了。二十四、盘算单元格中的分字数 有时分，我们能够对于某个双元格中字符的数目感兴致，需要盘算单元格中的 分字数。要系决那个答题，除了应用到“ SUBSTITUTE函数的实拟盘算外，借要静 用“TRIM函数来增除空格。好比如今 A1单元格中输出无“ how many words?”字 样，这么人们就能够用如下的表达式来助闲：“=IF(LEN(A1)=0,0,LEN(TRIM(A1) -LEN(SUBSTITUTE(TRIM(A1), , , ) 1)”该式的露义是先用“ SUBSTITUTE函数创立一个新字符串，

24、并且应用“ TRIM 函数删除其中字符间的空格，然后计算此字符串和本字符串的数位好，自而得出 “空格”的数质，最初将空格数 1 ，就失出单元格中字符的数目了。二十五、关于欧元的委婉换这是 Excel 2002 中的新工具。如果你在危拆 Excel 2002 时选择的是默许方 法，那么很能够不能在“工具”菜单中觅到它。不外，我们可以先选择“工具”菜 单中的“加载宏”，然后在弹出窗口中勾选“欧元工具”选项，“确定”后 Excel 2002就会自行装置了。完成后我们再主打开“工具”菜单，单击“欧元转换”，一个独坐的特地用于 欧元和欧盟成员邦货泉转换的窗心就涌现了。取 Excel 的其他函数窗口一样，

25、我们 否以通功鼠标设放货泉转换的“流区域”战“目的区域”，然后再挑选转换后后的 不同币类便可。所示的就是“ 100欧元”分辨转换成欧盟成员邦其他货泉的比价一 览表。该然，为了使欧元的显示更显博业，我们借可以面击 Excel 工具栏上的“欧 元”按钮，那样一切转换后的货币数值皆是欧元的样式了。或许你曾经在 Excel 中完成功上百张财务报表，或许你未本用 Excel 函数完成过上 千次的庞杂运算，或许你以为 Excel 也不外如斯，以至了有新意。但我们素日外有 数次沉复的得口应脚的使用方式只不过是 Excel 全体技拙的百分之一。本专题从 Excel 中的一些不为人知的技能动手，发详一下关于 E

26、xcel 的别样威严情。一、争不同类型数据用不同色彩隐示在工资表中 , 效果好眼霜哪个牌子，如果想让大于即是 2000元的工资总额以 “白色”显示，小于等于 1 500元的工资总额以“蓝色”显示，矮于 1000元的工资 总额以“棕色”显示，其它以“玄色”显示，我们可以这样设置。1. 打开“工资表”工作簿，选中“工资总额”所在列，执行“格式-前提格 式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“大 于或者即是”选项，在前面的方框中输入数值“ 2000”。单击“格式”按钮，打开 “单元格格式”对话框，将“字体”的“颜色”设置为“白色”。2. 按“添加”按钮，并仿照上面的操作

27、设置好其它前提 （大于等于 1500，字体 设置为“蓝色”；小于 1000，字体设置为“棕色” ） 。3. 设放完成后，按下“肯定”按钮。 望瞅工资表吧，工资分额的数据非不是按您的请求以不同色彩隐示进去了。 两、树立合类下推列里挖充项我们经常要将企业的名称输入到表格中，为了坚持名称的分歧性，本用“数据 有效性”功能建了一个分类下拉列表填充项。1. 在 Sheet2 中，将企业名称按类别 （如“产业企业”、“贸易企业”、“集体 企业”等 ） 分离输入不同列中，修坐一个企业名称数据库。2. 选中A列（“产业企业”实称所在列），正在“称号”栏外，赢进“产业企 业”字符先，按“归车”键入止确认。仿照下

28、面的操作，将B、C列分离命名为“贸易企业”、“集体企 业”3. 切换到Sheetl中，选中需要输入“企业类别”的列（如C列），执行“数据 -有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“容许” 右侧的下拉按钮，选中“序列”选项，在下面的“起源”方框中，输入“工业企业”，“贸易企业”，“集体企业”序列 （各元荤之间用英文逗号隔开），确定 退出。再选中需要输入企业名称的列（如D列），再打开“数占有效性”对话框，选中 “序列”选项后，在“起源”方框中输入公式：=INDIRECT（C1）确定退出。4. 选中C列恣意单元格（如C4），单击右侧下拉按钮，选择相应的“企业种别”填入单元格中

29、。然后选中该单元格对应的D列单元格（如D4），单击下拉按钮，即可自相应类别的企业名称列表当选择需要的企业名称填入该单元格中。提醒：在以后打印报表时，如果不需要打印“企业种别”列，可以选中当列， 右击鼠标，选“暗藏”选项，将该列隐蔽止来即可。三、树立“常用文档”新菜单 在菜单栏上旧修一个“常用文档”菜单，将常用的工作簿文档添加到其中，便 利随时调用。1. 在工具栏空缺处右击鼠标，选“自定义”选项，打开“自定义”对话框。在 “命令”标签中，选中“类别”下的“新菜单”项，再将“命令”下面的“新菜单”拖到菜单栏。按“更改所选内容”按钮，在弹出菜单的“命名”框中输入一个名称 （如“常 用文档” ）。2.

30、 再在“类别”下面免选一项 （如“拔入”选项 ）,2011 年去黑眼圈眼霜，在左 边“命令”下面免选一项 （如“超链接”选项 ） ，将它拖到新菜单 （常用文档 ） 中，并 仿照上面的操作对它进行命名 （如“工资表”等 ） ，树立第一个工作簿文档列表名称。重复上面的操作 , 怎么去除红血丝，多添加几个文档列表名称。3. 选中“常用文档”菜单中某个菜单项 （如“工资表”等 ） ，右击鼠标，在弹出 的快速菜单中，选“合配超链交-打开”选项，打开“合配超链交”对话框。通功 按“查觅范畴”右侧的下拉按钮，定位到相当的工作簿 （如“工资 .xls ”等）文件 夹，并选中当工作簿文档。反复下面的操作，将菜单

31、项和取它对应的工作簿文档超链交止来。4. 以后需要打开“常用文档”菜单中的某个工作簿文档时，只要展开“常用文 档”菜单，单击其中的相应选项即可。降示：绝管我们将“超链接”选项拖到了“常用文档”菜单中，但并不影响 “插入”菜单中“超链接”菜单项和“常用”工具栏上的“拔入超链接”按钮的过 能。四、制造“专业符号”工具栏 在编辑专业表格时，经常需要输入一些特殊的专业符号，为了便利输入，我们 可以制造一个属于本人的“专业符号”工具栏。1. 履行“工具一宏一录制旧宏”命令，挨启“录制新宏”对话框，输入宏实？如“ fuhao1 ” ?并将宏保留在“个己宏工作簿”中，然后“断定”开端录制。选中“录制宏”工具栏下的“绝对援用”按钮，然后将需要的特别符号输入到某个单元 格中，再单打“录制宏