《必备的EXCEL技巧问答.doc》由会员分享，可在线阅读，更多相关《必备的EXCEL技巧问答.doc（9页珍藏版）》请在三一文库上搜索。
1、必备的EXCEL技巧问答摸索出了一套自己的“合并与拆分”理论，供同行们参考。所谓合并数据是指将两列中的数据合并到一列中，拆分数据是指将一列中的数据拆分到两列中，图1为“毕业院校”和“专业”两列的拆分及合并的效果图。合并数据要将“毕业院校”和“专业”两列中数据合并为“毕业院校及专业”，有“真”、“假”两种合并方法，所谓真合并就是将两列数据真正合并到一列中，而假合并表面上看是合并为一列了，实际数据还是存在于两列中，具体操作跟我一起来看看吧。1、真合并如果将一列中的数据通过再输入合并到另一列中，那不是我们希望看到的解决办法，快捷而准确的操作方法是：(1)将需要合并的“毕业院校”和“专业”两列数据通过
2、“剪切”、“粘贴”移动到一个新工作表中，如命名为“合并”工作表。(2)在“合并”工作表中将XLS文件另存为TXT文件，单击“文件另存为”，保存类型选择“文本文件(制表符分隔)”，单击保存按钮后根据提示连续单击两次确定按钮即可。(3)在Execl中单击打开按钮，重新打开TXT文件，在“文本导入向导步骤之1”中采用默认设置，单击下一步按钮，在“步骤之2”分隔符号中，去掉“Tab键”前复选框的选择，单击完成按钮，这时两列中数据合并到一列中。(4)将合并后的数据再通过“剪切”、“粘贴”操作移动到原工作表的原位置处，合并操作完成。提示：合并后的数据在原来两列数据(如毕业院校和专业)之间会有一个空格，而有
3、时根据实际需要去掉或加宽空格，可采取如下措施：方法一：对TXT文件进行编辑XLS文件中列与列之间数据转换为TXT文件后是以制表符分隔的，此时合并后的数据在原来两列数据之间会有一个空格，改变TXT文件中两列之间距离(即改变空格的大小)，对应的XLS文件中原两列数据之间的距离也会随之发生改变，若TXT文件中两列数据之间距离增大或减小为零，则合并的XLS文件中两列数据之间距离也会随之增大或减小为零。如果你要将两列数据无缝合并，建议应用下面两种方法。方法二：利用函数ConcateNameConcateName函数可以实现将几个文本字符串合并为一个文本字符串，语法为：ConcateName (text1
4、,text2, )，text1, text2, 为1到30个将要合并成单个文本项的文本项，这些文本项可以为文本字符串、数字或对单个单元格的引用，如图2所示。图2方法三：利用“&”运算符利用“&”运算符也可将几个文本字符串合并为一个文本字符串，“&”运算方式同“+”、“-”等运算方式一样，如图2所示。2、假合并我们也来一个“瞒天过海”吧，呵呵，在对单元格设置边框时，利用工具栏中的“外部框线”将两个单元格框在一起，这样两个单元格中数据就在一个表格中了，打印出来后保证不留一点“作假”痕迹。此招对付较少单元格效果不错，要应付大量的单元格，奉劝你还是来点“真”的吧！否则够你忙的了。拆分数据合并方法掌握了
5、，拆分也不难，拆分的方法与前面介绍的合并方法相似，先将XLS文件另存为TXT文件，再对TXT文件进行编辑，将需要拆分的数据用“Tab”键或空格分开，然后再用Excel重新打开编辑后的TXT文件，这样原来在一起的数据就被拆分开了。处理含有大量信息的表格时，以上合并与拆分的方法很有效，有兴趣的朋友可以试一试。从身份证号码中提取性别Q：A1单元格中是15位的身份证号码，要在B1中显示性别(这里忽略15位和18位身份证号码的判别）B1=if(mod(right(A1,1),2)0,male,female)请问这个公式有无问题，我试过没发现问题。但在某个网站看到作者所用的是如下公式：B1=if(mid(
6、A1,15,1)/2=trunc(mid(A1,15,1)/2）,female,male)A：leaf道理都是一样的，不过你的公式比那个公式优质提取性别（无论是15位还是18位）=IF(LEN(A1)=15,IF(MOD(MID(A1,15,1),2)=1,男,女),IF(MOD(MID(A1,17,1),2)=1,男,女如果身份证号的输入已是15或18位，用公式=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1),2),男,女xls-exe可以么？A：Kevin如果只是简单的转换成EXE，当然可以。如果你指的是脱离Excel也可以运行，好像没听说过可以。当然，通过DDE
7、，是可以不运行Excel但调用它的所有功能的，但前提仍然是你的计算机上已经安装了Excel列的跳跃求和Q:若有20列（只有一行），需没间隔3列求和，该公式如何做？前面行跳跃求和的公式不管用。A:roof假设a1至t1为数据（共有20列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20),3)=0,(a1:t1)按ctrlshiftenter结束即可求出每隔三行之和。跳行设置：如有12行，需每隔3行求和=SUM(IF(MOD(ROW(1:12),3)=0,(A1:A12)能否象打支票软件那样输入一串数字它自动给拆分成单个数字？Q：如我输入123456.52它
8、自动给拆成￥1 2 3 4 5 6 5 2 的形式并且随我输入的长度改变而改变？A：Chiu我所知函数不多，我是这样做的，如有更方便的方法，请指点例如：在A1输入小写金额，则：千万：B1=IF(A1=10000000,MID(RIGHTB(A1*100,10),1,1),IF(A1=1000000,￥,0)百万：C1=IF(A1=1000000,MID(RIGHTB(A1*100,9),1,1),IF(A1=100000,￥,0)十万：D1=IF(A1=100000,MID(RIGHTB(A1*100,8),1,1),IF(A1=10000,￥,0)万：E1=IF(A1=10000,MID(R
9、IGHTB(A1*100,7),1,1),IF(A1=1000,￥,0)千：F1=IF(A1=1000,MID(RIGHTB(A1*100,6),1,1),IF(A1=100,￥,0)百：G1=IF(A1=100,MID(RIGHTB(A1*100,5),1,1),IF(A1=10,￥,0)十：H1=IF(A1=10,MID(RIGHTB(A1*100,4),1,1),IF(A1=1,￥,0)元：I1=IF(A1=1,MID(RIGHTB(A1*100,3),1,1),IF(A1=0.1,￥,0)角：J1=IF(A1=0.1,MID(RIGHTB(A1*100,2),1,1),IF(A1=0.
10、01,￥,0)分：K1=IF(A1=0.01,RIGHTB(A1*100,1),0)网客公式中最后一个0改为如何编这个公式Q：我想编的公式是： a/84 - （b4）其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。这个公式的关键是要统计出包含字符C的单元格的个数，可我不会。A：dongmu=a/(84-countif(b,=c)*4)chwd我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c”在D2-D30中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式如何做？再一次感谢！受dongmu朋友公式的启发，我做出了需要的公
11、式=a/(84-COUNTIF(D3:D30,c)*4)skysea575 :其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。包含字符C在这里的意思不清楚。你的公式中只可以计算仅含有“C”字符的单元格数。可能你的想法是计算字符中凡是含有这个字或字母的词。如“文章”和“文字”中都有一个“文”字，是否计算在内？将文件保存为以某一单元格中的值为文件名的宏怎么写A：lxxiu假设你要以Sheet1的A1单元格中的值为文件名保存，则应用命令：ActiveWorkbook.SaveCopyAs Str(Range(Sheet1!A1) + .xlsEXCEL中求两陈列的对应元
12、素乘积之和Q:即有简结一点的公式求如:a1*b1+a2*b2+b3*b3.的和.应有一函数XXXX(A1:A3,B1:B3)或XXXX(A1:B3)A:roof在B4中输入公式=SUM(A1:A3*B1:B3),按CTRL+SHIFT+ENTER结束.dongmu=SUMPRODUCT(A1:A10,B1:B10)求助日期转换星期的问题Q:工作中须将表格中大量的日期同时转换为中英文的星期几请问如何处理英文的星期转换，谢谢！A:Rowen1.用公式:=text(weekday(xx),ddd)2.用VBA,weekday(),然后自定义转换序列3.用拼写检查,自定义一级转换序列4.dongmu转
13、成英文： =TEXT(WEEKDAY(A1),dddd)转成中文： =TEXT(WEEKDAY(A1),aaaa)研究彩票，从统计入手Q：我有一个VBA编程的问题向你请教。麻烦你帮助编一个。我一定厚谢。有一个数组列在EXCEL中如： 01 02 03 04 05 06 07和01 04 12 19 25 26 3202 08 15 16 18 24 2801 02 07 09 12 15 2209 15 17 20 22 29 32比较，如果有相同的数就在第八位记一个数。如01 04 12 19 25 26 32 202 08 15 16 18 24 28 101 02 07 09 12 15
14、 22 209 15 17 20 22 29 32 0这个数列有几千组，只要求比较出有几位相同就行。我们主要研究彩票，从统计入手。如果你有兴趣我会告诉你最好的方法。急盼。A：roof把“01 02 03 04 05 06 07 ”放在表格的第一行，“01 04 12 19 25 26 32 2”放第二行。把以下公式贴到第二行第八个单元格“A9”中，按F2,再按CTRL+SHIFT+ENTER.=COUNT(MATCH(A2:G2,$A$1:$G$1,0)如何自动设置页尾线条？Q：各位大虾：菜鸟DD有一难题请教， 我的工作表通常都很长，偏偏我这人以特爱美，所以会将表格的外框线和框内线条设置为不同
15、格式，但在打印时却无法将每一页的底部外框线自动设为和其他三条边线一致，每次都必须手工设置（那可是几十页哦！），而且如果换一台打印机的话就会前功尽弃，不知哪位大侠可指教一两招，好让DD我终生受用，不胜感激！A：roof打印文件前试试运行以下的代码。打印后关闭文件时不要存盘，否则下次要把格式改回来就痛苦了。（当然你也可以另写代码来恢复原来的格式）：Sub detectbreak()mycolumn = Range(A1).CurrentRegion.Columns.CountSet myrange = Range(A1).CurrentRegionFor Each mycell In myrang
16、eSet myrow = mycell.EntireRowIf myrow.PageBreak = xlNone ThenGoTo NexElseSet arow = Range(Cells(myrow.Offset(-1).Row, 1), Cells(myrow.Offset(-1).Row,mycolumn)With arow.Borders(xlEdgeBottom).LineStyle = xlDouble 把这一行改成自己喜欢的表线.Weight = xlThick.ColorIndex = xlAutomaticEnd WithEnd IfNex: Next mycellEnd
17、Sub求工齡A：老夏=DATEDIF(B2,TODAY(),y)=DATEDIF(B2,TODAY(),ym)=DATEDIF(B2,TODAY(),md)=DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2,TODAY(),md)&日*DATEDIF() Excel 2000 可以找到說明 Excel 97 有說明 是,?暗惡?如何用excel求解联立方程：Q：x-x(7/y)z=68 x-x(20/y)z=61 x-x(30/y)z=38到底有人会吗？不要只写四个字，规划求解，我想要具体的解法，A：wenou这是一个指数函
18、数的联列方程。步骤如下1、令X/Y=W 则有 X-(7W)z=68 X-(20W)Z=61 X-(30W)Z=382、消去X(20Z-7Z)WZ=7 (30Z-20Z)WZ=233、消去W(30Z-20Z)/(20Z-7Z)=23/7由此求得Z=3.542899 x=68.173955 y=781.81960行高和列宽单位是什么? 如何换算到毫米?A：markxg在帮助中：“出现在“标准列宽”框中的数字是单元格中 0-9 号标准字体的平均数。”单位应该不是毫米，可能和不同电脑的字体有关吧。Q：Rowen是这样:行高/3=mm 列宽*2.97=mm鱼之乐实际上最终打印结果是以点阵为单位的,而且e
19、xcel中还随着打印比例的变化而变化如果想用宏写一个完全退出EXCEL的函数是什么？Q:因为我想在关闭lock.frm窗口时就自动退出EXCEL，请问用宏写一个完全退出EXCEL的函数是什么？多谢！A:Application.quit请问如何编写加载宏？把带有VBA工程的工作簿保存为XLA文件即可成为加载宏。请问如何在点击一个复选框后在后面的一个单元格内自动显示当前日期？如果是单元格用=TODAY()就可以了如果是文本框在默认属性中设置或在复选框的CLICK中设置文本框的内容EXCEL2000中视面管理器如何具体运用呀？请问高手EXCEL2000中视面管理器如何具体运用呀？最好有例子和详细说明
20、。明确的功能。不然我还是不能深刻的理解他。markxg其实很简单呀，你把它想象成运动场上的一串照片（记录不同时点的场景），一张照片记录一个场景，选择一张照片就把运动“拖”到照片上的时点。不同的是只是场景回复，而值和格式不回复。用VBA在自定义菜单中如何仿EXCEL的菜单做白色横线？Q：我在做自定义菜单时，欲仿EXCEL菜单用横线分隔各菜单项目，用VBA如何才能做到？A：Rowen那个东东也是一个部件,我想可以调用,不过没试过.diyee把它的显示内容中设置为-即可。simen1.此部件叫什么名字，在控件箱里有吗？2.用“-”我也试过，用它时单击可以，但你要知道EXCEL自己的横线是不可以单击下
21、去的kevin_168object.BeginGroup = True下面是我用到的代码：Set mymenubar = CommandBars.ActiveMenuBarSet newmenu1 = mymenubar.Controls.Add(Type:=msoControlPopup, _Temporary:=True)newmenu1.Caption = 文件制作(&M)newmenu1.BeginGroup = True 这就是你要的白色横线simen你知道在窗体中也有这样的分隔线的如何实现呢？kevin_168这,我可没有试过,不过我做的时候使用一LABEL将其设为 能否在取消“运
22、行宏”时并不打开其它工作表！Q：我看见有些模块（高手给的）能够在取消“运行宏”时并不打开其它工作表！不知是何办法？ 但当你启动宏后，工作表才被打开！这种方法是什么？A：Rowen这些工作表预先都是隐藏的,必须用宏命令打开,所以取消宏的情况下是看不到的.可以打开VBA编辑器,在工作表的属性窗口中将其Visible 设为xlSheetVisible立体,看起来也够美观的,不妨一试.象版主所说的多查帮助文件,对你有帮助.如何去掉单元格中间两个以上的空格？Q：单元格A1中有“中心是”，如果用TRIM则变成“中心是”，我想将空格全去掉，用什么办法，请指教！A：用SUBSTITUDE（）函数，多少空格都能
23、去掉。如A1中有：中心 是 则在B1中使用=SUBSTITUTE(A1, ,)就可以了。注意：公式中的第一个“ ”中间要有一个空格，而第二个“”中是无空格的。打印表头？Q：在Excel中如何实现一个表头打印在多页上？打印表尾？A：BY dongmu请选择文件-页面设置-工作表-打印标题-顶端标题行，然后选择你要打印的行。打印表尾？通过Excel直接提供的功能应该是无法实现的，需要用vba编制才行。提取性别（无论是15位还是18位）=IF(LEN(A1)=15,IF(MOD(MID(A1,15,1),2)=1,男,女),IF(MOD(MID(A1,17,1),2)=1,男,女)如果身份证号的输入
24、已是15或18位，用公式=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1),2),男,女)Excel 问答集锦 录入：e股脑 点击次数：127 更新时间：2006-11-9 正文: 笔者与Excel经常“打交道”的过程中，常遇到这样或那样的问题。其中，以下几方面的问题尤为突出。现将出现的问题及解决的方法告知同行，以此共勉。一、在Excel中不能进行求和运算由于在操作中更改了字段的数值后，求和字段的所有单元格中的数值没有随之变化，造成不能正常运算。可以单击“工具选项”命令，在打开的“选项”对话框中单击“重新计算”选项卡。在“计算”选项区中选中“自动重算”单选按钮，单击“确
25、定”按钮，就可以进行自动计算并更新单元格的值了。二、在Excel中打开多个工作表时提示“内存不足，不能执行显示”这时首先关闭所有的应用程序，在桌面上单击鼠标右键，从弹出的快捷菜单中选择“属性”选项，在打开的“显示属性”对话框中单击“设置”选项卡，将“颜色”下拉列表框设置为256色，即可解决问题。三、在Excel中出现启动慢且自动打开多个文件进入Excel中后，单击“工具选项”命令，在打开的“选项”对话框中单击“常规”选项卡，删除“替补启动目录”文本框中的内容，单击“确定”按钮即可。四、在Excel中出现“DIV/0！”错误信息若输入的公式中的除数为0，或在公式中除数使用了空白单元格（当运算对象
26、是空白单元格，Excel将此空值解释为零值），或包含零值单元格的单无格引用，就会出现错误信息“#DIV/0！”。只要修改单元格引用，或者在用作除数的单元格中输入不为零的值即可解决问题。五、在Excel中出现“VALUE！”错误信息此情况可能有以下四个方面的原因之一造成：一是参数使用不正确；二是运算符使用不正确；三是执行“自动更正”命令时不能更正错误；四是当在需要输入数字或逻辑值时输入了文本，由于Excel不能将文本转换为正确的数据类型，也会出现该提示。这时应确认公式或函数所需的运算符或参数是否正确，并且在公式引用的单元格中包含有效的数值。六、在Excel中出现“NAME？”错误信息出现此情况一般是在公式中使用了Excel所不能识别的文本，比如：使用了不存在的名称。解决的方法是：单击“插入名称定义”命令，打开“定义名称”对话框。如果所需名称没有被列出，在“在当前工作薄的名称”文本框中输入相应的名称，单击“添加”按钮将其添加，再单击“确定”按钮即可。七、在Excel中出现“NUM！”错误信息当函数或公式中使用了不正确的数字时将出现错误信息“#NUM!”。这时应确认函数中使用的参数类型的正确性，然后修改公式，使其结果在-10307到 10307范围内即可。