寻求谷歌表格Google Sheet Array Formula 解决B、C列公式自动生效方案

解决方案 #

B列数组公式（写在B2单元格即可自动向下生效） #

你原先拖拽生效的B列普通公式可以直接改造为数组版本，不用逐行填充：
=BYROW(A2:A, LAMBDA(x, IF(x="",, 把你原B列的公式逻辑放到这里)))

原理：用BYROW逐行迭代A列的导入项目，只有对应行A列有有效内容时才执行计算，空行自动返回空值，中间新增行时会自动识别A列的新内容触发计算。
如果你的B列需要生成连续序号，可直接使用现成公式：
=BYROW(A2:A, LAMBDA(x, IF(x="",, COUNTA(A$2:x))))

C列数组公式（写在C2单元格即可自动向下生效，同步实现排除D列已选项目逻辑） #

直接使用下方公式即可满足需求：
=BYROW(A2:A, LAMBDA(x, IF(x="",, FILTER(Sheet2!A:A, NOT(COUNTIF(D:D, Sheet2!A:A)), Sheet2!A:A<>""))))

逻辑说明：

• 逐行判断A列是否有值，空行直接返回空避免冗余内容
• 用FILTER筛选Sheet2的项目列表，同时排除两类内容：已经在D列被选中的项目、Sheet2中的空值
• 后续D列选中新项目、中间新增行时，所有行C列的可选列表都会同步更新，无需手动调整

注意事项 #

• 新版Google Sheets写完公式直接回车即可自动溢出填充，旧版需要按Ctrl+Shift+Enter确认数组公式
• 如果你的表头不在第1行、数据起始行不是第2行，把公式中对应的行号修改即可
• 需要限定公式生效范围的话，把公式里的A2:A这类整列范围改成A2:A1000这类固定区间，可减少不必要的性能消耗

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