Kahden sarakkeen vertaaminen Excelissä ja kaksoiskappaleiden poistaminen (korosta, väritä, siirrä)

Tämän artikkelin lukemiseen menee noin 10 minuuttia. Seuraavien 5 minuutin aikana voit helposti verrata kahta saraketta Excelissä ja selvittää, onko niissä kaksoiskappaleita, poistaa ne tai korostaa ne värillisinä. Eli aika on tullut!

Excel on erittäin tehokas ja todella siisti sovellus suurten tietomäärien luomiseen ja käsittelyyn. Jos sinulla on useita tietoja sisältäviä työkirjoja (tai vain yksi valtava taulukko), haluat luultavasti vertailla kahta saraketta, etsiä päällekkäisiä arvoja ja sitten tehdä niille jotain, esimerkiksi poistaa, korostaa tai tyhjentää sisältö . Sarakkeet voivat olla samassa taulukossa, vierekkäin tai ei vierekkäin, voivat sijaita kahdella eri arkilla tai jopa eri kirjoissa.

Kuvittele, että meillä on 2 saraketta ihmisten nimillä – 5 nimeä sarakkeessa A ja 3 nimeä sarakkeessa B. Sinun on verrattava näiden kahden sarakkeen nimiä ja löydettävä kaksoiskappaleet. Kuten ymmärrät, tämä on kuvitteellista dataa, joka otetaan yksinomaan esimerkiksi. Oikeissa taulukoissa olemme tekemisissä tuhansien tai jopa kymmenien tuhansien tietueiden kanssa.

Vaihtoehto A: molemmat sarakkeet ovat samalla arkilla. Esimerkiksi sarake A ja sarake B.

Vaihtoehto B: Sarakkeet ovat eri arkeilla. Esimerkiksi sarake A arkilla Sheet2 ja sarake A arkilla Sheet3.

Excel 2013, 2010 ja 2007 sisältävät sisäänrakennetun työkalun Poista kaksoiskappaleet (Poista kaksoiskappaleet), mutta se on voimaton tässä tilanteessa, koska se ei voi verrata tietoja kahdessa sarakkeessa. Lisäksi se voi poistaa vain kaksoiskappaleita. Ei ole muita vaihtoehtoja, kuten korostus tai värien vaihtaminen. Ja pointti!

Seuraavaksi näytän sinulle mahdollisia tapoja verrata kahta saraketta Excelissä, jonka avulla voit etsiä ja poistaa päällekkäisiä tietueita.

• Vertaa kahta saraketta ja etsi kaksoiskappaleita kaavojen avulla
  
  • Vaihtoehto A: molemmat sarakkeet ovat samalla arkilla
  • Vaihtoehto B: Sarakkeet ovat eri arkeilla tai eri työkirjoissa
• Löytyneiden kaksoiskappaleiden käsittely
  
  • Näytä vain päällekkäiset rivit sarakkeessa A
  • Vaihda väriä tai korosta löydetyt kaksoiskappaleet
  • Poista kaksoiskappaleet ensimmäisestä sarakkeesta

Vertaa kahta saraketta Excelissä ja etsi päällekkäisiä merkintöjä kaavojen avulla

Vaihtoehto A: molemmat sarakkeet ovat samalla arkilla

