Yuav Ua Li Cas Pom Qhov Tseem Ceeb Dua hauv Excel Table Kem

Ib lub rooj uas muaj txiaj ntsig zoo ib yam yog qhov teeb meem loj rau ntau tus neeg siv Microsoft Excel. Cov ntaub ntawv rov qab tuaj yeem raug tshem tawm siv cov cuab yeej tsim rau hauv qhov kev pab cuam, nqa lub rooj mus rau qhov tshwj xeeb. Yuav ua li cas kom raug yuav tau tham hauv kab lus no.

Txoj Kev 1 Yuav ua li cas los txheeb xyuas lub rooj rau duplicates thiab tshem tawm lawv siv lub Conditional Formatting cuab tam

Yog li ntawd cov ntaub ntawv tib yam tsis yog duplicated ob peb zaug, nws yuav tsum raug pom thiab tshem tawm ntawm lub rooj array, tawm hauv ib qho kev xaiv xwb. Txhawm rau ua qhov no, koj yuav tsum ua cov kauj ruam hauv qab no:

1. Siv tus nas khawm sab laug los xaiv qhov ntau ntawm cov hlwb uas koj xav kuaj xyuas cov ntaub ntawv sib npaug. Yog tias tsim nyog, koj tuaj yeem xaiv tag nrho lub rooj.
2. Nyob rau sab saum toj ntawm qhov screen, nyem rau ntawm "Tsev" tab. Tam sim no, nyob rau hauv lub toolbar, ib cheeb tsam nrog kev ua haujlwm ntawm ntu no yuav tsum tau muab tso tawm.
3. Hauv "Styles" ntu ntu, sab laug-nias rau ntawm "Conditional Formatting" khawm kom pom qhov ua tau ntawm cov haujlwm no.
4. Hauv cov ntawv qhia zaub mov uas tshwm sim, nrhiav kab "Tsim txoj cai ..." thiab nyem rau nws nrog LMB.

5. Hauv cov ntawv qhia zaub mov tom ntej, hauv ntu "Xaiv hom kev cai", koj yuav tsum xaiv kab "Siv tus qauv los txiav txim siab cov cell formatted."
6. Tam sim no, hauv kab nkag hauv qab no ntu ntu, koj yuav tsum manually sau cov qauv ntawm cov keyboard “=COUNTIF($B$2:$B$9; B2)>1”. Cov tsiaj ntawv nyob rau hauv kab lus qhia txog qhov ntau ntawm cov cell uas formatting thiab nrhiav cov duplicates yuav ua. Nyob rau hauv cov brackets, nws yog ib qho tsim nyog yuav tau sau ib qho tshwj xeeb ntawm cov lus hais thiab dai duas cov cim ntawm lub hlwb kom cov qauv tsis "tshem tawm" thaum lub sij hawm formatting.

7. Yog tias xav tau, hauv "Tsim txoj cai formatting", tus neeg siv tuaj yeem nyem rau ntawm "Format" khawm txhawm rau txhawm rau txheeb xyuas cov xim uas yuav siv los qhia cov duplicates hauv lub qhov rais tom ntej. Qhov no yog qhov yooj yim, vim hais tias rov ua qhov tseem ceeb tam sim ntawd ntes lub qhov muag.

Ua tib zoo mloog! Koj tuaj yeem pom cov duplicates hauv Excel spreadsheet manually, ntawm qhov muag, los ntawm kev txheeb xyuas txhua lub xov tooj. Txawm li cas los xij, qhov no yuav siv sijhawm ntau rau tus neeg siv, tshwj xeeb tshaj yog tias lub rooj loj raug kuaj xyuas.

Txoj Kev 2: Nrhiav thiab tshem tawm qhov tseem ceeb uas siv lub khawm Tshem Tawm Duplicates

Microsoft Office Excel muaj qhov tshwj xeeb uas tso cai rau koj kom tshem tawm cov hlwb tam sim nrog cov ntaub ntawv sib npaug ntawm lub rooj. Qhov kev xaiv no tau qhib raws li hauv qab no:

1. Ib yam li ntawd, taw qhia ib lub rooj los yog ntau yam ntawm cov hlwb hauv Excel.
2. Nyob rau hauv daim ntawv teev cov cuab yeej nyob rau sab saum toj ntawm lub ntsiab zaub mov ntawm qhov kev pab cuam, nyem rau ntawm lo lus "Cov ntaub ntawv" ib zaug nrog rau sab laug nas khawm.
3. Hauv "Ua haujlwm nrog cov ntaub ntawv" ntu ntu, nyem rau ntawm "Delete duplicates" khawm.

