Ehdollinen muotoilu Excelissä: Lopullinen opas esimerkkeineen

Ehdollinen muotoilu on yksi Excel -laskentataulukoiden yksinkertaisimmista mutta tehokkaimmista ominaisuuksista.

Kuten nimestä voi päätellä, voit käyttää ehdollista muotoilua Excelissä, kun haluat korostaa solut, jotka täyttävät tietyn ehdon.

Se antaa sinulle mahdollisuuden lisätä visuaalinen analyysikerros nopeasti tietojoukkoosi. Voit luoda lämpökarttoja, näyttää kasvavia/väheneviä kuvakkeita, Harvey -kuplia ja paljon muuta käyttämällä ehdollista muotoilua Excelissä.

Ehdollisen muotoilun käyttäminen Excelissä (esimerkkejä)

Tässä opetusohjelmassa näytän sinulle seitsemän hämmästyttävää esimerkkiä ehdollisen muotoilun käytöstä Excelissä:

• Tunnista nopeasti kaksoiskappaleet käyttämällä ehdollista muotoilua Excelissä.
• Korosta solut, joiden arvo on suurempi/pienempi kuin numero tietojoukossa.
• Ylä-/alaosan 10 (tai 10%) arvojen korostaminen tietojoukossa.
• Korosta virheitä/tyhjiä käyttämällä ehdollista muotoilua Excelissä.
• Lämpökarttojen luominen ehdollisen muotoilun avulla Excelissä.
• Korosta jokainen n. Rivi/sarake ehdollisen muotoilun avulla.
• Hae ja korosta käyttämällä ehdollista muotoilua Excelissä.

1. Tunnista nopeasti kaksoiskappaleet

Excelin ehdollista muotoilua voidaan käyttää kopioiden tunnistamiseen tietojoukossa.

Voit tehdä tämän seuraavasti:

• Valitse tietojoukko, jossa haluat korostaa kaksoiskappaleet.
• Siirry etusivulle -> Ehdollinen muotoilu -> Solusääntöjen korostaminen -> Päällekkäiset arvot.
• Varmista Monista arvot -valintaikkunassa, että Kopio on valittuna vasemmasta avattavasta valikosta. Voit määrittää käytettävän muodon oikean avattavan valikon avulla. Joitakin olemassa olevia muotoja voit käyttää tai määrittää oman muodon mukautetun muodon avulla.
• Napsauta OK.

Tämä korostaisi välittömästi kaikki solut, joilla on kaksoiskappale valitussa tietojoukossa. Tietojoukko voi olla yhdessä sarakkeessa, useissa sarakkeissa tai ei-viereisessä solualueella.

Katso myös: Ultimate Guide to Find and Remove Duplicates in Excel.

2. Korosta solut, joiden arvo on suurempi/pienempi kuin luku

Voit käyttää Excelin ehdollista muotoilua korostaaksesi nopeasti solut, jotka sisältävät määritettyä arvoa suurempia tai pienempiä arvoja. Esimerkiksi korostamalla kaikki solut, joiden myyntiarvo on alle 100 miljoonaa, tai korostamalla solut, joiden merkinnät ovat alle ohituskynnyksen.

Tässä on seuraavat vaiheet:

• Valitse koko tietojoukko.
• Siirry etusivulle -> Ehdollinen muotoilu -> Solusääntöjen korostaminen -> Suurempi kuin… / Alle…
• Valitsemasi vaihtoehdon perusteella (suurempi tai pienempi) avautuu valintaikkuna. Oletetaan, että valitset "Suurempi kuin" -vaihtoehdon. Kirjoita valintaikkunan numero vasemmalla olevaan kenttään. Tarkoituksena on korostaa soluja, joiden numero on suurempi kuin tämä määritetty luku.

• Määritä ehto täyttäville soluille käytettävä muoto käyttämällä oikealla olevaa avattavaa valikkoa. Joitakin olemassa olevia muotoja voit käyttää tai määrittää oman muodon mukautetun muodon avulla.
• Napsauta OK.

Tämä korostaisi välittömästi kaikki solut, joiden arvot ovat suurempia kuin 5 tietojoukossa.Huomautus: Jos haluat korostaa arvoja, jotka ovat suurempia kuin 5, käytä ehdollista muotoilua uudelleen kriteerillä "Equal To".

