Tämä wikiHow opettaa sinulle, kuinka voit käyttää ehdollista muotoilua kaikkien Excel -laskentataulukon kahden erillisen luettelon solujen vertaamiseen ja merkitä molemmissa luetteloissa näkyvät solut. Tämä ominaisuus on käytettävissä vain Excelin työpöytäversiossa, eikä mobiilisovellus tue sitä.
Askeleet
Menetelmä 1/2: CountIf -kaavan käyttäminen
Vaihe 1. Avaa Excel -laskentataulukko, jota haluat muokata
Etsi laskentataulukkotiedosto, jossa on vertailtavat luettelot, ja avaa se kaksoisnapsauttamalla sitä Microsoft Excelissä.
Vaihe 2. Valitse ensimmäinen luettelosi
Napsauta ensimmäisen luettelon ensimmäistä solua ja vedä hiiri kokonaan alas luettelon viimeiseen soluun valitaksesi koko tietoalueen.
Vaihe 3. Napsauta Kaavat -välilehteä työkalurivin valintanauhassa
Tämä välilehti löytyy laskentataulukon yläreunan työkalupalkin yläpuolelta. Se avaa työkalut työkalurivin nauhalla.
Vaihe 4. Napsauta työkalurivin Määritä nimi -painiketta
Löydät tämän vaihtoehdon "Kaavat" -nauhan keskellä. Se avaa uuden ponnahdusikkunan ja antaa sinun nimetä luettelosi.
Vaihe 5. Kirjoita Nimi -kenttään Lista1
Napsauta ponnahdusikkunan yläosassa olevaa tekstikenttää ja kirjoita luettelon nimi tähän.
- Voit myöhemmin käyttää tätä nimeä lisätäksesi luettelosi vertailukaavaan.
- Vaihtoehtoisesti voit antaa listallesi toisen nimen täällä. Jos tämä on esimerkiksi sijaintiluettelo, voit antaa sille nimen "locations1" tai "locationList".
Vaihe 6. Napsauta ponnahdusikkunassa OK
Tämä vahvistaa toimintasi ja nimeää listasi.
Vaihe 7. Nimeä toinen luettelosi nimellä List2
Noudata samoja vaiheita kuin ensimmäisessä luettelossa ja anna toiselle listalle nimi. Näin voit käyttää tätä toista luetteloa nopeasti vertailukaavassasi myöhemmin.
Voit antaa listalle haluamasi nimen. Muista muistaa tai merkitä muistiin jokaiselle listallesi antamasi nimi
Vaihe 8. Valitse ensimmäinen luettelosi
Napsauta ensimmäisen luettelon ensimmäistä solua ja valitse koko tietoalue vetämällä alaspäin.
Varmista, että ensimmäinen luettelosi on valittu, ennen kuin aloitat ehdollisen muotoilun määrittämisen
Vaihe 9. Napsauta työkalurivin Aloitus -välilehteä
Tämä on ensimmäinen välilehti työkalurivin nauhan vasemmassa yläkulmassa. Se avaa peruslaskentataulukkotyökalut työkalupalkissa.
Vaihe 10. Napsauta työkalupalkissa Ehdollinen muotoilu
Tämä vaihtoehto näyttää pieneltä laskentataulukkokuvakkeelta, jossa jotkut solut on korostettu punaisella ja sinisellä. Se avaa avattavan valikon kaikista muotoiluvaihtoehdoista.
Vaihe 11. Napsauta avattavasta valikosta Uusi sääntö
Se avaa uuden ponnahdusikkunan ja antaa sinun määrittää manuaalisesti uuden muotoilusäännön valitulle alueelle.
Vaihe 12. Valitse "Käytä kaavaa määritettävien solujen määrittämiseen" -vaihtoehto
Tämän vaihtoehdon avulla voit kirjoittaa muotoilukaavan manuaalisesti kahden luettelon vertaamiseksi.
- Päällä Windows, löydät sen sääntöluettelon alareunasta "Valitse säännön tyyppi" -ruudusta.
- Päällä Mac, valitse Klassikko avattavasta "Tyyli" -valikosta ponnahdusikkunan yläosassa. Etsi sitten tämä vaihtoehto Tyyli-valikon alla olevasta toisesta avattavasta valikosta.
Vaihe 13. Napsauta ponnahdusikkunan kaavakenttää
Voit kirjoittaa minkä tahansa kelvollisen Excel -kaavan, jos haluat määrittää ehdollisen muotoilusäännön.
Vaihe 14. Kirjoita kaavapalkkiin = laskuri (Lista2, A1) = 1
Tämä kaava skannaa kaksi luetteloa ja merkitsee kaikki ensimmäisen luettelon solut, jotka näkyvät myös toisessa luettelossa.
- Korvaa kaavan A1 ensimmäisen luettelosi ensimmäisen solun numerolla.
- Jos esimerkiksi ensimmäisen listasi ensimmäinen solu on solu D5, kaava näyttää tältä = laskuri (Lista2, D5) = 1.
- Jos annoit toiselle listallesi toisen nimen, muista korvata kaava Lista2 oman listasi todellisella nimellä.
- Vaihtoehtoisesti voit muuttaa kaavan arvoksi = laskuri (Lista2, A1) = 0, jos haluat merkitä solut siihen Älä näkyvät toisessa luettelossa.
Vaihe 15. Kirjoita = laskuri (Lista1, B1) = 1 kaavapalkkiin (valinnainen)
Jos haluat löytää ja merkitä solut toinen luettelo, joka näkyy myös ensimmäisessä luettelossa, käytä tätä kaavaa ensimmäisen sijaan.
Korvaa List1 ensimmäisen listasi nimellä ja B1 toisen listasi ensimmäisellä solulla
Vaihe 16. Valitse solujen merkitsemiseksi mukautettu muoto (valinnainen)
Voit valita mukautetun taustan täyttövärin ja eri kirjasintyylit kaavan löytämien solujen merkitsemiseksi.
- Päällä Windows, Klikkaa Muoto -painiketta ponnahdusikkunan oikeassa alakulmassa. Voit valita taustavärin "Täytä" -välilehdestä ja kirjasintyylit "Fontti" -välilehdestä.
- Päällä Mac, valitse esiasetettu muoto avattavasta Muotoile-valikosta. Voit myös valita mukautettu muoto valitse taustan täyttö- ja kirjasintyylit manuaalisesti.
Vaihe 17. Napsauta ponnahdusikkunassa OK
Tämä vahvistaa ja soveltaa vertailukaavaa. Kaikki ensimmäisen luettelosi solut, jotka näkyvät myös toisessa luettelossa, on merkitty valitsemallasi värillä ja fontilla.
- Jos esimerkiksi valitset vaaleanpunaisen täytteen tummanpunaisella tekstillä, kaikki toistuvat solut muuttuvat tälle värille ensimmäisessä luettelossasi.
- Jos käytät toista yllä olevaa kaavaa, ehdollinen muotoilu merkitsee toistuvat solut toisessa luettelossasi ensimmäisen luettelon sijaan.
Tapa 2/2: VLookup -kaavan käyttö
Vaihe 1. Avaa Excel -laskentataulukko
Etsi Excel-tiedosto, jossa on vertailtavat luettelot, ja kaksoisnapsauta tiedoston nimeä tai kuvaketta avataksesi laskentataulukon Microsoft Excelissä.
Vaihe 2. Napsauta tyhjää solua toisen luettelosi ensimmäisen kohteen vieressä
Etsi toinen luettelosi laskentataulukosta ja napsauta tyhjää solua ensimmäisen luettelokohteen vieressä ylhäältä.
- Voit lisätä VLookup -kaavan tähän.
- Vaihtoehtoisesti voit valita minkä tahansa tyhjän solun laskentataulukosta. Tämä solu tekee vain helpommaksi nähdä vertauksesi toisen luettelosi vieressä.
Vaihe 3. Kirjoita = vlookup (tyhjään soluun
VLookup -kaavan avulla voit vertailla kaikkia kahden erillisen luettelon kohteita ja nähdä, onko arvo toistuva vai uusi arvo.
Älä sulje kaavan sulkeita ennen kuin kaava on valmis
Vaihe 4. Valitse toinen luettelosi ensimmäinen kohde
Sulkematta kaavan sulkeita napsauta toisen luettelosi ensimmäistä kohdetta. Tämä lisää toisen luettelosi ensimmäisen solun kaavaan.
Vaihe 5. Kirjoita kaavaan a, pilkku
Kun olet valinnut toisen luettelosi ensimmäisen solun, kirjoita kaavaan pilkku. Voit valita vertailualueen seuraavaksi.
Vaihe 6. Pidä painettuna ja valitse koko ensimmäinen luettelosi
Tämä lisää ensimmäisen luettelosi solualueen VLookup -kaavan toiseen osaan.
Tämän avulla voit etsiä ensimmäisestä luettelosta valitun kohteen toisesta luettelostasi (ensimmäinen kohde toisen luettelon yläosassa) ja palata, jos se on toistuva tai uusi arvo
Vaihe 7. Kirjoita kaavaan a, pilkku
Tämä lukitsee kaavasi vertailualueen.
Vaihe 8. Kirjoita kaavaan 1 pilkun jälkeen
Tämä numero edustaa sarakkeen hakemistonumeroa. Se kehottaa VLookup -kaavaa etsimään todellista luettelosaraketta sen vieressä olevan eri sarakkeen sijaan.
Jos haluat, että kaava palauttaa arvon ensimmäisen luettelosi vieressä olevasta sarakkeesta, kirjoita 2 tähän
Vaihe 9. Kirjoita kaavaan a, pilkku
Tämä lukitsee sarakkeen hakemistonumerosi (1) VLookup -kaavassa.
Vaihe 10. Kirjoita kaavaan FALSE
Tämä etsii luettelosta tarkan vastaavuuden valitusta hakukohteesta (ensimmäinen kohde toisen luettelon yläosassa) likimääräisten osumien sijaan.
- FALSE: n sijaan voit käyttää 0: ta, se on täsmälleen sama.
- Vaihtoehtoisesti voit kirjoittaa TOSI tai 1, jos haluat etsiä likimääräistä osumaa.
Vaihe 11. Sulje kaava kirjoittamalla) lopussa
Voit nyt suorittaa kaavan ja tarkistaa, onko toisessa luettelossasi valittu hakukohde toistuva vai uusi arvo.
Jos esimerkiksi toinen luettelosi alkaa B1: stä ja ensimmäinen luettelosi siirtyy soluista A1 kohtaan A5, kaava näyttää = vlookup (B1, $ A $ 1: $ A $ 5, 1, false).
Vaihe 12. Paina ↵ Enter tai Palaa näppäimistöllä.
Tämä suorittaa kaavan ja etsii ensimmäisestä luettelostasi toisen luettelosi ensimmäisen kohteen.
- Jos tämä on toistuva arvo, näet saman arvon uudelleen tulostettuna kaavasoluun.
- Jos tämä on uusi arvo, näet " #N/A"painettu tänne.
- Jos esimerkiksi etsit ensimmäisestä luettelosta sanaa "John" ja näet nyt "John" kaavasolussa, se on toistuva arvo, joka näkyy molemmissa luetteloissa. Jos näet "#N/A", se on uusi arvo toisessa luettelossa.
Vaihe 13. Valitse kaavasolu
Kun olet suorittanut kaavan ja nähnyt ensimmäisen luettelokohteen tulokset, valitse se valitsemalla kaavasolu.
Vaihe 14. Napsauta ja vedä alas solun oikeassa alakulmassa olevaa vihreää pistettä
Tämä laajentaa kaavasolua luetteloa pitkin ja soveltaa kaavaa kaikkiin toisen luettelosi luettelokohteisiin.
- Näin voit verrata jokaista toisen luettelosi kohdetta koko ensimmäiseen luetteloon.
- Tämä etsii ensimmäisestä luettelostasi jokaisen toisen luettelosi kohteen erikseen ja näyttää tuloksen jokaisen solun vieressä erikseen.
- Jos haluat nähdä uuden merkin uusille arvoille "#N/A": n sijaan, käytä tätä kaavaa: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "New Value"). Tämä tulostaa "Uusi arvo" uusille arvoille "#N/A" sijaan.
