Excel

Ehdollinen muotoilu kaavoilla (10 esimerkkiä)

Conditional Formatting With Formulas

Pika -aloitus | Esimerkkejä | Ongelmien karttoittaminen | Koulutus

Ehdollinen muotoilu on loistava tapa visualisoida laskentataulukon tiedot nopeasti. Ehdollisella muotoilulla voit tehdä esimerkiksi seuraavien 30 päivän korostuspäiviä, merkitä tietojen syöttöongelmia, korostaa parhaita asiakkaita sisältäviä rivejä, näyttää päällekkäisyyksiä ja paljon muuta.



Excelissä on suuri määrä esiasetuksia, joiden avulla on helppo luoda uusia sääntöjä ilman kaavoja. Voit kuitenkin luoda sääntöjä myös omilla mukautetuilla kaavoillasi. Käyttämällä omaa kaavaasi otat haltuusi säännön käynnistävän ehdon ja voit soveltaa juuri sitä logiikkaa, jota tarvitset. Kaavat antavat sinulle maksimaalisen tehon ja joustavuuden.

Esimerkiksi käyttämällä 'Equal to' -esiasetusta on helppo korostaa omenaa vastaavat solut.





Mutta entä jos haluat korostaa soluja, jotka vastaavat omenaa, kiiviä tai limeä? Toki voit luoda säännön kullekin arvolle, mutta se on paljon vaivaa. Sen sijaan voit yksinkertaisesti käyttää yhtä sääntöä, joka perustuu kaavaan TAI -toiminto :

Sääntö, jolla korostetaan x, y tai z



Tässä on tämän taulukon alueelle B4: F8 sovelletun säännön tulos:

Ehdollinen muotoilu OR -funktiolla

Tässä on tarkka kaava:

 
= OR (B4='apple',B4='kiwi',B4='lime')

Pika-aloitus

Voit luoda kaavapohjaisen ehdollisen muotoilusäännön neljässä yksinkertaisessa vaiheessa:

1. Valitse solut, jotka haluat muotoilla.

Valitse muotoiltavat solut

2. Luo ehdollinen muotoilusääntö ja valitse Kaava -vaihtoehto

Valitse kaava -vaihtoehto

3. Anna kaava, joka palauttaa TOSI tai EPÄTOSI.

Anna kaava suhteessa aktiiviseen soluun

4. Määritä muotoiluvaihtoehdot ja tallenna sääntö.

Aseta muotoiluvaihtoehdot

The ISODD -toiminto palauttaa vain TOSI parittomille numeroille ja laukaisee säännön:

kuinka käyttää Excel-pivot-taulukoita

ISODD -funktio palauttaa parittomien numeroiden arvon TOSI ja laukaisee säännön

Video: Ehdollisen muotoilun käyttäminen kaavalla

Tarjoamme myös videokoulutus tästä aiheesta .

Kaavan logiikka

Ehdollista muotoilua käyttävien kaavojen on palautettava TOSI tai EPÄTOSI tai vastaavat numerot. Tässä muutamia esimerkkejä:

mikä on 3d-viite excelissä
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Kaikki yllä olevat kaavat palauttavat TOSI tai EPÄTOSI, joten ne toimivat täydellisesti ehdollisen muotoilun laukaisijana.

Kun ehdollista muotoilua sovelletaan solualueeseen, kirjoita soluviittaukset valinnan ensimmäiseen riviin ja sarakkeeseen (eli vasempaan yläkulmaan). Temppu ehdollisten muotoilukaavojen toiminnan ymmärtämiseen on visualisoida sama kaava, jota sovelletaan jokainen solu valinnassa , ja soluviittaukset päivitetään tavalliseen tapaan. Kuvittele, että kirjoitit kaavan valinnan vasempaan yläkulmaan ja kopioit sen jälkeen koko kaavan. Jos kamppailet tämän kanssa, katso kohta Nukkekaavat alla.

Kaavan esimerkkejä

Alla on esimerkkejä mukautetuista kaavoista, joita voit käyttää ehdollisen muotoilun käyttöön. Jotkut näistä esimerkeistä voidaan luoda käyttämällä Excelin sisäänrakennettuja esiasetuksia solujen korostamiseksi, mutta mukautetut kaavat voivat mennä paljon esiasetuksia pidemmälle, kuten alla näet.

