Excel 电子表格运用技巧汇总(续)

10、用Excel绘制函数图像 　 　函数图像能直观地反映函数的性质，用手工方法来绘制函数图像效果不太好，而用Excel绘制函数图像非常简便，所作图像非常标准、漂亮，具体方法如下： 　　Ａ、首先打开电子表格的*作窗口，然后用鼠标选择菜单栏中的“新建”命令，这时屏幕上会出现一个空白的电子表格； Ｂ、然后在A列的A1格输入“X=”，表明这是自变量，再在A列的A2及以后的格内逐次从小到大输入自变量的各个值；实际输入的时候，通常应用等差数列输入法，先输入前三个值，定出自变量中数与数的距离，然后点击A2格，按住鼠标拖到A4格选中这三项，使这三项变成一个黑色矩形，再用鼠标指向这黑色矩形的右下角的小方块，当光标变成“＋”后，按住鼠标拖动光标到适当的位置，就完成自变量的输入； 　　Ｃ、接着在B列的B1格输入函数式y=f（x）的一般函数表达式，如y=1/x；也可在点击工具栏上的函数图标“fx”后，在出现的不同函数列表的选项中选择需要的一种；输入结束后，点击函数输入对话框旁的勾号，B2格内马上得出了计算的结果。这时，再选中B2格，让光标指向B2矩形右下角的方框，当光标变成“＋”时按住光标沿B列拖动到适当的位置即完成函数值的计算。注意一定要把该函数中自变量x的位置输入前面A列自变量的绝对位置A2格，这样下面计算的时候才会对不同的自变量进行计算； Ｄ、最后点击工具栏上的“图表向导”图标，在出现的各种图表格式图标中选择“X，Y散点图”，然后在出现的“X，Y散点图”类型中选择“无数据点平滑线散点图”；这时，可按住鼠标察看即将绘好的函数图像。另外，对于自变量仅为正值的函数，也可用“图表向导”中的“折线图”格式绘制。 在Excel中自动推测出生年月日及性别的技巧　　大家都知道，身份证号码已经包含了每个人的出生年月日及性别等方面的信息（对于老式的15位身份证而言，7-12位即个人的出生年月日，而最后一位奇数或偶数则分别表示男性或女性。如某人的身份证号码为420400700101001，它的7-12位为700101，这就表示该人是1970年元月1日出生的，身份证的最后一位为奇数1，这就表示该人为男性；对于新式的18位身份证而言，7-14位代表个人的出身年月日，而倒数第二位的奇数或偶数则分别表示男性或女性）。根据身份证号码的这些排列规律，结合Excel的有关函数，我们就能实现利用身份证号码自动输入出生年月日及性别等信息的目的，减轻日常输入的工作量。 　　Excel中提供了一个名为MID的函数，其作用就是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定（另有一个名为MIDB的函数，其作用与MID完全一样，不过MID仅适用于单字节文字，而MIDB函数则可用于汉字等双字节字符），利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。 　　提示：MID函数的格式为MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)，其中Text是包含要提取字符的文本串；Start_num是文本中要提取的第一个字符的位置（文本中第一个字符的start_num为1，第二个为2……以此类推）；至于Num_chars则是指定希望MID从文本中返回字符的个数。 　　假定某单位人员管理希望通过身份证号码自动得出员工的出生年月日及性别，现以此为例，将有关步骤向广大用户作一简要介绍： 　　1. 选中整个B列，然后执行“格式”菜单中的“单元格”命令，打开“单元格格式”窗口。 　　2. 选择“数字”选项卡。 　　3. 在“分类”栏中选择“文本”选项，然后单击“确定”按钮，关闭“单元格格式”窗口，将所有包含有身份证号码的单元格设置为文本格式。 　　4.将光标移至C3单元格中，然后输入“="19"&MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&"日"”内容。其中MID(B3,7,2)就是从身份证号码的第7位开始取2位数，得出该员工的出生年份，MID(B3,9,2)就是得出该员工的出生月份，而MID(B3,11,2)则是该员工的出生日期，这些信息再加上年、月、日等文字就会组成该员工的准确出生年月日“1970年1月1日”。 　　5. 接下来我们应将光标移至D3单元格中，然后输入“=IF(MID(B3,15,1)/2=TRUNC(MID(B3,15,1)/2),"女","男")”。这就表示取身份证号码的第15位数，若能被2整除，这表明该员工为女性，否则为男性。 　　6. 最后我们只需利用自动填充功能对其他各个员工的出生年月日、性别进行填充即可。 　　上面都是以15位身份证为例进行介绍的，18位身份证的*作方法与此类似，广大用户若使用的是18位身份证，只需对有关函数的取值位置进行适当调整即可（如将“="19"&MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&"日"”修改为“=MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日"”）。 　　这样就实现了由Excel自动填充员工出生年月日、性别的功能，从而极大地减轻了用户录入数据时的工作量，有兴趣的读者不妨一试。 在EXCEL中增加自动填充序列　　　中文ＥＸＣＥＬ９７作为一种功能强大、技术先进的电子表格软件，给我们的办公带来了极大方便。 　　在ＥＸＣＥＬ中提供了自动填充功能，我们在使用时，可以通过拖动“填充柄”来完成数据的自动填充。例如要输入甲、乙、丙、丁……，可以先在指定单元格输入甲，然后将鼠标移至单元格的右下角的小方块处，直至出现“＋”字，按住鼠标左键，向下（右）拖动至目的单元格，然后松开即完成了自动填充。可是有时我们会发现有一些数据序列不能自动填充，例如车间一、车间二、车间三等，填充方法有两种： 　　第一种：单击“菜单”栏上的“工具”，选“选项”→“自定义序列”，这时就可以在“输入序列”栏输入要定义的序列。需要注意的是每输入完成一项就要回车一次，表示一项已经输入完毕，全部输入完成以后单击“添加”→“确定”，这样我们自定义的序列就可以使用了。 　　第二种：首先把你要添加的序列输入到一片相临的单元格内，例如要定义一个序列：车间一、车间二、车间三，把这三项分别输入到单元Ｈ１：Ｈ３，单击“工具”→“选项”→“自定义序列”→“导入”，在“导入序列所在的单元格”所指的对话框中输入Ｈ１：Ｈ３，单击“导入”→“添加”→“确定”，这样新序列就产生了。 　　定义的序列如果不再使用，还可删除，方法是：单击“工具”→“选项”→“自定义序列”，在“自定义序列”框中，单击要删除的序列，再单击“删除”→“确定”。 发掘Execl XP合并拆分技巧本人在用Excel XP管理教职工档案时，经常遇到合并或拆分单元格数据的情况，经过一段时间的使用后摸索出了一套自己的“合并与拆分”理论，供同行们参考。     所谓合并数据是指将两列中的数据合并到一列中，拆分数据是指将一列中的数据拆分到两列中，图1为“毕业院校”和“专业”两列的拆分及合并的效果图。     合并数据     要将“毕业院校”和“专业”两列中数据合并为“毕业院校及专业”，有“真”、“假”两种合并方法，所谓真合并就是将两列数据真正合并到一列中，而假合并表面上看是合并为一列了，实际数据还是存在于两列中，具体*作跟我一起来看看吧。     1、真合并     如果将一列中的数据通过再输入合并到另一列中，那不是我们希望看到的解决办法，快捷而准确的*作方法是：     (1)将需要合并的“毕业院校”和“专业”两列数据通过“剪切”、“粘贴”移动到一个新工作表中，如命名为“合并”工作表。     (2)在“合并”工作表中将XLS文件另存为TXT文件，单击“文件→另存为”，保存类型选择“文本文件(制表符分隔)”，单击[保存]按钮后根据提示连续单击两次[确定]按钮即可。     (3)在Execl中单击[打开]按钮，重新打开TXT文件，在“文本导入向导→步骤之1”中采用默认设置，单击[下一步]按钮，在“步骤之2”分隔符号中，去掉“Tab键”前复选框的选择，单击[完成]按钮，这时两列中数据合并到一列中。     (4)将合并后的数据再通过“剪切”、“粘贴”*作移动到原工作表的原位置处，合并*作完成。     提示：合并后的数据在原来两列数据(如毕业院校和专业)之间会有一个空格，而有时根据实际需要去掉或加宽空格，可采取如下措施：     方法一：对TXT文件进行编辑     XLS文件中列与列之间数据转换为TXT文件后是以制表符分隔的，此时合并后的数据在原来两列数据之间会有一个空格，改变TXT文件中两列之间距离(即改变空格的大小)，对应的XLS文件中原两列数据之间的距离也会随之发生改变，若TXT文件中两列数据之间距离增大或减小为零，则合并的XLS文件中两列数据之间距离也会随之增大或减小为零。如果你要将两列数据无缝合并，建议应用下面两种方法。     方法二：利用函数ConcateName     ConcateName函数可以实现将几个文本字符串合并为一个文本字符串，语法为：ConcateName (text1,text2, ……)，text1, text2, ……为1到30个将要合并成单个文本项的文本项，这些文本项可以为文本字符串、数字或对单个单元格的引用，如图2所示。     图2     方法三：利用“&”运算符     利用“&”运算符也可将几个文本字符串合并为一个文本字符串，“&”运算方式同“+”、“-”等运算方式一样，如图2所示。     2、假合并     我们也来一个“瞒天过海”吧，呵呵，在对单元格设置边框时，利用工具栏中的“外部框线”将两个单元格框在一起，这样两个单元格中数据就在一个表格中了，打印出来后保证不留一点“作假”痕迹。此招对付较少单元格效果不错，要应付大量的单元格，奉劝你还是来点“真”的吧！否则够你忙的了。     拆分数据     合并方法掌握了，拆分也不难，拆分的方法与前面介绍的合并方法相似，先将XLS文件另存为TXT文件，再对TXT文件进行编辑，将需要拆分的数据用“Tab”键或空格分开，然后再用Excel重新打开编辑后的TXT文件，这样原来在一起的数据就被拆分开了。     处理含有大量信息的表格时，以上合并与拆分的方法很有效，有兴趣的朋友可以试一试。]]ExcelHome精华版整理（2003年11月14日）By 徒然客从身份证号码中提取性别Q：A1单元格中是15位的身份证号码，要在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,17,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为数据（共有20列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1)) 按ctrl＋shift＋enter结束即可求出每隔三行之和。 跳行设置：如有12行，需每隔3行求和=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12))) 能否象打支票软件那样输入一串数字它自动给拆分成单个数字？Q：如我输入123456.52它自动给拆成￥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(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(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.01,"￥",0)) 分：K1=IF(A1>=0.01,RIGHTB(A1*100,1),0) 网客 公式中最后一个0改为""如何编这个公式Q：我想编的公式是： a/[84 - （b×4）] 其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。 这个公式的关键是要统计出包含字符C的单元格的个数，可我不会。 A：dongmu =a/(84-countif(b,"=c")*4)chwd我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c”在D2-D30中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式如何做？ 再一次感谢！ 受dongmu朋友公式的启发，我做出了需要的公式 =a/(84-COUNTIF(D3 30,"c")*4) skysea575 :其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。"包含字符C"在这里的意思不清楚。你的公式中只可以计算仅含有“C”字符的单元格数。可能你的想法是计算字符中凡是含有这个字或字母的词。如“文章”和“文字”中都有一个“文”字，是否计算在内？将文件保存为以某一单元格中的值为文件名的宏怎么写A：lxxiu 假设你要以Sheet1的A1单元格中的值为文件名保存，则应用命令： ActiveWorkbook.SaveCopyAs Str(Range("Sheet1!A1")) + ".xls"IE中实现链接EXCEL表Q:我想在IE中实现链接EXCEL表并打开后可填写数据，而且可以实现数据的远程保存（在局域网内的数据共享更新），我的设想是在NT中上提供电子表格服务， 各位局域网内用户在IE浏览器中共享修改数据，请问我该如何*作才能实现这一功能。我是初学者，请尽量讲得详细一点。A:老夏 <a href="C:\mm.xls" target="x"> mm.xls</a> <a href="" target="x">桌面</a> <iframe width=780 height=400></iframe> ************************************************************** 貼到計事本- 存為Home.htm擺桌面上- 設為共享- 區域網路其他電腦設捷徑連結Home.htm 懂這三行字的結構,即可寫出網頁資料庫系統 其中每個字都是[物件名稱] 檔案多了改用<Frame>這個元件 要設幾個框隨便您 EXCEL中求两陈列的对应元素乘积之和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:Rowen 1.用公式:=text(weekday(xx),"ddd") 2.用VBA,weekday(),然后自定义转换序列 3.用"拼写检查",自定义一级转换序列 4....dongmu 转成英文： =TEXT(WEEKDAY(A1),"dddd") 转成中文： =TEXT(WEEKDAY(A1),"aaaa")研究彩票，从统计入手Q：我有一个VBA编程的问题向你请教。麻烦你帮助编一个。我一定厚谢。 有一个数组列在EXCEL中如： 01 02 03 04 05 06 07 和01 04 12 19 25 26 32 02 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 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有一难题请教， 我的工作表通常都很长，偏偏我这人以特爱美，所以会将表格的外框线和框内线条设置为不同格式，但在打印时却无法将每一页的底部外框线自动设为和其他三条边线一致，每次都必须手工设置（那可是几十页哦！），而且如果换一台打印机的话就会前功尽弃，不知哪位大侠可指教一两招，好让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.Offset(-1).Row, 1), Cells(myrow.Offset(-1).Row, mycolumn)) With arow.Borders(xlEdgeBottom) .LineStyle = xlDouble '把这一行改成自己喜欢的表线 .Weight = xlThick .ColorIndex = xlAutomatic End With End If Nex: Next mycell End 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 这是一个指数函数的联列方程。步骤如下 1、令X/Y=W 则有 X-(7W)^z=68   X-(20W)^Z=61   X-(30W)^Z=38 2、消去X (20^Z-7^Z)W^Z=7   (30^Z-20^Z)W^Z=23 3、消去W (30^Z-20^Z)/(20^Z-7^Z)=23/7 由此求得Z=3.542899 x=68.173955 y=781.81960 行高和列宽单位是什么? 如何换算到毫米?A：markxg 在帮助中： “出现在“标准列宽”框中的数字是单元格中 0-9 号标准字体的平均数。” 单位应该不是毫米，可能和不同电脑的字体有关吧。 Q：Rowen是这样: 行高/3=mm 列宽*2.97=mm鱼之乐 实际上最终打印结果是以点阵为单位的,而且excel中还随着打印比例的变化而变化如果想用宏写一个完全退出EXCEL的函数是什么？Q:因为我想在关闭lock.frm窗口时就自动退出EXCEL，请问用宏写一个完全退出EXCEL的函数是什么？多谢！A:Application.quit请问如何编写加载宏？把带有VBA工程的工作簿保存为XLA文件即可成为加载宏。请问如何在点击一个复选框后在后面的一个单元格内自动显示当前日期？如果是单元格用"=TODAY()"就可以了 如果是文本框在默认属性中设置或在复选框的CLICK中设置文本框的内容 EXCEL2000中视面管理器如何具体运用呀？请问高手EXCEL2000中视面管理器如何具体运用呀？最好有例子和详细说明。明确的功能。不然我还是不能深刻的理解他。markxg 其实很简单呀，你把它想象成运动场上的一串照片（记录不同时点的场景），一张照片记录一个场景，选择一张照片就把运动“拖”到照片上的时点。不同的是只是场景回复，而值和格式不回复。用VBA在自定义菜单中如何仿EXCEL的菜单做白色横线？Q：我在做自定义菜单时，欲仿EXCEL菜单用横线分隔各菜单项目，用VBA如何才能做到？ A：Rowen 那个东东也是一个部件,我想可以调用,不过没试过. diyee 把它的显示内容中设置为"-"即可。simen 1.此部件叫什么名字，在控件箱里有吗？ 2.用“-”我也试过，用它时单击可以，但你要知道EXCEL自己的横线是不可以单击下去的 kevin_168 object.BeginGroup = True 下面是我用到的代码： Set mymenubar = CommandBars.ActiveMenuBar Set newmenu1 = mymenubar.Controls.Add(Type:=msoControlPopup, _ Temporary:=True) newmenu1.Caption = "文件制作(&M)" newmenu1.BeginGroup = True '这就是你要的白色横线 simen 你知道在窗体中也有这样的分隔线的如何实现呢？kevin_168 这,我可没有试过,不过我做的时候使用一LABEL将其设为 能否在取消“运行宏”时并不打开其它工作表！Q：我看见有些模块（高手给的）能够在取消“运行宏”时并不打开其它工作表！不知是何办法？ 但当你启动宏后，工作表才被打开！这种方法是什么？ A：Rowen 这些工作表预先都是隐藏的,必须用宏命令打开,所以取消宏的情况下是看不到的.可以打开VBA编辑器,在工作表的属性窗口中将其Visible 设为xlSheetVisible立体,看起来也够美观的,不妨一试.象版主所说的多查帮助文件,对你有帮助. 如何去掉单元格中间两个以上的空格？Q：单元格A1中有“中　　心　　是”，如果用TRIM则变成“中　心　是”，我想将空格全去掉，用什么办法，请指教！！A：用SUBSTITUDE（）函数，多少空格都能去掉。如A1中有：中 心 是 则在B1中使用=SUBSTITUTE(A1," ","")就可以了。注意：公式中的第一个“ ”中间要有一个空格，而第二个“”中是无空格的。 打印表头？Q：在Excel中如何实现一个表头打印在多页上？打印表尾？A：BY dongmu请选择文件-页面设置-工作表-打印标题-顶端标题行，然后选择你要打印的行。打印表尾？通过Excel直接提供的功能应该是无法实现的，需要用vba编制才行。