Sorok kiemelése az Excel cellaértéke alapján (feltételes formázás)

Videó megtekintése - Sorok kiemelése az Excel cellaértékei alapján

Ha inkább írásbeli utasítást szeretne olvasni, az alábbiakban bemutatjuk az oktatóanyagot.

A feltételes formázás lehetővé teszi egy cella (vagy cellatartomány) formázását a benne szereplő érték alapján.

Néha azonban ahelyett, hogy csak kiemelné a cellát, érdemes a teljes sort (vagy oszlopot) kiemelni az egyik cella értéke alapján.

Hogy egy példát mondjak, az alábbiakban van egy adatkészletem, ahol kiemeltem az összes sort, ahol az értékesítési képviselő neve Bob.

Ebben az oktatóanyagban megmutatom, hogyan kell kiemelni a sorokat cellaérték alapján, feltételes formázással, különböző kritériumok használatával.

Kattints ide töltse le a Példa fájlt, és kövesse.

Sorok kiemelése szöveges kritériumok alapján

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és ki szeretné emelni az összes rekordot, ahol az értékesítési képviselő neve Bob.

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

1. Válassza ki a teljes adatkészletet (ebben a példában A2: F17).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. Kattintson az „Új szabályok” gombra.
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. A képlet mezőbe írja be a következő képletet: = $ C2 = "Bob"
7. Kattintson a „Formátum” gombra.
8. A megnyíló párbeszédpanelen állítsa be azt a színt, amelyben a sort kiemelni szeretné.
9. Kattintson az OK gombra.

Ez kiemeli az összes sort, ahol az értékesítési képviselő neve „Bob”.

Kattints ide töltse le a Példa fájlt, és kövesse.

Hogyan működik?

A feltételes formázás minden cellában ellenőrzi a megadott feltételt, azaz = $ C2 = "Bob"

Tehát amikor az A2 sor minden celláját elemzi, ellenőrizni fogja, hogy a C2 cella neve Bob vagy sem. Ha igen, akkor ez a cella kiemelésre kerül, ellenkező esetben nem.

Ne feledje, hogy itt a trükk az, hogy dollárjelet ($) használ az oszlop ábécéje előtt ($ C1). Ezzel lezártuk az oszlopot mindig C -re. Tehát még akkor is, ha az A2 cellát ellenőrzik a képletre, akkor a C2 -t fogja ellenőrizni, és amikor az A3 állapotát ellenőrzi, akkor a C3 -at.

Ez lehetővé teszi, hogy feltételes formázással kiemeljük a teljes sort.

Sorok kiemelése számkritériumok alapján

A fenti példában láttuk, hogyan kell ellenőrizni a nevet, és kiemelni az egész sort.

Ugyanezzel a módszerrel ellenőrizhetjük a numerikus értékeket is, és feltétel alapján kiemelhetjük a sorokat.

Tegyük fel, hogy ugyanazokkal az adatokkal rendelkezem (ahogy az alábbiakban látható), és ki szeretném emelni az összes sort, ahol a mennyiség több mint 15.

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

1. Válassza ki a teljes adatkészletet (ebben a példában A2: F17).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. Kattintson az „Új szabályok” gombra.
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. A képlet mezőbe írja be a következő képletet: = $ D2> = 15
7. Kattintson a „Formátum” gombra. A megnyíló párbeszédpanelen állítsa be azt a színt, amelyben a sort kiemelni szeretné.
8. Kattintson az OK gombra.

Ez minden olyan sort kiemel, ahol a mennyiség 15 -nél nagyobb vagy egyenlő.

Hasonlóképpen használhatjuk ezt a dátumra vonatkozó kritériumok megadására is.

Például, ha minden olyan sort szeretne kiemelni, ahol a dátum 2021. és 2022. július 10. között van, akkor használja az alábbi dátumképletet:

= $ A2> DATE (2018,7,10)

Sorok kiemelése több kritérium alapján (ÉS/VAGY)

Több feltételt is használhat a sorok feltételes formázással történő kiemeléséhez.

Például, ha ki szeretné emelni az összes olyan sort, ahol az értékesítési képviselő neve „Bob”, és a mennyiség meghaladja a 10 -et, akkor ezt a következő lépésekkel teheti meg:

1. Válassza ki a teljes adatkészletet (ebben a példában A2: F17).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. Kattintson az „Új szabályok” gombra.
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. A képlet mezőbe írja be a következő képletet: = ÉS ($ C2 = ”Bob”, $ D2> 10)
7. Kattintson a „Formátum” gombra. A megnyíló párbeszédpanelen állítsa be azt a színt, amelyben a sort kiemelni szeretné.
8. Kattintson az OK gombra.

Ebben a példában csak azok a sorok kerülnek kiemelésre, ahol mindkét feltétel teljesül (ez az ÉS képlet segítségével történik).

