Yuav ua li cas sib piv ob kab hauv Excel thiab tshem tawm cov duplicates (highlight, colorize, txav)

Kab lus no yuav siv sijhawm li 10 feeb los nyeem. Hauv 5 feeb tom ntej no, koj tuaj yeem sib piv ob kab hauv Excel yooj yim thiab paub seb puas muaj cov duplicates hauv lawv, rho tawm lawv lossis qhia cov xim. Yog li, lub sijhawm tau los!

Excel yog ib daim ntawv thov uas muaj zog heev thiab zoo heev rau kev tsim thiab ua cov ntaub ntawv ntau. Yog tias koj muaj ob peb phau ntawv ua haujlwm nrog cov ntaub ntawv (lossis tsuas yog ib lub rooj loj), ces tej zaum koj yuav xav sib piv 2 kab, nrhiav qhov tseem ceeb dua, thiab tom qab ntawd ua ib yam dab tsi nrog lawv, piv txwv li, tshem tawm, taw qhia lossis tshem tawm cov ntsiab lus. Kem tuaj yeem nyob hauv tib lub rooj, nyob ib sab lossis tsis nyob ib sab, tuaj yeem nyob ntawm 2 daim ntawv sib txawv lossis txawm nyob hauv phau ntawv sib txawv.

Xav txog tias peb muaj 2 kab nrog tib neeg lub npe - 5 lub npe ib kab A thiab 3 lub npe hauv ib kab B. Koj yuav tsum sib piv cov npe hauv ob kab ntawv no thiab nrhiav cov duplicates. Raws li koj nkag siab, qhov no yog cov ntaub ntawv tsis tseeb, ua piv txwv nkaus xwb. Hauv cov ntxhuav tiag tiag, peb tab tom cuam tshuam nrog ntau txhiab lossis txawm tias kaum tawm txhiab cov ntaub ntawv.

Kev Xaiv A: ob kab ntawv yog nyob rau tib daim ntawv. Piv txwv li, ib kab A thiab kem B.

Kev xaiv B: Cov kab yog nyob rau ntawm cov nplooj ntawv sib txawv. Piv txwv li, ib kab A ntawm daim ntawv Ntawv 2 thiab kem A ntawm daim ntawv Ntawv 3.

Excel 2013, 2010 thiab 2007 muaj ib lub cuab yeej built-in Tshem Cov Duab Tsis Txaus Ua Ntej (Tshem tawm Duplicates) tab sis nws tsis muaj zog hauv qhov xwm txheej no vim nws tsis tuaj yeem sib piv cov ntaub ntawv hauv 2 kab. Ntxiv mus, nws tsuas tuaj yeem tshem cov duplicates. Tsis muaj lwm txoj kev xaiv xws li nthuav tawm lossis hloov xim. Thiab taw tes!

Tom ntej no, kuv yuav qhia koj txog txoj hauv kev los sib piv ob kab hauv Excel, uas yuav tso cai rau koj mus nrhiav thiab tshem tawm cov ntaub ntawv duplicate.

• Sib piv 2 kab thiab nrhiav duplicates siv cov qauv
  
  • Kev xaiv A: ob kab ntawv yog nyob rau tib daim ntawv
  • Kev xaiv B: Cov kab nyob ntawm cov nplooj ntawv sib txawv lossis hauv cov ntawv sib txawv
• Ua cov duplicates pom
  
  • Qhia tsuas yog cov kab sib npaug hauv kab A
  • Hloov xim lossis pom cov duplicates
  • Tshem cov duplicates ntawm thawj kab

Sib piv 2 kab hauv Excel thiab nrhiav cov khoom sib xws uas siv cov qauv

Kev xaiv A: ob kab ntawv yog nyob rau tib daim ntawv

1. Hauv thawj lub xov tooj khoob (hauv peb qhov piv txwv, qhov no yog cell C1), peb sau cov qauv hauv qab no:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Hauv peb cov qauv A1 qhov no yog thawj kab ntawm thawj kab peb yuav los sib piv. $B $1 и $B $10000 cov no yog cov chaw nyob ntawm thawj thiab kawg ntawm cov kab ke thib ob, uas peb yuav ua qhov sib piv. Nco ntsoov cov lus qhia meej - kab ntawv thiab kab zauv yog ua ntej los ntawm tus lej kos npe ($). Kuv siv cov ntaub ntawv pov thawj tseeb kom cov chaw nyob ntawm tes nyob zoo ib yam thaum luam cov qauv.

   Yog hais tias koj xav nrhiav duplicates nyob rau hauv ib kem B, hloov cov ntawv xa mus kom cov qauv zoo li no:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Hloov chaw "tsuas yog"Thiab"Theej tawm» Koj tuaj yeem sau koj tus kheej daim ntawv lo, piv txwv li, «Tsis pom"Thiab"pom", los yog tawm xwb"Theej tawm' thiab nkag mus rau qhov chaw cim tsis yog tus nqi thib ob. Nyob rau hauv rooj plaub tom kawg, cov hlwb uas tsis pom muaj qhov sib npaug yuav nyob twj ywm, thiab, kuv ntseeg, qhov kev sawv cev ntawm cov ntaub ntawv no yog qhov yooj yim tshaj plaws rau kev txheeb xyuas ntxiv.

