Evidențierea liniilor în culoare pe rând. Cum se selectează rândurile în Excel după condiție? Selectarea rândurilor cu valori specificate

Excel nu este doar un instrument puternic pentru efectuarea calculelor, ci și un instrument cu care puteți prezenta în mod atractiv rezultatele calculelor. De aceea, în acest articol vreau să vorbesc despre modalități de a adăuga culoare celulelor. Culoarea celulei în Excel poate fi setată în mai multe moduri. Este imposibil de spus care metodă este cea mai bună, deoarece fiecare este unică în felul său și este destinată utilizării în anumite condiții și circumstanțe.

Setarea manuală a culorii celulei

Cea mai ușoară modalitate de a seta culoarea celulei în Excel este instrumentul Fill Color situat în bara de instrumente principală.

După ce ați selectat o anumită gamă de celule, trebuie să faceți clic pe instrumentul „Fill Color” sau pe săgeata de lângă acesta. Dacă faceți clic pe săgeata de lângă ea, se vor deschide culorile temei. Puteți selecta culoarea dorită din culorile sugerate sau puteți face clic pe butonul „Alte culori”. În acest caz, se va deschide o altă fereastră cu posibilitatea de a selecta o culoare.

Pe lângă metoda descrisă, există o a doua metodă manuală. Mai întâi trebuie să selectați o serie de celule, apoi să apelați meniul contextual și să faceți clic pe elementul de meniu „Format Cells”.

Se deschide caseta de dialog Format Cell. După aceasta, va trebui să faceți clic pe fila „Umplere”.

În această casetă de dialog trebuie să selectați o culoare și asta este tot.

Trebuie remarcat faptul că toate metodele manuale sunt folosite doar pentru a face tabelele mai lizibile. Folosind aceste metode, puteți decora anteturi de tabel și puteți evidenția rândurile de tabel. Dar metodele manuale nu sunt potrivite pentru colorarea celulelor pe baza valorii înregistrate.

Setați culoarea de fundal a celulei în funcție de valoare

Uneori se întâmplă ca, în funcție de ce valoare este scrisă într-o celulă, trebuie să setați culoarea unui anumit fundal. Să vedem cum să facem asta cu un exemplu. Avem agenți de vânzări cărora li se atribuie norme de plan pentru anumite grupuri de produse. Dacă norma nu este îndeplinită, atunci celula de vânzări ar trebui să se „lumineze” în roșu. Ca o condiție principală, spunem că vânzările săptămânale nu ar trebui să fie mai mici de 185 de mii de ruble.

Pentru a finaliza această sarcină, trebuie să selectați o serie de celule și să faceți clic pe „Formatare condiționată” din bara de instrumente.

În meniul care se deschide, ne interesează elementul „Reguli pentru selectarea celulelor”. Dacă faceți clic pe elementul indicat, puteți vedea că dezvoltatorii Excel au implementat multe opțiuni pentru selectarea celulelor. Puteți selecta fundalul folosind următoarele criterii: mai mare decât, mai mic decât, între, egal cu, textul conține, dată, valori repetate. Pentru a ne rezolva problema, trebuie să folosim „Less”.

După ce am specificat criteriul de 185 mii, selecțiile de celule vor apărea în tabel.

Metoda propusă este utilizată pe scară largă în analiza economică a diferitelor date. Este simplu și accesibil. Vă permite să setați anumite condiții de selecție chiar înainte de a defini în mod explicit datele.

Setarea culorii celulei folosind VBA

Metoda de selectare a celulelor în Excel, care va fi discutată mai jos, este cea mai universală. Pentru că cu ajutorul lui puteți construi cele mai complexe reguli de selectare a celulelor. Dar, din păcate, doar utilizatorii avansați îl pot folosi. Motivul este simplu - trebuie să programați, ceea ce înseamnă că trebuie să aveți abilități mai complexe.

Setarea culorii celulei folosind VBA este destul de simplă, codul de evidențiere va arăta astfel:

Interval("O6").Selectați
Selection.Interior.Color = QBColor(10)

După executarea acestui cod, celula O6 va deveni verde deschis.

