Ehdollisen muotoilun kaavojen käyttäminen Excelissä

Ehdollisen muotoilun lisääminen Exceliin antaa mahdollisuuden soveltaa erilaisia ​​muotoiluvaihtoehtoja soluun tai solualueelle, jotka täyttävät määrätyt ehdot. Muokkausvaihtoehtoja sovelletaan vain silloin, kun valitut solut täyttävät nämä asetetut ehdot.

Sovellettavia muotoiluvaihtoehtoja ovat fontti- ja taustavärien muutokset, fonttityylit, solurajat ja lisäämällä numeromuotoja tietoihin.

Excelillä on ollut useita sisäänrakennettuja vaihtoehtoja yleisesti käytetyille olosuhteille, kuten etsimällä numeroita, jotka ovat suurempia tai pienempiä kuin tietyn arvon tai löytää numerot, jotka ovat keskimääräisen arvon ylä- tai alapuolella.

Näiden ennalta asetettujen vaihtoehtojen lisäksi on myös mahdollista luoda mukautettuja ehdollisia muotoilusääntöjä Excel-kaavojen avulla testaamaan käyttäjän määrittämiä ehtoja.

01/04

Sovelletaan useita sääntöjä Excelissä

Useita sääntöjä voidaan soveltaa samoihin tietoihin eri olosuhteiden testaamiseksi. Esimerkiksi budjettitietoihin voi olla asetettuja ehtoja, jotka koskevat muotoilumuutoksia, kun käytetään tiettyjä tasoja, kuten 50%, 75% ja 100% kokonaistalousarviosta.

Tällaisissa olosuhteissa Excel määrittää ensin, ovatko erilaiset säännöt ristiriidassa, ja jos näin on, ohjelma noudattaa määritettyä ensisijaisuusjärjestystä sen määrittämiseksi, mikä ehdollinen muotoilusääntö sovelletaan tietoihin.

Tietojen löytäminen ylittää 25% ja 50% lisäykset

Seuraavassa esimerkissä käytetään kahta mukautettua ehdollista muotoilusääntöä solut B2 että B5.

• Ensimmäinen sääntö tarkistaa, onko tiedot solut A2 että A5 on suurempi kuin vastaava arvo B2 että B5 yli 25 prosenttia.
• Toinen sääntö tarkistaa, onko samat tiedot A2: A5 ylittää vastaavan arvon B2: B5 yli 50 prosenttia.

Kuten yllä olevasta kuvasta voidaan nähdä, jos jompikumpi edellä mainituista ehdoista on totta, solun tai solujen taustasivu alueella B1: B4 tulee muuttumaan.

• Jos ero on suurempi kuin 25%, solun taustaväri muuttuu vihreäksi.
• Jos ero on suurempi kuin 50%, solun taustaväri muuttuu punaiseksi.

Tämän tehtävän suorittamiseen käytetyt säännöt syötetään ehdollisen muotoilun avulla Uusi muotoilusääntö valintaikkuna. Aloita syöttämällä näyte-tieto soluihin A1 - C5 edellä olevan kuvan mukaisesti

Vuonna kolmas vaihe oppaasta lisää kaavoja soluihin C2: C4 jotka osoittavat tarkan prosentuaalisen eron solujen arvojen välillä A2: A5 ja B2: B5 jotta voidaan tarkistaa ehdollisen muotoilusäännön tarkkuus.

02/04

Ehdollisen muotoilusäännön asettaminen

Kuten mainittiin, ehdolliset muotoilusäännöt, jotka tarkistavat nämä kaksi ehtoa, syötetään ehdollisen muotoilun avulla Uusi muotoilusääntö valintaikkuna. Aloitetaan asettamalla mukautettu ehdollinen muotoilumme:

Ehdollinen muotoilu yli 25 prosentin kasvun löytämiseksi

= (A2-B2) / A2> 25%

1. Kohokohta solut B2 että B5 lomakkeessa.
2. Klikkaa Etusivu-välilehti n nauha.
3. Klikkaa Ehdollinen muotoilu kuvaketta nauha avaamaan pudotusvalikosta.
4. Valita Uusi sääntö avaa Uusi muotoilusääntö valintaikkuna.
5. Vuodesta Tyyli: -valintaruutu, valitse Klassikko vaihtoehto.
6. Valitse toisesta pudotusvalikosta viimeinen vaihtoehtoKäytä kaavaa määrittääksesi minkä solujen muotoon.
7. Kirjoita kaava edellä annettuun tilaan.
8. Klikkaa Muotoile käyttäen: avattava ja valitse sitten Vaalea punainen täyttö tummalla tekstillä vaihtoehto.
9. Klikkaus kunnossa sulje valintaikkunat ja palaa laskentataulukkoon.
10. Tässä vaiheessa solut B3 ja B5 pitäisi olla vihreä.

Ehdollinen muotoilu löytää suurempi kuin 50 prosentin nousu

= (A2-B2) / A2> 50%

Kohokohta solut B2 että B5 lomakkeessa.

1. Klikkaa Etusivu-välilehti n nauha.
2. Klikkaa Ehdollinen muotoilu kuvaketta nauha avaamaan pudotusvalikosta.
3. Valita Uusi sääntö avaa Uusi muotoilusääntö valintaikkuna.
4. Vuodesta Tyyli: -valintaruutu, valitse Klassikko vaihtoehto.
5. Valitse toisesta pudotusvalikosta viimeinen vaihtoehtoKäytä kaavaa määrittääksesi minkä solujen muotoon.
6. Kirjoita kaava edellä annettuun tilaan.
7. Klikkaa Muotoile käyttäen: avattava ja valitse sitten Vaaleanvihreä Täytä tummanvihreä teksti vaihtoehto.
8. Klikkaus kunnossa sulje valintaikkunat ja palaa laskentataulukkoon.

