Feltételes formázás használata Excel VBA -val

Excel feltételes formázása

Az Excel feltételes formázása lehetővé teszi a cellaformázást meghatározó szabályok meghatározását.

Létrehozhat például egy szabályt, amely kiemeli azokat a cellákat, amelyek megfelelnek bizonyos feltételeknek. Például:

• Számok, amelyek egy bizonyos tartományba esnek (pl. Kevesebb mint 0).
• A lista 10 legjobb eleme.
• „Hőtérkép” készítése.
• „Képlet-alapú” szabályok gyakorlatilag minden feltételes formázásra.

Az Excelben a Feltételes formázás megtalálható a szalagon a Kezdőlap> Stílusok (ALT> H> L) alatt.

Saját szabály létrehozásához kattintson az „Új szabály” gombra, és megjelenik egy új ablak:

Feltételes formázás VBA -ban

Mindezek a feltételes formázási szolgáltatások elérhetők a VBA használatával.

Ne feledje, hogy amikor feltételes formázást állít be a VBA-kódon belül, az új paraméterek megjelennek az Excel front-end feltételes formázási ablakában, és láthatók lesznek a felhasználó számára. A felhasználó ezeket szerkesztheti vagy törölheti, kivéve, ha lezárta a munkalapot.

A feltételes formázási szabályok a munkalap mentésekor is mentésre kerülnek

A feltételes formázási szabályok kifejezetten egy adott munkalapra és egy adott cellatartományra vonatkoznak. Ha máshol szükség van rájuk a munkafüzetben, akkor azokat a munkalapon is be kell állítani.

A feltételes formázás gyakorlati felhasználása a VBA -ban

Előfordulhat, hogy egy csomó nyers adatot importál a munkalapra egy CSV (vesszővel elválasztott értékek) fájlból, vagy egy adatbázis táblából vagy lekérdezésből. Ez egy irányítópultba vagy jelentésbe kerülhet, és a változó számok egyik időszakból a másikba importálódhatnak.

Ha egy szám megváltozik, és kívül esik az elfogadható tartományon, akkor ezt érdemes kiemelni pl. a cella háttérszínét pirossal, és ezt elvégezheti a feltételes formázás beállításával. Ily módon a felhasználó azonnal vonzza ezt a számot, és utána megvizsgálhatja, miért történik ez.

A VBA használatával be- vagy kikapcsolhatja a feltételes formázást. A VBA segítségével törölheti a szabályokat egy cellatartományon, vagy újra bekapcsolhatja azokat. Előfordulhat olyan helyzet, amikor teljesen jó oka van egy szokatlan számnak, de amikor a felhasználó bemutatja a műszerfalat vagy jelentést a magasabb szintű vezetésnek, akkor el akarja távolítani a „vészharangot”.

Emellett a nyers importált adatokon érdemes kiemelni, hogy a számok nevetségesen nagyok vagy nevetségesen kicsik. Az importált adattartomány általában különböző méretű minden időszakban, így a VBA segítségével értékelheti az új adattartomány méretét, és feltételes formázást csak ehhez a tartományhoz szúrhat be.

Előfordulhat olyan helyzet is, hogy van egy rendezett névsor, numerikus értékekkel mindegyik ellen pl. munkavállalói fizetés, vizsgajegyek. Feltételes formázás esetén a fokozatosan használt színek segítségével a legmagasabbról a legalacsonyabbra léphet, ami nagyon lenyűgözőnek tűnik prezentációs célokra.

A nevek listája azonban nem mindig lesz statikus méretű, és a VBA -kód használatával frissítheti az osztott színek skáláját a tartomány méretének változása szerint.

Egyszerű példa feltételes formátum létrehozására egy tartományon

Ez a példa feltételes formázást állít be egy munkalap cellatartományához (A1: A10). Ha a tartományban lévő szám 100 és 150 között van, akkor a cella háttérszíne piros lesz, különben nem lesz színe.

1234567891011121314

Feltételes formázásPélda ()'Határozza meg a tartománytDim MyRange mint tartományA MyRange = tartomány beállítása („A1: A10”)„Törölje a meglévő feltételes formázást a tartománybólMyRange.FormatConditions.Delete‘Feltételes formázás alkalmazásaMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlBetween, _Képlet1: = "= 100", Képlet2: = "= 150"MyRange.FormatConditions (1). Belső.Szín = RGB (255, 0, 0)End Sub

