Zvýrazňujeme buňky v Excelu, abychom mohli snadno identifikovat oblasti zaměření na datech. Formátování buněk v aplikaci Excel se provádí také pro vizualizaci hodnot dat na základě barvy a stylu. Můžete se například zaměřit na 3 nejmenší hodnoty v datech. Je těžké to udělat ve velkých datech, ale pokud byste mohli říct Excelu, aby zvýraznil 3 nejnižší hodnoty, pak by to bylo docela snadné. Naštěstí to můžeme udělat pomocí možnosti podmíněného formátování v Excelu.
Je snadné zvýraznit 3 nejmenší hodnoty v aplikaci Excel, ale je to trochu složité, když přidáme kritéria. V tomto článku se naučíme, jak pomocí podmínky zvýraznit nejmenší 3 hodnoty.
Obecný vzorec v podmíněném formování
= AND (kontrolní buňka = kritéria, hodnotová buňka<= MALÝ (IF (rozsah kritérií = kritéria, rozsah hodnot),3))
Zkontrolujte buňku: Je to buňka, kterou chcete zkontrolovat, zda splňuje první kritéria (kategorii).
Kritéria: Kritéria, která chcete filtrovat.
Hodnotová buňka: Je to buňka, která obsahuje číselnou hodnotu. Porovnáme jej s 3. nejmenší hodnotou ve své kategorii, v rozsah hodnot.
Rozsah kritérií: Je to absolutní rozsah, který obsahuje kritéria.
Rozsah hodnot: Rozsah, který obsahuje všechny hodnoty.
Podívejme se na příklad, aby bylo vše jasné.
Příklad: Zvýrazněte spodní tři prodeje provedené oddělením 1
Zde mám ukázkový pracovní soubor, který obsahuje tržby provedené různými odděleními v různých měsících.
Chci zdůraznit tři nejnižší tržby provedené oddělením 1.
Pojďme identifikovat proměnné, které zde máme.
Zkontrolujte buňku: $ B2 (chceme zkontrolovat, zda B2 obsahuje „obchodní zastoupení 1“. Protože nechceme, aby se sloupec měnil, používáme znak $. Tím se zvýrazní celý řádek.)
Kritéria: "Oddělení 1"
Hodnotová buňka: $ C2
Rozsah kritérií: $ B $ 2: $ B $ 12
Rozsah hodnot: $ C $ 2: $ C $ 12
Nyní, když známe všechny argumenty, které musíme předat vzorci, je čas implementovat výše uvedený obecný vzorec do datové sady.
- Vyberte data. Jít domů ? podmíněné formátování ? Nové pravidlo
- Vyberte možnost „Pomocí vzorce určit, kterou buňku chcete formátovat“.
- Do níže uvedeného textového pole napište tento vzorec
= AND ($ B2 = "Dealership 1", $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealership 1", $ C $ 2: $ C $ 12), 3))
Stiskněte tlačítko OK. A je hotovo. Jsou zvýrazněny spodní tři nebo řekněme nejmenší 3 tržby z oddělení 1.
Jak to funguje?
Pojďme rozebrat vzorec zevnitř.
Tento vzorec je maticový vzorec, ale při podmíněném formátování není třeba CTRL+SHIFT+ENTER.
Protože máme dvě podmínky, první je dealerství 1 a druhé jsou hodnoty menší nebo rovné 3. nejmenší hodnotě v dealerství 1. Obě musí být PRAVDA. K tomu používáme funkci AND.
$ B2 = "Obchodní zastoupení 1"
První kritéria lze snadno zkontrolovat. Jednoduše jsme uvedli toto prohlášení $ B2 = "Obchodní zastoupení 1". Bude aktuální, pokud aktuální buňka B2 obsahuje Obchodní zastoupení 1.
$ C2 <=MALÝ(LI($ B $ 2: $ B $ 12 = "Obchodní zastoupení 1", $ C $ 2: $ C $ 12), 3)
U druhého kritéria musíme filtrovat Dealership 1 a najít v něm 3. nejmenší hodnotu. Poté porovnejte buňku kritérií s 3. nejmenší hodnotou. To se provádí pomocí příkazu $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealership 1", $ C $ 2: $ C $ 12), 3)
Když to vyhodnotíme, tento vzorec vyřeší takto:
Zkontrolujeme rozsah $ B $ 2: $ B $ 12 a pomocí příkazu $ B $ 2: $ B $ 12 = „Dealership 1“ získáme buňky obsahující „Dealership 1“. Budeme mít pole PRAVDA a NEPRAVDA.
$ C2 <= SMALL (IF ({TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, $ C $ 2: $ C $ 12), 3)
Pro každou PRAVDU bude hodnota vrácena z rozsahu hodnot $ C $ 2: $ C $ 12 a budeme mít další pole.
$ C2 <= MALÝ ({12; FALSE; 25; FALSE; FALSE; FALSE; FALSE; 18; FALSE; FALSE; 14}, 3)
Nyní funkce SMALL vrátí 3. nejmenší hodnotu z tohoto pole. (FLASE bude ignorováno).
Nyní máme:
$ C2 <= 18
Nakonec vzorec je
= AND ($ B2 = "Obchodní zastoupení 1", $ C2 <= 18)
Nyní zkontroluje, zda B2 obsahuje Obchodní zastoupení 1. To ano. Proto je PRAVDA
= AND (PRAVDA, $ C2 <= 18)
Dále zkontroluje, zda je C2 menší než nebo rovno 18 (třetí nejmenší tržby v obchodním zastoupení 1). Je to PRAVDA (12).
= A (PRAVDA, PRAVDA)
Protože oba příkazy jsou PRAVDA, vzorec vrací PRAVDU a my dostaneme první ŘADU zvýrazněnou barvou. To samé se děje v každém řádku.
Fuj !!! Bylo těžké to vysvětlit. Doufám, že jsem byl dostatečně jasný, abyste pochopili metodu. V praxi je to snadné.
Zde jsme použili pevně zakódovaná kritéria, ale můžeme také poskytnout odkaz na buňku. Díky tomu bude vzorec flexibilnější.
Tady pro Kritéria Použil jsem odkaz na buňku E1. Bez ohledu na to, jaké obchodní zastoupení máte v E1, budou zvýrazněny tři nejmenší hodnoty z tohoto zastoupení.
Vzorec v podmíněném formátování je:
= AND ($ B2 = $ E $ 1, $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Takže ano, takto můžete zvýraznit spodní tři hodnoty s podmínkou v aplikaci Excel. Doufám, že článek byl užitečný. Pokud máte jakýkoli dotaz, použijte níže uvedenou sekci komentáře. Můžete klást otázky související s tímto článkem nebo jakýmkoli jiným článkem souvisejícím s Excelem. Rádi vám pomůžeme.
Podmíněné formátování na základě jiné hodnoty buňky
IF a podmíněné formátování
Podmíněné formátování pomocí vzorce 2016
Podmíněné formátování pomocí VBA v Microsoft Excelu
Zvýrazněte buňky, které obsahují konkrétní text
Jak sečíst více sloupců s podmínkou
Oblíbené články:
50 zkratek aplikace Excel ke zvýšení produktivity
Funkce VLOOKUP v Excelu
COUNTIF v aplikaci Excel 2016
Jak používat funkci SUMIF v aplikaci Excel