Taustan väri solu B3 pitäisi edelleen olla vihreä, mikä osoittaa, että prosentuaalinen ero lukujen välillä solut A3 ja B3 on yli 25 prosenttia, mutta pienempi tai yhtä suuri kuin 50 prosenttia. Taustan väri solu B5 pitäisi muuttua punaiseksi osoittaen, että prosentuaalinen ero lukujen välillä solut A5 ja B5 on yli 50 prosenttia.

03/04

Ehdollisten muotoilusääntöjen tarkistaminen

Voit tarkistaa, että ehdolliset muotoilusäännöt ovat oikein, jotta voimme syöttää kaavoja soluihin C2: C5 joka laskee tarkat prosentuaaliset erot alueiden numeroiden välillä A2: A5 ja B2: B5.

= (A2-B2) / A2

1. Klikkaa solu C2 jotta se olisi aktiivinen solu.
2. Kirjoita edellä oleva kaava ja paina Tulla sisään näppäintä.
3. Vastauksen pitäisi olla 10% solu C2, mikä osoittaa, että numero on solu A2 on 10% suurempi kuin vuonna 2005 solu B2.
4. Muotoilun muuttaminen voi olla tarpeen solu C2 jotta vastaus voidaan näyttää prosentteina.
5. Käytä täyttä kahva kopioida kaava solu C2 että solut C3 että C5.
6. Vastaukset solut C3 että C5 tulisi olla 30%, 25% ja 60%.

Näissä soluissa olevat vastaukset osoittavat, että luodut ehdolliset muotoilusäännöt ovat oikeita, koska ero on solut A3 ja B3 on yli 25 prosenttia ja ero solut A5 ja B5 on yli 50 prosenttia.

Solu B4 ei muuttanut väriä, koska ero oli soluja A4 ja B4 on 25 prosenttia, ja ehdollisen muotoilusäännön mukaan prosenttiosuus yli 25 prosenttia oli tarpeen, jotta taustaväri muuttuisi vihreiksi.

04/04

Etuoikeusjärjestys ehdolliselle muotoilulle

Kun samaan tietoluokkaan sovelletaan useita sääntöjä, Excel määrittää ensin, ovatko säännöt ristiriidassa. Ristiriitaisia ​​sääntöjä ovat ne, joissa kunkin säännön valittuja muotoiluvaihtoehtoja ei voida soveltaa samaan dataan.

Tässä opetusohjelmassa käytetyllä esimerkillä säännöt ovat ristiriidassa, koska molemmilla säännöt käyttävät samaa muotoilutoimintoa - taustan solun värin muuttamista.

Tilanteessa, jossa toinen sääntö on tosi (arvo on yli 50 prosenttia kahdesta solusta), myös ensimmäinen sääntö (arvoero on yli 25 prosenttia) on myös totta.

Excelin etuoikeusjärjestys

Koska solussa ei voi olla sekä punaista että vihreää taustaa samanaikaisesti, Excelin on tiedettävä, mitä ehdollista muotoilusääntöä sen pitäisi soveltaa.

Järjestelmä, jota sovelletaan, määräytyy Excelin etusijajärjestyksen mukaan, jonka mukaan sääntö, joka on korkeampi Ehdollisen muotoilun sääntöjen hallinta -valintaikkunassa olevassa luettelossa, on etusija.

Kuten edellä olevassa kuvassa näkyy, tässä opetusohjelmassa käytetty toinen sääntö on korkeampi luettelossa, ja sen vuoksi sillä on etusija ensimmäisen säännön yläpuolella. Tämän seurauksena solun B5 taustaväri muuttuu punaiseksi.

Oletusarvon mukaan uudet säännöt lisätään luettelon yläosaan ja niillä on siten suurempi etusija. Jos haluat muuttaa oletusjärjestystä, käytä Ylös ja alas nuolinäppäimiä yllä olevan kuvan mukaisesti.

Sovellettaessa ristiriitaisia ​​sääntöjä

Jos kahden tai useamman ehdollisen muotoilusäännön kanssa ei ole ristiriitaa, molempia sovelletaan, kun sääntöjen jokainen sääntö on testaus.

Jos esimerkissämme oleva ensimmäinen ehdollinen muotoilusääntö alustasi solualueen B2: B5 vihreän reunuksen sijaan vihreä taustaväri, kaksi ehdollista muotoilusääntöä eivät ristiriidassa, koska molemmat muodot voidaan soveltaa häiritsemättä toista.

Tuloksena, solu B5 olisi sekä vihreää rajaa että punaista taustaväriä, koska numerot solut A5 ja B5 on suurempi kuin sekä 25 että 50 prosenttia.

Ehdollinen muotoilu vs. säännöllinen muotoilu

Ehdollisen muotoilusäännön ja manuaalisesti sovellettujen muotoiluvaihtoehtojen välisissä ristiriitoissa ehdollinen muotoilu-sääntö on aina etusijalla, eikä sitä sovelleta manuaalisesti lisättyjen muotoiluvaihtoehtojen sijaan.

Jos aluksi sovellettiin keltaista taustaväriä solut B2 että B5 Esimerkissä, kun ehdolliset muotoilusäännöt lisättiin, vain solut B2 ja B4 jäisi keltaiseksi. Koska ehdolliset muotoilusäännöt koskevat soluja B3 ja B5, niiden taustavärit muuttuisivat keltaisesta vihreäksi ja punaiseksi vastaavasti.