Feltételes formázás Excelben – részletesen példákkal

Ha vizuálisan kell bemutatnia az információkat, akkor nagyon kényelmes ezt feltételes formázással megtenni. A szolgáltatást igénybe vevő felhasználó rengeteg energiát és időt takarít meg. A szükséges információk megszerzéséhez elegendő a fájl gyors megtekintése.

Hogyan készítsünk feltételes formázást Excelben

A „Feltételes formázást” a szalag legelső lapján találja meg a „Stílusok” szakaszban. 

Ezután meg kell találnia a nyíl ikont egy kicsit jobbra a szemével, és mozgassa rá a kurzort. Ezután megnyílnak a beállítások, ahol rugalmasan konfigurálhatja az összes szükséges paramétert. 

Ezután ki kell választania a megfelelő operátort, hogy összehasonlítsa a kívánt változót egy számmal. Négy összehasonlítási operátor létezik – nagyobb, mint, kisebb, egyenlő és között. A szabályok menüben vannak felsorolva. 

Ezután megadtunk egy számsort az A1:A11 tartományban.

Formázzunk megfelelő adathalmazt. Ezután megnyitjuk a „Feltételes formázás” beállítások menüt és beállítjuk a szükséges adatkiválasztási feltételeket. Nálunk például a „Tovább” kritériumot választjuk. 

Megnyílik egy ablak a beállításokkal. Az ablak bal oldalán van egy mező, amelyben meg kell adni a 15-ös számot. A jobb oldalon az információ kiemelésének módja látható, feltéve, hogy az megfelel a korábban megadott kritériumnak. Az eredmény azonnal megjelenik.

Ezután fejezze be a konfigurációt az OK gombbal.

Mire való a feltételes formázás?

Bizonyos mértékig azt mondhatjuk, hogy ez egy olyan eszköz, amelynek elsajátítása után az Excellel való munka soha nem lesz ugyanolyan. Ez azért van így, mert sokkal könnyebbé teszi az életet. Ahelyett, hogy minden alkalommal manuálisan beállítaná egy adott feltételnek megfelelő cella formázását, és saját maga is megpróbálná ellenőrizni, hogy megfelel-e ennek a feltételnek, csak egyszer kell megadnia a beállításokat, majd az Excel mindent megtesz.

Például beállíthatja, hogy minden 100-nál nagyobb számot tartalmazó cella pirosra váltson. Vagy határozza meg, hogy hány nap van még hátra a következő fizetésig, majd színezze zöldre azokat a cellákat, amelyekben még nincs elég messze a határidő. 

Vegyünk egy példát.

Ez a táblázat a raktáron lévő készletet mutatja. Oszlopai vannak. termékként, átlagos eladások (heti egységben mérve), raktárkészlet és hány hét van hátra ebből a termékből.

A beszerzési menedzser feladata továbbá azon pozíciók meghatározása, amelyek utánpótlása szükséges. Ehhez meg kell nézni balról a negyedik oszlopot, amely heti bontásban rögzíti az árukészletet.

Tegyük fel, hogy a pánik okának meghatározásának kritériuma kevesebb, mint 3 hét leltár. Ez azt jelzi, hogy megrendelést kell készítenünk. Ha az árukészlet kevesebb, mint két hét, akkor ez azt jelzi, hogy sürgősen meg kell adni a rendelést. Ha a táblázat nagyon sok pozíciót tartalmaz, akkor a hátralévő hét kézi ellenőrzése meglehetősen problémás. Még ha használod is a keresőt. És most lássuk, hogyan is néz ki a táblázat, amely pirossal kiemeli a szűkös árukat.

És valóban, sokkal könnyebb navigálni. 

Igaz, ez a példa oktatási jellegű, ami a valós képhez képest némileg leegyszerűsített. És az ilyen táblázatok rendszeres használatával megspórolt másodpercek és percek órákká alakulnak. Most elég csak ránézni a táblázatra, hogy megértsük, melyik áruk hiányoznak, és ne töltsünk órákat az egyes cellák elemzésével (ha több ezer ilyen árucikk van).

Ha vannak „sárga” áruk, akkor el kell kezdeni a vásárlást. Ha a megfelelő pozíció piros, akkor azonnal meg kell tennie. 

Egy másik cella értéke szerint

Most nézzük meg a következő gyakorlati példát.

Tegyük fel, hogy van egy ilyen táblázatunk, és azzal a feladattal állunk szemben, hogy kiemeljünk bizonyos értékeket tartalmazó sorokat. 

