你已经摸到了用SUBTOTAL+OFFSET判断单元格可见性的核心逻辑，只需要再加一层简单的判断就能达成需求，直接上解决方案：

核心公式 #

假设你的目标文本区域是$F$5:$F$400，可以使用以下公式：

=IF(SUBTOTAL(3,OFFSET($F$5,ROW($F$5:$F$400)-ROW($F$5),0)),$F$5:$F$400,"")

公式解析 #

• 保留你熟悉的SUBTOTAL(3,OFFSET(...))部分：这部分会生成一个由1和0组成的数组，1对应可见单元格，0对应隐藏单元格。
• 外层嵌套IF函数：当SUBTOTAL返回1（单元格可见）时，返回对应单元格的文本值；当返回0（单元格隐藏）时，返回空字符串""，完全匹配你要的数组格式。

示例验证 #

就像你提到的场景：筛选后仅Apple、Pineapple、Banana可见，其余单元格隐藏，这个公式生成的数组就是：

{"Apple","","","","Pineapple","","Banana","",""...}

版本适配 #

• Excel 365/2021及以上：直接输入公式后按回车，会自动溢出为动态数组。
• 旧版Excel（2019及更早）：输入公式后需要按Ctrl+Shift+Enter作为数组公式确认，才能生成正确的数组结果。

内容的提问来源于stack exchange，提问作者joehua