Utilizarea VBA pentru a evidenția culoarea unei celule vă permite să rezolvați o gamă largă de probleme, cum ar fi evidențierea celulelor cu o anumită culoare:

• in functie de tipul valorii introduse;
• în funcție de momentul în care a fost editat documentul;
• in functie de utilizatorul calculatorului pe care este editat documentul etc.

Trebuie să evidențiați valorile duplicate într-o coloană? Trebuie să selectați primele 5 celule maxime? Trebuie să faceți o scală termică pentru claritate (culoarea se schimbă în funcție de creșterea/scăderea valorii celulei)? În Excel, evidențierea celulelor după culoare se poate face foarte rapid. În Excel, o funcție specială numită „Formatare condiționată” este responsabilă pentru evidențierea celulelor cu culoare. Recomand cu incredere! Citiți mai multe mai jos:

Pentru a începe, pe panglica de activități din meniul principal, găsiți secțiunea Stiluri și faceți clic pe butonul Formatare condiționată.

Când faceți clic, se va deschide un meniu cu diferite opțiuni pentru această editare. După cum puteți vedea, există într-adevăr o mulțime de oportunități aici.

Acum mai multe despre cele mai utile:

Excel evidențierea celulelor după culoare în funcție de condiții. Condiții simple

Pentru a face acest lucru, accesați Regulile de selecție a celulelor. Dacă, de exemplu, trebuie să selectați toate celulele mai mari de 100, faceți clic pe butonul Mai multe. La fereastră:

În mod implicit, condițiile sunt sugerate să fie evidențiate cu roșu, dar puteți seta formatarea dorită a celulei făcând clic în fereastra din dreapta și selectând opțiunea necesară.

Evidențierea valorilor duplicate, incl. pe mai multe coloane

Pentru a evidenția toate valorile care se repetă, selectați meniul corespunzător (vezi imaginea de la începutul articolului). Apoi, fereastra de formatare va apărea din nou. Configurați-l după cum doriți.

Ce ar trebui să faci dacă trebuie să găsești repetări în două sau mai multe coloane, de exemplu când numele tău complet este în coloane diferite? Faceți o altă coloană și combinați valorile cu formula =, adică. Într-o celulă separată veți avea scris IvanovIvanIvanovich, apoi folosind această coloană veți putea selecta valori duplicate. Este important să înțelegeți că, dacă ordinea cuvintelor diferă, Excel va considera astfel de linii ca nerepetate.

Evidențierea primelor/ultimelor valori în culoare. Din nou formatare condiționată

Pentru a face acest lucru, mergeți la secțiunea Reguli pentru selectarea primei și ultimei celule și selectați elementul dorit. Pe lângă faptul că poți evidenția primele/ultimele valori (inclusiv în procente), poți folosi posibilitatea de a evidenția date peste și sub medie (eu o folosesc și mai des). Foarte convenabil pentru vizualizarea rezultatelor care diferă de normă sau medie!

Construirea unei diagrame termice și a histogramei

O caracteristică interesantă pentru vizualizarea datelor este graficul termic/temperatură. Ideea este că, în funcție de valoarea dintr-o coloană sau un rând, celula este evidențiată într-o anumită nuanță de culoare. Tabelele sunt percepute mult mai bine cu ochii, iar luarea deciziilor devine mai ușoară. La urma urmei, unul dintre cei mai buni analizori este ochiul nostru și, în consecință, creierul nostru, nu o mașină!

Histograma dintr-o celulă (imaginea de mai jos) este, de asemenea, o funcție extrem de utilă pentru identificarea modificărilor valorilor și compararea acestora.

Evidențierea celulelor care conțin text specific

Foarte des trebuie să găsiți celule care conțin un anumit set de caractere, puteți utiliza, desigur, funcția =, dar este mai ușor și mai rapid să o aplicați în formatare condiționată, parcurgeți - Reguli pentru selectarea celulelor - Textul conține (vezi poza 2).

Foarte util atunci când lucrați cu text. Un exemplu când aveți numele complete ale angajaților scrise într-o coloană, dar trebuie să selectați toți colegii lui Ivanov. Selectați celulele, mergeți la elementul de meniu și selectați textul pe care îl conține Ivanov, apoi filtrați tabelul după culoare:

