我有一份被分到两所学校的孩子名单。我想统计一下这两所学校中年龄最大的孩子的数量。下面是一个例子。
我设法用在网上找到的公式计算了家族(唯一名称出现)的数量=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
。我不清楚这个公式是如何工作的,但它似乎提供了正确的答案。
现在我想统计一下有多少个家庭的长子在 A 学校上学(3 个:史密斯一家、泰勒一家和威廉姆斯一家),又有多少个家庭的长子在 B 学校上学(2 个:琼斯一家和布朗一家)。
编辑:我希望公式中只计算双胞胎中的一个长子(例子中的罗伯特和琳达泰勒)。
我通过在 F、G 和 H 列中添加中间公式来实现这一点:
- F2 中的公式:
=MINIFS($C$2:$C$9,$A$2:$A$9,$A$2:$A$9)
- G2 中的公式:
=A2&F2
- H2 中的公式:
=(COUNTIF($G$2:$G2,$G2)=1)+0
最后,计算出最年长的孩子在 A 学校(B12)就读的家庭数量:=SUMPRODUCT((H2:H9)*(D2:D9="A"))
。
有没有办法直接写出单个公式?
3
最佳答案
3
根据@Michal 和@Mayukh Bhattacharya 的回答,该示例使用的公式是:
- 家中最大孩子在校上学的家庭数量 A:
=ROWS(UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9="A"))))
- 最年长的孩子在 B 校上学的家庭数量:
=ROWS(UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9="B"))))
- 使用
MINIFS()
函数获取每个姓氏的最低子女出生年份:
MINIFS(C2:C9,A2:A9,A2:A9)
- 与儿童出生年份范围进行比较,得到
TRUE
和FALSE
,其中TRUE
表示匹配的
=MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9
- 将这些与这里的一所学校相匹配
A
,它返回TRUE
和,FALSE
并且当有数组乘法时,TRUE
上面的匹配给出1
而另一个0
(D2:D9=A12)
返回 1 和 0
=(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12)
- 现在,包裹在内
FILTER()
以提取年龄最大的孩子以及基于学校;合并为一个ColA
和C
=FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12))
- 因为,我们需要唯一计数,因此将其包装在内
UNIQUE()
以获得相同的结果。请参阅最后一张屏幕截图,它显示它根据TRUE
s返回重复项
=UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12)))
- 最后,包装在内部
ROWS()
以获取数组的计数。也可以使用COUNTA()
,但就我个人而言,我更喜欢使用SUM()
=ROWS(UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12))))
另外,SUM()
=SUM(N(UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12)))<>""))
而且看起来您有权访问UNIQUE()
并且也FILTER()
应该有权访问LET()
,那么为什么不定义变量以避免使用重复的范围/函数并通过减少冗余(如果有)来提高性能呢?
=LET(
a, A2:A9,
b, C2:C9,
c, MINIFS(b,a,a),
SUM(N(UNIQUE(FILTER(a&"|"&b,(c=b)*(D2:D9=A12)))<>"")))
建议:如果你在理解公式时遇到问题,更好的方法是逐一写下每个使用的公式或选择公式单元格,转到Formulas
选项卡 –>Formula Auditing
组下 –> 选择Evaluate Formula
–> 单击Evaluate
以了解。屏幕截图如下所示:
12
-
2你甚至不需要 SUMPRODUCT,我仍然继续以“老式 Excel”的方式思考,SUM 也可以工作。
– -
1你可以尝试这个:
=ROWS(UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12))))
– -
1@MayukhBhattacharya 哦,我现在明白了,你指的是 A12。我已将 A12 改为“A”或“B”,现在它工作正常。你愿意在回复这篇文章时提供公式的详细信息吗?我认为这会非常有价值。谢谢!
– -
1我没有做任何特别的事情。我应用了和你一样的方法。如果你仔细听的话。功劳归于你和@Michal。没有什么大的区别。使用
FILTER()
我正在提取最年长的以及基于学校的那些,然后将其包装在内UNIQUE()
以获得唯一值,最后将其包装在内ROWS()
以获得计数。我没有添加它。因为你应该得到功劳
–
-
1@MayukhBhattacharya 我尝试提供解释。请随意编辑帖子。我不太清楚这是如何工作的。尤其是“&”|“&”。
–
|
让我给你解释一下这个公式是如何工作的。
首先:你不需要SUMPRODUCT()
,SUM()
就足够了。
接下来,情况是SUM(1/COUNTIF(...)
:假设你有三个条目:“a”、“b”和“c”。它们每个都有不同的出现次数,如你所见:
入口 | 发生次数 | 发生次数的倒数 |
---|---|---|
一个 | 3 | 1/3 |
一个 | 3 | 1/3 |
一个 | 3 | 1/3 |
b | 1 | 1/1 |
丙 | 4 | 1/4 |
丙 | 4 | 1/4 |
丙 | 4 | 1/4 |
丙 | 4 | 1/4 |
计算这些倒数的总和,您将得到1/3 + 1/3 + 1/3 (three times) + 1/1 (one time) + 1/4 + 1/4 + 1/4 + 1/4 (four times)
,从而得到1 + 1 + 1
,即条目的数量。
这样,你就可以理解公式=SUM(1/COUNTIF()
实际上是一种获得的方法COUNT_DISTINCT()
(但是后面这个函数不存在:-))。
2
-
1非常感谢!太清楚了!
– -
这是一个很好的解释,但我总是会添加一条附加信息,如果我有 Unique 功能,我会使用它,如果没有,我会使用 Frequency
–
|
当有多个最老的时,选择第一个:
TAKE(FILTER(first, (family = fn) * (birth_year = eldests_year)),1))
=LET(data, A2:D9, family, INDEX(data, , 1), first, INDEX(data, , 2), birth_year, INDEX(data, , 3), school, INDEX(data, , 4),
u_family, UNIQUE(family),
eldests_year, MINIFS(birth_year, family, family),
eldest_one, MAP(
u_family,
LAMBDA(fn, TAKE(FILTER(first, (family = fn) * (birth_year = eldests_year)), 1))
),
in_school, LAMBDA(fn,eldest,sch,
SUM((family = fn) * (first = eldest) * (school = sch))
),
num_eldest_in_school, LAMBDA(sch,
HSTACK(
"# eldest in school " & sch,
SUM(MAP(u_family, eldest_one, EXPAND(sch, ROWS(u_family), , sch), in_school))
)
),
VSTACK(
HSTACK("# families", ROWS(u_family)),
num_eldest_in_school("A"),
num_eldest_in_school("B")
)
)
|
–
COUNTIFS
基于学校和年份的列表,其中年份是MINIFS
基于学校的吗?–
–
|