Katso myös: Yli 30 ehdollista muotoilukaavaa

Korosta tilauksia Texasista

Jos haluat korostaa rivejä, jotka edustavat tilauksia Texasista (lyhennetty TX), käytä kaavaa, joka lukitsee sarakkeen F viittauksen:

 
=$F5='TX'

Käytä kaavaa korostamaan rivit, joissa tila =

Lisätietoja on tässä artikkelissa: Korosta rivit ehdollisella muotoilulla .

Video: Rivien korostaminen ehdollisella muotoilulla

Korosta seuraavan 30 päivän päivämäärät

Seuraavien 30 päivän aikana esiintyvien päivämäärien korostamiseksi tarvitsemme kaavan, joka (1) varmistaa, että päivämäärät ovat tulevaisuudessa, ja (2) varmistaa, että päivämäärät ovat enintään 30 päivää tästä päivästä. Yksi tapa tehdä tämä on käyttää JA -toiminto yhdessä NOW -toiminto kuten tämä:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

Nykyinen päivämäärä 18.8.2016 ehdollinen muotoilu korostaa päivämäärät seuraavasti:

Ehdollinen muotoilu seuraavien 30 päivän päivämäärien korostamiseksi

The NOW -toiminto palauttaa nykyisen päivämäärän ja kellonajan. Lisätietoja tämän kaavan toiminnasta on tässä artikkelissa: Korosta seuraavan N päivän päivämäärät .

Korosta sarakkeiden erot

Koska kaksi saraketta sisältää samankaltaisia ​​tietoja, voit käyttää ehdollista muotoilua havaitaksesi pienet erot. Alla olevan muotoilun käynnistämiseen käytetty kaava on:

 
=$B4$C4

Ehdollinen muotoilu sarakkeiden vertailuun

Katso myös: tämän kaavan versio, joka käyttää EXACT-funktiota kirjainkoon erottamiseen .

Korosta puuttuvat arvot

Voit korostaa luettelosta arvoja, jotka puuttuvat toisesta, käyttämällä kaavaa, joka perustuu COUNTIF -toiminto :

 
= COUNTIF (list,B5)=0

Korosta puuttuvat arvot ehdollisella muotoilulla

Tämä kaava yksinkertaisesti tarkistaa jokaisen arvon Luettelo A suhteessa nimettyjen alueiden luettelon arvoihin (D5: D10). Kun määrä on nolla, kaava palauttaa arvon TOSI ja laukaisee säännön, joka korostaa arvot Luettelo A joista puuttuu Lista B. .

Video: Kuinka löytää puuttuvat arvot COUNTIF: n avulla

Korosta kiinteistöjä, joissa on yli 3 makuuhuonetta alle 350 000 dollaria

Löydät luettelosta kiinteistöt, joissa on vähintään 3 makuuhuonetta mutta jotka ovat alle 300 000 dollaria, käyttämällä AND -funktioon perustuvaa kaavaa:

 
= AND ($C5<350000,$D5>=3)

Dollarimerkit ($) lukitsevat viittauksen sarakkeisiin C ja D ja JA -toiminto käytetään varmistamaan, että molemmat ehdot ovat TOSI. Riveillä, joilla AND -funktio palauttaa TOSI, käytetään ehdollista muotoilua:

Ehdollinen muotoilu korostaaksesi kiinteistötiedot

Korosta huippuarvot (dynaaminen esimerkki)

Vaikka Excelissä on esiasetuksia 'huippuarvoille', tämä esimerkki näyttää, miten sama asia tehdään kaavan kanssa ja miten kaavat voivat olla joustavampia. Käyttämällä kaavaa voimme tehdä laskentataulukosta interaktiivisen - kun F2: n arvo päivitetään, sääntö reagoi välittömästi ja korostaa uusia arvoja.

Dynaaminen ehdollinen muotoilu huippuarvoille

Tätä sääntöä varten käytetty kaava on:

Pidä ylärivi näkyvissä Excelissä
 
=B4>= LARGE (data,input)

