请帮忙简化公式

yangyanbin

请帮忙简化公式=IF(H3=MIN($H3:$AB3),$H$1,"")&IF(I3=MIN($H3:$AB3),$I$1,"")&IF(J3=MIN($H3:$AB3),$J$1,"")&IF(K3=MIN($H3:$AB3),$K$1,"")&IF(L3=MIN($H3:$AB3),$L$1,"")&IF(M3=MIN($H3:$AB3),$M$1,"")&IF(N3=MIN($H3:$AB3),$N$1,"")&IF(O3=MIN($H3:$AB3),$O$1,"")&IF(P3=MIN($H3:$AB3),$P$1,"")&IF(Q3=MIN($H3:$AB3),$Q$1,"")&IF(R3=MIN($H3:$AB3),$R$1,"")&IF(S3=MIN($H3:$AB3),$S$1,"")&IF(T3=MIN($H3:$AB3),$T$1,"")&IF(U3=MIN($H3:$AB3),$U$1,"")&IF(V3=MIN($H3:$AB3),$V$1,"")&IF(W3=MIN($H3:$AB3),$W$1,"")&IF(X3=MIN($H3:$AB3),$X$1,"")&IF(Y3=MIN($H3:$AB3),$Y$1,"")&IF(Z3=MIN($H3:$AB3),$Z$1,"")&IF(AA3=MIN($H3:$AB3),$AA$1,"")&IF(AB3=MIN($H3:$AB3),$AB$1,"")                                                 请帮忙简化公式用VBA          谢谢！

yangyanbin

请帮忙简化公式

zjdh

AC3=IFERROR(INDEX($H$1:$AB$1,MATCH(MIN($H3:$AB3),$H3:$AB3,)),"")  下拉

yangyanbin

谢谢各位大师！您们简化的公式只能选出一个公司，同时报价为相同低价二个以上的公司就选不出来了。

zjdh

就用VBA吧
Sub TEST()
    Dim AR(1 To 100)
    For I = 3 To Range("A65536").End(3).Row
        N = Application.Min(Range(Cells(I, "H"), Cells(I, "AB")))
        For J = 8 To 28
            If Cells(I, J) = N Then AR(I - 2) = AR(I - 2) & Cells(1, J).Value
        Next
    Next
    Range("AC3").Resize(I - 2) = Application.Transpose(AR)
End Sub

zjdh

请帮忙简化格式.rar (8.92 KB, 下载次数: 4)

2018-9-12 12:58 上传

点击文件名下载附件