Kahden solun vertailu Excelissä (merkkijonovertailu)

Ohjeessa näytetään, miten Excelissä verrataan tekstin merkkijonoja niin, että ne eivät huomioi isoja ja pieniä kirjaimia ja että ne vastaavat täsmällisesti toisiaan. Opit useita kaavoja, joilla voit vertailla kahta solua niiden arvojen, merkkijonon pituuden tai tietyn merkin esiintymien lukumäärän perusteella sekä vertailla useita soluja.

Kun käytät Exceliä data-analyysiin, tarkkuus on elintärkein asia. Väärät tiedot johtavat myöhästyneisiin määräaikoihin, väärin arvioituihin trendeihin, vääriin päätöksiin ja menetettyihin tuloihin.

Vaikka Excelin kaavat ovat aina täysin oikeita, niiden tulokset voivat olla vääriä, koska järjestelmään on tunkeutunut virheellisiä tietoja. Tällöin ainoa keino on tarkistaa tietojen oikeellisuus. Kahden solun vertailu manuaalisesti ei ole iso juttu, mutta satojen ja tuhansien tekstijonojen välisten erojen havaitseminen on lähes mahdotonta.

Tässä opetusohjelmassa opit, miten voit automatisoida solujen vertailun työlästä ja virhealtista tehtävää ja mitä kaavoja kannattaa käyttää kussakin erityistapauksessa.

• Miten verrataan 2 solua Excelissä
• Miten verrataan useita soluja
• Vertaillaan solualuetta näytesoluun
• Vertaillaan soluja merkkijonon pituuden mukaan
• Vertaillaan merkkijonoja tietyn merkin esiintymisen mukaan

Miten verrataan kahta solua Excelissä

Excelissä on kaksi erilaista tapaa vertailla merkkijonoja riippuen siitä, etsitäänkö case-tapauskohtaista vai tapauskohtaista vertailua.

Kokoaakkoset huomioimaton kaava kahden solun vertailuun

Vertaillaksesi kahta solua Excelissä huomioimatta isoja ja pieniä kirjaimia, käytä tällaista yksinkertaista kaavaa:

=A1=B1

Jossa A1 ja B1 ovat solut, joita vertaat. Kaavan tuloksena saadaan Boolen arvot TRUE ja FALSE.

Jos haluat tulostaa omia tekstejä vastaavuuksista ja eroista, upota yllä oleva lauseke IF-funktion loogiseen testiin. Esimerkiksi:

=IF(A1=B1, "Equal", "Not equal")

Kuten näet alla olevasta kuvakaappauksesta, molemmat kaavat vertailevat yhtä hyvin tekstijonoja, päivämääriä ja numeroita:

Case-sensitive-kaava merkkijonojen vertailemiseksi Excelissä

Jossain tilanteissa voi olla tärkeää vertailla kahdessa solussa olevan tekstin lisäksi merkkien oikeinkirjoitusasuja. Isokirjainherkkä tekstivertailu voidaan tehdä Excelin EXACT-funktiolla:

Jossa teksti1 ja teksti2 ovat kaksi solua, joita vertaillaan.

Jos merkkijonot ovat soluissa A2 ja B2, kaava menee seuraavasti:

=EXACT(A2, B2)

Tulokseksi saat TRUE, jos tekstijonot täsmäävät täsmälleen, mukaan lukien jokaisen merkin isot ja pienet kirjaimet, ja muuten FALSE.

Jos haluat EXACT-funktion tuottavan joitain muita tuloksia, upota se IF-kaavaan ja kirjoita value_if_true- ja value_if_false-argumenteille oma tekstisi:

=IF(EXACT(A2 ,B2), "Exactly equal", "Not equal")

Oheinen kuvakaappaus näyttää isot ja pienet kirjainmerkit huomioivan merkkijonovertailun tulokset Excelissä:

Monien solujen vertailu Excelissä

Vertaillaksesi useampaa kuin kahta solua rivillä, käytä yllä olevissa esimerkeissä käsiteltyjä kaavoja yhdessä AND-operaattorin kanssa. Täydelliset yksityiskohdat seuraavat alla.

Kokoonpanoton kaava useamman kuin 2 solun vertailemiseksi

Riippuen siitä, miten haluat näyttää tulokset, käytä jotakin seuraavista kaavoista:

=AND(A2=B2, A2=C2)

tai