Missä 'data' on nimetty alue B4: G11 ja 'input' on nimetty alue F2. Tällä sivulla on yksityiskohdat ja täydellinen selitys .

Gantt -kaaviot

Usko tai älä, voit jopa käyttää kaavoja yksinkertaisten Gantt -kaavioiden luomiseen ehdollisella muotoilulla seuraavasti:

Ehdollisen muotoilun käyttäminen Gantt -kaavion luomiseen

Tämä laskentataulukko käyttää kahta sääntöä, toinen palkeille ja toinen viikonlopun varjostukselle:

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

Tässä artikkelissa selitetään palkkien kaava ja tässä artikkelissa selitetään kaava viikonlopun varjostusta varten .

Yksinkertainen hakukenttä

Yksi hieno temppu, jonka voit tehdä ehdollisella muotoilulla, on rakentaa yksinkertainen hakukenttä. Tässä esimerkissä sääntö korostaa sarakkeen B solut, jotka sisältävät soluun F2 kirjoitettua tekstiä:

Ehdollisen muotoilun hakukenttä

Käytetty kaava on:

 
= ISNUMBER ( SEARCH ($F,B2))

Jos haluat lisätietoja ja täydellisen selityksen, katso:

Ongelmien karttoittaminen

Jos ehdollisia muotoilusääntöjä ei voi käynnistää oikein, kaavassa on todennäköisesti ongelma. Varmista ensin, että aloitit kaavan yhtäläisyysmerkillä (=). Jos unohdat tämän vaiheen, Excel muuntaa koko kaavan äänettömästi tekstiksi, mikä tekee siitä hyödyttömän. Voit korjata poistamalla Excelin lainausmerkit molemmilta puolilta ja varmista, että kaava alkaa yhtäsuurilla (=).

Jos kaava on syötetty oikein, mutta se ei käynnistä sääntöä, sinun on ehkä kaivettava hieman syvemmälle. Yleensä, voit tarkistaa kaavan tulokset käyttämällä F9 -näppäintä tai käydä läpi kaavan Evaluate -ominaisuuden avulla. Valitettavasti et voi käyttää näitä työkaluja ehdollisen muotoilun kaavojen kanssa, mutta voit käyttää tekniikkaa nimeltä 'dummy -kaavat'.

Nukkekaavat

Nukkekaavat ovat tapa testata ehdollisia muotoilukaavoja suoraan laskentataulukolla, jotta näet, mitä he todella tekevät. Tämä voi säästää paljon aikaa, kun kamppailet saadaksesi soluviittaukset toimimaan oikein.

Lyhyesti sanottuna, syötät saman kaavan solualueelle, joka vastaa tietojesi muotoa. Näin näet kunkin kaavan palauttamat arvot, ja se on loistava tapa visualisoida ja ymmärtää, miten kaavapohjainen ehdollinen muotoilu toimii. Tarkempaa selitystä varten katso tämä artikkeli .

Tarkista dummy -kaavojen avulla ehdolliset muotoilukaavat

Video: Testaa ehdollinen muotoilu nukkekaavoilla

Rajoitukset

Kaavapohjaiseen ehdolliseen muotoiluun liittyy joitain rajoituksia:

  1. Et voi käyttää kuvakkeita, väriasteikkoja tai tietopalkkeja mukautetulla kaavalla. Voit käyttää vain solujen vakiomuotoilua, mukaan lukien lukumuotoja, fonttia, täyttöväriä ja reunusvaihtoehtoja.
  2. Et voi käyttää tiettyjä kaavarakenteita, kuten liittoja, leikkauksia tai matriisivakioita ehdollisiin muotoiluehtoihin.
  3. Et voi viitata muihin työkirjoihin ehdollisen muotoilukaavan avulla.

Voit joskus kiertää numeroita 2 ja 3. Voit ehkä siirtää kaavan logiikan laskentataulukon soluun ja viitata sen sijaan kyseiseen soluun kaavassa. Jos yrität käyttää matriisivakioita, yritä luoda nimetty alue sen sijaan.

Lisää CF -kaavaresursseja

  • Yli 30 esimerkkiä ehdollisista muotoilukaavoista
  • Videokoulutus käytännön työkirjoilla
Kirjailija Dave Bruns


^