Hoe rijen in Excel op voorwaarde te markeren? Markeer rijen met opgegeven waarden. Cellen vullen op basis van waarde in Microsoft Excel

Om accenten correct te plaatsen in een document gemaakt in MS Excel, kunt u de cellen vergroten, een ander lettertype of lettergrootte selecteren, of u kunt over de vierkanten schilderen waar u zeker op moet letten.

Nu zullen we uitzoeken hoe we cellen met kleur in Excel kunnen markeren of de kleur kunnen wijzigen van de cellen die al zijn overgeschilderd. Hoe u een cel van kleur kunt laten veranderen volgens een bepaalde voorwaarde, afhankelijk van de ingevoerde waarde, en hoe u met de gemaakte regels werkt.

Eenvoudige blokvulling

Het inkleuren van één of meerdere blokken in Excel is niet moeilijk. Selecteer ze eerst en klik op het tabblad Start op de pijl naast de verfemmer om de lijst uit te vouwen. Kies daar een geschikte kleur en als niets past, klik dan op "Andere kleuren".

Trouwens, op deze manier kun je de hele regel invullen, klik eerst op het nummer om het te selecteren. Over het selecteren van cellen in Excel kun je een apart artikel lezen.

Als je met een tafel werkt waarin al iets is overschilderd, dan kun je de kleur van de blokken veranderen of zelfs helemaal verwijderen. Klik erop en uit de kleuren, of selecteer een nieuwe, of klik op de optie "Geen".

Afhankelijk van de ingevoerde gegevens

Laten we nu eens kijken hoe we een cel van kleur kunnen laten veranderen op basis van een bepaalde voorwaarde. Hiervoor wordt voorwaardelijke opmaak gebruikt, waarover op de site een apart artikel staat.

Tekst

Laten we de volgende tabel als voorbeeld nemen. Laten we ervoor zorgen dat het rood overeenkomt met de appel, geel met de peer en oranje met de sinaasappel.

We selecteren de gegevens waarmee we zullen werken, in mijn geval zijn dit de namen van fruit. Dan drukken we op "Conditionele opmaak", die we in de toekomst zullen blijven gebruiken. Klik in de lijst op "Maak een regel".

Dit venster wordt geopend. Selecteer het type bovenaan "Alleen cellen opmaken die bevatten", verder gaan we het ook vieren. Hieronder geven we de voorwaarden aan: we hebben een tekst die bepaalde woorden bevat. Klik in het laatste veld op de knop en geef een cel op, of voer tekst in.

Het verschil is dat door een verwijzing naar een cel te plaatsen (=$B$4 ), de voorwaarde zal veranderen afhankelijk van wat erin wordt getypt. In plaats van een appel in B4 zal ik bijvoorbeeld een bes aangeven, de regel verandert dienovereenkomstig en blokken met dezelfde tekst worden overschilderd. En als u een appel in het veld invoert, wordt naar dit specifieke woord gezocht en is het van niets afhankelijk.

Selecteer hier een vulkleur en klik op OK. Om alle opties te bekijken, klikt u op de knop "Overige".

De regel is gemaakt en sla deze op door op de knop OK te drukken.

Als gevolg hiervan werden alle blokken met de opgegeven tekst rood geverfd.

Er worden ook regels gemaakt voor ander fruit, alleen een andere vuloptie is geselecteerd.

Numeriek

Laten we het nu hebben over numerieke gegevens. Laten we de getallen in kolom D op een bepaalde achtergrond plaatsen volgens de voorwaarde die we stellen.

Selecteer een kolom, maak een regel, geef het type op. Vervolgens schrijven we - "Waarde" "groter dan" "15". U kunt het laatste nummer handmatig invoeren of het adres opgeven van de cel waar de gegevens vandaan komen. We beslissen over de vulling, klik op "OK".

Blokken waar getallen groter dan de geselecteerde worden ingevoerd, worden overschilderd.

Laten we meer regels specificeren voor de geselecteerde cellen - selecteer "Regelbeheer".

Kies hier alles, zoals ik hierboven heb beschreven, je hoeft alleen de kleur te veranderen en de voorwaarde in te stellen "minder dan of gelijk aan".

Als alles klaar is, klikt u op "Toepassen" en "OK".