Tehát, ha a projekt még végrehajtás alatt van (azaz még nem fejeződött be, ezért „P” betűvel van jelölve), akkor a hátterét pirosra kell festenünk. A befejezett projektek zölddel vannak jelölve.

Ebben a helyzetben a műveleteink sorrendje a következő lesz:

1. Válasszon ki egy értéktartományt. 
2. Kattintson a „Feltételes formázás” – „Szabály létrehozása” gombra.
3. Felhívjuk figyelmét, hogy esetünkben a képletet szabály formájában kell alkalmazni. Ezután a függvényt használjuk IFaz egyező vonalak kiemelésére. 

Ezután töltse ki a sorokat a képen látható módon.

Fontos! A karakterláncokra feltétlenül hivatkozni kell. Ha cellára hivatkozunk, akkor ebben az esetben kevert (oszloprögzítéssel).

Hasonlóképpen szabály jön létre azokhoz a munkaprojektekhez, amelyek még nem fejeződtek be. 

Így néznek ki kritériumaink.

És végül ennek eredményeként egy ilyen adatbázisunk van.

Több feltétel

Az érthetőség kedvéért vegyük a táblázatból az A1:A11 tartományt annak bemutatására, hogy egy valós gyakorlati példában hogyan lehet többféle feltételt felhasználni a cellaformázás meghatározására.

Maguk a feltételek a következők: ha a cellában lévő szám meghaladja a 6-os értéket, akkor azt pirossal kiemeljük. Ha zöld, akkor a színe zöld. És végül a legnagyobb számok, több mint 20, sárga színnel lesznek kiemelve. 

A feltételes formázás többféle szabály szerint hajtható végre.

1. 1. módszer. Kiválasztjuk a tartományunkat, majd a feltételes formázási beállítások menüben válasszunk egy ilyen cellakiválasztási szabályt, mint a „Tovább”. A bal oldalon a 6-os szám, a jobb oldalon pedig a formázás van beállítva. Esetünkben a kitöltésnek pirosnak kell lennie. Ezt követően a ciklus kétszer megismétlődik, de más paraméterek már be vannak állítva – több mint 10 és zöld szín, illetve több mint 20 és sárga szín. Ilyen eredményt fogsz kapni.
   

   

2. 2. módszer. Lépjen az Excel „Feltételes formázás” eszközének fő beállítások menüjébe. Itt találjuk a „Szabály létrehozása” menüt, és kattintson a bal gombbal erre az elemre.
   

   

    Ezután válassza ki a „Képlet használata…” elemet (az ábrán piros kerettel kiemelve), és állítsa be az első feltételt. Ezt követően kattintson az OK gombra. Ezután a ciklus megismétlődik a következő feltételekhez a fent leírt módon, de a képletet használjuk.

   

Ezt fontos figyelembe venni. Példánkban néhány cella egyszerre több kritériumnak is megfelel. Az Excel a következőképpen oldja fel ezt az ütközést: először a fenti szabály kerül alkalmazásra. 

És itt van egy képernyőkép a jobb megértés érdekében.

Például van a 24-es számunk. Mindhárom feltételnek egyszerre megfelel. Ebben az esetben az első feltételnek megfelelő kitöltés lesz, annak ellenére, hogy inkább a harmadik alá esik. Ezért ha fontos, hogy a 20 feletti számokat sárgával töltsük ki, akkor a harmadik feltételt kell az első helyre tenni. 

Feltételes dátum formázás

Most nézzük meg, hogyan működik a feltételes formázás a dátumokkal. Nagyon klassz kínálatunk van.

Ebben az esetben be kell állítania egy feltételes formázási szabályt, például „Dátum”.

Ezt követően megnyílik egy párbeszédpanel a feltételek teljes készletével. Ezen a képernyőképen részletesen megismerkedhet velük (a képernyő bal oldalán lista formájában vannak felsorolva).

Csak ki kell választanunk a megfelelőt, és rá kell kattintanunk az „OK” gombra.

Azt látjuk, hogy azok a dátumok, amelyek a tartomány kihúzásának időpontjában az utolsó hétre vonatkoznak, pirossal vannak kiemelve. 

Képletek használata

A szabványos feltételes formázási szabályok készlete meglehetősen nagy. De a helyzetek különbözőek, és előfordulhat, hogy a standard lista nem elég. Ebben az esetben létrehozhat saját formázási szabályt egy képlet segítségével. 

