Question

我有一份被分到两所学校的孩子名单。我想统计一下这两所学校中年龄最大的孩子的数量。下面是一个例子。

我设法用在网上找到的公式计算了家族（唯一名称出现）的数量=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"))。

有没有办法直接写出单个公式？

=SUMPRODUCT((C2:C9=MINIFS(C2:C9,A2:A9,A2:A9))*(D2:D9=”A”)) — 
那么，您想要一个COUNTIFS基于学校和年份的列表，其中年份是MINIFS基于学校的吗？ —

Answer 1

根据@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()以获得相同的结果。请参阅最后一张屏幕截图，它显示它根据TRUEs返回重复项

=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以了解。屏幕截图如下所示：

你甚至不需要 SUMPRODUCT，我仍然继续以“老式 Excel”的方式思考，SUM 也可以工作。 — 
你可以尝试这个：=ROWS(UNIQUE(FILTER(A2:A9&"|"&C2:C9,(MINIFS(C2:C9,A2:A9,A2:A9)=C2:C9)*(D2:D9=A12)))) — 
@MayukhBhattacharya 哦，我现在明白了，你指的是 A12。我已将 A12 改为“A”或“B”，现在它工作正常。你愿意在回复这篇文章时提供公式的详细信息吗？我认为这会非常有价值。谢谢！ — 
我没有做任何特别的事情。我应用了和你一样的方法。如果你仔细听的话。功劳归于你和@Michal。没有什么大的区别。使用FILTER()我正在提取最年长的以及基于学校的那些，然后将其包装在内UNIQUE()以获得唯一值，最后将其包装在内ROWS()以获得计数。我没有添加它。因为你应该得到功劳 — 
@MayukhBhattacharya 我尝试提供解释。请随意编辑帖子。我不太清楚这是如何工作的。尤其是“&”|“&”。 —

Answer 2

让我给你解释一下这个公式是如何工作的。

首先：你不需要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()（但是后面这个函数不存在:-)）。

这是一个很好的解释，但我总是会添加一条附加信息，如果我有 Unique 功能，我会使用它，如果没有，我会使用 Frequency —

Answer 3

当有多个最老的时，选择第一个：

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")
    )
)

Excel 统计每所学校最年长的孩子的数量 – 代码日志

最佳答案
3

最佳答案 3

最佳答案
3