Tag Archives: excel

the Power of Regex

Today I solved a problem during my work in help of vim and regex. Take a note here, in case I forget it someday.

There are two files(excel A and word B) for me to figure out whether the names in word B are also existed in excel A. Excel A and word B look like this:

Excel A:

From Drop Box

Word B:

From Drop Box

First of all, if I want to compare the two files, I should copy the names in word B to excel A and use the function vlookup, this suppose to be simple, so I don’t want to talk about this part. The main problem here is how to transform the names in word B to some other format, each name in a row actually. So, the objective is set, let’s begin to solve it.

  1. Copy the entire word B to gvim C, because vim is truly a word editor and it is where the regex can take full advantage of.
  2. There are two space between each name, we should turn them to one. It’s time for regex to roll out.

    :%s/ss/ /gc

    “s” means match a white space character, exactly a space and a tab.

  3. And some names contain two character, but they also have a space between them, we should eliminate these space.

    :%s/s(S)s/ 1/gc

    “S” means match anything that isn’t a space and a tab and “(…)” means making matched subtexts available in the replacement part of a substitute command, such as “1”.

  4. Replace every space to a carriage return, this one seems a piece of cake.

    :%s/s/r/gc

    “r” means a carriage return.

  5. Copy the entire contexts to excel A, and transfer the job to function vlookup. The contexts should look like this:
  6. From Drop Box

If the excel A and word B are some big files, we’ll save great amount of time using regex. In my case, they really are. And I think I did my job nice and easy.

关于excel–浅谈vlookup函数

vlookup算是个相当好用的excel函数,详细的说明在office的帮助文件里都有,我这里只是简单谈谈。

首先,这个函数派什么用处呢?ok,举例说明。
X校的学生总数在10000人左右。A君手上有一张拟追求的X校女生列表,人数为100,但只有名字没有具体的联系电话。于是某日A君侵入X校数据库获得了所有的学员资料,这时候如果A君根据姓名去一一查找电话号码,那实在是个令人吐血的笨办法。我们这时就可以通过vlookup函数来解决。

X校学生的资料表(美女永远是稀缺资料,~~T_T~~),记做表1
列A 列B 列C
行1 姓名 电话 地址
行2 美女883 54555454533 fgggfhrty
行3 丑女446 89034586743 ytytyfgf
行4 猥琐男887 34989584545 opikjhk
行5 伪娘169 15377676767 gqsdadsfa
行6 人妖774 98547573455 stytrdsfa
行7 美女034 87684334434 vbhyrsakl
行8 猛男938 06554364554 yrwesdv
行9 衰男657 05488629043 piogfgsd
行10 平庸女656 42425656677 kczserod
行11 平庸女023 65476253454 baqerpob
行12 平庸女710 2349757567 zsdvmklt
A君手上的列表,记做表2
列A 列B 列C
行1 姓名 电话 地址
行2 美女883 未知 未知
行3 美女756 未知 未知
行4 美女845 未知 未知
行5 美女255 未知 未知
行6 美女645 未知 未知
行7 美女034 未知 未知
行8 美女267 未知 未知

我们的问题在于:表2中的众多的未知项需要去表1中匹配。

先简单解释下vlookup:在表格的首列查找指定的数值,并由此返回表格当前行中指定列处的数值。

再看语法构:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  • Lookup_value :  需查找的哪个数值?
  • Table_array  :  在哪个表格中查找该数值?
  • Col_index_num : 在表格中查到该数值后将该数值所在行的哪一列数值返回回来?
  • range_lookup : (可选项)如果 range_lookup 为 TRUE,则 table_array 的第一列中的数值必须按升序排列:…、-2、-1、0、1、2、…、-Z、FALSE、TRUE;否则,函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE,table_array 不必进行排序。

好了,至此你应该晕菜了,所以还是实践一下。先将两张表按顺序置于同一个excel工作表中,然后在表2的第一个未知项处(列B行2),输入“=VLOOKUP(A17,A3:B13,2,FALSE)”,如下图红框部分所示:

From Drop Box

A17表示我要找的是A17单元格,即“美女883”;

A3:B13表示我要在单元格A3到B13的范围内查找“美女883”,而从图中可以看出A3:B13即是表1的第一列和第二列;

2表示如果在表1中找到美女883,则返回A3:B13中美女883所在行的第2列,即A3:B13中的B列,也就是表1中的“电话”列。

FALSE表示A3:B13(表1)不需要进行排序。(这一点很搞,我最后会提到。

举一反三,美女883的地址如何自动匹配呢?答案刮开后查看:=VLOOKUP(A17,A3:C13,3,FALSE)

接下来,依样卖葫芦,就能将表2的未知项完全匹配上。数据量小或许没什么,但如果这两张表都是成千上万的大表,那vlookup函数的高效就能越发地体现。


然后谈一谈关于这个函数我认为不合理的一个设置。range_lookup的默认值是“TRUE”,因为这是可选项,新手一般都会忽略。而我们用到的查找表很少是排序过的,即使排序可未必是按第一列升序排列,如果忘了填这个参数的话。。。造成的结果就是匹配混乱!大家可以试一试,得出的结果绝对是乱七八糟的。知道了这一点,相信没人会使用range_lookup的默认值,那么这样的设置又有什么用呢?再翻翻文档,发现关于这个参数还有一种说明:

Range_lookup    为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。

这样看来默认值“TRUE”作用是用来近似匹配的,作用当然有,可普通用户绝对是精确匹配用的多啊!所以从实用性看来,默认值应设为“Flase”才对。本来大家用个函数就不容易,你这么瞎搞,把结果弄得一滩糊涂,这不是变相的吓退用户吗?