arrays - 如何导入工作表的所有非空白单元格的数组（用于矩阵乘法）？

Question

我有一个电子表格，用于计算我吃的每餐的营养数据。一张表（“成分”）包含每种成分的营养数据矩阵。另一张表（“食谱”）包含每餐的每种成分的份量矩阵。在我的“膳食营养数据”表中，我想将这些矩阵相乘以获得每个食谱的营养数据。

我可以为我目前拥有的一定数量的成分和食谱执行此操作：=mmult(ingredients!B5:AF39,recipes!B2:G32)但是如果我添加食谱或成分，我希望它自动更新，所以我不希望该公式被硬编码。由于矩阵需要所有单元格中的值相乘，并且由于约束 NxP * PxM（num 行 x num 列）必须保持，因此我需要将每个工作表的所有非空白单元格的数组导入到MMULT()公式中。

我目前的尝试=mmult(query(ingredients!B5:FN39,"select * where Col1 is not null",0),query(recipes!B2:G32,"select * where Col1 is not null",0))不起作用。它抛出错误：“无法解析函数查询参数 2 的查询字符串：NO_COLUMN：Col1”。

我不知道应该用什么来代替“Col1”这个词，或者我的公式是否是实现这些结果的有效方法。

我也尝试了该filter()功能，但它不能与数组一起使用。

文件.csv

