Feltételes formázás Excelben: A végső útmutató példákkal

A feltételes formázás az Excel -táblázatok egyik legegyszerűbb, de legerősebb funkciója.

Ahogy a neve is sugallja, feltételes formázást használhat az Excelben, ha kiemelni szeretné a megadott feltételeknek megfelelő cellákat.

Lehetővé teszi, hogy gyorsan adjunk hozzá vizuális elemzési réteget az adathalmazhoz. Létrehozhat hőtérképeket, növekvő/csökkenő ikonokat, Harvey buborékokat és még sok mást a feltételes formázással Excelben.

Feltételes formázás használata Excelben (példák)

Ebben az oktatóanyagban hét csodálatos példát mutatok be a feltételes formázás Excelben való használatára:

• Az ismétlődések gyors azonosítása az Excel feltételes formázásával.
• Jelölje ki azokat a cellákat, amelyek értéke nagyobb/kevesebb, mint egy adathalmaz száma.
• A felső/alsó 10 (vagy 10%) értékek kiemelése egy adatkészletben.
• Hibák/üres helyek kiemelése a feltételes formázás használatával Excelben.
• Hőtérképek létrehozása feltételes formázással Excelben.
• Feltételes formázással jelölje ki minden n. Sort/oszlopot.
• Keresés és kiemelés az Excel feltételes formázásával.

1. Az ismétlődések gyors azonosítása

Az Excel feltételes formázása használható az ismétlődések azonosítására egy adathalmazban.

Ezt a következőképpen teheti meg:

• Válassza ki azt az adatkészletet, amelyben az ismétlődéseket kiemeli.
• Lépjen a Főoldalra -> Feltételes formázás -> A cellaszabályok kiemelése -> Ismétlődő értékek.
• Az Ismétlődő értékek párbeszédpanelen győződjön meg arról, hogy a Másolat van kiválasztva a bal oldali legördülő menüben. Az alkalmazandó formátumot a jobb oldali legördülő menü segítségével adhatja meg. Van néhány létező formátum, amelyeket használhat, vagy megadhatja saját formátumát az Egyéni formátum opció használatával.
• Kattintson az OK gombra.

Ez azonnal kiemeli az összes cellát, amelynek ismétlődése van a kiválasztott adatkészletben. Az adathalmaz egyetlen oszlopban, több oszlopban vagy nem szomszédos cellatartományban lehet.

Lásd még: A végső útmutató az ismétlődések megkereséséhez és eltávolításához Excelben.

2. Jelölje ki azokat a cellákat, amelyek értéke nagyobb/kevesebb, mint egy szám

Az Excel feltételes formázásával gyorsan kiemelheti a megadott értéknél nagyobb/kisebb értékeket tartalmazó cellákat. Például minden olyan cella kiemelése, amelynek értékesítési értéke kevesebb, mint 100 millió, vagy a cellák kiemelése a túllépési küszöb alatti értékekkel.

Ehhez tegye a következő lépéseket:

• Válassza ki a teljes adatkészletet.
• Lépjen a Főoldalra -> Feltételes formázás -> A cellaszabályok kiemelése -> Nagyobb, mint… / Kevesebb, mint…
• Attól függően, hogy melyik opciót választja (nagyobb vagy kisebb), megnyílik egy párbeszédpanel. Tegyük fel, hogy a „Nagyobb mint” lehetőséget választja. A párbeszédpanelen írja be a számot a bal oldali mezőbe. A cél az, hogy kiemelje azokat a cellákat, amelyek száma ennél nagyobb.

• A jobb oldali legördülő menü segítségével adja meg a feltételnek megfelelő cellákra alkalmazandó formátumot. Van néhány létező formátum, amelyeket használhat, vagy megadhatja saját formátumát az Egyéni formátum opció használatával.
• Kattintson az OK gombra.

