Hvernig á að bera saman tvo dálka í Excel og fjarlægja afrit (auðkenna, lita, færa)

Það tekur þig um 10 mínútur að lesa þessa grein. Á næstu 5 mínútum geturðu auðveldlega borið saman tvo dálka í Excel og fundið út hvort það séu afrit í þeim, eyða þeim eða auðkenna þá í lit. Svo, tíminn er kominn!

Excel er mjög öflugt og virkilega flott forrit til að búa til og vinna mikið magn af gögnum. Ef þú ert með nokkrar vinnubækur með gögnum (eða bara eina risastóra töflu), þá viltu líklega bera saman 2 dálka, finna tvítekin gildi og gera eitthvað við þá, til dæmis eyða, auðkenna eða hreinsa innihaldið. Dálkar geta verið í sömu töflu, verið aðliggjandi eða ekki aðliggjandi, geta verið staðsettir á 2 mismunandi blöðum eða jafnvel í mismunandi bókum.

Ímyndaðu þér að við höfum 2 dálka með nöfnum fólks - 5 nöfn í hverjum dálki A og 3 nöfn í dálki B. Þú þarft að bera saman nöfnin í þessum tveimur dálkum og finna afrit. Eins og þú skilur eru þetta uppdiktuð gögn, tekin eingöngu sem dæmi. Í alvöru töflum erum við að fást við þúsundir eða jafnvel tugþúsundir skráa.

Valkostur A: báðir dálkarnir eru á sama blaði. Til dæmis dálkur A og dálki B.

Valkostur B: Dálkarnir eru á mismunandi blöðum. Til dæmis dálkur A á blaðinu Sheet2 og dálki A á blaðinu Sheet3.

Excel 2013, 2010 og 2007 eru með innbyggt tól Fjarlægðu afrit (Fjarlægja afrit) en það er máttlaust í þessum aðstæðum þar sem það getur ekki borið saman gögn í 2 dálkum. Þar að auki getur það aðeins fjarlægt afrit. Það eru engir aðrir valkostir eins og að auðkenna eða breyta litum. Og benda!

Næst mun ég sýna þér mögulegar leiðir til að bera saman tvo dálka í Excel, sem gerir þér kleift að finna og fjarlægja afrit af skrám.

• Berðu saman 2 dálka og leitaðu að afritum með formúlum
  
  • Valkostur A: báðir dálkarnir eru á sama blaði
  • Valkostur B: Dálkarnir eru á mismunandi blöðum eða í mismunandi vinnubókum
• Vinnsla á fundnum afritum
  
  • Sýna aðeins tvíteknar línur í dálki A
  • Breyttu lit eða auðkenndu afrit sem finnast
  • Fjarlægðu afrit úr fyrsta dálki

Berðu saman 2 dálka í Excel og finndu tvíteknar færslur með formúlum

Valkostur A: báðir dálkarnir eru á sama blaði

1. Í fyrsta tóma reitnum (í dæminu okkar er þetta reit C1), skrifum við eftirfarandi formúlu:

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

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

   

   Í formúlunni okkar A1 þetta er fyrsta reitinn í fyrsta dálknum sem við ætlum að bera saman. $B$1 и $B$10000 þetta eru heimilisföng fyrstu og síðustu frumna í öðrum dálki, sem við munum framkvæma samanburðinn með. Taktu eftir algildum tilvísunum - dálkstöfum og línunúmerum er á undan dollaramerki ($). Ég nota algjörar tilvísanir þannig að frumuföng haldist þau sömu þegar formúlur eru afritaðar.

   Ef þú vilt finna afrit í dálki B, breyttu tilvísunum þannig að formúlan líti svona út:

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

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

   Í staðinn "Aðeins"Og"Afrit» Þú getur skrifað eigin merkimiða, til dæmis, «Ekki fundið"Og"Fundið“, eða farðu aðeins eftir “Afrit' og sláðu inn bil í staðinn fyrir annað gildi. Í síðara tilvikinu verða frumurnar sem engar afrit finnast fyrir áfram tómar og ég tel að þessi framsetning gagnanna sé hentugust fyrir frekari greiningu.

2. Nú skulum við afrita formúluna okkar í allar frumurnar í dálknum C, alla leið niður í neðstu röðina, sem inniheldur gögnin í dálknum A. Til að gera þetta skaltu færa músarbendilinn í neðra hægra hornið á reitnum C1, mun bendillinn vera í formi svarts krosshárs, eins og sýnt er á myndinni hér að neðan:Smelltu og haltu inni vinstri músarhnappi og dragðu ramma rammans niður, auðkenndu allar frumur þar sem þú vilt setja formúluna inn. Þegar allar nauðsynlegar frumur eru valdar, slepptu músarhnappnum:

   

Ábending: Í stórum töflum verður afritun formúlunnar hraðari ef þú notar flýtilykla. Auðkenndu hólf C1 og ýttu Ctrl + C (til að afrita formúluna á klemmuspjaldið), smelltu síðan á Ctrl + Shift + End (til að velja allar reiti sem ekki eru auðar í dálki C) og ýttu að lokum á Ctrl + V (til að setja formúluna inn í allar valdar frumur).

3. Frábært, nú eru öll tvítekin gildi merkt sem "Afrit":

Valkostur B: tveir dálkar eru á mismunandi blöðum (í mismunandi vinnubókum)