Evidențierea Excel. Filtrați după culoare

Pe lângă opțiunile de mai sus, puteți filtra celulele selectate după culoare folosind un filtru obișnuit. Spre surprinderea mea, foarte puțini oameni știu despre asta - aparent ecouri ale versiunii din 2003 - această caracteristică nu era acolo.

Pentru a plasa corect accentele într-un document realizat în MS Excel, puteți crește dimensiunea celulelor, puteți alege un font sau o dimensiune diferită a literelor sau puteți picta peste pătratele cărora trebuie neapărat să le acordați atenție.

Acum ne vom da seama cum să evidențiem celulele cu culoare în Excel sau să schimbăm culoarea celor care sunt deja completate. Cum să faci o celulă să schimbe culoarea în funcție de o anumită condiție, în funcție de valoarea introdusă în ea și cum să lucrezi cu regulile create.

Umplere simplă în bloc

Colorarea unuia sau mai multor blocuri în Excel nu este dificilă. Mai întâi, selectați-le și, în fila Acasă, faceți clic pe săgeata de lângă găleata de vopsea pentru a extinde lista. Selectați o culoare potrivită de acolo și, dacă nimic nu se potrivește, faceți clic pe „Alte culori”.

Apropo, puteți completa o linie întreagă în acest fel, doar mai întâi faceți clic pe numărul său pentru a-l evidenția. Puteți citi un articol separat despre selectarea celulelor în Excel.

Dacă lucrați cu o masă în care ceva a fost deja pictat, atunci puteți schimba culoarea blocurilor sau o puteți elimina complet astfel. Faceți clic pe el și din culori sau selectați unul nou, sau faceți clic pe opțiunea „Nu”.

În funcție de datele introduse

Acum să ne uităm la cum să facem ca o celulă să schimbe culoarea pe baza unei anumite condiții. Pentru aceasta este folosită formatarea condiționată, despre care există un articol separat pe site.

Text

Să luăm ca exemplu următorul tabel. Să facem astfel încât roșul să corespundă unui măr, galben unei pere și portocaliu unei portocale.

Selectăm datele cu care vom lucra, în cazul meu acestea sunt denumirile fructelor. Apoi apasa "Formatarea condițională", pe care îl vom folosi în continuare continuu. Din listă, faceți clic pe „Creează o regulă”.

Se deschide această fereastră. Selectați tipul din partea de sus - „Formatați numai celulele care conțin”, îl vom sărbători și în continuare. Mai jos indicăm condițiile: avem un text care conține anumite cuvinte. În ultimul câmp, fie dați clic pe butonul și selectați o celulă, fie introduceți text.

Diferența este că, prin plasarea unui link către o celulă (=$B$4), condiția se va schimba în funcție de ceea ce este introdus în ea. De exemplu, în loc de măr în B4, indic coacăze, regula se va schimba în consecință, iar blocurile cu același text vor fi completate. Și dacă introduceți măr în câmp, atunci acest cuvânt special va fi căutat și nu va depinde de nimic.

Aici, selectați o culoare de umplere și faceți clic pe OK. Pentru a vedea toate opțiunile, faceți clic pe butonul „Altele”.

Regula a fost creată și o salvăm făcând clic pe butonul „OK”.

Ca rezultat, toate blocurile care conțineau textul specificat au fost vopsite în roșu.

Sunt create reguli și pentru alte fructe, este selectată doar o opțiune de umplere diferită.

Numeric

Acum să ne uităm la datele numerice. Să plasăm numerele din coloana D pe un anumit fundal în funcție de condiția pe care am stabilit-o.

Selectați coloana, creați o regulă și indicați tipul acesteia. Apoi scriem - „Valoare” „mai mare decât” „15”. Puteți fie să introduceți manual ultimul număr, fie să specificați adresa celulei din care vor fi preluate datele. Decideți cu privire la completare, faceți clic pe „OK”.

Blocurile care conțin numere mai mari decât cel selectat sunt colorate.

Să specificăm mai multe reguli pentru celulele selectate - selectați „Managementul regulilor”.

