如何在Excel中计算多列数据的平均排名（忽略空白单元格）

需求说明 #

你有按日期排列的多列时间数据，需计算每行的平均排名：同一日期列内时间值越大排名越靠前，空白单元格忽略不计，且列数多达数百列无法手动逐个处理。

方法1：Excel 365/2021（动态数组） #

假设数据区域为 A2:D4（A列是property，B~D为日期列，可扩展至任意列数），在E2单元格输入以下公式，回车后自动溢出填充所有行：

=AVERAGE(IF(B2:D2<>"",RANK(B2:D2,B$2:D$4,0),""))

公式拆解 #

• IF(B2:D2<>"", ... , "")：筛选当前行的非空单元格，仅对这些单元格计算排名，空值返回空白（会被AVERAGE自动忽略）
• RANK(B2:D2,B$2:D$4,0)：对当前单元格的值，在对应日期列的所有行中做降序排名（参数0表示降序，值越大排名越靠前）；B$2:D$4固定整个数据区域，确保每列排名取该列全部有效值
• AVERAGE(...)：计算所有有效排名的平均值，自动跳过空白值

方法2：旧版Excel（非动态数组） #

针对Excel 2019及更早版本，需使用数组公式：在E2单元格输入公式后，按 Ctrl+Shift+Enter 确认，再下拉填充到其他行：

=AVERAGE(IF(B2:D2<>"",RANK(B2:D2,B$2:D$4,0),""))

额外需求：显示排名计算过程 #

如果需要像示例那样展示(1+1+3)/3这类计算式，可使用以下公式：

=TEXTJOIN("+",TRUE,IF(B2:D2<>"",RANK(B2:D2,B$2:D$4,0),""))&"/"&COUNT(B2:D2)

• Excel 365直接回车，旧版按Ctrl+Shift+Enter
• TEXTJOIN拼接有效排名的求和项，COUNT(B2:D2)统计当前行的非空列数作为分母

内容的提问来源于stack exchange，提问作者apostofes