We markeren cellen in Excel, zodat we gemakkelijk de focusgebieden op gegevens kunnen identificeren. Opmaak van cellen in Excel wordt ook gedaan voor het visualiseren van gegevenswaarden op basis van kleur en stijl. U kunt zich bijvoorbeeld concentreren op de 3 kleinste waarden in uw gegevens. Het is moeilijk om te doen in een grote hoeveelheid gegevens, maar als je Excel zou kunnen vertellen om de 3 laagste waarden te markeren, dan zou het vrij eenvoudig zijn. Gelukkig kunnen we dat doen met behulp van de voorwaardelijke opmaakoptie in Excel.
Het is gemakkelijk om de 3 kleinste waarde in Excel te markeren, maar het wordt een beetje lastig als we criteria toevoegen. In dit artikel leren we hoe je de kleinste 3 waarden kunt markeren met een voorwaarde.
Algemene formule in voorwaardelijke opmaak
=EN(check cel=criteria, waarde cel<=KLEIN(ALS(criteriumbereik = criterium, waardebereik),3))
Controleer cel: Het is de cel die u wilt controleren of deze voldoet aan de eerste criteria (de categorie).
criteria: De criteria die u wilt filteren.
Waarde cel: Het is de cel die een numerieke waarde bevat. We zullen het vergelijken met de 3e kleinste waarde in zijn categorie, in de waardebereik.
Criteriabereik: Het is het absolute bereik dat de criteria bevat.
Waardebereik: Het bereik dat alle waarden bevat.
Laten we een voorbeeld bekijken om dingen duidelijk te maken.
Voorbeeld: markeer de onderste drie verkopen die zijn gedaan door afdeling 1
Hier heb ik een voorbeeldwerkbestand dat verkopen bevat die door verschillende afdelingen in verschillende maanden zijn gedaan.
Ik wil de onderste drie verkopen benadrukken die door afdeling 1 zijn gedaan.
Laten we de variabelen identificeren die we hier hebben.
Controleer cel: $B2 (we willen controleren of B2 "dealer 1" bevat. Omdat we niet willen dat de kolom verandert, gebruiken we het $ teken. Hierdoor wordt de hele rij gemarkeerd.)
criteria: "Afdeling 1"
Waarde cel: $C2
Criteriabereik: $B$2:$B$12
Waardebereik: $C$2:$C$12
Nu we alle argumenten kennen die we aan de formule moeten doorgeven, is het tijd om de bovenstaande generieke formule op de dataset te implementeren.
- Selecteer de gegevens. Ga naar huis ? conditionele opmaak ? Nieuwe regel
- Selecteer de optie "Een formule gebruiken om te bepalen welke cel moet worden opgemaakt".
- Schrijf deze formule in het onderstaande tekstvak:
=AND($B2="Dealer 1",$C2<=KLEIN(IF($B$2:$B$12="Dealer 1",$C$2:$C$12),3))
Druk op de OK-knop. En het is klaar. De onderste drie of zeg maar de kleinste 3 verkopen van afdeling 1 zijn gemarkeerd.
Hoe werkt het?
Laten we de formule van binnenuit opsplitsen.
Deze formule is een matrixformule, maar CTRL+SHIFT+ENTER is niet nodig bij voorwaardelijke opmaak.
Aangezien we twee voorwaarden hebben, is de eerste de Dealerschap 1 en de tweede de waarden kleiner dan of gelijk aan de 3e kleinste waarde in Dealerschap 1. Beide moeten WAAR zijn. Daarvoor gebruiken we de EN-functie.
$B2="Dealer 1"
Het eerste criterium is eenvoudig te controleren. We zetten eenvoudig deze verklaring $B2="Dealer 1". Het is WAAR als de huidige cel B2 bevat Dealer 1.
$C2<=KLEIN(INDIEN($B$2:$B$12="Dealer 1",$C$2:$C$12),3)
Voor het tweede criterium moeten we dealer 1 filteren en de op twee na kleinste waarde daarin vinden. Vergelijk vervolgens de criteriacel met de op drie na kleinste waarde. Dit wordt gedaan met behulp van de verklaring $C2<=SMALL(IF($B$2:$B$12="Dealerschap 1",$C$2:$C$12),3)
Wanneer we het evalueren, lost deze formule op deze manier op:
We controleren het bereik $B$2:$B$12 en krijgen cellen die "Dealerschap 1" bevatten met de instructie $B$2:$B$12="Dealerschap 1". We zullen een reeks TRUE en FALSE hebben.
$C2<=SMALL(IF({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE},$C$2:$C$12),3)
Voor elke TRUE wordt waarde geretourneerd uit het waardebereik $C$2:$C$12 en hebben we een andere array.
$C2<=KLEIN({12;FALSE;25;FALSE;FALSE;FALSE;FALSE;18;FALSE;FALSE;14},3)
Nu zal de SMALL-functie de op twee na kleinste waarde van deze array retourneren. (FLASE wordt genegeerd).
Nu hebben we:
$C2<=18
Ten slotte is de formule
=EN($B2="Dealer 1",$C2<=18)
Nu controleert het of B2 bevat Dealer 1. Het doet. Daarom is het WAAR
=EN(WAAR,$C2<=18)
Vervolgens wordt gecontroleerd of C2 kleiner is dan of gelijk is aan 18 (derde kleinste verkoop bij dealer 1). Het is WAAR (12).
=EN(WAAR, WAAR)
Aangezien beide uitspraken WAAR zijn, retourneert de formule WAAR en krijgen we onze eerste RIJ gemarkeerd met kleur. Hetzelfde gebeurt in elke rij.
Fewww!!! Het was moeilijk uit te leggen. Ik hoop dat ik duidelijk genoeg was om u de methode te laten begrijpen. In de praktijk is het eenvoudig.
Hier hebben we hardgecodeerde criteria gebruikt, maar we kunnen ook celverwijzingen geven. Dit maakt de formule flexibeler.
Hier voor criteria Ik heb celverwijzing van E1 gebruikt. Welke dealer u ook heeft in E1, de drie kleinste waarde van die dealer wordt gemarkeerd.
De formule in voorwaardelijke opmaak is:
=EN($B2=$E$1,$C2<=KLEIN(IF($B$2:$B$12=$E$1,$C$2:$C$12),3))
Dus ja, dit is hoe je de onderste drie waarde kunt markeren met een voorwaarde in Excel. Ik hoop dat het artikel nuttig was. Als je een vraag hebt, gebruik dan de opmerkingen hieronder. U kunt vragen stellen met betrekking tot dit artikel of elk ander Excel-gerelateerd artikel. Wij helpen u graag verder.
Voorwaardelijke opmaak op basis van een andere celwaarde
IF en voorwaardelijke opmaak
Voorwaardelijke opmaak met formule 2016
Voorwaardelijke opmaak met VBA in Microsoft Excel
Markeer cellen die specifieke tekst bevatten
Meerdere kolommen optellen met voorwaarde
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?