EXCEL应用技巧2.docx

1、EXCEL应用技巧2 1楼注：方法是好，可以省略辅助列，但测试不行。文本输入时，避免在字符串前后无意输入多余的空格，假如在 B 列输入，选中 B 列，菜单中，数据有效性设置自定义公式，输入公式：=B1=TRIM(B1)，确定。 2楼注：比较不错，但只以对第一个数据有效。相当于快速复制。同时选中需要复制的单元格和该单元格同列的下面多个连续或不连续的空白单元格，然后按下“Ctrl+D”组合键，即可将需要复制单元格的内容复制到下面选中的多个连续或不连续的单元格中。3楼注：前3位为妥，若2位，判断等差、等比？且左右列不能为空， 快速填充。先输入编号系列值的前两位，确定步长，然后选中该两单元格，双击小黑

2、“十”，即可自动编号至右边为空白单元格为止 4楼1、快速删除单元格/行/列 删除单元格/行/列：选中单元格，ctrl+“-”（减号） 插入单元格/行/列：选中单元格，ctrl+shift+“+”（加号）注：未必会快2、通过一次按键创建一个excel图表 A、选择要绘制的数据 B、按F11（或ALT+F1） C、选定整个表，点右键-位置-“作为其中的对象插入sheet”3、实现平方、立方上标： 选定单元格： 平方：ALT+178 立方：ALT+179注：在单元格中须在编辑状态使一列数据内容输入不重复公式是“=COUNTIF(A:A,A1)=1” 注：其实公式很多5楼注：有用。取工作表名及路径=C

