Yuav ua li cas sau ntawv mis hauv Excel? Kev kawm Cov qauv tsim nyog tshaj plaws

Zoo tav su

Ib zaug dhau ib zaug, sau ntawv mis hauv Excel koj tus kheej yog ib qho zoo kawg rau kuv. Thiab txawm tias lub fact tias kuv feem ntau tau ua hauj lwm nyob rau hauv qhov kev pab cuam, Kuv tsis tau dab tsi tab sis ntawv ...

Raws li nws muab, feem ntau ntawm cov qauv no tsis muaj dab tsi nyuab thiab nws yog ib qho yooj yim mus ua hauj lwm nrog lawv, txawm rau ib tug novice computer neeg siv. Hauv tsab xov xwm, cia li, kuv xav qhia cov qauv tsim nyog tshaj plaws, uas feem ntau tau ua hauj lwm ...

Thiab yog li ntawd, peb pib ...

1. Kev pib ua haujlwm thiab kev qhia. Excel kev kawm.

Txhua yam hauv tsab xov xwm yuav tsum tau qhia rau hauv Excel version 2007.

Tom qab pib qhov kev pab cuam Excel - lub qhov rais tshwm nrog ntau lub hlwb - peb cov lus. Lub ntsiab ntawm qhov kev pab cuam yog qhov uas nws tuaj yeem nyeem tau (raws li lub laij lej) koj daim qauv uas koj sau. Los ntawm txoj kev, koj tuaj yeem ntxiv ib qho formula rau txhua lub cell!

Cov mis yuav tsum pib ntawm qhov "=" kos npe. Qhov no yog qhov yuav tsum ua ua ntej. Tom qab, koj sau dab tsi koj yuav tsum xam: piv txwv, "= 2 + 3" (tsis muaj quotes) thiab nias sau - vim li ntawd koj yuav pom tias qhov tshwm sim tau tshwm sim nyob rau hauv lub xov tooj ntawm "5". Saib screenshot hauv qab no.

Nws tseem ceeb heev! Dua li ntawm qhov tseeb tias tus naj npawb "5" yog sau hauv cell A1, nws yog xam los ntawm cov mis ("= 2 + 3"). Yog hais tias nyob rau hauv lub xovtooj tom ntej koj tsuas sau "5" nrog cov ntawv - ces thaum koj hla tus cursor ntawm cov xovtooj ntawm no - hauv tus qauv editor (kab saum toj no, Fx) - koj yuav pom tus zauv cim "5".

Tam sim no xav hais tias nyob hauv ib lub xov tooj ntawm tes koj tuaj yeem sau tsis yog ntawm tus nqi 2 + 3, tiam sis cov zauv ntawm lub hlwb uas nws qhov kev ntseeg koj xav tau ntxiv. Piv txwv li "= B2 + C2".

Teb zoo! Lus hauv no teb Lawm, yuav tsum muaj ib co zauv hauv B2 thiab C2, txwv tsis pub Excel yuav qhia peb hauv Cell A1 ib qho kev sib npaug zos rau 0.

Thiab ib qho tseem ceeb ntxiv ...

Thaum koj luam ib lub xov tooj uas muaj ib qho qauv, piv txwv li, A1 - thiab muab nws tso rau hauv lwm lub xov tooj, tsis yog tus nqi "5" tau theej, tiam sis yog tus qauv!

Ntxiv mus, qhov formula yuav hloov ncaj qha: yog A1 tau theej rau A2 - ces tus qauv hauv cell A2 yuav sib npaug rau "= B3 + C3". Excel yeej pauv koj tus kheej cov mis: yog A1 = B2 + C2, ces nws yog qhov logic uas A2 = B3 + C3 (tag nrho cov zauv ntau ntxiv los ntawm 1).

Qhov tshwm sim, los ntawm txoj kev, yog A2 = 0, txij thaum hlwb B3 thiab C3 tsis teem, thiab yog li ntawd sib npaug zos rau 0.

Nyob rau hauv txoj kev no, koj tuaj yeem sau ib qho mis ib zaug, thiab tom qab ntawd muab luam tawm rau txhua lub hlwb ntawm yam column - thiab Excel nws tus kheej yuav xam hauv txhua kab ntawm koj lub rooj!

Yog tias koj tsis xav B2 thiab C2 hloov thaum luam thiab muaj ib txwm txuas rau cov hlwb, cia li ntxiv "$" cim rau lawv. Ib qho piv txwv hauv qab no.

Yog li, txhua qhov chaw koj luam cell A1, nws yuav nco ntsoov mus txuas rau cov hlwb.

2. Ntxiv ntawm qhov tseem ceeb hauv cov hlua (formula SUM thiab SUMMESLIMN)

