最近一两个月,处理了大量的Excel表格,具体的目标包括筛选出包含特定字符串的行,查找某个值是否在表中存在等。工作中积累了大量使用Excel的经验,想记下来,却一直没有时间。今天促成动笔因素的还是因为帮同事小Z处理了一份Excel文档。想来有趣,每每帮她做事,我都要赶快记下来,看来她是我写Blog的动力源,可惜我就要离开她去别的办公地点了。
她的问题是这样的:她维护的一个Excel表格保存全部员工参加每个产品培训的开始和结束时间,她以前的记录方法是“07/9/3-9/8”,表示某门课是2007年9月3日到9月8日,现在根据系统的需要,她必须把记录改成“2007/9/3-2007/9/8”的形式,她要改掉的记录占了几万个单元格,一个个修改是不现实的,我必须帮她找出个尽量快的方法。
部分数据示例如下:
A | B | C | D | |
1 | 姓名 | DC8000 | DT120 | DCC3000 |
2 | 吴云 | 06/5/4-5/17 | 07/3/3-3/15 | 07/7/6-7/11 |
3 | 王志军 | 05/4/2-/4/16 | 06/10/8-10/18 | 06/11/4-/11/9 |
4 | 张明亮 | 06/11/3-11/15 | ||
5 | 李向东 | 07/5/10-5/18 | 07/1/4-1/9 | |
6 | 谢伟伦 | 07/3/5-3/18 | 07/6/14-6/19 | |
7 | … | … | … | … |
我第一步先做了个预处理工作,用Edit菜单的Replace命令把“06/”全部替换成“2006/”,把“07/”全部替换成“2007/”等等。
现在任务变成“2007/3/3-3/15”要转换成“2007/3/3-2007/3/15”。
我把每个产品培训的列复制了一下插在该产品列后面,一个是旧的字符串,一个准备存放转换后的新字符串。以B5为例,C栏现在是B栏的复制,在C5输入以下公式:
=REPLACE(B5,FIND("-",B5),1,CONCATENATE("-",LEFT(B5,5)))
我把这个公式解释一下。原来的记录方式在“-”后面缺少年份,“-”是个标志位,要在后面插入跟前面部分相同的年份(暂不考虑跨年度的培训)。“-”标志位的查找是用FIND函数,得出它在字符串中的位置。年份的提取是用LEFT函数,因为年份出现在字符串的最左边,我要的结果是“2007/”,所以从最左边数了5个字符过来。我没有在Excel的字符操作函数里找到有插入功能的函数,就用REPLACE函数来达到相同效果。把“-”替换为“-2007/”,“-2007/”则是用CONCATENATE函数把“-”和LEFT函数返回值结合在一起生成的。
具体的函数设计好后,用Excel的自动填充功能把函数在相关单元格填满就好啦。
REPLACE,FIND,CONCATENATE和LEFT函数的语法还请到MS Excel帮助文件里去查看。