查找组合并在Excel中对它们进行计数 [英] Finding combinations and counting them in Excel

问题描述

我对Excel不太了解，我正在尝试执行以下操作:

I don't know much about Excel and I'm trying to do the following:

因此，如果我有A列和B列:

So, if a I had column A and column B:

A     B  
red   green  
red   green  
red   green  
blue  pink  
blue  pink  
blue  pink  
blue  pink  
black white  
black white  

比方说，我有数百行组合.我需要做的是在第二张纸上，显示所有不同的组合以及每种组合出现的次数.因此对于上面的结果将是:

Let's say I have hundreds of rows of combinations. What I need to do is on a second sheet, show all the different combinations and the number of times each occurs. So for the above, the result would be:

Combination: Number of times:   
red green    3  
blue pink    4  
black white  2  

因此，我需要给我组合及其发生的次数. 知道我该怎么做吗?

So, I would need to give me the combination and the number of times it occurs. Any idea how I could do this?

推荐答案

在电子表格中添加标题:A1 = color1，B1 = color2，C1 =组合

Make a header into your spreadsheet: A1 = color1, B1 = color2, C1 = combination

1-在C2上输入

=A2&"-"&B2

将公式向下拖动到C列，直到最后一行包含A和B列的数据.

drag the formula down on column C until the last row in which there are data on columns A and B.

2-转到插入"->数据透视表"

2- Go to "Insert" --> "PivotTable"

将组合"拖到行标签"中，然后将组合"拖到值"标签中.
您需要在数据透视表的值"字段中进行数学运算，并且当人们将变量拖入其中时，计数"操作已经自动设置(因此，它应该显示为组合计数").

Drag "combination" into the "Row Labels", and Drag "combinations" into the "Values" label.
You need to have a mathematical operation in the pivot-table "Values" field, and the "Count" operation is already set automatically when one drags a variable into it (so, it should appear "Count of combinations").

以下是有关数据透视表外观的屏幕截图:

Here is a screenshot about how the Pivot Table should look like:

这篇关于查找组合并在Excel中对它们进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！