Txoj hauv kev zoo tshaj plaws kom nkag siab yuav ua li cas muab MIN thiab IF ua haujlwm hauv Excel yog nrog piv txwv. Qhov piv txwv qhia no muaj lub sijhawm kub kub rau ob qhov xwm txheej los ntawm kev sib ntsib-lub 100 thiab 200-meter sprints, thiab siv rau Excel rau Microsoft 365, Excel 2019, Excel 2016, Excel 2013, thiab Excel 2010.
MIN Yog Array yog dab tsi?
Siv MIN IF array formula tso cai rau koj pom lub sijhawm kub ceev tshaj plaws rau txhua haiv neeg nrog ib tus qauv.
Txoj haujlwm ntawm txhua feem ntawm cov qauv yog raws li hauv qab no:
Lub luag haujlwm MIN pom lub sijhawm nrawm tshaj plaws lossis tsawg tshaj plaws rau qhov kev tshwm sim xaiv.
IF muaj nuj nqi tso cai rau peb xaiv haiv neeg los ntawm kev teeb tsa tus mob siv cov npe haiv neeg.
Cov qauv array cia IF muaj nuj nqi kuaj rau ntau yam mob hauv ib lub xovtooj, thiab thaum qhov xwm txheej tau ua tiav, cov qauv array txiav txim siab seb cov ntaub ntawv twg (cov sijhawm sib tw) MIN muaj nuj nqi tshuaj xyuas kom pom lub sijhawm ceev tshaj plaws.
MIN YOG Nested Formula Syntax thiab Arguments
Tus syntax rau MIN IF qauv yog:
Vim tias IF muaj nuj nqi yog ze rau hauv MIN muaj nuj nqi, tag nrho IF muaj nuj nqi dhau los ua ib qho kev sib cav rau MIN muaj nuj nqi
Cov lus sib cav rau IF muaj nuj nqi yog:
logical_test (yuav tsum tau) - Tus nqi lossis kev qhia uas raug kuaj xyuas seb nws puas yog qhov tseeb lossis cuav.
value_if_true (yuav tsum tau) - Tus nqi uas tso tawm kom pom yog tias qhov kev ntsuas qhov tseeb.
value_if_false (optional) - Tus nqi uas tso tawm kom pom yog tias cov logical_test tsis tseeb.
Hauv qhov piv txwv, qhov kev sim ntsuas sim nrhiav qhov sib tw rau lub npe haiv neeg ntaus rau hauv cell D10 ntawm daim ntawv ua haujlwm. Qhov kev sib cav value_if_true yog, nrog kev pab ntawm MIN muaj nuj nqi, lub sijhawm ceev tshaj plaws rau cov haiv neeg xaiv. Tus nqi_if_false argument raug tshem tawm vim nws tsis xav tau thiab nws qhov tsis tuaj yeem ua rau cov qauv luv. Yog hais tias ib lub npe haiv neeg uas tsis nyob rau hauv cov ntaub ntawv cov ntaub ntawv, xws li cov haiv neeg 400-meter, yog ntaus rau hauv cell D10, cov mis rov qab ib tug xoom.
Excel's MIN IF Array Formula Piv txwv
Nkag mus rau cov ntaub ntawv qhia hauv qab no rau hauv hlwb D1 txog E9:
Lub Sijhawm Sib Tw
Lub Sijhawm Sib Tw (sec)
100 meters 11.77
100 meters 11.87
100 meters 11.83
200 meters 21.54
200 meters 21.50
200 meters 21.49
Race Fastest Heat (sec)
Hauv xov tooj D10, ntaus "100 meters" (tsis muaj cov lus qhia). Cov mis yuav saib hauv lub xovtooj no kom pom cov haiv neeg twg uas koj xav kom nws nrhiav lub sijhawm sai tshaj plaws rau.
Image
Nkag mus rau MIN yog Nested Formula
Vim tias koj tab tom tsim ob qho tib si qauv thiab cov qauv array, koj yuav tsum ntaus tag nrho cov qauv rau hauv ib daim ntawv ua haujlwm.
Tom qab koj nkag mus rau cov qauv tsis txhob nias tus yuam sij Enter ntawm cov keyboard lossis nyem lub xov tooj sib txawv nrog tus nas; koj yuav tsum tig tus qauv mus rau hauv ib qho array formula. Txhawm rau ua qhov ntawd, xaiv lub xov tooj ntawm tes E10, qhov chaw nyob qhov twg cov txiaj ntsig tau tshwm sim, thiab hom:
=MIN(IF(D3:D8=D10, E3:38))
Creating the Array Formula
Tam sim no koj tau nkag mus rau MIN IF qauv, koj yuav tsum hloov nws mus rau ib qho array. Ua raws li cov kauj ruam no los ua.
Nias thiab tuav lub Ctrl thiab Shift tus yuam sij ntawm cov keyboard.
Nias lub Enter tus yuam sij ntawm cov keyboard los tsim cov qauv array.
Cov lus teb 11.77 tshwm hauv cell F10 vim tias nws yog lub sijhawm ceev tshaj plaws (tsawg tshaj) rau peb 100 meter sprint heats.
Cov qauv array tiav tshwm nyob rau hauv daim ntawv qhia bar saum daim ntawv ua haujlwm.
Vim Ctrl, Shift, thiab Enter tus yuam sij ntawm cov keyboard raug nias ib txhij Tom qab cov qauv tau ntaus ntawv lawm, cov qauv tsim tau qee zaum hu ua CSE cov qauv.
Test the Formula
Tsim cov qauv los ntawm kev nrhiav lub sijhawm ceev tshaj plaws rau 200 meters. Ntaus 200 meters rau hauv cell D10 thiab nias lub Enter tus yuam sij ntawm cov keyboard. Cov qauv yuav tsum rov qab lub sijhawm 21.49 vib nas this hauv cell E10.
