08.25 一帖玩转有趣而强大的Cell函数

导读：

①初识CELL，忽略隐藏列统计求和。

②进阶CELL，利用CELL函数制作具有模糊查询效果的数据有效性下拉菜单

③妙用CELL，快速将总表数据动态拆分到各个分表，函数也可以！

---

（一）

CELL函数属于信息类函数，返回有关单元格的格式、位置或内容等信息，一般情况下应用比较少。当然啦，用的情况少，不代表不实用，更不代表木有 用；正所谓物以稀为贵，发以疏为珍，所以越是稀疏越是珍贵（某语）——这期我们就通过几个实例，学习下有趣而强大的CELL函数。

语法：CELL(info_type, [reference])

· ● Info_type 必需。 一个文本值，指定要返回的单元格信息的类型。 下面的列表显示了 Info_type 参数的可能值及相应的结果。

· ● Reference 可选。 需要其相关信息的单元格。 如果省略，则将 Info_type 参数中指定的信息返回给最后更改的单元格。 如果参数 reference 是某一单元格区域，则函数 CELL 只将该信息返回给该区域左上角的单元格。

——以上无聊无趣头大头疼的信息引用自Excel帮助文件，仅供伙伴们参考，大清早的可以纠结蛋黄吃不吃，但真不必纠结上述语法说明，看不懂咱就不甭看了；下面咱们通过一个小例子，忽略隐藏列统计求和，对CELL函数稍加了解。

如上图，是一份某星球百强公司的人员信息表。B列、E列是隐藏的，现在需要忽略掉隐藏列，对每个人员的成绩在H列求和。

有朋友想，可以用SUBTOTAL函数——抱歉，SUBTOTAL函数只对隐藏行求和有效。

可能也有人说了，这题儿也忒……简单了！H2输入公式=C2 D2 F2 G2，向下填充就好了。

——这是赤果果的逼俺发大招啊！

打个响指，假设数据有七百多列，主观性的隐藏了其中的一百多列，同时隐藏列是动态的，今天隐藏这几列，明天隐藏那几列，那可如何是好呢？ 哼！~

---

步骤1：

A10单元格输入公式，并横向填充至G10单元格。

=CELL("width",A1)

Cell是单元格的意思，Width是宽度的意思，这个公式的意思就是获取A1单元格的列宽。当列隐藏时，列宽为0。以此判断A:G列是否处于隐藏状态。

---

步骤2：

H2单元格输入公式，并向下填充至H9单元格。

=SUMIF($B$10

G$10,">0",B2:G2)

这是一个简单的条件求和函数，通过判断B10:G10单元格区域的值是否大于0，来对B2:G2区域的值求和，需要注意的是SUMIF第一参数，即条件范围需绝对引用。结果如下：

此时想必会有好学聪慧的小伙伴问，能不能用一个数组公式得出H列的结果？

比如在H2输入公式:

=SUMPRODUCT((CELL("width",B1:G1)>0)*B2:G2)

答案是否定的，上述公式的结果为错误值。

至于原因，开篇的CELL语法里，有这么一句说明：如果参数 reference 是某一单元格区域，则函数 CELL 只将该信息返回给该区域左上角的单元格。。。。。

暖心小贴士：

· ● 在计算列宽时，CELL函数采用的是四舍五入后取整的计算方式，假如列宽窄细到0.49及以下，则CELL函数计算结果为0，列宽为0.5，则计算结果为 1。——意思是，CELL函数计算结果为0时，并不都是隐藏列的情况，当然，这种情况很少见。怎么个少见法呢？就是那个六小龄童无缘春晚惹众怒 然并卵春晚好评如潮差评为0的奇闻异事般的少见。。。。

· ● 通常只有当单元格的值属性发生改变或者使用【F9】快捷键进行公式重算时，工作表函数才会重新计算，所以列宽的改变并不会造成CELL函数的自动重算。 ——举例来说，当你把上图中的D列隐藏，H列CELL函数的结果并不会发生改变，解决方法，看我手指的方向——→请参见本段第一句话。

· 下一节咱们就继续讲CELL函数——利用CELL函数制作具有模糊查询效果的数据有效性下拉菜单。比如动画酱紫样子：

