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

查看：36
本文介绍了EXCEL- 计算具有匹配组合的实例的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我非常擅长 excel，但由于某种原因无法找到解决这个问题的正确方法(也许我把它复杂化了):

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

第1栏第2栏红色蓝色黄色B 蓝色B 绿色B 布朗C 红色C 蓝色C布朗

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

例如，我希望结果如下:

 红蓝棕红色 2 2 1

Red-Red 的计算结果为 2，因为第 1 列中有 2 个实例，其中项目在第 2 列中具有红色

Red-Blue 的计算结果为 2，因为有两个实例，其中 A 列中的值在第 2 列中同时具有红色和蓝色值(A-Red/A-Blue 和 C-Red/C-Blue)

Red-Brown 的计算结果为 1，因为 C 只是第 1 列中的值，而第 2 列中的值为 Red 和 Brown

我希望这很清楚.我已经尝试了 countifs/sumifs 的多种组合，但没有任何运气得到想要的结果.:(

解决方案

基于公式的方法

我的解决方案中单元格E2中的公式如下，因为它是一个数组公式，你需要按Ctrl+Shift+输入使其工作.

{=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 中查看 Red，我的 IF 函数将返回以下内容{"A";FALSE;FALSE;FALSE;FALSE;FALSE;"C";FALSE;FALSE;FALSE;"D";FALSE;FALSE;FALSE;FALSE};

  2. 使用 & 将上一步的范围与第 1 行中的查找值组合起来.如果我们将范围与 Red<组合起来/strong> 在单元格 E1 中，我们将有{"ARed";"FALSERed";"FALSERed";"FALSERed";"FALSERed";"FALSERed";"CRed";"FALSERed";"FALSERed";"FALSERed";"DRed";"FALSERed";"FALSERed";"FALSERed";"FALSERed"};

  3. 下一步是匹配上一步的范围与给定的组合，即代表ARed的Column_1&Column_2，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 来SUM强>.

<块引用>

基于 Power Query 的方法

在我的解决方案中使用相同的源数据 A1:B16，将其加载到 Power Query 编辑器，然后您应该有以下内容:

请注意，我已按第 2 列和第 1 列连续排序.这是一个可选步骤，只是为了帮助您更容易理解以下步骤.

下一步是通过匹配列 1 将表格与自己的表格合并:

展开新列以显示第 2 列:

突出显示最后一列，使用Pivot Column功能来变换表格，那么你应该有:

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

如果保持颜色的原始顺序很重要，一种方法是

  1. 创建一个查找表，用索引列显示所需的颜色顺序；
  2. 合并这个查找表在第一个查询中的数据透视列之前，并匹配第三列中的颜色，展开和排序索引列，然后将其删除；
  3. 做Pivot Column，然后再次合并查找表，再次展开、排序和删除索引列.

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

letSource = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],#"更改类型" = 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}}),#"合并查询" = Table.NestedJoin(#"Sorted Rows", {"Column 1"}, #"Sorted Rows", {"Column 1"}, "Filtered Rows", JoinKind.LeftOuter),#"Expanded Filtered Rows" = Table.ExpandTableColumn(#"合并查询", "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)在#旋转柱"

如果您有任何问题，请告诉我.干杯:)

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屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