excel同时满足两个数值的最接近数值

发布网友发布时间：2023-07-20 14:34

我来回答

共3个回答

热心网友时间：2024-10-24 12:50

接近小

=VLOOKUP(LARGE(IF(A3:A14<=0,A3:A14),1),$A$3:$D$14,2,0)

=VLOOKUP(LARGE(IF(A3:A14<=0,A3:A14),1),$A$3:$D$14,3,0)

=VLOOKUP(LARGE(IF(A3:A14<=0,A3:A14),1),$A$3:$D$14,4,0)

接近大

=VLOOKUP(SMALL(IF(A3:A14>0,A3:A14),1),$A$3:$D$14,2,0)

=VLOOKUP(SMALL(IF(A3:A14>0,A3:A14),1),$A$3:$D$14,3,0)

=VLOOKUP(SMALL(IF(A3:A14>0,A3:A14),1),$A$3:$D$14,4,0)

输入公式后按 ctrl+shift+enter

热心网友时间：2024-10-24 12:50

G5=IFERROR(INDEX($D$4:$D$15,MATCH(H4&I4,$B$4:$B$15&$C$4:$C$15,0)),INDEX($D$4:$D$15,MATCH(MIN(--TEXT(($B$4:$B$15*$C$4:$C$15-H4*I4)*(B4:B15>=H4)*(C4:C15>=I4),"0.000;9999999;999999999")),--TEXT(($B$4:$B$15*$C$4:$C$15-H4*I4)*(B4:B15>=H4)*(C4:C15>=I4),"0.000;9999999;999999999"),0)))
数组公式

热心网友时间：2024-10-24 12:51

“h3&i3”是个什么意思？是想表达“h3+i3”吗？