Figyeljük meg, hogy először határozzuk meg a tartományt MyRange feltételes formázást alkalmazni.

Ezután töröljük a tartomány minden létező feltételes formázását. Ez jó ötlet annak megakadályozására, hogy ugyanaz a szabály kerüljön hozzáadásra a kód minden egyes futtatásakor (természetesen nem minden esetben lesz megfelelő).

A színeket számértékek adják meg. Ehhez célszerű RGB (piros, zöld, kék) jelölést használni. Ehhez szabványos színállandókat használhat pl. vbRed, vbBlue, de nyolc szín közül választhat.

Több mint 16,7 millió szín áll rendelkezésre, és az RGB használatával mindegyiket elérheti. Ez sokkal könnyebb, mint megpróbálni megjegyezni, hogy melyik szám melyik színhez tartozik. Mindhárom RGB színszám 0 és 255 között van.

Ne feledje, hogy az „xlBetween” paraméter tartalmazza, így a 100 vagy 150 cellaértékek kielégítik a feltételt.

Több feltételes formázás

Érdemes több feltételes szabályt beállítani az adattartományon belül, hogy a tartomány minden értékére különböző feltételek vonatkozzanak:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Adja hozzá az első szabálytMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlBetween, _Képlet1: = "= 100", Képlet2: = "= 150"MyRange.FormatConditions (1). Belső.Szín = RGB (255, 0, 0)'Adja hozzá a második szabálytMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlLess, _Képlet1: = "= 100"MyRange.FormatConditions (2). Interior.Color = vbBlue'Harmadik szabály hozzáadásaMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlGreater, _Képlet1: = "= 150"MyRange.FormatConditions (3). Belső.Szín = vbSárgaEnd Sub

Ez a példa az első szabályt állítja be a korábbiakhoz hasonlóan, a piros cella színét, ha a cella értéke 100 és 150 között van.

Ezután további két szabály kerül hozzáadásra. Ha a cella értéke kisebb, mint 100, akkor a cella színe kék, és ha nagyobb, mint 150, akkor a cella színe sárga.

Ebben a példában meg kell győződnie arról, hogy a számok minden lehetősége le van fedve, és hogy a szabályok nem fedik egymást.

Ha az üres cellák ebben a tartományban vannak, akkor kék színűek lesznek, mert az Excel továbbra is 100 -nál kisebb értékűnek tekinti őket.

Ennek módja egy másik feltétel hozzáadása kifejezésként. Ezt a kód első feltételszabályaként kell hozzáadni. Nagyon fontos, ha több szabály létezik, hogy a végrehajtási sorrend helyes legyen, különben az eredmények kiszámíthatatlanok lehetnek.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Adja hozzá az első szabálytMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1). Interior.Pattern = xlNone'Adja hozzá a második szabálytMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlBetween, _Képlet1: = "= 100", Képlet2: = "= 150"MyRange.FormatConditions (2). Belső.Szín = RGB (255, 0, 0)'Harmadik szabály hozzáadásaMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlLess, _Képlet1: = "= 100"MyRange.FormatConditions (3). Interior.Color = vbBlue- Adja hozzá a negyedik szabálytMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlGreater, _Képlet1: = "= 150"MyRange.FormatConditions (4). Belső.Szín = RGB (0, 255, 0)End Sub

Ez az xlExpression típusát használja, majd egy szabványos Excel képlet segítségével határozza meg, hogy a cella üres -e a numerikus érték helyett.

A FormatConditions objektum a Range objektum része. Hasonlóan működik, mint egy gyűjtemény, amelynek indexe 1 -nél kezdődik. Ezt az objektumot a For… Next vagy For … Minden ciklus segítségével ismételheti meg.

Szabály törlése

Néha előfordulhat, hogy törölnie kell egy egyedi szabályt több szabályból, ha az nem felel meg az adatkövetelményeknek.

12345678910111213

Sub DeleteConditionalFormattingExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Adja hozzá az első szabálytMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlBetween, _Képlet1: = "= 100", Képlet2: = "= 150"MyRange.FormatConditions (1). Belső.Szín = RGB (255, 0, 0)'Töröld a szabálytMyRange.FormatConditions (1). TörlésEnd Sub

