Excel for Mac中SUMPRODUCT公式返回#VALUE!错误的调试请求
2026-4-20
Excel for Mac中SUMPRODUCT公式返回#VALUE!错误的调试请求
大家好，我在Excel for Mac里写的SUMPRODUCT公式一直返回#VALUE!错误，实在找不到问题所在，想请各位帮忙看看。
需求说明
这个公式的目的是在「food tracker」工作表的Daily Tally区域，计算对应宏量营养素（比如Kcal）的总摄入量——具体来说，就是把「nutrition by portion」工作表中每种食物的单位营养值，乘以「food tracker」里对应的食用量，最后求和。
出错的公式
=SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A12,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3))
相关工作表与命名范围
「nutrition by portion」工作表（精简版）
这张表存储了每种食物每克/每毫升的营养数据：
| Foodstuff | weight (grams or ml) | Kcal | Animal Protein (g) | Plant Protein (g) | Sodium (mg) | Potassium (mg) | Fat (g) |
|---|---|---|---|---|---|---|---|
| almond butter | 1.00 | 5.94 | 0.00 | 0.22 | 0.00 | 7.47 | 0.53 |
| almond cake | 1.00 | 3.55 | 0.00 | 0.08 | 2.74 | 3.48 | 0.30 |
| almond flour | 1.00 | 6.33 | 0.00 | 0.23 | 0.00 | 6.67 | 0.50 |
| almondmilk | 1.00 | 0.13 | 0.00 | 0.00 | 0.67 | 0.15 | 0.01 |
| almonds | 1.00 | 5.89 | 0.00 | 0.21 | 0.01 | 7.32 | 0.50 |
「food tracker」工作表
这张表记录当日食用的食物及分量，Daily Tally区域用公式引用营养表计算总和：
| 项目 | 内容 |
|---|---|
| day of week | friday |
| meal number | 1 |
| almond butter | 30 |
| almond cake | 40 |
| almond flour | 10 |
| almondmilk | 5 |
| almonds | 2 |
| Daily Tally | |
| Kcal | =SUMPRODUCT(IFNA(OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH(@A:A,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| Animal Protein (g) | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A12,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| Plant Protein (g) | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A13,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 其他营养素项 | 公式格式与上述一致，仅MATCH中的行号（如$A14、$A15）不同 |
已定义的命名范围
| 命名范围 | 范围值 | 引用位置 |
|---|---|---|
| DailyFooditems | {"almond butter"; "almond cake"; "almond flour";"almondmilk";"almonds"} | ='food tracker'!$A$4:$A$8 |
| AllFooditems | {"almond butter"; "almond cake"; "almond flour";"almondmilk";"almonds"} | ='nutrition by portion'!$A$2:$A$10 |
| Nutrients | {"weight (grams or ml)", "Kcal","Animal Protein (g)","Plant Protein (g)","Sodium (mg)","Potassium (mg)","Fat (g)"} | ='nutrition by portion'!$B$1:$AA$1 |
我的排查思路（以及请求帮助的点）
我已经检查了命名范围的引用是否正确，食物名称和营养素名称也都是完全匹配的，但公式还是返回#VALUE!。怀疑是@运算符（隐式交集）在Excel for Mac中的行为和数组运算不兼容，或者OFFSET函数的数组返回格式有问题？
有没有大佬能帮忙分析下错误原因，或者给出可行的修改方案？
备注：内容来源于stack exchange，提问作者SottoVoce
相关产品推荐
相关产品推荐
