一、A15单元格输入以下公式按【ctrl+shift+enter】下拉即可
- =IFERROR(INDEX(A:A,SMALL(IF(B$3:K$8,ROW($3:$8)),ROW(A1))),"")
复制代码
解释:首先判断B3:K8是否不等于0,返回对应的行号,结果是- {3,FALSE,FALSE,FALSE,3,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,4,4,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,5,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;6,FALSE,FALSE,FALSE,FALSE,FALSE,6,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,7,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,8,FALSE}
复制代码 ,然后small从小到大逐渐提取,分别是3,3,4,4,……最后用index取对应行号的值
二、B15单元格输入以下公式按【ctrl+shift+enter】下拉即可
- =IFERROR(INDEX($2:$2,MOD(SMALL(IF(B$3:K$8,ROW($3:$8)/1%+COLUMN(B:K)),ROW(A1)),100)),"")
复制代码
解释:大致意思跟第一个差不多,行号乘以100+列号,同样的从小到大排序,- {302,FALSE,FALSE,FALSE,306,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,403,404,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,505,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;602,FALSE,FALSE,FALSE,FALSE,FALSE,608,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,709,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,810,FALSE}
复制代码
因为列号最多就2位数,所以呢,用mod函数取余,返回列号,同样是用index函数取得相应的值。
三、C15单元格输入以下公式回车下拉即可
- =SUMPRODUCT(B$3:K$8*(A$3:A$8=A15)*(B$2:K$2=B15))
复制代码 这个不解释了很简单了
四、D15单元格输入以下公式回车下拉即可- =SUMPRODUCT(M$3:V$8*(A$3:A$8=A15)*(B$2:K$2=B15))
复制代码
|