1. Ensimmäiseen tyhjään soluun (esimerkissämme tämä on solu C1) kirjoitamme seuraavan kaavan:

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

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

   

   Meidän kaavassa A1 tämä on ensimmäinen solu ensimmäisestä sarakkeesta, jota aiomme vertailla. 1 dollaria и 10000 dollaria nämä ovat toisen sarakkeen ensimmäisen ja viimeisen solun osoitteet, joiden avulla suoritamme vertailun. Huomaa absoluuttiset viittaukset – sarakkeiden kirjaimia ja rivinumeroita edeltää dollarimerkki ($). Käytän absoluuttisia viittauksia, jotta solujen osoitteet pysyvät samoina kaavoja kopioitaessa.

   Jos haluat löytää kaksoiskappaleita sarakkeesta B, muuta viittauksia niin, että kaava näyttää tältä:

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

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

   Sen sijaan "Vain"Ja"Monistaa» Voit kirjoittaa omia tarrojasi, esimerkiksi «Ei löydy"Ja"Löydetty"tai jätä vain"Monistaaja kirjoita välilyöntimerkki toisen arvon sijaan. Jälkimmäisessä tapauksessa solut, joille ei löydy kaksoiskappaleita, jäävät tyhjiksi, ja uskon, että tämä tietojen esitys on kätevin jatkoanalyysiä varten.

2. Kopioidaan nyt kaavamme kaikkiin sarakkeen soluihin C, aina alimmalle riville, joka sisältää sarakkeen tiedot A. Voit tehdä tämän siirtämällä hiiren osoittimen solun oikeaan alakulmaan C1, osoitin on mustan hiusristikon muodossa, kuten alla olevassa kuvassa näkyy:Napsauta ja pidä hiiren vasenta painiketta painettuna ja vedä kehyksen reunaa alas korostaen kaikki solut, joihin haluat lisätä kaavan. Kun kaikki tarvittavat solut on valittu, vapauta hiiren painike:

   

Vihje: Suurissa taulukoissa kaavan kopioiminen on nopeampaa, jos käytät pikanäppäimiä. Korosta solu C1 ja paina Ctrl + C (kopioi kaava leikepöydälle), napsauta sitten Ctrl + Vaihto + End (valitseksesi kaikki ei-tyhjät solut sarakkeesta C) ja paina lopuksi Ctrl + V (lisätäksesi kaavan kaikkiin valittuihin soluihin).

3. Hienoa, nyt kaikki päällekkäiset arvot on merkitty "Monistaa":

Vaihtoehto B: kaksi saraketta on eri arkeilla (eri työkirjoissa)

1. Työtaulukon ensimmäisen tyhjän sarakkeen ensimmäisessä solussa Sheet2 (meidän tapauksessamme se on sarake B) syötä seuraava kaava:

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

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

   Tässä Sheet3 on sen arkin nimi, jolla toinen sarake sijaitsee, ja $ A $ 1: $ A $ 10000 ovat tämän 1. sarakkeen soluosoitteita 2. viimeisestä.

2. Kopioi kaava kaikkiin sarakkeen soluihin B (sama kuin vaihtoehto A).
3. Saamme tämän tuloksen:

Löytyneiden kaksoiskappaleiden käsittely

Hienoa, olemme löytäneet ensimmäisestä sarakkeesta merkintöjä, jotka ovat myös toisessa sarakkeessa. Nyt meidän on tehtävä jotain heidän kanssaan. Taulukon kaikkien päällekkäisten tietueiden läpikäyminen manuaalisesti on melko tehotonta ja vie liian paljon aikaa. On parempiakin tapoja.

Näytä vain päällekkäiset rivit sarakkeessa A

Jos sarakkeissasi ei ole otsikoita, sinun on lisättävä ne. Aseta kohdistin ensimmäistä riviä edustavan numeron kohdalle, jolloin se muuttuu mustaksi nuoleksi alla olevan kuvan mukaisesti:

Napsauta hiiren kakkospainikkeella ja valitse pikavalikosta lisäys (Lisää):

Nimeä sarakkeet, esimerkiksi "Nimi"Ja"Kopioidaanko?» Avaa sitten välilehti Päiväys (Data) ja paina Suodattaa (Suodattaa):

Napsauta sen jälkeen pientä harmaata nuolta kohdan "Kopioidaanko?« avataksesi suodatinvalikon; poista valinta kaikista tämän luettelon kohteista paitsi Monistaa, ja paina OK.

