英文:
How to calculate average etc from table containing frequences of values?
问题
在Excel表格中，一个列包含从4到10的成绩，另一列包含每个成绩的数量。如何计算这些成绩的中位数、众数和平均数？
我知道可以通过列出每个成绩，然后使用适当的函数来计算这些值。我想知道是否有Excel中的函数可以从这样的表格计算这些统计数据。
我尝试过使用AVERAGE（平均）、MEDIAN（中位数）和MODE.SNGL（众数）函数。我预计它们不会起作用，结果如我所料。
英文:
In an Excel table, one column contains grades from 4 to 10. Another column contains the number of each grade. How do I calculate the median, mode, and average of the grades?
I know I can calculate those by listing each grade and then using appropriate functions. I would like to know if there are functions in Excel which calculate these statistics from a table like this.
I tried functions AVERAGE, MEDIAN and MODE.SNGL. I expected them not to work and was right.
答案1
得分: 2
=LET(
ζ, A2:B8,
ξ, TAKE(ζ,,-1),
κ, SEQUENCE(ROWS(ξ)),
INDEX(TAKE(ζ,,1), XMATCH(SEQUENCE(SUM(ξ)), MMULT(N(κ>=TOROW(κ)), ξ), 1))
)
这是翻译好的代码部分。
英文:
Assuming your data is in A2:B8 (excluding headers), generate this array:
=LET(
ζ,A2:B8,
ξ,TAKE(ζ,,-1),
κ,SEQUENCE(ROWS(ξ)),
INDEX(TAKE(ζ,,1),XMATCH(SEQUENCE(SUM(ξ)),MMULT(N(κ>=TOROW(κ)),ξ),1))
)
which you can then pass to your desired function(s).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论