Ez a kód új szabályt hoz létre az A1: A10 tartományhoz, majd törli azt. A törléshez a megfelelő indexszámot kell használnia, ezért ellenőrizze a „Szabályok kezelése” lehetőséget az Excel kezelőfelületén (ez a szabályokat a végrehajtás sorrendjében jeleníti meg), hogy megbizonyosodjon arról, hogy a megfelelő indexszámot kapja. Ne feledje, hogy az Excelben nincs visszavonási lehetőség, ha töröl egy feltételes formázási szabályt a VBA-ban, ellentétben azzal, ha az Excel kezelőfelületén keresztül teszi.

Szabály megváltoztatása

Mivel a szabályok egy meghatározott tartományon alapuló objektumok gyűjteménye, könnyen módosíthatja az egyes szabályokat a VBA használatával. A szabály hozzáadását követően a tényleges tulajdonságok csak olvashatóak, de a módosítási módszerrel módosíthatja őket. Az olyan tulajdonságok, mint a színek, olvashatók / írhatók.

123456789101112131415

Sub ChangeConditionalFormattingExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Adja hozzá az első szabálytMyRange.FormatConditions.Add Type: = xlCellValue, Operátor: = xlBetween, _Képlet1: = "= 100", Képlet2: = "= 150"MyRange.FormatConditions (1). Belső.Szín = RGB (255, 0, 0)'Változtasd meg a szabálytMyRange.FormatConditions (1). Módosítsa xlCellValue, xlLess, "10"„Változtassa meg a szabály színétMyRange.FormatConditions (1). Interior.Color = vbGreenEnd Sub

Ez a kód létrehoz egy tartományobjektumot (A1: A10), és hozzáad egy szabályt a 100 és 150 közötti számokhoz. Ha a feltétel igaz, akkor a cella színe pirosra változik.

A kód ezután a szabályt 10 -nél kisebb számokra változtatja. Ha a feltétel igaz, akkor a cella színe most zöldre változik.

Graduált színséma használata

Az Excel feltételes formázása lehetővé teszi, hogy a növekvő vagy csökkenő sorrendben futó számok tartományában fokozatos színeket használjunk.

Ez nagyon hasznos, ha olyan adatokkal rendelkezik, mint az értékesítési adatok földrajzi terület, városhőmérséklet vagy városok közötti távolság szerint. A VBA használatával további előnye, hogy választhatja a saját színsémáját az Excel kezelőfelületén kínált szabványos színek helyett.

1234567891011121314151617181920212223242526272829

Részdiplomás színek ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Határozza meg a skála típusátMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Válassza ki a színt a tartomány legalacsonyabb értékéhezMyRange.FormatConditions (1). ColorScaleCriteria (1). Típus = _xlConditionValueLowestValueMyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Szín = 7039480Vége ezzel'Válassza ki a színt a tartomány középső értékeihezMyRange.FormatConditions (1). ColorScaleCriteria (2). Típus = _xlConditionValuePercentileMyRange.FormatConditions (1). ColorScaleCriteria (2). Érték = 50'Válassza ki a tartomány közepének színétMyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Szín = 8711167Vége ezzel'Válassza ki a színt a tartomány legmagasabb értékéhezMyRange.FormatConditions (1). ColorScaleCriteria (3). Típus = _xlConditionValueHighestValueMyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Szín = 8109667Vége ezzelEnd Sub

Ennek a kódnak a futtatásakor a cella színeit az A1: A10 tartomány növekvő értékei szerint osztja be.

Ez egy nagyon lenyűgöző módja az adatok megjelenítésének, és minden bizonnyal felkelti a felhasználók figyelmét.

Feltételes formázás a hibaértékekhez

Ha hatalmas adatmennyiséggel rendelkezik, könnyen elveszíthet egy hibaértéket a különböző munkalapjain. Ha ezt megoldás nélkül mutatják be a felhasználónak, az nagy problémákhoz vezethet, és a felhasználó elveszítheti bizalmát a számokban. Ez az xlExpress szabálytípust és az IsError Excel függvényét használja a cella értékeléséhez.

Létrehozhat kódot úgy, hogy minden hibás cella vörös cella színe legyen:

1234567891011

RészhibaConditionalFormattingExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Hibaszabály hozzáadásaMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Állítsa a belső színt vörösreMyRange.FormatConditions (1). Belső.Szín = RGB (255, 0, 0)End Sub

Feltételes formázás a múltbeli dátumokhoz

Előfordulhat, hogy olyan adatokat importál, ahol a múltbeli dátumokat szeretné kiemelni. Erre példa lehet egy adósjelentés, amelyben azt szeretné, hogy a 30 naposnál régebbi számla dátumok kiemelkedjenek.

Ez a kód az xlExpress szabálytípust és egy Excel függvényt használja a dátumok értékeléséhez.

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumot dátumok oszlopa alapjánA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Hibaszabály hozzáadása a múltbeli dátumokhozMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Most ()-A1> 30"'Állítsa a belső színt vörösreMyRange.FormatConditions (1). Belső.Szín = RGB (255, 0, 0)End Sub

Ez a kód egy dátumtartományt vesz igénybe az A1: A10 tartományban, és a cella színét pirosra állítja minden olyan dátumhoz, amely elmúlt 30 nap.

A feltételben használt képletben a Now () megadja az aktuális dátumot és időt. Ez minden alkalommal újraszámítja a munkalapot, így a formázás egyik napról a másikra változik.

Adatsávok használata VBA feltételes formázásban

A VBA segítségével adatsorokat adhat hozzá egy számtartományhoz. Ezek majdnem olyanok, mint a mini diagramok, és azonnali képet adnak arról, hogy mekkora számok vannak egymáshoz képest. Az adatsorok alapértelmezett értékeinek elfogadásával a kód nagyon könnyen írható.

123456

