代码之家  ›  专栏  ›  技术社区  ›  Rotsiser Mho

使用Excel(适用于Mac)查找范围内的每个单元格

  •  1
  • Rotsiser Mho  · 技术社区  · 7 年前

    如果Excel中有一行的列数可变,并且填充了项目编号,我想在另一个表中查找,列出填充列中每个项目的成本,并求总成本的和。我的Google fu让我失望,因为我似乎找不到任何方法在一个范围内的每个单元格上执行Excel公式(本例中是VLOOKUP)。基本上,如果我有以下表格:

    项目成本 Item | Cost A | 10 B | 20 C | 40

    订单 Order | 1 | A 2 | A | B 3 | B | C

    在给定订单号的情况下,如何计算下表中的总数?

    订单摘要 Order | Total 1 | 10 2 | 30 3 | 60

    我已经编写了一些公式,可以在表单中生成查找范围 Orders!$B$2:$C$2 。我想我可以使用该范围作为一些公式的输入,将项目按顺序映射到其项目成本并求和。我曾希望我可以对范围内的每一件物品执行VLOOKUP,它可以从 ItemCosts 然后我可以求和,但VLOOKUP只接受一个单元格作为查找值的表。我觉得我错过了一些明显的东西。我应该在VBA中完成吗? This answer 提到VLOOKUP可以获取一个数组,然后返回一个数组,但我无法在Excel 2016 for Mac中复制它。它只是将第一个匹配项作为单个值返回。

    编辑: 从评论来看,非规范化订单表可能是个问题。但即使看起来像这样,我也不清楚如何查找每个项目的成本,以便计算总成本。

    订单 Order | Item 1 | A 2 | A 2 | B 3 | B 3 | C

    1 回复  |  直到 7 年前
        1
  •  1
  •   FloLie    7 年前

    有两种方法可以使用“标准公式”生成所需的输出,我将提出这两种方法。我想,可能有一个数组函数可以在没有辅助列/表的情况下解决这个问题,但它们往往很复杂,很难维护。

    对于规范化版本,您可以执行以下操作:

    1) 将值列添加到orders表中:

    'Column of Cost in ItemCost Table' = ColumnCost
    'Column of Item in ItemCost Table' = ColumnItem
    
    Order | Item | Value
    1     | A    |=index(ColumnCost, match(ItemCell, ColumnItem, 0))
    2     | A    |...
    2     | B    |...
    3     | B    |...
    3     | C    |...
    

    2) 使用sumif公式根据订单号对订单表中的值列求和。

    对于非规范化版本,而不是辅助列,可以使用与订单工作表具有相同形式和尺寸的辅助表。

    使用与上例中相同的索引公式结构,但不使用订单中的ItemCell。如下所示:

    订单

    Order |
    1     | A
    2     | A | B
    3     | B | C
    

    订单帮助器

    Order |
    1     | 10
    2     | 10 | 20
    3     | 20 | 40
    

    对于汇总表,您只需在相应的列上求和即可。

    您可以在此处找到一个示例: https://ufile.io/rbxf1