=IF(AND(A2=B2, A2=C2), "Equal", "Not equal")

AND-kaava palauttaa arvon TRUE, jos kaikki solut sisältävät saman arvon, ja arvon FALSE, jos mikä tahansa arvo eroaa. IF-kaava tulostaa siihen kirjoittamasi merkinnät, tässä esimerkissä ”Equal” ja ”Not equal”.

Kuten alla olevassa kuvakaappauksessa osoitetaan, kaava toimii täydellisesti minkä tahansa tietotyypin – tekstin, päivämäärien ja numeeristen arvojen – kanssa:

Kokoonpanon huomioiva kaava, jolla voit vertailla useissa soluissa olevaa tekstiä

Vertaillaksesi useita merkkijonoja toisiinsa ja nähdäksesi, vastaavatko ne täsmälleen toisiaan, käytä seuraavia kaavoja:

=AND(EXACT(A2,B2), EXACT(A2, C2))

Or

=IF(AND(EXACT(A2,B2), EXACT(A2, C2)),"Exactly equal", "Not equal")

Kuten edellisessä esimerkissä, ensimmäinen kaava tuottaa TOSI- ja VÄÄRÄ-arvot, kun taas toinen näyttää omat tekstisi vastaavuuksia ja eroja varten:

Solualueen vertailu näytesoluun

Seuraavissa esimerkeissä näytetään, miten voit tarkistaa, että kaikki tietyn alueen solut sisältävät samaa tekstiä kuin näytesolussa.

Kantakirjaimet huomioimaton kaava solujen vertaamiseksi näytetekstiin

Jos merkkien kantakirjaimilla ei ole oikeastaan väliä, voit verrata soluja näytetekstiin seuraavalla kaavalla:

Jos-funktion loogisessa testauksessa VERTAAT kahta lukua:

• Määritellyn alueen solujen kokonaislukumäärää (rivien lukumäärä kerrottuna sarakkeiden lukumäärällä) ja
• Solujen lukumäärää, jotka sisältävät saman arvon kuin näytesolu (COUNTIF-funktion palauttama).

Edellyttäen, että esimerkkiteksti on sarakkeessa C2 ja vertailtavat merkkijonot ovat alueella A2:B6, kaava menee seuraavasti:

=ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2)

Käyttäjäystävällisempien tulosten saamiseksi esim. tulostamaan jotain tyyliin ”Kaikki täsmäävät” ja ”Kaikki eivät täsmää” TRUE:n ja FALSE:n sijaan, käytä IF-funktiota, kuten aiemmissa esimerkeissä:

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2),"All match", "Not all match")

Kuten yllä olevasta kuvakaappauksesta näkyy, kaava selviää täydellisesti tekstijonojen alueesta, mutta sitä voidaan käyttää myös numeroiden ja päivämäärien vertailuun.

Kantavuusherkkä kaava merkkijonojen vertailemiseksi esimerkkitekstiin

Jos merkkien isolla ja pienellä kirjaintyypillä on väliä, voit verrata merkkijonoja esimerkkitekstiin käyttämällä seuraavia sarjakaavoja.

Lähdealueen sijaitessa A2:ssa:B6 ja näyteteksti C2:ssa, kaava saa seuraavan muodon:

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=SUM(--EXACT(C2, A2:B6)), "All match", "Not all match")

Toisin kuin tavalliset Excel-kaavat, array-kaavat täytetään painamalla Ctrl + Shift + Enter. Jos kaava on kirjoitettu oikein, Excel sulkee array-kaavan {juokseviin hakasulkeisiin}, kuten kuvakaappauksessa näkyy:

Miten verrataan kahta solua merkkijonon pituuden mukaan

Joskus voi olla, että halutaan tarkastaa, sisältävätkö kummallakin rivillä olevat tekstijonot yhtä monta merkkiä. Kaava tähän tehtävään on hyvin yksinkertainen. Ensin saat kahden solun merkkijonon pituuden käyttämällä LEN-funktiota ja sen jälkeen vertaat lukuja.

Jos vertailtavat merkkijonot ovat soluissa A2 ja B2, käytä jompaakumpaa seuraavista kaavoista:

=LEN(A2)=LEN(B2)

Or

=IF(LEN(A2)=LEN(B2), "Equal", "Not equal")

Vaikka

=IF(LEN(A2)=LEN(B2), "Equal", "Not equal")

