以下返回 #VALUE! 错误：
=SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A12,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3))
此公式的目的是将“食物追踪器”工作表中“每日计数”下与每种常量营养素相匹配的所有值相加——例如“千卡”。这些值中的每一个都按食物类型（例如“杏仁酱”）细分在工作表“按份营养”中。下面的图片提供了更多背景信息。
根据反馈，以下是作为 markdown 表的工作表和命名范围：
“按份计算营养”工作表（节选）
| 食品 | 重量（克或毫升） | 千卡 | 动物蛋白（克） | 植物蛋白（克） | 钠（毫克） | 钾（毫克） | 脂肪（克） |
|---|---|---|---|---|---|---|---|
| 杏仁奶油 | 1.00 | 5.94 | 0.00 | 0.22 | 0.00 | 7.47 | 0.53 |
| 杏仁蛋糕 | 1.00 | 3.55 | 0.00 | 0.08 | 2.74 | 3.48 | 0.30 |
| 杏仁粉 | 1.00 | 6.33 | 0.00 | 0.23 | 0.00 | 6.67 | 0.50 |
| 杏仁奶 | 1.00 | 0.13 | 0.00 | 0.00 | 0.67 | 0.15 | 0.01 |
| 杏仁 | 1.00 | 5.89 | 0.00 | 0.21 | 0.01 | 7.32 | 0.50 |
“食物追踪器”工作表（参考“按份营养”中的值）：
| 星期几 | 星期五 |
|---|---|
| 餐号 | 1 |
| 杏仁奶油 | 三十 |
| 杏仁蛋糕 | 40 |
| 杏仁粉 | 10 |
| 杏仁奶 | 5 |
| 杏仁 | 2 |
| 每日统计 | 千卡 |
|---|---|
| =SUMPRODUCT(IFNA(OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH(@A:A,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) | |
| 动物蛋白（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A12,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 植物蛋白（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A13,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 脂肪（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A14,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 饱和脂肪（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A15,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 净碳水化合物（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A16,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 纤维（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A17,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 可溶性纤维（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A18,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 糖（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A19,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| Omega-3（ALA）（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A20,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 单不饱和脂肪（克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A21,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 钠（毫克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A22,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 钾（毫克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A23,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 钙（毫克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A24,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 铁（毫克） | =SUMPRODUCT(IFNA(@OFFSET(AllFoodItems,MATCH(@DailyFoodItems,AllFoodItems,0)-1,MATCH($A25,Nutrients,0),1,1),0), OFFSET(DailyFoodItems,0,B$3)) |
| 命名范围 | 范围值 | 参考 |
|---|---|---|
| 每日食品 | {“杏仁酱”；“杏仁蛋糕”；“杏仁粉”；“杏仁奶”；“杏仁”} | ='食物追踪器'!$A$4:$A$8 |
| 全部食品 | {“杏仁酱”；“杏仁蛋糕”；“杏仁粉”；“杏仁奶”；“杏仁”} | ='按份营养'!$A$2:$A$10 |
| 营养素 | {“重量（克或毫升）”、“千卡”、“动物蛋白（克）”、“植物蛋白（克）”、“钠（毫克）”、“钾（毫克）”、“脂肪（克）”} | ='按份营养'!$B$1:$AA$1 |
另请参阅图像以了解工作簿的组织方式和名称管理器值。
答案1
我摆脱了 OFFSET，因此它不再易变。
=SUMPRODUCT(B$4:B$8,INDEX('nutricion by portion'!B$2:AA$6,MATCH(DailyFoodItems,AllFooditems,0),MATCH(@A:A,Nutrients,0)))
您可能想要B4:B8动态化，但您提供的示例数据太少。也 'nutricion by portion'!B$2:AA$6可以成为命名范围。命名范围的好处是：如果您知道它们是什么以及它们在哪里，它会使公式易于阅读。如果您不知道，那么实际范围对于局外人来说更易于阅读。
如果您使用的是 Office 365，则可能需要使用LET()，您可以在公式本身中命名范围或数组。如果使用得当，这将使任何人都能更好地阅读它。