Samaa prosessia voidaan noudattaa korostamaan solut, joiden arvo on pienempi kuin määritetyt arvot.

3. Korostaminen Ylä/ala 10 (tai 10%)

Ehdollinen muotoilu Excelissä voi nopeasti tunnistaa kymmenen suosituinta kohdetta tai kymmenen prosenttia tietojoukosta. Tästä voi olla apua tilanteissa, joissa haluat nopeasti nähdä parhaat ehdokkaat myyntitietojen pisteiden tai parhaiden kauppa -arvojen perusteella.

Samoin voit myös nopeasti tunnistaa tietojoukon alimmat 10 kohdetta tai alimmat 10%.

Tässä on seuraavat vaiheet:

• Valitse koko tietojoukko.
• Siirry etusivulle -> Ehdollinen muotoilu -> Ylä- / alareunan säännöt -> Top 10 kohdetta (tai %) / 10 alinta kohdetta (tai %).
• Valitsemasi perusteella se avaa valintaikkunan. Oletetaan, että valitsit Top 10 -kohteet, ja se avaa valintaikkunan alla olevan kuvan mukaisesti:
• Määritä muoto, jota sovelletaan ehtoja täyttäviin soluihin, käyttämällä oikeanpuoleista avattavaa valikkoa. Joitakin olemassa olevia muotoja voit käyttää tai määrittää oman muodon mukautetun muodon avulla.
• Napsauta OK.

Tämä korostaisi heti valitun tietojoukon kymmenen parasta kohdetta. Huomaa, että tämä toimii vain soluissa, joissa on numeerinen arvo.

Lisäksi jos tietojoukossa on alle 10 solua ja valitset vaihtoehdot korostaaksesi Top 10 -kohteet/10 alinta kohdetta, kaikki solut korostuvat.

Seuraavassa on muutamia esimerkkejä siitä, miten ehdollinen muotoilu toimii:

4. Korostusvirheet/tyhjät kohdat

Jos käytät Excelissä paljon numeerisia tietoja ja laskelmia, tiedät, kuinka tärkeää on tunnistaa ja käsitellä virheellisiä tai tyhjiä soluja. Jos näitä soluja käytetään lisälaskelmissa, se voi johtaa virheellisiin tuloksiin.

Ehdollinen muotoilu Excelissä voi auttaa sinua tunnistamaan ja korostamaan nopeasti solut, joissa on virheitä tai jotka ovat tyhjiä.

Oletetaan, että meillä on alla esitetyn kaltainen tietojoukko:

Tässä tietojoukossa on tyhjä solu (A4) ja virheitä (A5 ja A6).

Seuraavat vaiheet korostavat tyhjiä tai virheellisiä soluja:

• Valitse tietojoukko, jossa haluat korostaa tyhjät solut ja solut, joissa on virheitä.
• Siirry etusivulle -> Ehdollinen muotoilu -> Uusi sääntö.
• Valitse Uusi muotoilusääntö -valintaikkunassa Käytä kaavaa määritettävien solujen määrittämiseen.
• Kirjoita seuraava kaava Muokkaa säännön kuvausta -osiossa olevaan kenttään:
  = TAI (ISBLANK (A1), ISERROR (A1))
  • Yllä oleva kaava tarkistaa kaikista soluista kaksi ehtoa - onko se tyhjä vai ei ja onko siinä virhe tai ei. Jos jokin ehdoista on TOSI, se palauttaa arvon TOSI.
• Määritä muoto, jota haluat käyttää soluihin, jotka ovat tyhjiä tai joissa on virheitä. Voit tehdä tämän napsauttamalla Muotoile -painiketta. Se avaa "Muotoile solut" -valintaikkunan, jossa voit määrittää muodon.
• Napsauta OK.

Tämä korostaisi välittömästi kaikki solut, jotka ovat joko tyhjiä tai joissa on virheitä.