RészadatsávFormattingExample ()Dim MyRange mint tartományA MyRange = tartomány beállítása („A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub

Az adatok így fognak kinézni a munkalapon:

Ikonok használata a VBA feltételes formázásában

Feltételes formázással ikonokat helyezhet el a számok mellé egy munkalapon. Az ikonok lehetnek nyilak, körök vagy más alakzatok. Ebben a példában a kód nyíl ikonokat ad hozzá a számokhoz a százalékos értékük alapján:

12345678910111213141516171819202122232425

Sub IconSetsExample ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Ikonkészlet hozzáadása a FormatConditions objektumhozMyRange.FormatConditions.AddIconSetCondition'Állítsa az ikonkészletet nyilakra - 1. feltételA MyRange.FormatConditions használatával (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Vége ezzel'állítsa be az ikonfeltételeket a szükséges százalékos értékhez - 2. feltételMyRange.FormatConditions (1). IconCriteria (2).Type = xlConditionValuePercent.Érték = 33.Operátor = xlGreaterEqualVége ezzel'állítsa be az ikonfeltételeket a szükséges százalékos értékhez - 3. feltételMyRange.FormatConditions (1). IconCriteria (3).Type = xlConditionValuePercent.Érték = 67.Operátor = xlGreaterEqualVége ezzelEnd Sub

Ez azonnali nézetet ad, amely megmutatja, hogy egy szám magas vagy alacsony. A kód futtatása után a munkalapja így fog kinézni:

Feltételes formázás használata az első öt kiemeléséhez

A VBA kóddal kiemelheti az adattartományon belüli 5 legjobb számot. Az „AddTop10” nevű paramétert használja, de a kódon belüli rangszámot 5 -re állíthatja. A felhasználó szeretné látni a tartomány legmagasabb számát anélkül, hogy először rendeznie kellene az adatokat.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange mint tartomány'Hozzon létre tartományobjektumotA MyRange = tartomány beállítása („A1: A10”)'Törölje a korábbi feltételes formátumokatMyRange.FormatConditions.Delete'Add hozzá a Top10 feltételtMyRange.FormatConditions.AddTop10A MyRange.FormatConditions használatával (1)'Állítsa a felülről lefelé paramétert.TopBottom = xlTop10Top„Csak a top 5 -öt.Rang = 5Vége ezzelMyRange.FormatConditions (1). Betűtípus'Állítsa be a betűszínét.Szín = -16383844Vége ezzelMyRange.FormatConditions (1). Belső'Állítsa be a cella háttérszínét.Szín = 13551615Vége ezzelEnd Sub

A munkalap adatai a kód futtatása után így néznek ki:

Ne feledje, hogy a 145 érték kétszer jelenik meg, tehát hat cella van kiemelve.

A StopIfTrue és a SetFirstPriority paraméterek jelentősége

A StopIfTrue fontos, ha egy cellatartomány több feltételes formázási szabályt tartalmaz. A tartományon belüli egyetlen cella kielégítheti az első szabályt, de a későbbi szabályokat is. Fejlesztőként érdemes lehet, hogy csak az első szabály formázását jelenítse meg. Más szabályfeltételek átfedhetik egymást, és nem kívánt változtatásokat hajthatnak végre, ha tovább engedik a szabályok listáját.

Ennek a paraméternek az alapértelmezett értéke True, de megváltoztathatja, ha azt szeretné, hogy az adott cellára vonatkozó összes többi szabályt figyelembe vegyék:

1	MyRange. FormatConditions (1) .StopIfTrue = Hamis

A SetFirstPriority paraméter határozza meg, hogy az adott feltételszabály kiértékelődik -e először, ha több szabály van az adott cellára.

1	MyRange. FormatConditions (1) .SetFirstPriority

Ez a szabály pozícióját a formázási feltételek gyűjteményén belül az 1. pozícióba helyezi át, és minden más szabály lefelé kerül a megváltozott indexszámokkal. Vigyázzon, ha az indexszámokkal módosítja a kód szabályait. Győződjön meg arról, hogy módosítja vagy törli a megfelelő szabályt.

Módosíthatja a szabály prioritását:

1	MyRange. FormátumFeltételek (1). Prioritás = 3

Ez megváltoztatja a feltételes formátumok listájában szereplő többi szabály relatív pozícióját.

Feltételes formázás használata hivatkozás más cellaértékekre

Ez az egyik dolog, amire az Excel feltételes formázása nem képes. Ehhez azonban létrehozhat saját VBA -kódot.

Tegyük fel, hogy van egy adatoszlopa, és az egyes számok szomszédos cellájában található egy szöveg, amely jelzi, hogy milyen formázást kell végrehajtani az egyes számokon.

A következő kód lefutja a számlistát, keresse meg a szomszédos cellában a szöveg formázását, majd formázza a számot a kívánt módon:

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()'Hozzon létre változókat a táblázatos adatok sorainak megtartásáhozDim RRow Long, N As Long'Rögzítse a táblázatos adattartományon belüli sorok számátRRow = ActiveSheet.UsedRange.Rows.Count'Ismételje meg a táblázatos adattartomány összes sorátN = 1 esetén RRow'A Select Case utasítással értékelje a formázást a 2. oszlop alapjánVálassza a Case ActiveSheet.Cells (N, 2) lehetőséget. Érték- Kapcsolja a belső színt kékre"Kék" tokActiveSheet.Cells (N, 1). Belső.Szín = vbKék'Kapcsolja a belső színt vörösre"Vörös" tokActiveSheet.Cells (N, 1). Belső.Szín = vbRed'Kapcsolja be a belső színt zöldre"Zöld" tokActiveSheet.Cells (N, 1). Belső.Szín = vbZöldVége KiválasztásKövetkező N.End Sub

A kód futtatása után a munkalapja így fog kinézni:

A formázáshoz hivatkozott cellák bárhol lehetnek a munkalapon, vagy akár a munkafüzet egy másik munkalapján. Bármilyen szöveget használhat a feltételhez a formázáshoz, és csak a fantáziája szab határt azoknak a felhasználásoknak, amelyekben ezt a kódot felhasználhatja.

Feltételes formázási utasításokban használható operátorok

Amint azt az előző példákban láthatta, az operátorok segítségével határozzák meg, hogy a feltételértékek hogyan kerülnek kiértékelésre pl. xlKözött.

Számos ilyen operátor használható, attól függően, hogy hogyan szeretné megadni a szabály feltételeit.

Név	Érték	Leírás
xlKözött	1	Között. Csak akkor használható, ha két képlet szerepel.
xlEqual	3	Egyenlő.
xlNagyobb	5	Nagyobb, mint.
xlGreaterEqual	7	Nagyobb vagy egyenlő.
xl Kevesebb	6	Kevesebb, mint.
xlLessEqual	8	Kevesebb vagy egyenlő.
xlNotBetween	2	Nem között. Csak akkor használható, ha két képlet szerepel.
xlNotEqual	4	Nem egyenlő.