ingredient,"Great Value Golden Sweet Whole Kernel Corn, 15 oz","Great Value White Kidney Beans Cannellini Beans, 15.5 oz","Organic Great Value Garbanzo Beans Chick Peas, 15 oz","Great Value Sweet Peas, 15 oz",Great Value Chunk Light Tuna in Water 12 oz. Can,"Great Value Chunk Light Tuna in Water, 5 oz","Great Value Sardines in Water, 3.75 oz","Great Value 100% Whole Wheat Bread, Round Top, 20 oz","Great Value Wheat Sandwich Bread, 20 oz",Unsalted Roasted Peanuts,"Tyson® Fully Cooked Chicken Patties, 59.2 oz. (Frozen)","Butterball All Natural 90%/10% Lean Ground Turkey, 1 lb.",Sweet Potato,Potato,"Country Crock Original Butter Spread, 15 oz",Great Value Extra Virgin Olive Oil 101 oz,Romaine Lettuce,Great Value Teriyaki Sauce 15 oz,Great Value Soy Sauce 15 oz,Great Value Ketchup 64 oz,Great Value Reduced Fat Mayo with Olive Oil 30 oz,Kroger Ranch Dressin 16 oz,"Great Value Light Creamy Caesar Dressing, 16 oz",Large Egg,"Great Value Diced Tomatoes In Tomato Juice, 28 Oz",Green Bell Pepper,Cucumber,"Great Value Italian Style Bread Crumbs, 15 oz","Great Value Finely Shredded Parmesan Cheese, 6 oz","Great Value Frozen Rising Crust Supreme Pizza, 30.7 oz",Nature Valley Chocolate Pretzel Nut Chewy Granola Bars,Great Value Long Grain Enriched Rice,Broccoli Stir-Fry,Poke Marinade and Sauce ,Great Value Blueberries,Great Value Frozen Mango Chunks,Great Value Frozen Chopped Spinach,Bananas,Blue Diamond Unsweetened Original Almond Milk,Tropicana 100% Orange Juice (some pulp),Kroger 100% Pineapple Juice (unsweetened),Zucchini,Simple Truth Organic Tofu Extra Firm,"Classico Signature Recipes Traditional Basil Pesto Sauce and Spread, 8.1 oz Jar","Frozen Cooked Medium Peeled & Deveined Tail-On Shrimp, 12 oz"
ingredient type,starchy vegetable,bean,bean,vegetable,protein,protein,protein,grain,grain,snack,protein,protein,starchy vegetable,starchy vegetable,fat,fat,vegetable,sauce,sauce,sauce,sauce,sauce,sauce,protein,vegetable,vegetable,vegetable,grain,dairy,meal,snack,carb,vegetable,sauce,fruit,fruit,vegetable,fruit,dairy,fruit,fruit,vegetable,protein,sauce,protein
servings per container,3.5,3.5,3.5,3.5,3,8,1,22,22,6,22,4,1,1,,,,,,,,,,,7,1,1,,,6,,50,7,16,3,10,4,,8,6,8,,0,4,4
serving size,1/2 cup,1/2 cup,1/2 cup,1/2 cup,3 oz,2 oz,1 can,1 slice,1 slice,1 oz,1 patty,4 oz,1 medium potato (114 g),1 medium potato (148 g),1 tbsp,1tbsp,2 cups (94g),1 tbsp,1 tbsp,1 tbsp,1 tbsp,2 tbsp,2 tbsp,1 egg,1/2 cup,1 pepper (100 g),1 cucumber (104g),1/3 cup,1/3 cup,1/6 pizza,1,1/4 cup,1 cup,1 tbsp,1 cup,1 cup,1 cup,1 banana,1 cup,8 fl oz,8 fl oz,1 zucchini,1 pack,1/4 cup,11
calories,45,110,110,60,80,45,100,60,70,170,200,190,103,110,50,120,15,15,5,20,50,140,70,70,25,20,16,110,110,330,150,160,30,50,100,130,35,105,30,110,120,33,270,240,100
total fat (g),0.5,0,2,0,0.5,0.5,4.5,0.5,1,15,13,11,0,0,6,14,0,0,0,0,6,14,6,5,0,0,0,1.5,7,12,5,0,0,0,1.5,0.5,0.5,0.4,2.5,0,0,0.6,13.5,24,1.5
saturated fat (g),0,0,0,0,0,0,1.5,0,0,2,3,2.5,0,0,1.5,2,0,0,0,0,1,2.5,1,1.5,0,0,0,0,4,4.5,2,0,0,0,0,0,0,0.1,0,0,0,0.2,1.5,4.5,0.5
trans fat (g),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.2,0,0,0
polyunsaturated fat (g),0,0,0,0,0,0,1.5,0,0,0,4.5,0,0,0,2.5,0,0,0,0,0,3,0,3.5,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0.1,0.5,0,0,0,0,0,0
monounsaturated fat (g),0,0,0,0,0,0,4,0,0,0,5,0,0,0,1,0,0,0,0,0,1.5,0,1.5,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1.5,0,0,0,0,0,0
cholesterol (mg),0,0,0,0,35,20,75,0,0,0,35,80,0,0,0,0,0,0,0,0,5,5,0,185,0,0,0,0,20,30,0,0,0,0,0,0,0,0,0,0,0,0,0,5,180
sodium (mg),200,270,120,300,270,180,300,110,135,0,400,80,41,0,100,0,10,600,900,160,110,260,560,70,180,3,2,420,340,810,110,0,20,710,0,0,80,1,170,0,35,16,45,590,710
total carbohydrates (g),9,20,18,11,0,0,0,11,13,5,10,0,24,26,0,0,3,3,1,5,1,2,3,0,5,5,4,65,1,42,24,36,7,11,24,29,4,27,1,26,30,6,9,5,2
dietary fiber (g),1,6,4,3,0,0,0,2,1,3,0,0,4,2,0,0,2,2,0,0,0,0,0,0,1,2,2,21,0,2,1,1,2,0,6,3,3,3.1,1,0,1,2,3,1,0
total sugars (g),2,1,3,5,0,0,0,1,2,1,0,0,7,1,0,0,1,2,0,4,0,1,2,0,3,2,2,1,0,4,9,0,2,9,17,27,0,14,0,22,26,4.9,3,0,0
added sugars (g),0,0,0,2,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,3,0,1,2,0,0,0,0,2,0,13,8,0,0,9,0,0,0,0,0,0,0,0,0,0,0
protein (g),1,7,6,3,18,0,16,4,2,8,9,20,2,3,0,0,1,0,1,0,0,0,1,6,1,1,0,4,10,0,2,3,2,1,0,1,3,1.3,1,2,0,2.4,27,3,20
potassium (mg),125,440,210,133,210,110,0,0,30,210,0,0,0,620,0,0,232,0,0,0,0,0,0,70,93,0,152,0,0,0,0,50,200,55,130,340,245,422,160,450,260,0,282,0,0
vitamin A (mg),0,0,0,0,0,0,18,0,0,0,0,0,"3,942",0,90,0,0,0,0,0,0,0,0,80,0,63,0,0,0,72,0,0,0,0,0,0,0,10,150,0,0,63,0,225,36
calcium (mg),14,50,30,17,0,0,325,50,20,30,0,104,52,20,0,0,31,0,0,0,0,0,0,30,0,13,16,78,325,195,0,10,29,0,0,40,124,0,450,20,0,39,390,195,104
vitamin D (mcg),0,0,0,0,2,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,5,0,0,0,0,0,0
vitamin C (mg),0,0,0,0,0,0,0,0,0,0,0,0,11.1,27,0,0,4,0,0,0,0,0,0,0,0,120,0,0,0,9,0,0,0,0,0,101,0,15.3,0,90,72,52,0,7.2,0
iron (mg),0,2.2,1.2,1,1.2,0.6,1.7,0.8,1,1,0.4,1.8,0.7,1.1,0,0,1,0,0,0,0,0,0,0.9,0,0.4,2,1.1,0,2.7,0.9,1.9,1,0,0,0,1.6,0.2,0.7,0,0,0.36,4.32,1.08,0.36
vitamin b-6 (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0.2,0,0,0.1,0,0,0,0,0,0,0.1,0,0,0,0,0,0,0,0,0,0,0,0,0,0.5,0,0,0,0.9,0,0,0
magnesium (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,13,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,32,15,0,0,33.6,0,0,0
thiamine (mg),0,0,0,0,0,0,0,0,0.1,0,0,0,0,0,0,0,0.1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.26,0,0,0,0,0,0,0,0,0,0,0,0,0
riboflavin (mg),0,0,0,0,0,0,0,0,0.1,0,0,0,0,0,0,0,0.1,0,0,0,0,0,0,0.2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
niacin (mg),0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1.4,0,0,0,0,0,0,0,1.9,0,0,0,0,0,0,0,0,0,0,0,0,0
folate (mcg),0,0,0,0,0,0,0,0,20,0,0,0,0,0,0,0,128,0,0,0,0,0,0,25,0,0,0,0,0,0,0,174,0,0,0,0,0,0,0,0,0,0,0,0,0
vitamin k (mcg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,96,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
vitamin b-12 (mcg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
zinc (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.7,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
vitamin e (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,7.5,0,0,0,0,0,0
biotin (mcg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
pantothenic acid (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
phosphorus (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,20,0,0,0,0,0,0
iodine (mcg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,28,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
selenium (mcg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,15,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
choline (mg),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,150,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

标签： arraysgoogle-sheetsgoogle-sheets-formulamatrix-multiplicationarray-formulas

Answer 1

尝试：

=ARRAYFORMULA(MMULT(
 INDIRECT("ingredients!B5:"&ADDRESS(COUNTA(ingredients!A5:A)+4, 
 MAX(IF(ingredients!1:1="",,COLUMN(ingredients!1:1))))), 
 INDIRECT("recipes!B2:"&ADDRESS(COUNTA(recipes!A2:A)+1, 
 MAX(IF(recipes!1:1="",,COLUMN(recipes!1:1)))))))