Yuav Paub
- IF muaj nuj nqi yog siv los ua qhov kev sim ntsuas, piv txwv li seb ib yam dab tsi tseeb los tsis yog.
- The IF function syntax thiab arguments yog =IF(logical_test, value_if_true, [value_if_false]).
- Piv txwv li =IF(A2>A3, "Biger", "smaller").
Kab lus no piav qhia yuav ua li cas siv IF muaj nuj nqi los ua qhov kev sim ntsuas txhua yam ntawm Excel, suav nrog Excel 2019 thiab Microsoft 365. Ntau qhov piv txwv tau piav qhia.
IF muaj nuj nqi yog dab tsi?
IF muaj nuj nqi hauv Excel yog siv los ua qhov kev sim ntsuas. Ib qho qauv siv lub luag haujlwm no tseem hu ua IF nqe lus lossis ib nqe lus yog / tom qab ntawd.
Txhua tus qauv uas siv cov haujlwm no tuaj yeem muaj ib qho ntawm ob qhov txiaj ntsig. Txoj kev uas nws ua haujlwm, raws li peb yuav pom hauv cov piv txwv hauv qab no, yog cov qauv tsim los kuaj seb puas muaj tseeb. Yog tias muaj tseeb, ib yam tshwm sim, tab sis yog tias nws tsis yog, lwm yam tshwm sim.
IF muaj nuj nqi yog ib qho ntawm ntau qhov laj thawj uas koj tuaj yeem siv hauv Excel. Lwm tus muaj xws li THIAB, IFERROR, IFS, TSIS, thiab LOSSIS.
IF Function Syntax & Arguments
Txhua tus qauv uas siv IF muaj nuj nqi muaj ob peb ntu rau nws:
=IF(logical_test, value_if_true, [value_if_false])
- logical_test: Tus mob koj tab tom sim. Yuav tsum tau.
- value_if_true: Yuav ua li cas yog tias qhov kev xeem_test muaj tseeb. Yuav tsum tau.
- value_if_false: Yuav ua li cas yog tias logical_test tsis tseeb. Nws yog xaiv tau.
Sau Excel IF nqe lus yooj yim yog tias koj nyeem nws txawv me ntsis: yog thawj ntu muaj tseeb, ces ua qhov no. Yog tias thawj ntu tsis tseeb, ces ua qhov no es tsis txhob.
Kua cov cai no hauv siab:
- Excel rov FALSE yog tias logical_test tsis tseeb thiab value_if_false raug tshem tawm.
- Txhawm rau xa cov ntawv xa ua tus nqi_if_true lossis tus nqi_if_false, nws yuav tsum nyob ib puag ncig hauv cov lus hais, tshwj tsis yog cov lus TRUE thiab FALSE.
- Tus IF muaj nuj nqi tsis raug teeb meem.
- Excel 2010 thiab tshiab dua tso cai rau 64 IF nqe lus kom muaj nyob hauv tib lub qauv. Cov ntawv qub ntawm Excel tau txwv rau xya.
IF Function Piv txwv
Ntawm no yog qee qhov sib txawv koj tuaj yeem siv IF cov qauv hauv Excel:
Sau ntawv yog hais tias muaj tseeb
=IF(A2>A3, "Biger", "smaller")
Qhov no yog qhov piv txwv tiag tiag ntawm IF nqe lus hauv Excel. Kev ntsuam xyuas yog saib seb A2 loj dua A3. Yog tias yog, sau Bigger, txwv tsis pub sau Smaller.
Ua lej Yog tias nqe lus muaj tseeb
=IF(A2>A3, A2-A3)
Daim ntawv IF no yog sau txawv me ntsis. Es tsis txhob muaj qhov value_if_true tshwm sim yog ib lo lus, nws yog rho tawm ib qho nqi los ntawm lwm tus. Yog li, yog A2 qhov tseeb loj dua A3, qhov sib txawv yuav yog qhov tshwm sim. Yog tias nws tsis muaj tseeb, txij li peb tau tshem tawm qhov nqi_if_false, Excel rov FALSE.
Test the Statement With Math
=IF(A2/A3=5, A2/A3, "")
Lwm txoj hauv kev los sau IF nqe lus yog los ua kev suav hauv seem logical_test. Qhov xwm txheej IF ntawm no yog A2 / A3=5. Yog tias qhov tseeb, peb ua qhov suav A2 / A3. Yog tias nws tsis sib npaug rau 5, peb xav kom qhov tshwm sim tsis muaj dab tsi, yog li peb siv ob nqe lus.
Test Yog Hnub No
=IF(A2=TODAY(), "Nov yog hnub no", "")
Lwm cov haujlwm Excel tuaj yeem siv tau hauv IF nqe lus. Hauv qhov piv txwv no, peb siv TODAY muaj nuj nqi los xyuas seb A2 yog hnub no li cas. Yog tias nws yog, tus qauv sau Qhov no yog hnub no, txwv tsis pub tsis muaj dab tsi sau.
Siv thiab Nrog Yog Tus Qauv
=IF(E2<=TODAY(), "Tam sim no", "Soon")
=IF(AND(F2="Tam sim no", D2>=(B2-C2)), "Yog", "No")
Qhov piv txwv ntawm IF muaj nuj nqi yog qhov cuam tshuam me ntsis. Lub tswv yim ntawm no yog saib seb ib yam khoom uas peb tshuav nyiaj li cas dhau los, thiab yog tias nws yog, peb tab tom pom tias cov nyiaj ntawd puas nyob hauv peb cov peev nyiaj kom peb them tau. Yog tias ob nqe lus no muaj tseeb, peb tuaj yeem pom hauv Kab G yog tias lub sijhawm them nws.
IF(E2<=TODAY(), "Tam sim no", "Soon") is in the Urgency kem. Nws qhia peb yog tias cov khoom dhau sijhawm lossis yog tias nws yuav dhau hnub no los ntawm kev sib piv hnub kawg nrog hnub no. Yog tias hnub kawg yog hnub no lossis yav dhau los, Tam sim no sau rau hauv Kab F, txwv tsis pub peb sau Tsis ntev.
Daim ntawv thib ob IF tseem raug teeb tsa zoo li IF nqe lus txawm tias AND tau siv rau hauv nws. Qhov tseem ceeb ntawm no yog qhov chaw AND muaj nuj nqi zaum, thiab txij li nws nyob hauv thawj txheej ntawm commas, nws yog qhov peb tab tom siv los ua cov logical_test:
=IF(AND(F2="Tam sim no", D2>=(B2-C2)), "Yes", "No")
Ntawm no yog sau txawv los qhia tias nws zoo ib yam li lwm nqe lus IF:
=IF(test this AND function, sau Yes yog tias muaj tseeb, lossis sau Noyog tsis muaj tseeb)
Nyob rau hauv THIAB muaj nuj nqi yog ob nqe lus IF:
- F2="Tam sim no" yog ib feem ntawm cov qauv hauv Kem G. Nws xyuas seb Tam sim no puas nyob hauv F2.
- D2>=(B2-C2) muaj ob ntu: nws xub ua qhov kev suav B2-C2 kom pom tias peb tshuav nqi npaum li cas ntawm cov khoom, thiab tom qab ntawd nws tshawb xyuas cov peev nyiaj muaj nyob hauv D2 kom pom tias peb muaj nyiaj los them nws.
Yog tias peb tshuav nyiaj tam sim no thiab peb muaj nyiaj them rov qab, peb tau hais tias Yes, yog lub sijhawm them cov khoom tawm.
Nested IF nqe lus piv txwv
Nested IF nqe lus yog qhov nws hu ua thaum ntau tshaj ib nqe lus IF suav nrog hauv cov qauv. Kev teeb tsa zoo ib yam nkaus, tab sis tsis txhob kaw lub kaw lus ntawm qhov kawg ntawm thawj txheej, peb muab tus lej cim thiab sau lwm nqe lus.
Ob IF nqe lus hauv ib qho qauv
=IF(B2="F", "Class A", IF(B2="M", "Class B"))
thawj qhov piv txwv no yog siv los txheeb xyuas cov tub ntxhais kawm los ntawm lawv cov poj niam txiv neej, qhov twg cov poj niam raug xaiv rau Chav A thiab cov txiv neej Chav B. Cov qauv tshuaj xyuas F thiab M hauv B2 thiab tom qab ntawd sau Class A lossis Chav B nyob ntawm nqe lus yog muaj tseeb.
Tus naj npawb ntawm kab lus koj xav tau tom kawg ntawm cov qauv nrog IF ua haujlwm yog tib lub sijhawm IF sau. Hauv peb qhov piv txwv, IF tau sau ob zaug, yog li peb xav tau ob kab lus kawg.
Peb nqe lus hauv ib qho qauv
=IF(A2=TODAY(), "Nov yog hnub no", IF(A2TODAY(), "Hnub tom ntej")))
Nov yog ib qho piv txwv ntawm cov qauv nrog ntau cov lus IF. Nws zoo ib yam rau hnub no piv txwv saum toj no tab sis nrog ib qho kev sim ntau ntxiv:
- Thawj daim tshev yog A2 hnub no hnub thiab rov los Qhov no yog hnub no yog nws yog.
- Qhov kev xeem thib ob yog hnub no ntau dua A2 txhawm rau txiav txim siab tias A2 yog hnub qub, thiab rov qab hnub qub yog tias nws yog.
- Thaum kawg, muaj qhov kev sim kom pom tias hnub no hnub tsawg dua hnub hauv A2, thiab cov qauv rov qab hnub tom ntej yog tias nws yog.
Cim nqe Yog hais tias lus dag
=IF(C2="Bill", "", IF(C2="Food", "", B2))
Nyob rau hauv qhov kawg nested IF qauv piv txwv, peb yuav tsum tau ceev nrooj txheeb xyuas tag nrho cov nqi ntawm tag nrho cov kev yuav khoom uas tsis poob rau hauv ib pawg. Peb tau sau tag nrho peb cov khoom tsis tsim nyog, thiab nrog cov npe ntev, qhov no yog txoj hauv kev zoo tshaj plaws los ua. Peb tau txiav txim siab tias txhua yam khoom piav qhia uas hais tias Bill lossis Khoom Noj yog qhov tseem ceeb, yog li tus nqi, hauv B2, yuav tsum tau qhia rau txhua yam khoom.
Qhov no yog qhov tshwm sim:
- C2="Bill", "": Yog C2 hais tias Bill, tso lub xovtooj tawm.
- C2="Food", "": Yog C2 hais tias zaub mov, cia lub cell blank.
- B2: Yog ob nqe lus no tsis tseeb, sau dab tsi hauv B2.
Yuav ua li cas cov qauv no tso rau peb nrog yog cov npe ntawm cov nqi uas peb tuaj yeem ua tiav nrog SUM ua haujlwm txhawm rau ntsuas sai npaum li cas cov nyiaj tau siv rau cov khoom uas peb tsis xav tau.
Ib txoj hauv kev yooj yim sau Nested IF nqe lus
Raws li koj tsim ntau thiab ntau dua rau hauv cov qauv, nws tuaj yeem ua tsis tau sai thiab nyuaj rau kho tom qab. Ib txoj hauv kev los ua kom nested IF nqe lus yooj yim ua hauj lwm nrog yog muab ib kab so tom qab txhua nqe lus, zoo li qhov no:
=
IF(A2=TODAY(), "Nov yog hnub no",
IF(A2<TODAY(), "Old date",
IF(A2IF(A2>TODAY(), "Hnub tom ntej")))
Txhawm rau ua qhov no hauv Excel, peb yuav tsum hloov kho los ntawm tus qauv bar:
- Xaiv tus qauv bar rau saum Excel.
- Muab tus nas tso rau hauv qhov chaw ntawv kom txog thaum tus cursor hloov mus rau ob sab xub, thiab tom qab ntawd nyem thiab rub lub thawv rau hauv kom muaj chaw ua haujlwm ntau dua.
- Muab tus cursor tom qab kos npe sib npaug thiab nias Alt+Enter (Windows) lossis Ctrl + Option + Enter (Mac). Qhov no tso cov mis rau ntawm kab tshiab.
-
Rov Ua Kauj Ruam 3 ua ntej txhua nqe lus IF kom txhua qhov piv txwv muab tso rau ntawm nws tus kheej kab.
Image