Ez azonnal kiemeli az összes adatcellában lévő 5 -nél nagyobb értékű cellát.Megjegyzés: Ha 5 -nél nagyobb értékeket szeretne kiemelni, akkor újra alkalmazza a feltételes formázást az „Egyenlő” kritériumokkal.

Ugyanezt a folyamatot követheti a megadott értéknél kisebb értékű cellák kiemelésére.

3. Felső/alsó 10 kiemelés (vagy 10%)

Az Excel feltételes formázásával gyorsan azonosíthatók a 10 legjobb elem vagy a 10% -os adathalmaz. Ez hasznos lehet olyan helyzetekben, amikor gyorsan szeretné látni a legjobb jelölteket a pontszámok vagy a legjobb ügyleti értékek alapján az értékesítési adatokban.

Hasonlóképpen gyorsan azonosíthatja az adatkészlet alsó 10 elemét vagy alsó 10% -át.

Ehhez tegye a következő lépéseket:

• Válassza ki a teljes adatkészletet.
• Lépjen a Főoldalra -> Feltételes formázás -> Felső / alsó szabályok -> Top 10 elem (vagy %) / Alsó 10 elem (vagy %).
• A kiválasztott adatok alapján megnyílik a párbeszédpanel. Tegyük fel, hogy a Top 10 elemet választotta, majd megnyit egy párbeszédpanelt az alábbiak szerint:
• A jobb oldali legördülő menü segítségével adja meg a feltételnek megfelelő cellákra alkalmazandó formátumot. Van néhány létező formátum, amelyeket használhat, vagy megadhatja saját formátumát az Egyéni formátum opció használatával.
• Kattintson az OK gombra.

Ez azonnal kiemeli a kiválasztott adatkészlet 10 legfontosabb elemét. Vegye figyelembe, hogy ez csak azoknál a celláknál működik, amelyekben numerikus érték található.

Továbbá, ha kevesebb, mint 10 cella van az adatkészletben, és kiválasztja a Top 10 elemek/az alsó 10 elem kiemelésére szolgáló beállításokat, akkor az összes cella kiemelésre kerül.

Íme néhány példa a feltételes formázás működésére:

4. Hibák/üres helyek kiemelése

Ha sok számadattal és számítással dolgozik az Excelben, akkor tudja, hogy fontos a hibás vagy üres cellák azonosítása és kezelése. Ha ezeket a cellákat további számításokban használják, az hibás eredményekhez vezethet.

Az Excel feltételes formázása segíthet a hibás vagy üres cellák gyors azonosításában és kiemelésében.

Tegyük fel, hogy rendelkezünk az alábbi adatkészlettel:

Ez az adathalmaz üres cellával (A4) és hibákkal (A5 és A6) rendelkezik.

Az alábbi lépésekkel kiemelheti az üres vagy hibás cellákat:

• Válassza ki azt az adatkészletet, amelyben kiemelni szeretné az üres cellákat és a hibákat tartalmazó cellákat.
• Lépjen a Főoldalra -> Feltételes formázás -> Új szabály.
• Az Új formázási szabály párbeszédpanelen válassza a Képlet használata a formázandó cellák meghatározásához.
• Írja be a következő képletet a „Szabályleírás szerkesztése” szakasz mezőjébe:
  = VAGY (ISBLANK (A1), ISERROR (A1))
  • A fenti képlet két feltételt ellenőriz minden cellában - üres vagy sem, és van -e hiba, vagy sem. Ha bármelyik feltétel IGAZ, akkor IGAZ értéket ad vissza.
• Állítsa be azt a formátumot, amelyet alkalmazni szeretne az üres vagy hibás cellákra. Ehhez kattintson a Formázás gombra. Megnyitja a „Cellák formázása” párbeszédpanelt, ahol megadhatja a formátumot.
• Kattintson az OK gombra.

Ez azonnal kiemeli az összes üres vagy hibás cellát.