Siinä kaikki, nyt näet vain sarakkeen elementit А, jotka on kopioitu sarakkeessa В. Harjoitustaulukossamme on vain kaksi tällaista solua, mutta kuten ymmärrät, käytännössä niitä tulee olemaan paljon enemmän.

Voit näyttää sarakkeen kaikki rivit uudelleen А, napsauta sarakkeen suodatinsymbolia В, joka näyttää nyt suppilolta, jossa on pieni nuoli, ja valitse Valitse kaikki (Valitse kaikki). Tai voit tehdä saman nauhan kautta napsauttamalla Päiväys (Data) > Valitse & Suodata (Lajittele ja suodata) > Poista valinta (Tyhjennä) alla olevan kuvakaappauksen mukaisesti:

Vaihda väriä tai korosta löydetyt kaksoiskappaleet

Jos muistiinpanot "Monistaa” ei riitä tarkoituksiin, ja haluat merkitä päällekkäiset solut eri fontin värillä, täyttövärillä tai jollain muulla tavalla…

Suodata tässä tapauksessa kaksoiskappaleet yllä olevan kuvan mukaisesti, valitse kaikki suodatetut solut ja napsauta Ctrl + 1avataksesi valintaikkunan formaatti Cells (solumuoto). Muutetaan esimerkiksi kaksoiskappaleiden rivien solujen täyttöväri kirkkaan keltaiseksi. Voit tietysti muuttaa täyttöväriä työkalulla Täyttää (Täyttöväri) -välilehti Koti (Koti), mutta valintaikkunan etu formaatti Cells (Solumuoto), jossa voit määrittää kaikki muotoiluasetukset samanaikaisesti.

Nyt et varmasti menetä soluja, joissa on kaksoiskappaleita:

Päällekkäisten arvojen poistaminen ensimmäisestä sarakkeesta

Suodata taulukko niin, että vain solut, joissa on päällekkäisiä arvoja, näytetään, ja valitse kyseiset solut.

Jos vertailemasi 2 saraketta ovat eri taulukoilla, eli eri taulukoissa napsauta valittua aluetta hiiren kakkospainikkeella ja valitse Poista rivi (Poista rivi):

lehdistö OKkun Excel pyytää sinua vahvistamaan, että haluat todella poistaa koko arkin rivin, ja tyhjentää sitten suodattimen. Kuten näet, vain rivit, joilla on yksilölliset arvot, jää jäljelle:

Jos 2 saraketta on samalla arkilla, lähellä toisiaan (vieressä) tai ei lähellä toisiaan (ei vierekkäin), kaksoiskappaleiden poistaminen on hieman monimutkaisempaa. Emme voi poistaa koko riviä, jossa on päällekkäisiä arvoja, koska tämä poistaa solut myös toisesta sarakkeesta. Joten sarakkeeseen jätetään vain yksilölliset merkinnät А, toimi näin:

1. Suodata taulukko näyttämään vain päällekkäiset arvot ja valitse kyseiset solut. Napsauta niitä hiiren kakkospainikkeella ja valitse pikavalikosta Selkeä sisältö (selkeä sisältö).
2. Puhdista suodatin.
3. Valitse kaikki sarakkeen solut А, alkaen solusta A1 aina tiedot sisältävään pohjaan asti.
4. Valitse Päiväys (Data) ja paina Lajittele A - Z (Lajittele A:sta Z:hen). Valitse avautuvasta valintaikkunasta Jatka nykyisellä valinnalla (Lajittele määritetyn valinnan sisällä) ja napsauta painiketta Musta (lajittelu):
5. Poista sarake kaavalla, et enää tarvitse sitä, tästä lähtien sinulla on vain yksilölliset arvot.
6. Siinä se, nyt kolumni А sisältää vain ainutlaatuisia tietoja, joita ei ole sarakkeessa В:

Kuten näet, kaksoiskappaleiden poistaminen kahdesta sarakkeesta Excelissä kaavoilla ei ole niin vaikeaa.