merkintä: Sinun ei tarvitse käyttää koko aluetta A1: A7 kaavassa ehdollisessa muotoilussa. Edellä mainittu kaava käyttää vain A1: tä. Kun käytät tätä kaavaa koko alueelle, Excel tarkistaa solun kerrallaan ja säätää viitettä. Esimerkiksi kun se tarkistaa A1: n, se käyttää kaavaa = TAI (ISBLANK (A1), ISERROR (A1)). Kun se tarkistaa solun A2, se käyttää sitten kaavaa = TAI (ISBLANK (A2), ISERROR (A2)). Se säätää automaattisesti viitettä (koska ne ovat suhteellisia viittauksia) sen mukaan, mitä solua analysoidaan. Joten sinun ei tarvitse kirjoittaa erillistä kaavaa jokaiselle solulle. Excel on tarpeeksi älykäs muuttaakseen soluviittauksen kokonaan itsestään 🙂

Katso myös: IFERRORin ja ISERRORin käyttäminen Excel -virheiden käsittelyyn.

5. Lämpökarttojen luominen

Lämpökartta on visuaalinen esitys tiedoista, joissa väri edustaa solun arvoa. Voit esimerkiksi luoda lämpökartan, jossa solu, jolla on suurin arvo, on väriltään vihreä ja siirtyy kohti punaista väriä arvon pienentyessä.

Jotain alla olevan kuvan mukaisesti:

Yllä olevan tietojoukon arvot ovat välillä 1 ja 100. Solut korostetaan niiden arvon perusteella. 100 saa vihreän värin, 1 saa punaisen värin.

Tässä on vaiheet lämpökarttojen luomiseksi käyttämällä ehdollista muotoilua Excelissä.

• Valitse tietojoukko.
• Siirry etusivulle -> Ehdollinen muotoilu -> Väriasteikot ja valitse yksi värimalleista.

Heti kun napsautat lämpökarttakuvaketta, se soveltaa muotoilua tietojoukkoon. Voit valita useista värigradienteista. Jos et ole tyytyväinen nykyisiin värivaihtoehtoihin, voit valita lisää sääntöjä ja määrittää haluamasi värin.

Huomautus: Samalla tavalla voit käyttää myös Data Bard- ja Icon -joukkoja.

6. Korosta joka toinen rivi/sarake

Voit halutessasi korostaa vaihtoehtoisia rivejä tietojen luettavuuden lisäämiseksi.

Näitä kutsutaan seepraviivoiksi ja ne voivat olla erityisen hyödyllisiä, jos tulostat tietoja.

Nyt on kaksi tapaa luoda nämä seepraviivat. Nopein tapa on muuntaa taulukkotietosi Excel -taulukkoksi. Se käytti automaattisesti väriä vaihtoehtoisille riveille. Voit lukea siitä lisää täältä.

Toinen tapa on käyttää ehdollista muotoilua.

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Tässä on vaiheet vaihtoehtoisten rivien korostamiseksi käyttämällä ehdollista muotoilua Excelissä.

• Valitse tietojoukko. Valitse yllä olevassa esimerkissä A2: C13 (joka ei sisällä otsikkoa). Jos haluat sisällyttää myös otsikon, valitse koko tietojoukko.
• Avaa Ehdollinen muotoilu -valintaikkuna (Koti-> Ehdollinen muotoilu-> Uusi sääntö) [Pikanäppäin - Alt + O + D].
• Valitse valintaikkunassa Käytä kaavaa määritettäessä solut, jotka haluat muotoilla.
• Kirjoita seuraava kaava Muokkaa säännön kuvausta -osiossa olevaan kenttään:
  = ISODD (RIVI ())

• Yllä oleva kaava tarkistaa kaikki solut ja jos solun RIVI -numero on pariton, se palauttaa arvon TOSI. Määritettyä ehdollista muotoa sovellettaisiin kaikkiin soluihin, jotka palauttavat TOSI.
• Määritä muoto, jota haluat käyttää soluihin, jotka ovat tyhjiä tai joissa on virheitä. Voit tehdä tämän napsauttamalla Muotoile -painiketta. Se avaa "Muotoile solut" -valintaikkunan, jossa voit määrittää muodon.
• Napsauta OK.

Se siitä! Tietojoukon vaihtoehtoiset rivit korostetaan.

Voit käyttää samaa tekniikkaa monissa tapauksissa. Sinun tarvitsee vain käyttää asianmukaista kaavaa ehdollisessa muotoilussa. Tässä muutamia esimerkkejä:

• Korosta vaihtoehtoiset parilliset rivit: = ISEVEN (ROW ())
• Korosta vaihtoehtoiset lisärivit: = ISODD (ROW ())
• Korosta joka kolmas rivi: = MOD (ROW (), 3) = 0

7. Etsi ja korosta tietoja käyttämällä ehdollista muotoilua

Tämä on hieman edistynyt ehdollisen muotoilun käyttö. Se saisi sinut näyttämään Excelin rockstarilta.

Oletetaan, että sinulla on alla näytetty tietojoukko, jossa on tuotteen nimi, myyntiedustaja ja maantiede. Ajatuksena on kirjoittaa merkkijono soluun C2, ja jos se vastaa minkä tahansa solun tietoja, sen pitäisi olla korostettu. Jotain alla olevan kuvan mukaisesti:

Luo tämä haku- ja korostustoiminto seuraavasti:

• Valitse tietojoukko.
• Siirry etusivulle -> Ehdollinen muotoilu -> Uusi sääntö (Pikanäppäin - Alt + O + D).
• Valitse Uusi muotoilusääntö -valintaikkunassa vaihtoehto Käytä kaavaa määritettävien solujen määrittämiseen.
• Kirjoita seuraava kaava Muokkaa säännön kuvausta -osiossa olevaan kenttään:
  = JA ($ C $ 2 "", $ C $ 2 = B5)
• Määritä muoto, jota haluat käyttää soluihin, jotka ovat tyhjiä tai joissa on virheitä. Voit tehdä tämän napsauttamalla Muotoile -painiketta. Se avaa "Muotoile solut" -valintaikkunan, jossa voit määrittää muodon.
• Napsauta OK.

Se siitä! Nyt kun kirjoitat mitään soluun C2 ja painat enter, se korostaa kaikki vastaavat solut.

Miten tämä toimii?

Ehdollisessa muotoilussa käytetty kaava arvioi kaikki tietojoukon solut. Oletetaan, että kirjoitat Japaniin solussa C2. Nyt Excel arvioi kunkin solun kaavan.

Kaava palauttaa solun TOSI, kun kaksi ehtoa täyttyvät:

• Solu C2 ei ole tyhjä.
• Solun C2 sisältö vastaa tarkasti tietojoukon solun sisältöä.

Näin ollen kaikki solut, jotka sisältävät Japanin tekstin, korostuvat.

Lataa esimerkkitiedosto

Voit käyttää samaa logiikkaa muunnelmien luomiseen, kuten:

• Korosta koko rivi solun sijaan.
• Korosta vaikka osittainen vastaavuus.
• Korosta solut/rivit kirjoittaessasi (dynaaminen) [Tulet rakastamaan tätä temppua :)].

Ehdollisen muotoilun poistaminen Excelistä

Kun ehdollinen muotoilu on otettu käyttöön, se pysyy paikallaan, ellet poista sitä manuaalisesti. Paras käytäntö on, että ehdollinen muotoilu koskee vain niitä soluja, joissa sitä tarvitaan.

Koska se on epävakaa, se voi johtaa hitaaseen Excel -työkirjaan.

Ehdollisen muotoilun poistaminen:

• Valitse solut, joista haluat poistaa ehdollisen muotoilun.
• Siirry etusivulle -> Ehdollinen muotoilu -> Tyhjennä säännöt -> Tyhjennä säännöt valituista soluista.
  • Jos haluat poistaa ehdollisen muotoilun koko laskentataulukosta, valitse Tyhjennä säännöt koko taulukosta.

Tärkeitä tietoja Excelin ehdollisesta muotoilusta

• Ehdollinen muotoilu haihtuvassa muodossa. Se voi johtaa hitaaseen työkirjaan. Käytä sitä vain tarvittaessa.
• Kun kopioit ehdollisen muotoilun sisältäviä liitäntäsoluja, myös ehdollinen muotoilu kopioidaan.
• Jos käytät useita sääntöjä samassa solusarjassa, kaikki säännöt pysyvät aktiivisina. Päällekkäisyyden tapauksessa etusijalla on viimeksi käytetty sääntö. Voit kuitenkin muuttaa järjestystä muuttamalla järjestystä Hallitse sääntöjä -valintaikkunassa.