Koj tuaj yeem, tau kawg, ntxiv txhua lub xov tooj, ua tus qauv A1 + A2 + A3, thiab lwm yam. Tab sis nyob rau hauv kev txiav txim tsis raug kev txom nyem ntau, nyob rau hauv Excel muaj ib tug tshwj xeeb formula uas yuav ntxiv li tag nrho cov qhov tseem ceeb nyob rau hauv lub hlwb uas koj xaiv!

Ua ib qho piv txwv yooj yim. Muaj ntau ntau yam khoom hauv Tshuag, thiab peb paub ntau npaum li cas txhua yam khoom nyob hauv kg. yog nyob rau hauv Tshuag. Cia sim sim seb ntau npaum li cas hauv lub qe. cargo nyob rau hauv Tshuag.

Ua li no, mus rau ntawm tes uas qhov tshwm sim yuav muab tso tawm thiab sau tus qauv mis: "= SUM (C2: C5)". Saib screenshot hauv qab no.

Yog li ntawd, tag nrho cov hlwb nyob rau hauv cov kev xaiv yuav raug sau, thiab koj yuav pom qhov tshwm sim.

2.1. Ntxiv nrog rau kev mob (nrog rau cov neeg mob)

Tam sim no xav hais tias peb muaj tej yam kev mob, i.e. nws tsis tas yuav ntxiv tag nrho cov qhov tseem ceeb hauv lub hlwb (Kg, hauv Tshuag), tiam sis tsuas yog cov hais tseg, hais, nrog tus nqi (1 kg) tsawg tshaj 100.

Rau qhov no nws yog ib qho zoo mis "SUMMESLIMN"Tam sim ntawd ib qho piv txwv, thiab ces piav qhia txog txhua lub cim hauv tus qauv.

= SUMMESLIMN (C2: C5; B2: B5; "<100")qhov twg:

C2: C5 - Cov kem ntawd (cov hlwb), uas yuav muab ntxiv;

B2: B5 - kem ntawm qhov ntsuas yuav raug kuaj (xws li nqe, piv txwv, tsawg dua 100);

"<100" - tus mob nws tus kheej, nco ntsoov tias tus mob yog sau nyob rau hauv quotes.

Yog tsis muaj dab tsi nyuab rau hauv cov mis no, qhov tseem ceeb tshaj plaws yog los soj ntsuam kev sib npaug: C2: C5; B2: B5 yeej muaj tseeb; C2: C6; B2: B5 tsis yog lawm. Ie qhov sib xyaw kom zoo thiab qhov ntau yam mob yuav tsum tau proportionate, txwv tsis pub lub mis yuav rov ua yuam kev.

Nws tseem ceeb heev! Tej zaum yuav muaj ntau yam kev mob rau tus nqi, i.e. Koj tuaj yeem tshawb tsis tau los ntawm kab 1, tab sis los ntawm 10 ib zaug, los ntawm kev teev cov txheej xwm.

3. Suav tus naj npawb ntawm cov kab uas txaus siab rau cov xwm txheej (lub COUNTIFSLIMN)

Ib txoj haujlwm ua haujlwm heev yog los xam tsis tag nrho ntawm qhov tseem ceeb hauv lub hlwb, tiam sis tus naj npawb ntawm cov hlwb uas txaus siab rau tej yam kev mob. Qee zaum, ntau qhov chaw.

Thiab yog li ntawd ... cia pib.

Nyob rau hauv tib qho piv txwv, peb yuav sim suav cov khoom npe nrog tus nqi ntau tshaj 90 (yog tias koj saib nws, koj tuaj yeem hais tias muaj 2 khoom xws li: tangerines thiab txiv kab ntxwv).

Yuav kom suav cov khoom hauv lub xov tooj yam, peb tau sau cov qauv nram no (saib saum toj no):

= LUB TEB CHAWS (B2: B5; "> 90")qhov twg:

B2: B5 - qhov ntau tshaj qhov uas lawv yuav raug kuaj xyuas raws li qhov peb tau teem tseg;

">90" - tus mob nws tus kheej yog nyob rau hauv quotes.

Tam sim no peb yuav sim nyuab peb tus piv txwv me ntsis, thiab ntxiv ib daim ntawv qhia nyiaj raws li ib qho kev mob ntxiv: nrog tus nqi ntawm ntau tshaj 90 + qhov ntau hauv Tshuag yog tsawg dua 20 kg.

Cov mis no siv daim ntawv:

= COUNTIFS (B2: B6; "> 90"; C2: C6; "<20"))

Ntawm no yog txhua yam tseem zoo li qub, tsuas yog rau ib qho mob ntxiv (C2: C6; "<20"). Los ntawm txoj kev, muaj peev xwm ua tau ntau yam ntawm tej yam kev mob!

Nws yog tseeb hais tias rau xws li ib lub rooj me me, tsis muaj leej twg yuav sau cov qauv no, tab sis rau ib lub rooj ntawm ntau pua kab - qhov no yog lwm yam teeb meem nkaus. Piv txwv li, cov lus no yog ntau tshaj qhov tseeb.