Alles werkt, waarden gelijk aan en lager dan 15 zijn overschilderd in lichtblauw.

Hier zou het verschil tussen geïmporteerde en verkochte goederen ook duidelijk zichtbaar zijn. Selecteer twee kolommen en klik op "Regels voor celselectie" – "Dubbele waarden". Kies de juiste kleur. Als daarna twee cellen naast elkaar gekleurd zijn, betekent dit dat de binnengebrachte vruchten allemaal zijn verkocht.

Laten we het voorbeeld van de laatste kolom gebruiken om te laten zien hoe de cel van kleur kan veranderen afhankelijk van de opgegeven waarde. Selecteer ze en ga naar "Regelbeheer".

We maken een nieuwe voor het huidige fragment, indien nodig, in deze vervolgkeuzelijst kunt u kiezen voor het hele blad of voor andere bladen.

Selecteer de gewenste items in het venster dat wordt geopend. Ik zal alle waarden die groter zijn dan 90 met donkergroen vullen. Aangezien ik het adres in het laatste veld (=$F$15 ) heb opgegeven, verandert de regel wanneer het getal 90 in de cel verandert in bijvoorbeeld 110 ook veranderen. Sla de wijzigingen op door op de knop OK te klikken.

Ik zal nog een regel maken, maar daarin zal ik in lichtgroen alles markeren dat kleiner is dan of gelijk is aan 90. Vergeet niet alles op te slaan door op de knoppen rechtsonder te klikken.

Dit is wat er is gebeurd. Uit de laatste kolom kunt u visueel snel de winst bepalen uit de verkoop van welk product groter was.

Als bijvoorbeeld de prijs verandert, worden de regels ook herzien. Als de waarde groter of kleiner wordt dan de opgegeven waarde, verandert de kleur van de cel automatisch.

Ik denk dat je hebt gemerkt dat de regel niet alleen voor tekst en cijfers is gemaakt, maar ook voor datums, en afhankelijk van de vulling en de aanwezigheid van fouten in de cellen. Geef een voorwaarde op, kies een kleur en sla op.

Om te zien wat je hebt toegevoegd, selecteer je het bereik en in het venster "Regelbeheer" er zal een volledige lijst zijn. Met de knoppen bovenaan kunt u ze toevoegen, wijzigen of verwijderen.

Als de waarde in een cel voldoet aan een door de gebruiker gedefinieerde voorwaarde, kunt u met behulp van hulp deze cel markeren (bijvoorbeeld de achtergrond wijzigen). Laten we in dit artikel verder gaan - we selecteren de hele rij van de tabel die deze cel bevat.

Laat in het bereik A6:C16 er is een tabel met een lijst van werken, deadlines en status van hun voltooiing (zie voorbeeldbestand).

Taak1 - tekstwaarden

Het is noodzakelijk om de regel met het werk van een bepaalde status met een kleur te markeren. Als het werk bijvoorbeeld nog niet is begonnen, wordt de lijn rood gemarkeerd, is het werk nog niet voltooid, dan grijs en indien voltooid, dan groen. We zullen lijnen selecteren met behulp van regels.

Oplossing1

Laten we een kleine tabel maken met de statussen van taken in het bereik E6:E9 .

Selecteer een celbereik A7:C17 , met een lijst van werken, en installeer via het menu Home/ Opvulkleur de opvulachtergrond is rood (we gaan ervan uit dat alle werken in eerste instantie de status hebben Niet begonnen).

Zorg ervoor dat het cellenbereik is geselecteerd A7:C17 (A7 moet zijn ). Roep de menuopdracht op Voorwaardelijke opmaak / Regel maken / Formule gebruiken om cellen te bepalen die moeten worden opgemaakt.

• in het veld " Waarden opmaken waarvoor de volgende formule waar is» je moet =$C7=$E$8 invoeren (in de cel E8 waarde is gevonden aan het werk). Let op het gebruik van ;
• druk op de knop Formaat;
• selecteer tabblad vullen;
• kies grijze kleur ;
• Druk op OK.

AANDACHT: Nogmaals, ik let op de formule =$C7=$E$8 . Meestal komen gebruikers binnen =$C$7=$E$8, d.w.z. voer een extra dollarteken in.

