在目标单元格字段中,找出指定的,可能出现的,某些字符。

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

例如,有下面一些地址描述的单元格:

地址记录

假设我们要统计这些地址,分别统计这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出来就行了。

 

统计

sumif与sumifs

sumif是指选择性指定满足某些特定条件的数值之和,它与sumifs的差别为,前者的选择条件只有一个,而后者是可以多个。

sumif的函数格式是:sumif(筛选条件所在的区域,需要满足的条件,对应需相加值所在的列)

sumifs的函数格式是:sumifs(对应需相加值所在的列,筛选条件1所在的区域,条件1,筛选条件2所在的区域,条件2,。。。)

注意:sumif的需相加值区域是放在最后面的参数,而sumifs的需相加值区域则是放在最前面。

例子:在一项家庭记账活动中,可导出某月份的流水帐:

现在,要统计“食品酒水”分类的支出总和。那么可用sumif,函数为:sumif(C:C,“食品酒水”,H:H)

如果要统计“出外就餐”子分类中,使用“招商银行”帐户消费的总和,那么可以用sumifs,函数为:sumifs(H:H,D:D,“出外就餐”,E:E,“招商银行”),如此类推