疑难杂症的克星常用的25条Excel技巧.docVIP

1、两列数据查找相同值对应的位置=MATCH(B1,A:A，0)2、已知公式得结果定义名称=EVALUATE(Sheet1!C1)已知结果得公式定义名称=GET.CELL(6,Sheet1!C1)3、强制换行用Alt+Enter4、超过15位数字输入这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入5、如果隐藏了B列，如果让它显示出来?选中A到C列，点击右键，取消隐藏选中A到C列，双击选中任一列宽线或改变任一列宽将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。6、EXCEL中行列互换复制，选择性粘贴，选中转置，确定即可7、Excel是怎么加密的(1)、保存时可以的另存为右上角的工具常规设置(2)、工具选项安全性8、关于COUNTIFCOUNTIF函数只能有一个条件，如大于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)*110、想在SHEET2中完全引用SHEET1输入的数据工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet211、一列中不输入重复数字数据-有效性-自定义-公式输入=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)、自定义函数Public Function name()Dim filename As Stringfilename = ActiveWname = filenameEnd Function19、如何获取一个月的最大天数:=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文档修复工具ExcelRecovery21、EXCEL开方运算将8开3次方，可以用这个公式，在单元格中输入=8(1/3)22、单元格中的数据分散对齐文本格式全角输入23、查找工作表中的链接Ctrl+ 或编辑链接24、如何让空单元格自动填为0选中需更改的区域查找空替换025、把Word里的数字转换到Excel方法有多种，选中复制设置输入单元格为文本选择性粘贴值选中表格转换为文本粘贴分列对分列选项设置为文本另存为文本文件EXCEL中打开文本文件对导入文本对话框进行对应设置利用身份证号码提取个人信息（如生日、性别、年龄、籍贯、是否成年）ex.下面是我个人搜集整理的excel的应用方法，非常的有用，和大家一块分享。利用身份证号码提取个人信息（身份证号码一定要正确，如果没有特别说明则身份证放在B列）1、利用身份证号码提取性别=IF(LEN(B2)=18,IF(MID(B2,17,1)/2=INT(MID(B2,17,1)/2),F,M),IF(MID(B2,15,1)/2=INT(MID(B2,15,1)/2),F,M)显示格式为：F、M（F为女、M为男）=IF(LEN(B2)=18,IF(MID(B2,17,1)/2=INT(MID(B2,17,1)/2),女,男),IF(MID(B2,15,1)/2=INT(MID(B2,15,1)/2),女,男)显示格式为：男、女2、（推荐 方法一：不用判断身份证位数）利用身份证号码提取出生年月（1）=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2)显示格式为：1996-1-1、1996-12-1、1999-12-12、1997-2-18（2）=CONCATENATE(MID(B2,7,4),-,MID(B2,11,2),-,MID(B2,13,2)显示格式为：1996-01-01、1996-12-01、1999-12-12、1997-02-18（3）=IF(LEN(B2)=15,19&MID(B2,7,2)&年,MID(B2,7,4)&年)&IF(LEN(B2)=15,MID(B2,9,2)&月,MID(B2,11,2)&月)&IF(LEN(B2)=15,MID(B2,11,2)&日,MID(B2,13,2)&日)显示格式为：1996年01月01日、1996年12月01日、1999年12月12日、1997年02月18日（方法二：需要判断身份证位数）提取出生年月信息身份证号写在A列。15位号码的公式：=CONCATENATE(19,(LEFT(RIGHT(A1,9),2),-(LEFT(RIGHT(A1,7),2),-(LEFT(RIGHT(A1,5),2)18位号码的公式：=CONCATENATE(LEFT(RIGHT(A1,12),4),-(LEFT(RIGHT(A1,8),2),-(LEFT(RIGHT(A1,6),2)上述公式的含义是：将分别提取的年份(LEFT(RIGHT(A1,9),2)，（在提取A1的右边9位数中再提取左边两位数值。下面的月份、日期公式的原理相同）月份(LEFT(RIGHT(A1,7),2)，日期(LEFT(RIGHT(A1,5),2)，并分别在月份、日期数据的前面加上日期的分隔符“”号，组合成XXXXXX的日期格式。有关公式的详情可参考Excel的函数帮助。3、利用身份证号码提取出生地区（同一张工作表在sheet2中放我国身份证编码）=LOOKUP(1,0/(LEFT(B2,6)=Sheet2!$A$1:$A$5805),Sheet2!B:B)利用电子表格的自动填充柄就可以大量的处理数据了。附件1-样例/d/b086481000d0dc885b1f24e5bc52bea8f962772b00920700附件2-我国身份证编制编码/d/4c4959b7eaeb50f4d48851f115043a2e89f6a21d004207004、判断A1单元格的身份证号是否成年的公式=IF(DATEDIF(DATEVALUE(TEXT(MID(A1,7,LEN(A1)/2.2),00-00-00),TODAY(),Y)18,未成年,成年)5、求A1单元格的身份证号年龄的公式：=YEAR(NOW()-IF(LEN(A1)=18,MID(A1,7,4),19&MID(A1,7,2)6、根据某一列的数字由大到小排名（与身份证无关）。这里举例从A2到A29排名。最后的0是让排名由大到小降序排列的。如果把这个0改为非零数字，就会按照由小到大的升序排列。=RANK(A2,$A$2:$A$29,0)WPS表格从身份证号提取生日及性别信息2007-01-11 09:57:12来源: 天极网 网友评论 3 条 进入论坛最近，单位需要上报人事局一份职工信息，其中既有个人身份证号，又有出生日期、性别等信息。由于人员众多，输入确实麻烦，而且容易出错，弄不好还得返工。能不能有个一个一劳永逸的办法，又快又准地录入出生日期、性别等个人信息呢？能！因为身份证号中包含这些个人信息，我们使用WPS表格可以轻松地提取些个人信息，以实现自动录入，从而又快又准地完成工作。目前，我国的身份证号分为两种，一种为15位，一种为18位。在15位的老版身份证中，第7到12位为出生日期数，最后一位为性别代码，偶数为女，奇数为男。18位的新身份证中第7到14位为出生日期数，倒数第2位为性别代码，同样偶数为女，奇数为男。基于这个特点，我们可以用函数加以判断。如图1是我们已输入完身份证号的职工信息表，在输入身份证号时需注意，要把单元格格式设为文本型，或在所输身份证号前加一个单引号“”。1、提取出生日期在D2中输入公式“=IF(LEN(C2)=15,TEXT(MID(C2,7,6),1900年00月00日),TEXT(MID(C2,7,8),00年00月00日)”，拖动填充柄向下复制公式，这样就完成了出生日期的提取（如图2）：2、提取性别信息在E2中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1),2)=1,男,女)”，拖动填充柄向下复制公式，这样就完成了性别信息的提取（如图3）:公式详解：LEN是长度函数，MID是提取字符函数，TEXT是转换格式函数，MOD是整除函数，IF是判断函数。提取出生日期公式的意思是如果身份证号为15位，把从第7位起的6个字符转换为1900年00月00日的格式，否则（即为18位身份