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 :
Tässä on tämän taulukon alueelle B4: F8 sovelletun säännön tulos:
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.
2. Luo ehdollinen muotoilusääntö ja valitse Kaava -vaihtoehto
3. Anna kaava, joka palauttaa TOSI tai EPÄTOSI.
4. Määritä muotoiluvaihtoehdot ja tallenna sääntö.
The ISODD -toiminto palauttaa vain TOSI parittomille numeroille ja laukaisee säännön:
kuinka käyttää Excel-pivot-taulukoita
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 muotoilukaavaaKorosta tilauksia Texasista
Jos haluat korostaa rivejä, jotka edustavat tilauksia Texasista (lyhennetty TX), käytä kaavaa, joka lukitsee sarakkeen F viittauksen:
=$F5='TX'
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:
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 
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
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:
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.
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:
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ä:
Käytetty kaava on:
= ISNUMBER ( SEARCH ($F,B2))
Jos haluat lisätietoja ja täydellisen selityksen, katso:
- Artikla: Tietyn tekstin sisältävien solujen korostaminen
- Artikla: Tietyn tekstin sisältävien rivien korostaminen
- Video: Kuinka rakentaa hakukenttä tietojen korostamiseksi
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 .
Video: Testaa ehdollinen muotoilu nukkekaavoilla
Rajoitukset
Kaavapohjaiseen ehdolliseen muotoiluun liittyy joitain rajoituksia:
- 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.
- Et voi käyttää tiettyjä kaavarakenteita, kuten liittoja, leikkauksia tai matriisivakioita ehdollisiin muotoiluehtoihin.
- 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