Aici, selectați totul așa cum am descris mai sus, trebuie doar să schimbați culoarea și să setați condiția „mai puțin sau egal”.

Când totul este gata, faceți clic pe „Aplicați” și „OK”.

Totul funcționează, valorile sunt egale și sub 15 sunt vopsite în albastru pal.

Aici diferența dintre mărfurile importate și cele vândute ar fi clar vizibilă. Selectați două coloane și faceți clic „Reguli pentru evidențierea celulelor” – „Valori duplicate”. Alegeți o culoare potrivită. După aceasta, dacă două celule lângă cealaltă sunt colorate, înseamnă că fructele care au fost livrate au fost vândute.

Să folosim ultima coloană ca exemplu pentru a arăta cum să schimbi culoarea unei celule în funcție de valoarea specificată. Selectați-le și accesați „Managementul regulilor”.

Creăm unul nou pentru fragmentul curent dacă este necesar, în această listă derulantă puteți selecta fie pentru întreaga foaie, fie pentru alte foi.

Selectați elementele necesare în fereastra care se deschide. Voi completa toate valorile care sunt mai mari de 90 cu verde închis Deoarece am indicat adresa în ultimul câmp (=$F$15), atunci dacă numărul 90 din celulă se schimbă, de exemplu, la 110, regula. se va schimba de asemenea. Salvați modificările făcând clic pe butonul „OK”.

Voi crea o altă regulă, dar în ea voi evidenția cu verde deschis tot ceea ce este mai mic sau egal cu 90. Nu uitați să salvați totul făcând clic pe butoanele din dreapta jos.

Asta s-a intamplat. Din ultima coloană, puteți determina vizual rapid profitul din vânzare a cărui produs a fost mai mare.

De exemplu, dacă prețul se modifică, regulile vor fi și ele revizuite. Dacă valoarea devine mai mare sau mai mică decât valoarea specificată, culoarea celulei se va schimba automat.

Cred că ați observat că regula este creată nu numai pentru text și numere, ci și pentru date și în funcție de completarea și prezența erorilor în celule. Specificați condiția, alegeți o culoare și salvați.

Pentru a vedea ce ați adăugat, selectați intervalul și în fereastră „Managementul regulilor” va fi o listă completă. Folosind butoanele din partea de sus le puteți adăuga, modifica sau șterge.

Aflați cum să schimbați rapid culoarea unui întreg rând în foile Excel pe baza valorii unei singure celule. Vedeți tehnici și exemple de formule pentru valori numerice și text.

Într-unul dintre articolele anterioare, am discutat despre cum să schimbați culoarea unei celule în funcție de valoarea acesteia. De data aceasta, vom vorbi despre cum să evidențiați un întreg rând în Excel 2010 și 2013 în funcție de valoarea unei celule și, de asemenea, vom dezvălui mai multe trucuri și vom arăta exemple de formule pentru lucrul cu valori numerice și text.

Cum se schimbă culoarea unui rând pe baza valorii numerice a uneia dintre celule

Să presupunem că avem un tabel cu comenzile companiei ca acesta:

Dorim sa coloram randurile cu culori diferite in functie de cantitatea de produs comandata (valoarea din coloana Cant.) pentru a evidenția cele mai importante comenzi. Instrumentul Excel ne va ajuta să facem față acestei sarcini - „ Formatarea condițională».

După cum puteți vedea, schimbarea culorii unui întreg rând în Excel pe baza valorii numerice a uneia dintre celule nu este deloc dificilă. În continuare ne vom uita la câteva exemple de formule și câteva trucuri pentru rezolvarea unor probleme mai complexe.

Cum să creați mai multe reguli de formatare condiționată cu o anumită prioritate

În tabelul din exemplul anterior, probabil că ar fi mai convenabil să folosiți culori de umplere diferite pentru a evidenția rândurile care conțin Cant. sensuri diferite. De exemplu, creați o altă regulă de formatare condiționată pentru liniile care conțin valoarea 10 sau mai multe și evidențiați-le în roz. Pentru aceasta avem nevoie de formula:

Pentru ca ambele reguli pe care le-am creat să funcționeze simultan, trebuie să le plasăm în prioritatea dorită.

