Cell函数的另类应用

标签：Excel函数与公式，CELL函数，COUNTIF函数，SEQUENCE函数

需求

数据验证下拉列表必须包含具有货币数据类型列的列标题，如果数据类型改变，下拉列表也相应改变。

示例数据

本文学习整理自myspreadsheetlab.com，可以在该站点获得示例数据，也可以直接到知识星球完美Excel社群下载。示例数据共有20列399行。

图1

解决方案

1.提取数据类型

CELL函数从数据的第一行提取数据类型。

图2

由于CELL函数似乎不能以数组公式形式工作，因此必须逐个单元格输入公式。

公式结果中：

• 以“C”或“,”开头的意味着货币。
• 以“F”开头的意味着数字。

2.统计

在单元格C4中使用数组来统计以“C”开头的数据类型：

=COUNTIF($A$1:$T$1,C5&"*")

图3

3.计数

使用动态数组函数SEQUENCE在单元格B6中创建了一个计数器：

=SEQUENCE(C4,1,1,1)

在必需时会向下溢出。

4.列标题列表

B6#中的#用于将公式沿计数器向下溢出。无需手动向下拖动公式（它会自动扩展）。

=INDEX(‘blockbuster-top_ten_movies_per_’!$A$1:$T$1,SMALL(IF(LEFT($A$1:$T$1,1)=C$5,COLUMN(‘blockbuster-top_ten_movies_per_’!$A$1:$T$1),””),B6#))

5.添加下拉列表

在数据验证里使用OFFSET函数创建列表。CELL函数和OFFSET函数都是易失性函数。

=OFFSET($C$6,0,0,$C$4,1)

图4

至此，问题解决。

注意，如果改变了数据表中的列的数据类型，则需要按F9键重新计算。

虽然说，这个示例可能用处不大，但它给我们使用Excel公式解决问题开拓了思路。

CELL函数官方介绍链接：

https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60173&ui=en-us&rs=en-us&ad=us

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。