如何用INDEX和MATCH函数基于表头与条目填充站点商品数量汇总表

结论先行 #

INDEX+MATCH是可行的实现方案，但不一定是最优，具体最优方案要根据你的数据量、需求场景决定。

不同场景下的最优方案说明 #

• 如果你需要公式联动、数据量在1万行以内，且能确保原始表没有「同站点同商品重复记录」：INDEX+MATCH是合适的方案
  • 参考公式（假设原始数据存放在Sheet1的A:D列，汇总表A列是商品列表，第1行是站点列表，公式写在B2单元格）：
    =IFERROR(INDEX(Sheet1!$D:$D,MATCH(1,(Sheet1!$B:$B=$A2)*(UPPER(Sheet1!$A:$A)=UPPER(B$1)),0)),"")
  • 写完按Ctrl+Shift+回车触发数组运算即可（Excel 365/WPS 2021及以上版本不需要按组合键，直接回车即可生效）
• 如果你数据量超过1万行、不需要实时公式联动，或者需要快速调整统计维度：数据透视表是最优方案
  • 操作步骤：选中原始数据表任意单元格 → 点击「插入」选项卡 → 选择「数据透视表」 → 行区域拖入「ITEM」字段，列区域拖入「SITE」字段，值区域拖入「quantity」字段，一步生成所需汇总表，运算效率远高于公式方案。
• 如果原始表可能存在同站点同商品的多条记录，需要汇总求和：SUMIFS是最优方案
  • 参考公式：
    =SUMIFS(Sheet1!$D:$D,Sheet1!$B:$B,$A2,Sheet1!$A:$A,B$1)
  • 优势是Excel/WPS默认忽略大小写匹配SITE字段，多条记录自动求和，不存在匹配错误的问题，写法也比INDEX+MATCH更简单。
• 如果你使用的是Excel 365/WPS 2021及以上版本：XLOOKUP是比INDEX+MATCH更简洁的方案
  • 参考公式：
    =XLOOKUP(1,(Sheet1!$B:$B=$A2)*(UPPER(Sheet1!$A:$A)=UPPER(B$1)),Sheet1!$D:$D,"")

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