Feltételes formázás Excelben értékes segédprogramnak tekinthető, amely segíthet vizuálisan javítani az adatok elemzését és megjelenítését a táblázatban azáltal, hogy előre meghatározott szabályokon vagy feltételeken alapuló formázást alkalmaz. Ez a bejegyzés megosztja, hogyan állíthatja be a feltételes formázást az Excelben a dátumokhoz. Részletes példákat is mellékeltünk, amelyeket az útmutató olvasása közben kipróbálhat.
A feltételes formázás beállítása az Excel dátumokhoz programban
A feltételes formázás nemcsak a cellák színkódokkal történő kiemelését segíti elő, hanem a sorokat, oszlopokat és a dátumokból álló cellákat is vizuálisan értelmesnek és vonzónak tekinti.
- Előre meghatározott
- Felhasználó által meghatározott
- Hozzon létre egy egyedi formázási szabályt
- Az ünnepek kiemelése az Excelben
- A dátumok feltételes formázása több feltétel alapján
- A közelgő dátumok és késések kiemelése
Ügyeljen arra, hogy alaposan tanulmányozza át a részleteket az Excelben. Számos lehetőséget kínál, amelyek közül néhány túlmutat ezen útmutató hatókörén.
1] Előre meghatározott feltételes formázási dátumszabályok dátumokkal
A dátumokkal való feltételes formázás előnyös lehet a dátumhoz kapcsolódó információkat tartalmazó adatkészletek kezelésekor. Az Excel csaknem tíz különböző lehetőséget kínál a cellák dátumok alapján történő formázására, az alábbiak szerint:
- Ha olyan cellát szeretne formázni, amelynek adattípusa a dátum, kattintson a gombra Kezdőlap > Feltételes formázás > Cellaszabályok kiemelése > Előfordulási dátum
- A feltételes formázási lehetőségek a következő ablak legördülő menüiből választhatók. Az első legördülő menüből választhatjuk ki a naptartományt, a másodikból pedig a formázási lehetőségeket.
- Kattintson rendben a választott formázási szabályok alkalmazásához.
2] Felhasználó által meghatározott feltételes formázási dátumszabályok dátumokkal
Az előre meghatározott szabályokon kívül az Excel lehetőséget biztosít ezeknek a szabályoknak a felhasználói igények alapján történő testreszabására is. Számos módja van a testreszabások beállításának, az alábbiak szerint:
Egyéni formátum használata
- Nyissa meg a Egy dátum előfordul formázza az ablakot a fent említett lépések végrehajtásával.
- A naptartomány kiválasztása után válassza a lehetőséget Egyedi formátum a formázási beállítások legördülő menüből.
- A Cellák ablak formázása A fentiek miatt felbukkanna, ahol kiválaszthatjuk a színt, a keretet, a betűtípust és az effektusokat, majd az OK gombra kattintva alkalmazhatjuk a változtatásokat.
Hozzon létre új szabályokat
Ez a módszer egy teljesen új szabály létrehozását jelenti a feltételes formázáshoz, ellentétben a fenti módszerrel, ahol néhány előre meghatározott szabály közül választhatunk, és csak egyéni formátumot hozhatunk létre. Az új szabály létrehozása a következőket tartalmazza:
- A cellák kiválasztása, amelyekre a formázást alkalmazni kívánja.
- A itthon Tabulátor, kattintás Feltételes formázás > Új szabály , aminek meg kell nyitnia a Új formázási szabály ablak.
- Az említett ablakon választhatunk a rendelkezésre állók közül Szabálytípusok , majd a szabály leírásának szerkesztése a feltételes formázás feltételeinek beállításához.
- Miután elkészült, a Formátum gombra kattintva az ablak jobb alsó sarkában kiválaszthatja az említett szabály formázását.
- A Cellák formázása A megnyíló ablakban kiválaszthatjuk a betűtípust, a szegélyt, a cella színét, az effektusokat stb., majd az ablakban egyszer kattintsunk az OK gombra, majd a Új formázási szabály A módosítások alkalmazására szolgáló ablak.
Jegyzet: A formázási szabályok használhatók egynél több szabály létrehozására és alkalmazására ugyanarra az adathalmazra különböző feltételek/kritériumok alapján.
Olvas: Az Excel dátumszűrője nem csoportosít hónapok szerint
3] Hogyan készítsünk egyedi formázási szabályt dátumokkal
A fenti példában végignéztük a feltételes formázás új szabályának létrehozásának lépéseit a már megadott kritériumok alapján. Az Excel azt is lehetővé teszi, hogy az említett formázási szabályokat a felhasználó által meghatározott feltételekre alkalmazzuk.
Tegyük fel például, hogy ki kell jelölnünk azokat a cellákat, amelyek dátuma 30 vagy 60 napnál régebbi. Ebben az esetben ugyanez megvalósítható az Excelben elérhető dátumképletek és függvények alkalmazásával is, az alábbiak szerint:
A] Jelölje ki az aktuális és régebbi dátumokat
Alatt Válasszon ki egy új szabályt a Feltételes formázás lehetőségnél válassza a lehetőséget Használjon képletet a formázandó cella meghatározásához , típus:
=$H4=MA() hogy kiemeljük a mai napot
=$H4>MA() hogy kiemeljük a jövő napjait
=$H4
=$H4<=(MA()-30) a 30 napnál régebbi dátumok kiemeléséhez.
=H4 $<=(MA()-60) a 60 napnál régebbi dátumok kiemeléséhez.
A fenti képlet ellenőrzi a kiválasztott dátumtartományt az aktuális dátumhoz, és miután megtalálta az egyezést, módosítsa a háttérszínt Lilára a Formátum opcióban kiválasztott módon. A $ mellett használják H azt jelenti, hogy az oszlop állandó marad, de a sorok változóak.
- Egyszer a Szabály leírása említésre kerül, a betűtípus, a színek és az effektusok a Formátum gombra kattintva választhatók ki.
- Kattintson rendben a Cellák formázása ablakban, majd ugyanez az Új szabály ablakban, hogy a módosítások érvénybe lépjenek.
B] Kiemelés a felhasználó által meghatározott dátumtartomány alapján
Hasonló módszerek alkalmazhatók a felhasználók által megadott dátumtartományok formázására is. Például, ha ki kell jelölnünk azokat a dátumokat, amelyek 30 napnál régebbi, de 45 napnál rövidebbek, a TODAY() függvény használható olyan logikai operátorral, mint például az ÉS, hogy megadja a két feltételt magában foglaló feltételeket:
- Válassza ki azokat a cellákat, amelyekre a formázást alkalmazni kell, és nyissa meg az Új szabály ablakot.
- Ban,-ben Szerkessze a szabály leírását mezőbe írja be az alábbi képletet,
=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))
A fenti képlet a ÉS operátort, hogy sárgával jelölje ki azokat a dátumokat, amelyek 30 napnál régebbi, de 45 napnál nem régebbiek a teljes kijelölésnél, a 2. számú cellától kezdve. H2.
C] Hétvégék kiemelése
Ugyanebben a tartományban a WEEKDAY() formulával a hétvégére eső dátumok is kiemelhetők:
- Válassza ki azokat a cellákat, amelyekre a formázást alkalmazni kell, és nyissa meg az Új szabály ablakot.
- Ban,-ben Szerkessze a szabály leírását mezőbe írja be az alábbi képletet,
=WEEKDAY($H2, 2)>5
A szintaxis a Weekday képlet WEEKDAY(sorszám, [visszaküldési_típus]) alakban ábrázolható, ahol a sorozatszám az ellenőrizni kívánt dátum cellaszáma (ebben az esetben SA1).
A return_types paraméter a hétfő(1)-től vasárnapig(7) kezdődő hét típusát jelöli, a hét kezdőnapját hétfőnek tekintve (1-et jelölve), a hét utolsó napját pedig vasárnapnak (7-es számmal jelölve).
A visszatérési_típusok azonban nem kötelezőek a képletben, és harmadik kapcsos zárójelben vannak ábrázolva. >5 a szombat(6) és vasárnap(7) kiemelésére szolgál a megadott visszatérési típushoz.
D] Sorok kiemelése egy adott dátum alapján
Ez a lehetőség hasznos lehet nagy mennyiségű adat kezelésekor, amikor egy adott dátumot kell kiemelni. Ugyanez érhető el a dátum számértékének meghatározásával vagy a DATEVALUE() funkció. A numerikus értéket először meg kell határozni, hogy egy adott dátumra feltételes formázást alkalmazhasson, mivel az Excel a dátumokat folyamatosan sorszámként tárolja. Egy adott dátum számértékének megkereséséhez,
- Kattintson a jobb gombbal a dátumot tartalmazó cellára, kattintson a gombra Cellák formázása > Szám, majd válassz Tábornok . A cellán egy 5 jegyű számérték jelenik meg, amelyet későbbi hivatkozás céljából feljegyezhet.
- Kattintson a Mégse gombra, miután feljegyezte a számot, mivel nem kell módosítanunk a cella dátumformátumát.
- Kattintson Feltételes formázás>Új szabály> Válasszon ki egy új szabályt > Használjon képletet a formázandó cella meghatározásához és írja be a következő képletet,
=$G5=45249
- Használni a DATEVALUE() függvény, ugyanez megtehető az alábbi képlet beírásával is,
=$G5=DATEVALUE(“11/19/2023”)
A C mellett használt $ azt jelenti, hogy az oszlop állandó marad, de a sorok változóak.
Jegyzet: Ezeknek a dátumoknak a sorozatszáma az Excelben 1-től kezdődik utca 1900. január, mint 1. sorozatszám. Ezért, ha figyelembe vesszük a dátumot 2023. november 19., akkor a számozása 45249 lenne, mivel az 1. után 45248 nap. utca 1900. január.
Olvas: Hogyan lehet a sorozatszámot dátummá konvertálni a Microsoft Excel programban
4] Az ünnepek kiemelése az Excelben
Mivel az ünnepek a demográfiai adatoktól függően változhatnak, az Excel nem rendelkezik beépített funkcióval a munkaszüneti napok kiemelésére. Ezért az ünnepek azonosításához a dátumokat egy másik oszlopban kell rögzítenünk, és alkalmaznunk kell a countif képlet az ünnepi dátumok előfordulásának ellenőrzésére az adatlapon.
Például van néhány dátum a D oszlopban, és az ünnepnapok listája a G oszlopban. A feltételes formázással kiemelhetjük a D oszlopban a PIROS színű ünnepnapokat, ha vannak ilyenek, összehasonlítva azokat a G oszlopban említett ünnepnapokkal. az alábbi lépések segítségével:
- Nyissa meg a Új szabály párbeszédablakot, és válassza ki Használjon képletet a formázandó cellák meghatározásához alatt Válassza ki a Szabály típusát.
- Írja be a képletet =COUNTIF($G:$G,$D3)
- Kattintson Formátum > Kitöltés és válassza ki a színt PIROS a palettáról, és kattintson rá rendben .
A countif() ellenőrzi a G3 – G5 cellában említett dátumok előfordulását a D oszlopban szereplő dátumokkal a D3 cellától kezdve.
5] A dátumok feltételes formázása több feltétel alapján
Tételezzünk fel egy munkalapot, amelyen különféle háztartási cikkekkel kapcsolatos panaszaink vannak. A lap tartalmazza a panaszok benyújtásának dátumait, valamint adott esetben az esedékes és a bezárási dátumokat.
Feltételes formázást használva jelöljük FEHÉRRE a ZÁRVA, PIROS színűre azokat, amelyek már túl vannak, de még nem zárták le, és KÉKRE azokat, amelyek még elérték a határidőt.
A folyamat során két feltételes formázási szabályt kell létrehozni ugyanazokhoz az adatokhoz, egyet azokhoz, amelyek még nem érték el a határidőt, egy másikat pedig azoknak, amelyek túllépték a határidőt. Ehhez
- Nyissa meg a feltételes formázás Új szabály ablakát, és válassza ki Használjon képletet a formázandó cellák meghatározásához alatt Válasszon egy szabálytípust.
- Írja be az alábbi képletet
=AND($I10="",$H10>$K)
- Kattintson Formátum , válassza ki a KÉK színt a palettáról, majd kattintson az OK gombra.
- Hozzon létre egy másikat Új szabály ugyanazzal a szabálytípussal, és írja be az alábbi képletet
=AND($I10="",$H10<$K)
- Kattintson a Formátum gombra, válassza ki a PIROS színt a palettáról, majd kattintson az OK gombra.
Ebben a képletben először ellenőrizzük, hogy a Lezárás dátuma oszlop ÜRES-e vagy sem, hogy megállapítsuk, hogy a panasz még mindig nyitva van. ÉS majd ellenőrizze, hogy a DUE DATE nagyobb-e, mint az aktuális dátum, vagy kisebb-e, hogy meghatározza, közeledik-e a határidő ($H10>$K ) vagy meghaladta ($H10<$K ) . A K6 cellában lévő dátum az aktuális dátumot jelöli.
6] A közelgő dátumok és késések kiemelése
Ahhoz, hogy megértsük, hogyan emelhetjük ki a késéseket a dátumokkal kapcsolatban egy Excel-lapon, nézzünk meg egy példát, ahol van egy listánk a részvényekről a nevükkel és a lejárati dátumukkal. Feltételes formázással, ha ZÖLD színnel kell megjelölnünk a lejárókat és PIROS színnel a már lejártakat,
- Válassza ki az adattartományt, és nyissa meg a Új szabály párbeszédablak.
- Választ Csak azokat a cellákat formázza, amelyek tartalmazzák alatt Válassza ki a Szabály típusát
- Ban ben Szabályleírás szerkesztése , választ Cella értéke , Nagyobb, mint , =Most()+30 a megfelelő legördülő menükben.
- Kattintson Formátum, válassza a ZÖLD lehetőséget a színpalettáról , és kattintson rendben .
- Ismét nyissa meg a Új szabály és ugyanazt válaszd Szabály típusa, mint fent.
- Ban ben Szabályleírás szerkesztése , választ Cella értéke , Kevesebb, mint , =Most() a megfelelő legördülő menükben.
- Kattintson Formátum és válassza ki PIROS a színpalettáról, és kattintson rendben .
Következtetés
alma jegyzetek az ablakokon
Ennek az oktatóanyagnak az a célja, hogy átfogóan lefedje azokat a lehetőségeket és funkciókat, amelyek használhatók az Excel munkalapokon a dátumok alapján történő feltételes formázáshoz. Segíthet megérteni, hogyan kell alkalmazni a formázási szabályokat az adatok megjelenítésének és elemzésének javítására. A feltételes formázás elsajátítása segíthet a trendek azonosításában, a határidők ellenőrzésében és a feladatok prioritásainak meghatározásában, így megalapozott döntéseket hozhat.
Olvas : Hogyan kell jelölje ki a dátumokkal ellátott sorokat az Excel feltételes formázásával
Hogyan hozhatok létre dátumok alapján feltételes formázást az Excelben?
Először válassza ki a dátumcellákat, hogy dátumok alapján feltételes formázást hozzon létre az Excelben. Kattintson a „Kezdőlap” > „Feltételes formázás” > „Új szabály” elemre. Válassza a „Tartalmazó cellák formázása” lehetőséget, majd a szabály alatt válassza a „Dátumok előfordulása” lehetőséget, válassza ki a konkrét dátumfeltételeket, majd a „Formátum” gombra kattintva állítsa be a kívánt formátumot. Jelentkezzen az „OK” gomb megnyomásával. Ez gyorsan kiemeli a legfontosabb dátumokat az adatok között.
Hogyan formázhatom feltételesen a határidőket az Excelben?
Először válassza ki a dátumokat tartalmazó cellákat a határidők feltételes formázásához az Excelben. Ezután kattintson a „Feltételes formázás” lehetőségre a „Kezdőlap” lapon. Válassza az „Új szabály” lehetőséget, válassza a „Csak tartalmazó cellák formázása” lehetőséget, állítsa be a szabályt „Cellaérték” és „kevesebb, mint” értékre, és adja meg a határidőt vagy a képletet. Kattintson a „Formázás” gombra, válassza ki a formázási stílust, és az alkalmazáshoz nyomja meg az „OK” gombot.
