Tingimuslik vormindamine Excelis: lõplik juhend koos näidetega

Tingimuslik vormindamine on Exceli arvutustabelite üks lihtsamaid, kuid samas võimsaid funktsioone.

Nagu nimigi ütleb, saate Excelis kasutada tingimusvormingut, kui soovite esile tõsta lahtreid, mis vastavad määratud tingimustele.

See annab teile võimaluse kiiresti lisada oma andmekogumile visuaalse analüüsi kiht. Excelis tingimusvormingu abil saate luua soojuskaarte, näidata kasvavaid/kahanevaid ikoone, Harvey mullid ja palju muud.

Tingimusliku vormindamise kasutamine Excelis (näited)

Selles õpetuses näitan teile seitset hämmastavat näidet tingimusvormingu kasutamise kohta Excelis:

• Duplikaatide kiire tuvastamine Excelis tingimusvormingu abil.
• Tõstke esile lahtrid, mille väärtus on suurem/väiksem kui arv andmekogumis.
• Andmekogumi 10 (või 10%) väärtuse esiletõstmine.
• Tõrgete/tühikute esiletõstmine, kasutades Excelis tingimuslikku vormingut.
• Soojuskaartide loomine tingimusvormingu abil Excelis.
• Tõstke tingimusvormingu abil esile iga n -ö rida/veerg.
• Otsige ja tõstke esile Excelis tingimusvormingu abil.

1. Kiiresti tuvastage duplikaadid

Tingimuslikku vormingut Excelis saab kasutada andmestiku duplikaatide tuvastamiseks.

Seda saate teha järgmiselt.

• Valige andmekogum, milles soovite duplikaate esile tõsta.
• Avage Avaleht -> Tingimuslik vormindamine -> Lahtri reeglite esiletõstmine -> Dubleerivad väärtused.
• Veenduge, et dialoogiboksis Korduvad väärtused on vasakpoolses rippmenüüs Duplicate. Kasutatava vormingu saate määrata parema rippmenüü abil. Olemasolevaid vorminguid saate kasutada või kohandatud vormingu abil oma vormingu määrata.
• Klõpsake nuppu OK.

See tõstaks koheselt esile kõik lahtrid, millel on valitud andmekogumis duplikaat. Teie andmekogum võib olla ühes veerus, mitmes veerus või mitteseotud lahtrite vahemikus.

Vaata ka: Ultimate juhend Exceli duplikaatide leidmiseks ja eemaldamiseks.

2. Tõstke esile lahtrid, mille väärtus on suurem/väiksem kui arv

Saate Excelis tingimusvormingu abil kiiresti esile tõsta lahtreid, mis sisaldavad määratud väärtusest suuremaid/väiksemaid väärtusi. Näiteks kõigi lahtrite esiletõstmine, mille müügiväärtus on alla 100 miljoni, või lahtrite esiletõstmine, kui märgid on madalamad kui lävend.

Selleks toimige järgmiselt.

• Valige kogu andmekogum.
• Avage Avaleht -> Tingimuslik vormindamine -> Lahtri reeglite esiletõstmine -> Suurem kui… / Vähem kui…
• Sõltuvalt teie valitud valikust (suurem või väiksem) avaneb dialoogiboks. Oletame, et valite valiku „Suurem kui”. Sisestage dialoogiboksis vasakpoolsel väljal number. Eesmärk on esile tõsta lahtreid, mille arv on suurem kui see määratud arv.

• Määrake vorming, mida tingimustele vastavatele lahtritele rakendada, kasutades paremal olevat rippmenüüd. Olemasolevaid vorminguid saate kasutada või kohandatud vormingu abil oma vormingu määrata.
• Klõpsake nuppu OK.

See tooks koheselt esile kõik andmestiku lahtrid, mille väärtused on suuremad kui 5.Märkus. Kui soovite esile tõsta väärtusi, mis on suuremad kui 5, peaksite uuesti kasutama tingimusvormingut kriteeriumiga „Võrdne”.