Jegyzet: Nem kell feltételes formázásban használni a képletben az A1: A7 teljes tartományt. A fent említett képlet csak az A1-et használja. Amikor ezt a képletet alkalmazza a teljes tartományra, az Excel egy -egy cellát ellenőriz, és módosítja a referenciát. Például amikor az A1 -et ellenőrzi, az = VAGY (ISBLANK (A1), ISERROR (A1)) képletet használja. Amikor ellenőrzi az A2 cellát, akkor az = OR (ISBLANK (A2), ISERROR (A2)) képletet használja. Automatikusan beállítja a referenciát (mivel ezek relatív hivatkozások) attól függően, hogy melyik cellát elemzik. Tehát nem kell külön képletet írni minden cellához. Az Excel elég okos ahhoz, hogy önmagában megváltoztassa a cellahivatkozást 🙂

Lásd még: Az IFERROR és az ISERROR használata az Excel hibáinak kezelésére.

5. Hőtérképek készítése

A hőtérkép az adatok vizuális ábrázolása, ahol a szín a cella értékét jelenti. Létrehozhat például egy hőtérképet, ahol a legmagasabb értékű cella zöldre van színezve, és az érték csökkenésével a vörös szín felé tolódik el.

Valami az alábbiak szerint:

A fenti adatkészlet értéke 1 és 100 között van. A cellák a benne szereplő érték alapján vannak kiemelve. 100 kapja a zöld színt, 1 pedig a piros színt.

Íme a lépések a hőtérképek létrehozásához feltételes formázással Excelben.

• Válassza ki az adatkészletet.
• Lépjen a Főoldalra -> Feltételes formázás -> Színskálák, és válasszon egyet a színsémák közül.

Amint rákattint a hőtérkép ikonra, alkalmazni fogja a formázást az adatkészletre. Több színátmenet közül választhat. Ha nem elégedett a meglévő színbeállításokkal, kiválaszthat további szabályokat, és megadhatja a kívánt színt.

Megjegyzés: Hasonló módon alkalmazhat Data Bard és Icon készleteket is.

6. Jelöljön ki minden más sort/oszlopot

Érdemes alternatív sorokat kiemelni az adatok olvashatóságának növelése érdekében.

Ezeket zebravonalaknak nevezik, és különösen hasznosak lehetnek, ha nyomtatja az adatokat.

Most kétféleképpen lehet ezeket a zebravonalakat létrehozni. A leggyorsabb módszer a táblázatos adatok Excel -táblává alakítása. Automatikusan alkalmazta a színt az alternatív sorokra. Bővebben itt olvashat róla.

Egy másik módszer a feltételes formázás.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Íme a lépések az alternatív sorok kiemeléséhez feltételes formázással az Excelben.

• Válassza ki az adatkészletet. A fenti példában válassza az A2: C13 lehetőséget (amely nem tartalmazza a fejlécet). Ha a fejlécet is fel szeretné venni, akkor válassza ki a teljes adathalmazt.
• Nyissa meg a Feltételes formázás párbeszédpanelt (Főoldal-> Feltételes formázás-> Új szabály) [Billentyűparancs - Alt + O + D].
• A párbeszédpanelen válassza a „Képlet használata a formázandó cellák meghatározásához” párbeszédpanelt.
• Írja be a következő képletet a „Szabályleírás szerkesztése” szakasz mezőjébe:
  = ISODD (ROW ())

• A fenti képlet ellenőrzi az összes cellát, és ha a cella sorszáma páratlan, akkor IGAZ értéket ad vissza. A megadott feltételes formátum minden cellára érvényes lesz, amely IGAZ értéket ad vissza.
• Állítsa be azt a formátumot, amelyet alkalmazni szeretne az üres vagy hibás cellákra. Ehhez kattintson a Formázás gombra. Megnyitja a „Cellák formázása” párbeszédpanelt, ahol megadhatja a formátumot.
• Kattintson az OK gombra.

Ez az! Az adathalmaz alternatív sorai kiemelésre kerülnek.

Ugyanazt a technikát sok esetben használhatja. Mindössze annyit kell tennie, hogy használja a megfelelő képletet a feltételes formázásban. Íme néhány példa:

