如何在Excel中高效计算并对比不同行列的键值关联数据？

问题说明 #

需针对每个鞋类品牌，通过公式 (PALLET数量 / LAYER数量) * LAYER高度 计算托盘理论高度，再与实际PALLET高度对比。当前使用数组形式的XLOOKUP（=XLOOKUP([@[Shoe Brand]]&"|&"&{"PALLET","LAYER"},[Shoe Brand]&"|&"&[Measure],[Amount])...）会导致电脑卡顿，COUNTIF类公式无法满足需求，需更高效的实现方法。

示例数据：

高效解决方案 #

方法1：拆分XLOOKUP为单条件查找（非数组） #

将数组XLOOKUP拆分为两个独立的单条件查找，避免数组运算的性能损耗：
在Is Pallet Height Correct?列的PALLET行输入公式：

=IF([@Measure]="PALLET", IF(ROUND((XLOOKUP([@[Shoe Brand]]&"PALLET", [Shoe Brand]&[Measure], [Amount])/XLOOKUP([@[Shoe Brand]]&"LAYER", [Shoe Brand]&[Measure], [Amount]))*XLOOKUP([@[Shoe Brand]]&"LAYER", [Shoe Brand]&[Measure], [Height]),0)=[@Height], "Yes", "No"), "")

• 优势：单条件查找运算量远小于数组查找，大幅降低卡顿概率
• 说明：用ROUND处理浮点精度误差，非PALLET行返回空值保持表格整洁

方法2：使用SUMIFS替代XLOOKUP #

SUMIFS作为原生多条件聚合函数，在数据量大时性能更稳定：
同样在PALLET行输入公式：

=IF([@Measure]="PALLET", IF(ROUND((SUMIFS([Amount],[Shoe Brand],[@[Shoe Brand]],[Measure],"PALLET")/SUMIFS([Amount],[Shoe Brand],[@[Shoe Brand]],[Measure],"LAYER"))*SUMIFS([Height],[Shoe Brand],[@[Shoe Brand]],[Measure],"LAYER"),0)=[@Height], "Yes", "No"), "")

• 优势：计算逻辑更轻量化，数据量越大，对比数组XLOOKUP的效率优势越明显

方法3：定义名称缓存计算结果（超大数据表适用） #

针对超大型数据表，通过定义名称缓存重复计算的结果，进一步提升效率：

1. 定义名称PalletAmt：=SUMIFS(Table1[Amount],Table1[Shoe Brand],Table1[@[Shoe Brand]],Table1[Measure],"PALLET")
2. 定义名称LayerAmt：=SUMIFS(Table1[Amount],Table1[Shoe Brand],Table1[@[Shoe Brand]],Table1[Measure],"LAYER")
3. 定义名称LayerHt：=SUMIFS(Table1[Height],Table1[Shoe Brand],Table1[@[Shoe Brand]],Table1[Measure],"LAYER")
   随后公式简化为：

=IF([@Measure]="PALLET", IF(ROUND((PalletAmt/LayerAmt)*LayerHt,0)=[@Height], "Yes", "No"), "")

• 优势：名称引用会缓存计算结果，减少重复运算，大幅降低大数据表的计算负载

注意事项 #

• 确保每个品牌的PALLET和LAYER记录唯一，否则会返回错误值，可提前用数据验证或条件格式排查重复项
• 根据数据量选择方案：小表用方法1，中大型表用方法2，超大型表用方法3

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