2. Tam sim no cia peb luam peb cov qauv rau txhua lub hlwb hauv kab C, tag nrho txoj kev mus rau hauv qab kab, uas muaj cov ntaub ntawv nyob rau hauv kem A. Txhawm rau ua qhov no, txav tus nas pointer mus rau sab xis sab xis ntawm lub xov tooj ntawm tes C1, tus pointer yuav coj daim ntawv ntawm ib tug dub crosshair, raws li qhia nyob rau hauv daim duab hauv qab no:Nyem thiab tuav cia rau sab laug nas khawm thiab luag ciam teb ntawm thav duab nqes, highlighting tag nrho cov hlwb uas koj xav mus ntxig rau cov mis. Thaum txhua lub hlwb xav tau raug xaiv, tso tus nas khawm:

   

Tswv yim: Hauv cov ntxhuav loj, luam cov qauv yuav nrawm dua yog tias koj siv cov keyboard shortcuts. Highlight ib cell C1 thiab nias Ctrl + C (kom luam cov qauv rau hauv daim ntawv teev npe), ces nyem Ctrl + Ua haujlwm + Xaus (kom xaiv tag nrho cov hlwb tsis pub dawb hauv kab C) thiab thaum kawg nias Ctrl + V (kom ntxig cov mis rau hauv txhua lub cell xaiv).

3. Zoo heev, tam sim no tag nrho cov nqi sib npaug raug cim tias "Theej tawm":

Kev xaiv B: ob kab nyob rau ntawm cov nplooj ntawv sib txawv (hauv cov ntawv sib txawv)

1. Hauv thawj lub xov tooj ntawm thawj kab ntawv khoob ntawm daim ntawv ua haujlwm Ntawv 2 (nyob rau hauv peb cov ntaub ntawv nws yog kab B) nkag mus rau cov qauv hauv qab no:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Ntawm no Ntawv 3 yog lub npe ntawm daim ntawv uas nyob rau sab 2, thiab $A $1: $A $10000 yog cov chaw nyob ntawm tes los ntawm 1st mus rau kawg hauv kab ntawv thib 2 no.

2. Luam cov qauv rau txhua lub hlwb hauv ib kab B (ib yam li kev xaiv A).
3. Peb tau txais qhov txiaj ntsig no:

Ua cov duplicates pom

Zoo heev, peb tau pom cov ntawv nkag hauv thawj kab uas tseem muaj nyob hauv kab thib ob. Tam sim no peb yuav tsum ua ib yam dab tsi nrog lawv. Mus dhau tag nrho cov ntaub ntawv duplicate hauv ib lub rooj manually yog qhov tsis muaj txiaj ntsig thiab siv sijhawm ntau dhau. Muaj txoj hauv kev zoo dua.

Qhia tsuas yog cov kab sib npaug hauv kab A

Yog tias koj cov kab ntawv tsis muaj headers, ces koj yuav tsum tau ntxiv rau lawv. Txhawm rau ua qhov no, tso tus cursor rau ntawm tus lej uas sawv cev rau thawj kab, thiab nws yuav tig mus rau hauv lub xub dub, raws li qhia hauv daim duab hauv qab no:

Right-click thiab xaiv los ntawm cov ntawv qhia zaub mov Insertion (Insert):

Muab cov npe rau kab, piv txwv li, "lub npe"Thiab"Luam tawm?» Ces qhib lub tab Cov ntaub ntawv (Cov ntaub ntawv) thiab nias Lim (Filter):

Tom qab ntawd nyem rau ntawm tus xub grey me ntsis ntawm ib sab ntawm "Luam tawm?« qhib cov ntawv qhia zaub mov; uncheck tag nrho cov khoom hauv daim ntawv teev npe no tshwj tsis yog Theej tawm, thiab nias OK.

