在 Excel 中,如何将分隔值与另一列中的分隔值相乘?

在 Excel 中,如何将分隔值与另一列中的分隔值相乘?

我有两列数据。A 列包含商品,B 列包含商品数量。在下面的例子中,我想计算“叉子”的数量:

 - A1: spoon|fork|knife
 - A2: spoon|bottle|fork|piston
 - B1: 2|1|1
 - B2: 6|2|4|3

或者参见下图:

excel 截图中的图像

总共有 5 个叉子,第 1 行有一个叉子,第 2 行有 5 个叉子。

我该如何计算这个叉子的总数呢?

答案1

使用COUNTIF。例如,在第 9 行输入:=COUNTIF(A1:A7, "star")然后您将看到出现的次数。

在此处输入图片描述

另外,您还可以使用优秀的在线工具:http://string-functions.com/countsubstrings.aspx(在第一个字段中,输入所需的单词,即fork,然后在下面插入文本)

来源

答案2

这是您可以做到的一种方法。我假设您是从其他地方复制并粘贴的。

  1. 不要将数据导入 Excel,而是将原始数据保存为 .txt 文件。
  2. 打开 Excel 并打开一个新的空白工作簿。
  3. 在数据功能区下,使用“获取外部数据”“来自文本”选项(左侧)
  4. 选择您保存的 .txt 文件,您将进入文本导入向导。
  5. 在步骤 1 中,选择“分隔”
  6. 在步骤 2 中,选择“其他”,然后键入您使用的分隔符(在您的情况下为“|”)。
  7. 单击“完成”跳过步骤 3。
  8. 选择“新工作表”作为输出目的地

    现在,您的数据格式会稍微好一些。请注意,如果数据已在 Excel 中,则可以使用“文本到列”功能执行上述步骤。

  9. 在 Sheet1(或任何空白表)中,创建列标题:

    • A1:项目
    • A2:数量
  10. 现在,下面介绍如何将混合数据转换为可用数据:

    在 A2 中输入以下公式:

    =OFFSET(Sheet4!$A$1,INT((ROW()-2)/4),MOD(ROW()-2,4))  
    
    • 确保将其替换"Sheet4!$A$1"为 ITEM 数据开始的单元格。在您的示例中,这将是第一个“勺子”的单元格
    • 确保你有美元符号(即不是A1,但是$A$1
    • 如果需要,请将公式中的数字 4 替换为项目数据的列数,在您的情况下最大值为 4)
  11. 在 B2 中,重复您在 A2 中执行的操作,但要将 替换为"Sheet4!$A$1"QUANTITY 数据开始的单元格。对我来说,它看起来像这样:

    =OFFSET(Sheet4!$E$1,INT((ROW()-2)/4),MOD(ROW()-2,4))  
    
  12. 现在您要做的就是填写此公式,直到每个项目/数量组合都有一行。以您的示例为例,它看起来如下:

    ITEM    QUANTITY
    spoon   2
    fork    1
    knife   1
    0       0
    spoon   6
    bottle  2
    fork    4
    piston  3
    
  13. 接下来,从上面的结果中复制 A 列并将其粘贴到同一工作簿中的新空白表中。(我正在使用 Sheet2)

  14. 当粘贴的数据仍处于选中状态时,在数据功能区下,选择“删除重复项”
  15. 在出现的对话框中,选中“我的数据有标题”框,然后单击“确定”

    你现在应该有这个:

    ITEM  
    spoon  
    fork  
    knife  
    0  
    spoon  
    bottle  
    
  16. 在 B1 中创建列标题“QUANTITY”

  17. 在 B2 中输入以下公式:

    =SUMIF(Sheet1!$A:$A,$A2,Sheet1!$B:$B)
    

    确保将“Sheet1”替换为您用于第一个公式的工作表。

这就是结果!

ITEM    QUANTITY  
spoon   8  
fork    5  
knife   1  
0       0  <-- Go ahead and delete this row if you want!  
spoon   8  
bottle  2

您可以保存此 Excel 工作簿并在将来的数据导入时重复使用,只要您始终将导入的文本数据放在同一位置并且列数不变即可。如果确实如此,只需相应地更新公式即可。

正如其他人指出的那样,这是一种奇怪的数据格式,但我认为这些是你所面临的限制。

相关内容