有时候,我们在要一些记录里,找出所需要的信息,但是这些记录并没有一个统一的记录格式要求,记录人员在输入记录的时候都按照自己的样式写,对我们提取关键信息带来麻烦。
例如,有下面一些地址描述的单元格:

假设我们要统计这些地址,分别统计这8个城市(北京,上海,广州,深圳,重庆,南京,天津,成都)中各自有几个,不在这8个城市中的,归为“其它”。
在数量少的时候,我们可以用人工看出,北京2个,天津1个,广州2个,深圳0个,上海1个,南京1个,重庆1个,成都0个,其它2个。但数据量大的时候如何用Excel函数帮助统计呢?
这种统计我们知道用countif或者countifs函数就可以了,但是由于记录复杂,不能直接套用函数,先要清洗一下,否则数据就不准确,例如,广州市的北京路,因为有北京字眼,直接套用函数的话,但会被统计在了北京中。因此,初步思路是,先作一个辅助列,把记录清洗出城市名称,然后再countif函数统计即可。
首先,我们需要查找的8个城市名称可以列为一个数组,为了说明方便,这里我们暂且用{城市名称}代替。
在大多数记录中,都是可以用find({城市名称},记录单元格)得出城市名称的位置。但是有些在我们需要的城市名称前,多出了一些包含无用城市名称的前缀(例如A5和A11)。还好,我们找到“地址:”这个关键词,在它的后面,才是真正的地址,我们在查找城市名称时,只用从“地址:”这个词开始查找就行。
这里以A2为例,可以得出以下公式:FIND({城市名称},A2,FIND(“地址:”,A2)),但如果记录里没有“地址:”这个字眼呢?那就可以用IFERROR来把是否含有“地址:”字眼认识出来:IFERROR(FIND(“地址:”,A3),1),这里的意思是,如果找到“地址:”字眼就返回它的值位置,从这个位置值开始查找城市名称。如果找不到“地址:”,就返回1,从记录的开头开始找城市名称,于是得到这样的公式:FIND({城市名称},A2,IFERROR(FIND(“地址:”,A2),1))。
但是毕竟{城市名称}是一个数组,那如何确认我们所找到的那个城市名称到底是哪一个呢?而且,如果记录里,即使在“地址:”字眼后,也是有多个城市的名称呢,那怎么办?这里我们就用IFERROR和MIN函数。首先,我们就用IFERROR函数,把上面得到的公式得到的结果数组再进行一次识别,不能找到城市名称的,赋予一个很大的值,这个值只要大于最长记录的字符数即可(我们这里假设为999),然后再用MIN从结果数组里找出最小值,那么,这个值就是单元格里,最前面的(或者是紧跟“地址:”字眼的)城市名称的位置。然后再用MID函数,把此位置后的2个字符取出,就能返回里面的名称了,如果里面有{城市名称}里的城市,就会返回城市,没有就会返回(空白)。加个if函数,把(空白)改成“其它”。公式就变成:=IF(MID(A2,MIN(IFERROR(FIND({城市名称},A2,IFERROR(FIND(“地址:”,A2),1)),999)),2)=””,”其它”,MID(A2,MIN(IFERROR(FIND({城市名称},A2,IFERROR(FIND(“地址:”,A2),1)),999)),2))。

最后,在新列中把数据Countif出来就行了。
