Kuinka vertailla kahta luetteloa Excelissä

Excel on tehokas tietojenkäsittelyohjelma. Ja yksi tiedon analysointimenetelmistä on kahden listan vertailu. Jos vertaat oikein kahta luetteloa Excelissä, tämän prosessin järjestäminen on erittäin helppoa. Riittää, kun noudatat joitain kohtia, joista keskustellaan tänään. Tämän menetelmän käytännön toteutus riippuu täysin henkilön tai organisaation tietyn hetken tarpeista. Siksi useita mahdollisia tapauksia on harkittava.

Kahden listan vertailu Excelissä

Voit tietysti verrata kahta luetteloa manuaalisesti. Mutta se kestää kauan. Excelillä on oma älykäs työkalupakki, jonka avulla voit verrata tietoja paitsi nopeasti, myös saada tietoa, jota ei ole niin helppo saada silmilläsi. Oletetaan, että meillä on kaksi saraketta, joissa on koordinaatit A ja B. Jotkin arvot toistuvat niissä.

Ongelman muotoilu

Joten meidän on verrattava näitä sarakkeita. Kahden asiakirjan vertailu tapahtuu seuraavasti:

1. Jos kunkin luettelon yksilölliset solut ovat samat ja yksilöllisten solujen kokonaismäärä on sama ja solut ovat samat, näitä listoja voidaan pitää samoina. Järjestyksellä, jossa tämän luettelon arvot pinotaan, ei ole niin suurta merkitystä.
2. Listojen osittaisesta yhteensattumasta voidaan puhua, jos yksilölliset arvot ovat samat, mutta toistojen määrä on erilainen. Siksi tällaiset luettelot voivat sisältää eri määrän elementtejä.
3. Se tosiasia, että nämä kaksi luetteloa eivät täsmää, ilmaistaan ​​​​erilaisilla yksilöllisillä arvoilla.

Kaikki nämä kolme ehtoa ovat samanaikaisesti ongelmamme ehdot.

Ongelman ratkaisu

Luodaan kaksi dynaamista aluetta luetteloiden vertailun helpottamiseksi. Jokainen niistä vastaa kutakin luetteloa.

Vertaaksesi kahta luetteloa, toimi seuraavasti:

1. Erillisessä sarakkeessa luomme luettelon yksilöllisistä arvoista, jotka ovat ominaisia ​​molemmille luetteloille. Tätä varten käytämme kaavaa: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Itse kaava on kirjoitettava taulukkokaavana.
2. Määritetään, kuinka monta kertaa kukin yksilöllinen arvo esiintyy tietotaulukossa. Tässä on kaavat tämän tekemiseen: =LASKEJOS(Lista1,D5) ja =LASKE(Lista2,D5).
3. Jos sekä toistojen määrä että yksilöllisten arvojen määrä ovat samat kaikissa näillä alueilla olevissa luetteloissa, funktio palauttaa arvon 0. Tämä osoittaa, että vastaavuus on XNUMX%. Tässä tapauksessa näiden luetteloiden otsikot saavat vihreän taustan.
4. Jos kaikki yksilöllinen sisältö on molemmissa luetteloissa, palautetaan kaavoilla =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) arvo on nolla. Jos E1 ei sisällä nollaa, mutta sellainen arvo on soluissa E2 ja F2, niin tässä tapauksessa alueet tunnistetaan vastaaviksi, mutta vain osittain. Tässä tapauksessa vastaavien luetteloiden otsikot muuttuvat oranssiksi.
5. Ja jos jokin yllä kuvatuista kaavoista palauttaa nollasta poikkeavan arvon, luettelot eivät täsmää täysin.

Tämä on vastaus kysymykseen, kuinka analysoida sarakkeita vastaavuuksien varalta kaavojen avulla. Kuten näet, funktioiden avulla voit toteuttaa melkein minkä tahansa tehtävän, joka ei ensi silmäyksellä liity matematiikkaan.

Esimerkkitestaus

Taulukon versiossamme on kolmen tyyppisiä luetteloita kustakin edellä kuvatusta tyypistä. Siinä on osittain ja kokonaan yhteensopivuus sekä ei-vastaavuus.

Tietojen vertailuun käytämme aluetta A5:B19, johon lisäämme vuorotellen nämä luetteloparit. Siitä, mikä on vertailun tulos, ymmärrämme alkuperäisten luetteloiden värin perusteella. Jos ne ovat täysin erilaisia, se on punainen tausta. Jos osa tiedoista on sama, niin keltainen. Jos identiteetti on täydellinen, vastaavat otsikot ovat vihreitä. Kuinka tehdä väri riippuen siitä, mikä on lopputulos? Tämä vaatii ehdollisen muotoilun.

Erojen löytäminen kahdesta luettelosta kahdella tavalla

Kuvataan kaksi muuta menetelmää erojen löytämiseksi sen mukaan, ovatko listat synkronisia vai eivät.