3、ELL(filename,$A$1)取工作簿及表名=MID(CELL(filename,$A$1),FIND(,CELL(filename,$A$1),LEN(CELL(filename,$A$1)+1-FIND(,CELL(filename,$A$1)取工作簿名=MID(CELL(filename,$A$1),FIND(,CELL(filename,$A$1),FIND(,CELL(filename,$A$1)-FIND(,CELL(filename,$A$1)+1)取工作表名=MID(CELL(filename,$A$1),FIND(,CELL(filename,$A$1)+1,LEN(C

4、ELL(filename,$A$1)-FIND(,CELL(filename,$A$1)6楼注：有用。任意多表条件汇总=SUM(SUMIF(INDIRECT(&sheet1,sheet2,.sheetn&!range),criteria,INDIRECT(&sheet1,sheet2,.sheetn&!sum_range)7楼任意多表条件汇总=SUM(SUMIF(INDIRECT(&sheet1,sheet2,.sheetn&!range),criteria,INDIRECT(&sheet1,sheet2,.sheetn&!sum_range)一是：如何让你这一公式中的sum_range随单元

5、格的复制而随列改变！二是：&sheet1,sheet2,.sheetn&我可改为&sheetrow(a1:an)&,而这个n我想通过另外一个单元格中的数字来控制，应如何实现？请指教！8楼半榻茶烟 回复to 风中飘雪：sheet1,sheet2,.sheetn如果是单元格引用，问题就更简单，比如1!-n!若按数字顺序排列，直接用row(a1:an)代替即可。你可参看下面帖子： 12楼 宏与vba我感觉这个问题很有代表性,所以就Copy过来了smailsuper 提问：EXCEL里的宏和VBA是不是一回事？严格的说,应该不是一回事.宏概念的范围很广,在很多地方都能看到.编程语言中就有此概念,比如C

6、,汇编.在一些软件中也可以看到,比如Excel,IBM Aix中的Vi编辑器。宏,我的理解就是一组命令(动作)的一个抽象。通过这个宏可以重复而连续做一些动作。在Excel中,你可以录制或编辑宏。而在Excel中,Vba的概念要比宏范围广得多,它可以是一个录制或编制的宏,也可以是其它的一些操作,比如显示一个窗体.创建一个类.而Vba本身的含义就是Visual Basic For Application,顾名思义就是面向应用的Visual Basic.比如:Excel中的Vba就是面向Excel的(针对Excel的),往往会对其做在的应用提供更加强大灵活的功能.对于常见的Vba应用有Office系

7、列组件,AutoCAD,ArcInfo(Gis软件中的一个老大)等等.在Office中常见的缩写还有VBE,即Visual Basice Editor，其实相当于一个VB IDE环境,在这里可以进行Vba可视化开发. 第 11 楼 可以在编辑栏的名称框中直接输入单元格引用就可以快速选择单元格，特别是在数据很多的情况下. 第 12 楼 excel查找替换中的 * 和 ? 是通配符,分别可以替代多个字符和单个字符,但要查找替换*和?自身怎么实现呢?有一个绝窍:在*和?前加 即可使它们失去通配符的作用,也就可以查找替换它自身了 第 13 楼 在Excel中，要在一个有很多个工作表的工作簿中快速的选择

8、某一个工作表，只需将鼠标指针移到工作薄左下角的工作表滚动轴(Sheet Tabs)，然后点击右键，在快捷菜单中选择你想要的工作表。 第 14 楼 注：是一种方法，但未必方便，且需记住方法如果您想要将一块复制的单元格插入到其它行或列之间-而不是覆盖这些行或列-以下就是完成这一操作的简单方法： 选择您将要复制的单元格单击编辑菜单上的复制在工作表上选择将要放置被复制单元格的区域按下Ctrl + SHIFT + +在插入对话框中单击周围单元格的转换方向，然后单击确定。 现在，复制的单元格将插入到合适的位置，您无需担心它们覆盖现有的信息。 第 15 楼 想要在多个单元格输入同一数据，先选中所需填写的单元

9、格，然后用键盘输入，最后用Ctrl+Enter即可 第 16 楼 当你想复制公式到整列（至文件尾）可选中有公式的单元格，待出现黑色填充柄时，双击即可，只要相邻列没有空格，多少条记录也会一下子到底，很快噢！注：好，但有条件，相邻单元格不能为空。 第 17 楼 复制公式后，Shift + Ctrl + ，Shfit + Insert。一样干净利落。 第 18 楼 拖动单元格到某位置(不覆盖目标区域) 想要移动某一个或几个单元格到表中某一位置，选中它们，按Shift键同时拖到要去的位置，待黑色工字出现，放下即可，绝不会复盖周围的记录，它会挤进去，很方便噢！ 第 19 楼 把函数写在自动校正里头保存，

10、不过使用定义名称似乎可以达到同样目的。请老夏和其它各位高手比较一下此举与定义名称的优劣。 第 20 楼 多表进行合并，大多数回复都是讲用VBA来实现，而实际上，部分用户可能因为功力的问题而实现不了，这里我贴一个不用VBA的方法，只用Excel的数据透视表即可实现：问题来源：SHEET1，SHEET2，A列都是代码，B列都是金额，我想做SHEET3，结构与前表相同，如果前两张表的代码相同，则代码相同，金额相加；若某个代码只出现在1或2表，则金额和代码都照抄。实现方式：数据-数据透视表-多重合并计算-单个字段-在选定区域里将多个表的相应区域加入-新建工作表-完成后如果汇总项为计数，适当改一改即可。

11、类似的数据利用合并计算功能可以更简单的实现! 第 21 楼 F4 键在输入公式时的功用：当在某一单元格中输入或编辑公式过程中，按 F4 键可以切换引用地址的方式。比如：在 A1 中输入 “B2”时（注意不要按 Enter），按动 F4 键，可以使 B2 依次变换成“$B$2、B$2、$B2、B2”。 第 22 楼 如果用针式打印机和压感纸打印表格的话，可以选择边框样式为“无”下面的那一个虚线样式，这样既省针头又让表格看起来突出数据。 第 25 楼 输入分数：0空格1/2。 第 26 楼 我倒有个办法，使用公式循环引用=trim(b1),在选项里把迭代次数改为一次就好了 第 27 楼 在单元格内

12、显示多行文本 在一个单元格中，若要在特定的位置开始一个新的文本行，请将光标定位（单击鼠标左键）在希望断行的位置，并按 Alt+Enter。 第 28 楼 快速查看EXCEL工作表 一个 EXCEL 工作簿也许会包含数十张工作表，每次要查看都要一直按标签滚动按钮箭头一个一个去找，很累人！其实，用鼠标右键点击标签滚动按钮就会列出所有工作表的快捷菜单，从快捷菜单上选择所需工作表，就可以激活工作表了！ 第 29 楼 巧用快捷键激活已经打开的 EXCEl 的各工作簿 用快捷键 CtrlF6 可以方便、快速的依此激活已经打开的EXCEl的各工作簿。 第 30 楼 第 31 楼 注：相当于工具摄影的功能，都

13、是图片，但摄影后的图片中内容有链接能更新。将单元格复制成图片：选择区域 按住SHIFT不放，点击编辑/复制图片在弹出的对话框里选择 确定粘贴图片：按住SHIFT不放，点击编辑/粘贴图片 第 32 楼 在excel编辑页面状态下，按着ctrl并滑动鼠标中轮，可快速改变表格的行高度。 第 33 楼 用CTRL+TAB键可以快速地在几个打开的工作簿之间切换 第 34 楼 SHIFT+F2,在选中单元格插入批注. 第 35 楼 =TEXT(TODAY(),ddd) Thu=TEXT(TODAY(),dddd) Thursday=TEXT(TODAY(),aaa) 四=TEXT(TODAY(),aaaa

14、) 星期四 第 36 楼 输入计算公式后按F9可将公式转成数值。注：按CTRL+公式与数值间的转换 第 37 楼 选一单元格F8键定位再选其它单元格名称方块定义Range名称 第 38 楼 用OFFICE XP的,在EXCEL的表标签sheet 1等上点右键，可以选择标签颜色，以便快速区分。 第 39 楼 当EXCLE工作表有不止一个SHEET需打印，可按住CTRL+需打印的SHEET？，被选中的SHEET会闪亮，按打印即可打印所需的SHEET，而不需到每个SHEET按打印键。 第 40 楼 第 42 楼 每次输入公式时，EXCEL默认的格式需要以等号，加号或减号开头，但是如果在工具-选项-1

15、-2-3的帮助-选择LOTUS1-2-3的公式后，则不用先输入等号，加号或减号，EXCEL会自动计算出结果。例如：未转换前，输入公式的格式为：=2+5*3转换后，输入公式的格式为：2+5*3 第 43 楼 Excel就象Word一样，可以直接修改字号来加大字体，原最大为72，可改到409磅。 第 44 楼 我常用到一些数计算，如果只是将显示的小数位确定为两位的话，合计数值有可能就会有点小误差了（原来我想就不用解释了），这在财务上是不行的，当然用ROUND函数是很简单，但我一直都是用工具选项重新计算里面的以显示值为准。注：比公式方便。 第 45 楼 用分列可解决一整列的格式设置问题 第 46 楼

16、 如果在sheet1中制作了一个表头，在sheet2中也想用的话。先选中表头选复制，然后在sheet2中选粘贴，然后选择“保留原列宽”，就可以了。好象只有OFFICEXP可以用了 第 47 楼 标签颜色是在XP里面用！ 第 49 楼 超快速复制公式：要复制大量公式如几千上万行，选择第二行窗口冻结，将最后一行提至第二行，将公式拖至第二行。注：好方法之一，另一办法：在名称框中确定范围，第一格输入公式，按ctrl+enter ,另外还有办法。 第 50 楼 ALT+；选定可见单元格 第 51 楼 超快速复制公式：实际上，只要公式单元格的边上有数据，你选中有公式的单元格，双击“”后，公式就自动复制到空

17、白单元格为止。注：前面已讲到，但条件是粘贴单元格边上的单元格必须有数据。 第 55 楼 输入自动求和公式时，不用鼠标点按工具栏上的按钮，只需按“Alt+=”即可。 第 57 楼 按住ctr+end去到本工作表的最尾端，很有用！用记录单好过用筛选！想请问各位高手，如何将“分类汇总”的结果一次性、直接复制到另外一张工作表中？ 第 60 楼 在一行中填写数据时，经常要快速移动光标到行首或行尾，可以用HOME、CTRL+- 第 62 楼 1、选中含有公式的某个单元格，然后在编辑栏中选中整个公式，按下功能键，就可以将公式转换为返回的值。2、选中需要复制和下面需要粘贴的多个单元格，按下Ctrl+D组合键，

18、就可以将需要复制的内容粘贴到下面多个单元格中。 第 63 楼 如果你要在引用中使用单字节双引号(),比如你要实现A1的显示为(I Love You!),则在A1中输入=&I Love You!&,即4个双引号在一起可以显示一个双引号.因为我经常用Excel来写VBA语句,特别是从Access中读出来的SQL语句,这一招还挺有用！ 第 66 楼 =I Love You更清楚 第 67 楼 在原数基础将其变为万元，只需在一格中输入1000，复制，再选定转换范围，右击“选择性粘贴”-数值（复选 “除”）即可！ 第 70 楼 假设某单元格为一常数，其余相邻几个单元格都要与此单元格相乘（除）。可以先点击

19、常数单元格，然后按F4键，再乘最上一个相邻单元格，然后拖动，每个乘数就算出来了。 第 71 楼 格式单元格数字自定义：“;”，可以隐藏该单元格（区域）所有内容 第 72 楼 ;就可以 注：使用;可以使数子,中文,英文一起隐藏,而使用;只能隐藏数字 第 73 楼 按Ctrl +上下左右箭头可快速移动到数据清单的顶端、底部、最左边及最右边。按Ctrl + Shift + 上下左右箭头可快速选定数据清单的各个部份。 第 74 楼 要使G：G在向右复制的过程中保持不变，只要用$G:$G就行了，只加半边。当是不选整个G列时，它可保证向下复制它会自动改行，而向左右复制时列却不变。也许和你们想要的搭不上边。

20、 第 75 楼 输入计算公式后按F9可将公式转成数值 第 76 楼 如何改变EXCEL文档中SHEET的字体及字号呢 第 77 楼 剔除表里所有空格的方法：用查找取代，查找里按一下空格键，取代里什么也不输，然后点击全部取代。 第 79 楼 自动筛选时会有行自动隐藏，你只要选中区域，在右下的状态栏中，它便会求和，且只求没隐藏的行的和。也适用于手动隐藏行。 第 80 楼 ctrl+“*” = Activecell.CurrentRegion.Select注：选定相邻单元格，只要有数据，不局限于周边8个。 第 84 楼 选定有公式的单元格的区域，按右键/复制，鼠标不要离开选定的区域，按右键/选择性粘

21、贴/数值。是不是单元格的公式没有了。 第 85 楼 快速输入当天日期:Ctrl+:可在单元格中输入系统当前日期. 第 86 楼 一个网友教我的：用fineprint软件可以大大节约打印纸！ 第 88 楼 输入类似“011352”数时，可这样输入，011352 即可， 第 89 楼 在输入数值时,为防止输入的数值超过要求的范围,我们可以用以下方法提示：数据-有效性 -设置中设置数据的范围, 在出错警告中,可以设置当输入数据错误时的提示信息. 第 91 楼 行列转置选中需要转置的区域，复制，选择性粘贴，转置，行列即可转置。 第 92 楼 按了保存按钮之后，撤消功能就会无效，当你打开多个工作簿时，其

22、它工作簿的撤消功能也无效。 第 93 楼 快速选定区域按F5键，在“定位”对话框的“引用位置”栏中输入需选中区域，如“a1:w1000”，按“确定”即可。 第 94 楼 如果你用的是带滚轮的鼠标，那么先按住“Ctrl”键，再拨动滚轮吧！可以快速改变显示比例。 第 105 楼 显示要复制的行（如第二层是合计数，点2），按Ctrl+G,点“定位条件”，再选“可见单元格”，点“确定”，再复制，选择性粘贴值即可。 第 108 楼 选中要向右填充的单元格，按住shift,选中要向右填充的目标单元格，松开shift，再按ctrl+R,可将此单元格的值向右填充。 110 楼 在会计工作中，我们常常需要将一些

23、合计金额大写，下面是我的一点心得，请多多指教。一、由于在EXCEL中，IF只能嵌套七层，所以我们先行将合计用公式四舍五入：=ROUND(SUM(N8:N47),2)二、在要显示大写人民币的单元格里（假定是N48）输入以下公式：IF(INT($N$48*10)-INT($N$48)*10)=0,TEXT(INT($N$48),DBNum2G/通用格式)&元&IF(INT($N$48*100)-INT($N$48)*10)*10)=0,整,零&TEXT(INT($N$48*100)-INT($N$48*10)*10,DBNum2G/通用格式)&分),TEXT(INT($N$48),DBNum2G/

24、通用格式)&元&IF(INT($N$48*100)-INT($N$48)*10)*10)=0,TEXT(INT($N$48*10)-INT($N$48)*10),DBNum2G/通用格式)&角整,TEXT(INT($N$48*10)-INT($N$48)*10),DBNum2G/通用格式)&角&TEXT(INT($N$48*100)-INT($N$48*10)*10,DBNum2G/通用格式)&分) 第 111 楼 选取部分文本或数值我们有时要将WORD文件中的数据导入EXCEL中，比如要在G2即“LB027S345.00“中取出“LB027S”，可以用下面的公式取出：TRIM(LEFT(G2

25、,LEN(G2)-(MATCH( ,MID(G2,LEN(G2)-ROW(INDIRECT(1:&LEN(G2),1),0)说明：ROW(INDIRECT(1:&LEN(G2)=(1:2:3:4:5:6:7:8:9:10:11:12:13)=AAALEN(G2)-AAA=(12:11:10:9:8:7:6:5:4:3:2:1:0)=BBBMID(G2,BBB,1)=(0,.,5,4,3,”(空格),S,7,2,0,B,L,#NAME)=CCC MATCH( ,CCC,0)=6=DDDLEN(G2)-DDD)=7=EEELEFT(G2,EEE)=“LB027S”(含空格)=FFFTRIM(FFF

26、)=LB027S 第 114 楼 自己定义很多名称，有时连自己也忘了当时取什么名字，要插入其中一个时，可按f3选择一个贴上! 第 118 楼 Ctrl+也可以实现Ctrl+D的功能。 第 121 楼 在EXCEL中要想打天多个工作薄：单击“工具”单击“选项”然后选择“任务栏上的窗口”确定既可。 第 125 楼 在自动填充时不使数据递增的办法：按住ctrl键的同时拖动填充柄，这样可使自动填充变为复制了。 第 126 楼 用模板向导来处理每次重复使用一个表格，又要将其中某些单元格的内容存入数据库的例子非常有用，特别是每宗订单之类等表格，此主题相关图片如下：其中的”范本精灵“即简体的模板向导，如要使

27、用，要先在工具-加载宏中选中模板向导，才可使用。 第 130 楼 建立多张工作表通过单击“插入/工作表”，或在工作表底部右击鼠标在弹出菜单中进行选择，均可以插入一张新的工作表，但这只能应付少量的需求，一次操作只能建立一张工作表，如果在一个文件里需要上十张的工作表，可以单击“工具/选项”，选择“常规”，在“新工作簿内的工具表数”一项即可进行调节。 第 131 楼 按ALT+向下键，即可得到本列中原有文本的列表。 第 132 楼 性别=IF(or(and(len(q1)=15,mod(RIGHT(q1,1),2)=1),(and(len(q1)=18,mod(int(RIGHT(q1,2)/10)

28、,2)=1),男,女)出生日期=IF(LEN(q1)=15,date(mid(q1,7,2),mid(q1,9,2),mid(q1,11,2),date(mid(q1,7,4),mid(q1,11,2),mid(q1,13,2)年龄DATEDIF(a1,TODAY(),y)（注：q1为身份证号码，a1为出生日期） 第 133 楼 完整的中文大写转换公式，最完善的。=IF(A10,无效数值,IF(A1=0,IF(A10),零&TEXT(INT(A1*10)-INT(A1)*10,dbnum2)&角,TEXT(INT(A1*10)-INT(A1)*10,dbnum2)&角)&IF(INT(A1*1

29、00)-INT(A1*10)*10)=0,整,TEXT(INT(A1*100)-INT(A1*10)*10,dbnum2)&分)如：120.56，显示为“壹佰贰拾元零伍角陆分”而不是“壹佰贰拾元伍角陆分”。 第 135 楼 用特定函数实现快速输入在工作中，无论是进行文字处理或是制作报表，都可能要输入大量重复的数据。利用Word的“自动更正”命令(“工具”菜单)可以巧妙地实现数据的快速输入，但是在Excel中却没有类似的菜单命令。但它自带的VLOOKUP函数可巧妙地解决这个问题。 比如处理产品的销售数据时， 往往要输入大量同一单位的名称，使用VLOOKUP函数可以使我们只需键入一个字母就可实现单位名称的快速输入。1.创建源工作表进入Excel 7， 单击“文件”菜单，再单击“新建”命令，创建一个新工作簿。在工作表Sheet1上建立产品