EXCEL-计数具有匹配组合的实例 [英] EXCEL- Count instances that have matching combinations

问题描述

我擅长于excel，但是由于某种原因找不到正确的解决方案(也许我已经使问题复杂化了):

基本上我有2列信息如下:

Column 1    Column 2
A                  Red
A                  Blue
A                  Yellow
B                  Blue
B                  Green
B                  Brown
C                  Red
C                  Blue
C                  Brown

我想要创建一个交叉表类型的表，该表计算第1列中有多少项在第2列中具有正确的值组合

例如，我希望得到以下结果:

         Red      Blue     Brown
Red   2            2            1

Red-Red的值为2，因为第1列中有2个实例，其中第2列中的项目为Red

由于在两种情况下，A列中的值在第2列中同时具有Red和Blue值，所以Red-Blue的值为2(A-Red/A-Blue和C-Red/C-Blue)

红棕色将求值为1，因为C仅是第1列中的值，而在第2列中具有红色和棕色的值

我希望这很清楚.我曾尝试过将countifs/sumifs进行多种组合，但运气不佳，没有得到理想的结果. :(

解决方案

基于公式的方法

我的解决方案中单元格 E2 中的公式如下，由于它是数组公式，因此您需要按 Ctrl+Shift+Enter 使它起作用.

{=SUM(--ISNUMBER(MATCH(IF((Column_1&$D2=Column_1&Column_2),Column_1)&E$1,Column_1&Column_2,0)))}

请注意，我已将一些示例数据添加到您的原始数据集中，并且我已命名了以下范围:

Column_1 代表第一列A中的所有数据；

Column_2 代表第二列B中的所有数据.

我的逻辑是

1. 使用 IF 功能和 = 来查找 D列中给定颜色的对应颜色是什么 Column_1 中的值是多少?如果我们在单元格D2中查看红色，我的IF函数将返回以下内容 {"A";FALSE;FALSE;FALSE;FALSE;FALSE;"C";FALSE;FALSE;FALSE;"D";FALSE;FALSE;FALSE;FALSE};

2. 使用 & 将最后一步的范围与行1中的查找值合并.如果我们将范围与单元格E1，我们将拥有 {"ARed";"FALSERed";"FALSERed";"FALSERed";"FALSERed";"FALSERed";"CRed";"FALSERed";"FALSERed";"FALSERed";"DRed";"FALSERed";"FALSERed";"FALSERed";"FALSERed"};

3. 下一步是匹配，从上一步到给定组合的范围是Column_1&Column_2，代表 ARed ， ABlue ， AYellow ， BBlue ， BGreen 等.继续我的示例，结果将是 {1;#N/A;#N/A;#N/A;#N/A;#N/A;7;#N/A;#N/A;#N/A;11;#N/A;#N/A;#N/A;#N/A}其中数值是 ARed ， CRed 和 DRed 在以下范围内的位置给定的组合Column_1&Column_2.

4. 使用 ISNUMBER 进行查找的最后一步是从最后一步开始的范围内有多少个值是数字，应返回 {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE} 然后通过 -- 将所有 TRUE 结果转换为数值1，从而求和.

基于功率查询的方法

在我的解决方案A1:B16中使用相同的源数据，将其加载到 Power Query Editor ，那么您应该具有以下内容:

请注意，我已经按照表格的第2列对表进行了排序，然后按第1列对表进行了连续升序.这是一个可选步骤，旨在帮助使以下步骤更易于理解.

下一步是通过匹配第1列:

将表与自己的表合并:

展开新列以显示第2列:

突出显示最后一列，使用枢轴列功能转换表格，那么您应该具有:

唯一的问题是颜色的位置将与源位置不同.

如果重要的是要保持原始颜色不变，一种方法是

1. 创建一个查找表，并通过索引列显示所需的颜色顺序；
在第一个查询中
2. 合并此查询表在透视列之前，并匹配第三列中的颜色，展开并排序索引列，然后将其删除；
3. 执行透视表"列，然后再次合并查找表，然后再次展开，排序和删除索引列.

以下是幕后代码，仅供参考:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column 2", Order.Ascending}, {"Column 1", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Column 1"}, #"Sorted Rows", {"Column 1"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Column 2"}, {"Filtered Rows.Column 2"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Filtered Rows", List.Distinct(#"Expanded Filtered Rows"[#"Filtered Rows.Column 2"]), "Filtered Rows.Column 2", "Column 1", List.Count)
in
    #"Pivoted Column"

如果您有任何疑问，请告诉我.欢呼声:)

