Voit etsiä ja korostaa päällekkäiset merkinnät kahden sarakkeen välillä käyttämällä Google Sheetsin ehdollista muotoilua.
Kun työskentelet Google Sheetsissä suurten tietojoukkojen kanssa, kohtaat todennäköisesti ongelman, jossa joudut käsittelemään monia päällekkäisiä arvoja. Jotkut kaksoismerkinnät on sijoitettu tarkoituksella, kun taas toiset ovat virheitä. Tämä pätee erityisesti silloin, kun työskentelet samalla arkilla tiimin kanssa.
Kun on kyse Google Sheetsin tietojen analysoinnista, kaksoiskappaleiden suodattaminen voi olla välttämätöntä ja kätevää. Vaikka Google Sheetsillä ei ole natiivitukea kaksoiskappaleiden etsimiseen taulukoista, se tarjoaa useita tapoja verrata, tunnistaa ja poistaa päällekkäisiä tietoja soluista.
Joskus haluat verrata sarakkeen kutakin arvoa toiseen sarakkeeseen ja selvittää, onko siinä kaksoiskappaleita ja päinvastoin. Google Sheetsissa voit helposti löytää kaksoiskappaleita kahden sarakkeen välillä ehdollisen muotoilun avulla. Tässä artikkelissa näytämme, kuinka voit verrata kahta saraketta Google Sheetsissa ja etsiä niiden välistä kaksoiskappaletta.
Etsi päällekkäisiä merkintöjä kahden sarakkeen välillä käyttämällä ehdollista muotoilua
Ehdollinen muotoilu on Google Sheetsin ominaisuus, jonka avulla käyttäjä voi käyttää tiettyjä muotoiluja, kuten fontin väriä, kuvakkeita ja tietopalkkeja soluun tai solualueeseen tiettyjen ehtojen mukaan.
Voit käyttää tätä ehdollista muotoilua korostamaan kahden sarakkeen välisiä kaksoiskappaleita joko täyttämällä solut väreillä tai muuttamalla tekstin väriä. Sinun on verrattava sarakkeen kutakin arvoa toiseen sarakkeeseen ja selvitettävä, toistetaanko jokin arvo. Jotta tämä toimisi, sinun on sovellettava ehdollista muotoilua jokaiseen sarakkeeseen erikseen. Tee se seuraavasti:
Avaa laskentataulukko, jonka kaksoiskappaleet haluat tarkistaa Google Sheetsistä. Valitse ensin ensimmäinen sarake (A), joka tarkistetaan sarakkeella B. Voit korostaa koko sarakkeen napsauttamalla sen yläpuolella olevaa sarakkeen kirjainta.
Napsauta sitten valikkopalkista Muotoile-valikkoa ja valitse Ehdollinen muotoilu.
Ehdollinen muotoilu -valikko avautuu google-taulukoiden oikealle puolelle. Voit vahvistaa, että solualue on se, jonka valitsit "Käytä alueelle" -vaihtoehdossa. Jos haluat muuttaa aluetta, napsauta "aluekuvaketta" ja valitse toinen alue.
Napsauta sitten Muotoilusäännöt-kohdan avattavaa valikkoa ja valitse Mukautettu kaava -vaihtoehto.
Nyt sinun on syötettävä mukautettu kaava Arvo tai kaava -ruutuun.
Jos valitsit koko sarakkeen (B:B), kirjoita seuraava COUNTIF-kaava Muotosäännöt-kohdan Arvo tai kaava -ruutuun:
=countif($B:$B,$A2)>0Tai,
Jos valitsit sarakkeesta solualueen (esimerkiksi sata solua, A2:A30), käytä tätä kaavaa:
=LASKEJOS($B$2:$B$30, $A2)>0Kun syötät kaavaa, muista korvata kaikki B-kirjaimen esiintymät kaavassa korostamasi sarakkeen kirjaimella. Lisäämme $-merkin soluviittausten eteen tehdäksemme niistä absoluuttisen alueen, joten se ei muuta kaavan soveltamista.
Muotoilutyyli-osiossa voit valita muotoilutyylin päällekkäisten kohteiden korostamiseksi. Oletuksena se käyttää vihreää täyttöväriä.
Voit valita jonkin esiasetetuista muotoilutyyleistä napsauttamalla "Oletus"-kohtaa "Muotoilutyyli"-vaihtoehtojen alla ja valitsemalla sitten jonkin esiasetuksista.
Tai voit käyttää mitä tahansa Muotoilutyyli-osion seitsemästä muotoilutyökalusta (lihavoitu, kursivoitu, alleviivaus, yliviivaus, tekstin väri, täyttöväri) korostaaksesi kaksoiskappaleita.
Täällä valitsemme täyttövärin päällekkäisille soluille napsauttamalla "Täyttöväri" -kuvaketta ja valitsemalla "keltaisen" värin.
Kun olet valinnut muotoilun, napsauta "Valmis" korostaaksesi solut.
COUNTIF-funktio laskee, kuinka monta kertaa kukin solun arvo sarakkeessa A näkyy sarakkeessa B. Joten jos kohde esiintyy edes kerran sarakkeessa B, kaava palauttaa TRUE. Sitten tämä kohde korostetaan sarakkeessa A valitsemasi muotoilun perusteella.
Tämä ei korosta kaksoiskappaleita, vaan se korostaa kohteita, joilla on kaksoiskappaleita sarakkeessa B. Tämä tarkoittaa, että jokaisella keltaisella korostetulla kohteella on kaksoiskappaleet sarakkeessa B.
Nyt meidän on sovellettava ehdollista muotoilua sarakkeeseen B käyttämällä samaa kaavaa. Voit tehdä tämän valitsemalla toisen sarakkeen (B2:B30), siirtymällä "Muotoile"-valikkoon ja valitsemalla "Ehdollinen muotoilu".
Vaihtoehtoisesti napsauta Lisää toinen sääntö -painiketta Ehdollisen muotoilun säännöt -ruudun alla.
Vahvista seuraavaksi alue (B2:B30) Apply to range -ruudussa.
Aseta sitten "Muotoile solut jos..." -asetukseksi "Muokattu kaava on" ja kirjoita alla oleva kaava kaavaruutuun:
=LASKEJOS($A$2:$A$30, $B2)>0Tässä käytämme sarakkeen A aluetta ($A$2:$A$30) ensimmäisessä argumentissa ja '$B2' toisessa argumentissa. Tämä kaava tarkistaa sarakkeen B solun arvon sarakkeen A jokaiseen soluun verrattuna. Jos vastaavuus (kopio) löytyy, ehdollinen muotoilu nostaa kyseisen kohteen sarakkeessa B.
Määritä sitten muotoilu Muotoilutyyli-asetuksissa ja napsauta Valmis. Tässä valitsemme sarakkeen B oranssin värin.
Tämä korostaa sarakkeen B kohteet, joilla on kaksoiskappaleita sarakkeessa A. Nyt olet löytänyt ja korostanut päällekkäisiä kohteita kahden sarakkeen väliltä.
Olet todennäköisesti huomannut, vaikka sarakkeessa A on kopio "Arcelialle", sitä ei ole korostettu. Tämä johtuu siitä, että kaksoiskappalearvo on vain yhdessä sarakkeessa (A), ei sarakkeiden välissä. Siksi sitä ei korosteta.
Korosta kaksoiskappaleet kahden sarakkeen välillä samalla rivillä
Voit myös korostaa rivit, joilla on samat arvot (kopiot) kahden sarakkeen välillä käyttämällä ehdollista muotoilua. Ehdollinen muotoilusääntö voi tarkistaa jokaisen rivin ja korostaa rivit, joilla on vastaavat tiedot molemmissa sarakkeissa. Näin teet tämän:
Valitse ensin molemmat sarakkeet, joita haluat verrata, siirry sitten Muotoile-valikkoon ja valitse Ehdollinen muotoilu.
Vahvista Ehdollisen muotoilun säännöt -ruudussa alue Käytä alueeseen -ruudussa ja valitse "Muokattu kaava on" avattavasta Kaavasolut jos... -valikosta.
Kirjoita sitten alla oleva kaava Arvo tai kaava -ruutuun:
=$A2=$B2Tämä kaava vertaa kahta saraketta rivi kerrallaan ja korostaa rivit, joilla on samat arvot (kopiot). Kuten näet, tähän syötetty kaava on vain valitun alueen ensimmäiselle riville, mutta ehdollinen muotoiluominaisuus käyttää kaavaa automaattisesti valitun alueen riveille.
Määritä sitten muotoilu Muotoilutyyli-asetuksista ja napsauta Valmis.
Kuten näet, vain rivit, joilla on vastaavat tiedot (kopiot) kahden sarakkeen välillä, korostetaan ja kaikki muut kaksoiskappaleet ohitetaan.
Korosta päällekkäiset solut useissa sarakkeissa
Kun käsittelet suurempia laskentataulukoita, joissa on useita sarakkeita, saatat haluta korostaa kaikki kopiot, jotka näkyvät useissa sarakkeissa yhden tai kahden sarakkeen sijasta. Voit silti käyttää ehdollista muotoilua korostaaksesi kaksoiskappaleen useissa sarakkeissa.
Valitse ensin kaikkien sarakkeiden ja rivien alue, joista haluat etsiä kaksoiskappaleita yhden tai kahden sarakkeen sijaan. Voit valita kokonaisia sarakkeita pitämällä Ctrl-näppäintä painettuna ja napsauttamalla kunkin sarakkeen yläosassa olevaa kirjainta. Vaihtoehtoisesti voit myös napsauttaa alueen ensimmäistä ja viimeistä solua samalla, kun pidät Shift-näppäintä painettuna valitaksesi useita sarakkeita kerralla.
Esimerkissä valitsemme A2:C30.
Napsauta sitten "Muotoile" -vaihtoehtoa valikosta ja valitse "Ehdollinen muotoilu".
Aseta Ehdollisen muotosäännön muotosäännöiksi Mukautettu kaava ja kirjoita seuraava kaava Arvo tai kaava -ruutuun:
=countif($A$2:$C$30,A2)>Lisäämme $-merkin soluviittausten eteen tehdäksemme niistä absoluuttisia sarakkeita, joten se ei muuta kaavaa. Voit myös syöttää kaavan ilman '$'-merkkejä, se toimii kummallakin tavalla.
Valitse sitten muotoilu, jossa haluat korostaa päällekkäiset solut käyttämällä Muotoilutyyli-vaihtoehtoja. Täällä valitsemme "keltaisen" täyttövärin. Napsauta sen jälkeen 'Valmis'.
Tämä korostaa kopiot kaikista valitsemistasi sarakkeista alla olevan kuvan mukaisesti.
Kun olet ottanut ehdollisen muotoilun käyttöön, voit muokata tai poistaa ehdollisen muotoilun sääntöä milloin tahansa.
Jos haluat muokata nykyistä ehdollisen muotoilun sääntöä, valitse mikä tahansa solu, jossa on ehdollinen muotoilu, siirry valikosta Muotoile ja valitse Ehdollinen muotoilu.
Tämä avaa oikealla olevan Ehdolliset muotosäännöt -ruudun, jossa on luettelo nykyiseen valintaan sovelletuista muotosäännöistä. Kun viet hiiren osoittimen säännön päälle, näet poistopainikkeen. Poista sääntö napsauttamalla Poista-painiketta. Tai jos haluat muokata parhaillaan näkyvää sääntöä, napsauta itse sääntöä.
Jos haluat lisätä toisen ehdollisen muotoilun nykyisen säännön päälle, napsauta Lisää toinen sääntö -painiketta.
Laske kahden sarakkeen väliset kaksoiskappaleet
Joskus haluat laskea, kuinka monta kertaa yhden sarakkeen arvo toistuu toisessa sarakkeessa. Se voidaan tehdä helposti käyttämällä samaa COUNTIF-toimintoa.
Jos haluat selvittää, kuinka monta kertaa sarakkeen A arvo esiintyy sarakkeessa B, kirjoita seuraava kaava toisen sarakkeen soluun:
=LASKEJOS($B$2:$B$30,$A2)Kirjoita tämä kaava soluun C2. Tämä kaava laskee, kuinka monta kertaa solun A2 arvo on sarakkeessa (B2:B30) ja palauttaa luvun solussa C2.
Kun kirjoitat kaavan ja painat Enter, Auto-Fill-ominaisuus tulee näkyviin. Napsauta rastimerkkiä täyttääksesi tämän kaavan automaattisesti muihin soluihin (C3:C30).
Jos automaattisen täytön ominaisuus ei tule näkyviin, napsauta sinistä neliötä solun C2 oikeassa alakulmassa ja vedä se alas kopioidaksesi solun C2 kaavan soluihin C3:C30.
'Vertailu 1' -sarake (C) näyttää nyt, kuinka monta kertaa kukin vastaava sarakkeen A arvo näkyy sarakkeessa B. Esimerkiksi A2:n tai "Franklynin" arvoa ei löydy sarakkeesta B, joten COUNTIF-funktio palauttaa "0". Ja arvo "Loreta" (A5) löytyy kahdesti sarakkeesta B, joten se palauttaa "2".
Nyt meidän on toistettava samat vaiheet löytääksemme sarakkeen B päällekkäiset määrät. Kirjoita seuraava kaava sarakkeen D soluun D2 (vertailu 2):
=LASKEJOS($A$2:$A$30, $B2)Korvaa tässä kaavassa alue '$B$2:$B$30' arvoon '$A$2:$A$30' ja '$B2' arvoon '$A2'. Funktio laskee, kuinka monta kertaa solun B2 arvo on sarakkeessa A (A2:A30) ja palauttaa määrän soluun D2.
Täytä sitten kaava automaattisesti loput solut (D3:D30) sarakkeessa D. Nyt 'Vertailu 2' näyttää, kuinka monta kertaa kukin vastaava arvo sarakkeessa B näkyy sarakkeessa A. Esimerkiksi , B2:n eli Starkin arvo löytyy kahdesti sarakkeesta A, joten COUNTIF-funktio palauttaa arvon 2.
merkintä: Jos haluat laskea kopiot kaikista sarakkeista tai useista sarakkeista, sinun on vain muutettava COUNTIF-funktion ensimmäisen argumentin alue useiksi sarakkeiksi yhden sarakkeen sijaan. Muuta esimerkiksi alue A2:A30 arvoksi A2:B30, jolloin kaikki kaksoiskappaleet lasketaan kahdessa sarakkeessa yhden sijasta.
Se siitä.