U moet soortgelijke acties uitvoeren om werken in de status te selecteren Voltooid. De formule ziet er in dit geval uit als =$C7=$E$9 en stelt de vulkleur in op groen.

Als gevolg hiervan zal onze tabel de volgende vorm aannemen.

Als u regels voor voorwaardelijke opmaak snel wilt uitbreiden naar een nieuwe rij in een tabel, selecteert u de nieuwe rijcellen ( A17:C17 ) en druk op . De regels worden gekopieerd naar een string 17 tafels.

Taak 2 - Datums

Stel dat er een logboek wordt bijgehouden van de aanwezigheid van medewerkers van wetenschappelijke congressen (zie afb. voorbeeld bladdatumbestand).

Helaas is de kolom Bezoekdatum niet gesorteerd en moet u de datum van het eerste en laatste bezoek van elke medewerker markeren. Zo ging medewerker Kozlov voor het eerst naar de conferentie op 24/07/2009, en de laatste keer op 18/07/2015.

Laten we eerst een formule maken voor voorwaardelijke opmaak in kolommen B en E. Als de formule WAAR retourneert, wordt de bijbehorende rij gemarkeerd, indien ONWAAR, dan niet.

In kolom D gemaakt = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 Een die de maximumdatum voor een bepaalde werknemer definieert.

Opmerking: Als u de maximumdatum moet bepalen, ongeacht de werknemer, dan is de formule veel eenvoudiger =$B7=MAX($B$7:$B$16) en is de matrixformule niet nodig.

Laten we nu alle tabelcellen zonder kop selecteren en een regel maken. Laten we de formule naar de regel kopiëren (deze hoeft niet als matrixformule te worden ingevoerd!).

Stel nu dat de datumkolom is gesorteerd en dat het nodig is om rijen te selecteren waarvan de bezoekdatums binnen een bepaald bereik vallen.

Gebruik hiervoor de formule =AND($B23>$E$22;$B23<$E$23)

voor cellen E22 en E23 met afsluitdatums (geel gemarkeerd) gebruikt $ E $ 22 en $E$23. Omdat de verwijzing ernaar mag niet veranderen in de UV-regels voor alle tabelcellen.

voor cel B22 gemengde adressering $B23 wordt gebruikt, d.w.z. de verwijzing naar kolom B mag niet veranderen (hiervoor gaat het B vooraf met een $-teken), maar de verwijzing naar de rij moet veranderen afhankelijk van de rij van de tabel (anders worden alle datumwaarden vergeleken met de datum van B23 ).

Dus de UV-regel voor bijvoorbeeld een cel is A27 zal kijken =EN($B27>$E$22,$B27<$E$23) , т.е. A27 wordt gemarkeerd omdat in deze regel dateren van B27 valt binnen het gespecificeerde bereik (voor cellen uit kolom A wordt de selectie nog steeds gemaakt afhankelijk van de inhoud van kolom B uit dezelfde rij - dit is de "magie" van gemengde adressering $B23).