Hasonlóképpen használhatja az OR feltételt is. Ha például azokat a sorokat szeretné kiemelni, ahol vagy az értékesítési képviselő Bob, vagy a mennyiség több mint 15, akkor használja az alábbi képletet:

= VAGY ($ C2 = "Bob", $ D2> 15)

Kattints ide töltse le a Példa fájlt, és kövesse.

Sorok kiemelése különböző színben, több körülmény alapján

Előfordulhat, hogy az állapot alapján színekkel szeretné kiemelni a sorokat.

Például minden olyan sort kiemelhet, ahol a mennyiség több mint 20 zöld színű, és ahol a mennyiség több mint 15 (de kevesebb, mint 20) narancs színnel.

Ehhez két feltételes formázási szabályt kell létrehoznia, és meg kell adnia a prioritást.

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

1. Válassza ki a teljes adatkészletet (ebben a példában A2: F17).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. Kattintson az „Új szabályok” gombra.
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. A képlet mezőbe írja be a következő képletet: = $ D2> 15
7. Kattintson a „Formátum” gombra. A megnyíló párbeszédpanelen állítsa a színt narancsra.
8. Kattintson az OK gombra.
9. A „Feltételes formázási szabályok kezelője” párbeszédpanelen kattintson az „Új szabály” gombra.
10. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
11. A képlet mezőbe írja be a következő képletet: = $ D2> 20
12. Kattintson a „Formátum” gombra. A megnyíló párbeszédpanelen állítsa a színt zöldre.
13. Kattintson az OK gombra.
14. Kattintson az Alkalmaz (vagy az OK) gombra.

A fenti lépések azt mutatják, hogy az összes sor, amelynek mennyisége több mint 20, zöld színű lesz, és a több, mint 15 (de kevesebb, mint 20, narancssárga).

A szabályok megértése:

Több feltétel használata esetén fontos meggyőződni arról, hogy a feltételek sorrendje helyes.

A fenti példában a zöld szín feltétele a narancssárga szín felett van.

Ha fordítva van, akkor az összes sor csak narancssárga színű lesz.

Miért?

Mert egy sor, ahol a mennyiség több mint 20 (mondjuk 23), mindkét feltételünknek megfelel (= $ D2> 15 és = $ D2> 20). És mivel a narancssárga állapot a tetején van, előnyben részesül.

A feltételek sorrendjét a Fel/Le mozgatás gombokkal módosíthatja.

Kattints ide töltse le a Példa fájlt, és kövesse.

Jelölje ki azokat a sorokat, ahol a cellák üresek

Ha minden olyan sort ki szeretne emelni, ahol a cellák üresek, feltételes formázással ellenőriznie kell az egyes cellákat.

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

1. Válassza ki a teljes adatkészletet (ebben a példában A2: F17).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. Kattintson az „Új szabályok” gombra.
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. A képlet mezőbe írja be a következő képletet: = COUNTIF ($ A2: $ F2, ””)> 0
7. Kattintson a „Formátum” gombra. A megnyíló párbeszédpanelen állítsa a színt narancsra.
8. Kattintson az OK gombra.

A fenti képlet számolja az üres cellák számát. Ha az eredmény több, mint 0, akkor az üres cellák vannak a sorban.

Ha a cellák bármelyike ​​üres, akkor az egész sort kiemeli.

Sorok kiemelése a legördülő lista alapján

Az eddig tárgyalt példákban az összes feltételt a feltételes formázás párbeszédpanellel adtuk meg.

Az oktatóanyag ezen részében megmutatom, hogyan lehet dinamikussá tenni (hogy megadhassa a feltételt egy cellán belül az Excelben, és ez automatikusan kiemelje a sorokat).

Az alábbiakban egy példa látható, ahol kiválasztok egy nevet a legördülő menüből, és az ezzel a névvel ellátott összes sor kiemelve jelenik meg:

Ennek létrehozásához tegye a következő lépéseket:

1. Hozzon létre egy legördülő listát az A2 cellában. Itt az értékesítési képviselők nevét használtam a legördülő lista létrehozásához. Itt található egy részletes útmutató arról, hogyan hozhat létre legördülő listát az Excelben.
2. Válassza ki a teljes adatkészletet (ebben a példában C2: H17).
3. Kattintson a Kezdőlap fülre.
4. A Stílusok csoportban kattintson a Feltételes formázás elemre.
5. Kattintson az „Új szabályok” gombra.
6. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
7. A képlet mezőbe írja be a következő képletet: = $ E2 = $ A $ 2
8. Kattintson a „Formátum” gombra. A megnyíló párbeszédpanelen állítsa a színt narancsra.
9. Kattintson az OK gombra.

Most, amikor kiválaszt egy nevet a legördülő menüből, automatikusan kiemeli azokat a sorokat, ahol a név megegyezik a legördülő menüből.

Szeretne többet megtudni arról, hogyan kell keresni és kiemelni az Excelben? Nézd meg az alábbi videókat.