• Alternatív páros sorok kiemelése: = ISEVEN (ROW ())
• Alternatív sorok kiemelése: = ISODD (ROW ())
• Minden 3. sor kiemelése: = MOD (ROW (), 3) = 0

7. Adatok keresése és kiemelése feltételes formázással

Ez a feltételes formázás kicsit fejlett használata. Úgy nézne ki, mint egy Excel rocksztár.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, terméknévvel, értékesítési képviselővel és földrajzzal. Az ötlet az, hogy írjon be egy karakterláncot a C2 cellába, és ha egyezik a cellák adataival, akkor ezt ki kell emelni. Valami az alábbiak szerint:

A keresés és kiemelés funkció létrehozásához tegye a következő lépéseket:

• Válassza ki az adatkészletet.
• Lépjen a Főoldalra -> Feltételes formázás -> Új szabály (Billentyűparancs - Alt + O + D).
• Az Új formázási szabály párbeszédpanelen válassza a „Képlet segítségével határozza meg a formázandó cellákat” lehetőséget.
• Írja be a következő képletet a „Szabályleírás szerkesztése” szakasz mezőjébe:
  = ÉS ($ C $ 2 ””, $ C $ 2 = B5)
• Állítsa be azt a formátumot, amelyet alkalmazni szeretne az üres vagy hibás cellákra. Ehhez kattintson a Formázás gombra. Megnyitja a „Cellák formázása” párbeszédpanelt, ahol megadhatja a formátumot.
• Kattintson az OK gombra.

Ez az! Most, amikor bármit beír a C2 cellába, és megnyomja az enter billentyűt, az összes megfelelő cellát kiemeli.

Hogy működik ez?

A feltételes formázásban használt képlet értékeli az adatkészlet összes celláját. Tegyük fel, hogy Japánba lép a C2 cellában. Most az Excel értékeli az egyes cellák képletét.

A képlet IGAZ értéket ad vissza egy cellának, ha két feltétel teljesül:

• A C2 cella nem üres.
• A C2 cella tartalma pontosan megegyezik az adathalmaz cella tartalmával.

Ezért minden Japán szöveget tartalmazó cella kiemelésre kerül.

Töltse le a példa fájlt

Ugyanezt a logikát használva hozhat létre variációkat, például:

• Cella helyett jelölje ki az egész sort.
• Akkor is kiemelje, ha részleges egyezés van.
• A cellák/sorok kiemelése gépelés közben (dinamikus) [Imádni fogod ezt a trükköt :)].

Hogyan lehet eltávolítani a feltételes formázást az Excelben

Alkalmazás után a feltételes formázás a helyén marad, kivéve, ha manuálisan távolítja el. A legjobb gyakorlat szerint a feltételes formázást csak azokra a cellákra alkalmazza, ahol szüksége van rá.

Mivel illékony, lassú Excel -munkafüzethez vezethet.

A feltételes formázás eltávolítása:

• Válassza ki azokat a cellákat, amelyekből törölni szeretné a feltételes formázást.
• Lépjen a Főoldalra -> Feltételes formázás -> Szabályok törlése -> Szabályok törlése a kijelölt cellákból.
  • Ha el szeretné távolítani a feltételes formázást a teljes munkalapról, válassza a Szabályok törlése a teljes lapról lehetőséget.

Fontos tudnivalók az Excel feltételes formázásáról

• Feltételes formázás illékony. Ez lassú munkafüzethez vezethet. Csak akkor használja, ha szükséges.
• Ha feltételes formázást tartalmazó pasztacellákat másol, a feltételes formázás is másolásra kerül.
• Ha ugyanazon cellakészletre több szabályt alkalmaz, akkor minden szabály aktív marad. Bármilyen átfedés esetén az utoljára alkalmazott szabály részesül előnyben. A sorrendet azonban a Szabályok kezelése párbeszédpanel sorrendjének megváltoztatásával módosíthatja.