Ib ntawm Microsoft Excel qhov muaj peev xwm ntau yog lub peev xwm los sib piv ob daim ntawv ntawm cov ntaub ntawv, txheeb xyuas qhov sib tw ntawm cov npe thiab txheeb xyuas cov khoom uas pom hauv ib daim ntawv nkaus xwb. Qhov no muaj txiaj ntsig thaum piv cov ntaub ntawv nyiaj txiag lossis tshawb xyuas seb puas muaj lub npe tshwj xeeb nyob hauv cov ntaub ntawv khaws tseg. Koj tuaj yeem siv MATCH ua haujlwm txhawm rau txheeb xyuas thiab kos cov ntawv sib txuam lossis cov ntaub ntawv tsis sib xws, lossis koj tuaj yeem siv cov txheej txheem txias nrog COUNTIF ua haujlwm. Cov theem hauv qab no qhia koj yuav siv li cas kom phim koj cov ntaub ntawv.
Cov kauj ruam
Txoj Kev 1 ntawm 2: Txheeb Cov Ntaub Ntawv Nrog MATCH Function
Kauj Ruam 1. Luam cov ntaub ntawv teev npe mus rau ib daim ntawv ua haujlwm
Excel tuaj yeem ua haujlwm nrog ntau daim ntawv ua haujlwm hauv ib phau ntawv ua haujlwm, lossis nrog ntau phau ntawv ua haujlwm, tab sis koj yuav pom sib piv cov npe yooj yim dua yog tias koj theej lawv cov ntaub ntawv mus rau ib daim ntawv ua haujlwm.
Kauj Ruam 2. Muab txhua daim ntawv teev npe tshwj xeeb
Yog tias koj ob daim ntawv tsis qhia tawm ib txoj hauv kev los txheeb xyuas lawv, koj yuav xav tau ntxiv kab ntawv ntxiv rau txhua daim ntawv teev cov ntaub ntawv uas txheeb xyuas cov khoom ntawd rau Excel kom nws tuaj yeem pom yog tias cov khoom hauv cov npe muab cuam tshuam nrog ib yam khoom nyob rau lwm daim ntawv. Qhov xwm txheej ntawm tus cim no yuav nyob ntawm seb hom ntaub ntawv twg koj tab tom sim ua kom haum. Koj yuav xav tau tus cim rau txhua kab npe.
- Txog cov ntaub ntawv nyiaj txiag cuam tshuam nrog lub sijhawm muab, xws li nrog cov ntaub ntawv sau se, qhov no tuaj yeem yog cov lus piav qhia ntawm cov khoom muaj nqis, hnub uas cov khoom tau txais, lossis ob qho tib si. Hauv qee kis, kev nkag tuaj yeem raug txheeb xyuas nrog tus lej lej; txawm li cas los xij, yog tias tib lub kaw lus tsis siv rau ob daim ntawv teev npe, tus cim no tuaj yeem tsim qhov sib tw qhov twg tsis muaj lossis tsis quav ntsej cov ntais ntawv uas yuav tsum tau ua.
- Qee qhov xwm txheej, koj tuaj yeem nqa cov khoom los ntawm ib daim ntawv teev npe thiab sib sau ua ke nrog cov khoom los ntawm lwm cov npe los tsim tus cim, xws li cov lus piav qhia khoom muaj nqis thiab lub xyoo nws tau yuav. Txhawm rau tsim cov cim qhia no, koj sib txuas (ntxiv, sib txuas) cov ntaub ntawv los ntawm ob lossis ntau lub hlwb siv ampersand (&). Txhawm rau muab cov lus piav qhia hauv xov tooj ntawm tes F3 nrog hnub tim hauv xov tooj ntawm tes G3, cais los ntawm qhov chaw, koj yuav nkag mus rau tus lej '= F3 & "" & G3' hauv lwm lub xovtooj hauv kab ntawd, xws li E3. Yog tias koj xav suav nrog tsuas yog xyoo hauv tus cim (vim tias ib daim ntawv teev npe siv hnub tim tag nrho thiab lwm qhov siv tsuas yog xyoo), koj yuav suav nrog YEAR ua haujlwm los ntawm kev nkag mus '= F3 & "" & YEAR (G3)' hauv xov tooj E3 xwb. (Tsis suav nrog cov lus hais ib zaug; lawv tsuas yog qhia qhov piv txwv.)
- Thaum koj tau tsim cov mis, koj tuaj yeem luam nws mus rau txhua lwm lub cell ntawm kab cim cim los ntawm kev xaiv lub xov tooj ntawm tes nrog cov mis thiab rub cov ntawv tuav hla lwm lub xov tooj ntawm kab uas koj xav luam cov qauv. Thaum koj tso koj lub nas khawm, txhua lub xovtooj uas koj rub hla yuav raug suav nrog cov qauv, nrog cov xovtooj ntawm tes hloov kho rau cov xovtooj tsim nyog hauv tib kab.
Kauj Ruam 3. Txheem cov ntaub ntawv yog ua tau
Thaum lub siab lees paub tias "Inc." thiab "Koom nrog" txhais tau tib yam, Excel tsis yog tshwj tsis yog tias koj tau rov kho dua ib lo lus lossis lwm yam. Ib yam nkaus, koj yuav txiav txim siab qhov tseem ceeb xws li $ 11, 950 thiab $ 11, 999.95 kom ze txaus kom phim, tab sis Excel yuav tsis yog tshwj tsis yog koj qhia nws.
- Koj tuaj yeem cuam tshuam nrog qee cov ntawv luv, xws li "Co" rau "Company" thiab "Inc" rau "Kev koom ua ke los ntawm kev siv LEFT txoj hlua ua haujlwm kom txiav cov cim ntxiv. Lwm cov ntawv luv, xws li" Assn "rau" Association, "tej zaum yuav zoo tshaj cuam tshuam nrog los ntawm kev teeb tsa cov ntaub ntawv nkag mus rau cov lus qhia thiab tom qab ntawd sau qhov program los saib thiab kho cov qauv tsis raug.
- Rau cov hlua ntawm tus lej, xws li ZIP cov lej uas qee qhov nkag nrog ZIP+4 cov ntawv txuas ntxiv thiab lwm tus tsis ua, koj tuaj yeem rov siv LEFT txoj hlua ua haujlwm kom paub thiab sib phim tsuas yog thawj tus lej ZIP. Txhawm rau kom Excel lees paub tus lej tus lej uas nyob ze tab sis tsis zoo ib yam, koj tuaj yeem siv ROUND ua haujlwm kom hloov cov txiaj ntsig ze rau tib tus lej thiab phim lawv.
- Qhov chaw ntxiv, xws li ntaus ob kab ntawm cov lus tsis txhob siv ib qho, tuaj yeem tshem tawm los ntawm kev siv TRIM ua haujlwm.
Kauj Ruam 4. Tsim kab rau cov qauv sib piv
Ib yam li koj yuav tsum tsim kab rau cov npe teev npe, koj yuav tsum tsim kab rau cov qauv uas ua piv rau koj. Koj yuav xav tau ib kab lus rau txhua daim ntawv teev npe.
Koj yuav xav sau cov kab ntawv no nrog qee yam xws li "Poob?"
Kauj Ruam 5. Nkag mus rau cov qauv sib piv hauv txhua lub cell
Txog cov qauv sib piv, koj yuav siv MATCH ua haujlwm zes hauv lwm qhov haujlwm Excel, ISNA.
- Cov mis yuav siv daim ntawv "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", qhov twg cov xov tooj ntawm tes ntawm tus cim kab lus ntawm thawj cov npe raug muab piv rau txhua tus cim nyob hauv daim ntawv thib ob rau saib nws puas phim ib ntawm lawv. Yog tias nws tsis sib xws, cov ntaub ntawv ploj lawm, thiab lo lus "TRUE" yuav tshwm rau hauv lub xovtooj ntawd. Yog tias nws sib phim, cov ntaub ntawv nyob tam sim no, thiab lo lus "FALSE" yuav muab tso tawm. (Thaum nkag mus rau tus lej, tsis suav nrog cov ntawv hais tawm.)
- Koj tuaj yeem luam cov qauv rau hauv cov cell uas seem ntawm kab ntawv tib txoj kev koj tau theej cov lej cim cim ntawm tes. Hauv qhov no, tsuas yog kev siv xov tooj ntawm tes rau tus cim ntawm tes hloov pauv, raws li tso cov cim nyiaj daus las nyob rau pem hauv ntej ntawm kab thiab kab lus hais ua ntej rau thawj zaug thiab zaum kawg hauv cov npe ntawm cov cim thib ob ntawm tes ua rau lawv hais tsis meej.
- Koj tuaj yeem luam cov qauv sib piv rau thawj daim ntawv rau hauv thawj kab ntawm kab ntawv rau daim ntawv thib ob. Tom qab ntawd koj yuav tsum hloov kho cov ntaub ntawv xov tooj ntawm tes kom "G3" tau hloov pauv nrog qhov siv rau thawj tus cim ntawm tes ntawm daim ntawv thib ob thiab "$ L $ 3: $ L $ 14" tau hloov nrog thawj thiab tus lej cim kawg ntawm lub daim ntawv thib ob. (Tawm ntawm cov cim nyiaj daus las thiab cov nyuv ib leeg.) Tom qab ntawd koj tuaj yeem luam cov qauv no hloov pauv mus rau hauv cov hlwb uas seem hauv kab sib piv ntawm cov npe thib ob.
Kauj Ruam 6. Txheeb cov npe kom pom cov txiaj ntsig tsis sib xws yooj yim dua, yog tias tsim nyog
Yog tias koj cov npe loj, koj yuav tsum tau txheeb lawv kom muab txhua qhov tsis sib piv qhov txiaj ntsig ua ke. Cov lus qhia hauv kab ntawv hauv qab no yuav hloov cov qauv mus rau qhov muaj txiaj ntsig kom zam kev suav qhov yuam kev, thiab yog tias koj cov npe muaj ntau, yuav zam lub sijhawm rov suav dua.
- Luag koj nas hla txhua lub cell hauv daim ntawv kom xaiv nws.
- Xaiv Luam los ntawm Kho cov ntawv qhia zaub mov hauv Excel 2003 lossis los ntawm Pawg Pawg Ntawv ntawm Tsev Ribbon hauv Excel 2007 lossis 2010.
- Xaiv Paste Tshwj Xeeb los ntawm Kho kom raug cov ntawv qhia hauv Excel 2003 lossis los ntawm Paste dropdown khawm hauv Pawg Pawg ntawm Excel 2007 lossis 2010s Tsev ribbon.
- Xaiv "Tus Nqi" los ntawm Paste Raws li cov npe hauv Paste Special dialog box. Nyem OK kom kaw qhov kev sib tham.
- Xaiv Sort los ntawm Cov Ntawv Qhia zaub mov hauv Excel 2003 lossis Sort and Filter group of Data Data hauv Excel 2007 lossis 2010.
- Xaiv "Header row" los ntawm "My data range have" list in the Sort By dialog, select "Missing?" (lossis lub npe uas koj tau muab rau kab lus sib piv lub taub hau) thiab nyem OK.
- Rov ua cov kauj ruam no rau lwm daim ntawv teev npe.
Kauj Ruam 7. Sib piv cov khoom uas tsis sib xws pom kom pom tias vim li cas lawv thiaj tsis sib phim
Raws li tau sau tseg yav dhau los, Excel tau tsim los nrhiav cov ntaub ntawv sib xws tshwj tsis yog tias koj teeb tsa nws los nrhiav qhov kwv yees. Koj qhov tsis sib xws tuaj yeem yooj yim raws li kev sib kis ntawm cov tsiaj ntawv lossis tus lej. Nws kuj tseem tuaj yeem yog qee yam uas xav tau kev txheeb xyuas ywj pheej, xws li kuaj xyuas seb cov khoom muaj npe xav tau tshaj tawm thawj zaug.
Txoj Kev 2 ntawm 2: Raws Li Txoj Cai Nrog COUNTIF
Kauj Ruam 1. Luam cov ntaub ntawv teev npe mus rau ib daim ntawv ua haujlwm
Kauj Ruam 2. Txiav txim siab cov npe twg uas koj xav tau los hais txog cov ntaub ntawv sib txuam lossis tsis sib xws
Yog tias koj xav ua kom pom cov ntaub ntawv hauv ib daim ntawv nkaus xwb, tej zaum koj yuav xav qhia cov ntaub ntawv tshwj xeeb rau cov npe ntawd; uas yog, cov ntaub ntawv uas tsis sib xws cov ntaub ntawv hauv lwm daim ntawv. Yog tias koj xav ua kom pom cov ntaub ntawv hauv ob daim ntawv teev npe, koj yuav xav qhia cov ntaub ntawv uas sib phim ua ke. Rau lub hom phiaj ntawm qhov piv txwv no, peb yuav xav tias thawj daim ntawv yuav siv cov cell G3 txog G14 thiab daim ntawv thib ob yuav siv cov cell L3 txog L14.
Kauj Ruam 3. Xaiv cov khoom hauv daim ntawv uas koj xav ua kom pom qhov tshwj xeeb lossis sib txuam cov khoom hauv
Yog tias koj xav ua kom pom cov khoom sib xws hauv ob daim ntawv teev npe, koj yuav tsum xaiv cov npe ib zaug thiab siv cov qauv sib piv (piav qhia hauv cov kauj ruam tom ntej) rau txhua daim ntawv teev npe.
Kauj Ruam 4. Siv cov mis sib piv uas tsim nyog
Txhawm rau ua qhov no, koj yuav tsum tau nkag mus rau Cov Qauv Sib Tham hauv koj li version ntawm Excel. Hauv Excel 2003, koj ua li ntawd los ntawm kev xaiv Cov Qauv Raws Li los ntawm Cov Ntawv qhia zaub mov, thaum nyob hauv Excel 2007 thiab 2010, koj nyem rau lub pob Hloov Kho Raws Li Txoj Cai hauv Pawg Pawg ntawm Tsev Ribbon. Xaiv hom kev cai raws li "Cov Qauv" thiab nkag mus rau koj cov mis hauv qhov Hloov Kho Txoj Cai Cov Lus Teb.
- Yog tias koj xav hais txog cov ntaub ntawv tshwj xeeb rau thawj daim ntawv, cov qauv yuav yog "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", nrog rau ntau ntawm cov xovtooj ntawm cov npe thib ob uas tau muab ua qhov tseem ceeb thiab siv rau thawj lub cell ntawm thawj daim ntawv raws li tus txheeb ze tus nqi. (Tsis txhob sau cov nqe lus kaw.)
- Yog tias koj xav hais txog cov ntaub ntawv tshwj xeeb rau cov npe thib ob, cov qauv yuav yog "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", nrog rau ntau yam ntawm cov cell ntawm thawj daim ntawv teev npe ua qhov muaj txiaj ntsig thiab siv tau rau thawj lub cell ntawm daim ntawv thib ob raws li tus txheeb ze tus nqi. (Tsis txhob sau cov nqe lus kaw.)
- Yog tias koj xav ua kom pom cov ntaub ntawv hauv txhua daim ntawv teev npe uas tau pom hauv lwm daim ntawv teev npe, koj yuav xav tau ob tus qauv, ib qho rau thawj daim ntawv thiab ib qho rau qhov thib ob. Cov mis rau thawj daim ntawv yog "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", thaum cov mis rau cov npe thib ob yog COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". sau tseg yav dhau los, koj xaiv thawj daim ntawv thov siv nws cov mis thiab tom qab ntawd xaiv cov npe thib ob los siv nws cov mis.
- Ua ntawv thov hom twg los xij koj xav kom ua kom pom cov ntaub ntawv raug chij. Nyem OK kom kaw qhov kev sib tham.
Yees duab - Los ntawm kev siv qhov kev pabcuam no, qee cov ntaub ntawv yuav raug muab qhia rau YouTube
Lub tswv yim
- Hloov chaw ntawm kev siv xov tooj ntawm tes siv nrog COUNTIF cov txheej txheem txheej txheem, koj tuaj yeem nkag mus rau tus nqi kom tshawb nrhiav thiab chij ib lossis ntau daim ntawv teev cov xwm txheej ntawm tus nqi ntawd.
- Txhawm rau ua kom yooj yim cov ntawv sib piv, koj tuaj yeem tsim cov npe rau koj cov npe, xws li "List1" thiab "List2." Tom qab ntawd, thaum sau cov qauv, cov npe teev npe no tuaj yeem hloov pauv rau cov xov tooj ntawm tes uas tau siv hauv cov piv txwv saum toj no.
