GET.CELL

GET.CELL是EXCEL早期宏表函数,可返回引用单元格的信息

简介

宏表函数GET.CELL是在早期低版本Excel中使用的函数,其作用是返回引用单元格的信息。它仍可以在高版本的工作表中使用,不过不能直接用在单元格中,而只能通过定义的名称的方式来使用。

语法

GET.CELL(Type_num, Reference)Type_num

指明单元格中信息的类型。用数字表示,范围为1-66。

Reference为引用的单元格或区域。

百度百科地址:https://baike.baidu.com/item/GET.CELL/7787662?fr=aladdin

举个例子

有时候,我们需要处理或筛选标了底色的单元格进行处理,但如何识别单元格的底色呢,可以使用下面的方法:

在名称管理器那里新建一个名称,“引用位置”的内容为:=GET.CELL(63.Sheet1!A1)

其中63,是底色(填充背景)的颜色代码。

“Sheet1!A1”是指你要获取底色号码的单元格。

例如现在我们就新建了一个“Color”的名称,接下来,我们在G1里填上“=Color”,那么G1就会显示A1的底色号码,0为没有颜色。我们向右向下填充,就得到对应区域里各单元格的底色代码。

我们也就可以使用一些函数组合,算出红色单元格里数值的和。

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

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

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

地址记录

假设我们要统计这些地址,分别统计这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,“招商银行”),如此类推