Execl函数中的固定单元格

Ghostzhang 发表于

经常用Execl统计一些数据,很好很强大,也很复杂,高级的功能用不上,有几个场景是经常会用到的,比如考勤。

原来没有这篇的,不过今天发现了一个很方便的技巧,其实网上能找到,只是一直不知道有这个功能,也就不知道搜索的关键字。假设原始数据如下表:

  A B C
1 人员 休假类型 时间
2 userA 年假 2
3 userB 事假 0.5
4 userA 年假 1.5
2 userA 事假 1

时常会遇到这样的需求,统计对应人的某一个状态的数量。

  A B C D
1 人员 年假 事假 总次数
2 userA      
3 userB      

这个时候可以使用『COUNTIFS』函数,像这样: =COUNTIFS(检查范围1,条件1,检查范围2,条件2) 比如B2单元格是要统计userA这个人休了多少次年假,可以在B2单元格里这样写:

=COUNTIFS(原始数据!A:A,A1,原始数据!B:B,B1)

这样就完成了一个单元格的定义。问题从这里才开始,填充一个单元格很容易,后面还有很多呢。用过execl的同学应该知道,有个很方便的功能,选中单元格之后右下角会有一个控制点,直接拖动可以快速智能填充,我们来试下,比如拖动填充了B3单元格,内容如下:

=COUNTIFS(原始数据!A:A,A2,原始数据!B:B,B2)

再填充C2单元格,内容如下:

=COUNTIFS(原始数据!B:B,B1,原始数据!C:C,C1)

很明显并不是我们希望的值。我的方法比在execl上改要稍稍高效一点,就是用文本编辑器先写好再复制粘贴到对应的单元格里:

=COUNTIFS(原始数据!A:A,A1,原始数据!B:B,B1) //B2
=COUNTIFS(原始数据!A:A,A2,原始数据!B:B,B1) //B3
=SUM(B2,B3) //B4
=COUNTIFS(原始数据!A:A,A1,原始数据!B:B,C1) //C2
=COUNTIFS(原始数据!A:A,A2,原始数据!B:B,C1) //C3
=SUM(C2,C3) //C4

原本已经觉得很不错了,但还是不太方便,本着一懒到底的精神,甚至有想说写一个小工具。直到今天,在用Numbers的时候,发现它在定义函数的时候可以选『保留行』或『保留列』,可以很方便的把函数中的参数固定:

Numbers函数编辑

这样就不会因为自动填充而被改变了,才知道原来可以这样简单,只是因为之前一直不知道。于是又找了下execl下有没类似的东西,结果在mac版的execl下有同样的功能,不过体验差了很多:

mac execl函数编辑

windows版的没有找到,但功能是支持的,同样只需要在要保留的行或列前面加上一个美元符号 $ 即可。于是只要这样:

=COUNTIFS(原始数据!$A:$A,$A1,原始数据!$B:$B,B$1)

然后使用自动填充的功能就可以了,简单快捷,再也不用编辑器一个个改了,感激涕零。

评论