Using Validation With Conditional Formatting

Miksi yhdistää nämä kaksi?

Tietojen validointi määrittää, mitä voidaan syöttää. Ehdollinen muotoilu määrittää, miltä solut näyttävät. Kumpikin on hyödyllinen yksinään — yhdessä ne muodostavat järjestelmän, joka sekä estää virheellisen datan että viestii visuaalisesti datan tilan kaikille taulukkoa tarkasteleville.

Validointisolu, jossa on virheellinen arvo (syötetty ennen sääntöjen käyttöönottoa tai liitetty), ei oletuksena näytä visuaalista varoitusta. Ehdollinen muotoilu paikkaa tämän puutteen — ongelmasolut merkitään värillä, jolloin virheet eivät jää huomaamatta.

Miten ne toimivat yhdessä

Keskeinen oivallus on, että molemmat työkalut voivat käyttää samoja loogisia ehtoja. Jos validointi hylkää yli 30 %:n alennukset, ehdollinen muotoilu voi korostaa kaikki solut, joissa alennus ylittää 30 %, punaisella. Yksi sääntö valvoo, toinen visualisoi.

Ehdollisen muotoilun käyttöönotto

1. Valitse kohdesolut;
2. Siirry kohtaan Aloitus → Ehdollinen muotoilu → Uusi sääntö;
3. Valitse Käytä kaavaa määrittääksesi, mitkä solut muotoillaan;
4. Syötä kaavasi;
5. Aseta muotoilu (täyttöväri, fontin väri, reunus);
6. Napsauta OK.

Esimerkki 1: Virheellisten alennusten korostaminen

Validointisääntö hylkää jo yli 30 %:n alennukset. Mutta entä arvot, jotka pääsivät läpi ennen säännön käyttöönottoa? Ehdollinen muotoilu tunnistaa ne visuaalisesti.

Kohdistettu Alennus % -sarakkeeseen (J):

=J2>30

Muotoilu: punainen täyttö. Kaikki solut, joissa arvo ylittää 30 %, muuttuvat heti punaisiksi.

Esimerkki 2: Loppupäivämäärän merkitseminen ennen aloituspäivämäärää

Validointi estää uudet virheet, mutta olemassa olevat virheet tulee tehdä näkyviksi. Oletetaan, että Start Date on sarakkeessa K ja End Date sarakkeessa L:

Kohdistettu End Date -sarakkeeseen L:

=AND(L2<>"", L2<=K2)

Muotoilu: oranssi täyttö. Tarkistus L2<>"" varmistaa, ettei tyhjiä End Date -kenttiä merkitä turhaan.

Esimerkki 3: Korosta puutteelliset rivit

Rivi, jossa Status on Closed, mutta End Date on tyhjä, on loogisesti puutteellinen. Tee siitä helposti havaittava:

Kohdistetaan Status-sarakkeeseen (M):

=AND(M2="Closed", L2="")

Muotoilu: punainen fontti. Kaikki suljetut tilaukset ilman End Date -arvoa näkyvät heti.

Esimerkki 4: Vihreä kelvolliselle, punainen virheelliselle

Sarakkeessa kuten Customer Email voit käyttää kahta sääntöä liikennevalotehosteen luomiseksi:

• Vihreä täyttö: =ISNUMBER(FIND("@", G2)) — kelvollinen sähköposti;
• Punainen täyttö: =NOT(ISNUMBER(FIND("@", G2))) — virheellinen sähköposti.

Ehdollinen muotoilu arvioi säännöt järjestyksessä — varmista, että tarkempi sääntö on ylempänä prioriteettilistalla (Hallinnoi sääntöjä → käytä nuolinäppäimiä järjestyksen muuttamiseen).

Sääntöjen prioriteetin hallinta

Kun useat ehdollisen muotoilun säännöt koskevat samaa solua, Excel arvioi ne ylhäältä alas ja käyttää ensimmäistä täsmäävää sääntöä. Tämä on tärkeää, kun säännöt menevät päällekkäin.

Prioriteetin hallinta:

1. Siirry kohtaan Aloitus → Ehdollinen muotoilu → Hallitse sääntöjä;
2. Valitse ylhäältä taulukon laajuus;
3. Käytä ylös/alas -nuolia sääntöjen järjestyksen muuttamiseen;
4. Valitse Lopeta jos tosi estääksesi alempien sääntöjen yliajamisen.

Tehtävä

1. Lisää punainen täyttöehdollinen muotoilusääntö Discount % -sarakkeeseen:

   • Kaava: =J2>30

2. Lisää oranssi täyttösääntö End Date -sarakkeeseen:

   • Kaava: =AND(L2<>"", L2<=K2)

3. Lisää punainen fonttisääntö Status -sarakkeeseen:

   • Kaava: =AND(M2="Closed", L2="")

4. Lisää kaksi sääntöä liikennevalomuotoiluun Customer Email -sarakkeeseen:

   • Vihreä täyttö: =ISNUMBER(FIND("@", G2))
   • Punainen täyttö: =NOT(ISNUMBER(FIND("@", G2)))

5. Syötä tarkoituksella virheellisiä arvoja jokaiseen sarakkeeseen ja varmista, että muotoilu aktivoituu oikein;

6. Avaa Manage Rules ja harjoittele sähköpostisääntöjen järjestyksen muuttamista — tarkkaile, miten prioriteetti vaikuttaa siihen, mikä muotoilu voittaa, kun molemmat ehdot voivat toteutua;

7. Lisää rivi, jossa Status on Closed ja End Date puuttuu — varmista, että punainen fontti näkyy Status-solussa.