Excel 2016如何提取Sheet2数量≥1的条目生成Sheet3清单

针对Excel 2016版本不支持FILTER动态数组函数、无法直接实现筛选输出的问题，以下3种方法均可落地，可根据使用习惯选择：

方法1：高级筛选（零公式，操作效率最高） #

不需要写任何函数，适合快速出结果的场景，操作步骤：

1. 切换到Sheet2，选中包含表头在内的全部有效数据区域
2. 点击顶部菜单栏「数据」选项卡，选择「高级」筛选功能
3. 在弹出的配置窗口中按规则设置：
   • 筛选方式选择「将筛选结果复制到其他位置」
   • 条件区域配置：先在Sheet2的空白位置，第一行输入和数量列表头完全一致的文本，第二行输入筛选条件>=1，选中这两个单元格作为条件区域
   • 复制目标位置选择Sheet3中清单要放置的起始单元格（比如A1）
   • 可按需勾选「选择不重复的记录」
4. 点击「确定」即可自动把所有数量≥1的条目及对应数量完整输出到Sheet3。后续Sheet2数据更新后，重新执行一次高级筛选即可刷新结果。

方法2：INDEX+SMALL+IF 组合公式（支持自动同步更新） #

如果需要Sheet2数据修改后，Sheet3的清单自动同步，可使用全版本兼容的经典数组筛选公式：

• 提前确认数据源结构：假设Sheet2中A列为条目名称、B列为需求数量，有效数据从第2行开始，最大数据行不超过1000行（可根据实际数据范围调整参数）
• 选中Sheet3的A2单元格，输入以下公式，输入完成后必须按Ctrl+Shift+Enter三键组合确认数组公式（Excel 2016不支持直接回车确认数组公式），随后先向右拖动公式填充到B列，再向下拖动直到单元格显示空白即可：

=IFERROR(INDEX(Sheet2!A:A,SMALL(IF(Sheet2!$B$2:$B$1000>=1,ROW($2:$1000),9^9),ROW(A1))),"")

• 公式逻辑说明：
  • 先遍历Sheet2的数量列，把数量≥1的行号标记出来，不符合条件的标记为极大值9^9
  • 用SMALL函数从小到大依次提取符合条件的行号
  • 用INDEX函数根据行号提取对应列的条目/数量内容
  • 用IFERROR做容错，所有符合条件的条目提取完成后，单元格显示空白不抛错

注意：如果实际数据量超过1000行，把公式里的1000改成实际最大行号即可，不要直接引用整列，避免表格计算卡顿。

方法3：数据透视表（适合后续需要做汇总统计的场景） #

如果后续需要对条目做分类汇总，可直接用数据透视表实现筛选：

1. 选中Sheet2的全部数据源，点击「插入」选项卡下的「数据透视表」，选择放置位置为Sheet3
2. 在右侧字段配置面板，把条目名称字段拖到「行」区域，把需求数量字段拖到「值」区域，默认汇总方式设置为「求和」
3. 点击行标签旁的筛选下拉按钮，选择「值筛选」-「大于或等于」，输入阈值1后确认即可
4. 后续Sheet2数据更新后，右键点击数据透视表选择「刷新」即可同步最新清单。

场景参考截图 #

• Sheet2原始数据界面
• Sheet3目标清单效果参考

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