Vaihtoehto 1. Synkroniset listat

Tämä on helppo vaihtoehto. Oletetaan, että meillä on sellaisia ​​luetteloita.

Voit määrittää, kuinka monta kertaa arvot eivät lähentyneet, voit käyttää kaavaa: =SUMMATUOTE(—(A2:A20<>B2:B20)). Jos saimme tulokseksi 0, tämä tarkoittaa, että kaksi listaa ovat samat.

Vaihtoehto 2: Sekoitettu luettelo

Jos luettelot eivät ole identtisiä niiden sisältämien objektien järjestyksessä, sinun on käytettävä ominaisuutta, kuten ehdollista muotoilua, ja väritettävä päällekkäiset arvot. Tai käytä toimintoa LASKE.JOS, jonka avulla määritämme, kuinka monta kertaa yhdestä luettelosta oleva elementti esiintyy toisessa.

Kuinka vertailla kahta saraketta rivi riviltä

Kun vertaamme kahta saraketta, meidän on usein verrattava eri riveillä olevia tietoja. Tässä operaattori auttaa meitä JOS. Katsotaanpa kuinka se toimii käytännössä. Tätä varten esittelemme useita havainnollistavia tilanteita.

Esimerkki. Kuinka vertailla kahta saraketta vastaavuuksille ja eroille yhdellä rivillä

Analysoidaksemme, ovatko samalla rivillä mutta eri sarakkeissa olevat arvot samoja, kirjoitamme funktion IF. Kaava lisätään jokaiselle riville, joka on sijoitettu apusarakkeeseen, jossa tietojen käsittelyn tulokset näytetään. Mutta sitä ei tarvitse määrätä jokaiselle riville, vaan kopioi se tämän sarakkeen muihin soluihin tai käytä automaattisen täydennyksen merkkiä.

Meidän pitäisi kirjoittaa tällainen kaava ymmärtääksemme, ovatko arvot molemmissa sarakkeissa samat vai eivät: =JOS(A2=B2, "Match", ""). Tämän toiminnon logiikka on hyvin yksinkertainen: se vertaa arvoja soluissa A2 ja B2, ja jos ne ovat samat, se näyttää arvon "Samaan". Jos tiedot ovat erilaisia, se ei palauta arvoa. Voit myös tarkistaa solut, onko niiden välillä täsmää. Tässä tapauksessa käytetty kaava on: =JOS(A2<>B2, “Ei täsmää”, “”). Periaate on sama, ensin suoritetaan tarkistus. Jos käy ilmi, että solut täyttävät kriteerin, arvo "Ei täsmää" näytetään.

On myös mahdollista käyttää seuraavaa kaavaa kaavakentässä näyttämään sekä "Match", jos arvot ovat samat, ja "Ei täsmää", jos ne ovat erilaisia: =JOS(A2=B2; "Sopii", "Ei täsmää"). Voit myös käyttää epätasa-arvooperaattoria tasa-arvooperaattorin sijaan. Vain tässä tapauksessa näytettävien arvojen järjestys on hieman erilainen: =JOS(A2<>B2, "Ei täsmää", "Yhteys"). Kaavan ensimmäisen version käytön jälkeen tulos on seuraava.

Tässä kaavan muunnelmassa kirjainkoolla ei ole merkitystä. Siksi, jos yhden sarakkeen arvot eroavat muista vain siinä, että ne on kirjoitettu isoilla kirjaimilla, ohjelma ei huomaa tätä eroa. Jos haluat tehdä vertailusta kirjainkoon erottelun, sinun on käytettävä kriteerien funktiota TARKKA. Muut argumentit jätetään ennalleen: =JOS(TARKKA(A2,B2), "Osuma", "Ainutlaatuinen").

Kuinka vertailla useita sarakkeita vastaavuuksille yhdellä rivillä

On mahdollista analysoida luetteloiden arvoja useiden kriteerien mukaan:

1. Etsi ne rivit, joilla on samat arvot kaikkialla.
2. Etsi rivit, joilla on osumia vain kahdesta luettelosta.

Katsotaanpa muutamia esimerkkejä siitä, kuinka edetä kussakin näistä tapauksista.

Esimerkki. Kuinka etsiä osumia yhdeltä riviltä useissa taulukon sarakkeissa

Oletetaan, että meillä on sarja sarakkeita, jotka sisältävät tarvitsemamme tiedot. Edessämme on tehtävä määrittää ne rivit, joissa arvot ovat samat. Tätä varten sinun on käytettävä seuraavaa kaavaa: =JOS(JA(A2=B2,A2=C2), "vastaa", " ").

Jos taulukossa on liian monta saraketta, sinun tarvitsee vain käyttää sitä yhdessä funktion kanssa IF operaattori LASKE.JOS: =JOS(LASKEJOS($A2:$C2,$A2)=3;”vastaavuus”;” “). Tässä kaavassa käytetty numero ilmaisee tarkistettavien sarakkeiden määrän. Jos se eroaa, sinun on kirjoitettava niin paljon kuin se on totta sinun tilanteessasi.