Cum se schimbă culoarea unui rând în funcție de valoarea textului uneia dintre celule

Pentru a simplifica controlul comenzii, putem evidenția linii de comandă în tabelul nostru cu diferite culori cu diferite stări de livrare, informații despre care sunt conținute în coloană Livrare:

• Dacă data de livrare a comenzii este în viitor (valoarea Datorită în X zile), atunci umplerea acestor celule ar trebui să fie portocalie;
• Dacă comanda este livrată (valoare Livrat), atunci umplerea acestor celule ar trebui să fie verde;
• Dacă data de livrare a comenzii este în trecut (valoarea Ulterior), atunci umplerea acestor celule ar trebui să fie roșie.

Și, desigur, culoarea de umplere a celulei ar trebui să se schimbe dacă starea comenzii se schimbă.

Cu formula pentru valori LivratȘi Ulterior totul este clar, va fi similar cu formula din primul nostru exemplu:

=$E2="Livrat"
=$E2="Restaurat"

Sarcina este mai dificilă pentru comenzile care trebuie livrate prin X zile (valoare Datorită în X zile). Vedem că timpul de livrare pentru diverse comenzi este de 1, 3, 5 sau mai multe zile, ceea ce înseamnă că formula de mai sus nu se aplică aici deoarece vizează o valoare exactă.

În acest caz, este convenabil să utilizați funcția CĂUTARE(CĂUTARE) și pentru a găsi o potrivire parțială scrieți următoarea formulă:

CĂUTARE(„Scadență în”;$E2)>0
=SEARCH(„Scade în”,$E2)>0

În această formulă E2– aceasta este adresa celulei pe baza valorii căreia vom aplica regula de formatare condiționată; semnul dolarului $ necesar pentru a aplica o formulă pe un întreg rând; condiție " >0 ” înseamnă că regula de formatare va fi aplicată dacă textul specificat (în cazul nostru, „Due in”) este găsit.

Cheie: Dacă formula folosește condiția „ >0 „, atunci linia va fi evidențiată color în fiecare caz când textul specificat se găsește în celula cheie, indiferent de locul exact în celulă. În tabelul exemplu din figura de mai jos, coloana Livrare(coloana F) poate conține textul „Urgent, scadentă în 6 ore”, iar această linie va fi, de asemenea, colorată.

Pentru a evidenția în culoare acele linii în care conținutul celulei cheie începe cu textul sau simbolurile specificate, formula trebuie scrisă sub această formă:

CĂUTARE(„Scadență în”;$E2)=1
=CĂUTARE(„Scadență”, $E2)=1

Trebuie să fiți foarte atenți când utilizați o astfel de formulă și să verificați dacă există date în celulele coloanei cheie care începe cu un spațiu. În caz contrar, îți poți agita creierul pentru o lungă perioadă de timp, încercând să înțelegi de ce formula nu funcționează.

Deci, urmând aceiași pași ca în , am creat trei reguli de formatare, iar tabelul nostru a început să arate astfel:

Cum se schimbă culoarea unei celule în funcție de valoarea altei celule

De fapt, acesta este un caz special. În loc de întregul tabel, selectați coloana sau intervalul în care trebuie să schimbați culoarea celulelor și utilizați formulele descrise mai sus.

De exemplu, putem configura cele trei reguli ale noastre pentru a evidenția numai celulele care conțin numărul de comandă (coloana Număr de ordine) pe baza valorii altei celule din acest rând (folosim valorile din coloană Livrare).

Cum să setați mai multe condiții pentru a schimba culoarea unui rând

Dacă doriți să evidențiați rândurile cu aceeași culoare atunci când apare una dintre mai multe valori diferite, atunci în loc să creați mai multe reguli de formatare, puteți utiliza funcțiile ȘI(ȘI), SAU(SAU) și astfel combină mai multe condiții într-o singură regulă.

De exemplu, putem marca comenzile așteptate în 1 și 3 zile cu roz, iar cele estimate a fi finalizate în 5 și 7 zile cu galben. Formulele vor arăta astfel:

SAU($F2="Scade în 1 zile";$F2="Scade în 3 zile")
=SAU($F2="Scade în 1 zile",$F2="Scade în 3 zile")

