行政人事管理工作常用EXCEL函数教程

文本函数的应用,,,,,,,,

1、TEXT函数,,,,,,,,

用途：,依据需要将目标数字转换为指定的文本格式,,,,,,,

语法：,"TEXT(value,format_text)",,,value为目标数值，可以为数字，也可以为计算结果。format_text为读者需要设定的文本格式。,,,,

,"该格式设置方式：按<Ctrl+1>组合键弹出""单元格格式""对话框，单击""数字""选项卡，在""分类""列表框中选中文本形式的数字格式，最后单击""确定""按钮即可。",,,,,,,

简单示例：,,,,,,,,

,工业,45%,,运算结果：,工业占总GDP比重45%,,,

,服务业,1000亿,,,服务业的GDP为1000亿,,,

,公式,,,,,说明（结果）,,

,=,"B7&""占总GDP比重""&TEXT(C7,""0%"")",,,,将B7和C7单元格里的内容以及引号间的内容合并为一句（工业占总GDP比重的45%）,,

,=,"B8&""的GDP为""&TEXT(C8,"""")",,,,将B8和C8单元格里的内容以及引号间的内容合并为一句（服务业的GDP为1000亿）,,

,,,,,,,,,

TEXT函数的应用：,,结合TEXT函数舍入计算加班时间,,,,,例：P109,

2、MID函数,,,,,,,,

用途：,在目标字符串中指定一个开始位置，按设定的数值返回该字符串中的相应数目字符内容。,,,,,,,

语法：,"MID(text,start_num,num_chars)",,,,,,,

,text是目标字符串,,,,,,,

,start_num是字符串中开始的位置。通常start_num是从字符串的首个字符编号，依次为1、2、3…start_num有取值范围，不能大于字符串的长度，不能小于1。,,,,,,,

,num_chars指设定的数目，MID函数将按此数目返回相应的字符个数。显然该数值不为负，否则函数将输出错误值。,,,,,,,

简单示例：,,,,,,,,

,wonderful,,,运算结果：,wonde,erful,,#VALUE!

,公式,,,说明（结果）,,,,

,=,"MID(B22,1,5)",,提取B22单元格里字符串中的5个字符，且从第一个字符开始提取（wonde）,,,,

,=,"MID(B22,5,20)",,"提取B22单元格里字符串中的20个字符，且从第5个字符开始提取。因为B22单元格中的字符串

中总字符数是9，所以最多只能从第5个字符开始，将余下的所有字符都提取（erful)",,,,

,,,,,,,,,

,=,"MID(B22,10,2)",,因为B22单元格中的字符串中总字符数是9，此时要从第10个字符开始提取，所以系统返回空格（）,,,,

,=,"MID(B22,0,2)",,因为start_num小于1，所以系统返回错误值（#VALUE！）,,,,

,,,,,,,,,

3、LEN函数,,,,,,,,

用途：,计算目标字符中的字符数,,,,,,,

语法：,LEN(text),,text是目标字符串,,,,,

说明：,空格也视为字符，加到字符串的字符数中,,,,,,,

简单示例：,,wonderful,,Excelword,,运算结果：,,

,公式,,,说明（结果）,,,,

,=,LEN(C34),,返回C34单元格里字符串的长度（9）,,,,

,=,LEN(D34),,返回D34单元格里字符串的长度（10）,,,,

▲,应用TEXT结合MID、LEN函数从身份证号中提出生日信息,,,,,,例：P232,

4、LEFT函数,,,,,,,,

用途：,从字符串的左侧开始，按照指定的数值返回相应数目的字符内容。,,,,,,,

语法：,"LEFT(text,num_chars)",,,,,,,

,text是目标字符串，num_chars是指定的数值，该数值必须大于或等于0,,,,,,,

简单示例：,,,,,,,,

,wonderful,,,运算结果：,wond,w,wonderful,

,公式,,,说明（结果）,,,,

,=,LEFT（B43，4）,,对A3单元格里的字符串，按从左到右提取前4位字符（wond),,,,

,=,LEFT（B43）,,因为省略了num_chars，因此系统假定其为1，将提取A3单元格里字符串左侧的首字符（W）,,,,

,=,LEFT（B43，11）,,因为指定数值大于目标字符的总数，所以系统将提取目标字符的所有字符（wonderful),,,,

,,,,,,,,,

5、RIGHT函数,,,,,,,,

用途：,从字符串的右侧开始，按指定的数值返回相应数目的字符内容。该函数恰好与LEFT函数的功能相反。,,,,,,,

语法：,"RIGHT(text,num_chars)",,,,,,,

,text是目标字符串，num_chars是指定的数值，该数值必须大于或等于0,,,,,,,

简单示例：,,,,,,,,

,wonderful,,,运算结果：,derful,,l,

,公式,,,说明（结果）,,,,

,=,"RIGHT(B53,6)",,对B53单元格里的字符串，按从右到左提取6位字符(derful),,,,

,=,RIGHT(B53),,因为省略了num_chars，因此系统假定其为1，将提取A3单元格里字符串右侧的首字符（1）,,,,

,,,,,,,,,

6、FIND函数,,,,,,,,

用途：,查找字符串中首字符编号为1，接着对余下的字符按顺序依次进行编号，然后在查找字符串中查找目标字符，并且返回目标字符在查找字符串中的编号。,,,,,,,

语法：,"FIND(find_text,within_text,start_num)",,,,,,,

,find_text是目标字符。,,,,,,,

,within_text是查找字符串。,,,,,,,

,start_num是查找字符串中开始查找的起始位置。,,,,,,,

,Start_num有取值范围，要大于0，同时又不能大于查找字符串的长度。,,,,,,,

说明：,FIND函数区分大小写。,,,,,,,

简单示例：,,,,,,,,

,subsititute,,,运算结果：,2,#VALUE!,#VALUE!,9

,公式,,,,说明（结果）,,,

,=,"FIND(""u"",B65)",,,"因为忽略了start_num,故而系统假设start_num为1，所以查找的是字符串里第一个""u""的位置(2)",,,

,=,"FIND(""u"",B65,0)",,,因为start_num为0，所以系统会显示错误值（#VALUE!),,,

,=,"FIND(""A"",B65,3)",,,因为目标字符在查找字符串中没有出现，所以FIND函数会显示错误值(#VALUE!),,,

,=,"FIND(""u"",B65,5)",,,"从字符串的第5个字符开始查找第一个""u""的位置（9）",,,

,,,,,,,,,

7、SUBSTITUTE函数,,,,,,,,

用途：,将目标字符或字符串替换到需要被替换的字符串中。,,,,,,,

语法：,"SUBSTITUTE(text,old_text,new_text,instance_num)",,,,,,,

,text为存放需要被替换的字符串的单元格位置。,,,,,,,

,old_text为需要被替换的原字符或字符串。,,,,,,,

,new_text是用于替换的目标字符或字符串。,,,,,,,

,instance_num指明替换字符串中第几个字符或字符串。若不指明，SUBSTITUTE函数将会替换所有的符合条件的原字符或字符串。,,,,,,,

简单示例：,,,,,,,,

,清远北江清远,,,运算结果：,英德北江清远,,英德北江英德,