Esimerkki. Kuinka etsiä osumia yhdeltä riviltä missä tahansa taulukon kahdessa sarakkeessa

Oletetaan, että meidän on tarkistettava, vastaavatko yhden rivin arvot kahdessa sarakkeessa taulukon arvoista. Tätä varten sinun on käytettävä funktiota ehtona OR, jossa kirjoitetaan vuorotellen kunkin sarakkeen yhtäläisyys toisiinsa. Tässä on esimerkki.

Käytämme tätä kaavaa: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Saattaa olla tilanne, jossa taulukossa on paljon sarakkeita. Tässä tapauksessa kaava on valtava, ja kaikkien tarvittavien yhdistelmien valitseminen voi viedä paljon aikaa. Tämän ongelman ratkaisemiseksi sinun on käytettävä toimintoa LASKE.JOS: =JOS(LASKEJOS(B2:D2,A2)+LASKEJOS(C2:D2,B2)+(C2=D2)=0; "Ainutlaatuinen merkkijono"; "Ei yksilöllinen merkkijono")

Näemme, että meillä on yhteensä kaksi toimintoa LASKE.JOS. Ensimmäisellä määritetään vuorotellen, kuinka monella sarakkeella on samankaltaisuus A2:n kanssa, ja toisella tarkistamme samankaltaisuuksien määrän B2:n arvolla. Jos tämän kaavan avulla laskettaessa saamme nolla-arvon, tämä osoittaa, että kaikki tämän sarakkeen rivit ovat ainutlaatuisia, jos enemmän, yhtäläisyyksiä on. Siksi, jos kahdella kaavalla laskemalla ja lopullisten tulosten lisäämisellä saamme nolla-arvon, palautetaan tekstiarvo "Ainutlaatuinen merkkijono", jos tämä luku on suurempi, kirjoitetaan, että tämä merkkijono ei ole ainutlaatuinen.

Kuinka vertailla kahta saraketta Excelissä vastaavuuksien varalta

Otetaan nyt esimerkki. Oletetaan, että meillä on taulukko, jossa on kaksi saraketta. Sinun on tarkistettava, vastaavatko ne. Tätä varten sinun on käytettävä kaavaa, jossa myös funktiota käytetään IFja operaattori LASKE.JOS: =JOS(COUNTIF($B:$B,$A5)=0, "Ei osumia sarakkeessa B", "Sarakkeessa B on osumia"

Lisätoimia ei tarvita. Kun tulos on laskettu tällä kaavalla, saadaan jos funktion kolmannen argumentin arvo IF Ottelut. Jos niitä ei ole, toisen argumentin sisältö.

Kuinka vertailla kahta saraketta Excelissä vastaavuuksien ja värien korostamiseksi

Vastaavien sarakkeiden visuaalisen tunnistamisen helpottamiseksi voit korostaa niitä värillä. Tätä varten sinun on käytettävä "Ehdollinen muotoilu" -toimintoa. Katsotaan käytännössä.

Osumien etsiminen ja korostaminen värin perusteella useissa sarakkeissa

Määrittääksesi osumat ja korostaaksesi ne, sinun on ensin valittava tietoalue, jolla tarkistus suoritetaan, ja avattava sitten "Ehdollinen muotoilu" -kohta "Koti"-välilehdellä. Valitse siellä "Duplicate Values" solunvalintasäännöksi.

Sen jälkeen ilmestyy uusi valintaikkuna, jossa vasemmasta ponnahdusluettelosta löydämme vaihtoehdon “Toistuva” ja oikeasta listasta valitsemme valinnassa käytettävän värin. Kun napsautamme "OK" -painiketta, kaikkien samankaltaisten solujen tausta valitaan. Vertaile sitten sarakkeita silmällä.

Yhteensopivien viivojen etsiminen ja korostaminen

Tekniikka merkkijonojen yhteensopivuuden tarkistamiseksi on hieman erilainen. Ensin meidän on luotava ylimääräinen sarake, ja siellä käytämme yhdistettyjä arvoja käyttämällä &-operaattoria. Tätä varten sinun on kirjoitettava lomakkeen kaava: =A2&B2&C2&D2.

Valitsemme sarakkeen, joka on luotu ja sisältää yhdistetyt arvot. Seuraavaksi suoritamme saman toimintosarjan, joka on kuvattu edellä sarakkeille. Päällekkäiset rivit korostetaan määrittämälläsi värillä.

Näemme, ettei toistojen etsimisessä ole mitään vaikeaa. Excel sisältää kaikki tarvittavat työkalut tähän. On tärkeää vain harjoitella ennen kaiken tämän tiedon soveltamista käytäntöön.