Sama protsessi saab järgida lahtrite esiletõstmiseks, mille väärtus on väiksem kui määratud väärtused.

3. Ülemine/alumine esiletõstmine 10 (või 10%)

Tingimuslik vormindamine Excelis võimaldab andmekogumist kiiresti tuvastada 10 parimat üksust või 10%. See võib olla kasulik olukordades, kus soovite kiiresti näha parimaid kandidaate müügiandmete hinde või parima tehinguväärtuse järgi.

Samamoodi saate kiiresti tuvastada andmestiku 10 alumist üksust või 10% alumist.

Selleks toimige järgmiselt.

• Valige kogu andmekogum.
• Avage Avaleht -> Tingimuslik vormindamine -> Ülemised / alumised reeglid -> Top 10 üksust (või %) / 10 alumist üksust (või %).
• Teie valitud põhjal avaneb dialoogiboks. Oletame, et valisite 10 parimat üksust, seejärel avaneb dialoogiboks, nagu allpool näidatud:
• Määrake vorming, mida tingimustele vastavatele lahtritele rakendada, kasutades paremal olevat rippmenüüd. Olemasolevaid vorminguid saate kasutada või kohandatud vormingu abil oma vormingu määrata.
• Klõpsake nuppu OK.

See tõstaks koheselt esile valitud andmekogumi kümme parimat üksust. Pange tähele, et see toimib ainult lahtrite puhul, millel on numbriline väärtus.

Samuti, kui andmekogus on vähem kui 10 lahtrit ja valite suvandid, mis tõstavad esile 10 parimat üksust/10 alumist üksust, tõstetakse kõik lahtrid esile.

Siin on mõned näited selle kohta, kuidas tingimusvorming toimiks.

4. Vigade/tühikute esiletõstmine

Kui töötate Excelis paljude arvandmete ja arvutustega, siis teate, kui oluline on tuvastada ja töödelda vigu sisaldavaid või tühje lahtreid. Kui neid lahtreid kasutatakse edasistes arvutustes, võib see põhjustada ekslikke tulemusi.

Tingimuslik vormindamine Excelis aitab teil kiiresti tuvastada ja esile tõsta vigu sisaldavaid või tühje lahtreid.

Oletame, et meil on allpool näidatud andmekogum:

Sellel andmekogul on tühi lahter (A4) ja vead (A5 ja A6).

Siin on sammud tühjade või vigadega lahtrite esiletõstmiseks.

• Valige andmekogum, milles soovite esile tõsta tühjad lahtrid ja vead.
• Avage Avaleht -> Tingimuslik vormindamine -> Uus reegel.
• Valige dialoogiboksis Uus vormindamise reegel suvand Kasutage valemit vormindatavate lahtrite määramiseks.
• Sisestage jaotise „Reegli kirjelduse muutmine” väljale järgmine valem:
  = VÕI (ISBLANK (A1), ISERROR (A1))
  • Ülaltoodud valem kontrollib kõiki lahtreid kahe tingimuse osas - kas see on tühi või mitte ja kas sellel on viga või mitte. Kui mõni tingimustest on tõene, tagastab see tõe.
• Määrake vorming, mida soovite tühjadele või vigadega lahtritele rakendada. Selleks klõpsake nuppu Vorminda. See avab dialoogiboksi „Lahtrite vormindamine”, kus saate vormingu määrata.
• Klõpsake nuppu OK.

See tõstaks koheselt esile kõik lahtrid, mis on tühjad või milles on vigu.

