Kahden Excel-taulukon vertaaminen kaksoiskappaleille: 5 nopeaa tapaa

Kun työskentelet suurten tietojoukkojen kanssa Excelissä, on yleistä, että käytössä on useita samanlaisia ​​tietoja sisältäviä laskentataulukoita. Näiden arkkien vertaaminen päällekkäisiin tietueisiin voi olla melkoinen haaste, varsinkin kun tiedot ovat hajallaan eri sarakkeissa ja riveissä.

Yleisimmät menetelmät kaksoiskappaleiden etsimiseen kahdesta Excel-taulukosta ovat:

1. VLOOKUP-, COUNTIF- tai EXACT-funktiot

2. Ehdollinen muotoilu

3. Power Query

4. Ulkoiset työkalut ja lisäosat

5. Kaksoiskappaleiden silmämääräinen tarkistus

Tässä artikkelissa käydään vaihe vaiheelta läpi nämä viisi tapaa paikantaa ja käsitellä useiden laskentataulukoiden kaksoiskappaleita. Lopussa näet, kuinka helppoa on hallita päällekkäisiä arvoja.

Aloitetaan!

Sisällysluettelo

• Esimerkkilaskentataulukoita ja tietoja
• 1. VLOOKUP-, COUNTIF- tai EXACT-funktioiden käyttäminen kopioiden etsimiseen
  • A. Kuinka käytät VLOOKUP-toimintoa kaksoiskappaleiden etsimiseen kahdelta arkilta?
    • Entä jos käsittelet erilaisia ​​työkirjoja?
  • B. Kuinka käytät COUNTIF-funktiota kopioiden etsimiseen laskentataulukoista?
  • C. Kuinka käytät EXACT-funktiota kopioiden etsimiseen laskentataulukoista?
• 2. Ehdollisen muotoilun käyttäminen päällekkäisille riveille
  • Ehdollisen muotoilun sääntöjen hallinnan käyttäminen
• 3. Power Queryn käyttäminen kaksoiskappaleiden etsimiseen laskentataulukoista
• 4. Työkalut ja apuohjelmat laskentataulukoiden kaksoiskappaleiden tunnistamiseen
• 5. Kaksoiskappaleiden visuaalinen tarkistaminen kahdelta arkilta
• 3 vinkkiä Excel-laskentataulukoiden valmisteluun
• Kuinka käsitellä virheitä ja epäjohdonmukaisuuksia
• Lopulliset ajatukset

Esimerkkilaskentataulukoita ja tietoja

Noudata tämän artikkelin esimerkkejä luomalla uusi Excel-työkirja, jossa on nämä tiedot ensimmäisen arkin sarakkeessa A:

• Omena

• Oranssi

• Päärynä

• Mansikka

Laita nämä tiedot toisen arkin sarakkeeseen A:

• Päärynä

• Mansikka

• Omena

• Ananas

Työkirjasi näyttävät tältä:

Kun olet määrittänyt laskentataulukot, voimme nyt käydä läpi erilaisia ​​tapoja löytää kaksoiskappaleita kahdelta taulukolta alkaen VLOOKUP-, COUNTIF- ja EXACT-funktioista.

Mennään!

1. VLOOKUP-, COUNTIF- tai EXACT-funktioiden käyttäminen kopioiden etsimiseen

Excelissä on kolme sisäänrakennettua toimintoa, jotka tekevät kopioiden löytämisestä helppoa: VLOOKUP , COUNTIF ja EXACT .

Nämä toiminnot on suunniteltu auttamaan sinua löytämään, laskemaan ja vertailemaan tietoja laskentataulukoista, mikä tekee niistä ihanteellisia työkaluja päällekkäisten merkintöjen etsimiseen.

Jokainen toiminto palvelee ainutlaatuista tarkoitusta, ja niiden käytön ymmärtäminen voi merkittävästi virtaviivaistaa tietojen analysointiprosessia.

Seuraavissa osissa tutkimme, kuinka voit käyttää VLOOKUP-, COUNTIF- ja EXACT-funktioita tunnistamaan tehokkaasti kaksoiskappaleet Excel-laskentataulukoistasi.

A. Kuinka käytät VLOOKUP-toimintoa kaksoiskappaleiden etsimiseen kahdelta arkilta?