4. Tshawb nrhiav thiab hloov qhov tseem ceeb ntawm ib lub rooj rau lwm tus (CDF formula)

Cia li xav tias ib lub rooj tshiab tau tuaj rau peb, nrog cov nqi tshiab rau cov khoom muag. Zoo, yog hais tias lub npe ntawm 10-20 - thiab koj tau manually "tsis nco qab" lawv tag nrho. Thiab yog tias muaj ntau pua lub npe xws li? Yooj yim sai yog tias Excel tau pom los ntawm cov ntawv sib piv ntawm ib lub rooj rau lwm tus, thiab mam li luam cov nqi tshiab rau peb lub rooj qub.

Rau qhov hauj lwm no, cov mis no siv Vpr. Nyob rau ntawm ib zaug, nws nws tus kheej "ntse ntsuav" nrog cov zajlus kom "IF" tseem tsis tau ntsib qhov zoo tshaj plaws!

Thiab yog li ntawd, peb pib ...

Nov yog peb tus qauv + nqe tshiab nrog nqe cim npe. Tam sim no peb yuav tsum tau hloov tus nqi tshiab nqe ntawm lub rooj tshiab rau cov laus (cov nqe tshiab yog cov xim liab).

Muab tus cursor hauv cell B2 - i.e. nyob hauv thawj lub xov tooj ntawm tes uas peb xav hloov tus nqi tag cia. Tom ntej no, peb sau cov formula raws li nyob rau hauv lub screenshot hauv qab no (tom qab lub screenshot yuav muaj lus qhia ntxaws rau nws).

= CDF (A2; $ D $ 2: $ E $ 5; 2)qhov twg

A2 - Tus nqi uas peb yuav tau nrhiav kom tau ib daim ntawv tshiab. Nyob rau hauv peb cov ntaub ntawv, peb tab tom nrhiav lo lus "txiv apples" nyob rau hauv lub rooj tshiab.

$ D $ 2: $ E $ 5 - Peb xaiv tag nrho peb cov lus tshiab (D2: E5, xaiv mus ntawm sab laug sab mus rau sab xis txoj kab sib txuas), i.e. qhov twg yuav raug tshawb. Cov cim "$" hauv qhov formula no yog tsim nyog kom thaum luam tsab ntawv no rau lwm lub hlwb - D2: E5 tsis hloov!

Nws tseem ceeb heev! Kev tshawb rau lo lus "apples" yuav ua nyob rau hauv thawj kab ntawv ntawm koj xaiv lub rooj; hauv qhov ua piv txwv, tus "apples" yuav raug tshawb hauv sab D.

2 - Thaum lo lus "apples" nyob hauv, qhov kev ua haujlwm yuav tsum paub los ntawm cov kem ntawm lub rooj xaiv (D2: E5) los luam cov nqi uas xav tau. Hauv peb qhov piv txwv, luam tawm ntawm kem 2 (E), txij li thaum hauv thawj kab lus (D) peb tshawb. Yog tias koj xaiv lub rooj rau kev tshawb no yuav muaj txog 10 kab, ces thawj kem yuav tshawb, thiab ntawm 2 mus rau 10 kab - koj tuaj yeem xaiv tus xov tooj kom muab luam tawm.

Yuav formula = CDF (A2; $ D $ 2: $ E $ 5; 2) hloov tshiab qhov tseem ceeb rau lwm cov npe khoom - cia li luam tawm rau lwm lub hlwb ntawm kem nrog cov nqe lus khoom nqe (hauv peb tus piv txwv, luam lub hlwb B3: B5). Cov mis yuav tuaj yeem tshawb thiab luam cov nqi ntawm kem ntawm lub rooj tshiab uas koj xav tau.

5. Qhov xaus

Nyob rau hauv tsab xov xwm no, peb tau pom cov ntsiab lus ntawm kev ua haujlwm nrog Excel los pib sau cov qauv ntawv. Lawv tau muab cov qauv ntawm cov qauv tshaj plaws uas feem ntau ua haujlwm nrog feem coob ntawm cov neeg ua haujlwm hauv Excel.

Kuv vam tias cov piv txwv uas tau tshawb fawb yuav pab tau rau ib tug neeg thiab yuav pab kom nws ua haujlwm. Cov kev sim ua tau zoo!

PS

Thiab zoo li cas cov qauv koj siv, yog nws ua tau kom ua cas simplify cov qauv uas muab hauv tsab xov xwm? Piv txwv, ntawm cov computers tsis muaj zog, thaum qee qhov kev hloov pauv hauv cov rooj loj loj, qhov twg yog qhov kev suav ua kom tau, lub computer khi rau ob peb xuab moos, rov ua dua tshiab thiab qhia tau cov qhabnias tshiab ...