Excel - 搜索列以查找文本是否匹配并计算字符串

I want to add values from 1 worksheet to another.

我想将1个工作表中的值添加到另一个工作表中。

The first worksheet ("November 2011") simply contains name & status, names are however duplicated and a percentage of a string needs to be added on the second worksheet ("Sales").

第一个工作表(“2011年11月”)只包含名称和状态,但是名称是重复的,并且需要在第二个工作表(“Sales”)上添加字符串的百分比。

e.g. "November 2011"

例如“2011年11月”

    A           B
1   Name        Status
2   McDonalds   Completed
3   McDonalds   Won
4   Burger King Won
5   Burger King Won
6   Wendys      Completed
7   iHop        Completed
8   iHop        Completed

The second worksheet ("Sales") contains 3 columns Name, Completed & Won. However, Name has had duplicates removed.

第二个工作表(“Sales”)包含3列Name,Completed&Won。但是,Name已删除重复项。

e.g. "Sales"

    A           B           C
1   Name        Completed   Won
2   McDonalds   
3   Burger King 
4   Wendys      
5   iHop

I want the "Sales" worksheet to look like this however:

我希望“销售”工作表看起来像这样:

    A           B           C
1   Name        Completed   Won
2   McDonalds   50%         50%
3   Burger King 0%          100%
4   Wendys      100%        0%
5   iHop        100%        0%

Just wondering what formula to use? It needs to check against the name.

只是想知道使用什么配方?它需要检查名称。

2 个解决方案

#1

I'd use a countif function to find the number of sales and countifs to find the number of wins and completed.

我将使用countif函数来查找销售数量和商家数量,以查找获胜数量并完成。

=COUNTIFS('November 2011'!$A:$A, A2, 'November 2011'!$B:$B, "won") 
  / COUNTIF('November 2011'!$A:$A,A2)

This would give you the ratio or percent if formatted of the won.

如果格式化赢了,这将给你比率或百分比。

#2

Another alternative is to use an "array formula" with AVERAGE function, i.e. this formula in B2

另一种方法是使用具有AVERAGE功能的“阵列公式”,即B2中的该公式

=AVERAGE(IF(November 2011'!$A$2:$A$1000=$A2,IF('November 2011'!$B$2:$B$1000=B$1,1,0)))

=平均(如果(2011年11月)'!$ A $ 2:$ A $ 1000 = $ A2,IF('2011年11月'!$ B $ 2:$ B $ 1000 = B $ 1,1,0)))

confirmed with CTRL+SHIFT+ENTER and copied across and down

用CTRL + SHIFT + ENTER确认并向上和向下复制

#1