Thaum ua hauj lwm nrog ib lub rooj los yog cov ntaub ntawv muaj ntau yam ntaub ntawv, nws muaj peev xwm hais tias ib txhia kab rov qab dua. Qhov no ntxiv ua rau cov ntaub ntawv array. Tsis tas li ntawd, nyob rau hauv lub xub ntiag ntawm duplicates, xam tsis yog ntawm kev tshwm sim hauv cov qauv. Wb saib yuav ua li cas thiaj paub thiab tshem tawm cov kab hauv Microsoft Excel.
Tshawb nrhiav thiab rho tawm
Nrhiav thiab rho tawm cov lus ntawm qhov tseem ceeb uas tau duplicated, tejzaum nws nyob rau hauv ntau txoj kev. Hauv txhua qhov ntawm cov kev xaiv, kev tshawb nrhiav thiab tshem tawm cov hom kab ke yog cov kev sib txuas hauv ib txoj kev.
Txoj kev 1: yooj yim deletion ntawm kab tshuam
Qhov yoojyim tshaj los tshem cov duplicates yog siv lub pob tshwj xeeb rau daim kab xev uas tsim rau lub hom phiaj no.
- Xaiv tag nrho cov lus ntau. Mus rau lub tab "Cov ntaub ntawv". Peb nias lub khawm "Tshem Cov Ntawv Sau". Nws nyob rau ntawm daim kab xev hauv lub nras cov cuab yeej. "Ua hauj lwm nrog cov ntaub ntawv".
- Qhov kev rho tawm tawm qhov rais qhib. Yog hais tias koj muaj ib lub rooj nrog lub hau (thiab feem ntau nws yeej yog), ces txog qhov ntsuas "Kuv cov ntaub ntawv muaj cov hau kev" yuav tsum tau xaiv. Nyob rau hauv lub ntsiab ntawm lub qhov rais yog ib daim ntawv teev txhua kab uas yuav raug kuaj xyuas. Ib tug uake yuav muab suav hais tias yog sib npaug xwb yog tias cov ntaub ntawv ntawm tag nrho cov kab xaiv nrog ib qho kos cim match. Ntawd yog, yog tias koj tshem ntawm lub cim ntawm lub npe ntawm ib kem, nws ua kom ntau qhov pom tseeb tias pom cov ntaub ntawv rov qab ua dua. Tom qab tag nrho cov chaw yuav tsum tau ua, nyem rau ntawm lub pob. "OK".
- Excel ua tus txheej txheem rau kev nrhiav thiab tshem tawm tib yam. Tom qab ua tiav lawm, cov ntaub ntawv qhib qhov rai, uas qhia koj rov qab ntau npaum li cas tau muab tshem tawm thiab cov xov tooj ntawm cov ntaub ntawv seem. Txhawm rau kaw lub qhov rai, nias lub khawm. "OK".
Txoj kev 2: Tshem Cov Duplicates nyob rau hauv lub rooj ntse
Cov ntaub ntawv yuav raug tshem tawm ntawm cov kab mob hlwb los tsim ib lub rooj ntse ntse.
- Xaiv tag nrho cov lus ntau.
- Nyob hauv tab "Tsev" nias lub khawm "Hom ntawv raws li rooj"nyob rau ntawm daim kab xev hauv lub nras cov cuab yeej "Yeej". Nyob rau hauv daim ntawv uas tshwm, xaiv cov lus uas koj nyiam.
- Tom qab ntawd lub qhov rai me me qhib rau qhov koj xav tau kom paub meej tias qhov xaiv tau ntau yam thiaj li tsim tau ib lub rooj ntse ntse. Yog tias koj xaiv txhua yam kom raug, ces koj tuaj yeem lees paub, yog tias koj ua yuam kev, ces qhov rai no yuav tsum raug kho. Nws tseem ceeb heev uas yuav tau them nqi rau qhov tseeb uas hais txog "Cov lus nrog lub npe" muaj ib tug zuam. Yog tsis yog, ces nws yuav tsum muab tso rau. Tom qab tag nrho cov chaw tiav, nias ntawm lub pob. "OK". Cov lus ntse tsim.
- Tab sis qhov kev tsim ntawm "ntse lub rooj" tsuas yog ib kauj ruam los daws peb qhov haujlwm tseem ceeb - tshem tawm cov kev sib tw. Nias rau ntawm txhua lub xov tooj ntawm kab lus. Ib pawg ntxiv ntawm cov tabs tshwm. "Ua hauj lwm nrog cov ntxhuav". Nyob hauv tab "Tus tswv tsev" nyem rau ntawm lub pob "Tshem Cov Ntawv Sau"uas yog nyob rau ntawm daim kab xev hauv lub nras cov cuab yeej "Kev pabcuam".
- Tom qab ntawd, qhov qhib kev tshem tawm qhov rai qhib, ua haujlwm nrog uas tau piav qhia nyob rau hauv kev nthuav dav thaum piav txog thawj txoj kev. Tag nrho cov kev ua ntxiv yog ua raws nraim tib qho kev txiav txim.
Hom no yog qhov ntau yam thiab kev ua haujlwm ntawm txhua tus tau piav qhia nyob rau hauv tsab xov xwm no.
Tshooj: Yuav ua li cas yuav ua rau daim ntawv khom nqi hauv Excel
Method 3: thov kev cais
Qhov no yog tsis tag nrho tshem tawm duplicates, txij li qhov tsuas yog hides dua cov ntaub ntawv nyob rau hauv lub rooj.
- Xaiv lub rooj. Mus rau lub tab "Cov ntaub ntawv". Peb nias lub khawm "Lim"nyob hauv qhov chaw thaiv "Tsi thiab lim".
- Qhov lim no yog, raws li cov cim uas tshwm sim hauv daim duab ntawm daim duab triangles hauv kem npe. Tam sim no peb yuav tsum tau kho nws. Ndliv lub khawm "Qib Siab"nyob ze ntawm txhua yam hauv tib pab pawg ntawm cov cuab yeej "Tsi thiab lim".
- Lub lim piam siab qhib opens. Teem ib zuam ntawm xub ntiag ntawm lub parameter "Tsuas yog cov cim nkag". Tag nrho lwm cov chaw tso tawm raws li kev pom zoo. Tom qab ntawd nias ntawm khawm "OK".
Tom qab ntawd, cov ntaub ntawv sau cia yuav muab zais. Tab sis lawv tuaj yeem pom tshwm txhua lub sijhawm los ntawm nias lub pob dua. "Lim".
Tshooj: Advanced Excel Lim
Method 4: Muaj Cai Ua Qauv
Koj tseem tuaj yeem nrhiav cov kab mob sib npaug ntawm kev siv cov lus teb haum. Muaj tseeb, lawv yuav tsum tau muab tshem tawm nrog lwm lub cuab tam.
- Xaiv thaj chaw. Nyob hauv tab "Tsev"nias lub khawm "Muaj Cai Txuj Ci"nyob hauv qhov chaw thaiv "Yeej". Hauv cov ntawv qhia zaub mov uas tshwm, kauj ruam yog kauj ruam "Cov kev cai xaiv" thiab "Duplicate qhov tseem ceeb ...".
- Lub qhov rai formatting window opens. Thawj parameter nyob rau hauv nws yog sab laug unchanged - "Kaw". Tab sis nyob rau hauv qhov xaiv parameter, koj tuaj yeem tawm ntawm qhov chaw tawm lossis xaiv cov xim uas tsim koj, ces nias rau ntawm lub pob "OK".
Tom qab qhov no, lub hlwb yuav xaiv tau qhov tseem ceeb. Yog tias koj xav tau, koj tuaj yeem tshem tawm cov qe ntshav hauv ib txoj hauv kev.
Mloog zoo! Tshawb nrhiav kev siv cov cai sib xyaw ua ke tsis yog ua raws li kab, tab sis nyob rau ntawm txhua lub cell tshwj xeeb, yog li ntawd nws tsis haum rau txhua tus neeg mob.
Tshooj: Txuj Kauj Formatting hauv Excel
Txoj kev 5: siv cov mis
Tsis tas li ntawd, cov neeg tuaj yeem tshawb pom tau los ntawm kev siv cov mis siv ntau lub zog. Nrog nws txoj kev pab, koj tuaj yeem tshawb nrhiav cov kab lus ntawm ib kem. Daim ntawv ntawm hom mis no yuav xyuas raws li nram no:
= YOG HAUS HAIS TIAS (INDEX (column_address; MATCH) (0; comp.
- Tsim dua ib qho ntawm cov kab lus uas yuav tshwm sim.
- Sau tus qauv rau cov qauv ntawv saum toj no hauv thawj lub xovtooj dawb ntawm kab tshiab. Hauv peb cov ntaub ntawv, tus qauv yuav yog raws li nram no:
= YOG HAUS HAIS (INDEX (A8: A15; MATCHES (0; ACCOUNTS (E7: $ E $ 7; A8: A15) YOG HAIS TIAS (TXHEEJ XWM (A8: A15; A8: A15)> 1; 0; "") - Xaiv kab sab nraud kom duplicates, tsuas yog rau lub hau. Teem tus cursor ntawm qhov kawg ntawm lub mis bar. Nias lub pob ntawm cov keyboard F2. Tom qab ntawd ntaus lub ntsiab ua ke Ctrl + ua haujlwm + nkag. Qhov no yog vim lub peculiarities ntawm thov cov qauv rau arrays.
Tom qab cov kev nqis tes ua no "Duplicates" duplicate qhov tseem ceeb yog tso tawm kom pom.
Tab sis, qhov no yog qhov tseem nyuab heev rau cov neeg siv feem ntau. Tsis tas li ntawd xwb, nws tsuas yog kev tshawb nrhiav sib txawv, tiam sis tsis yog qhov kev tshem tawm. Yog li, nws raug pom zoo kom siv cov zauv yooj yim thiab cov kev ua haujlwm zoo tshaj plaws piav qhia ua ntej.
Raws li koj tau pom, hauv Excel muaj ntau cov cuab yeej tsim los nrhiav thiab rhuav tshem cov luam. Txhua ntawm lawv muaj nws tus yam ntxwv. Piv txwv, conditional formatting yuav tsum nrhiav kev sib npaug rau txhua lub xov tooj ntawm tes. Tsis tas li, tsis yog tag nrho cov cuab yeej tsuas tuaj yeem tshawb tsis tau, tab sis kuj rho tawm qhov tseem ceeb. Qhov feem ntau universal xaiv yog los tsim ib lub ntse lub rooj. Thaum siv hom no, koj tuaj yeem kho qhov kev tshawb nrhiav duplicate kom meej thiab yooj yim li sai tau. Ntxiv mus, lawv kev tshem tawm tshwm sim sai li sai tau.