VLOOKUP on lyhenne sanoista Vertical Lookup. Tämän toiminnon avulla voit etsiä päällekkäisiä arvoja kahden sarakkeen välillä. Tämä on syntaksi:

=VHAKU(hakuarvo, taulukon_taulukko, sarakkeen_indeksin_määrä, [alueen_haku])

• lookup_value : Arvo, jota haluat etsiä taulukon_taulukon ensimmäisestä sarakkeesta.

• table_array : Solualue, joka sisältää tiedot, joista haluat etsiä.

• col_index_num : Taulukon_taulukon sarakkeen numero, josta haluat palauttaa arvon.

• range_lookup : Valinnainen. Se on joko TOSI (likimääräinen vastaavuus) tai EPÄTOSI (tarkka vastaavuus). Oletusarvo on TOSI.

Jos haluat käyttää toimintoa kahdessa Excel-tiedoston laskentataulukossa, sinun on osattava viitata erilliseen taulukkoon kaavassa. Kirjoita taulukon nimi, huutomerkki (!) ja solusi tai solualue.

Tämä viittaa esimerkiksi soluihin A2–A5 saman työkirjan taulukossa 2: Sheet 2!$A$2:$A$5.

Voit käyttää VLOOKUP-funktiota esimerkkilaskentataulukossasi seuraavasti:

1. Valitse solu B2 näyttääksesi ensimmäisen vertailutuloksen.

