excel小技巧.docx
《excel小技巧.docx》由会员分享，可在线阅读，更多相关《excel小技巧.docx（14页珍藏版）》请在冰豆网上搜索。
excel小技巧
提取年龄：
C2是身份证号,用下面的公式（15位和18位通用）可以提取出生年份，
=IF（LEN（C2）=15,MID（C2,7,2）,MID（C2,9,2））
然后用现在的年份减去出生年份就行了，比如用上面的公式得出的是82年，然后用108-82=26岁
年龄
=IF（LEN（H4）=18,DATEDIF（DATE（MID（H4,7,4）,MID（H4,11,2）,MID（H4,13,2））,TODAY（）,"y"）,DATEDIF（DATE（MID（H4,7,2）+1900,MID（H4,9,2）,MID（H4,11,2））,TODAY（）,"y"））
生日
=IF（LEN（H4）=15,"19"&MID（H4,7,2）&"-"&MID（H4,9,2）&"-"&MID（H4,11,2）,MID（H4,7,4）&"-"&MID（H4,11,2）&"-"&MID（H4,13,2））
性别
=IF（MOD（IF（LEN（H4）=15,MID（H4,15,1）,MID（H4,17,1））,2）=0,"女","男"）（抄的。
。
。
。
。
。
。
。
。
。
^o^H4指有身份证号的那一单元格）
提问人的追问 2009-10-0922:
31
生日的计算不对，而且，年龄那个公式在没有身份证号的情况下，显示
#VALUE!
，我希望什么也不显示。
回答人的补充 2009-10-0922:
32
=IF（LEN（A2）=18,DATEDIF（DATE（MID（A2,7,4）,MID（A2,11,2）,MID（A2,13,2））,TODAY（）,"y"）,DATEDIF（DATE（MID（A2,7,2）+1900,MID（A2,9,2）,MID（A2,11,2））,TODAY（）,"y"））
将H4我替换成A2，并且在A2中输入的是身份证号，然后算出正确的年龄来了啊，没有问题的！
在excel中提取生日
在excel中如何快速输入身份证号如"610321"#
提取生日为什么会变成这样
按Enter后
本人现在有2张表格,2张都是有人名地址身份证号的.B表有部分人名地址与A表相符合,但是身份证号没有填入现在需要把B表中未输入身份证号的人名地址与A表中相符合的找出来然后把A表中与其对应的身份证号输入到B表本人很少接触excel不过觉得手工太慢应该可以做成函数什么的本人请同事做了一个程序但是人名相同地址确不同不能确认是同一人想做成查找到后人名地址都符合的输入到B表中去.
其实这个蛮简单的，用查找函数vlookup就可以做到。
但有一个无法解决的问题是：
如果你有多人同名，用公式函数只会填入同一个人的身份证号码。
这一点要注意一下。
用数据筛选，把A的人名和身份证，以及B的姓名复制到另一个工作薄中，对姓名进行筛选，筛选后把A跟B相同姓名的身份证复制下，回到B的工作薄，对姓名进行筛选，在把刚复制的身份证在复制下就OK了！
在Excel中，点插入——图表——饼图——子图表类型选分离式三维饼图——下一步——点“数据区域”后的按钮，选中第三列第二行到每五行单元格区域——回车——下一步——然后填写图表标题、设置是否显示图例，及位置、数据标志包含的内容等——完成。
在excel中如何快速输入身份证号如"610321"#方法如下:
选中要输入的区域,右击>"设置单元格格式">"数字">"自定义">"610321"@,如图:
>"确定",只需输入后面的部分（如198901151518）即可,如图:
取生日的公式则应改为:
=DATE（MID（A1,1,4）,MID（A1,5,2）,MID（A1,7,2））
提取生日为什么会变成这样
Excel中常用的公式和用法
1、两列数据查找相同值对应的位置
=MATCH（B1,A:
A，0）
2、已知公式得结果
定义名称=EVALUATE（Sheet1!
C1）
已知结果得公式
定义名称=GET.CELL（6,Sheet1!
C1）
3、强制换行
用Alt+Enter
4、超过15位数字输入
这个问题问的人太多了，也收起来吧。
一、单元格设置为文本;二、在输入数字前先输入’
5、如果隐藏了B列，如果让它显示出来?
选中A到C列，点击右键，取消隐藏
选中A到C列，双击选中任一列宽线或改变任一列宽
将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
6、EXCEL中行列互换
复制，选择性粘贴，选中转置，确定即可
7、Excel是怎么加密的
（1）保存时可以的另存为>>右上角的"工具">>常规>>设置
（2）工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1:
A10,">=90"）
介于80与90之间需用减，为=COUNTIF（A1:
A10,">80"）-COUNTIF（A1:
A10,">90"）
9、根据身份证号提取出生日期
（1）=IF（LEN（A1）=18,DATE（MID（A1,7,4）,MID（A1,11,2）,MID（A1,13,2））,IF（LEN（A1）=15,DATE（MID（A1,7,2）,MID（A1,9,2）,MID（A1,11,2））,"错误身份证号"））
（2）=TEXT（MID（A2,7,6+（LEN（A2）=18）*2）,"#-00-00"）*1
10、想在SHEET2中完全引用SHEET1输入的数据
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
数据--有效性--自定义--公式
输入=COUNTIF（A:
A,A1）=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF（A:
A,A5）>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上
13、excel下拉菜单的实现
数据-有效性-序列
14、10列数据合计成一列
=SUM（OFFSET（$A$1,（ROW（）-2）*10+1,,10,1））
15、查找数据公式两个（基本查找函数为VLOOKUP,MATCH）
（1）根据符合行列两个条件查找对应结果
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）根据符合两列数据查找对应结果（为数组公式）
=INDEX（C1：
C7，MATCH（H1&I1,A1:
A7&B1:
B7,0））
16、如何隐藏单元格中的0
单元格格式自定义0;-0;;@或选项》视图》零值去勾。
呵呵，如果用公式就要看情况了。
17、多个工作表的单元格合并计算
=Sheet1!
D4+Sheet2!
D4+Sheet3!
D4，更好的=SUM（Sheet1:
Sheet3!
D4）
18、获得工作表名称
（1）定义名称：
Name
=GET.DOCUMENT（88）
（2）定义名称：
Path
=GET.DOCUMENT
（2）
（3）在A1中输入=CELL（"filename"）得到路径级文件名
在需要得到文件名的单元格输入
=MID（A1,FIND（"*",SUBSTITUTE（A1,"\","*",LEN（A1）-LEN（SUBSTITUTE（A1,"\",""））））+1,LEN（A1））
（4）自定义函数
PublicFunctionname（）
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
19、如何获取一个月的最大天数
:
"=DAY（DATE（2002,3,1）-1）"或"=DAY（B1-1）",B1为"2001-03-01
数据区包含某一字符的项的总和,该用什么公式
=sumif（a:
a,"*"&"某一字符"&"*",数据区）
最后一行为文本：
=offset（$b$1,MATCH（CHAR（65535）,b:
b）-1,）
最后一行为数字：
=offset（$b$1,MATCH（9.9999E+307,b:
b）-1,）
或者：
=lookup（2,1/（b1:
b1000""）,b1:
b1000）
评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?
同时显示出被去掉的分数。
看看trimmean（）函数帮助。
被去掉的分数：
最大两个：
=large（data,{1;2}）
最小两个：
=small（data,{1;2}）
怎样很简单的判断最后一位是字母
right（a1）*1
出错的字母
=IF（ISNUMBER（--RIGHT（A1,1））,"数字","字母"）
=IF（ISERR（RIGHT（A1）*1）,"字母","数字"）
如何设置单元格，令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror（find（"",a1））
数据--有效性--自定义--公式
=len（a1）=len（trim（a1））
原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....
现在是=sum（n（offset（a1,（row（1:
10）-1）*3,）））
在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?
=INDIRECT（"A1"&"!
"&"E1"）A1为工作表名
奇数行求和=SUMPRODUCT（（A1:
A1000）*MOD（ROW（A1:
A1000）,2））
偶数行求和=SUMPRODUCT（（A1:
A1000）*NOT（MOD（ROW（A1:
A1000）,2）））
查看字符串字数
=LEN（A1）
求非空单元格数量
公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）
=COUNTIF（$E$3:
$E$65536,"?
*"）
动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM（INDIRECT（"A1:
A"&ROW（）-1））
20、比较好用的EXCEL文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方，可以用这个公式，在单元格中输入=8^（1/3）
22、单元格中的数据分散对齐
文本格式－全角输入
23、查找工作表中的链接
Ctrl+~或编辑－链接
24、如何让空单元格自动填为0
选中需更改的区域－查找－空－替换－0
25、把Word里的数字转换到Excel
方法有多种，选中－复制－设置输入单元格为文本－选择性粘贴－值－选中－表格转换为文本－粘贴－分列－对分列选项设置为文本
另存为文本文件－EXCEL中打开文本文件－对导入文本对话框进行对应设置
将下面公式复制后粘贴到“B2”单元格内：
=IF（ISERROR（VLOOKUP（A2,A$1:
A1,1,0））,"","重复"）
然后拖下去即可在“B”列显示同一行“A”列上已重复的数据，如图：
光标放入数据区内任一单元（最好是第一行）点“数据”—“筛选”—“自动筛选”，点“B1”单元格的三角尖—“重复”，则重复的号码就筛选出来了，效果如图：
111111*********11111111111111111111111111111111
=IF（COUNTIF（A:
A,A1）>1,"重复"&COU
升级会员 