统计重名的电子表格函数最后一个好用.docx
《统计重名的电子表格函数最后一个好用.docx》由会员分享,可在线阅读,更多相关《统计重名的电子表格函数最后一个好用.docx(12页珍藏版)》请在冰点文库上搜索。
![统计重名的电子表格函数最后一个好用.docx](https://file1.bingdoc.com/fileroot1/2023-5/16/8359fe63-76e3-4598-89df-684de9315e3d/8359fe63-76e3-4598-89df-684de9315e3d1.gif)
统计重名的电子表格函数最后一个好用
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)。
附件就不传了,具体的自己试试,玩下就知了。
再者,你将这问题XX一下也可以得到更加详细的答案。
第五招:
留头去尾(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) 转换为String
3,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打开文件:
OpenpathnameFormode[Accessaccess][lock]As[#]filenumber[Len=reclength]能够对文件输入/输出(I/O)。
pathname必要。
字符串表达式,指定文件名,该文件名可能还包括目录、文件夹及驱动器。
mode必要。
关键字,指定文件方式,有Append、Binary、Input、Output、或Random方式。
如果未指定方式,则以Random访问方式打开文件。
access可选。
关键字,说明打开的文件可以进行的操作,有Read、Write、或ReadWrite操作。
lock可选。
关键字,说明限定于其它进程打开的文件的操作,有Shared、LockRead、LockWrite、和LockReadWrite操作。
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关闭文件:
Close[filenumberlist]关闭Open语句所打开的输入/输出(I/O)文件
注意如果今后想用Input#语句读出文件的数据,就要用Write#语句而不用Print#语句将数据写入文件。
因为在使用Write#时,将数据域分界就可确保每个数据域的完整性,因此可用Input#再将数据读出来。
使用Write#还能确保任何地区的数据都被正确读出。
Write与Print#语句不同,当要将数据写入文件时,Write#语句会在项目和用来标记字符串的引号之间插入逗号。
Write#语句在将outputlist中的最后一个字符写入文件后会插入一个新行字符,即回车换行符,(Chr(13)+Chr(10))。
为了您的安全,请只打开来源可靠的网址
打开网站 取消
来自:
第九招:
鲁班神尺(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位出来。
做完第一个公式后,不管下面还有几千或几万个数据,一拖到底即可。
对于要求比较简单的用户,得到这个结果己经够用了。
但实际上,这个取出来的数据,并不是日期格式的。
因此,就无法像对待日期那样处理它,如更改日期格式,或设置条件格式化,让当天为生日的数据显示为红色等。
要让其变为日期,其实也是很简单。
只是其中一招我们还未介绍-Datevalue,同时,取出的数据,也需额外加上分隔符,让系统识别。
我先把公式列在这里,有兴趣的用户可以试试。
=DATEVALUE(IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2)))
记住:
公式得出的结果,是一个时间序列号,日期格式你们可自行设置。
下图中,C3及C5单元格的“30720,30034”就是因没设置日期格式而直接显示序列值。
下面我们再来看一个使用了“瞎子摸象”函数的例子。
假如有这样一串数据,格式类似BillGates(****)Bill.Gates@或CharlesPeng(****)Charles.Peng@,我们需要取出其中的邮件地址部分。
因为其邮件地址时长时短,因此,无法直接截取,单用Len函数也无法实现。
但根据观查发现,邮件地址起始于“)”后,因此我们可以利用“瞎子摸象”Find函数,先定出每个数据中的“)”位置,再用Len量出整个数据长度,相减之后,就是邮件地址的长度,这样,用“去头留尾”函数就可将需要的数据取出。
公式为:
=RIGHT(A2,LEN(A2)-FIND(")",A2))。
为了您的安全,请只打开来源可靠的网址
打开网站 取消
来自: