统计重名的电子表格函数最后一个好用.doc

B1=COUNTIF($A$1:$A$8,A1&*)“=COUNTIF($A:$A,A1)”，=COUNTIF($A$1:$A$8,A1&*)公式向下复制,得出的数值大于1的都是重复数据=IF(COUNTIF(A:A,A1)1,重复,)取数函数属于取数问题。EXCEL中常用的取数函数有三个，MID,RIGHT,LEFT。（具体语法可调用EXCEL中的函数帮助然后查看。）这三个函数挺有意思的，与len（lenb）函数结合可以解决很多问题。18位的身份证号码中，要提取8位出年出生年月。首先要知道18位身份证号码的构成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。故要由第七位开始取数，取8个。据此需求，故用MID函数。假设单元格A1中的内容为18位的身份证号码，则在B2中可输入公式=mid(a1,7,8)即可得到所要的。eg:19861021。若你想以1986-10-21的形式表达。则可用公式=MID(A1,7,4)&-&MID(A1,11,2)&-&MID(A1,13,2)。附件就不传了，具体的自己试试，玩下就知了。再者，你将这问题百度一下也可以得到更加详细的答案。第五招：留头去尾（Left函数） 此招用来对原始数据进行截取。截取的方式是从第一个字符开始，截取用户指定长度的内容。 例如：在一个工作表中，某一列的资料是地址，录有省、市、街道等。如果你想插多一列，加入省份的资料，以便进行省份筛选，则可用该函数自动进行截取，而无需人工输入。 使用语法 LEFT(text,num_chars) Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。 Num_chars 指定要由 LEFT 所提取的字符数。 Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 LEFT 返回所有文本。 如果省略 num_chars，则假定其为 1。 应用示例： 上图中，对含有不同地方的数据，利用“Left”函数，非常简单分离出了他们的省份。 详细解释 公式“=Left(A2,3)”中A2表示要截取的数据为A2单元格的内容“广东省东莞市东城区”，“3”表示从第一位开始，共截取3个字符，因此系统返回“广东省”。第六招：去头留尾（Right函数） 此招与上招刚好相反，截取的方式是从最后一个字符开始，从后往前截取用户指定长度的内容。 使用语法 RIGHT(text,num_chars) RIGHTB(text,num_bytes) Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。 Num_chars 指定希望 RIGHT 提取的字符数。 注意：Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。 如果忽略 num_chars，则假定其为 1。 应用示例： 详细解释 公式“=Right(A2,8)”中A2表示要截取的数据为A2单元格的内容“广东省东莞市 电话：22222222”，“8”表示从最后一位开始，共截取8个字符，因此系统返回“22222222”。尽管原始数据长短不齐，但我们只关心最后的8位电话号码。第四招：瞒天过海（自创组合招数） 我们来看学第一招时用的例子， 对于找不到的项目，系统显示#N/A，但这样的报告交给上司，未免太难看了些。用什么方法，可以让其不显示出错误值呢？对了，先来一招投石问路，对系统返回的值做一个判断，看看系统到底找到没有。再来一招左右逢源，对于找到的就显示原值，找不到的，就干脆让它显示空白（当然，也可让设置其他的值如No等），岂不妙哉？ 因此，对于原单一公式：=VLOOKUP(A2,$E$2:$F$5,2,FALSE)，可以结合IF和IS函数来使用。大家刚才看到，对于投石问路，共有九种变化，其中第三式（ISERROR）或第五式（ISNA）均适合这种情况，可以使用。因此，组合后的公式就变成： =IF(ISNA(VLOOKUP(D2,$G$2:$H$5,2,FALSE),VLOOKUP(D2,$G$2:$H$5,2,FALSE) 或 =IF(ISERROR(VLOOKUP(D3,$G$2:$H$5,2,FALSE),VLOOKUP(D3,$G$2:$H$5,2,FALSE) 下图显示了这种情况。红框中用的就是组合的公式，而其中的Plum & Pear没有再显示难看的#N/A，报表因此漂亮多了。 因为是公式，只要写好第一个单元格的公式，其余的一拖就好了。 好了，新创的这招叫什么呢？就叫瞒天过海吧！VBA 字符串函数&转换函数 1,vba字符串函数列表：Trim(string) 去掉string左右两端空白Ltrim(string) 去掉string左端空白Rtrim(string) 去掉string右端空白Len(string) 计算string长度Left(string, x) 取string左段x个字符组成的字符串Right(string, x) 取string右段x个字符组成的字符串Mid(string, start,x) 取string从start位开始的x个字符组成的字符串Ucase(string) 转换为大写Lcase(string) 转换为小写Space(x) 返回x个空白的字符串Asc(string) 返回一个integer，代表字符串中首字母的字符代码Chr(charcode) 返回string,其中包含有与指定的字符代码相关的字符2，VBA转换函数列表：CBool(expression) 转换为Boolean型CByte(expression) 转换为Byte型CCur(expression) 转换为Currency型CDate(expression) 转换为Date型CDbl(expression) 转换为Double型CDec(expression) 转换为Decemal型CInt(expression) 转换为Integer型CLng(expression) 转换为Long型CSng(expression) 转换为Single型CStr(expression) 转换为String型CVar(expression) 转换为Variant型Val(string) 转换为数据型Str(number) 转换为String3，VBA时间函数：Now 返回一个Variant (Date)，根据计算机系统设置的日期和时间来指定日期和时间。Date 返回包含系统日期的Variant (Date)。Time 返回一个指明当前系统时间的Variant (Date)。Timer 返回一个Single，代表从午夜开始到现在经过的秒数。TimeSerial(hour, minute, second)返回一个Variant (Date)，包含具有具体时、分、秒的时间。DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)返回Variant (Long)的值，表示两个指定日期间的时间间隔数目Second(time)返回一个Variant (Integer)，其值为0到59之间的整数，表示一分钟之中的某个秒Minute(time)返回一个Variant (Integer)，其值为0到59之间的整数，表示一小时中的某分钟Hour(time)返回一个Variant (Integer)，其值为0到23之间的整数，表示一天之中的某一钟点Day(date)返回一个Variant (Integer)，其值为1到31之间的整数，表示一个月中的某一日Month(date)返回一个Variant (Integer)，其值为1到12之间的整数，表示一年中的某月Year(date)返回Variant (Integer)，包含表示年份的整数。Weekday(date, firstdayofweek)返回一个Variant (Integer)，包含一个整数，代表某个日期是星期几4，VBA获取文件列表：Dir(pathname, attributes)；pathname可选参数，用来指定文件名的字符串表达式，可能包含目录或文件夹、以及驱动器。如果没有找到 pathname，则会返回零长度字符串(); attributes可选参数。常数或数值表达式，其总和用来指定文件属性。如果省略，则会返回匹配pathname但不包含属性的文件。5，VBA删除文件：Killpathname从磁盘中删除文件, pathname参数是用来指定一个文件名RmDirpathname从磁盘中删除删除目录，pathname参数是用来指定一个文件夹6,用VBA打开文件:Openpathname For mode Access access lock As #filenumber Len=reclength能够对文件输入/输出(I/O)。pathname必要。字符串表达式，指定文件名，该文件名可能还包括目录、文件夹及驱动器。mode必要。关键字，指定文件方式，有Append、Binary、Input、Output、或Random方式。如果未指定方式，则以Random访问方式打开文件。access可选。关键字，说明打开的文件可以进行的操作，有Read、Write、或Read Write操作。lock可选。关键字，说明限定于其它进程打开的文件的操作，有Shared、Lock Read、Lock Write、和Lock Read Write操作。filenumber必要。一个有效的文件号，范围在1到511之间。使用FreeFile函数可得到下一个可用的文件号。reclength可 选。小于或等于32，767（字节）的一个数。对于用随机访问方式打开的文件，该值就是记录长度。对于顺序文件，该值就是缓冲字符数。说明对文件做任何I/O操作之前都必须先打开文件。Open语句分配一个缓冲区供文件进行I/O之用，并决定缓冲区所使用的访问方式。如果 pathname指定的文件不存在，那么，在用Append、Binary、Output、或Random方式打开文件时，可以建立这一文件。如果文件已 由其它进程打开，而且不允许指定的访问类型，则Open操作失败，而且会有错误发生。如果mode是Binary方式，则Len子句会被忽略掉。重要在Binary、Input和Random方式下可以用不同的文件号打开同一文件，而不必先将该文件关闭。在Append和Output方式下，如果要用不同的文件号打开同一文件，则必须在打开文件之前先关闭该文件7,VBA读入文件:Input#filenumber, varlist从已打开的顺序文件中读出数据并将数据指定给变量Get#filenumber, recnumber, varname将一个已打开的磁盘文件读入一个变量之中。8,写入文件:Write#filenumber, outputlist将数据写入顺序文件Print#filenumber, outputlist将格式化显示的数据写入顺序文件中Put#filenumber, recnumber, varname将一个变量的数据写入磁盘文件中。9,VBA关闭文件:Closefilenumberlist关闭Open语句所打开的输入/输出(I/O)文件注意 如果今后想用Input #语句读出文件的数据，就要用Write #语句而不用Print #语句将数据写入文件。因为在使用Write #时，将数据域分界就可确保每个数据域的完整性，因此可用Input #再将数据读出来。使用Write #还能确保任何地区的数据都被正确读出。Write与Print #语句不同，当要将数据写入文件时，Write #语句会在项目和用来标记字符串的引号之间插入逗号。Write #语句在将outputlist中的最后一个字符写入文件后会插入一个新行字符，即回车换行符，(Chr(13) + Chr(10)。为了您的安全，请只打开来源可靠的网址 打开网站取消来自: /cool%5Fliqing第九招：鲁班神尺 (Len函数) 此招用来对单元格内容的长度进行测量，得出其长度后，再做相应的处理。 使用语法 LEN(text) Text 是要查找其长度的文本。空格将作为字符进行计数。 应用示例： 详细解释 公式“=Len(A2)”中A2表示要查找长度的数据为A2单元格的内容“*19851211*”，系统测量后，返回长度“18”。 好了，我们己经学完了“瞎子摸象”和“鲁班神尺”，再加上上次所学的三招（留头去尾Left函数，去头留尾Right函数和掐头去尾Mid函数），对一些文本的处理，我们就能将其玩弄与股掌之上。 下面我们举一个例子，做一次实际操作。假如你有一堆公司员工的身份证号码登记表，但却没有单独的员工出生年月日这一栏，而偏偏员工的生日资料你很需要。一个一个的将其抄出来？这未免太花时间吧！别急，利用上面的几招，不出一分钟你就能够将其“生产”出来。 第一步的思路，就是利用“掐头去尾”函数，把身份证号码的前后内容去掉，留下中间的出生日期。但因为身份证号有两种长度（15位及18位），直接套用，一定会截错。于是，我们得加上“鲁班神尺”，先量出其长度，再加上“左右逢源”这招，对不同的长度号码，我们做不同的截取，问题就解决了。上图中，两种颜色的数据长度是不一致的，但利用公式，我们很简单的把生日数据截出来了。公式“=IF(LEN(A2)=15,19&MID(A2,7,6),MID(A2,7,8)”中，我们利用IF函数，用Len函数对A2的长度进行判断，如果等于15，则返回“19&MID(A2,7,6)”，表示如果为15位的身份证号码，就在其之前补上“19”（注：“”符号在Excel中，用来把两个数据合并在一起），然后对A2单元格中的数据从第7位开始，截6位出来，合在一起刚好8位。如果不是15位，则返回“MID(A2,7,8)”，表示直接在A2单元格的数据中，从第7位开始，截取8位出来。做完第一个公式后，不管下面还有几千或几万个数据，一拖到底即可。 对于要求比较简单的用户，得到这个结果己经够用了。但实际上，这个取出来的数据，并不是日期格式的。因此，就无法像对待日期那样处理它，如更改日期格式，或设置条件格式化，让当天为生日的数据显示为红色等。 要让其变为日期，其实