Ntawd yog tag nrho, tam sim no koj pom tsuas yog cov ntsiab lus ntawm kem А, uas yog duplicated nyob rau hauv kab В. Tsuas muaj ob lub hlwb hauv peb lub rooj cob qhia, tab sis, raws li koj nkag siab, hauv kev xyaum yuav muaj ntau yam ntxiv.

Kom tso saib tag nrho cov kab ntawm ib kab dua А, nyem lub cim lim hauv kab В, uas tam sim no zoo li ib tug funnel nrog ib tug me me xub, thiab xaiv Xaiv tag nrho (Xaiv tag nrho). Lossis koj tuaj yeem ua tib yam los ntawm Ribbon los ntawm txhaj Cov ntaub ntawv (Data) > Xaiv & Lim (Xaiv & Lim) > Clear (Clear) raws li qhia hauv lub screenshot hauv qab no:

Hloov xim lossis pom cov duplicates

Yog cov ntawv "Theej tawm” tsis txaus rau koj lub hom phiaj thiab koj xav kos cov kab sib npaug nrog cov xim sib txawv, sau xim lossis lwm txoj hauv kev…

Hauv qhov no, lim cov duplicates raws li qhia saum toj no, xaiv tag nrho cov lim lim thiab nyem Ctrl + 1qhib lub dialog Hom Ntawv Hlwb (cell format). Ua piv txwv, cia peb hloov cov xim ntawm cov hlwb hauv kab nrog duplicates rau ci daj. Tau kawg, koj tuaj yeem hloov cov xim puv nrog lub cuab yeej Sau (Fill Color) tab Tsev (Tsev) tab sis dialog box kom zoo dua Hom Ntawv Hlwb (Cell Format) nyob rau hauv uas koj tuaj yeem teeb tsa tag nrho cov kev xaiv formatting tib lub sijhawm.

Tam sim no koj yuav twv yuav raug hu tsis nco cov cell nrog duplicates:

Tshem tawm cov nqi sib npaug ntawm thawj kab

Lim lub rooj kom tsuas yog cov hlwb uas muaj qhov sib npaug pom, thiab xaiv cov cell.

Yog tias 2 kab ntawv koj tab tom sib piv yog nyob ntawm cov nplooj ntawv sib txawv, uas yog, nyob rau hauv cov rooj sib txawv, txoj cai-nias ntawm qhov xaiv ntau yam thiab xaiv Rho Rhais (Tshem tawm kab):

Xovxwm OKThaum Excel nug koj kom paub meej tias koj xav rho tawm tag nrho cov kab ntawv thiab tom qab ntawd tshem cov lim. Raws li koj tuaj yeem pom, tsuas yog kab nrog cov txiaj ntsig tshwj xeeb tseem nyob:

Yog 2 kab nyob rau tib daim ntawv, nyob ze rau ib leeg (nyob ib sab) lossis tsis nyob ib leeg (tsis nyob ib sab), ces cov txheej txheem ntawm kev tshem tawm duplicates yuav nyuaj me ntsis. Peb tsis tuaj yeem tshem tawm tag nrho cov kab uas muaj qhov sib npaug, vim qhov no yuav tshem tawm cov hlwb ntawm kab thib ob ib yam nkaus. Yog li kom tawm tsuas yog cov khoom nkag hauv ib kab А, ua li no:

1. Lim lub rooj kom pom tsuas yog duplicate qhov tseem ceeb thiab xaiv cov cell. Right-click rau lawv thiab xaiv los ntawm cov ntawv qhia zaub mov Nthuav cov ntsiab lus (cov ntsiab lus meej).
2. Ntxuav lub lim.
3. Xaiv tag nrho cov cell hauv ib kab А, pib ntawm lub cell A1 txhua txoj kev mus rau hauv qab uas muaj cov ntaub ntawv.
4. nias lub Cov ntaub ntawv (Cov ntaub ntawv) thiab nias Txheeb A rau Z (Siv los ntawm A txog Z). Hauv lub dialog box uas qhib, xaiv Txuas ntxiv nrog rau qhov kev xaiv tam sim no (Xaiv hauv qhov kev xaiv tshwj xeeb) thiab nyem lub pob Dub (Sorting):
5. Rho tawm kem nrog cov qauv, koj yuav tsis xav tau nws lawm, txij li tam sim no koj tsuas muaj qhov tseem ceeb tshwj xeeb.
6. Tias yog nws, tam sim no kab А tsuas muaj cov ntaub ntawv tshwj xeeb uas tsis nyob hauv kab В:

Raws li koj tuaj yeem pom, tshem cov duplicates ntawm ob kab hauv Excel siv cov qauv tsis yog qhov nyuaj.