Märge: Tingimusvormingus ei pea te valemis kasutama kogu vahemikku A1: A7. Ülalmainitud valemis kasutatakse ainult A1. Kui rakendate seda valemit kogu vahemikule, kontrollib Excel üks lahtrit korraga ja kohandab viidet. Näiteks kui ta kontrollib A1, kasutab ta valemit = VÕI (ISBLANK (A1), ISERROR (A1)). Lahtrit A2 kontrollides kasutab ta seejärel valemit = VÕI (ISBLANK (A2), ISERROR (A2)). See kohandab automaatselt viidet (kuna need on suhtelised viited) sõltuvalt sellest, millist lahtrit analüüsitakse. Seega ei pea iga lahtri jaoks eraldi valemit kirjutama. Excel on piisavalt nutikas, et muuta lahtri viidet iseenesest 🙂

Vaata ka: IFERRORi ja ISERRORi kasutamine Excelis esinevate vigade käsitlemiseks.

5. Soojuskaartide loomine

Soojuskaart on andmete visuaalne esitus, kus värv tähistab lahtri väärtust. Näiteks saate luua soojuskaardi, kus kõrgeima väärtusega lahter on värvitud roheliseks ja väärtuse vähenedes toimub nihe punase värvi poole.

Midagi, nagu allpool näidatud:

Ülaltoodud andmekogumi väärtused on vahemikus 1 kuni 100. Lahtrid tõstetakse esile nende väärtuse alusel. 100 saab rohelise, 1 punase.

Siin on sammud soojuskaartide loomiseks, kasutades Excelis tingimuslikku vormingut.

• Valige andmekogum.
• Avage Avaleht -> Tingimuslik vormindamine -> Värviskaala ja valige üks värviskeemidest.

Niipea, kui klõpsate soojuskaardi ikoonil, rakendab see andmestikule vormindust. Valida saab mitme värvigradiendi vahel. Kui te pole olemasolevate värvivalikutega rahul, saate valida rohkem reegleid ja määrata soovitud värvi.

Märkus. Sarnasel viisil saate rakendada ka Data Bardi ja ikoonide komplekte.

6. Tõstke esile iga teine ​​rida/veerg

Andmete loetavuse suurendamiseks võiksite esile tõsta alternatiivseid ridu.

Neid nimetatakse sebrajoonteks ja need võivad olla eriti kasulikud andmete printimisel.

Nüüd on nende sebrajoonte loomiseks kaks võimalust. Kiireim viis on muuta tabeliandmed Exceli tabeliks. See rakendas alternatiivsetele ridadele automaatselt värvi. Selle kohta saate rohkem lugeda siit.

Teine võimalus on tingimusvormingu kasutamine.

Oletame, et teil on allpool näidatud andmekogum:

Siin on sammud alternatiivsete ridade esiletõstmiseks, kasutades Excelis tingimusvormingut.

• Valige andmekogum. Ülaltoodud näites valige A2: C13 (mis välistab päise). Kui soovite lisada ka päise, valige kogu andmekogum.
• Avage dialoogiboks Tingimuslik vormindamine (Kodu-> Tingimuslik vormindamine-> Uus reegel) [Klaviatuuri otsetee - Alt + O + D].
• Valige dialoogiboksis dialoogiboks „Kasutage valemit vormindatavate lahtrite määramiseks”.
• Sisestage jaotise „Reegli kirjelduse muutmine” väljale järgmine valem:
  = ISODD (ROW ())

• Ülaltoodud valem kontrollib kõiki lahtreid ja kui lahtri ROW number on paaritu, tagastab see tõe. Määratud tingimusvorming rakendatakse kõigile lahtritele, mis tagastavad tõde.
• Määrake vorming, mida soovite tühjadele või vigadega lahtritele rakendada. Selleks klõpsake nuppu Vorminda. See avab dialoogiboksi „Lahtrite vormindamine”, kus saate vormingu määrata.
• Klõpsake nuppu OK.

See on kõik! Andmekogumi alternatiivsed read tõstetakse esile.

Sama tehnikat saate kasutada paljudel juhtudel. Kõik, mida peate tegema, on kasutada tingimusvormingus vastavat valemit. siin on mõned näidised:

