Finding combinations and counting them in Excel

问题:

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

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?

回答1:

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

1- Type on C2

=A2&"-"&B2

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

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:

回答2:

One way you could do this is the following:

Select the entire data, copy it and paste it where you want to calculate the number of occurences. Select that range and in the Data tab select Remove Duplicates. This will get you all unique occurences of patterns.

Now, with the following formula you can get the count of each of those cases. Notice that this is an array formula so when you enter it initially you have to hit Ctrl+Shift+Enter in the formula box for it to calculate properly. Here's the formula, just change the cells to those that match your need:

=SUM(IF($A$1:$A$4&$B$1:$B$4=A1&B1,1,0))

Here,$A$1:$A$4&$B$1:$B$4 concatenates the two columns together to create "keys". It then matches this with the current combination to check (A1&B1) and then returns 0 or 1 and sums the total to get the count.

回答3:

Add a third column - "Count" - adding the value "1" in each row of this column.

Include this column in your Pivot Table data and then allocate your fields in the Pivot Table as follows:

Columns: A | Rows: B | Values: Count