Excel-计算符合多个条件的唯一值 [英] Excel - Counting unique values that meet multiple criteria

问题描述

我正在尝试使用一种功能来计算电子表格中同时满足多个条件的唯一单元格的数量.

I'm trying to use a function to count the number of unique cells in a spreadsheet that, at the same time, meet multiple criteria.

给出以下示例:

A        B       C
QUANT    STORE#  PRODUCT
1        75012   banana
5                orange
6        56089   orange
3        89247   orange
7        45321   orange
2                apple
4        45321   apple

在上面的示例中，我需要知道有多少个具有有效STORE#的独特商店收到了橘子或苹果.在上述情况下，结果应为3(存储56089、89247和45321).

In the example above, I need to know how many unique stores with a valid STORE# have received oranges OR apples. In the case above, the result should be 3 (stores 56089, 89247 and 45321).

这就是我开始尝试解决问题的方式:

This is how I started to try solving the problem:

=SUM(IF(FREQUENCY(B2:B9,B2:B9)>0,1))

以上公式将得出具有有效商店编号的唯一商店的数量，而不仅仅是获得橙子或苹果的商店.如何添加这些额外条件?

The above formula will yield the number of unique stores with a valid store#, but not just the ones that have received oranges or apples. How can I add that extra criteria?

推荐答案

我的操作方式略有不同(并且有效)，但是我发布此消息的主要原因是因为我想知道公式的第二部分是否可以以某种方式制成数组公式，从而使其更短.

Mine is done a slightly different way (and it works), but the main reason I am posting this is because I was wondering if the second part of my formula could somehow be made into an array formula thus making it shorter.

要执行此操作，请按最小到最大的顺序对B列中的3列进行排序，并且由于其中的一部分当前是数组公式，请键入公式，然后按[ctrl] + [shift] + [enter].排序后的数据现在看起来像这样:

To do this, sort the 3 columns on column B from smallest to largest and since part of it is currently an array formula, type in the formula and [ctrl]+[shift]+[enter]. The sorted data now looks like this:

  A     B       C
QUANT   STORE#  PRODUCT
  7   45321   orange
  4   45321   apple
  6   56089   orange
  1   75012   banana
  3   89247   orange
  5           orange
  2           apple

公式为:

={SUM((B3:B9>0)*(C3:C9="orange"))+SUM((B3:B9>0)*(C3:C9="apple"))}-{IF(AND(OR(C3="orange",C3="apple"),OR(C4="orange",C4="apple"),B3=B4),1,0)+IF(AND(OR(C4="orange",C4="apple"),OR(C5="orange",C5="apple"),B4=B5),1,0)+IF(AND(OR(C5="orange",C5="apple"),OR(C6="orange",C6="apple"),B5=B6),1,0)+IF(AND(OR(C6="orange",C6="apple"),OR(C7="orange",C7="apple"),B6=B7),1,0)}

第一部分(用花括号括起来)很不言自明:

The first part (in curly braces) is pretty self-explanatory:

SUM((B3:B9>0)*(C3:C9="orange"))+SUM((B3:B9>0)*(C3:C9="apple")) 

我感兴趣的是尝试查看第二部分是否可以表示为数组公式.我最初想出了

What I am interested in is trying to see if the second part can be expressed as an array formula. I had initially come up with

=SUM((AND((C3:C9="orange"),OR(C4:C9="orange",C4:C9="apple")))*(B3:B9=B4:B10)*(B3:B9<>""))

但是出于某种原因，它似乎将香蕉"视为一项合法条目.因此，我不得不手动输入第二部分中的所有元素，而不是使用数组公式.任何人都对如何完成这项工作有想法吗?

but it seems to be counting "banana" as a legit entry for some reason. Therefore, I had to go with entering all elements in the 2nd part manually rather than use an array formula. Anyone have ideas as to how to get this done?

这篇关于Excel-计算符合多个条件的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！