excel使用技巧.docx

excel使用技巧.docx

《excel使用技巧.docx》由会员分享，可在线阅读，更多相关《excel使用技巧.docx（7页珍藏版）》请在冰豆网上搜索。

excel使用技巧

excel空格全部去掉

单元格A1的例子为 :

  YES

1] 消除空格, B1输入公式 :

=SUBSTITUTE（SUBSTITUTE（A1," ",""）,CHAR（160）,""）

返回 YES

excel 将A1到C6中大于=3的数依次放入E列

''将A1到C6中大于=3的数依次放入E列

Private Sub CommandButton1_Click（）

Dim i As Long

r = 1

For Each i In Range（"a1:

c6"）

    If i > =3 Then Cells（r, 5） = i:

 r = r + 1

Next

End Sub

excel 一次性删除工作簿中所有工作表的背景

Sub delback（）

For Each shtSheet In Sheets

    shtSheet.SetBackgroundPicture Filename:

=""

    Next shtSheet

End Sub

excel 列表一次性增加项目

Sheet1.ListBox1.List = Array（"一月", "二月", "三月", "四月"）''一次性增加项目

EXCEL中求两陈列的对应元素乘积之和

即有简结一点的公式求和:

a1*b1+a2*b2+b3*b3...的和.应有一函数XXXX（A1:

A3,B1:

B3）或XXXX（A1:

B3）

在B4中输入公式"=SUM（A1:

A3*B1:

B3）",按CTRL+SHIFT+ENTER结束. 

或

=SUMPRODUCT（A1:

A10,B1:

B10）

从身份证号码中提取性别

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"）

道理都是一样的，比那个公式优质

提取性别（无论是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）,"男","女"

发掘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、假合并

    我们也来一个“瞒天过海”吧，呵呵，在对单元格设置边框时，利用工具栏中的“外部框线”将两个单元格框在一起，这样两个单元格中数据就在一个表格中了，打印出来后保证不留一点“作假”痕迹。

此招对付较少单元格效果不错，要应付大量的单元格，奉劝你还是来点“真”的吧！

否则够你忙的了。

    拆分数据

    合并方法掌握了，拆分也不难，拆分的方法与前面介绍的合并方法相似，先将XLS文件另存为TXT文件，再对TXT文件进行编辑，将需要拆分的数据用“Tab”键或空格分开，然后再用Excel重新打开编辑后的TXT文件，这样原来在一起的数据就被拆分开了。

    处理含有大量信息的表格时，以上合并与拆分的方法很有效，有兴趣的朋友可以试一试。

]]

在Excel中自动推测出生年月日及性别的技巧

大家都知道，身份证号码已经包含了每个人的出生年月日及性别等方面的信息（对于老式的15位身份证而言，7-12位即个人的出生年月日，而最后一位奇数或偶数则分别表示男性或女性。

如某人的身份证号码为420400*********，它的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自动填充员工出生年月日、性别的功能，从而极大地减轻了用户录入数据时的工作量，有兴趣的读者不妨一试。

日期转换星期的问题

工作中须将表格中大量的日期同时转换为中英文的星期几 

1.用公式:

=text（weekday（xx）,"ddd"） 

2.用VBA,weekday（）,然后自定义转换序列 

3.用"拼写检查",自定义一级转换序列 

转成英文：

 =TEXT（WEEKDAY（A1）,"dddd"） 

转成中文：

 =TEXT（WEEKDAY（A1）,"aaaa"）

自动设置页尾线条

将表格的外框线和框内线条设置为不同格式，但在打印时却无法将每一页的底部外框线自动设为和其他三条边线一致，每次都必须手工设置（那可是几十页哦！

），而且如果换一台打印机的话就会前功尽弃,如何解决?

打印文件前试试运行以下的代码。

打印后关闭文件时不要存盘，否则下次要把格式改回来就痛苦了。

（当然你也可以另写代码来恢复原来的格式）：

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　　　B　　　　　C 

　1　50　　采购　　（采购汇总） 

　2　60　　工程　　（工程汇总） 

　3　80　　工资　　（工资汇总） 

　4　100　　税　　　（税汇总） 

　5　70　　采购　　 

　6　50　　工资　　 

　7　60　　工程 

　. . . 

　C1、C2、C3、C4中的公式如何设置？

（C1、C2、C3、C4是B列的采购合计数、工程汇合计数、工资合计数等）

C1={SUM（IF（MOD（ROW（data）,4）=1,data））} 

C2={SUM（IF（MOD（ROW（data）,4）=2,data））} 

C3={SUM（IF（MOD（ROW（data）,4）=3,data））} 

C4={SUM（IF（MOD（ROW（data）,4）=0,data））} 

无需数组公式：

sumif（条件区域,条件,求和区域） 

c1=sumif（b1:

b7,"采购",a1:

a7） 

c2=sumif（b1:

b7,"工程",a1:

a7） 

c3=sumif（b1:

b7,"工资",a1:

a7） 

c4=sumif（b1:

b7,"税",a1:

a7）