4. Hauv cov ntawv qhia zaub mov uas yuav tsum tshwm sim tom qab ua tiav cov kev tswj hwm saum toj no, kos lub thawv nyob ib sab ntawm kab "Kuv cov ntaub ntawv" muaj cov ntawv sau. Hauv seem "Kem", cov npe ntawm tag nrho cov kab ntawm lub phaj yuav raug sau, koj kuj yuav tsum tau kos lub thawv nyob ib sab ntawm lawv, thiab tom qab ntawd nyem "OK" hauv qab ntawm lub qhov rais.

5. Ib qho kev ceeb toom txog pom duplicates yuav tshwm sim ntawm qhov screen. Lawv yuav raug rho tawm.

Tseem ceeb! Tom qab uninstalling duplicate qhov tseem ceeb, lub phaj yuav tsum tau coj mus rau "tsim" daim ntawv manually los yog siv formatting xaiv, vim hais tias ib co kab thiab kab yuav tsiv tawm.

Txoj Kev 3: Siv lub tshuab lim siab heev

Txoj kev no ntawm kev tshem tawm duplicates muaj ib qho yooj yim siv. Txhawm rau ua kom tiav nws, koj yuav xav tau:

1. Hauv seem "Cov ntaub ntawv", tom ntej no mus rau "Filter" khawm, nyem rau ntawm lo lus "Advanced". Lub qhov rais Advanced Filter qhib.

2. Tso lub toggle hloov ntawm ib sab ntawm kab "Cov txiaj ntsig luam tawm mus rau lwm qhov chaw" thiab nyem rau ntawm lub cim nyob ib sab ntawm "Pib ntau" teb.
3. Xaiv nrog tus nas qhov ntau ntawm cov hlwb uas koj xav nrhiav duplicates. Lub qhov rais xaiv yuav cia li kaw.
4. Tom ntej no, hauv kab "Qhov chaw tshwm sim nyob rau hauv ntau", koj kuj yuav tsum nyem LMB ntawm lub cim kawg thiab xaiv ib lub xov tooj ntawm tes sab nraum lub rooj. Qhov no yuav yog lub hauv paus pib qhov twg cov ntawv kho yuav raug tso.

5. Kos lub thawv "Tsuas yog cov ntaub ntawv tshwj xeeb" thiab nyem "OK". Yog li ntawd, ib lub rooj edited uas tsis muaj duplicates yuav tshwm nyob ib sab ntawm tus thawj array.

Cov ntaub ntawv ntxiv! Cov qub ntau ntawm cov hlwb tuaj yeem raug tshem tawm, tawm hauv tsuas yog cov ntawv kho kom raug.

Txoj Kev 4: Siv PivotTables

Txoj kev no suav tias ua raws li cov hauv qab no ib kauj ruam algorithm:

1. Ntxiv ib qho kev pabcuam rau cov thawj lub rooj thiab tus lej nws los ntawm 1 txog N. N yog tus lej ntawm kab kawg hauv array.

2. Mus rau ntu "Insert" thiab nyem rau ntawm "Pivot Table" khawm.

3. Nyob rau hauv lub qhov rais tom ntej, muab lub toggle hloov nyob rau hauv kab "Rau ib daim ntawv uas twb muaj lawm", nyob rau hauv lub "Table los yog ntau yam" teb, qhia ib tug tshwj xeeb ntau yam ntawm cell.
4. Hauv kab "Range", qhia qhov pib ntawm tes uas kho cov lus array yuav raug ntxiv thiab nyem rau ntawm "OK".

5. Nyob rau hauv lub qhov rais nyob rau sab laug ntawm daim ntawv ua hauj lwm, kos lub thawv nyob ib sab ntawm cov npe ntawm kab lus.

6. Tshawb xyuas qhov tshwm sim.

xaus

Yog li, muaj ntau txoj hauv kev los tshem tawm cov duplicates hauv Excel. Txhua txoj hauv kev tuaj yeem hu ua yooj yim thiab siv tau. Yuav kom nkag siab lub ntsiab lus, koj yuav tsum ua tib zoo nyeem cov ntaub ntawv saum toj no.