• Tõstke esile alternatiivsed paarisarvad: = ISEVEN (ROW ())
• Tõstke esile alternatiivsed lisaridad: = ISODD (ROW ())
• Tõstke esile iga kolmas rida: = MOD (ROW (), 3) = 0

7. Otsige ja tõstke esile andmeid tingimusvormingu abil

See on tingimusvormingu natuke arenenud kasutamine. See näeks välja nagu Exceli rokkstaar.

Oletame, et teil on allpool näidatud andmekogum koos toodete nime, müügiesindaja ja geograafiaga. Idee on sisestada lahtrisse C2 string ja kui see sobib mis tahes lahtri (te) andmetega, peaks see olema esile tõstetud. Midagi, nagu allpool näidatud:

Selle otsingu- ja esiletõstmisfunktsiooni loomiseks toimige järgmiselt.

• Valige andmekogum.
• Avage Avaleht -> Tingimuslik vormindamine -> Uus reegel (Kiirklahv - Alt + O + D).
• Valige dialoogiboksis Uus vormindamise reegel suvand „Kasutage valemit vormindatavate lahtrite määramiseks”.
• Sisestage jaotise „Reegli kirjelduse muutmine” väljale järgmine valem:
  = JA ($ C $ 2 "", $ C $ 2 = B5)
• Määrake vorming, mida soovite tühjadele või vigadega lahtritele rakendada. Selleks klõpsake nuppu Vorminda. See avab dialoogiboksi „Lahtrite vormindamine”, kus saate vormingu määrata.
• Klõpsake nuppu OK.

See on kõik! Nüüd, kui sisestate lahtrisse C2 midagi ja vajutate sisestusklahvi, tõstab see esile kõik sobivad lahtrid.

Kuidas see töötab?

Tingimusvormingus kasutatav valem hindab kõiki andmestiku lahtreid. Oletame, et sisestate lahtrisse C2 Jaapani. Nüüd hindaks Excel iga lahtri valemit.

Valem tagastab lahtri väärtuse TRUE, kui kaks tingimust on täidetud:

• Lahter C2 pole tühi.
• Lahtri C2 sisu vastab täpselt andmestiku lahtri sisule.

Seega tõstetakse esile kõik lahtrid, mis sisaldavad teksti Jaapan.

Laadige alla näidisfail

Sama loogika abil saate luua variatsioone, näiteks:

• Tõstke lahtri asemel esile kogu rida.
• Tõstke esile isegi osalise vaste korral.
• Sisestage tippimisel lahtrid/read (dünaamiline) [Teile meeldib see trikk :)].

Kuidas eemaldada Excelis tingimusvorming

Pärast rakendamist jääb tingimusvorming kehtima, kui te seda käsitsi ei eemalda. Parima tava kohaselt hoidke tingimusvormingut ainult nendes lahtrites, kus seda vajate.

Kuna see on kõikuv, võib see põhjustada aeglase Exceli töövihiku.

Tingimusvormingu eemaldamiseks tehke järgmist.

• Valige lahtrid, millest soovite tingimusvormingu eemaldada.
• Avage Avaleht -> Tingimuslik vormindamine -> Tühjenda reeglid -> Tühjendage valitud lahtrite reeglid.
  • Kui soovite tingimusvormingu kogu töölehelt eemaldada, valige Kustuta reeglid kogu lehelt.

Oluline teave Excelis tingimusliku vormindamise kohta

• Tingimuslik vormindamine volatiilis. See võib põhjustada aeglase töövihiku. Kasutage seda ainult vajadusel.
• Tingimusvormingut sisaldavate lahtrite kopeerimisel kopeeritakse ka tingimusvorming.
• Kui rakendate samale lahtrikomplektile mitu reeglit, jäävad kõik reeglid aktiivseks. Kattuvuse korral eelistatakse viimati rakendatud reeglit. Saate aga järjekorda muuta, muutes dialoogiboksi Halda reegleid järjekorda.