必备的excel技巧问答

《必备的excel技巧问答》由会员分享，可在线阅读，更多相关《必备的excel技巧问答（9页珍藏版）》请在金锄头文库上搜索。

1、必备的必备的 EXCELEXCEL 技巧问答技巧问答摸索出了一套自己的“合并与拆分”理论，供同行们参考。 所谓合并数据是指将两列中的数据合并到一列中，拆分数据是指将一列中的数据拆 分到两列中，图 1 为“毕业院校”和“专业”两列的拆分及合并的效果图。 合并数据 要将“毕业院校”和“专业”两列中数据合并为“毕业院校及专业” ，有“真” 、 “假”两种合并方法，所谓真合并就是将两列数据真正合并到一列中，而假合并表面上 看是合并为一列了，实际数据还是存在于两列中，具体操作跟我一起来看看吧。 1、真合并 如果将一列中的数据通过再输入合并到另一列中，那不是我们希望看到的解决办 法，快捷而准确的操作方法是

2、： (1)将需要合并的“毕业院校”和“专业”两列数据通过“剪切” 、 “粘贴”移动 到一个新工作表中，如命名为“合并”工作表。 (2)在“合并”工作表中将 XLS 文件另存为 TXT 文件，单击“文件另存为” ，保存 类型选择“文本文件(制表符分隔)” ，单击保存按钮后根据提示连续单击两次确定 按钮即可。 (3)在 Execl 中单击打开按钮，重新打开 TXT 文件，在“文本导入向导步骤之 1” 中采用默认设置，单击下一步按钮，在“步骤之 2”分隔符号中，去掉“Tab 键”前复 选框的选择，单击完成按钮，这时两列中数据合并到一列中。 (4)将合并后的数据再通过“剪切” 、 “粘贴”操作移动到原

3、工作表的原位置处， 合并操作完成。 提示：合并后的数据在原来两列数据(如毕业院校和专业)之间会有一个空格，而有 时根据实际需要去掉或加宽空格，可采取如下措施： 方法一：对 TXT 文件进行编辑 XLS 文件中列与列之间数据转换为 TXT 文件后是以制表符分隔的，此时合并后的数据 在原来两列数据之间会有一个空格，改变 TXT 文件中两列之间距离(即改变空格的大 小)，对应的 XLS 文件中原两列数据之间的距离也会随之发生改变，若 TXT 文件中两列数 据之间距离增大或减小为零，则合并的 XLS 文件中两列数据之间距离也会随之增大或减 小为零。如果你要将两列数据无缝合并，建议应用下面两种方法。 方

4、法二：利用函数 ConcateName ConcateName 函数可以实现将几个文本字符串合并为一个文本字符串，语法为： ConcateName (text1,text2, )，text1, text2, 为 1 到 30 个将要合并成单个文 本项的文本项，这些文本项可以为文本字符串、数字或对单个单元格的引用，如图 2 所 示。 图 2 方法三：利用“&”运算符 利用“&”运算符也可将几个文本字符串合并为一个文本字符串， “&”运算方式同 “+” 、 “-”等运算方式一样，如图 2 所示。 2、假合并 我们也来一个“瞒天过海”吧，呵呵，在对单元格设置边框时，利用工具栏中的 “外部框线”将两个

5、单元格框在一起，这样两个单元格中数据就在一个表格中了，打印 出来后保证不留一点“作假”痕迹。此招对付较少单元格效果不错，要应付大量的单元格，奉劝你还是来点“真”的吧！否则够你忙的了。 拆分数据 合并方法掌握了，拆分也不难，拆分的方法与前面介绍的合并方法相似，先将 XLS 文件另存为 TXT 文件，再对 TXT 文件进行编辑，将需要拆分的数据用“Tab”键或空格分 开，然后再用 Excel 重新打开编辑后的 TXT 文件，这样原来在一起的数据就被拆分开了。 处理含有大量信息的表格时，以上合并与拆分的方法很有效，有兴趣的朋友可以试 一试。从身份证号码中提取性别 Q：A1 单元格中是 15 位的身份

