☰

Excel-构建列表列表的最佳方法？

tag-icon • tag-icon microsoft-excel worksheet-function array
Excel-构建列表列表的最佳方法？

我有一张烹饪食谱表。每个食谱都有一个名称以及一份配料清单及其用量。例如

Apple Pie
    x4 Apples
    x8 Flour
    x2 Butter
Scrambled Eggs
    x2 Eggs    
Burrito
    x1 Tortilla
    x1 Can of Beans
    x2 Cheese
    x1 Hot Sauce

目前我的数据组织如下：

| ... | Apple Pie |   #   | Scrambled Eggs |   #   | ... |
| ... | Apples    |   4   | Eggs           |   2   | ... |
| ... | Flour     |   8   |                |       | ... |
| ... | Butter    |   2   |                |       | ... |

如果我需要查找食谱中有哪些物品，我必须进行大量调用，例如查找 所在Apple Pie的列。然后我必须Apple Pie使用 来确定 下有多少种成分MATCH(ISBLANK。一旦我知道了，我必须执行INDEX(...):INDEX(...)来获取Apples, Flour, Butter然后我必须再次重复所有这些操作才能获取数量。

有没有更好的方法？从编程的角度来说，我想制作一个Map<String, Map<String, Integer>>食谱名称为钥匙我的第一张地图，成分名称是钥匙我的第二张地图给出了该成分的数量。

我的表格中还有另一部分列出了每种成分的价格：

| Ingredient | Cost (Cents) |
| Apples     | 129          |
| Cheese     | 45           |

我想编写一个简单的SUM函数来计算的成本总和Apple Pie，但是我的构造方式却使公式变得混乱，例如：

=IFERROR(ARRAYFORMULA(SUM(VLOOKUP(INDEX($F$4:$EQ$4, 1, MATCH($A3, $F$3:$EQ$3, 0)):INDEX($F$4:$EQ$11, MATCH(TRUE, ISBLANK(INDEX($F$4:$EQ$4, 1, MATCH($A3, $F$3:$EQ$3, 0)):INDEX($F$4:$EQ$11, 8, MATCH($A3, $F$3:$EQ$3, 0))), 0) - 1, MATCH($A3, $F$3:$EQ$3, 0)), 'PRICES'!$A:$G, 4, FALSE) * INDEX($F$4:$EQ$4, 1, MATCH($A3, $F$3:$EQ$3, 0) + 1):INDEX($F$4:$EQ$11, MATCH(TRUE, ISBLANK(INDEX($F$4:$EQ$4, 1, MATCH($A3, $F$3:$EQ$3, 0) + 1):INDEX($F$4:$EQ$11, 8, MATCH($A3, $F$3:$EQ$3, 0) + 1)), 0) - 1, MATCH($A3, $F$3:$EQ$3, 0) + 1))), "")

什么是更理想的数据结构方式，以便更容易地执行此类三维数组的查找？

答案1

如果您打算使用 Excel 来保存食谱，我更倾向于创建一个像下面这样的时间表。这样，您就可以通过在自动筛选下拉菜单中输入搜索来选择食谱。我在最后添加了成本列。然后，您可以添加第二个包含成本的小表（可以更新），并使用 vlookup 添加成本。

在此处输入图片描述

在此处输入图片描述

希望这对你有帮助，节日快乐，布拉德

答案2

用一个公式来得到总和：

=SUMPRODUCT(SUMIFS(I:I,H:H,INDEX(A:F,2,MATCH(M1,A1:F1,0)):INDEX(A:F,COUNTA(INDEX(A:F,MATCH(M1,A1:F1,0),0))-1,MATCH(M1,A1:F1,0)))*INDEX(A:F,2,MATCH(M1,A1:F1,0)+1):INDEX(A:F,COUNTA(INDEX(A:F,MATCH(M1,A1:F1,0),0))-1,MATCH(M1,A1:F1,0)+1))

这在 Excel 中有效，但在 Google Sheets 中无效。您将其标记为 Excel，但您提供的公式是 Google Sheets。

在此处输入图片描述

答案3

：警告：

根据我的理解，下面显示的方法是我发现的最好的方法，使用 OP 共享的数据，成分在前，数字在后。

  • 我决定对两者使用单一公式。
  • 无法找到有关将 X 与数字相加（如 x4）的线索。

在此处输入图片描述

  • 单元格中的公式A107：

     =IF(IFERROR(INDEX(A$100:A$103, ROWS(A107:A$107)), IFERROR(INDEX(C$100:C$101, ROWS(A107:A$107)-ROWS(A$100:A$103)), IFERROR(INDEX(E$100:E$104, ROWS(A107:A$107)-ROWS(A$100:A$103)-ROWS(C$100:C$101)), "")))=0,"",IFERROR(INDEX(A$100:A$103, ROWS(A107:A$107)), IFERROR(INDEX(C$100:C$101, ROWS(A107:A$107)-ROWS(A$100:A$103)), IFERROR(INDEX(E$100:E$104, ROWS(A107:A$107)-ROWS(A$100:A$103)-ROWS(C$100:C$101)), ""))))

  • 先右后下填写公式。

注意：

如果您坚持显示的输出，其中以 x 为前缀的数字首先出现，接下来是成分，那么请执行以下操作。

在此处输入图片描述

  • 单元格中的公式D107：

     =IF(IFERROR(INDEX(B$100:B$103, ROWS(B$107:B107)),IFERROR(INDEX(D$100:D$101,ROWS(B$107:B107)-ROWS(B$100:B$103)),IFERROR(INDEX(F$100:F$104,ROWS(B$107:B107)-ROWS(B$100:B$103)-ROWS(D$100:D$101)),"")))=0,"","x"&IFERROR(INDEX(B$100:B$103,ROWS(B$107:B107)),IFERROR(INDEX(D$100:D$101,ROWS(B$107:B107)-ROWS(B$100:B$103)),IFERROR(INDEX(F$100:F$104,ROWS(B$107:B107)-ROWS(B$100:B$103)-ROWS(D$100:D$101)),""))))

  • 单元格中的公式E107：

     =IFERROR(INDEX(A$100:A$103, ROWS(A107:A$107)), IFERROR(INDEX(C$100:C$101, ROWS(A107:A$107)-ROWS(A$100:A$103)), IFERROR(INDEX(E$100:E$104, ROWS(A107:A$107)-ROWS(A$100:A$103)-ROWS(C$100:C$101)), "")))

  • 向下填充公式。

A根据需要调整公式中的单元格引用。

相关内容