Ehhez kattintson a „Szabály létrehozása” gombra a feltételes formázás menüben, majd válassza ki a képernyőképen megjelenő elemet.

Sorok (cellaérték szerint)

Tegyük fel, hogy ki kell jelölnünk azt a sort, amely egy bizonyos értékű cellát tartalmaz. Ebben az esetben ugyanazt a műveletsort kell végrehajtanunk, mint fent, de karakterláncokra. Vagyis kiválaszthatja, hogy a feltételes formázás melyik tartományra vonatkozzon. 

Hogyan hozzunk létre szabályt

Szabály létrehozásához a „Feltételes formázás” szakasz megfelelő beállítását kell használnia. Nézzünk meg egy konkrét műveletsort. 

Először meg kell találnia a „Feltételes formázás” elemet a „Főoldal” lap szalagján. Felismerhető a jellegzetes sokszínű, különböző méretű cellákról, amelyek mellett egy áthúzott egyenlőségjellel ellátott kép látható. 

Van egy „Szabály létrehozása” gomb. A „Szabályok kezelése” gombra kattintva is megnyílik egy párbeszédpanel, amelyen keresztül a bal felső sarokban található megfelelő gombra kattintva szabályt is létrehozhat. 

Ebben az ablakban láthatja a már alkalmazott szabályokat is.

Cellakiválasztási szabályok

Oké, sokat beszélünk a szabályok létrehozásáról, de mi az? A cellakiválasztási szabály azokra a szolgáltatásokra vonatkozik, amelyeket a program figyelembe vesz a hozzájuk illeszkedő cellák formázásának eldöntéséhez. Például a feltételek lehetnek nagyobbak egy bizonyos számnál, kisebbek egy bizonyos számnál, egy képlet stb. Van belőlük egy egész halmaz. Megismerkedhetsz velük és gyakorolhatod a használatukat „a homokozóban”. 

Az összes cella formázása az értékek alapján

Egy személy önállóan beállíthatja a tartományt: egy egész sort, a teljes dokumentumot vagy egyetlen cellát. A program csak egy cella értékére fókuszálhat. Ez rugalmasabbá teszi a folyamatot. 

Csak egyedi vagy ismétlődő cellákat formázz

Számos szabály van közvetlenül bekötve a programba. Ezekkel a „Feltételes formázás” menüpontban ismerkedhet meg. Különösen annak érdekében, hogy a formázás kizárólag az ismétlődő értékekre vagy csak az egyediekre vonatkozzon, van egy speciális lehetőség - „Értékek megkettőzése”.

Ha ezt az elemet választja, megnyílik egy beállításokat tartalmazó ablak, ahol az ellenkező opciót is választhatja – csak egyedi értékek kiválasztása. 

Formátum értékek az átlag alatti és feletti tartományban

Az átlag alatti vagy feletti értékek formázásához ugyanabban a menüben van egy speciális lehetőség is. De ki kell választania egy másik almenüt - „Az első és az utolsó érték kiválasztásának szabályai”. 

Csak az első és az utolsó érték formázása

Ugyanebben az almenüben csak az első és az utolsó értékeket lehet kiemelni speciális színnel, betűtípussal és egyéb formázási módszerekkel. A szabvány szerint lehetőség van az első és az utolsó tíz elem kiválasztására, valamint az ebbe a tartományba tartozó cellák teljes számának 10%-ára. De a felhasználó önállóan meghatározhatja, hogy hány cellát kell kiválasztania.

Csak meghatározott tartalmú cellák formázása

Ha csak meghatározott tartalmú cellákat szeretne formázni, ki kell választania az Egyenlő vagy a szöveget tartalmaz formázási szabályt. A különbség közöttük az, hogy az első esetben a karakterláncnak teljesen, a másodikban pedig csak részben kell egyeznie a kritériummal. 

Amint láthatja, a feltételes formázás az Excel program többfunkciós funkciója, amely számos előnnyel jár annak, aki elsajátította. Tudjuk, hogy első pillantásra mindez bonyolultnak tűnhet. Valójában azonban azonnal megfogják a kezet, amint úgy tűnik, hogy egy bizonyos szövegrészt pirossal kiemel (vagy bármilyen más módon formázza), annak tartalma alapján vagy más kritériumnak megfelelően. Ez a funkció az Excel ismeretek alapkészletében szerepel, enélkül még az amatőr munkavégzés sem lehetséges táblázatokkal, adatbázisokkal.