Excel超十万行数据：识别库存录入异常的非VBA方案需求

针对超10万行、包含商品编号、商品名称、配送中心、库存数量列的Excel表格，以下是无需VBA的操作步骤：

方法一：基于商品平均库存的倍数筛选 #

1. 计算单商品平均库存
   • 在空白列（如E列，E2单元格）输入公式：=AVERAGEIF($A:$A,A2,$D:$D)，下拉填充至所有行。该公式会自动匹配当前行的商品编号，计算对应商品的库存平均值。
2. 计算库存与平均的倍数
   • 在F列（F2单元格）输入：=D2/E2，得到当前库存是该商品平均库存的倍数，下拉填充。
3. 筛选异常条目
   • 选中F列，使用Excel的筛选功能，筛选出数值大于业务设定阈值（如2，可根据实际情况调整）的行，这些行即为库存远高于对应商品平均水平的异常数据。

方法二：结合95分位数的严格筛选 #

如果需要更精准的异常判定，可结合分位数规则：

1. 计算单商品95分位数库存
   • 在G列（G2单元格）输入：=PERCENTILE.INC(IF($A:$A=A2,$D:$D,""),0.95)，Excel 365及以后版本直接回车，旧版需按Ctrl+Shift+Enter触发数组计算，下拉填充。该公式会算出对应商品库存的95分位数数值。
2. 判定是否超过分位数两倍
   • 在H列（H2单元格）输入：=D2>(G2*2)，下拉填充后，结果为TRUE的行即为库存超过该商品95分位数两倍的异常条目。
3. 筛选结果
   • 筛选H列中值为TRUE的行，即可得到目标异常列表。

注意事项 #

• 处理超10万行数据时，建议使用Excel 365或2021及以上版本，函数计算效率更高，减少卡顿。
• 若商品名称与编号一一对应，可将公式中的$A:$A（商品编号列）替换为$B:$B（商品名称列），效果一致。
• 先将数据区域转为表格格式（选中数据→按Ctrl+T），公式下拉时会自动扩展至全表，操作更便捷。

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