Färglägg en cell efter villkor eller formel. Fylla celler beroende på värdet i Microsoft Excel Hur man markerar en rad i Excel med färg

Om värdet i en cell uppfyller ett användardefinierat villkor kan du med hjälp markera denna cell (till exempel ändra dess bakgrund). I den här artikeln, låt oss gå vidare - vi kommer att välja hela raden i tabellen som innehåller den här cellen.

Släpp in sortimentet A6:C16 det finns en tabell med en lista över arbeten, deadlines och status för deras slutförande (se exempelfil).

Uppgift 1 - textvärden

Det är nödvändigt att markera raden som innehåller arbetet med en viss status med en färg. Till exempel, om arbetet inte har påbörjats, kommer raden att markeras i rött, om arbetet ännu inte har slutförts, sedan i grått, och om det är slutfört, sedan i grönt. Vi kommer att välja rader med hjälp av regler.

Lösning 1

Låt oss skapa en liten tabell med status för jobb i sortimentet E6:E9 .

Välj ett cellintervall A7:C17 , som innehåller en lista över verk, och installera via menyn Hem/ Fyllningsfärg fyllningsbakgrunden är röd (vi antar att alla verk är initialt i status Ej påbörjad).

Se till att cellintervallet är markerat A7:C17 (A7 måste vara ). Ring menykommandot Villkorlig formatering / Skapa regel / Använd formel för att bestämma celler som ska formateras .

• i fält" Formatera värden för vilka följande formel är sann» du måste ange =$C7=$E$8 (i cellen E8 värde hittas I arbetet). Var uppmärksam på användningen av ;
• tryck på knappen Formatera ;
• välj flik fylla ;
• välj grå färg ;
• Tryck på OK.

UPPMÄRKSAMHET: Än en gång uppmärksammar jag formeln =$C7=$E$8 . Vanligtvis kommer användare in =$C$7=$E$8 , dvs. ange en extra dollarsymbol.

Du måste göra liknande åtgärder för att välja verk i statusen Avslutad. Formeln i det här fallet kommer att se ut som =$C7=$E$9 , och ställ in fyllningsfärgen till grön.

Som ett resultat kommer vår tabell att ha följande form.

Om du snabbt vill utöka reglerna för villkorlig formatering till en ny rad i en tabell, markerar du cellerna ny linje (A17:C17 ) och tryck på . Reglerna kommer att kopieras till en sträng 17 tabeller.

Uppgift 2 - Datum

Antag att du för en logg över anställdas besök vetenskapliga konferenser(centimeter. exempelbladsdatumfil).

Tyvärr är kolumnen Datum besökt inte sorterad och du måste markera datumet för det första och sista besöket för varje anställd. Till exempel gick medarbetaren Kozlov till konferensen för första gången den 24/07/2009 och sista gången den 18/07/2015.

Låt oss först skapa en formel för villkorlig formatering i kolumnerna B och E. Om formeln returnerar TRUE, kommer motsvarande rad att markeras, om FALSE, då inte.

I kolumn D skapas = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 A som definierar maxdatum för en specifik anställd.

Notera: Om du behöver bestämma det maximala datumet oavsett anställd, blir formeln mycket enklare = $B7=MAX($B$7:$B$16) och matrisformeln behövs inte.

Låt oss nu markera alla tabellceller utan rubrik och skapa en regel. Låt oss kopiera formeln till regeln (den behöver inte anges som en matrisformel!).

Anta nu att datumkolumnen har sorterats och det krävs att man väljer rader vars besöksdatum faller inom ett visst intervall.