I’m pretty good at excel but for some reason can’t find the right solution to this problem (maybe I’m over complicating it):

Basically I have 2 columns with info like below:

Column 1    Column 2
A                  Red
A                  Blue
A                  Yellow
B                  Blue
B                  Green
B                  Brown
C                  Red
C                  Blue
C                  Brown

What I want is to create a cross tab type of table which counts how many items in column 1 have the correct combination of values in column 2

For instance, I’d expect the result below:

         Red      Blue     Brown
Red   2            2            1

Red-Red would evaluate to 2 because there are 2 instances in column 1 where the items have Red in Column 2

Red-Blue would evaluate to 2 because there are two instances where the values in column A have values of both Red and Blue in column 2 (A-Red/A-Blue and C-Red/C-Blue)

Red-Brown would evaluate to 1 because C is only value in Column 1 that has a value of Red and Brown in Column 2

I hope that’s clear. I’ve tried multiple combinations of countifs/sumifs but haven’t had any luck getting the desired result. :(

解决方案

Formula based approach

The formula in Cell E2 in my solution is as below, and as it is an array formula you need to press Ctrl+Shift+Enter to make it work.

{=SUM(--ISNUMBER(MATCH(IF((Column_1&$D2=Column_1&Column_2),Column_1)&E$1,Column_1&Column_2,0)))}

Please note I have added some sample data to your original set, and I've named the following ranges:

Column_1 stands for all data in the first column A;

Column_2 stands for all data in the second column B.

My logic is to

1. Use IF function and = to find out, for a given color in Column D, what is the corresponding value in Column_1? If we are looking at Red in cell D2, my IF function will return the following {"A";FALSE;FALSE;FALSE;FALSE;FALSE;"C";FALSE;FALSE;FALSE;"D";FALSE;FALSE;FALSE;FALSE};

2. Use & to combine the range from last step with the look up value in Row 1. If we combine the range with Red in cell E1, we will have {"ARed";"FALSERed";"FALSERed";"FALSERed";"FALSERed";"FALSERed";"CRed";"FALSERed";"FALSERed";"FALSERed";"DRed";"FALSERed";"FALSERed";"FALSERed";"FALSERed"};

3. Next step is to MATCH the range from last step with the given combination which is Column_1&Column_2 that represents ARed, ABlue, AYellow, BBlue, BGreen, etc. Continue with my example the results would be {1;#N/A;#N/A;#N/A;#N/A;#N/A;7;#N/A;#N/A;#N/A;11;#N/A;#N/A;#N/A;#N/A} in which the numerical values are the positions of ARed, CRed, and DRed in the range of the given combination Column_1&Column_2.

4. The last step to use ISNUMBER to find out how many values within the range from last step are number, which shall return {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE} and then SUM up all TRUE results by converting them into numerical value 1 using --.

Power Query Based Approach

Using the same source data in my solution, A1:B16, load this to the Power Query Editor, then you should have the following:

Please note I have sorted the table by Column 2 and then Column 1 ascending consecutively. This is an optional step just to help making the following steps easier to be understood.

The next step is to merge the table with its own by matching Column 1:

Expand the new column to show Column 2:

Highlight the last column, use Pivot Column function to transform the table, then you should have:

The only problem is that the colors will be positioned differently to the source.

If it is important to keep the colors in original order, one way of doing that is to

1. create a look up table showing the desired order of the colors with an index column;
2. merge this look up table before pivot column in the first query, and match the colors in the third column, expand and sort the index column and then remove it;
3. do the Pivot Column, and then merger the look up table again, and expand, sort and remove the index column again.

Here are the codes behind the scene for reference only:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column 2", Order.Ascending}, {"Column 1", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Column 1"}, #"Sorted Rows", {"Column 1"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Column 2"}, {"Filtered Rows.Column 2"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Filtered Rows", List.Distinct(#"Expanded Filtered Rows"[#"Filtered Rows.Column 2"]), "Filtered Rows.Column 2", "Column 1", List.Count)
in
    #"Pivoted Column"

Let me know if you have any questions. Cheers :)

这篇关于EXCEL-计数具有匹配组合的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！