Excel公式：根据另一行中的相应值计算一行中的唯一值 [英] Excel Formula: Count Unique Values in a Row Based on Corresponding Value in Another Row

问题描述

我一直在努力争取到这一段时间，我尝试了一些不同的方法，无法得到我正在寻找的结果。我有一个类似于Excel的数据集（对于格式化抱歉，我不能发布图像）：

I've been trying to get this one for quite a while and I've tried a few different approaches and can't get the result I'm looking for with any of them. I have a dataset similar to the below in Excel (apologize for the formatting, I can't yet post images):

          Manager 1     Manager 1       Manager 2        Manager 2
Issuer 1          0           0               0                  0  
Issuer 2        100         100               0                100
Issuer 3        100           0             100                  0
Issuer 4          0           0               0                  0

我正在计算与每个经理相关联的唯一发行人的数量（关联被定义为具有值> 0）。每个发行人都是唯一的（仅在列表中显示一次），但每个经理可以多次出现。我试图为每个经理获得一个号码。所以，结果将如下所示：

I'm trying to count the number of unique Issuers associated with each Manager ("associated" being defined as having a value > 0). Each Issuer is unique (only shows up once in the list) but each Manager can show up multiple times. I'm trying to get one number for each Manager. So, the results would be as follows:

经理1：2（不是3，因为发卡行2出现了经理1的两倍，而我正在寻找

Manager 1: 2 (not 3, because Issuer 2 shows up twice for Manager 1, and I'm looking for unique values so it would only be counted once)

经理2：2

我可以写一个公式来计算每个经理的大于零的结果总数，而不是唯一大于零的结果总数。我尝试过SUMPRODUCT和DCOUNT的变体，但我没有得到正确的结果。我也可以为我想要实现的宏编写一个宏，但我更喜欢一个公式（部分是为了看看如何做到这一点）。任何帮助是非常感谢！

I can write a formula to count the total number of greater-than-zero results for each Manager, but not the total number of unique greater-than-zero results. I've tried variations of SUMPRODUCT and DCOUNT but I'm not getting the correct result. I could also write a macro for what I'm trying to achieve but I'd prefer a formula (partly for the challenge in seeing how it can be done). Any help is greatly appreciated!

推荐答案

让 MRange 成为1x？经理姓名范围， VRange 是数值范围。

Let MRange be the 1x? range of manager names, and VRange be the range of number values.

如果您想要的经理的标签在A1中，获得唯一的发行人数量：

If the label for the manager you want is in A1, get the unique number of issuers with:

{=SUM(N(MMULT(VRange,TRANSPOSE(N(MRange=A1)))>0))}

确保使用 ctrl + shift + enter （请注意，在这种情况下， sackroduct hack不起作用。）

Be sure to enter using ctrl+shift+enter (Note that the sumproduct hack doesn't work in this case.)

这篇关于Excel公式：根据另一行中的相应值计算一行中的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！