SAU($F2="Scade în 5 zile";$F2="Scade în 7 zile")
=SAU($F2="Scade în 5 zile",$F2="Scade în 7 zile")

Pentru a evidenția comenzile cu o cantitate de mărfuri de cel puțin 5, dar nu mai mare de 10 (valoarea din coloana Cant.), scrieți formula cu funcția ȘI(ȘI):

ȘI($D2>=5;$D2<=10)
=ȘI($D2>=5,$D2<=10)

Desigur, în formulele tale poți folosi nu neapărat două, ci atâtea condiții câte sunt necesare. De exemplu:

SAU($F2="Schimbat în 1 zile";$F2="Schimbat în 3 zile";$F2="Durat în 5 zile")
=SAU($F2="Scade în 1 zile",$F2="Scade în 3 zile",$F2="Scade în 5 zile")

Cheie: Acum că ați învățat cum să colorați celulele diferit în funcție de valorile pe care le conțin, poate doriți să aflați câte celule sunt evidențiate într-o anumită culoare și să calculați suma valorilor din acele celule. Vreau să vă mulțumesc, această acțiune se poate face și automat și vom arăta soluția acestei probleme într-un articol dedicat întrebării Cum se calculează cantitatea, se însumează în Excel și se configurează un filtru pentru celulele de o anumită culoare .

Am arătat doar câteva dintre modalitățile posibile de a face un tabel să arate ca o zebră în dungi, a cărei culoare depinde de valorile din celule și se poate modifica odată cu modificările acestor valori. Dacă căutați ceva diferit pentru datele dvs., anunțați-ne și împreună vom găsi cu siguranță ceva.

Pentru a îndeplini această sarcină, vom folosi capacitățile de formatare condiționată.
Să luăm un tabel care conține o listă de comenzi, termenele limită ale acestora, starea curentă și costul. Să încercăm să facem celulele sale să se coloreze singure, în funcție de conținutul lor.

Instrucțiuni pentru Excel 2010

ACTIVAȚI SUBTITRĂRI!

Cum se face acest lucru în Excel 2007

ACTIVAȚI SUBTITRĂRI!
Selectați celulele cu prețurile comenzii și, făcând clic pe săgeata de lângă butonul „Formatizare condiționată”, selectați „Creare regulă”.

Să selectăm al patrulea element, care ne permite să comparăm valorile curente cu media. Suntem interesați de valori peste medie. Făcând clic pe butonul „Format”, vom seta culoarea celulelor.


Confirmăm alegerea noastră, iar celulele cu un preț peste medie devin albastre, atrăgându-ne atenția asupra comenzilor scumpe.


Să selectăm celulele cu stări de ordine și să creăm o nouă regulă. De data aceasta folosim a doua opțiune, care ne permite să verificăm conținutul celulei. Selectați „Text”, „conține” și introduceți cuvântul „Terminat”. Să setăm culoarea verde, să confirmăm, iar lucrarea finalizată va deveni verde.


Ei bine, să facem o altă regulă care colorează în roșu comenzile restante. Subliniem datele de finalizare a comenzilor. Când creăm o regulă, selectăm din nou al doilea element, dar de data aceasta setăm „Valoarea celulei”, „mai puțin decât”, iar în câmpul următor introducem o funcție care returnează data de astăzi.


„OK”, și am primit o masă decorată vesel care ne permite să urmărim vizual progresul comenzilor.


Ați observat că stările sunt setate selectând dintr-o listă derulantă de valori? Am descris cum să facem astfel de liste în instrucțiuni.

Cum se face acest lucru în Excel 2003

ACTIVAȚI SUBTITRĂRI!
„Formatare condiționată” în meniul „Format”. Acest lucru necesită puțin mai mult lucru manual. Așa vor arăta setările pentru prima noastră sarcină – colorarea celulelor cu valori mai mari decât media.


Va trebui să introduceți manual funcția „=AVERAGE()”, plasați cursorul între paranteze, faceți clic pe butonul de lângă acesta și utilizați mouse-ul pentru a specifica intervalul dorit.
Dar principiul de acțiune este același.
Cucerește Excel și ne vedem în curând!