---

（二）

利用Excel的【数据有效性】功能制作下拉菜单应是表哥表姐耳熟能详的一个技能了。

这节咱们讲的内容是也和数据有效性有关，利用CELL函数，制作具有模糊查询效果的动态下拉菜单。

效果参见上面的动画图。

---

1 目标

在A列某区域输入某个关键词，在数据有效性的下拉菜单里，显示出包含该关键词的数据。

2 材料

一份名单表。如下图D2

10单元格区域，是六个男人三个女人的人名儿。

---

步骤1：

使用组合键<ctrl>，在E2单元格输入数组公式，并向下填充到E10区域：/<ctrl>

=INDEX(D

,SMALL(IF(ISNUMBER(FIND(CELL("contents"),D$2

$10)),ROW($2

10),4^8),ROW(A1)))&""

这公式看起来很复杂的模样，长长的像老太太的裹脚布，但倘若您学习并掌握了我们之前推送的INDEX SMALL IF函数套路的文章（学函数怎能不会INDEX SMALL套路），理解起来就简单多了。

●CELL("contents")

CELL函数省略了第二参数，获得最后更改单元格的值。

●FIND(CELL("contents"),D$2

$10)

FIND函数查询CELL函数的结果，是否在D2

10单元格区域存在，如存在则返回一个位置数值，相反则返回错误值，生成一个内存数组，比如：{#VALUE!;4;4;2;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

●IF(ISNUMBER(FIND(CELL("contents"),D$2

$10)),ROW($2

10),4^8)

ISNUMBER函数判断FIND函数的结果是否为数值，如为数值，则IF函数判断为真，返回相关值所对应的行号，如否，则返回值4^8，即65536。

●=INDEX(D

,SMALL(IF(ISNUMBER(FIND(CELL("contents"),D$2

$10)),ROW($2

10),4^8),ROW(A1)))&""

SMALL函数对IF函数的结果进行从小到大取数，随着公式的向下填充，依次提取第1、2、3、4……N个最小值，由此依次得到符合条件——包含最后更改单元格值的单元格的行号。

INDEX函数根据SMALL函数返回的索引值，得出结果。

当 SMALL函数所得到的结果为4^8，即65536时，意味着符合条件的行号已经被取之殆尽了。此时INDEX函数将返回D65536单元格的值，通常来 说，这么大行号的单元格是空白单元格，使用&“”的方式，规避空白单元格返回零值的问题，使之返回假空：””。

●由于CELL("contents")，得到的是最后更改单元格的值，而编辑上述公式时的单元格即为最后更改内容的单元格，此时会造成循环引用，但不必理会。公式填充至E10单元格后结果如下：

---

制作过程（二）

选 取设置下拉菜单的单元格区域A2:A9，点击【数据】选项卡中的【数据验证】（10版本之前名为【数据有效性】），在弹出的数据验证对话框中，单击【设 计】选项卡【允许】输入框右侧的按钮，在下拉列表中选择【序列】；单击【来源】右侧的选取按钮选择工作表的数据区域：$E$2

E$10。

单击【出错警告】选项卡，去掉【输入无效数据时显示出错警告(S)】的对勾。

最后【确定】之，便大功告成了。

结束语：

挠 头……那个……对于大部分表弟表妹来说，这节分享的内容难度系数确实有些偏高，想必有不少人云里雾里的，难点之处在于那条长长的裹脚布函数。关于 INDEX SMALL IF的函数套路，坊间里戏称万金油套路，是熟通Excel函数必会的套路之一，由此可见它的强大，建议尽量掌握这个套路学函数怎能不会INDEX SMALL套路

下节我们分享利用Excel函数快速将总表信息拆分到各个分表中，效果请看动画(点击下图动起来）：

---

（三）

亲爱的表亲，都好：

又到了学习函数的时间了，这节咱们继续唠叨CELL函数

---

1 动态获取工作表名称

打开一个Excel工作薄，在某个单元格里输入公式：=CELL("filename",A1)，会返回一串字符串，比如

D:\\学习CELL函数\\[filename.xlsx]总表

其中，“学习CELL函数”是文件夹的名称；“[filename.xlsx]”是工作薄的名称和类型；“总表”是A1单元格所在工作表的名称。

如果我们要单独获取工作表的名称，比如此处的“总表”，可以使用文本函数对CELL函数的结果进行加工处理。

①=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99)

或：

②=TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT(" ",50)),50))