2. Kirjoita tämä kaava: =VHAKU(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. Näytä vertailutulos painamalla Enter.

4. Täytä kaava vertaillaksesi ensimmäisen arkin muiden rivien arvoja.

Tulokset näyttävät alla olevasta kuvasta:

Voit siistiä näyttöä näyttämällä käyttäjäystävällisen viestin virheilmoituksen sijaan, kun kaksoiskappaletta ei löydy.

Esimerkiksi tämä kaava näyttää "Kyllä" tai "Ei" löydetyille ja ei löydy arvoille:

=JOS(ISNA(VHAKU(A2, Taulukko 2!$A$2:$A$5, 1, EPÄTOSI)), "Ei", "Kyllä")

Lisää esimerkkejä tästä toiminnosta on artikkelissamme .

Entä jos käsittelet erilaisia ​​työkirjoja?

Jos laskentataulukot ovat erillisissä työkirjoissa, funktion käyttö on sama kahdella Excel-tiedostolla. Toiseen laskentataulukkoon viittaaminen on kuitenkin hieman monimutkaisempaa.

Sinun täytyy:

• Liitä Excel-työkirjan nimi suluihin

• Kirjoita perässä laskentataulukon nimi

• Laita työkirja ja laskentataulukko lainausmerkkeihin.

Jos solut ovat esimerkiksi "WB 2.xlsx"-nimisessä työkirjassa Sheet2-nimisessä taulukossa, muoto näyttää tältä:

'[WB 2.xlsx]Sheet2'!$A$2:$A$5

Sulje toinen työkirja ennen kaavan syöttämistä. Muuten saat virheilmoituksen.

B. Kuinka käytät COUNTIF-funktiota kopioiden etsimiseen laskentataulukoista?

Excelin COUNTIF-funktiota käytetään laskemaan tietyn alueen solujen lukumäärä, jotka täyttävät tietyt ehdot.

Voit verrata useita arkkeja laskemalla toisen laskentataulukon solujen määrän, joka vastaa ensimmäisen laskentataulukon solua.

Tämä on funktion syntaksi:

=LASKEJOS(väli, kriteeri)

• Alue : solualue, jonka haluat laskea määritettyjen ehtojen perusteella.

• Kriteerit : ehto, jonka on täytyttävä, jotta solu voidaan laskea.

Voit käyttää funktiota esimerkkitietojen kanssa seuraavasti:

1. Valitse solu B2 näyttääksesi ensimmäisen vertailutuloksen.

2. Kirjoita tämä kaava: =COUNTIF(Sheet2!$A$2:$A$5, A2)

3. Näytä vertailutulos painamalla Enter.

4. Täytä kaava vertaillaksesi ensimmäisen arkin muiden rivien arvoja.

Funktio löytää yhden vastaavuuden joillekin soluille ja ei yhtään toisille. Vertailusolussa näkyy luku. Tässä tulos näytetiedoista:

COUNTIF-toimintoa voidaan käyttää muihin hyödyllisiin tehtäviin, kuten .

C. Kuinka käytät EXACT-funktiota kopioiden etsimiseen laskentataulukoista?

Excelin EXACT-funktiota voidaan käyttää myös kaksoiskappaleiden etsimiseen samoista soluista kahdessa eri Excel-laskentataulukossa. Syntaksi on:

=TARKKA(teksti1, teksti2)

• teksti1 on ensimmäinen tekstimerkkijono, jota haluat verrata.

• text2 on toinen tekstimerkkijono, jota haluat verrata.

Toimi seuraavasti:

1. Valitse solu B2.

2. Kirjoita kaava =EXACT(A2, Sheet2!A2)

3. Näytä vertailutulos painamalla Enter. Kaava palauttaa arvon TOSI, jos molemmat arvot ovat identtisiä, tai muutoin EPÄTOSI.

4. Täytä kaava vertaillaksesi ensimmäisen arkin muiden rivien arvoja.

Huomaa, että tämä menetelmä ei etsi kaksoiskappaleita solualueelta. Sen sijaan etsit vain osumia, jotka perustuvat eri taulukon samaan soluun.

Tästä voi olla hyötyä järjestetyissä tiedoissa, joissa odotat vain muutamia poikkeuksia.

VLOOKUP-, COUNTIF- ja EXACT-funktiot ovat hyödyllisiä kaksoiskappaleiden etsimisessä, mutta Excel on monipuolinen ohjelma, ja muitakin vaihtoehtoja on. Seuraavassa osiossa tarkastellaan, kuinka voit käyttää ehdollista muotoilua kahden arkin kaksoiskappaleiden tunnistamiseen.

2. Ehdollisen muotoilun käyttäminen päällekkäisille riveille

Tässä osiossa opit käyttämään ehdollista muotoilua kahden Excel-laskentataulukon päällekkäisten rivien etsimiseen ja korostamiseen.

Voit luoda ehdollisen muotoilusäännön seuraavasti:

1. Valitse tiedot sisältävä solualue (tässä tapauksessa A2:A5).

2. Napsauta Excel-nauhan Koti-välilehteä.

3. Napsauta "Ehdollinen muotoilu" "Tyylit" -ryhmässä.

4. Valitse avattavasta valikosta "Uusi sääntö".

Seuraava tehtävä on luoda kaava sääntöäsi varten. Toimi seuraavasti:

1. Valitse valintaikkunassa "Käytä kaavaa määrittääksesi, mitkä solut muotoillaan".

2. Syötä seuraava kaava: =LASKEJOS(Sheet2!$A$2:$A$5, A2) > 0

Lopuksi käytät haluamaasi muotoilua päällekkäisille soluille.

1. Napsauta Muotoile-painiketta avataksesi Muotoile solut -valintaikkunan.

2. Valitse muoto, esim. täytä kaksoiskappaleet keltaisella taustavärillä.

3. Napsauta OK.

Päällekkäiset tietosi on nyt korostettu keltaisella.

Ehdollisen muotoilun sääntöjen hallinnan käyttäminen

Kun olet luonut ehdollisen muotoilusäännön, voit hallita sitä Ehdollisen muotoilun sääntöjen hallinnan avulla.

Pääset hallinnoijaan seuraavasti:

1. Siirry Koti-välilehdelle.

2. Napsauta Ehdollinen muotoilu.

3. Valitse "Hallitse sääntöjä".

Näet luettelon kaikista valittuun taulukkoon sovelletuista ehdollisista muotoilusäännöistä. Voit muokata, poistaa tai muuttaa sääntöjen järjestystä valitsemalla säännön ja napsauttamalla asianmukaisia ​​painikkeita.

Voit soveltaa samaa sääntöä toiseen taulukkoon seuraavasti:

1. Valitse toiselta arkilta alue, jota haluat verrata.

2. Siirry ehdollisen muotoilun sääntöjen hallintaan.

3. Valitse sääntö, napsauta Kopioi sääntöä ja napsauta sitten Muokkaa sääntöä.

4. Korvaa "Sheet2" ensimmäisen vertailtavan arkin nimellä.

Nyt kun olet käyttänyt ehdollista muotoilusääntöä molemmissa taulukoissa, kaksoiskappaleet korostetaan valitsemasi muotoilun mukaan.

Muista säätää kaavoissa olevaa aluetta ja soluviittauksia tarpeen mukaan kattaaksesi kaikki verrattavat tiedot.

Ehdollinen muotoilu saattaa tuntua hieman primitiiviseltä. Jos haluat tarkemman hallinnan, Power Query voi olla vastaus! Seuraavassa osiossa kerromme, kuinka voit käyttää Power Queryä kaksoiskappaleiden etsimiseen.

3. Power Queryn käyttäminen kaksoiskappaleiden etsimiseen laskentataulukoista

Power Query on Microsoft Excelin tietojen muunnos- ja valmistelutyökalu. Samojen arvojen tunnistaminen on vain yksi monista analyysitehtävistä, joita voit suorittaa työkalulla.

Tätä varten sinun tulee ensin tuoda kahden laskentataulukon tiedot erillisiin taulukoihin. Noudata näitä vaiheita kussakin taulukossa:

1. Napsauta hiiren kakkospainikkeella solualuetta.

2. Valitse "Hae tiedot taulukosta/alueesta".

3. Muuta taulukon nimi sopivaksi.

Kun molemmat taulukot on tuotu, ensimmäinen tehtävä on yhdistää tiedot:

1. Siirry Tiedot-välilehteen.

2. Napsauta "Hae tiedot".

3. Valitse "Yhdistä kyselyt".

4. Valitse "Yhdistä" ja valitse kaksi taulukkoa.

5. Napsauta kahta avainsaraketta.

6. Valitse "Inner" kohdassa "Join Kind" ja napsauta OK.

Power Query Editor avautuu, ja siinä on yhdistetyt tiedot Excel-taulukon molemmista taulukoista. Näet kaksi saraketta, yhden kustakin taulukosta. Koska olet kiinnostunut vain päällekkäisistä arvoista, voit poistaa toisen sarakkeen.

Voit ladata kaksoiskappaleet uuteen laskentataulukkoon napsauttamalla "Sulje ja lataa" Power Query -editorissa.

Tutustu tämän tehokkaan ominaisuuden muihin puoliin seuraamalla esimerkkejä artikkelissamme .

Excelissä on myös kolmannen osapuolen työkaluja ja apuohjelmia, jotka lisäävät mahdollisuuden löytää saumattomasti kaksoiskappaleita, joten katsotaanpa joitain näistä työkaluista seuraavassa osiossa.

4. Työkalut ja apuohjelmat laskentataulukoiden kaksoiskappaleiden tunnistamiseen

Ulkoiset työkalut ja apuohjelmat voivat tarjota lisätoimintoja, jotka eivät välttämättä ole käytettävissä alkuperäisissä Excel-ominaisuuksissa. Nämä työkalut voivat edelleen virtaviivaistaa arkkien vertaamista kaksoiskappaleisiin.

Spreadsheet Compare on Microsoftin työkalu, jonka avulla voit vertailla kahta työkirjaa vierekkäin, korostaa eroja ja tunnistaa helposti kaksoiskappaleita. Voit ladata sen Microsoftin verkkosivustolta.

Voit asentaa useita apuohjelmia, jotka automatisoivat kaksoiskappaleiden etsimisen. Yksi esimerkki on " Duplicate Remover ". Lisäosan asentaminen:

1. Siirry Lisää-välilehteen.

2. Napsauta "Hae lisäosa".

3. Hae "Duplicate".

4. Napsauta "Lisää" valitsemassasi työkalussa.

5. Kaksoiskappaleiden visuaalinen tarkistaminen kahdelta arkilta

Jos mikään muu ei auta, käytä silmiäsi! Excelin Järjestä Windows -valintaikkunassa voit tarkastella useita laskentataulukoita tai työkirjoja vierekkäin.

Vaikka se ei suoraan löydä kaksoiskappaleita, se voi auttaa vertailemaan tietoja visuaalisesti laskentataulukoiden tai työkirjojen välillä kaksoiskappaleiden havaitsemiseksi. Toimi seuraavasti:

1. Napsauta Excel-nauhan Näytä-välilehteä.

2. Napsauta "Ikkuna"-ryhmässä "Järjestä kaikki".

3. Valitse järjestelyvaihtoehto, esim. "Pysty" tai "Vaaka".

Tämä näyttää molemmat arkit joko vierekkäin tai päällekkäin. Nyt voit vertailla jokaisen taulukon tietoja manuaalisesti kaksoiskappaleiden tunnistamiseksi.

Sinun on vieritettävä tietoja ja tarkastettava silmämääräisesti jokainen arvo löytääksesi vastaavuuksia.

Huomaa, että tämä menetelmä ei ole tehokas suurille tietojoukoille, koska se vaatii manuaalista vertailua. Muiden tämän artikkelin menetelmien käyttäminen on tehokkaampaa kaksoiskappaleiden löytämisessä suuremmista tietojoukoista.

Ja tämä on viimeinen yleisimmistä tavoistamme löytää päällekkäisiä arvoja Excel-taulukoista! Seuraavassa osiossa annamme sinulle vinkkejä laskentataulukoiden valmisteluun.

3 vinkkiä Excel-laskentataulukoiden valmisteluun

Ennen kuin aloitat useiden taulukoiden vertailun, varmista, että tietojoukkojesi sarakkeet ja rivit on asetettu oikein.

Varmista, että molemmilla on sama rakenne ja samat otsikonimet. Tarvittaessa voit järjestää molempien arkkien sarakkeet uudelleen vastaamaan toisiaan.

Tässä on kolme ehdotusta tarkan vertailun varmistamiseksi:

1. Järjestä tietosi samaan järjestykseen molemmilla taulukoilla. Tämä helpottaa Excel-toimintojen tehokasta toimintaa.

2. Normalisoi tietosi käyttämällä johdonmukaista muotoilua, isoja kirjaimia ja tietotyyppejä. Tämä estää pienistä eroista johtuvia virheellisiä merkintöjä.

3. Poista tarpeettomat tyhjät rivit tai sarakkeet, koska ne voivat häiritä vertailua.

Voit arvioida, kuinka paljon tietojoukossasi on päällekkäisyyksiä laskemalla yksilölliset arvot. Tämä video käy läpi useita tapoja:

Kuinka käsitellä virheitä ja epäjohdonmukaisuuksia

Tietojesi epäjohdonmukaisuudet voivat vaikuttaa vertailuprosessiin. Tässä on neljä vinkkiä epäjohdonmukaisuuksien ratkaisemiseen:

1. Tarkista, ettei tietotyypeissä ole ristiriitoja, kuten tekstin ja numeeristen arvojen sekoittuminen samassa sarakkeessa.

2. Varmista, että päivämäärille, numeroille ja muille tietotyypeille käytetään johdonmukaista muotoilua.

3. Tarkista tietosi puuttuvien tai virheellisten merkintöjen varalta ja päivitä tarvittaessa.

4. Standardoi tietojoukkoidesi lyhenteet tai epäjohdonmukaiset nimeämiskäytännöt.

Lopulliset ajatukset

Kaksoiskappaleiden etsiminen kahdelta Excel-laskentataulukolta on olennainen tehtävä tietojen hallinnassa ja analysoinnissa, mikä varmistaa tietojen eheyden ja tarkkuuden. Excel tarjoaa useita tekniikoita kaksoiskappaleiden tunnistamiseen, joista jokaisella on omat etunsa ja rajoituksensa.

Menetelmän valinta riippuu käyttäjän tarpeista, tietojoukon koosta ja monimutkaisuudesta sekä halutusta tuloksesta. Pienemmille tietojoukoille ja yksinkertaisille vertailuille voi riittää , COUNTIF tai ehdollisen muotoilun käyttö.

Suurempia tietojoukkoja tai monimutkaisempia tietomuunnoksia varten Power Query on tehokas ja joustava työkalu, joka pystyy käsittelemään monenlaisia ​​tietojen valmistelutehtäviä, mukaan lukien kaksoiskappaleiden etsiminen.

Lopuksi vertaamalla kaksoiskappaleita on erittäin kätevä taito työkalupakkissasi. Tämän artikkelin temppujen avulla voit havaita ärsyttävät kaksoiskappaleet ja pitää tietosi puhtaana.

Luota meihin, kun opit tässä paremmin, selviät datatehtävistäsi ja teet vaikutuksen kaikkiin ympärilläsi!