En voor de cel B31 de UV-regel zou er als volgt uitzien =AND($B31>$E$22;$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из B31 valt niet binnen het opgegeven bereik.

Om snel alle cellen te vinden en te markeren die bepaalde soorten gegevens bevatten, zoals formules, gebruik je de opdracht Gaan. Bovendien, met de hulp Ga naar vind alleen cellen die aan bepaalde criteria voldoen, zoals de laatste cel op een werkblad die gegevens of opmaak bevat.

Doe het volgende:

Extra informatie

U kunt altijd een vraag stellen aan de Excel Tech Community, om hulp vragen in de Answers-community of een nieuwe functie of verbetering op de website voorstellen

Bij het werken met tabellen zijn de waarden die erin worden weergegeven van primair belang. Maar een belangrijk onderdeel is ook het ontwerp. Sommige gebruikers beschouwen dit als een ondergeschikte factor en besteden er niet veel aandacht aan. Maar tevergeefs, want een mooi vormgegeven tafel is een belangrijke voorwaarde voor een betere beleving en begrip door gebruikers. Vooral datavisualisatie speelt daarbij een belangrijke rol. U kunt bijvoorbeeld visualisatietools gebruiken om tabelcellen te kleuren op basis van hun inhoud. Laten we eens kijken hoe dit in Excel kan worden gedaan.

Het is natuurlijk altijd fijn om een ​​goed ontworpen tabel te hebben waarin cellen afhankelijk van de inhoud in verschillende kleuren worden gekleurd. Maar deze functie is vooral relevant voor grote tabellen met een aanzienlijke hoeveelheid gegevens. In dit geval zal het vullen van de cellen met kleur het voor gebruikers veel gemakkelijker maken om door deze enorme hoeveelheid informatie te navigeren, aangezien kan worden gezegd dat deze al gestructureerd is.

U kunt proberen de elementen van het blad handmatig in te kleuren, maar nogmaals, als de tafel groot is, zal dit een aanzienlijke hoeveelheid tijd kosten. Bovendien kan in zo'n arsenaal aan data de menselijke factor zijn rol spelen en worden fouten gemaakt. Om nog maar te zwijgen van het feit dat de tabel dynamisch kan zijn en de gegevens erin periodiek en massaal veranderen. In dit geval wordt het handmatig veranderen van de kleur over het algemeen onrealistisch.

Maar er is een uitweg. Voor cellen die dynamische (veranderende) waarden bevatten, wordt voorwaardelijke opmaak toegepast en voor statistische gegevens kunt u de tool gebruiken "Zoek en vervang".

Methode 1: Voorwaardelijke opmaak

Met behulp van voorwaardelijke opmaak kunt u bepaalde waardengrenzen instellen waarop cellen in een of andere kleur worden geverfd. Het kleuren wordt automatisch uitgevoerd. Als de waarde van de cel als gevolg van de wijziging de grens overschrijdt, wordt dit element van het blad automatisch opnieuw geverfd.

Laten we eens kijken hoe deze methode werkt op een specifiek voorbeeld. We hebben een tabel met bedrijfsinkomsten, waarin de gegevens per maand zijn uitgesplitst. We moeten in verschillende kleuren die elementen markeren waarin het inkomen minder is dan 400000 roebel, van 400000 voordat 500000 roebels en meer 500000 roebels.

1. We selecteren de kolom waarin de informatie over het inkomen van de onderneming staat. Dan gaan we naar het tabblad "Thuis". Klikken op de knop "Conditionele opmaak", die zich op het lint in de gereedschapskist bevindt "Stijlen". Selecteer het item in de lijst die wordt geopend "Regelbeheer...".



2. Het venster Voorwaardelijke opmaakregels wordt geopend. In veld "Toon opmaakregels voor" moet zijn ingesteld op "Huidige Fragment". Standaard is dit precies wat daar moet worden aangegeven, maar voor het geval dat, controleer en, in geval van inconsistentie, de instellingen wijzigen volgens de bovenstaande aanbevelingen. Klik daarna op de knop "Maak een regel...".



3. Het venster Opmaakregel maken wordt geopend. Selecteer in de lijst met regeltypen de positie . In het regelbeschrijvingsblok in het eerste veld moet de schakelaar in de positie "Waarden". Zet in het tweede veld de schakelaar in de stand "Minder". Geef in het derde veld de waarde op, de elementen van het blad met een waarde lager dan die in een bepaalde kleur worden geverfd. In ons geval is deze waarde 400000 . Klik daarna op de knop "Formaat…".



4. Het venster Cellen opmaken wordt geopend. Verplaatsen naar tabblad "Gieten". Selecteer de vulkleur waarmee we cellen willen markeren met een waarde kleiner dan 400000 . Klik daarna op de knop oke onderaan het venster.



5. We keren terug naar het venster voor het maken van een opmaakregel en daar klikken we ook op de knop oke.



6. Na deze actie worden we doorgestuurd naar: Beheer van voorwaardelijke opmaakregels. Zoals je kunt zien, is er al één regel toegevoegd, maar we moeten er nog twee toevoegen. Klik dus nogmaals op de knop. "Maak een regel...".



7. En opnieuw komen we bij het venster voor het maken van regels. Verplaatsen naar sectie "Alleen cellen opmaken die bevatten". Laat in het eerste veld van deze sectie de parameter "Cel Waarde", en in de tweede plaats de schakelaar in de positie "Tussen". In het derde veld moet u de beginwaarde opgeven van het bereik waarin de bladelementen worden opgemaakt. In ons geval is dit nummer 400000 . In de vierde geven we de uiteindelijke waarde van dit bereik aan. het zal bedragen 500000 . Klik daarna op de knop "Formaat…".



8. Ga in het opmaakvenster opnieuw naar het tabblad "Gieten", maar deze keer kiezen we al een andere kleur, waarna we op de knop klikken oke.



9. Nadat u bent teruggekeerd naar het venster voor het maken van regels, klikt u ook op de knop oke.



10. Zoals we zien, in Regelmanager We hebben al twee regels gemaakt. Het blijft dus om een ​​derde te creëren. Klikken op de knop "Maak een regel".



11. Ga in het venster voor het maken van regels opnieuw naar de sectie "Alleen cellen opmaken die bevatten". Laat in het eerste veld de optie "Cel Waarde". Zet in het tweede veld de schakelaar op de politie "Meer". Voer een getal in het derde veld in 500000 . Klik vervolgens, zoals in eerdere gevallen, op de knop "Formaat…".



12. In het raam "Celformaat" terug naar het tabblad "Gieten". Dit keer kiezen we voor een kleur die anders is dan de vorige twee cases. Een klik op een knop uitvoeren oke.



13. Klik nogmaals op de knop in het venster voor het maken van regels. oke.



14. opent Regelmanager. Zoals je kunt zien, zijn alle drie de regels gemaakt, dus klik op de knop oke.



15. Nu worden de tabelelementen gekleurd volgens de opgegeven voorwaarden en randen in de voorwaardelijke opmaakinstellingen.



16. Als we de inhoud in een van de cellen wijzigen, terwijl we de grenzen van een van de opgegeven regels overschrijden, verandert dit bladelement automatisch van kleur.

U kunt voorwaardelijke opmaak ook op een iets andere manier gebruiken om bladelementen in te kleuren.

Methode 2: De tool Zoeken en selecteren gebruiken

Als de tabel statische gegevens bevat waarvan het niet de bedoeling is dat ze in de loop van de tijd worden gewijzigd, kunt u de tool gebruiken om de kleur van de cellen te wijzigen op basis van hun inhoud, genaamd "Zoeken en selecteren". Met de opgegeven tool kunt u de gegeven waarden vinden en de kleur in deze cellen wijzigen in de gewenste gebruiker. Maar er moet worden opgemerkt dat bij het wijzigen van de inhoud in de bladelementen de kleur niet automatisch verandert, maar hetzelfde blijft. Om de kleur in de huidige te veranderen, moet u de procedure opnieuw herhalen. Daarom is deze methode niet optimaal voor tabellen met dynamische inhoud.

Laten we eens kijken hoe het werkt voor een specifiek voorbeeld, waarvoor we dezelfde tabel voor ondernemingsinkomsten nemen.

1. Selecteer de kolom met de gegevens die met een kleur moeten worden opgemaakt. Ga dan naar het tabblad "Thuis" en klik op de knop "Zoeken en selecteren", die zich op het lint in de gereedschapskist bevindt "Bewerken". Klik in de lijst die wordt geopend op het item "Vinden".



2. venster begint "Zoek en vervang" tabblad "Vinden". Laten we eerst de waarden zoeken tot 400000 roebels. Omdat we geen enkele cel hebben die een waarde bevat die kleiner is dan 300000 roebels, dan moeten we in feite alle elementen selecteren die getallen bevatten in het bereik van 300000 voordat 400000 . Helaas kunt u dit bereik bij deze methode niet direct specificeren, zoals bij het toepassen van voorwaardelijke opmaak.

   Maar er is een mogelijkheid om iets anders te doen, wat ons hetzelfde resultaat zal geven. U kunt het volgende patroon in de zoekbalk invoeren: "3?????". Het vraagteken betekent een willekeurig teken. Het programma zoekt dus naar alle zescijferige nummers die beginnen met een cijfer "3". Dat wil zeggen dat de zoekuitvoer waarden in het bereik zal bevatten 300000 – 400000 , dat is wat we nodig hebben. Als de tafel getallen had die kleiner waren dan 300000 of minder 200000 , dan zou voor elk bereik van honderdduizend afzonderlijk moeten worden gezocht.

   We voeren de uitdrukking . in "3?????" in het veld "Vinden" en klik op de knop "Vind alle».



3. Daarna worden de resultaten van de zoekresultaten onder in het venster geopend. Klik met de linkermuisknop op een van hen. Dan typen we de toetsencombinatie Ctrl+A. Daarna worden alle resultaten van de zoekresultaten geselecteerd en tegelijkertijd de elementen in de kolom waarnaar deze resultaten verwijzen.



4. Nadat de elementen in de kolom zijn geselecteerd, haast u niet om het venster te sluiten "Zoek en vervang". In het tabblad zijn "Thuis" waarnaar we eerder zijn verhuisd, ga naar het lint naar het gereedschapsblok "Lettertype". Klik op het driehoekje rechts van de knop "Opvulkleur". Er wordt een selectie van verschillende vulkleuren geopend. We selecteren de kleur die we willen toepassen op bladelementen met waarden kleiner dan 400000 roebels.



5. Zoals u kunt zien, zijn alle cellen in de kolom die waarden bevatten die kleiner zijn dan 400000 roebels zijn gemarkeerd in de geselecteerde kleur.



6. Nu moeten we de elementen kleuren die waarden bevatten die variëren van 400000 voordat 500000 roebels. Dit bereik bevat nummers die overeenkomen met het patroon "4?????????". Typ het in het zoekvak en klik op de knop "Vind alle", nadat we eerder de kolom hebben geselecteerd die we nodig hebben.



7. Net als de vorige keer in de zoekresultaten, selecteren we het volledige resultaat door op een combinatie van sneltoetsen te drukken CTRL+A. Ga daarna naar het pictogram voor de opvulkleurselectie. We klikken erop en klikken op het pictogram van de schaduw die we nodig hebben, die de elementen van het blad zal kleuren, waar de waarden in het bereik liggen van 400000 voordat 500000 .



8. Zoals u kunt zien, worden na deze actie alle elementen van de tabel met gegevens in het interval van 400000 Aan 500000 gemarkeerd in de geselecteerde kleur.



9. Nu blijft het aan ons om het laatste interval van waarden te selecteren - meer dan 500000 . Hier hebben we ook geluk, want alle cijfers zijn meer 500000 zijn in het bereik van 500000 voordat 600000 . Typ daarom in het zoekveld de uitdrukking "vijf?????" en klik op de knop "Vind alle". Als er waarden groter waren dan 600000 , dan zouden we extra moeten zoeken naar de uitdrukking "6?????" enzovoort.



10. Markeer nogmaals de zoekresultaten met een combinatie Ctrl+A. Selecteer vervolgens met de knop op het lint een nieuwe kleur om het interval te vullen dat overschrijdt 500000 op dezelfde manier als we eerder deden.



11. Zoals u kunt zien, worden na deze actie alle elementen van de kolom overschilderd, volgens de numerieke waarde die erin is geplaatst. Nu kunt u het zoekvenster sluiten door op de standaard sluitknop in de rechterbovenhoek van het venster te klikken, aangezien onze taak als opgelost kan worden beschouwd.



12. Maar als we het nummer vervangen door een ander nummer dat de limieten voor een bepaalde kleur overschrijdt, verandert de kleur niet, zoals bij de vorige methode. Dit geeft aan dat deze optie alleen betrouwbaar werkt in die tabellen waarin de gegevens niet veranderen.

Zoals u kunt zien, zijn er twee manieren om cellen te kleuren, afhankelijk van de numerieke waarden die erin staan: voorwaardelijke opmaak gebruiken en de tool gebruiken "Zoek en vervang". De eerste methode is progressiever, omdat u hiermee duidelijker de voorwaarden kunt instellen waaronder de elementen van het blad worden gemarkeerd. Bovendien verandert bij voorwaardelijke opmaak de kleur van het element automatisch als de inhoud erin verandert, wat de tweede methode niet kan. Cellen vullen afhankelijk van de waarde door de tool toe te passen "Zoek en vervang" het is ook heel goed mogelijk om te gebruiken, maar alleen in statische tabellen.