För att göra detta, använd formeln =AND($B23>$E$22;$B23

För celler E22 och E23 med brytdatum (markerade i gult) används $E$22 och $E$23. Därför att hänvisningen till dem bör inte ändras i UV-reglerna för alla tabellceller.

För cell B22 blandad adressering används $B23, dvs. referensen till kolumn B bör inte ändras (för detta föregår den B med ett $-tecken), men referensen till raden bör ändras beroende på raden i tabellen (annars kommer alla datumvärden att jämföras med datumet från B23 ).

Så UV-regeln för till exempel en cell är A27 ska titta =OCH($B27>$E$22;$B27 , dvs. A27 kommer att markeras eftersom i denna rad datum från B27 faller inom det angivna intervallet (för celler från kolumn A kommer valet fortfarande att göras beroende på innehållet i kolumn B från samma rad - detta är "magin" med blandad adressering $B23).

Och för cellen B31 UV-regeln skulle se ut =AND($B31>$E$22;$B31 , d.v.s. B31 kommer inte att väljas, eftersom på denna rad datumet från B31 inte faller inom det angivna intervallet.

Ta reda på hur Excel-arkändra snabbt färgen på en hel rad beroende på värdet på en cell. Se knep och exempel på formler för numeriska och textvärden.

I en av de tidigare artiklarna diskuterade vi hur man ändrar färgen på en cell beroende på dess värde. Den här gången kommer vi att prata om hur man markerar hela raden i Excel 2010 och 2013 beroende på värdet på en cell, och även avslöjar några knep och visar exempel på formler för att arbeta med numeriska och textvärden.

Hur man ändrar färgen på en rad baserat på det numeriska värdet för en av cellerna

Anta att vi har följande tabell över företagsbeställningar:

Vi vill färglägga raderna med olika färger beroende på den beställda mängden (värdet i kolumnen Antal.) för att markera de viktigaste beställningarna. Excel-verktyget hjälper oss att klara denna uppgift - " Villkorlig formatering».

Som du kan se, byt till excel färg en hel rad baserat på det numeriska värdet för en av cellerna - det här är inte alls svårt. Därefter ska vi titta på några fler exempel på formler och ett par knep för att lösa mer komplexa problem.

Hur man skapar flera regler för villkorlig formatering med en given prioritet

I tabellen från föregående exempel skulle det förmodligen vara mer praktiskt att använda olika fyllningsfärger för att markera raderna i kolumnen Antal. olika betydelser. Skapa till exempel en annan villkorlig formateringsregel för strängar som innehåller värdet 10 eller fler och markera dem i rosa. För detta behöver vi en formel:

För att båda reglerna vi skapat ska fungera samtidigt måste du ordna dem i rätt prioritet.

Hur man ändrar färgen på en rad baserat på textvärdet i en av cellerna

För att förenkla orderhanteringskontrollen kan vi i vår tabell markera rader av order med olika leveransstatus med olika färger, information om vilka finns i kolumnen Leverans:

• Om leveransdatumet för beställningen ligger i framtiden (värde Kommer om X dagar), då bör fyllningen av sådana celler vara orange;
• Om beställningen har levererats (värde Levereras), då bör fyllningen av sådana celler vara grön;
• Om leveransdatumet för beställningen ligger i det förflutna (värde Gått över tiden), då bör fyllningen av sådana celler vara röd.

Och självklart bör cellfyllningsfärgen ändras om orderstatusen ändras.

Med formel för värden Levereras och Gått över tiden allt är klart, det kommer att likna formeln från vårt första exempel:

=$E2="Levereras"
=$E2="Förfallen"

Uppgiften låter svårare för beställningar som måste levereras igenom X dagar (värde Kommer om X dagar). Vi ser att leveranstiden för olika beställningar är 1, 3, 5 eller fler dagar, vilket gör att ovanstående formel inte gäller här, då den syftar till ett exakt värde.

I det här fallet är det bekvämt att använda funktionen SÖK(SÖK) och för att hitta en partiell matchning, skriv följande formel:

SEARCH("Ska in";$E2)>0
=SÖK("Ska in",$E2)>0

I denna formel E2- detta är adressen till cellen, baserat på vars värde vi kommer att tillämpa regeln för villkorlig formatering; dollartecken $ behövs för att tillämpa formeln på hela linjen; skick " >0 ” betyder att formateringsregeln kommer att tillämpas om den angivna texten (i vårt fall ”Förfaller in”) hittas.

Ledtråd: Om formeln använder villkoret " >0 ", då kommer raden att markeras i färg varje gång den angivna texten hittas i nyckelcellen, oavsett var den är placerad i cellen. I exempeltabellen i figuren nedan, kolumnen Leverans(kolumn F) kan innehålla texten "Brådskande, förfaller inom 6 timmar" (vilket betyder Brådskande, leverera inom 6 timmar), och denna rad kommer också att färgas.

För att markera med färg de linjer där innehållet i nyckelcellen börjar med den angivna texten eller symbolerna, måste formeln skrivas i följande form:

SEARCH("Ska in";$E2)=1
=SÖK("Ska in",$E2)=1

Du måste vara mycket försiktig när du använder en sådan formel och kontrollera om cellerna i nyckelkolumnen med data börjar med ett mellanslag. Annars kan du knacka på dina hjärnor länge och försöka förstå varför formeln inte fungerar.

Så, efter samma steg som i , skapade vi tre formateringsregler, och vår tabell började se ut så här:

Hur man ändrar färgen på en cell baserat på värdet på en annan cell

Egentligen är detta ett specialfall. Istället för hela tabellen väljer du kolumnen eller området där du vill ändra färgen på cellerna och använd formlerna som beskrivs ovan.

Vi kan till exempel ställa in tre av våra regler på ett sådant sätt att endast celler som innehåller ett ordernummer är markerade i färg (kolumn ordernummer) baserat på värdet för en annan cell i den här raden (med värdena från kolumnen Leverans).

Hur man ställer in flera villkor för att ändra färgen på en rad

Om du behöver markera rader med samma färg när ett av flera olika värden visas kan du istället för att skapa flera formateringsregler använda funktionerna Och(och) ELLER(ELLER) och därmed kombinera flera villkor i en regel.

Vi kan till exempel markera beställningar som förväntas inom 1 och 3 dagar i rosa och de som ska betalas inom 5 och 7 dagar i gult. Formlerna kommer att se ut så här:

ELLER($F2="Förfaller om 1 dagar";$F2="Förfaller om 3 dagar")
=ELLER($F2="Förfaller om 1 dagar",$F2="Förfaller om 3 dagar")

ELLER($F2="Förfaller om 5 dagar";$F2="Förfaller om 7 dagar")
=ELLER($F2="Förfaller om 5 dagar",$F2="Förfaller om 7 dagar")

För att markera beställningar med en kvantitet på minst 5, men inte mer än 10 (värdet i kolumnen Antal.), skriver vi formeln med funktionen Och(OCH):

OCH($D2>=5;$D2<=10)
=OCH($D2>=5,$D2<=10)

Naturligtvis kan du i dina formler inte använda två, utan så många villkor som krävs. Till exempel:

ELLER($F2="Förfaller om 1 dagar";$F2="Förfaller om 3 dagar";$F2="Förfaller om 5 dagar")
=ELLER($F2="Förfaller om 1 dagar",$F2="Förfaller om 3 dagar",$F2="Förfaller om 5 dagar")

Ledtråd: Nu när du har lärt dig att färglägga celler i olika färger, beroende på vilka värden de innehåller, kanske du vill ta reda på hur många celler som är markerade i en viss färg och beräkna summan av värdena i dessa celler . Jag vill glädja dig, den här åtgärden kan också göras automatiskt, och vi kommer att visa lösningen på detta problem i en artikel som ägnas åt frågan Hur man beräknar mängden, beloppet i Excel och ställer in ett filter för celler av en viss färg .

Vi har bara visat några av de möjliga sätten att få en tabell att se ut som en zebrarand, vars färg beror på värdena i cellerna och kan ändras samtidigt som dessa värden ändras. Om du letar efter något annat för din data, låt oss veta så kommer vi definitivt på något tillsammans.

Hej alla. Dagens inlägg vill jag ägna åt sätt att välja celler i Microsoft Excel. Om du redan vet hur man väljer celler, skumma texten, kanske är vissa funktioner i programmet inte kända för dig, och du drar inte nytta av dem.

Och så, valet av grupper av celler är nödvändigt för att utföra någon allmän åtgärd med dem: eller , etc. Hur kan du markera celler i Excel? Förståelse!

Hur man markerar alla celler i ett excelark

Jag kommer omedelbart att svara på den mest ställda frågan om ämnet för artikeln. Hur markerar man alla celler i ett ark samtidigt? Jag erbjuder dig två sätt, vilket du än gillar bäst, använd det ena:

Välja ett rektangulärt cellområde

Anta att du behöver välja en rektangulär grupp av celler för att formatera dem för din datatyp. Jag kan erbjuda 5 sätt att lyfta fram, och du väljer vilket som är lämpligt för dig just nu:

1. Val av mus. Det vanligaste sättet är att hålla vänster musknapp inne i en av cellerna (inte på ramen) och dra markeringen i önskad riktning längs raderna och kolumnerna
2. Genom att trycka på en tangent Flytta. Placera markören i en av hörncellerna i det framtida urvalet. Håll ned Skift och välj ett område med piltangenterna. Släpp Shift när du är klar.
3. tryck tangent F8 . Kommandot liknar det föregående, men tangenten behöver inte tryckas ned. Placera markören i en av hörncellerna i arrayen, tryck på F8 för att aktivera valläget. Använd piltangenterna för att utöka markeringen, eller klicka med musen i det motsatta hörnet av det framtida urvalet. När arrayen är vald trycker du på F8 igen för att avsluta detta läge.

   Ett annat sätt är att placera markören i den övre vänstra cellen i området, håll nere Shift och klicka på den nedre högra cellen (se fig.)

4. Ange i fältet "Namn". celladress eller cellintervall (separerade med ett kolon) och tryck på Retur
   
   
5. Utför kommandot(på tangentbordet - F5 , eller Ctrl + G ). I fönstret som öppnas, i fältet "Referens", anger du adressen till en cell eller cellintervall separerade med ett kolon. Klicka på OK

Markera alla rader och kolumner

Om du behöver välja en hel kolumn eller rad finns här några alternativ:

• Klicka med musen på radnumret eller kolumnnamnet. Om du behöver välja flera intilliggande linjer, håll nere vänster musknapp på radnumret och dra markeringen till de linjer som du vill markera. Gör samma sak med kolumner.
• Placera markören i valfri cell på raden du vill markera och tryckkombination Skift+Mellanslag . Använd Ctrl+Mellanslag för att välja en kolumn
• För att markera icke intilliggande rader och kolumner − nypaCtrl och klicka efter kolumnnamn och radnummer. När du är klar, släpp Ctrl .

Val av icke sammanhängande intervall

Om du behöver välja flera icke-intilliggande celler, gör det på något av de föreslagna sätten:

Välj på flera ark samtidigt

Om kalkylbladen i ett dokument innehåller samma tabeller med olika data kan vi utföra operationer på alla flikar samtidigt. Detta sparar tid och minskar risken för fel. Till exempel behöver vi rubriker för var och en av tabellerna på flera ark. Du behöver inte göra detta på varje flik separat - välj alla ark och gör allt på en gång.

För att välja samma område på flera ark, välj först önskade ark. Aktivera det första arket från listan, håll ned Ctrl och klicka på etiketterna för alla ark för att välja.

När alla nödvändiga ark är valda kan du utföra operationer. Observera att på namnraden, bredvid filnamnet, inskriptionen " [Grupp]". Det betyder att Excel är redo att bearbeta en grupp ark.

Därefter, på det aktiva bladet, välj önskade intervall, gör ändringar, fyll i allmänna data och formler. Vad du än gör kommer att tillämpas på alla valda ark. Glöm inte att avmarkera arken när du har avslutat batchbearbetningen. För att göra detta, högerklicka på valfri etikett på det grupperade arket, välj i snabbmenyn Dela upp gruppen.

Villkorligt urval av celler

Microsoft Excel kan markera en grupp av celler baserat på deras innehåll. Inte alla användare är medvetna om den här funktionen, även om dess användning kan vara mycket användbar.

Utför kommandot Hem - Redigera - Hitta och välj - Gå. I fönstret som visas klickar du på Välj .... Dialogrutan Välj grupp av celler visas med följande urvalsalternativ:

Markera celler genom sökrutan

För att öppna värdesökningsfönstret - kör Hem - Redigera - Hitta och välj - Hitta(eller tryck på tangentkombinationen Ctrl+F ). Hitta alla visas. En lista med celler som innehåller den valda informationen visas längst ned i fönstret. Markera en eller flera celler (medan du håller ned Ctrl ) i listan så att Excel markerar dem. För att markera alla hittade celler - välj en av dem och tryck på kombinationen Ctrl + A.

Du kan använda specialtecken för att söka:

• "?" - vilken karaktär som helst
• "*" - valfritt antal tecken

Till exempel, för att hitta alla celler som börjar med bokstaven "A" - skriv in "A *" i sökningen.

Det handlar om att välja celler i MS Excel, och jag vill ägna nästa inlägg åt . Som alltid kommer den här artikeln att innehålla många användbara saker som definitivt kommer väl till pass i ditt arbete. Så läs den, du kommer inte ångra dig!

Förresten, jag väntar på dina frågor angående den här artikeln i kommentarerna!

Villkorlig formatering i Excel låter dig välja inte bara en cell, utan hela raden på en gång.För att kunna välja hela raden på en gång i en stor tabell måste du ställa in våra tabellformateringsvillkor i tabellen.Vad är villkorlig formatering, läs artikeln " Villkorlig formatering i Excel.
Vi har en tabell med besöksdata. Vi behöver välja ut besökare som har genomfört en utbildning, behandling, arbete etc. Tabellen är följande. I separata celler skapar vi en liten hjälptabell. Låt oss till exempel göra en etikett i cellerna G2, G3 och G4.
Om vi ​​skriver i kolumnen "Status" - "Slutförd" eller "Stage 1", "Steg 2", kommer hela linjen att färgas i färg.
Istället för en hjälptabell kan du skapa en "rullgardinslista" i hjälpcellen.
Nu ställer vi in ​​villkorlig formatering till tabellceller.
Vi väljer hela tabellen med data och ytterligare en tom rad längst ner i tabellen. Vi kopierar en tom rad i tabellen om vi behöver lägga till rader i tabellen. Formateringsvillkoren kommer att kopieras omedelbart.
Så vi har valt intervalltabellen A2:E7.
På fliken Hem klickar du på knappen Villkorlig formatering och väljer alternativet Skapa regler.
Klicka på raden "Jämför tabellkolumner för att bestämma formaterade celler."Skriv formeln på raden "Format". =$E2=$G$2
notera– hänvisning till cell E2 är blandad.
Tryck på knappen med "0". Här väljer vi och ställer in fyllnadsfärgen för linjen, teckensnittsfärgen för denna linje. Vi har valt den gröna cellfyllningsfärgen.Klicka på alla tre dialogrutorna "OK". Allt.
Nu skriver vi i tabellen i kolumnen "Status" - "Slutförd" och vår linje blir grön, vilket vi ställer in i de villkorliga formateringsreglerna.
Uppmärksamhet! I cellerna i kolumnen "Status" skriver du orden på samma sätt som de är skrivna i hjälptabellen. Till exempel har vi ordet "Slutfört" skrivet med stor bokstav. Om vi ​​skriver ordet "slutfört" i en kolumncell med en liten bokstav, kommer den villkorliga formateringen inte att fungera. Därför är det bättre att ställa in en rullgardinslista i kolumnen "Status".Hur man installerar en rullgardinslista, se artikeln " Dropdown-lista i Excel. Det blev så här.

Behöver du markera dubbletter av värden i en kolumn? Behöver du välja de första 5 maximala cellerna? Är det nödvändigt att göra en termisk skala för klarhet (färgen ändras beroende på ökningen / minskningen av värdet på cellerna)? I Excel kan celler markeras efter villkor mycket snabbt och enkelt. Specialfunktionen "Villkorlig formatering" är ansvarig för att markera celler med färg. Rekommenderas starkt! Läs mer på:

Jag beskrev huvuddragen i början av artikeln, men det finns faktiskt många av dem. Mer om det mest användbara

För att komma igång, i aktivitetsfältet i huvudmenyn, hitta avsnittet Stilar och klicka på knappen Villkorlig formatering.

När du klickar på den öppnas en meny med olika alternativ för denna redigering. Som ni ser finns det verkligen många möjligheter här.

Nu mer om det mest användbara:

Excel som markerar celler efter villkor. Enkla termer

För att göra detta, gå till objektet Cellvalsregler. Om du till exempel behöver markera alla celler som är större än 100, klicka på knappen Mer. I fönstret:

som standard föreslås villkoren markeras i rött, men du kan ställa in önskad cellformatering genom att klicka i den högra rutan och välja önskat alternativ.

Markera dubblettvärden, inkl. över flera kolumner

För att markera alla dubbletter av värden, välj lämpligt menyalternativ Duplicera värden.

Vad ska man göra om man behöver hitta upprepningar i två eller flera kolumner, till exempel när det fullständiga namnet finns i olika kolumner? Gör en annan kolumn och kombinera värdena med formeln = , dvs. i en separat cell kommer du att ha skrivit IvanovIvanIvanych. Genom en sådan kolumn kan du redan enkelt markera dubbletter av värden. Det är viktigt att förstå att om ordordningen skiljer sig, kommer Excel att betrakta sådana rader som icke-upprepande (till exempel Ivan Ivanych Ivanov).

Färgmarkering av det första/sista värdet. Återigen villkorlig formatering

För att göra detta, gå till Regler för att välja den första och sista cellen och välj önskat objekt. Förutom att du kan markera de första/sista värdena (inklusive procentsatser), kan du använda möjligheten att markera data över och under genomsnittet (jag använder det ännu oftare). Mycket praktiskt för att se resultat som avviker från normen eller genomsnittet!

Konstruktion av termiskt diagram och histogram

En cool funktion för datavisualisering är värme-/temperaturdiagrammet. Summan av kardemumman är att, beroende på värdet på värdet i en kolumn eller rad, markeras cellen med en viss färgnyans, ju fler desto rödare, till exempel. Tabeller uppfattas mycket bättre med ögonen, och det blir lättare att fatta beslut. En av de bästa analysatorerna är faktiskt ofta vårt öga, respektive hjärnan, och inte en maskin!

Cellhistogrammet (i blått i bilden nedan) är också en mycket användbar funktion för att upptäcka förändringar i värden och jämföra dem.

Markera celler som innehåller specifik text

Mycket ofta behöver du hitta celler som innehåller en viss uppsättning tecken, du kan givetvis använda funktionen = , men det är enklare och snabbare att tillämpa villkorlig formatering, go - Regler för att markera celler - Text innehåller

Mycket användbart när man arbetar med text. Ett exempel när du har de fullständiga namnen på anställda i kolumnen, men du måste välja alla Ivanovs kollegor. Välj cellerna, gå till önskat objekt och välj innehållstexten Ivanov, varefter vi filtrerar tabellen efter färg

Excel-markering. Filtrera efter färg

Utöver alternativen ovan kan du filtrera de markerade cellerna efter färg med ett vanligt filter. Till min förvåning är det väldigt få som känner till detta - tydligen ekon från 2003 års version - den här funktionen fanns inte där.

Kontrollera formateringsvillkor

För att kontrollera vilken villkorlig formatering du har ställt in, gå till Hem - Villkorlig formatering - Hantera regler. Här kan du redigera de redan inställda villkoren, tillämpningsområdet och även välja prioritet för den angivna formateringen (vem som är högre är viktigare, du kan ändra det med pilknapparna).

Ogiltigt villkorligt formateringsintervall

Viktig! Villkorlig formatering, när den används felaktigt, är ofta orsaken till stark . Det blir en fördubbling av formateringen, till exempel om du kopierar celler med markering många gånger. Då får du många förutsättningar med färg. Jag såg själv mer än 3 tusen förhållanden - filen saktade ner fult. Dessutom kan filen sakta ner när intervallet är inställt som på bilden ovan, det är bättre att ange A: A - för hela intervallet.

Läs mer om Excel-bromsar och deras orsaker. Den här artikeln har hjälpt mer än hundra personer ;)

Hoppas det var till hjälp, förlåt!