Kuten jo tiedät, ensimmäinen kaava palauttaa Boolen arvot TRUE tai FALSE, kun taas toinen kaava antaa omat tuloksesi:

Kuten yllä olevasta kuvakaappauksesta käy ilmi, kaavat toimivat sekä tekstijonoille että numeroille.

Vertaile kahta solua tietyn merkin esiintymisen perusteella

Tämä on viimeinen esimerkki Excel-oppaassamme Vertaile merkkijonoja, ja se näyttää ratkaisun melko erityiseen tehtävään. Oletetaan, että sinulla on 2 saraketta tekstijonoja, jotka sisältävät sinulle tärkeän merkin. Tavoitteenasi on tarkistaa, sisältävätkö kaksi solua kummallakin rivillä saman määrän tietyn merkin esiintymiä.

Ymmärtääksesi asiaa tarkastellaan seuraavaa esimerkkiä. Oletetaan, että sinulla on kaksi luetteloa toimitetuista (sarake B) ja vastaanotetuista (sarake C) tilauksista. Kummallakin rivillä on tilaukset tietylle nimikkeelle, jonka yksilöllinen tunniste sisältyy kaikkiin tilaustunnisteisiin ja on lueteltu samalla rivillä sarakkeessa A (katso alla oleva kuvakaappaus). Haluat varmistaa, että kullakin rivillä on yhtä monta toimitettua ja vastaanotettua nimikettä, joilla on kyseinen tunnus.

Kirjoita tämän ongelman ratkaisemiseksi kaava seuraavalla logiikalla.

• Korvaa ensin yksilöllinen tunniste tyhjällä käyttämällä SUBSTITUTE-funktiota:

  SUBSTITUTE(A1, character_to_count,"")

• Laskekaa sitten, kuinka monta kertaa yksilöllinen tunniste esiintyy kussakin solussa. Selvitä tätä varten merkkijonon pituus ilman yksilöllistä tunnusta ja vähennä se merkkijonon kokonaispituudesta. Tämä osa kirjoitetaan solulle 1 ja solulle 2 erikseen, esimerkiksi:

  LEN(cell 1) - LEN(SUBSTITUTE(cell 1, character_to_count, ""))
  ja
  LEN(cell 2) - LEN(SUBSTITUTE(cell 2, character_to_count, ""))

• Viimeiseksi verrataan näitä kahta lukua asettamalla edellä mainittujen osien väliin yhtäläisyysmerkki (=).

Esimerkissämme yksilöllinen tunniste on solussa A2, ja verrattavat merkkijonot ovat soluissa B2 ja C2. Täydellinen kaava on siis seuraava:

=LEN(B2)-LEN(SUBSTITUTE(B2,$A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2,$A2,""))

Kaava palauttaa arvon TRUE, jos soluissa B2 ja C2 on yhtä monta merkin esiintymää solussa A2, muutoin FALSE. Jotta tulokset olisivat käyttäjille mielekkäämpiä, voit upottaa kaavan IF-funktioon:

=IF(LEN(B2)-LEN(SUBSTITUTE(B2, $A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2, $A2,"")), "Equal", "Not equal")

Kuten yllä olevasta kuvakaappauksesta näkyy, kaava toimii täydellisesti muutamasta lisäkomplikaatiosta huolimatta:

• Laskettava merkki (yksilöllinen tunniste) voi esiintyä missä tahansa tekstijonossa.
• merkkijonot sisältävät vaihtelevan määrän merkkejä ja erilaisia erottimia, kuten puolipiste, pilkku tai välilyönti.

Näin vertaat merkkijonoja Excelissä. Jos haluat tutustua tarkemmin tässä opetusohjelmassa käsiteltyihin kaavoihin, voit ladata Excelin Vertaile merkkijonoja -työselosteen. Kiitän sinua lukemisesta ja toivon näkeväni sinut blogissamme ensi viikolla.

Olet ehkä myös kiinnostunut

• Vertaile kahta saraketta Excelissä yhteneväisyyksien ja eroavaisuuksien löytämiseksi
• Miten vertailla kahta Excelin laskentataulukkoa eroavaisuuksien löytämiseksi
• Miten verrata numeroita loogisella operaattorilla
• 4 tapaa tehdä suur- ja pienaakkoset huomioiva vlookup-laskentatulosteen vertailu Excelissä

.