A feltételes formázás hozzáadása az Excel programban lehetővé teszi, hogy különböző formázási beállításokat alkalmazzon egy olyan cellára vagy cellatartományra , amely megfelel bizonyos feltételeknek.
A formázási opciók csak akkor alkalmazhatók, ha a kijelölt cellák megfelelnek ezeknek a feltételeknek.
Az alkalmazható formázási lehetőségek közé tartozik a betűtípus és a háttérszín módosítása, a betűstílusok, a cellák határai és a szám formázása az adatokhoz.
Az Excel 2007 óta az Excel számos beépített opciót tartalmaz a gyakran használt feltételekhez, például olyan számok megtalálásához, amelyek nagyobbak vagy annál kisebbek, mint egy bizonyos érték, vagy olyan számok megtalálása, amelyek az átlagérték fölött vagy alatt vannak.
Ezen előre beállított opciók mellett egyedi feltételes formázási szabályokat is létrehozhat Excel formátumok segítségével a felhasználó által megadott feltételek teszteléséhez.
Több szabály alkalmazása
Egynél több szabály alkalmazható ugyanazon adatokra a különböző feltételek tesztelésére. Például a költségkeret-adatok olyan feltételekkel rendelkezhetnek, amelyek a formázási módosításokat alkalmazzák, amikor bizonyos szintek - például a teljes költségkeret 50% -a, 75% -a és 100% -a - elköltik.
Ilyen körülmények között az Excel először meghatározza, hogy a különböző szabályok ellentétesek-e, és ha igen, a program követi az előre meghatározott sorrendet annak meghatározásához, hogy mely feltételes formázási szabály kerül alkalmazásra az adatokra.
Példa: A 25% és 50% -ot meghaladó értékek megtalálása feltételes formázással
A következő példában két egyedi feltételes formázási szabályt alkalmaznak a B2 és B5 cellák tartományán.
- az első szabály ellenőrzi, hogy az A2-A5 cellákban lévő adatok több mint 25% -nál nagyobbak-e a B2-B5 megfelelő értéknél.
- a második szabály megvizsgálja, hogy ugyanazok az adatok az A2: A5-ben meghaladják-e a B2: B5 megfelelő értékét több mint 50% -kal.
Amint a fenti képen látható, ha a fenti feltételek egyike igaz, akkor a B1: B4 tartományban lévő sejt vagy sejtek háttérszíne megváltozik.
- ha a különbség nagyobb, mint 25%, a cella háttérszíne kékre változik.
- ha a különbség nagyobb, mint 50%, akkor a cella háttérszíne pirosra változik.
A feladat végrehajtásához alkalmazott szabályok,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%a formázás új formázási szabályának párbeszédpanelével kerül beírásra.
A bemutatóadatok beírása
- Adja meg az adatokat az A1-C5 cellákba a fenti képen látható módon
Megjegyzés: A bemutató 3. lépése formulákat ad hozzá a C2: C4 cellákhoz, amelyek az A2: A5 és B2: B5 cellákban lévő értékek közötti pontos százalékos különbséget mutatják a feltételes formázási szabályok pontosságának ellenőrzésére.
A Condtional Formatting Rules beállítása
Amint említettük, a feltételes formázási szabályok, amelyek a két feltétel ellenőrzését írják be, a feltételes formázás használatával írja be az Új formázási szabály párbeszédpanelt .
A feltételes formázás beállítása 25% -ot meghaladó növekedéshez
- Jelölje ki a B2-B5 cellákat a munkalapon.
- Kattintson a szalag Home lapjára.
- Kattintson a Feltételes Formázás ikonra a szalagon, hogy megnyissa a legördülő menüt.
- Válassza az Új szabály lehetőséget az új formázási szabály párbeszédpanel megnyitásához a fenti képen látható módon.
- A párbeszédpanel legfelső részén kattintson az utolsó lehetőségre: Egy képlet segítségével határozza meg, mely cellákat kell formázni.
- A párbeszédpanel alsó felében kattintson a Formátum értékekre, ahol ez a képlet igaz: sor.
- Írja be a képletet : = (A2-B2) / A2> 25% a megadott helyre
- Kattintson a Format ( Formázás) gombra a Format Cells párbeszédpanel megnyitásához.
- Ebben a párbeszédpanelen kattintson a Kitöltés fülre, és válassza ki a kék kitöltési színt.
- Kattintson kétszer az OK gombra a párbeszédpanelek bezárásához és a munkalaphoz való visszatéréshez.
- Ekkor a B3 és B5 cellák háttérszínének kéknek kell lennie.
A feltételes formázás beállítása 50% -ot meghaladó növekedéshez
- A B2-B5 cellák még mindig kiválasztva, ismételje meg a fenti 1-6. Lépéseket.
- Írja be a képletet: = (A2-B2) / A2> 50% a megadott helyre.
- Kattintson a Format ( Formázás) gombra a Format Cells párbeszédpanel megnyitásához.
- Kattintson a Kitöltés fülre, és válassza ki a piros színt.
- Kattintson kétszer az OK gombra a párbeszédpanelek bezárásához és a munkalaphoz való visszatéréshez.
- A B3 cellák háttérszínének továbbra is kéknek kell lennie, jelezve, hogy az A3 és B3 cellák számának különbsége nagyobb, mint 25%, de 50% vagy annál kevesebb.
- A B5 cellának háttérszíne vörösre változik, jelezve, hogy az A5 és B5 cellák számának százalékos különbsége nagyobb, mint 50%.
Feltételes formázási szabályok ellenőrzése
% Különbség kiszámítása
Annak ellenőrzésére, hogy a feltételes formázási szabályok helyesek-e, képleteket adhatunk a C2: C5 cellákba, amelyek kiszámítják az A2: A5 és B2: B5 tartományok közötti pontos százalékos különbséget.
- A C2 cellára kattintva létrehozhatja az aktív cellát.
- Írja be a képletet = (A2-B2) / A2 és nyomja meg az Enter gombot a billentyűzeten.
- A válasz 10% -nak jelennie kell a C2 cellában, jelezve, hogy az A2 cellában levő szám 10% -kal nagyobb, mint a B2 cellában lévő szám.
- Szükség lehet a formázásra a C2 cellán, hogy a válasz százalékban jelenjen meg.
- Használja a kitöltő fogantyút a C2 cellából a C3-tól a C5-ig terjedő cellákhoz.
- A C3-C5 sejtekre adott válaszok: 30%, 25% és 60%.
- A válaszok ebben a cellában azt mutatják, hogy a létrehozott feltételes formázási szabályok helyesek, mivel az A3 és B3 cellák közötti különbség nagyobb, mint 25%, és az A5 és B5 sejtek közötti különbség nagyobb, mint 50%.
- A B4 cellák nem változtattak színt, mert az A4 és B4 cellák közötti különbség 25%, a feltételes formázási szabály pedig azt írta elő, hogy a háttérszín kékre való átváltásához 25% -ot meghaladó százalék szükséges.
Feltételes formázási szabályok előtagrendje
Konfliktusos feltételes formázási szabályok alkalmazása
Ha több szabályt alkalmaznak ugyanarra az adatkategóriára, az Excel először meghatározza, hogy a szabályok ütköznek-e.
Konfliktusszabályok azok, amelyeknél az egyes szabályoknak a kiválasztott formázási opciói nem alkalmazhatók mind ugyanazon adatokra .
Az útmutatóban használt példában a szabályok ellentmondanak, mivel mindkét szabály ugyanazt a formázási opciót használja - a háttérsejtszín megváltoztatásának módját.
Abban a helyzetben, ahol a második szabály igaz (az értékkülönbség 50% -nál nagyobb, mint két sejt), akkor az első szabály (25% -nál nagyobb értékkülönbség) is igaz.
Az Excel előtagrendje
Mivel egy cella egyszerre nem lehet vörös és kék háttér, az Excelnek tudnia kell, hogy melyik feltételes formázási szabályt kell alkalmazni.
Melyik szabályt kell alkalmazni az Excel sorrendje határozza meg, amely kimondja, hogy a Feltételes Formázási Szabályzatok kezelője párbeszédpanelen lévő listában magasabb szabály precedens.
Amint a fenti képen látható, a második szabály (= (A2-B2) / A2> 50%) magasabb a listán, ezért elsőbbséget élvez az első szabály fölé.
Ennek eredményeként a B5 cellák háttérszíne vörösre változik.
Alapértelmezés szerint új szabályok kerülnek a lista tetejére, és ezért magasabb prioritásúak.
A sorrend sorrendjének megváltoztatásához használja a Fel és Le nyílgombokat a párbeszédpanelen a fenti képen.
Nem összeférhetetlen szabályok alkalmazása
Ha két vagy több feltételes formázási szabály nem ütközik, mindkettő akkor alkalmazható, ha az egyes szabályok tesztelésének feltétele igaz.
Ha a példánkban szereplő első feltételes formázási szabály (= (A2-B2) / A2> 25%) a kék háttérszín helyett kék határon formázta a B2: B5 cellák tartományát , akkor a két feltételes formázási szabály nem ütközne mindkét formátum alkalmazható anélkül, hogy megzavarná a másikat.
Ennek eredményeképpen a B5 cellának mind kék szegélye, mind piros színű háttérszíne lenne, mivel az A5 és B5 cellákban lévő számok közötti különbség nagyobb, mint 25 és 50 százalék.
Feltételes formázás vagy rendes formázás
A feltételes formázási szabályok és a kézzel alkalmazott formázási lehetőségek közötti ütközés esetén a feltételes formázási szabály mindig elsőbbséget élvez, és a kézzel hozzáadott formázási lehetőségek helyett alkalmazásra kerül.
Ha a példa B2-B5 celláiban kezdetben egy sárga háttérszínt alkalmaztak, a feltételes formázási szabályok hozzáadásával csak a B2 és B4 cellák maradtak sárga színűek.
Mivel a feltételes formázási szabályok a B3 és B5 cellákra vonatkoznak, háttérszíneik sárgától kékig és pirosra változhatnak.