6、证号码，要在 B1 中显示性别(这里忽略 15 位和 18 位身份证 号 码的判别）B1=if(mod(right(A1,1),2)0,“male“,“female“) 请问这个公式有无问题，我试过没发现问题。但在某个网站看到作者所用的是如下公式 ： B1=if(mid(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,1

7、7,1),2)=1,“男“,“女“如果身份证号的输入已是 15 或 18 位，用公式 =IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1),2),“男“,“女“xls-exe 可以么？ A：Kevin 如果只是简单的转换成 EXE，当然可以。 如果你指的是脱离 Excel 也可以运行，好像没听说过可以。 当然，通过 DDE，是可以不运行 Excel 但调用它的所有功能的，但前提仍然是你的计算机 上已经安装了 Excel列的跳跃求和 Q:若有 20 列（只有一行） ，需没间隔 3 列求和，该公式如何做？ 前面行跳跃求和的公式不管用。A:roof 假设 a1 至 t1 为数据

8、（共有 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：如我输入.52 它自动给拆成￥1 2 3 4 5 6 5 2 的形式并且随我输入的长度改 变而改变？ A：Chiu 我所知函数不多，我是这样做的，如有更方便的方法，请指点 例如： 在 A1 输入小写金额，则： 千万：B1=IF

9、(A1=,MID(RIGHTB(A1*100,10),1,1),IF(A1=,“￥“,0) 百万：C1=IF(A1=,MID(RIGHTB(A1*100,9),1,1),IF(A1=,“￥“,0) 十万：D1=IF(A1=,MID(RIGHTB(A1*100,8),1,1),IF(A1=10000,“￥“,0) 万：E1=IF(A1=10000,MID(RIGHTB(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(RIGH

10、TB(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.01,“￥“,0) 分：K1=IF(A1=0.01,RIGHTB(A1*100,1),0) 网客 公式中最后一个 0 改为“如何编这个公式 Q：我想编的公式是： a/84 - （b4） 其中 a 是一个数值，小于

11、或等于 84；b 是包含字符 C 的单元格的个数；C 是一个符号。 这个公式的关键是要统计出包含字符 C 的单元格的个数，可我不会。 A：dongmu=a/(84-countif(b,“=c“)*4) chwd 我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c” 在 D2-D30 中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式 如何做？ 再一次感谢！ 受 dongmu 朋友公式的启发，我做出了需要的公式=a/(84-COUNTIF(D3:D30,“c“)*4) skysea575 :其中 a 是一个数值，小于或等于 84；b 是包含字符 C 的

12、单元格的个数；C 是一 个符号。 “包含字符 C“在这里的意思不清楚。你的公式中只可以计算仅含有“C”字符的单元格 数。 可能你的想法是计算字符中凡是含有这个字或字母的词。如“文章”和“文字”中都有 一个“文”字，是否计算在内？将文件保存为以某一单元格中的值为文件名的宏怎么写 A：lxxiu 假设你要以 Sheet1 的 A1 单元格中的值为文件名保存，则应用命令：ActiveWorkbook.SaveCopyAs Str(Range(“Sheet1!A1“) + “.xls“EXCEL 中求两陈列的对应元素乘积之和 Q:即有简结一点的公式求如:a1*b1+a2*b2+b3*b3.的和.应有一

13、函数 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:Rowen 1.用公式:=text(weekday(xx),“ddd“) 2.用 VBA,weekday(),然后自定义转换序列 3.用“拼写检查“,自定义一级转换序列4. dongmu 转成英文： =TEXT(WEEKDAY(A1

14、),“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 28 01 02 07 09 12 15 22 09 15 17 20 22 29 32 比较，如果有相同的数就在第八位记一个数。如01 04 12 19 25 26 32 2 02 08 15 16 18 24 28 1 01 02 07 09 12 15 22

15、 2 09 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 有一难题请教， 我的工作表通常都很长，偏偏我这人以特爱美，所 以会将表格的外框线

16、和框内线条设置为不同格式，但在打印时却无法将每一页的底部外 框线自动设为和其他三条边线一致，每次都必须手工设置（那可是几十页哦！） ，而且 如果换一台打印机的话就会前功尽弃，不知哪位大侠可指教一两招，好让 DD 我终生受 用，不胜感激！ A：roof 打印文件前试试运行以下的代码。打印后关闭文件时不要存盘，否则下次要把格式改回 来就痛苦了。 （当然你也可以另写代码来恢复原来的格式）：Sub detectbreak()mycolumn = Range(“A1“).CurrentRegion.Columns.Count Set myrange = Range(“A1“).CurrentRegion For Each mycell In myrange Set myrow = mycell.EntireRow If myrow.PageBreak = xlNone Then GoTo Nex Else Set arow = Range(Cells(myrow.