Question

在Excel中怎样用公式快速计算出阶段性对应的数值？

如图，任意一个阶段的完成率，如何用公式快速计算出L列得分值？计分规则：1、项目1：当当年完成率大于或者等于90%时，得分0分；当完成率小于90%但大于或等于80%时，得分对应0-60分......2、项目2：当1季度完成率大于或者等于40%时，得分0分；当完成率大于40%但小于或等于50%时，得分对应0-60分.......3、项目3：当当年完成率大于或等于20%时，得分0分;当完成率小于20%但大于或等于15%时，得分对应0-60分(含60分)......4、项目4：当当年完成项目数小于或者等于10时，得分0分；当完成项目数大于10但小于或等于15时，得分对应0-60分（含60分）......问题需求描述：如何用公式快速计算出图中L列的得分值（得分精确到1位或2位小数点）？

Answer 1

在L3单元格填入以下公式并向下填充其余单元格：

=ROUND(IFNA(IF(MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))=5,100,SUBSTITUTE(INDIRECT(ADDRESS(2,MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))+2)),"分",)+ABS((INDIRECT(ADDRESS(ROW(L3),MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))+2))-K3)/(INDIRECT(ADDRESS(ROW(L3),MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))+2))-INDIRECT(ADDRESS(ROW(L3),MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))+3)))*(SUBSTITUTE(INDIRECT(ADDRESS(2,MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))+2)),"分",)-SUBSTITUTE(INDIRECT(ADDRESS(2,MATCH(K3,C3:G3,IF(C3-D3>0,-1,1))+3)),"分",)))),0),1)

最终计算效果如图：

Answer 2

L3=INDEX($A$2:$G$2,MATCH(LOOKUP(K3,C3:G3),C3:G3,))
下拉公式

Answer 3

2021版，在L3:L9中输入或复制粘贴下列公式

=INDEX(C$2:G$2,XMATCH(K3,C3:G3,-1,-1))

=INDEX(C$2:G$2,MATCH(K4,C4:G4))

=INDEX(C$2:G$2,MATCH(K5,C5:G5))

=INDEX(C$2:G$2,MATCH(K6,C6:G6))

=INDEX(C$2:G$2,MATCH(K7,C7:G7))

=INDEX(C$2:G$2,XMATCH(K8,C8:G8,-1,-1))

=INDEX(C$2:G$2,MATCH(K9,C9:G9))