1. Í fyrsta reit fyrsta tóma dálksins á vinnublaðinu Sheet2 (í okkar tilfelli er það dálkur B) sláðu inn eftirfarandi formúlu:

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

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

   Hér Sheet3 er nafn blaðsins sem 2. dálkur er á, og $A$1:$A$10000 eru frumföng frá 1. til síðasta í þessum 2. dálki.

2. Afritaðu formúluna í allar frumur í dálki B (sama og valkostur A).
3. Við fáum þessa niðurstöðu:

Vinnsla á fundnum afritum

Frábært, við höfum fundið færslur í fyrsta dálknum sem eru einnig til staðar í öðrum dálki. Nú þurfum við að gera eitthvað með þeim. Að fara í gegnum allar tvíteknar færslur í töflu handvirkt er frekar óhagkvæmt og tekur of mikinn tíma. Það eru betri leiðir.

Sýna aðeins tvíteknar línur í dálki A

Ef dálkarnir þínir eru ekki með hausa, þá þarftu að bæta þeim við. Til að gera þetta skaltu setja bendilinn á töluna sem táknar fyrstu línuna og hún mun breytast í svarta ör, eins og sýnt er á myndinni hér að neðan:

Hægrismelltu og veldu úr samhengisvalmyndinni Innsetning (Setja inn):

Gefðu dálkunum nöfn, til dæmis „heiti"Og"Afrit?» Opnaðu síðan flipann Gögn (Gögn) og ýttu á síur (Sía):

Eftir það smelltu á litlu gráu örina við hliðina á “Afrit?« til að opna síuvalmyndina; hakaðu við öll atriði á þessum lista nema Afritog ýttu á OK.

Það er allt, nú sérðu aðeins þá þætti dálksins А, sem eru afrituð í dálknum В. Það eru aðeins tvær slíkar frumur í þjálfunartöflunni okkar, en eins og þú skilur, í reynd verða þær miklu fleiri.

Til að birta allar línur dálks aftur А, smelltu á síutáknið í dálknum В, sem lítur nú út eins og trekt með lítilli ör, og veldu velja allt (Velja allt). Eða þú getur gert það sama í gegnum borðið með því að smella Gögn (Gögn) > Veldu & Sía (Raða og sía) > Hreinsa (Hreinsa) eins og sýnt er á skjámyndinni hér að neðan:

Breyttu lit eða auðkenndu afrit sem finnast

Ef athugasemdirnar „Afrit” er ekki nóg fyrir tilgang þinn og þú vilt merkja tvíteknar frumur með öðrum leturlit, fyllingarlit eða einhverri annarri aðferð…

Í þessu tilviki, síaðu afritin eins og sýnt er hér að ofan, veldu allar síaðar frumur og smelltu Ctrl + 1til að opna gluggann Sniðið frumur (frumusnið). Sem dæmi skulum við breyta fyllingarlit frumna í röðum með afritum í skærgult. Auðvitað geturðu breytt fyllingarlitnum með tólinu Fylla (Fill litur) flipinn Heim (Heima) en kostur við valmynd Sniðið frumur (Cell Format) þar sem þú getur stillt alla sniðvalkosti á sama tíma.

Nú muntu örugglega ekki missa af neinum frumum með afritum:

Fjarlægir tvítekin gildi úr fyrsta dálknum

Síuðu töfluna þannig að aðeins frumur með tvítekin gildi séu sýndar og veldu þá reiti.

Ef dálkarnir 2 sem þú ert að bera saman eru á mismunandi blöðum, það er, í mismunandi töflum, hægrismelltu á valið svið og veldu Eyða röð (Fjarlægja línu):

Press OKþegar Excel biður þig um að staðfesta að þú viljir virkilega eyða allri blaðlínunni og hreinsa síðan síuna. Eins og þú sérð eru aðeins línur með einstök gildi eftir:

Ef 2 dálkar eru á sama blaði, nálægt hvert öðru (aðliggjandi) eða ekki nálægt hvert öðru (ekki aðliggjandi), þá verður ferlið við að fjarlægja afrit aðeins flóknara. Við getum ekki fjarlægt alla röðina með tvíteknum gildum, þar sem þetta mun fjarlægja frumurnar úr öðrum dálknum líka. Svo að skilja aðeins eftir einstakar færslur í dálki А, gerðu þetta:

1. Síuðu töfluna til að sýna aðeins tvöföld gildi og veldu þær frumur. Hægrismelltu á þá og veldu úr samhengisvalmyndinni Hreinsa innihald (skýrt innihald).
2. Hreinsaðu síuna.
3. Veldu allar frumur í dálki А, frá frumunni A1 alla leið niður á botn sem inniheldur gögnin.
4. Smelltu á Gögn (Gögn) og ýttu á Raða A til Ö (Raða frá A til Ö). Í glugganum sem opnast velurðu Haltu áfram með núverandi val (Raða innan tilgreinds vals) og smelltu á hnappinn Black (Röðun):
5. Eyddu dálknum með formúlunni, þú þarft hana ekki lengur, héðan í frá hefurðu aðeins einstök gildi.
6. Það er það, nú dálkurinn А inniheldur aðeins einstök gögn sem eru ekki í dálknum В:

Eins og þú sérð er ekki svo erfitt að fjarlægja afrit úr tveimur dálkum í Excel með formúlum.