Hei Paula, voiko Excelissä vertailla luetteloita?
tai
Mikä on paras tapa verrata kahta tietokokonaisuutta Excelissä?
Vielä usein Excelissä on tarve verrata kahta luetteloa tai kahta tietokokonaisuutta puuttuvien tai yhteensopivien kohteiden löytämiseksi. Koska kyseessä on Excel, on aina useampi kuin yksi tapa tehdä asioita, mukaan lukien tietojen vertailu. Kaavoista ja ehdollisesta muotoilusta Power Queryyn. Tässä artikkelissa tarkastelemme useita tapoja verrata kahta luetteloa Excelissä, ja tarkastelemme myös tietokokonaisuuden kokonaisten rivien vertailua.
Mahdollisesti haluamasi tapa ei ole mukana alla. Jos ei ole, miksi et jättäisi kommenttia alle ja kertoisi meille, miten haluat verrata kahta luetteloa tai tietokokonaisuutta Excelissä. Odotan mielenkiinnolla kommenttejasi.
Haluamme verrata listaa 1 ja listaa 2.
Sisältö
Nopea ehdollinen muotoilu kahden tietosarakkeen vertailemiseksi
Ehdollisen muotoilun tyhjentäminen
Tietojen vertailu Excelissä MATCH-toiminnolla
Tietojen vertailu Excel 365:ssä kahden luettelon välillä MATCH- tai XMATCH-toiminnolla, koska se on Dynamic Array -toiminto
MATCH ja dynaamiset matriisit 2 luettelon vertailuun
XMATCH Excel 365:ssä kahden luettelon vertailuun
Taulukot – Luetteloiden vertailu Excelissä, jossa alueiden koot saattavat muuttua
Korosta eroja luetteloissa käyttämällä mukautettua ehdollista muotoilua
Kopioi kaava mukautettuun ehdolliseen muotoiluun
Muut kaavat, joita käytetään kahden luettelon vertailuun Excelissä
VLOOKUP kahden luettelon vertailuun Excelissä
XLOOKUP kahden luettelon vertailuun Excelissä
COUNTIF kahden luettelon vertailuun Excelissä
Miten verrataan kahta tietosarjaa Excelissä
Luetteloiden tai tietokokonaisuuksien vertailu Power Queryn avulla
Nopea ehdollinen muotoilu kahden datasarakkeen vertailemiseksi
Ehdollisen muotoilun avulla voit korostaa soluja tai aluetta ennalta määritettyjen kriteerien perusteella. Nopein ja yksinkertaisin tapa vertailla näitä kahta saraketta nopeasti visuaalisesti on käyttää ennalta määritettyä Korosta päällekkäiset arvot -sääntöä.
Aloita valitsemalla kaksi tietosaraketta.
Valitse Aloitus-välilehdeltä Ehdollinen muotoilu -pudotusvalikko. Valitse sitten Korosta solut -säännöt. Valitse seuraavaksi Kaksoisarvot.
Avautuu Kaksoisarvot-asetusruutu, jossa voit määritellä muotoilun ja valita Kaksois- tai Yksilöityjen arvojen välillä.
Valitsemalla Kaksoismuotoilun kaikki toistuvat merkinnät määritetään valitun muotoilun mukaan. Nyt näet nopeasti luettelon 1 kohdat, jotka ovat luettelossa 2, koska nämä ovat muotoiltuja kohtia. Voit myös nopeasti nähdä luettelossa 2 olevat kohteet, jotka eivät ole luettelossa 1, koska näihin ei ole sovellettu muotoilua.
Mutta voit muotoilla myös Yksittäiset kohteet. Tämä onnistuu valitsemalla Duplicate Values -asetusruudusta Unique.
Tässä tapauksessa olemme soveltaneet kahta eri ehdollista muotoilua. Punainen osoittaa kaksoiskappaleet ja vihreä ainutlaatuiset kohteet.
Huomaa, että en ottanut vain soluja, joissa on tietoja, vaan nappasin kaikki sarakkeet A-C. Sarakkeessa B ei ole tietoja, joten se ei voi vaikuttaa tuloksiin. Näihin soluihin on kuitenkin sovellettu ehdollista muotoilua, joten olisi parempi käytäntö valita vain tarvitsemasi solut.
Ehdollisen muotoilun poistaminen
Poistaaksesi kaikki ehdolliset muotoilut, valitse ensin solu tai alue. Valitse sitten Koti-nauhan Ehdollinen muotoilu -pudotusvalikko. Valitse seuraavaksi Tyhjennä säännöt. Valitse lopuksi Tyhjennä säännöt valituista soluista.
Jos sinulla on käytössäsi useita ehdollisia muotoiluja kerralla ja haluat poistaa niistä vain yhden, valitse ehdollisen muotoilun pudotusvalikosta Hallitse sääntöjä. Valitse sääntö, jonka haluat poistaa, ja valitse sitten Poista sääntö.
Painamalla OK-painiketta sääntö poistetaan Sääntöjen hallinnasta, eikä soluissa ole enää muotoilua.
Se on siis yksinkertaisin tapa, jolla voit vertailla kahta luetteloa Excelissä. Se on nopea, yksinkertainen ja tehokas. Voit myös soveltaa kaavoihin perustuvaa ehdollista muotoilua, jota tarkastelemme myöhemmin tässä artikkelissa.
Tietojen vertailu Excelissä MATCH-funktiolla
On olemassa monia hakukaavoja, joita voit käyttää kahden alueen tai luettelon vertailuun Excelissä. Ensimmäinen, jota tarkastelemme, on MATCH-funktio.
MATCH-funktio palauttaa suhteellisen sijainnin luettelossa. Numeron, joka perustuu sen sijaintiin, jos se löytyy lookup-joukosta.
MATCH-funktion syntaksi on
=MATCH (lookup-arvo, Lookup-joukko, Match-tyyppi)
Jossa lookup-arvo on arvo, jolle haluat löytää vastaavuuden. Lookup array on lista, josta etsitään vastaavuutta. Ja Match-tyypin avulla voit valita tarkan tai likimääräisen vastaavuuden välillä.
Haluamme kirjoittaa vastaavuuskaavan, jolla selvitetään, ovatko luettelossa 2 olevat kohteet luettelossa 1.
Soluun E3 voimme kirjoittaa kaavan
=MATCH(C2, $A$2:$A$21,0)
Täyttämällä tämän kaavan alaspäin Excel palauttaa niiden arvojen kohdalla, joissa Excel löytää täsmäävän osuman, kyseisen osuman sijainnin. Jos vastaavuutta ei löydy, palautusarvona on #N/A.
Hyvin usein suhteellisella sijainnilla tai #N/A:lla ei ole meille mitään arvoa, ja meidän on muunnettava nämä arvot tosiksi tai vääriksi. Tätä varten voimme helposti laajentaa Match-kaavaamme käyttämällä loogista funktiota. Koska Match palauttaa numeron, voimme käyttää ISNUMBER-funktiota
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Vertaile 2 luetteloa Excel 365:ssä MATCH:lla tai XMATCH:lla dynaamisena array-funktiona
Jos käytät Excel 365:tä, sinulla on muitakin vaihtoehtoja, kun käytät MATCH:ia luetteloiden tai tietojen vertailemiseen. Koska Excel 365 ajattelee matriiseissa, voimme nyt välittää MATCHin hakuarvona matriisin, jolloin tuloksemme vuotavat puolestamme. Tämä poistaa tarpeen kopioida kaava alas, ja kun käytössä on vain yksi kaava, laskentataulukkosi on vähemmän altis virheille ja kompaktimpi.
MATCH ja dynaamiset matriisit kahden listan vertailemiseksi
Jos dynaamiset matriisit eivät vielä ole sinulle tuttuja, suosittelen tutustumaan tähän artikkeliin: Excel Dynamic Arrays – A new way to model your Excel Spreadsheets: to get a better understand on how they work and spill ranges.
Ainoa muutos ottelukaavaan on se, että sen sijaan, että valitsisimme solun C2 hakuarvoksi, valitsemme alueen C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Kuten huomaat, yksi kaava vuodattaa tulokset sarakkeeseen E.
XMATCH Excel 365 vertaa kahta luetteloa
Excel 365 esittelee myös uuden funktion XMATCH. Aivan kuten MATCH-funktio XMATCH palauttaa suhteellisen sijainnin luettelossa. Nyt kun olet tutustunut XLOOKUPiin, joka korvaa vanhan VLOOKUP-funktion, tiedät, että XLOOKUPissa on lisätehoa. Tämä tulee uusien ehtojen muodossa kaavan syntaksissa, kuten hakutilassa ja vastaavuustyypeissä. No, XMATCH tarjoaa myös nämä lisävoimat edeltäjäänsä MATCH:iin verrattuna.
XMATCH:n syntaksi on
XMATCH (Lookup Value, Lookup Array, ,)
Jossa
Lookup Value on arvo, jonka suhteellista sijaintia etsitään
Lookup Array on rivi tai sarake, joka sisältää Lookup Value:n
Vastaustila on vapaaehtoinen. Toisin kuin vanhassa MATCH-funktiossa, oletusarvo on tarkka vastaavuus. Voit myös valita
- Tarkka vastaavuus tai seuraavaksi pienin
- Tarkka vastaavuus tai seuraavaksi suurin
- Hakutila on myös valinnainen. Oletusarvo (ja ainoa vaihtoehto vanhassa MATCH-funktiossa) on etsiä ylhäältä alaspäin. Voit myös valita viimeisestä ensimmäiseen ja binäärihaun. Jos työskentelet binäärihakujen kanssa. Jokerimerkkihakuvaihtoehto ei toimi.
XMATCH:lla voimme käyttää kaavan luomiseen joko dynaamisia matriiseja tai soluviittauksia, aivan kuten olemme tarkastelleet MATCH:lla. Tässä esimerkissä käytämme dynaamisia sarjoja. Kaava on hyvin samankaltainen kuin MATCHin kanssa käyttämämme kaava, paitsi että meidän ei tarvitse valita 0:ta tarkkaa vastaavuutta varten, koska XMATCHissa tämä on oletusasetus. Sekoitetaan asioita hieman tällä kertaa tarkastelemalla listan 2 kohteiden etsimistä listalta 1.
Tässä tapauksessa voimme käyttää kaavaa
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Jossa not muuttaa totuuden vääräksi ja väärän totuudeksi.
Taulukot – Luetteloiden vertailu Excelissä, jossa alueiden koot saattavat muuttua
Kussakin tähän mennessä tarkastelemassamme kaavassa olemme valinneet Match-funktioissamme solualueen, joka ei ole dynaaminen. Tämä tarkoittaa, että jos lisäämme johonkin luetteloon uusia tietoja, meidän on päivitettävä kaava manuaalisesti niin, että se sisältää uudet tiedot.
Muuttaaksemme luettelot taulukoiksi, valitse yksi luetteloista ja paina CTRL. Tämä on pikanäppäin, jolla muunnetaan taulukoksi. Jos valitsit solualueen otsikon, varmista, että rastitat ruudun vahvistaaksesi, että taulukossasi on otsikot.
Taulukot käyttävät luonteensa vuoksi strukturoitua nimeämistä. Siksi kun kirjoitat kaavaa ja valitset taulukon sarakkeen, se ei näytä soluviittauksia vaan sarakkeen nimen.
Katsomme aiempaa kaavaamme, jossa käytimme XMATCH:ia etsiessämme listan 2 kohteita, jotka eivät ole listassa 1. Voimme nyt kirjoittaa tämän funktion uudelleen käyttäen taulukkoviittauksia
=NOT (ISNUMBER(XMATCH(list2,lista1)))
Nyt, koska olemme käyttäneet taulukoita, jos lisäämme jompaankumpaan taulukkoon uuden rivin, myös vuotoalueemme kasvaa niin, että se sisältää uudet tiedot.
Korostetaan luetteloiden eroja mukautetun ehdollisen muotoilun avulla
Aiemmin tässä artikkelissa tarkastelimme hyvin nopeaa tapaa verrata näitä kahta luetteloa käyttämällä ennalta määritettyä sääntöä kaksoiskappaleiden varalta. Voimme kuitenkin käyttää myös mukautettua ehdollista muotoilua. Jos mukautettu ehdollinen muotoilu ei ole sinulle tuttu, suosittelen tutustumaan tähän artikkeliin: Excel Dynamic Conditional Formatting Tricks:
Meidän on tarkasteltava tässä kahta erilaista lähestymistapaa erojen korostamiseksi. Kun emme käytä taulukoita ja olemme luoneet tosi/väärin-kaavan erojen tunnistamiseksi, voimme ottaa kopion kaavasta ja lisätä sen mukautettuun muotoiluun. Taulukoiden kanssa meidän on kuitenkin pakotettava soluviittausten käyttö.
Kopioi kaava mukautettuun ehdolliseen muotoiluun
Aloita ottamalla kopio kaavasta. Koska olemme testanneet kaavaa laskentataulukossa, voimme nähdä, että se toimii ennen kuin käytämme sitä ehdollisessa muotoilussa. Tämä on paras käytäntö, sillä hyvin usein suhteellisten ja absoluuttisten soluviittausten kanssa voi olla vaikeaa saada kaavaa oikein.
Valitse solut, joihin haluat soveltaa mukautettua muotoilua. Valitse sitten Aloitus-nauhasta Ehdollinen muotoilu -pudotusvalikko ja valitse Uusi sääntö
Uuden muotoilusäännön asetusruutu avautuu ja valitse Käytä kaavaa määrittääksesi, mitkä solut muotoillaan. Liitä sitten kaava ja määritä muotoilutyyppi
Tämän tuloksena kaikki solut, jotka ovat molemmissa luetteloissa, muotoillaan valitsemallasi muotoilulla.
Muista, että valitsimme soluvälialueet, joihin sovellamme tätä ehdollista muotoilua, eikä se ole dynaaminen. Jos käyttäisimme taulukoita, tämä päivittyisi ilman, että tarvitsisi muuttaa mitään!
Muita kaavoja, joita käytetään kahden luettelon vertailuun Excelissä
On monia kaavoja, joita voit käyttää kahden luettelon vertailuun Excelissä. Olemme jo tarkastelleet MATCH- ja XMATCH-kaavoja, mutta nyt tarkastelemme muutamia muita. Mikä tahansa lookup-funktio todella toimii joidenkin muiden ohella!
VLOOKUP kahden luettelon vertailuun Excelissä
Jos VLOOKUP ei ole sinulle tuttu, voit lukea siitä täältä. Yksinkertaisesti sanottuna VLOOKUP palauttaa vastaavan arvon solusta, jos vastaavaa arvoa ei ole, palautetaan #N/A virhe. Esimerkissämme työskentelemme tekstin kanssa. Voimme siis suorittaa VLOOKUPin ja testata, palauttaako se tekstiä. Jos käyttäisimme numeroita, voisimme korvata ISTEXT:n ISNUMBER:llä.
Voisimme käyttää funktiota =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Vai jos käyttäisimme Excel 365:n dynaamisia matriiseja, voisimme käyttää funktiota
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP vertaillaksemme kahta luetteloa Excelissä
XLOOKUP otettiin käyttöön Excel 365:ssä, ja voit lukea siitä lisää täältä. Hyvin samanlainen kuin VLOOKUP, XLOOKUP palauttaa vastaavan arvon solusta, ja voit määrittää tuloksen, jos arvoa ei löydy. Dynaamisia matriiseja käyttäen funktio olisi
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF, jolla voit vertailla kahta listaa Excelissä
COUNTIF-funktio laskee, kuinka monta kertaa arvo tai teksti sisältyy alueeseen. Jos arvoa ei löydy, palautetaan 0. Voimme yhdistää tämän IF-lauseeseen palauttaaksemme tosia ja vääriä arvoja.
=IF(COUNTIF (A2:A21,C2:C12)<>0, ”Totta”, ”Väärin”)
Kahden tietokokonaisuuden vertailu Excelissä
Kahden luettelon vertailu on helppoa, ja olemme nyt tarkastelleet useita tapoja, joilla se voidaan tehdä. Kahden tietokokonaisuuden vertailu voi kuitenkin olla hieman vaikeampaa.
Katsotaanpa yksi esimerkki. Meillä on kaksi datataulukkoa, joissa kummassakin on samat sarakeotsikot. Kun tarkastelemme kuvaa, näemme, että näiden kahden taulukon vertailu edellyttäisi useamman kuin yhden sarakkeen tarkastelua.
Kun on tarkasteltava useampaa kuin yhtä saraketta, ratkaisu olisi luoda yhdistetty sarake, joka yhdistää tiedot yhdeksi sarakkeeksi. Näin luodaan jokaiselle riville yksilöllinen sarake, jota voimme sitten käyttää vastaavana sarakkeena
Tässä esimerkissä voisimme yhdistää Name- ja DoB-sarakkeet antaaksemme jokaiselle taululle yksilöllisen tunnisteen
Solun sisällön yhdistämiseen on monia tapoja, tässä tapauksessa teemme yksinkertaisen ketjutuksen. Koska käytämme taulukoita, kaavassa käytetään taulukon nimeämismuotoa.
= &”-”&
Toista vaiheet toiselle taulukolle.
Nyt voimme käyttää mitä tahansa yllä olevista esimerkeistä sovittamaan yhteen nämä kaksi uutta tietosaraketta. Jos ne täsmäävät, tiedämme, että ne täsmäävät koko rivin.
Luetteloiden tai tietokokonaisuuksien vertailu Power Queryn avulla
Luetteloita ja tietokokonaisuuksia voi vertailla myös Excels Power Queryn avulla. Yhdistämällä taulukot ja yhdistämällä taulukot, käyttämällä erilaisia join-tyyppejä voimme vertailla molempia luetteloita.
Tässä videossa opit vertailemaan tai täsmäyttämään kahta erilaista tietoaineistoa Excels Power Queryn avulla
Tämän videon ja harjoittelun ohessa on mukana tietoaineisto, jonka voit napata tästä artikkelista.