---

①是MID FIND的函数套路。

FIND函数查询字符"]"在字符串中的位置并加1，MID函数在此结果上开始取数，取99个数，99是一个很大的数，此处也可以是66、88等，只要超过了预想字符串的长度，改成250或者25也是可以的。

②是TRIM SUBSTITUTE的套路。

这也是一个常用的文本处理的函数套路，为了降霾环保节约字符等，此处就不再赘述了。

好啦，这是我们今天分享的第一个知识，利用CELL函数动态获取工作表名称。有朋友说了，工作表名就在那，我一眼就看的到，你用函数算计它干哈子嘛？做人可不要太宫心计……

然并卵然并卵（俺小学语文老师说过，双重否定代表肯定）这当然是有意义的。

---

2 批量拆分数据

举个简单的栗子。

有 这么一份表格，如下图，是某星球百强公司的人员信息表，现要根据性别把相关人员的信息批量填充到分表里，比如女生填写到女生表，妖人填写到妖人表等，而当 总表里的信息发生改变，比如某人的性别由男生变为女生（呵呵，传说中的泰国七日游。），或者有新增数据时，分表里的数据也发生相应的改变。

●批量选取需要拆分数据的工作表

左键选取【男生】表，按住Shift键，再选取【人妖】表，最后松开Shift键。此时除【总表】外的分表会成为一个组合工作表，每个分表均处于选中状态。

●输入数组公式，拆分数据

选取组合工作表中的任意工作表的A2单元格，比如男生表的A2单元格，使用组合键<ctrl>，输入下方的数组公式，向下向右复制填充到A2:B50区域。/<ctrl>

=INDEX(总表!B:B,SMALL(IF(总表!$C$2

C$13=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99),ROW($2

13),4^8),ROW(A1)))&""

太阳照，长城长——嗨，那谁，你看，这个长长的老太太裹脚布函数又来了，又是INDEX SMALL IF的函数套路：INDEX SMALL教程贴。上期咱可就说过，这是一个万金油套路，是使用EXCEL函数必会的套路之一，也不知道亲后来学了未？

还是简单说下这个公式的意思。

MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99)

获取A1单元格所在工作表的表名。需要说明的是，此处CELL("filename",A1) 第二参数A1是不能省略的，倘若省略，获取的将是最后更改单元格所在工作表的表名，会导致公式得出错误结果。

IF函数判断总表的C2:C13区域的值是否和相应工作表表名相等，如果相等则返回C列值对应的行号，否则返回4^8，结果得到一个内存数组。

SMALL函数对IF函数的结果进行从小到大取数，随着公式的向下填充，依次提取第1、2、3、4……N个最小值，由此依次得到符合条件——性别和公式所在工作表的名称一致的单元格的行号。

INDEX函数根据SMALL函数返回的索引值，得出结果。

当SMALL函数所得到的结果为4^8，即65536时，意味着符合条件的行号已经被取之殆尽了。此时INDEX函数将返回B65536单元格的值，通常来说，这么大行号的单元格是空白单元格，使用&“”的方式，规避空白单元格返回零值的问题，使之返回假空。

●取消组合工作表状态

公式填写完成后，左键单击不属于组合工作表的【总表】，Excel会自动取消组合工作表状态，也可以右键单击组合工作表中的任一工作表，在弹出的右键菜单中，单击【取消组合工作表】。

至此根据工作表名称批量拆分总表数据的操作也算是完成了；当总表的数据发生改变时，分表的数据也会随之改变，耳听为虚，口说无凭，那咱就眼见为实呗：

---

暖心小贴士：

利用数据透视表的【显示报表筛选页】功能或者VBA编程等也可以达到快速拆分数据的目的，但三者之间在可操作性、可接受性、动态性、适用性上是有蛮多区别的。——关于第一种方式，请参照链接：透视表技巧。关于第二种方式，我们稍后再分享。